MySQL：亿级数据量商品系统的SQL调优实战

下面的这个商品系统让用户根据品类筛选1商品的SQL语句：

select * from products where category='xx' and sub_category='xx' order by id desc limit xx,xx

在一个亿级数据库的商品表里执行，需要耗时几十秒，结果导致了数据库的连接资源全部打满，商品系统无法运行，出于崩溃状态。

现在我们来分析一下，到底为什么会出现这么一个情况。首先，这个表肯定是对经常用到的查询字段都建立好了索引，也就是说，KEY index_category(catetory,sub_category)肯定是存在的，所以基本可以确认上面的SQL绝对是可以用上索引的。

因为如果你一旦用上了品类的那个索引，那么按照品类和子类去索引里筛选，筛选很快速，而且筛选出来的数据时不多的，按说这个语句应该执行速度是很快的，即使表里有亿级数据，但是指向时间应该最多也不超过1s。

但是现在这个SQL语句跑了几十秒，那说明它肯定没用到我们建立的那个索引，所以才这么慢。因此我们先看下它是怎么执行的：

explain select * from products where category='xx' and sub_category='xx' order by id desc limit xx,xx

具体的执行内容就不写了。关键在于它的的possible_keys里是有我们的index_category的，结果实际用的key不是这个索引，而是PRIMARY！！而且Extra里清晰写了Using where。

也就是说它本质上就是在主键的聚簇索引上进行扫描，一边扫描，一边还用了where条件里的两个字段去筛选，所以这么扫描的话，肯定很慢。

因此此时为了快速解决这个文艺，就需要强制性的改变MySQL自动选择这个不合适的聚簇索引进行扫描的行为：

select * from products force index(index_category) where category='xx' and sub_category='xx' order by id desc limit xx,xx

这样问题就解决了。

如果MySQL使用了错误的执行计划，就可以用force index语句就可以纠正了

但是这个案例还没完，这里还遗留了很多的问题，比如：

• 为什么在这个案例中MySQL默认会选择对主键的聚簇索引进行扫描？
• 为什么没有使用index_category这个二级索引进行扫描？
• 即使使用了聚簇索引，为什么这个SQL以前没有问题，现在突然由问题了？

第一个问题：为什么针对select * from products where category=‘xx’ and sub_category=‘xx’ order by id desc limit xx,xx这样一个SQL语句，MySQL要选择对聚簇索引进行扫描呢？

• 这是因为这个表示一个亿级数据量的大表，那么对于它来说，index_category这个二级索引也是比较大的
• 所以对于MySQL来说，它有这么一个判断，它觉得如果要是从index_category二级索引里查找到符合where条件的一波数据，还需要回表，回到聚簇索引里去。
• 因为SQL语句是要select * 的，所以这里必然涉及到一次回表操作，回到聚簇索引里吧所有字段的数据都查出来，但是在查表之前，它必然要做完order by id desc limit xx，xx这个操作。
• 举个例子，比如他根据where category=‘xx’ and sub_category=‘xx’，从index_category二级索引里查找出了一大波数据。
• 比如从二级索引里假设找出了几万条数据，接着因为二级索引里是包含主键id的，所以此时它就得按照order by id desc这个排序语法，对这几万条数据基于临时磁盘文件进行filesort磁盘排序，排序完了之后，再按limit xx, xx语法，把指定位置的几条数据拿出来，假设limit 0, 10，那么就是把10条数据拿出来。
• 拿出来10条数据后，再回到聚簇索引里去根据id查找，把这10条数据的完全字段都查出来，这就是MySQL认为如果你使用index_category的话，可能会发生的一个情况。
• 所以它担心你根据where category=‘xx’ and sub_category=‘xx’，从index_category二级索引里查出来的数据太多了，还得在临时磁盘里排序，可能性能会很差，因此MySQL就把这种方式判定为一种不太好的方式。
• 因为它才会选择换一种方式，也就是直接扫描主键的聚簇索引。因为聚簇索引都是按照id值有序的，所以扫描的时候，直接按照order by id desc这个倒序顺序扫描过去就可以了。然后在顺序扫描的时候，就会对每一条数据都采用Using Where的方式，跟where category=‘xx’ and sub_category='xx’条件进行比对，符号条件的就直接放入结果集中去，最多就是放10条数据局可以返回了
• 此时MySQL认为，按顺序扫描聚簇索引，拿到10条符合where条件的数据，应该速度是很快的，很可能比使用index_category二级索引那个方案更快，因此此时他就采用了扫描聚簇索引的这种方式！

那接下来我们又要考虑一个问题了，那就是这个SQL语句，实际上之前在线上系统运行一直没什么问题，也就是说，之前在线上系统而言，即使采用扫描聚簇索引的方案，其实这个SQL语句也确实一般都运行不慢，最起码是不会超过1s的。那么为什么会在某一天晚上突然就大量报慢查询，耗时十几秒了呢？

• 这是因为之前where category=‘xx’ and sub_category='xx’这个条件都是有返回值的，就是说根据条件里的取值，扫描聚簇索引的时候，通常都是很快就能找到符号条件的值并返回，所以之前性能没有什么问题
• 但是后来可能是商品系统里的运营人员，在商品管理的时候加了几种商品分类和子类，但是这几种分类和字段的组合其实没有对应的商品。
• 所以在底层扫描聚簇索引的时候，扫来扫去都扫不到符号where条件的结果，一下子就把聚簇索引全部扫描了一遍，等于是把上亿数据都扫描了以便，都没找到符号where category=‘新分类’ and sub_category='新子类’这个条件的数据。
• 正是因为如此，才导致这个SQL语句频繁出现几十秒的慢查询，进而导致MySQL连接资源被打满，商品系统崩溃