Mysql数据库表中为什么有索引却没有提高查询速度

背景

时间过得太快了，春节假期感觉光速般就结束了，转眼间就要继续搬砖上班了。紧接着很快就要进入金三银四的求职面试高峰期，程序猿小枫还没有找到令自己感到满意的工作。就算是在过年放假期间也在拼命的准备技术面试，这不他又梳理了下之前面试过程中面试官经常问到的关于数据库方面的一道面试题，我们来一起帮小枫看看有没有遗漏的地方吧。

面试题目——问题

面试官:看你的简历中有提到过曾经进行过索引优化的工作，那我就问问你，假设数据库表中有索引，但是进行SQL数据查询还是很慢，这种情况下应该怎么分析查询慢的原因？

分析

在进行数据库查询的时候，我们都知道索引可以加快数据查询的效率。但是在实际的业务场景下，经常会遇到即使在表中增加了索引，但是同样还是会出现数据查询慢的问题。这就需要具体分析数据查询慢的具体原因到底是什么了。

首先需要进行确认的就是SQL语句中对应的条件查询中字段有没有建立索引。虽然面试官说了有索引，但是不一定SQL语句中的查询字段有建立索引，所以第一步应该进行SQL中的字段索引确认。如果没有建立对应的索引可以先尝试下建立索引再进行查询。如果已经有了索引，查询的字段也是索引字段，那么就要考虑下是不是出现了索引失效的情况。下面我们再具体分析下，看看在哪些场景下会出现索引失效的情况。

索引失效场景

在分析索引失效场景之前，我们必须要清楚索引结构的特点是什么。关于Mysql的数据库索引结构在之前的文章中已经进行了详细的分析，可以参见之前的文章。

Mysql数据库索引面试题(程序员基础技能)

我们来看下Mysql数据库索引的结构特点：

 这里以user_info这张表来作为分析的基础，在user_info这张表上，我们分别创建了idx_name以及idx_phone二级索引以及idx_age_address联合索引。

CREATE TABLE IF NOT EXISTS `user_info` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) NOT NULL,
 `gender` int(2) NOT NULL,
 `age` int(10) NOT NULL,
 `phone_number` VARCHAR(20) NOT NULL,
 `address` VARCHAR(40) NOT NULL,
  PRIMARY KEY ( `id` ),
  KEY `idx_name`(`name`),
  KEY `idx_phone`(`phone_number`),
  KEY `idx_age_address`(`age`,`address`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 1、字段类型不匹配导致的索引失效

进行SQL数据查询的时候，where条件字段类型与实际表中字段类型不匹配的时候，Mysql会进行隐式的数据类型转换，而类型转换会使用到内置函数，导致在进行数据查询的时候并没有使用索引。我们可以使用explain命令查看sql语句。可以看的出来在key栏中，对应的值为null，说明并没有使用索引进行查询。 

 但是如果在按照phone_number字段为字符串类型进行查询的时候，Mysql没有进行隐式的类型转换，所以最终还是走了索引。

• 2、被索引字段使用了表达式计算

在where中条件使用了条件表达式的时候，数据表中的索引就失效了，实际是因为Mysql需要将索引字段取出来之后再进行表达式的条件判断，因而进行了全表扫描，导致索引失效。

• 3、被索引字段使用了内置函数

索引字段实际上是依赖于整个B+索引树的遍历，而索引树的遍历又依赖于索引树底层叶子节点的有序性。索引保存的是索引列的原始值，如果经过函数计算，Msql的解释器无法判断计算后的索引在原来的索引树上是否可以被索引到，因此它就直接放弃使用索引查询了。

• 4、like使用了%X模糊匹配

使用左模糊匹配以及左右模糊匹配都会导致索引失效，但是使用右模糊匹配，还是可以走索引查询的。

由于B+树按照索引值进行排序的，实际是按照最左前缀进行比较，而使用了%作为最左前缀，Mysql无法判断其有序性，因此只能进行全表扫描查询。

• 5、索引字段不是联合索引字段的最左字段

如果数据库表中有联合索引的话，我们在SQL查询语句中使用的索引字段又不是联合索引的最左字段，那么就会导致索引失效。

实际上在Mysql中的索引检索是遵循最左匹配原则的，同时B+索引树的叶子节点的有序性也是建立都在最左匹配原则之上，而上述的4、5两种情况实际违反了最左匹配原则，因此Mysql执行器则无法使用对应的索引进行检查查询。

• 6、or分割的条件，如果or左边的条件存在索引，而右边的条件没有索引，不走索引

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

• 7、in、not in可能会导致索引失效

这里需要说明的是使用in以及not in走不走索引，实际和Mysql的版本以及表中的数据量有关系，在8.0之后的版本是走索引的。

总结

本文根据面试官的存在索引但是出现数据查询慢的问题为出发点，总结了几种索引失效的场景，希望在大家平时项目开发时遇到类似的问题可以有对应的问题排查方向。导致索引失效的场景归结起来实际就是在索引使用上面存在瑕疵最终导致了索引失效的情况，这就像我们小时候打拳皇97一样，遥感和按钮的组合如果姿势不对，就没办法放出我们希望的大招。小枫的求职面试之路还在继续，我们一起期待下次小枫还会遇到怎样的面试问题吧。