MySQL的索引优化策略

MySQL的索引优化策略王光亮2019.10—————————————————————————————目录CONTENTS一、索引的基础介绍二、索引的使用限制三、索引的优化策略1.1什么是索引1.2索引是如何工作的1.3索引的优点1.4索引的分类一、索引的基础介绍

打个比方：把表(包含表结构和数据)比作一本书，索引就是书的目录，方便快速定位查找书中的内容。一、索引的基础介绍1.1什么是索引

索引在MySQL中也叫做键（key），是存储引擎用于快速查找记录的一种数据结构。

再次以书为例，我们是怎么查找书中的内容？一、索引的基础介绍1.2索引是如何工作的

1、确定需要查找的内容主题，通过目录确定内容的页码范围；

2、找到页码指定的页面；

3、查看页面内容。

（存储引擎）先在索引中找到对应值，根据匹配的索引记录找到对应的数据行，然后返回查询结果。一、索引的基础介绍

索引大大减少了服务器需要扫描的数据量。

索引可以避免服务器使用临时表和排序。

索引可以将随机I/O变为顺序I/O。1.3索引的优点一、索引的基础介绍1.4索引的分类索引类型特点使用场景B-Tree索引数据是有序存储的，支持排序和范围查询。大部分数据场景哈希索引（hashindex）基于哈希表，查询速度快，但不支持排序和范围查询。某些特定场景空间数据索引（R-Tree）可有效使用任意维度来组合查询。地理数据存储全文索引（FullTextindex）匹配方式基于文本中的关键字，而非索引的值。关键字搜索2.1查询列不能包含表达式和函数2.2遵循左侧匹配原则2.3字符串判断必须加引号2.4不支持通配符开头的LIKE查询二、索引的使用限制2.5其他限制二、索引的使用限制

查询列如果包含表达式或函数，会导致查询无法使用索引。2.1查询列不能包含表达式和函数

举例2.1：以下2个查询的逻辑条件和查询结果一样，但是效率相差很多。正常查询：select*fromams_orderpaydetail_1whereorder_id=746212228578983936;

问题查询：select*fromams_orderpaydetail_1whereorder_id+1=746212228578983937；二、索引的使用限制分析：由于第2个语句“=”左侧是表达式“order_id+1”，优化器无法正常解析，导致索引失效，因此查询走全表扫描。二、索引的使用限制

在索引列为多列的情况下，查询语句的列顺序必须遵循“最左匹配原则”，即查询列的顺序必须与索引列顺序一致。2.2遵循左侧匹配原则

举例2.2：如果A表上建有多列索引

idx(a,b,c)，则以下查询符合“左侧匹配原则”：1、wherea=？andb=？andc=?2、wherea=？andb=？3、wherea=？或wherea=？andc=？二、索引的使用限制

以下查询不符合“左侧匹配原则”，无法使用索引的情况：1、whereb=？andc=?或whereb=？2、wherec=？andb=?或wherec=?

因此：针对以上索引，只要查询中有“a=？”的条件，就可以使用索引，但考虑到索引的使用效率，尽量按照“a=？andb=？andc=?”的顺序来写查询语句。二、索引的使用限制

当索引列为字符类型时，进行等值或范围比较时，查询条件中的常量值必须添加引号，否则会导致索引失效。2.3字符串判断必须加引号

举例2.3：ams_orderpaydetail_1表的pay_request_no字段是字符类型，使用该字段进行等值判断查询时，引号加与不加的在性能上有什么差别？二、索引的使用限制

加了引号：可以使用索引，只需要扫描1行记录。

不加引号：无法使用索引，走全表扫描方式，效率很低。二、索引的使用限制MySQL存储引擎不支持通配符开头的前缀LIKE（LIKE'%...'）查询，因此，查询尽量采用后缀匹配（LIKE'...%'）的方式。2.4不支持通配符开头的LIKE查询

如果在部分业务场景下，必须用最左前缀的LIKE，可以采用“延迟关联”的策略来提升索引的利用率（下一章节介绍）。二、索引的使用限制

后缀LIKE：可以使用索引，查询本身是范围查询，只需要扫描匹配的记录。

最左前缀LIKE：无法使用索引，是全表扫描查询，效率很低。二、索引的使用限制

MySQL索引使用的其他限制包括：（1）范围查询后的列不能使用索引。（2）使用OR条件，会导致索引失效。（3）查询数据量占总表的20%，自动选择表扫描。2.5其他限制3.1选择合适的索引列顺序3.2查询覆盖索引3.3延迟关联3.4索引扫描排序三、索引的优化策略三、索引的优化策略

创建索引时，将选择性较高的列放在前面，可以优化where条件的查找（不考虑排序和分组的影响）。3.1选择合适的索引列顺序

举例3.1：以下面的查询为例，查询中用到了staff_id和customer_id字段，那么应该创建一个(staff_id,customer_id)的索引，还是应该颠倒顺序呢？select*frompaymentwherestaff_id=2andcustomer_id=268；三、索引的优化策略可以通过以下SQL语句来进行计算和比较：selectcount(*),count(distinctstaff_id)/count(*)asstaff_id_selectivity,count(distinctcustomer_id)/count(*)ascustomer_id_selectivityfrompayment;结论：customer_id的选择性更高，可以更快过滤数据行，因此应将其放到第一列。查询结果如下：count(*)：16049staff_id_selectivity：0.0001customer_id_selectivity：0.0373三、索引的优化策略

如果一个索引包含（或覆盖）了查询需要的全部字段值，就可称之为“覆盖索引”（Extra信息为“Usingindex”）。覆盖索引的效率很高，因为只需要读取索引就可以获得数据。3.2覆盖索引

举例3.2：ams_orderpaydetail_1表上创建了merchant_app_id、pay_time、pay_state三列的复合索引，查询如何实现最佳的覆盖索引？三、索引的优化策略

最佳的索引覆盖：查询的所有列都是索引中的字段，且where后面的字段顺序和索引列顺序完全一致。Extra信息为“Usingindex”。三、索引的优化策略

一般的索引覆盖1：查询列都是索引中的字段，但是where语句中pay_time使用的是范围查询，无法完全覆盖索引，Extra信息为“Usingwhere;Usingindex”。三、索引的优化策略

索引未覆盖1：查询表的全部列，where后面的字段顺序和索引列顺序完全一致，该查询可以通过索引筛选数据，但无法实现索引覆盖，Extra信息为“NULL”。三、索引的优化策略

索引未覆盖2：查询表的全部列，且where语句中pay_time使用的是范围查询，无法完全覆盖索引，Extra信息为“Usingindexcondition”。三、索引的优化策略

合理的覆盖索引只需要扫描索引而无需回表，可以极大地提升查询性能。因此，在定义索引时，不仅参考where条件，还需要考虑查询选择的数据列。三、索引的优化策略

索引条件推送ICP（indexconditionpushdown）是MySQL5.6的新特性，当查询使用ICP时，Explain的Extra信息为”Usingindexcondition“。在没有ICP之前，存储引擎根据索引查找并将数据返回给服务端，服务端再根据where条件进行数据过滤。有了ICP之后，存储引擎在取出数据的同时，还可以根据索引中的列进行where条件过滤，也就是将where的部分过滤操作放在了存储引擎层，这样就减少了服务端对记录的处理。关于ICP三、索引的优化策略

在特殊查询情况下，可以利用“延迟关联”的方式，延迟对列的访问，从而提高索引的利用率。3.3延迟关联

举例3.3：ams_orderpaydetail_1表上创建了opposite_member_name列的索引，以下查询如何优化，才能利用索引呢？select*fromams_orderpaydetail_1whereopposite_member_namelike'%深圳智灵%';三、索引的优化策略

原SQL的执行计划如下：没有使用索引，查询走全表扫描。三、索引的优化策略优化后的SQL如下：selecta.*fromams_orderpaydetail_1ajoin( selectidfromams_orderpaydetail_1 whereopposite_member_namelike'%深圳智灵%')bona.id=b.id;优化思路：可以采用join的方式，子查询中先根据opposite_member_name获取匹配的id，再用id和外层的查询匹配筛选数据。三、索引的优化策略

修改后的SQL的执行计划如下：子查询可以用到索引，虽然索引无法覆盖整个查询，但效果比全表扫描好很多。三、索引的优化策略

使用索引扫描排序速度很快，但需要满足“最左前缀”原则：索引列的顺序必须和ORDERBY子句的顺序一致，包括列的排序方向（倒序或正序）。3.4索引扫描排序

如果前导列是常量，前导列和ORDERBY的字段可以一起构成“最左前缀”。select*frombps.ams_orderpaydetail_1wheremerchant_app_id=920133151198547968orderbymerchant_app_id,pay_time,pay_state三、索引的优化策略

举例3.4：ams_orderpaydetail_1表上创建了merchant_app_id、pay_time、pay_state三列的复合索引。针对以下查询有不同的ORDERBY组合：select*frombps.ams_orderpaydetail_1wheremerchant_app_id=920133151198547968limit100；三、索引的优化策略

组合1：merchant_app_id+pay_state组合2：merchant_app_id（ASC）+pay_state（DESC）

以下情况不符合“最左前缀原则”，无法使用索引扫描排序，Extra信息包含