Mysql复合索引最左匹配原则以及索引失效条件

╰半夏微凉° 2023-07-24 03:18 25阅读 0赞

### Mysql复合索引最左匹配原则以及索引失效条件 ###

*  复合索引最左匹配原则
 *  用EXPLAIN 来查看语句是否用到了索引
 *  索引失效的条件
 *   *  一般性建议

# 复合索引最左匹配原则 #

复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引。

对于复合索引：**例如索引是key index （a,b,c）。可以支持a | a,b| a,c| a,b,c 组合进行查找，但不支持 b| c| b,c进行查找。就算是你弄乱了顺序如 c,b,a，mysql也会自动帮你改为a,b,c，然后用到索引 。这就是mysql最左匹配原则，查询条件里面要有复合索引最左边的那个字段才会用到索引。当最左侧字段是常量引用时，索引就十分有效。**  
如我的表格设计是这样子：

KEY `waybill_key` (`waybill_code`,`waybill_id`),

这里给waybill\_code和waybill\_id添加了复合索引  
执行语句：

select * from waybill where waybill_code = '2132131'

用到了索引（explain查询type为ref），但是执行语句：

select * from waybill where waybill_id = '2132131'

并没有用到索引（explain查询type为all）

**所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。**

# 用EXPLAIN 来查看语句是否用到了索引 #

可以用 **EXPLAIN** 来查看索引是否生效，如：

EXPLAIN select * from waybill where waybill_code = '%2132131%'

查询结果：  
![在这里插入图片描述][20200409133408710.png]  
这里的type为ref说明已经用到了索引，这里附上这些type的说明：

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>null</td> 
   <td>MYSQL不用访问表或者索引就直接能到结果</td> 
  </tr> 
  <tr> 
   <td>system</td> 
   <td>表只有一行，MyISAM引擎</td> 
  </tr> 
  <tr> 
   <td>const</td> 
   <td>常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时</td> 
  </tr> 
  <tr> 
   <td>eq_ref</td> 
   <td>每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>如果每次只匹配少数行，那就是比较好的一种，使用=或&lt;=&gt;，可以是左覆盖索引或非主键或非唯一键</td> 
  </tr> 
  <tr> 
   <td>fulltext</td> 
   <td>全文搜索</td> 
  </tr> 
  <tr> 
   <td>ref_or_null</td> 
   <td>与ref类似，但包括NULL</td> 
  </tr> 
  <tr> 
   <td>index_merge</td> 
   <td>表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）</td> 
  </tr> 
  <tr> 
   <td>unique_subquery</td> 
   <td>在in子查询中，就是value in (select…)把形如select unique_key_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的</td> 
  </tr> 
  <tr> 
   <td>index_subquery</td> 
   <td>同上，但把形如”select non_unique_key_column“的子查询替换</td> 
  </tr> 
  <tr> 
   <td>range</td> 
   <td>常数值的范围</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找</td> 
  </tr> 
  <tr> 
   <td>all</td> 
   <td>全表扫描(full table scan)</td> 
  </tr> 
 </tbody> 
</table>

从上往下性能越来越慢，即：

null > system > const > eq_ref > ref > range > index  > all

all 全表扫描的时候最慢

mysql explain type 说明出自：[mysql explain type的区别和性能优化][mysql explain type]

# 索引失效的条件 #

1、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

2、存储引擎不能使用索引范围条件右边的列

3、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select \*

4、mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

5、is null,is not null也无法使用索引

6、like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

**问题：解决like‘%字符串%’时索引不被使用的方法？**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW94aWFvNDg_size_16_color_FFFFFF_t_70]  
**字符串不加单引号索引失效**

SELECT * from staffs where name='2000';  
    -- 因为mysql会在底层对其进行隐式的类型转换
     
    SELECT * from staffs where name=2000;  
    --- 未使用索引

## 一般性建议 ##

1、对于单键索引，尽量选择针对当前query过滤性更好的索引

2、在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引

4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

出自：[联合索引生效和失效的条件看这里][Link 1]

[20200409133408710.png]: /images/20230528/c1a26db130d94dd0a456b4e5b2bd6c19.png
[mysql explain type]: https://blog.csdn.net/lilongsy/article/details/95184594
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW94aWFvNDg_size_16_color_FFFFFF_t_70]: /images/20230528/40245ed808c14a77967f9a63f429e63b.png
[Link 1]: https://blog.csdn.net/fengzongfu/article/details/105324215