本文介绍: 正确的选择和创建索引是实现高性能查询的基础,以下是高效使用索引的方法。

正确的选择和创建索引是实现高性能查询的基础,以下是高效使用索引的方法
演示的sql

独立的列

独立的列指的是索引既不是表达式的一部分也不是函数的参数。

mysql> select actor_id from actor where actor_id + 1 = 5;

mysql> SELECT actor_id FROM actor WHERE CAST(actor_id AS CHAR) = '5';
前缀索引

如果索引是很长的列,那么索引会变得很大,并且导致索引数层数变高。通常可以索引的部分字符,这样可以节约索引空间。但是同时也要保证区分度。
区分度的计算这里以city表city字段为例

mysql> select count(distinct LEFT(city,3))/count(*) as e1, count(distinct LEFT(city,4))/count(*) as e2,  count(distinct LEFT(city,5))/count(*) as e3, count(distinct LEFT(city,6))/count(*) as e4, count(distinct LEFT(city,7))/count(*) as e5, count(distinct LEFT(city,8))/count(*) as e6 , count(distinct LEFT(city,9))/count(*) as e7 , count(distinct LEFT(city,10))/count(*) as e8 from city_demo ;
+--------+--------+--------+--------+--------+--------+--------+--------+
| e1     | e2     | e3     | e4     | e5     | e6     | e7     | e8     |
+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.0236 | 0.0293 | 0.0305 | 0.0309 | 0.0310 | 0.0310 | 0.0310 | 0.0311 |
+--------+--------+--------+--------+--------+--------+--------+--------+

可以看到当字符长度是5或者6时,区分度已经不怎么增长了。
可以创建前缀索引

mysql> alter table city_demo add key (city(6));

前缀索引可以使得索引更小更快,但是他不可以做order by和group by。

多列索引

很多人对多列索引的理解不够,一种常见的是为每个列都创建索引,或者错误顺序创建索引。或者把where条件后面所有的列都创建索引。

在多个列上创建独立的单列索引大多数情况下不能提高MYSQL的查询性能。比如

mysql> select film_id,actor_id from film_actor where actor_id =1 or film_id =1;

在老的版本,这个查询会全表扫描,但是在新版本中会对这两个单列索引进行扫描,并将结果合并,索引合并策略是一种优化的结果。

mysql> explain select film_id,actor_id from film_actor where actor_id =1 or film_id =1 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
     filtered: 100.00
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where
1 row in set, 1 warning (0.00 sec)

基本等价于

mysql> select film_id,actor_id from film_actor where actor_id =1  union all select film_id,actor_id from film_actor where film_id =1 and actor_id != 1;
合适的索引顺序

索引列的顺序,意味这首先按照最左序列进行排序,其次是第二列,索引可以根据升序或者降序扫描,以满足order by,group by,distinct等子句的需求。

索引列的顺序问题,将选择性高区分度高的列放在最前面是有帮助的,但是排序以及避免随机IO的优先级更高,当不需要排序和分组时,选择性高的列放前面,举例说明。

mysql> select * from payment where staff_id=2 and customer_id=584;

联合索引的顺序取决于哪个区分度更高

mysql> select sum(staff_id=2),sum(customer_id=584) from payment G
*************************** 1. row ***************************
     sum(staff_id=2): 7990
sum(customer_id=584): 30

这时是staff_id=2基数更大,应该把customer_id放到前面
以上是根据具体值的,对于全量数据来说

mysql> SELECT COUNT(DISTINCT staff_id)/count(*) as e1,count(distinct customer_id)/count(*) as e2 ,count(*) from payment G
*************************** 1. row ***************************
      e1: 0.0001
      e2: 0.0373
count(*): 16044
1 row in set (0.00 sec)

全局来说customer_id区分度更高,作为索引列第一列。

聚簇索引

聚簇索引并不是一种索引类型,而是一种数据存储方式,在Innodb中保存了B-tree索引和数据行,叶子页包含行的全部数据,节点页只包含索引列,这个索引列就是主键列,如果没有创建主键,那么Innodb会隐式创建主键。MYLSAM的叶子节点存储的时行的地址。

聚集数据的好处:

  • 这个索引列和行数据是紧凑存储在一起的,根据主键查找行数据时,可减少磁盘IO次数,
  • 使用覆盖索引可以直接使用叶节点的主键值
    聚集数据的缺点:
  • 按照顺序插入的,每次更新会导致其他行移动新的位置
  • 在移动过程中会导致页分裂,删除的时候会引起页的合并
覆盖索引

MYSQL可以使用索引来获取列的数据,这样就可不需要读取整行的数据了,如果索引的叶子节点已经包含要查询的数据了,那么就不需要回表查询,如果一个索引包含需要查询字段的值,那么就程位覆盖索引。

当发起一个被索引覆盖的查询,在explain的extra列可以看到’using index’的值。

mysql> explain select store_id,film_id from inventory G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index

假设覆盖索引只覆盖了where后面的字段,但是没有覆盖查询的字段。

mysql> EXPLAIN SELECT * FROM products WHERE actor = 'SEAN CARRY' AND title LIKE '%APOLLO%' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: products
   partitions: NULL
         type: ref
possible_keys: idx_actor_title
          key: idx_actor_title
      key_len: 63
          ref: const
         rows: 3
     filtered: 16.67
        Extra: Using index condition

Using index condition表示mysql可以根据该索引找到actor,但是title无法走索引,需要根据actor的结果再匹配。
这个查询可以优化成

mysql> explain select * from products join (select prod_id from products where actor = 'SEAN CARRY' AND title LIKE '%APOLLO%') as t1 on t1.prod_id = products_id  G

这样做实现延迟关联,子查询实现覆盖索引,再用外层匹配返回所有列值。

覆盖索引不仅可以访问索引中的部分列,也可以返回叶子节点上主键值。

mysql> explain select actor_id,last_name from actor where last_name = 'HOPPER' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

使用索引扫描做排序

MYSQL有两种发过誓可以生成有序结果,通过排序操作或者通过索引顺序扫描。只有当索引顺序和order by子句顺序一致,并且所有列的排序方向(正序和倒序)一样的时候,MYSQL才能使用索引对结果排序,当要关联多张表的时候,只有当order by子句引用的字段全部是第一张表的死后,才会使用索引做排序。order by子句和查询类的限制是一样的,要满足索引的最左前缀要求。

即使ORDER BY子句不满足最左前缀的要求,也可以使用索引顺序扫描。

mysql> explain select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id,customer_id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date
          key: rental_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

没有出现using filesort操作

原文地址:https://blog.csdn.net/qq_51059003/article/details/135629354

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。

如若转载,请注明出处:http://www.7code.cn/show_59066.html

如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注