经常有同学问我，我的一条SQL语句使用了索引，为什么还是进入慢查询？今天我们就从这个问题出发，来聊一聊索引和慢查询。另外插一句题外话，个人认为团队应该合理使用ORM，可以参考ORM的权衡取舍。合理利用ORM在面向对象和写操作上的优势，避免联合查询可能出现的陷阱（当然，如果你的Linq查询能力强，另当别论），因为ORM屏蔽了太多DB的底层知识内容，对程序员来说不是什么好东西，对性能有着极致的追求，但是对ORM理解不深的团队还是要多加小心。案例分析 言归正传，为了实验，我创建了下表：CREATETABLE`T`(`id`int(11)NOTNULL,`a`int(11)DEFAUTNULL,PRIMARYKEY(`id`),KEY`a`(`a`))ENGINE=InnoDB;该表有3个字段，id是主键索引，a是普通索引。SQL首先判断一条语句是否为慢查询语句，通过语句的执行时间来判断。他将语句执行时间与系统参数long_query_time进行比较。如果语句执行时间比它长，则该语句将被记录在慢查询日志中。此参数的默认值为10秒。当然，在生产中，我们不会设置这么大的值。一般我们会设置为1秒。对于一些敏感的业务，我们可能会设置一个小于1秒的值。语句执行过程中是否使用了表的索引，通过explain语句的输出结果可以看出KEY的值不为NULL。让我们看看explainselect*fromt;的KEY结果。isNULL （图1）explainselect*fromtwhereid=2的KEY结果；是PRIMARY，也就是我们常说的使用主键索引 （图2）explainselectafromt的KEY结果；是a，表示使用索引a。 （图3）虽然最后两次查询的KEYs不为NULL，但是最后一次其实是扫描了整棵索引树a。假设这张表的数据量有100万行，图2的语句还是可以很快执行的，但是图3的语句肯定很慢。如果是比较极端的情况，比如这个数据库的CPU压力很大，那么第二条语句的执行时间可能会超过long_query_time，就会进入慢查询日志。所以我们可以得出一个结论：是否使用索引和是否进入慢查询没有必然联系。索引的使用只代表一条SQL语句的执行过程，是否进入慢查询是由它的执行时间决定的，而这个执行时间可能会受到各种外界因素的影响。换句话说，你的语句在使用索引时可能仍然很慢。全索引扫描不足如果我们更深层次地看这个问题，其实还有一个隐藏的问题需要弄清楚，那就是什么叫使用索引。我们都知道InnoDB是一个索引组织表，所有的数据都存储在索引树上。比如上面的表t，这个表包含两个索引，一个主键索引和一个普通索引。在InnoDB中，数据放在主键索引中。如图：可以看到数据是放在主键索引上的。从逻辑上讲，所有对InnoDB表的查询都至少使用一个索引，那么现在我问你一个问题，如果你执行selectfromtwhereid>0，你认为这条语句对索引有用吗？让我们看看上面语句的explain输出显示PRIMARY。其实从数据上就知道，这个语句肯定是全扫描过的。但是优化器认为在执行这条语句的过程中，需要根据主键索引定位到第一个满足ID>0的值，同时也使用了索引。所以即使explain结果中写的KEY不为NULL，实际上也可能是全表扫描，所以InnoDB中只有一种情况叫做不使用索引，即从主键最左边的叶子节点开始索引并向右扫描整个索引树。也就是说，不使用索引并不是一个准确的描述。可以使用全表扫描来表示一个查询遍历整个主键索引树；您还可以使用全索引扫描来说明查询，例如selectafromt;他扫描了整个普通索引树；而select*fromtwhereid=2这样的语句就是我们通常所说的使用索引。他的意思是我们利用索引的快速查找功能，有效减少扫描行数。索引的过滤性必须足够好。根据上面的分析，我们知道全索引扫描会减慢查询速度。接下来，我们将讨论索引的可过滤性。假设你现在维护一张表，记录了中国14亿人的基本信息，现在你想找出所有10-15岁的人的姓名和基本信息，那么你的语句会这样写，从年龄在10到15岁之间的t_people中选择*。可以看到这条语句必须开始在age字段上建索引，否则就是全扫描，但是你会发现建好索引之后，这条语句的执行速度还是很慢，因为可能有超过1亿条数据满足这个条件就OK了。我们看一下建立索引后表的组织结构图：这条语句的执行流程是这样的：从索引中使用树查找，得到age等于10的第一条记录，得到value它的主键id，根据id的值，去主键索引中获取整行的信息，作为结果集的一部分返回；在索引age上右扫描，获取下一个id的值，到主键索引处获取整行的信息，作为结果集的一部分返回；重复上述步骤，直到遇到第一个年龄大于15的记录；看这条语句，虽然用了索引，但是扫描了1亿多行。所以你现在知道了，当我们在讨论是否使用索引时，我们关心的是扫描的行数。对于大表，不仅要有索引，而且索引的过滤性要足够好。像刚才例子中的age，它的过滤性不够好。在设计表结构的时候，我们需要让所有的过滤性都足够好，也就是区分度足够高。回表成本好，过滤性好。是不是说查询的扫描行数一定要少？再看一个例子：如果你的执行语句是select*fromt_peoplewherename='张三'andage=8t_people表上有一个索引是name和age的联合索引，那么这个的过滤性能联合指数应该不错。你可以很快在联合索引上找到第一个孩子，名字叫张三，年龄是8岁。当然这样的孩子应该不多，所以向右扫描的行数很少，查询效率很高。但是，查询的可过滤性和索引的可过滤性不一定相同。如果你的要求是找出所有首字为张且年龄为8岁的孩子，你会怎么写你的陈述？？你怎么写你的句子？显然你会这样写：select*fromt_peoplewherenamelike'Zhang%'andage=8;在MySQL5.5及之前的版本中，这条语句的执行流程是这样的：首先从联合索引中找到第一个age字段就是张开头的记录，取出主键id，然后去主键索引树，根据id取出整行的值；判断age字段是否等于8，如果是，则作为结果集中的一行返回，否则丢弃。在联合索引上向右遍历，重复回表判断的逻辑，直到遇到联合索引树上名字首字符不是张的记录。我们把根据id在主键索引上查找整行数据的动作称为回表。可以看到在这个执行过程中，最耗时的一步就是归表了。假设全国姓张的人有8000万，那么这个过程会回表8000万次，记录在定位第一行的时候，只有索引和联合索引的最左边前缀可以被使用，这被称为最左前缀原则。可以看到这个执行过程，返回表很多次，性能不够好，请问有什么办法可以优化吗？MySQL5.6版本引入了索引条件下推的优化。我们来看看这个优化的执行过程：首先从联合索引树中，找到第一个age字段以Zhang开头的记录，判断这条索引记录中的age值是否为8，如果是，则返回到表中，取出整行数据作为结果集的一部分返回，不是则丢弃；在联合索引树上，向右遍历，判断age字段后，按需返回表，直到遇到联合索引树上名字的第一个字符不是张某的记录；这个过程和上面的区别在于，在遍历联合索引的过程中，将年龄等于8的条件推送到所有遍历过程中，减少了回表的次数，假设全国name是第一个字是张的人中，有100万个8岁的孩子，所以在这个查询过程中，联合索引需要遍历8000万次，返回到只需要100万次桌子。这个优化的效果从virtualcolumn可以看出还是很不错的，但是这个优化还是没有绕过最左前缀原则的限制，所以联合索引还是要扫描8000万行，有没有更进一步的优化方法？我们可以考虑用名字和年龄的第一个词做一个联合索引。这可以通过使用MySQL5.7引入的虚拟列来实现。对应修改表结构的SQL语句：altertablet_peopleaddname_firstvarchar(2)generated(left(name,1)),addindex(name_first,age);我们看看这条SQL语句的执行效果：CREATETABLE`t_people`(`id`int(11)DEFAULTNULL,`name`varchar(20)DEFAUTNULL,`name_first`varchar(2)GENERATEDALWAYSAS(left(`name`,1))VIRTUAL,KEY`name_first`(`name_first`,'age'))ENGINE=InnoDBDEFAULTCHARSET=utf8;他首先在people上创建一个名为name_first的虚拟列，然后在name_first和age上创建一个联合索引，并让这个虚拟列的值永远等于name字段的前两个字节，这个虚拟列不能指定值插入数据时，更新时不能主动修改。它的值会根据定义自动生成，修改name字段时也会自动修改。有了这个新的联合索引，当我们要找第一个字符是Zhang，年龄为8岁的孩子时，这条SQL语句可以这样写：select*fromt_peoplewherename_first='Zhang'andage=8。在这个way，这条语句的执行过程只需要扫描联合索引的100万行，回表100万次。这种优化的本质是我们创建了一个更紧凑的索引来加速查询过程。总结本文向您介绍了索引的基本结构和查询优化的一些基本思路。现在你知道使用索引的语句也可能是一个慢查询。我们查询优化的过程往往就是减少扫描行数的过程。慢查询可以归纳为以下几种情况：全表扫描全索引扫描索引过滤性能不好考虑频繁返回表的开销假设业务需求是统计10-15岁的14亿人口，这不能提高过滤系数，怎么办？(select*fromt_peoplewhereagebetween10and15)假设统计必须是OLTP，如何解决实时显示统计的问题？
