mysql索引失效的原因总结

在工作中如果我们想提高一条语句查询速度通常都会想对字段建立索引。但是索引并不是万能的。建立了索引并不意味着任何查询语句都能走索引扫描。稍不注意可能你写的查询语句是会导致索引失效从而走了全表扫描虽然查询的结果没问题但是查询的性能大大降低。今天就来跟大家盘一盘常见的 6 种会发生索引失效的场景。不仅会用实验案例给大家说明也会清楚每个索引失效的原因。mysql索引教程参考mysql索引教程参考https://www.bilibili.com/video/BV1c3uEzcESh/?spm_id_from333.337.search-card.all.click发车索引存储结构长什么样我们先来看看索引存储结构长什么样因为只有知道索引的存储结构才能更好的理解索引失效的问题。索引的存储结构跟 MySQL 使用哪种存储引擎有关因为存储引擎就是负责将数据持久化在磁盘中而不同的存储引擎采用的索引数据结构也会不相同。MySQL 默认的存储引擎是 InnoDB它采用 BTree 作为索引的数据结构至于为什么选择 B 树作为索引的数据结构 详细的分析可以看我这篇文章为什么 MySQL 喜欢 B 树在创建表时InnoDB 存储引擎默认会创建一个主键索引也就是聚簇索引其它索引都属于二级索引。MySQL 的 MyISAM 存储引擎支持多种索引数据结构比如 B 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时创建的主键索引默认使用的是 B 树索引。虽然InnoDB 和 MyISAM 都支持 B 树索引但是它们数据的存储结构实现方式不同。不同之处在于InnoDB 存储引擎B 树索引的叶子节点保存数据本身MyISAM 存储引擎B 树索引的叶子节点保存数据的物理地址接下来我举个例子给大家展示下这两种存储引擎的索引存储结构的区别。这里有一张 t_user 表其中 id 字段为主键索引其他都是普通字段。如果使用的是 MyISAM 存储引擎B 树索引的叶子节点保存数据的物理地址即用户数据的指针如下图如果使用的是 InnoDB 存储引擎 B 树索引的叶子节点保存数据本身如下图所示InnoDB 存储引擎根据索引类型不同分为聚簇索引上图就是聚簇索引和二级索引。它们区别在于聚簇索引的叶子节点存放的是实际数据所有完整的用户数据都存放在聚簇索引的叶子节点而二级索引的叶子节点存放的是主键值而不是实际数据。如果将 name 字段设置为普通索引那么这个二级索引长下图这样叶子节点仅存放主键值。知道了 InnoDB 存储引擎的聚簇索引和二级索引的存储结构后接下来举几个查询语句说下查询过程是怎么选择用哪个索引类型的。在我们使用「主键索引」字段作为条件查询的时候如果要查询的数据都在「聚簇索引」的叶子节点里那么就会在「聚簇索引」中的 B 树检索到对应的叶子节点然后直接读取要查询的数据。如下面这条语句// id 字段为主键索引 select * from t_user where id1;在我们使用「二级索引」字段作为条件查询的时候如果要查询的数据都在「聚簇索引」的叶子节点里那么需要检索两颗B树先在「二级索引」的 B 树找到对应的叶子节点获取主键值然后用上一步获取的主键值在「聚簇索引」中的 B 树检索到对应的叶子节点然后获取要查询的数据。上面这个过程叫做回表如下面这条语句// name 字段为二级索引 select * from t_user where name林某;在我们使用「二级索引」字段作为条件查询的时候如果要查询的数据在「二级索引」的叶子节点那么只需要在「二级索引」的 B 树找到对应的叶子节点然后读取要查询的数据这个过程叫做覆盖索引。如下面这条语句// name 字段为二级索引 select id from t_user where name林某;上面这些查询语句的条件都用到了索引列所以在查询过程都用上了索引。但是并不意味着查询条件用上了索引列就查询过程就一定都用上索引接下来我们再一起看看哪些情况会导致索引实现而发生全表扫描。首先说明下下面的实验案例我使用的 MySQL 版本为 8.0.26。对索引使用左或者左右模糊匹配当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。比如下面的 like 语句查询 name 后缀为「林」的用户执行计划中的 typeALL 就代表了全表扫描而没有走索引。// name 字段为二级索引 select * from t_user where name like %林;如果是查询 name 前缀为林的用户那么就会走索引扫描执行计划中的 typerange 表示走索引扫描keyindex_name 看到实际走了 index_name 索引// name 字段为二级索引 select * from t_user where name like 林%;为什么 like 关键字左或者左右模糊匹配无法走索引呢因为索引 B 树是按照「索引值」有序排列存储的只能根据前缀进行比较。举个例子下面这张二级索引图是以 name 字段有序排列存储的。假设我们要查询 name 字段前缀为「林」的数据也就是 name like 林%扫描索引的过程首节点查询比较林这个字的拼音大小比首节点的第一个索引值中的陈字大但是比首节点的第二个索引值中的周字小所以选择去节点2继续查询节点 2 查询比较节点2的第一个索引值中的陈字的拼音大小比林字小所以继续看下一个索引值发现节点2有与林字前缀匹配的索引值于是就往叶子节点查询即叶子节点4节点 4 查询比较节点4的第一个索引值的前缀符合林字于是就读取该行数据接着继续往右匹配直到匹配不到前缀为林的索引值。如果使用 name like %林 方式来查询因为查询的结果可能是「陈林、张林、周林」等之类的所以不知道从哪个索引值开始比较于是就只能通过全表扫描的方式来查询。想要更详细了解 InnoDB 的 B 树查询过程可以看我写的这篇B 树里的节点里存放的是什么呢查询数据的过程又是怎样的对索引使用函数有时候我们会用一些 MySQL 自带的函数来得到我们想要的结果这时候要注意了如果查询条件中对索引字段使用函数就会导致索引失效。比如下面这条语句查询条件中对 name 字段使用了 LENGTH 函数执行计划中的 typeALL代表了全表扫描// name 为二级索引 select * from t_user where length(name)6;为什么对索引使用函数就无法走索引了呢因为索引保存的是索引字段的原始值而不是经过函数计算后的值自然就没办法走索引了。不过从 MySQL 8.0 开始索引特性增加了函数索引即可以针对函数计算后的值建立一个索引也就是说该索引的值是函数计算后的值所以就可以通过扫描索引来查询数据。举个例子我通过下面这条语句对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。alter table t_user add key idx_name_length ((length(name)));然后我再用下面这条查询语句这时候就会走索引了。对索引进行表达式计算在查询条件中对索引进行表达式计算也是无法走索引的。比如下面这条查询语句执行计划中 type ALL说明是通过全表扫描的方式查询数据的explain select * from t_user where id 1 10;但是如果把查询语句的条件改成 where id 10 - 1这样就不是在索引字段进行表达式计算了于是就可以走索引查询了。为什么对索引进行表达式计算就无法走索引了呢原因跟对索引使用函数差不多。因为索引保存的是索引字段的原始值而不是 id 1 表达式计算后的值所以无法走索引只能通过把索引字段的取值都取出来然后依次进行表达式的计算来进行条件判断因此采用的就是全表扫描的方式。有的同学可能会说这种对索引进行简单的表达式计算在代码特殊处理下应该是可以做到索引扫描的比方将 id 1 10 变成 id 10 - 1。是的是能够实现但是 MySQL 还是偷了这个懒没有实现。我的想法是可能也是因为表达式计算的情况多种多样每种都要考虑的话代码可能会很臃肿所以干脆将这种索引失效的场景告诉程序员让程序员自己保证在查询条件中不要对索引进行表达式计算。对索引隐式类型转换如果索引字段是字符串类型但是在条件查询中输入的参数是整型的话你会在执行计划的结果发现这条语句会走全表扫描。我在原本的 t_user 表增加了 phone 字段是二级索引且类型是 varchar。然后我在条件查询中用整型作为输入参数此时执行计划中 type ALL所以是通过全表扫描来查询数据的。select * from t_user where phone 1300000001;但是如果索引字段是整型类型查询条件中的输入参数即使字符串是不会导致索引实现还是可以走索引扫描。我们再看第二个例子id 是整型但是下面这条语句还是走了索引扫描的。explain select * from t_user where id 1;为什么第一个例子会导致索引失效而第二例子不会呢要明白这个原因首先我们要知道 MySQL 的数据类型转换规则是什么就是看 MySQL 是会将字符串转成数字处理还是将数字转换成字符串处理。我在看《mysql45讲的时候》看到一个简单的测试方式就是通过 select “10” 9 的结果来知道MySQL 的数据类型转换规则是什么如果规则是 MySQL 会将自动「字符串」转换成「数字」就相当于 select 10 9这个就是数字比较所以结果应该是 1如果规则是 MySQL 会将自动「数字」转换成「字符串」就相当于 select 10 9这个是字符串比较字符串比较大小是逐位从高位到低位逐个比较按ascii码 那么10字符串相当于 “1”和“0”字符的组合所以先是拿 “1” 字符和 “9” 字符比较因为 “1” 字符比 “9” 字符小所以结果应该是 0。在 MySQL 中执行的结果如下图上面的结果为 1说明MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。前面的例子一中的查询语句我也跟大家说了是会走全表扫描//例子一的查询语句 select * from t_user where phone 1300000001;这是因为 phone 字段为字符串所以 MySQL 要会自动把字符串转为数字所以这条语句相当于select * from t_user where CAST(phone AS signed int) 1300000001;可以看到CAST 函数是作用在了 phone 字段而 phone 字段是索引也就是对索引使用了函数而前面我们也说了对索引使用函数是会导致索引失效的。例子二中的查询语句我跟大家说了是会走索引扫描//例子二的查询语句 select * from t_user where id 1;这时因为字符串部分是输入参数也就需要将字符串转为数字所以这条语句相当于select * from t_user where id CAST(1 AS signed int);可以看到索引字段并没有用任何函数CAST 函数是用在了输入参数因此是可以走索引扫描的。联合索引非最左匹配对主键字段建立的索引叫做聚簇索引对普通字段建立的索引叫做二级索引。那么多个普通字段组合在一起创建的索引就叫做联合索引也叫组合索引。创建联合索引时我们需要注意创建时的顺序问题因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候会存在差别。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配。比如如果创建了一个 (a, b, c) 联合索引如果查询条件是以下这几种就可以匹配上联合索引where a1where a1 and b2 and c3where a1 and b2需要注意的是因为有查询优化器所以 x 字段在 where 子句的顺序并不重要。但是如果查询条件是以下这几种因为不符合最左匹配原则所以就无法匹配上联合索引联合索引就会失效:where b2where c3where b2 and c3有一个比较特殊的查询条件where a 1 and c 3 符合最左匹配吗这种其实严格意义上来说是属于索引截断不同版本处理方式也不一样。MySQL 5.5 的话前面 a 会走索引在联合索引找到主键值后开始回表到主键索引读取数据行然后再比对 z 字段的值。从 MySQL5.6 之后有一个索引下推功能可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。大概原理是截断的字段会被下推到存储引擎层进行条件判断因为 c 字段的值是在 (a, b, c) 联合索引里的然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据无需再回表读取数据来进行判断减少回表次数从而提升了性能。比如下面这条 where a 1 and c 0 语句我们可以从执行计划中的 ExtraUsing index condition 使用了索引下推功能。为什么联合索引不遵循最左匹配原则就会失效原因是在联合索引的情况下数据是按照索引第一列排序第一列数据相同时才会按照第二列排序。也就是说如果我们想使用联合索引中尽可能多的列查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索肯定无法走索引。WHERE 子句中的 OR在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。举个例子比如下面的查询语句id 是主键age 是普通列从执行计划的结果看是走了全表扫描。select * from t_user where id 1 or age 18;这是因为 OR 的含义就是两个只要满足一个即可因此只有一个条件列是索引列是没有意义的只要有条件列不是索引列就会进行全表扫描。要解决办法很简单将 age 字段设置为索引即可。可以看到 typeindex merge index merge 的意思就是对 id 和 age 分别进行了扫描然后将这两个结果集进行了合并这样做的好处就是避免了全表扫描。总结今天给大家介绍了 6 种会发生索引失效的情况当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列使用函数就会导致索引失效。当我们在查询条件中对索引列进行表达式计算也是无法走索引的。MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而条件语句中的输入参数是数字的话那么索引列会发生隐式类型转换由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。最后留一个很有意思的思考题给大家。题目1一个表有多个字段其中 name 是索引字段其他非索引id 拥有自增主键索引。题目2一个表有2个字段其中 name 是索引字段id 拥有自增主键索引。上面两张表分别执行以下查询语句select * from s where name like xxxselect * from s where name like xxx%select * from s where name like %xxxselect * from s where name like %xxx%针对题目 1 和题目 2 的数据表哪些触发索引查询哪些没有