【.com原创文章】小李今天一上班就收到客户的反馈，说查询用户信息会很慢，有时甚至会超时。图片来自Pexels小李，所以想知道为什么给表加索引后这么慢。小李半天也没有分析原因，只能找了同系的扫地僧大林子。大麟子一边看着项目，一边听着小李的描述。就在小李描述完问题后，大麟子对小李说道：“问题解决了。”小李一惊，问道：“所以6，是什么？”是什么原因？明明我已经加了索引了？”大林子说：“这是很多开发者容易忽略的问题……”听了大林子的解释，小李顿时豁然开朗。那么具体是什么原因呢，让我下面给大家解释一下原因解释首先，我们先创建一张以InnoDB为存储引擎的User表，这张表包含三个字段：id，name，age，其中id在主键name上增加了一个名为n的普通索引，以及那么我们往这张表中插入10亿条数据，表和数据都创建好了，接下来说说为什么加了索引之后查询还是慢，以及解决方法，MySQL会判断SQL语句是不是慢根据语句的执行时间来查询语句，当一条SQL语句正在执行时，MySQL会将语句执行时间与系统参数long_query_time进行比较（该参数默认值为10秒，但在实际项目中我们会设置该参数er值到1秒甚至更短）。如果执行时间大于该参数的值，则该语句被记录在慢查询日志中。那么我们如何知道语句执行过程中是否使用了索引呢？这时候我们可以使用explain语句来查看数据结果中Key的值是否为null。如果为null，则表示未使用该索引。我们来看一个例子：explainselect*fromuser;explainselect*fromuserwhereid=1;explainselectnamefromuser;上面三个explain语句返回的key如下表所示：从上表可以看出，第一条语句没有使用一个索引，第二条使用主键索引，第三条语句使用n索引。我们的用户表有10亿条数据。可以想象，第一条查询语句的执行效率一定很低，而第二条查询语句似乎执行效率很高。事实上，查询在极端环境下（如CPU负载高）也会出现效率低下的问题。虽然最后一条查询语句使用了n索引，但实际上是扫描了整棵索引树，所以查询效率并没有高多少。综上所述，我们可以看出，是否使用索引和慢查询是否记录几乎没有关系。索引只是SQL的一个执行过程，SQL的执行时间是判断是否记录在慢查询中的关键。在上一节中，我们只是简单地分析了这个问题。让我们仔细看看这个问题。我们知道InnoDB是一个索引组织表，所有的数据都存储在索引树上。在InnoDB中，数据是放在主键索引中的，所以理论上所有对InnoDB表的查询都至少使用一个索引。比如SQL查询语句select*fromuserwhereid>1000，显然使用了主键索引，而这条语句必须扫描整棵索引树。InnoDB中只有一种情况叫不使用索引，就是从主键索引最左边的叶子节点开始向右扫描整棵索引树。至此我们知道了全索引扫描会导致查询速度变慢，下面说说另一个知识点过滤。如果我们要查询user表中70岁以上和80岁以下的人的信息，必须在age字段上加索引，避免全局扫描。是的，这是个好主意，但是当你运行查询语句时，你会发现它仍然执行得很慢。为什么？要回答这个问题，我们先来看看SQL查询语句的执行流程：搜索ageindextree，得到第一条age为70的记录得到主键值，去主键索引树中获取对应的信息根据主键值，将信息添加到结果集中。在age索引树上向右扫描，得到下一个主键值，进行第二次操作。继续执行以上步骤，直到遇到第一个age大于80的记录。从上面的步骤我们可以看出，虽然使用了索引，但是在查询过程中扫描了数万甚至数亿行。因此，我们可以得出结论，对于这样一个数据量很大的表，我们要做的不仅仅是加索引，还要保证索引的过滤性足够好。如果我们也处理了索引的可过滤性，是否会减少查询中扫描的行数？答案是不。比如我们user表中的name和age字段由联合索引就很好的处理了可过滤性。这个时候我们查询李姓，年龄60岁的数据，查询效率还是很低的。我们看一下查询语句的执行过程：首先从联合索引中找到name字段以li开头的数据记录。获取主键值，根据主键值去除主键索引簿中匹配的数据。然后根据age字段判断年龄是否等于60，如果是，则加入到结果集中。然后结合以上所有向右遍历，继续回表判断，直到名字的第一个字符不是李氏。提示：所谓回表，就是根据主键值在主键索引树上查找对应的数据。从上面的步骤我们可以看出，最耗时的是回表。如果李姓数据有2亿条，那么需要回表2亿次，SQL在定位第一行数据时只能使用最左边的前缀。原则。这个耗时的返表操作步骤在MySQL5.6及以后版本针对索引条件下推进行了优化。优化的过程很简单：首先从联合索引中找到name字段以Li开头的数据记录，判断这条记录中的age是否为60，如果是则返回表检索数据作为结果放。重复步骤1，直到匹配到首字符不是“李”的记录。优化后和优化前的区别在于年龄比较的步骤放在了遍历联合索引树上，减少了回表的次数。但是虽然回表次数减少了，但是联合索引树的遍历并没有减少，仍然需要遍历2亿次。有没有更好的优化方案？答案是肯定的，我们可以使用虚拟列进行处理。首先，我们需要对name和age的第一个单词做一个联合索引，让虚拟列的值永远等于name字段的前两个字节。这里需要注意的是，virtualcolumn不会随着insert和update而改变，它的Values是自定义生成的。语句如下：altertableuseraddname_firstvarchar(2)generated(left(name,1)),addindex(name_first,age);经过上述优化后，联合索引树的查询次数也减少了，本质上是创建了一个紧凑的索引来加快查询速度。小结本文主要介绍查询优化的基本思想。只要记住优化查询的过程就是减少扫描行数的过程，就能在SQL查询面前取得胜利。作者：朱刚，笔名苗大叔简介：.NET高级开发人员，2019年度博客之星Top20之一，长期从事电子政务系统和AI客服系统的设计与开发，目前在职为国内某大型BIM公司从事招投标软件开发。编辑：陶佳龙征稿：如有意向投稿或寻求报道，请联系editor@51cto.com【原创稿件请注明原作者和出处为.com，合作网站转载】
