【MySQL】索引使用规则——（覆盖索引，单列索引，联合索引，前缀索引，SQL提示，数据分布影响，查询失效情况）

前言

大家好吖，欢迎来到 YY 滴MySQL系列 ，热烈欢迎！ 本章主要内容面向接触过C++的老铁
主要内容含：

欢迎订阅 YY滴C++专栏！更多干货持续更新！以下是传送门！

• YY的《C++》专栏
• YY的《C++11》专栏
• YY的《Linux》专栏
• YY的《数据结构》专栏
• YY的《C语言基础》专栏
• YY的《单片机》专栏
• YY的《STM32》专栏
• YY的《数据库》专栏
  

一.索引使用规则

※.验证索引效率提升

• 在未建立索引之前，执行如下SOL语句，查看SQL的耗时
• 耗时11s
  
• 利用主键索引（id）查，耗时0s
  
• 利用第二个字段sn查，耗时21s，性能极低
  
• 针对字段sn创建索引，然后再次执行相同的SQL语句，再次查看SQL的耗时
• 从21s变成0.01s，性能大大提升

1.覆盖索引——查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

• 尽量使用覆盖索引 (查询使用了素引，并且需要返回的列，在该索引中已经全部能够找到) ，减少select *
  

演示：

• 我们查看索引
  
• 紫色部分：上面比下面的效率好
  
• 多出的蓝色部分(返回的列)，不能够在在该索引中找到， 上部分的效率比下部分高
• 上部分：usingindexcondition:查找使用了索引，但是需要回表查询数据
• 下部分：using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

2.单列索引＆联合索引

• 单列索引：即一个索引只包含单个列。
• 联合家引：即一个索引包含了多个列。
• 在业务场景中，如果存在多个查询负件，考虑针对于查询字段建立索引时， 建议优先建立联合索引，而非单列引。
• 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。 要强制就用可视日志。

演示：

• name和phone字段，都是单列索引，但只用到一个字段索引
  
• 我们给name和phone字段创建联合索引，MySQL优化器会评估哪个字段的索引效率更高。如果我们要主动设置SQL语句用的索引，涉及到下文我们提到的SQL提示
  

3.前缀索引——解决冗长字符串与索引问题

【1】前缀索引＆索引选择性的介绍

• 当字段类型为字符串(varchar，text等)时， 有时候需要索引很长的字符串 ，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。
• 此时可以只将字符串的 一部分前缀，建立索引 ，这样可以大大节约索引空间，从而提高索引效率。
  
• 如下图，计算可得 字段选择性是1
  
• 不断调整substring截取部分，可得到不同选择性
  

【2】前缀索引创建演示：

• 针对email字段截取字符串，建立前缀索引，降低索引体积
  
• 建立前五个字符构成的前缀索引
  
• 查询发现用到了创建的前五个字符构成的前缀索引
  

5.SQL提示——指定某个索引/忽略索引/强制索引

• SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
• 例如下图SQL执行计划，可能索引有两个，但是最终应用的索引只有一个，某些情况下我们就是要 指定用某个索引
  

演示：

• 有这样一张表，我们看下这段SQL的执行计划，索引是 复合索引idx_user_pro_age_sta
  
• 我们针对profession创建一个单列索引
  
• 我们想要用这个单列索引，我们就建议索引use XX——MySQL不一定接受，要强制用force XX
  

6.数据分布影响——MySQL自我评估

• 如果MySQL评估使用索引比全表 更慢 ，则不使用索引

演示：

• 有一张表，我们关注其phone字段
  
• 当我们进行不同的范围查询时，MySQL会自己选择用不用索引
• 例如绿色部分用了联合索引，而红色部分要查找的数目已经大于总数一半了，此时MySQL自己选择全表扫描
  

7.查询失效的几种情况

【1】违背——最左前缀法则(联合索引)

• 如果索引了多列(联合索引)，要遵守最左前缀法则。
• 最左前缀法则指的是查询从索引的最左列开始 （最左列存在即可），并且不跳过索引中的列，索引才不会失效
• 如果跳跃某一列 ，索引将部分失效 (后面的字段索引失效)

演示：

• 有如下表
  
• 查看索引，有一个age字段和status字段的联合索引idx_user_pro_age_sta
  
• 联合索引生效，索引长度为54
• 去掉status条件后，索引长度为49，因此可以判断status部分对应的索引长度为5
  
• 去掉status和age条件后，索引长度为47，因此可以判断age部分对应索引长度为2
  

索引失效：

• 索引的最左列不存在，key为null，不走索引，走全表扫描
  

• 去除掉age，也会走联合索引，但是长度只有47， 只有profession部分走索引 ，部分失效
  

• 注： 索引的最左列只要存在即可，顺序无所谓
  

【2】范围查询右侧失效

• 联合索引中，出现范围查询(>，<)， 查询范围右侧的列索引失效
• 实际中应该规避(>，<)，在业务允许的范围下使用（>=，<=），（>=，<=）不会失效

演示：

• 如下图，联合索引正常长度应该是54，图中是49，说明没有走status索引——status索引失效了
  
• 如下图，采用>=,索引长度为54，说明 >=的范围查询不会让右侧失效
  

【3】用or分割开的条件，or后面没索引，所有索引失效

• 如果or前的条件中的列有索引，而后面的列中没有索引，那么 涉及的索引都不会被用到
• 例如：age无索引，但是主键索引仍然不会生效
  

【4】索引列上进行运算操作，索引失效

• 不要在索引列上进行运算操作， 索引将失效 、
  

演示：

• 有这样一张表，我们关注phone字段
  
• phone是单列索引，发现用上了
  
• 我们想要找手机号最后两位是15的，利用substring函数运算截取，第十位开始，截2位
• 索引失效
  

【5】字符串类型字段使用时，不加引号，索引失效

• 字符串类型字段使用时，不加引号， 索引失效

演示：

• phone是varchar类型，不加单引号，也能查
• 但是由于存在隐式类型转换，索引会失效
  

【6】头部模糊匹配，索引失效

• 如果仅仅是 尾部模糊匹配，索引不会失效
• 如果是 头部模糊匹配，索引失效