索引失效的十大杂症

背景：最近生产爆出一条慢sql，原因是用了or和!=，导致索引失效。于是，总结了索引失效的十大杂症

一、查询条件包含or，可能导致索引失效

新建一个user表，它有一个普通索引userId，结构如下：

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` int(11) NOT NULL,
      `age` int(11) NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userId` (`userId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 执行一条查询sql，它是会走索引的，如下图所示： image

2. 把or条件加上没有索引的age，并不会走索引，如图：

   image

分析&结论：

• 对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并
• 如果它一开始就走全表扫描，直接一遍扫描就完事。
• mysql是有优化器的，处于效率与成本考虑，遇到or条件，让索引失效，看起来也合情合理嘛。
  注意: 如果or条件的列都加了索引，索引可能会走的，大家可以自己试一试。

二、如何字段类型是字符串，where时一定用引号括起来，否则索引失效

假设demo表结构如下：

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` varchar(32) NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userId` (`userId`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId为字符串类型，是B+树的普通索引，如果查询条件传了一个数字过去，它是不走索引的，如图所示： image

如果给数字加上'',也就是传一个字符串呢，当然是走索引，如下图：

image

分析与结论：
为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

三、like通配符可能导致索引失效。

并不是用了like通配符，索引一定失效，而是like查询是以%开头，才会导致索引失效。
表结构：

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` varchar(32) NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userId` (`userId`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

like查询以%开头，索引失效，如图： image 把%放后面，发现索引还是正常走的，如下： image

把%加回来，改为只查索引的字段（覆盖索引），发现还是走索引，惊不惊喜，意不意外

image

结论：
like查询以%开头，会导致索引失效。可以有两种方式优化：

• 使用覆盖索引
• 把%放后面
  附: 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

四、联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

表结构：（有一个联合索引 idx_userid_age， userId在前， age在后）

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` int(11) NOT NULL,
      `age` int(11) DEFAULT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userid_age` (`userId`,`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

在联合索引中，查询条件满足最左匹配原则时，索引是正常生效的。请看demo：

image image

如果条件列不是联合索引中的第一个列，索引失效，如下：

image

分析与结论：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

五、在索引列上使用mysql的内置函数，索引失效。

表结构：

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` varchar(32) NOT NULL,
      `loginTime` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userId` (`userId`) USING BTREE,
      KEY `idx_login_time` (`loginTime`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然loginTime加了索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接GG，如图： image

六、对索引列运算（如，+、-、*、/），索引失效。

表结构：

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` varchar(32) NOT NULL,
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然age加了索引，但是因为它进行运算，索引直接迷路了。。。山重水复疑无路，算着算着脑瓜疼，索引就真的不认识路了。如图：

image

七、（where条件的）索引字段上使用（!= 或者 <>，not in）时，可能会导致索引失效。

表结构：

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` int(11) NOT NULL,
      `age` int(11) DEFAULT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然age加了索引，但是使用了!= 或者 <>，not in这些时，索引如同虚设。如下：

image image

八、索引字段上使用is null， is not null，可能导致索引失效。

表结构:

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `card` varchar(255) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`) USING BTREE,
      KEY `idx_card` (`card`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

单个name字段加上索引，并查询name为非空的语句，其实会走索引的，如下:

image 单个card字段加上索引，并查询name为非空的语句，其实也会走索引的，如下: image

但是它们用or连接起来，索引就失效了，如下：

image

九、左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

新建两个表，一个user，一个user_job

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

    CREATE TABLE `user_job` (
      `id` int(11) NOT NULL,
      `userId` int(11) NOT NULL,
      `job` varchar(255) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user 表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8。

image image

执行左外连接查询,user_job表还是走全表扫描，如下：

image

如果把它们改为name字段编码一致，还是会一路高歌，雄赳赳，气昂昂，走向索引。

image

十、mysql估计使用全表扫描要比使用索引快,则不使用索引。

• 当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

• 不要给'性别'等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。

Mysql出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快。这跟它的优化器有关，来看一下它的逻辑架构图吧（图片来源网上）

image

总结

总结了索引失效的十大杂症，在这里来个首尾呼应吧，分析一下我们生产的那条慢sql。模拟的表结构与肇事sql如下：

    CREATE TABLE `user_session` (
      `user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
      `device_id` varchar(64) NOT NULL,
      `status` varchar(2) NOT NULL,
      `create_time` datetime NOT NULL,
      `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`user_id`,`device_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    explain
    update user_session set status =1
    where  (`user_id` = '1' and `device_id`!='2')
    or (`user_id` != '1' and `device_id`='2')

分析：

• 执行的sql，使用了 or条件，因为组合主键( user_id, device_id)，看起来像是每一列都加了索引，索引会生效。
• 但是出现 !=,可能导致索引失效。也就是 or+ !=两大综合症，导致了慢更新sql。

解决方案:
那么，怎么解决呢？我们是把 or条件拆掉，分成两条执行。同时给 device_id加一个普通索引。

最后，总结了索引失效的十大杂症，希望今后在工作学习中，参考这十大杂症，多点结合执行计划 expain和场景，具体分析，而不是按部就班，墨守成规，认定哪个情景一定索引失效等等。

---

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
  　　select id from t where num/2=100
  　　应改为:
  　　select id from t where num=100*2
• 尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
• 并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引，如一表中有字段
  　　sex：male,female几乎个一半，那么即使在sex上建立了索引也对查询效率起不了作用。
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
  　　因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，
  　　若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
  　　这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
• mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
  　　 因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
• order by 索引 ，不起作用的问题（除了主键索引之外）：
  　　1、 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；
  　　2、如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！

二、四种索引
PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。就是 唯一 且 不能为空。
INDEX 索引，普通的
UNIQUE 唯一索引。不允许有重复。
FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。
三、常用SQL优化：
1.优化group by 语句
默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序
2.有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
3.如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引
select * from 表名 where 条件1=‘’ or 条件2=‘tt’