记一次生产慢sql索引优化及思考

素颜马尾好姑娘i 2024-05-23 22:17 54阅读 0赞

## 记一次生产慢sql索引优化及思考 ##

### 问题重现 ###

*  夜黑风高的某一晚，突然收到一条运营后台数据库慢sql的报警，耗时竟然达到了60s。
 *  ![img][]
 *  看了一下，还好不是很频繁，内心会更加从容排查问题，应该是特定条件下没有走到索引导致，如果频繁出现慢查询，可能会将数据库连接池打满，导致数据库不可用，从而导致应用不可用。

### 问题排查 ###

*  报警自带定位慢sql语句，这个是很早就上线的一条sql语句，下面对sql语句进行了简化：
 *  select \* from xxx where gear\_id=xxx and examine=xxx order by id desc limit 10，这是个简单的根据流量池gear\_id查询，按照主键id倒序进行分页查询10条数据的语句。
 *  在examine=2时查询速度很快，但是在examine=3时，查询速度极慢，然后分别在不同的examine下查看执行计划，得到的执行计划都是一致的
 *  ![img][img 1]
 *  查看执行计划，发现possible\_keys中有idx\_gear\_id索引，但是实际用到的key却是PRIMARY,并且extra中明确用了where条件进行数据过滤。到现在就明白了这个sql是在主键聚簇索引上进行扫描，然后用where语句条件进行过滤，时间耗费在这了。
 *  这个也解释了为什么examine在不同状态下的耗时不一样，取决于where过滤扫描的行数，扫描行数越多，执行越慢，但同一个问题是都没走到我们已有的索引idx\_gear\_id。
 *  当单表数量较小时，无论有没有索引，或者走主键索引扫描或者普通索引都很快，很容易忽略这些问题，此时的表现就是你好，我好，大家好，然后随着数据量的增大，当达到千万级别或者亿级时，慢查询的问题就凸显出来了。

### 原理剖析 ###

*  为什么mysql会选择这个不合适的主键聚簇索引?
 *  以常用的InnoDb存储引擎为例，看一下聚簇索引和非聚簇索引查询区别:
 *  聚簇索引：通常就是按照每张表的主键构造一颗B+树，叶子节点中存放的就是整张表的行记录数据，即数据和主键都在索引上
 *  非聚簇索引：表的二级索引字段（比如唯一索引，联合索引等）构造的一颗B+树，叶子节点存储的是Key字段+主键值，即非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据索引的指针。
 *  聚簇索引查询原理：
 *  ![img][img 2]
 *  非聚簇索引查询原理（二级索引查询）：
 *  ![img][img 3]
 *  由以上的索引数据结构可以看出，因为聚簇索引将索引和数据保存在同一个B+树中，因此通常从聚簇索引中获取数据比非聚簇索引更快，而非聚簇索引在获取到叶子节点的主键后，需要再次查询主键索引，即回表查询行记录数据。当然如果查询的列只是索引字段，比如查询姓名和年龄，可以创建联合索引，即索引存储的内容即为需要查询的内容，这种查询速度往往比主键索引更快，这种索引查询又称为覆盖索引。

#### 什么是回表？ ####

*  将以上的索引数据映射成常见的用户表user的索引为例,上面的聚簇索引就是以id字段为主键的索引,name字段为非聚簇索引，还有age等其他表字段是非索引字段，示例sql：select \* from user where id = 1; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 id聚簇索引这棵 B+ 树，就可以查到对应的数据。
 *  但当我们使用非聚簇索引 name 这个索引来查询 name = b 的记录时就要用到回表。原因是通过 name 这个二级索引查询方式，则需要先搜索 name 索引树，然后得到主键 id，即PK的值为 1，再到主键id聚簇索引树再搜索一次。这种根据二级索引查询到主键id,再根据主键id查询主键聚簇索引的过程就称为回表。
 *  回到为什么mysql会选择这个不合适的主键聚簇索引问题本身，mysql执行器认为使用二级索引查出来的数据太多了，还需要基于磁盘做临时存储进行排序，然后排序取出10条，然后进行回表查询字段，性能可能会很差，所以采用了直接采用了按顺序扫描主键聚簇索引，和where条件gear\_id=xxx and examine=xxx进行对比，最多放10条即可，这种情况就是数量小的时候没问题，但是当数据量大的时候，就需要一直扫描所有的数据，直到查到符合where条件的10条数据为止，同时耗时也急剧增长。

### 解决问题 ###

*  为了快速解决问题，可以采用强制索引force index，即在写sql语句时指定使用具体的索引
 *  sql示例 ：select \* from xxx force index (idx\_gear\_id) where gear\_id=xxx and examine=3 order by id desc limit 10,强制使用idx\_gear\_id这个索引。
 *  以下为使用强制索引的执行计划：
 *  ![img][img 4]
 *  可以看到实际使用的索引key就是idx\_gear\_id,执行耗时在几百毫秒，运营后台的业务人员完全可接受。

### 长期优化 ###

*  由于表的数据越来越多，查询条件错综复杂，还有用json字段查询问题，决定将数据异构到es查询，将json字段打平，es天然支持复杂的查询条件，查询响应更快。
 *  es数据同步方案：
 *  ![img][img 5]
 *  在ES数据同步链路中，通过京东科技中间件DTS监听数据库的binlog，将索引字段（查询条件字段）及业务唯一id写入ES。
 *  在业务运营查询时，根据复杂的查询条件，先去ES查询，将业务唯一id查出，再根据业务唯一id去DB中查询业务明细数据，同时解决了业务查询的复杂性和查询性能。
    
    转载自：https://juejin.cn/post/7313242116770709530

[img]: https://img-blog.csdnimg.cn/img_convert/262fc46edb10cc57c16b822f8a2af137.webp?x-oss-process=image/format,png
[img 1]: https://img-blog.csdnimg.cn/img_convert/208fc20788392e4b0b9da1a926033262.webp?x-oss-process=image/format,png
[img 2]: https://img-blog.csdnimg.cn/img_convert/e7259adc52bdc6315422cd5c019ffd28.webp?x-oss-process=image/format,png
[img 3]: https://img-blog.csdnimg.cn/img_convert/fe9ec81992f9f19dd973eaa513539bfd.webp?x-oss-process=image/format,png
[img 4]: https://img-blog.csdnimg.cn/img_convert/b9d157f8a7e843a5e43b71d2866ba8dd.webp?x-oss-process=image/format,png
[img 5]: https://img-blog.csdnimg.cn/img_convert/f330c65e9f3a3b90474b594370145247.webp?x-oss-process=image/format,png