聊聊慢SQL中关于IN语法的优化分析过程。技术人人都可以磨炼，但处理问题的思路和角度各有不同，希望这篇文章可以抛砖引玉。经常有开发小哥问我，我写SQL语句使用了索引，为什么还是会进入到慢查询之中呢？面对这种情况自然免不了一顿解释，今天我们就从这个问题开始来聊一聊索引和慢查询。
在很多应用场景中，SQL 的性能直接决定了系统的性能。本文介绍一些通过调整 SQL 语句就能优化SQL的通用小技巧，优化 SQL 的方法不能解决所有的性能问题，但是却能处理很多因 SQL 写法不合理而产生的性能问题。- 追溯SQL执行历史数据，分析关键指标在SQL多次执行的波动,这些关键指标可以用来做为SQL健康度参考指标。
- 用实际数据来验证推断，排除掉其它干扰因素，定位SQL慢的根本原因，帮助快速修复。
CREATE TABLE `test`(
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `a`(`a`)
) ENGINE=InnoDB;
判断一个SQL语句是不是慢查询语句，用的是语句的执行时间。慢查询日志可以采集执行时间超过long_query_time这个系统参数的语句，这个参数的默认值是10秒。当然在实际环境中这个参数不会设置这么大，一般会设置1秒，对于一些比较敏感的业务，可能会设置一个比1秒还小的值。语句执行过程中有没有用到表的索引，可以通过explain看SQL的具体执行计划是什么。explain select * from test where id=1000;
KEY结果是PRIMARY，就是我们常说的使用了主键索引。explain select a from test;
explain select * from test;
虽然后三个查询都走了索引，但是例2扫描了整个索引树a，例3不但扫描了整个索引树a，还产生了回表查询。假设这个表的数据量有1000万行，例1语句依然执行很快，但是例2和例3就会相对变慢了。如果遇到比较极端的情况，比如数据库服务器上CPU压力非常的高，那么可能语句的执行时间会超过long_query_time，就会进入到慢查询日志里面。这就是之前所说的：是否使用索引和是否进入慢查询之间并没有必然的联系。2、全索引扫描的不足
如果我们在更深层次的看这个问题，其实他还潜藏了一个问题需要澄清，就是什么叫做使用了索引。我们都知道，InnoDB是索引组织表，所有的数据都是存储在索引树上面的。比如上面的表test，这个表包含了两个索引，一个主键索引和一个普通索引。在InnoDB里，数据是放在主键索引里的。如果从逻辑上说，所有的InnoDB表上的查询，都至少用了一个索引。所以现在我问你一个问题，如果你执行select * from test where id>0，你觉得这个语句有用上索引吗？我们看上面这个语句的explain的输出结果显示的是PRIMARY。其实从数据上你是知道的，这个语句一定是做了全面扫描。但是优化器认为，这个语句的执行过程中，需要根据主键索引，定位到第1个满足ID>0的值，也算用到了索引。所以即使explain的结果里写的KEY不是NULL，实际上也可能是全表扫描的，因此InnoDB里面只有一种情况叫做没有使用索引，那就是从主键索引的最左边的叶节点开始，向右扫描整个索引树。3、索引的过滤性要足够好
根据以上分析，我们知道全索引扫描会让查询变慢，接下来就要来谈谈索引的过滤性。
假设你现在维护了一个表，这个表存储了一亿条数据，你想查询符合某个条件的数据，假设符合条件的数据有一千万，那么即使在字段上建立索引了，这个语句还是执行慢（不建索引更慢）。你看这个语句，虽然他用了索引，但是他扫描超过了一千万行。所以你现在知道了，当我们在讨论有没有使用索引的时候，其实我们关心的是扫描行数。对于一个大表，不止要有索引，索引的过滤性还要足够好。像刚才这个例子，它的过滤性就不够好，在设计表结构的时候，我们要让所有的过滤性足够好，也就是区分度足够高。4、回表的代价
那么过滤性好了，是不是表示查询的扫描行数就一定少呢？
现在有一张表，记录了中国14亿人的基本信息，有一个索引是姓名和年龄的组合索引。如果现在你的需求是查出所有名字的第1个字是张，并且年龄是8岁的所有小朋友，你的语句会怎么写呢？select * from people where name like '张%' and age=8;
在MySQL5.5和之前的版本中，这个语句的执行流程是这样的:我们把根据id到主键索引上查找整行数据这个动作，称为回表。你可以看到这个执行过程里面，最耗费时间的步骤就是回表，假设全国名字第1个字是张的人有8000万，那么这个过程就要回表8000万次，在定位第一行记录的时候，只能使用索引和联合索引的最左前缀，最称为最左前缀原则。你可以看到这个执行过程，它的回表次数特别多，性能不够好，有没有优化的方法呢？在MySQL5.6版本，引入了index condition pushdown的优化。我们来看看这个优化的执行流程：这个过程跟上面的差别，是在遍历联合索引的过程中，将年龄等于8的条件下推到所有遍历的过程中，减少了回表的次数。假设全国名字第1个字是张的人里面，有100万个是8岁的小朋友，那么这个查询过程中在联合索引里要遍历8000万次，而回表只需要100万次。5、虚拟列
可以看到这个优化的效果还是很不错的，但是这个优化还是没有绕开最左前缀原则的限制，因此在联合索引你还是要扫描8000万行，那有没有更进一步的优化方法呢？我们可以考虑把名字的第一个字和年龄来做一个联合索引。这里可以使用MySQL5.7引入的虚拟列来实现。首先在表上上创建一个字段叫name_first的虚拟列，然后给name_first和age上创建一个联合索引，并且，让这个虚拟列的值总是等于name字段的前两个字节，虚拟列在插入数据的时候不能指定值，在更新的时候也不能主动修改，它的值会根据定义自动生成，在name字段修改的时候也会自动修改。有了这个新的联合索引，我们在找名字的第1个字是张，并且年龄为8的小朋友的时候，这个SQL语句就可以这么写：select * from t_people where name_first='张' and age=8。
这样这个语句的执行过程，就只需要扫描联合索引的100万行，并回表100万次，这个优化的本质是我们创建了一个更紧凑的索引，来加速了查询的过程。总结
本文给你介绍了索引的基本结构和一些查询优化的基本思路，你现在知道了，使用索引的语句也有可能是慢查询，我们的查询优化的过程，往往就是减少扫描行数的过程。文中虽然列举了几个要点，但其实优化的核心思想只有一个，那就是找出性能瓶颈所在，然后解决它。
