欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

一次线上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;```