一次线上SQL查询的索引失效问题排查过程，阿里P7看了也直呼内行

程序员文章站 2024-03-17 20:21:34

...

前言

索引是MySQL中提高查询的有效手段，但是索引失效的问题就让开发同学们头大！这不少侠前段时间就亲身经历了这一让人掉发的问题。

业务场景

业务场景也不复杂，就是有两张表：用户表和用户积分表，现在想通过连接查询查出某一用户详细信息。话不多说，先给大家展示一下表结构：
一次线上SQL查询的索引失效问题排查过程，阿里P7看了也直呼内行
可以看到，两张表的结构十分简单，而查询语句也仅仅是个平淡无奇的连接查询：

select * from cps_user_info ui inner join cps_user_score us on ui.user_id=us.user_id where us.id=1;

问题排查

建立索引有个原则是：连接查询的连接字段必须要有索引，这里没问题，并且主键也有索引。一切看上去都是那么的合规合法。但就是这条合法的SQL线上查询的时间将近8秒的开销（cps_user_score数据量数百万）！于是你着急忙慌的打开执行计划：

explain select * from cps_user_info ui inner join cps_user_score us on ui.user_id=us.user_id where us.id=1;

一次线上SQL查询的索引失效问题排查过程，阿里P7看了也直呼内行
看看上图执行计划结果中cps_user_info表的type字段：ALL，糟糕，是心动的感觉，哦不，是心跳的感觉！怎么回事，于是你立马回头查看索引的创建，索引失效的条件，但是好像一切都很正常，难道是MySQL出了问题？这位同志，请保持理智。
一次线上SQL查询的索引失效问题排查过程，阿里P7看了也直呼内行

于是我们做了尝试，将内连接查询换成左连接、右连接，然而并没有什么用。这个时候少侠有点慌了，情急之下想到查询表创建的语句，于是又是一顿操作猛如虎，一看战绩零比五。
一次线上SQL查询的索引失效问题排查过程，阿里P7看了也直呼内行
但是这个建表语句看着也是正常啊，这索引的创建一看就是出自大师之手！嗯？等等，这里好像有一丢丢不一样，两张表的字符集CHARSET不同。难道是这个原因，抱着疑问，少侠修改了表的字符集（该操作线上环境慎重操作，万一出了问题可别大骂少侠这个渣男欺骗读者）。
修改完字符集之后再查询一次执行计划：
一次线上SQL查询的索引失效问题排查过程，阿里P7看了也直呼内行
看看此时的cps_user_info的type是ref，显然这个时候走了索引。索引显然是两张表的字符集不同的原因导致的！那么深层次的原因是啥呢？其实深究原因的话，这个就是避免索引失效的一个重要原则：索引列不要参与计算！字符集不同的话，两张表连接查询，列的值必然是有转换计算的过程的。

扩展

其实如果知道explain extended+show warnings 组合命令的话，也是有助于快速定位问题的，将表的字符集改回utf8再通过组合命令查看：

mysql> explain extended select * from cps_user_info ui inner join cps_user_score us on ui.user_id=us.user_id where us.id=1;
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | us    | NULL       | const | PRIMARY,idx_uid | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | ui    | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                             |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                                                                                                                                                                   |
| Note    | 1003 | /* select#1 */ select `studying`.`ui`.`id` AS `id`,`studying`.`ui`.`user_id` AS `user_id`,`studying`.`ui`.`user_name` AS `user_name`,'1' AS `id`,'001' AS `user_id`,'6.80' AS `score` from `studying`.`cps_user_info` `ui` join `studying`.`cps_user_score` `us` where ((convert(`studying`.`ui`.`user_id` using utf8mb4) = '001')) |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

注意看convert(studying.ui.user_id using utf8mb4)这部分，显然这里是参与了运算的，这势必导致索引失效！

思考

其实本文涉及的索引失效仅仅就是索引列参与了运算导致的，但并不是显式的数学计算，实际上在开发中也会遇到各种各样的场景，深入理论并能识别理论在不同场景中的实际应用至关重要，谨以此与诸君共勉！
最后附上SQL创建的语句，以供感兴趣的读者实践。

CREATE TABLE `cps_user_info`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid`(`user_id`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  CREATE TABLE `cps_user_score`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) NOT NULL,
  `score` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid`(`user_id`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;```