【知识】MySQL索引原理及慢查询优化

1. 摘要

MySQL用来加快查询的技术很多，其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引，MYSQL必须从第一条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。但也不全是这样。本文讨论索引是什么以及如何使用索引来改善性能，以及索引可能降低性能的情况。

2.MySQL索引原理

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5 4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

various-system-software-hardware-latencies

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b 树应运而生。

详解b 树

b 树

如上图，是一颗b 树，关于b 树的定义可以参见B 树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b 树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b 树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b 树性质

1.通过上面的分析，我们知道IO次数取决于b 数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m 1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b 树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.当b 树的数据项是复合的数据结构，比如(name,age,sex)的时候，b 数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b 树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b 树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b 树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

3. 索引分类和操作

索引的存储分类

索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引。

B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

Mysql目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符进行索引，这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序Order By和分组Group By 操作的时候无法使用。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。语法：create index idx_title on film (title(10))

MyISAM、InnoDB引擎、Memory三个常用引擎类型比较

索引	MyISAM引擎	InnoDB引擎	Memory引擎
B-Tree索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-Tree 索引	支持	不支持	不支持
Full-text 索引	不支持	暂不支持	不支持

B-TREE索引类型

普通索引 这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：（1）创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,...); （2）修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...); （3）创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

UNIQUE索引 表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique：（1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表); （2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表); （3）创建表时指定索引：CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );

主键：PRIMARY KEY索引 主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。（1）主键一般在创建表的时候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。（2）但是，我们也可以通过修改表的方式加入主键：“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。（主键相当于聚合索引，是查找最快的索引）注：不能用CREATE INDEX语句创建PRIMARY KEY索引

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY 其中，前两条语句是等价的，删除掉table_name中的索引index_name。第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

代码语言：javascript复制

mysql> show index from tblname;
mysql> show keys from tblname;

Table：表的名称 Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1 Key_name：索引的名称 Seq_in_index：索引中的列序列号，从1开始 Column_name：列名称 Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。 Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。 Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。 Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。 Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。 Comment：更多评注。

联合索引

联合索引的定义为（MySQL）：

代码语言：javascript复制

ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);

联合索引的优点： 若多个一条SQL，需要多个用到两个条件

代码语言：javascript复制

SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX';

当索引在检索 password字段的时候，数据量大大缩小，索引的命中率减小，增大了索引的效率。

符合索引的索引体积比单独索引的体积要小，而且只是一个索引树，相比单独列的索引要更加的节省时间复杂度和空间复杂度。

例如，有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页。如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

联合索引命中的本质（最左匹配原则） 在Mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先。如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2）; 如果有一个3列索引(col1,col2,col3)，实际上已经建立了三个联合索引(col1)、(col1,col2)、(col1,col2,col3)。

最左匹配举例

创建联合索引时列的选择原则 经常用的列优先（最左匹配原则）离散度高的列优先（离散度高原则）宽度小的列优先（最少空间原则）

索引选择注意事项

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

一般两种情况下不建议建索引：

表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了;

至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值： Index Selectivity = Cardinality / #T 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

代码语言：javascript复制

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

 ------------- 
| Selectivity |
 ------------- 
|      0.0000 |
 -------------

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。

不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

4. 慢查询优化

4.1 MySQL Explain详解

我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

代码语言：javascript复制

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

explain查询结果：

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra 概要描述： id:选择标识符 select_type:表示查询的类型。 table:输出结果集的表 partitions:匹配的分区 type:表示表的连接类型 possible_keys:表示查询时，可能使用的索引 key:表示实际使用的索引 key_len:索引字段的长度 ref:列与索引的比较 rows:扫描出的行数(估算的行数) filtered:按表条件过滤的行百分比 Extra:执行情况的描述和说明

id

SELECT识别符。这是SELECT的查询序列号

我的理解是SQL执行的顺序的标识，SQL从大到小的执行

1. id相同时，执行顺序由上至下

2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

代码语言：javascript复制

-- 查看在研发部并且名字以Jef开头的员工，经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';

select_type

示查询中每个select子句的类型：

(1) SIMPLE(简单SELECT，不使用UNION或子查询等) (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY) (3) UNION(UNION中的第二个或后面的SELECT语句) (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询) (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select) (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询) (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询) (8) DERIVED(派生表的SELECT, FROM子句的子查询) (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）。

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中。

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

不损失精确性的情况下，长度越短越好。

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

代码语言：javascript复制

-- 测试Extra的filesort
explain select * from emp order by name;

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句

4.2 慢查询优化案例

4.2.1 复杂的深分页问题优化

背景

有一个article表，用于存储文章的基本信息的，有文章id，作者id等一些属性，有一个content表，主要用于存储文章的内容，主键是article_id，需求需要将一些满足条件的作者发布的文章导入到另外一个库，所以我同事就在项目中先查询出了符合条件的作者id，然后开启了多个线程，每个线程每次取一个作者id，执行查询和导入工作。

查询出作者id是1111，名下的所有文章信息，文章内容相关的信息的SQL如下：

代码语言：javascript复制

SELECT
    a.*, c.*
FROM
    article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
    a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100

因为查询的这个数据库是机械硬盘的，在offset查询到20万时，查询时间已经特别长了，运维同事那边直接收到报警，说这个库已经IO阻塞了，已经多次进行主从切换了，我们就去navicat里面试着执行了一下这个语句，也是一直在等待，然后对数据库执行show proceesslist 命令查看了一下，发现每个查询都是处于Writing to net的状态，没办法只能先把导入的项目暂时下线，然后执行kill命令将当前的查询都杀死进程(因为只是客户端Stop的话，MySQL服务端会继续查询)。

然后我们开始分析这条命令执行慢的原因：

是否是联合索引的问题

当前是索引情况如下：

代码语言：javascript复制

article表的主键是id，author_id是一个普通索引
content表的主键是article_id

所以认为当前是执行流程是先去article表的普通索引author_id里面找到1111的所有文章id，然后根据这些文章id去article表的聚集索引中找到所有的文章，然后拿每个文章id去content表中找文章内容等信息，然后判断create_time是否满足要求，进行过滤，最终找到offset为20000后的100条数据。

所以我们就将article的author_id索引改成了联合索引(author_id,create_time),这样联合索引(author_id,create_time)中的B 树就是先安装author_id排序，再按照create_time排序，这样一开始在联合(author_id,create_time)查询出来的文章id就是满足create_time < '2020-04-29 00:00:00'条件的，后面就不用进行过滤了，就不会就是符合就不用对create_time过滤。

流程确实是这个流程，但是去查询时，如果limit还是210000, 100时，还是查不出数据，几分钟都没有数据，一直到navica提示超时，使用Explain看的话，确实命中索引了，如果将offset调小，调成6000, 100，勉强可以查出数据，但是需要46s，所以瓶颈不在这里。

真实原因如下：

先看关于深分页的两个查询，id是主键，val是普通索引

直接查询法

代码语言：javascript复制

select * from test where val=4 limit 300000,5;

先查主键再join

代码语言：javascript复制

select * from test a 
inner join
(select id from test where val=4 limit 300000,5) as b 
on a.id=b.id;

这两个查询的结果都是查询出offset是30000后的5条数据，区别在于第一个查询需要先去普通索引val中查询出300005个id，然后去聚集索引下读取300005个数据页，然后抛弃前面的300000个结果，只返回最后5个结果，过程中会产生了大量的随机I/O。第二个查询一开始在普通索引val下就只会读取后5个id，然后去聚集索引下读取5个数据页。

同理我们业务中那条查询其实是更加复杂的情况，因为我们业务的那条SQL不仅会读取article表中的210100条结果，而且会每条结果去content表中查询文章相关内容，而这张表有几个TEXT类型的字段，我们使用show table status命令查看表相关的信息发现

Name	Engine	Row_format	Rows	Avg_Row_length
article	InnoDB	Compact	2682682	266
content	InnoDB	Compact	2824768	16847

发现两个表的数据量都是200多万的量级，article表的行平均长度是266，content表的平均长度是16847，简单来说是当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时，我们并不会直接将所有的内容都存放在数据页节点中，而是将行数据中的前 768 个字节存储在数据页中，后面会通过偏移量指向溢出页。

（详细了解可以看看这篇文章深度好文带你读懂MySQL和InnoDB）

img

这样再从content表里面查询连续的100行数据时，读取每行数据时，还需要去读溢出页的数据，这样就需要大量随机IO，因为机械硬盘的硬件特性，随机IO会比顺序IO慢很多。所以我们后来又进行了测试，

只是从article表里面查询limit 200000，100的数据，发现即便存在深分页的问题，查询时间只是0.5s，因为article表的平均列长度是266，所有数据都存在数据页节点中，不存在页溢出，所以都是顺序IO，所以比较快。

代码语言：javascript复制

//查询时间0.51s
SELECT a.* FROM article a  
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
LIMIT 200100, 100

相反的，我们直接先找出100个article_id去content表里面查询数据，发现比较慢，第一次查询时需要3s左右(也就是这些id的文章内容相关的信息都没有过，没有缓存的情况)，第二次查询时因为这些溢出页数据已经加载到buffer pool，所以大概0.04s。

代码语言：javascript复制

SELECT SQL_NO_CACHE c.* 
FROM article_content c 
WHERE c.article_id in(100个article_id)

解决方案

所以针对这个问题的解决方案主要有两种：

先查出主键id再inner join

非连续查询的情况下，也就是我们在查第100页的数据时，不一定查了第99页，也就是允许跳页查询的情况，那么就是使用先查主键再join这种方法对我们的业务SQL进行改写成下面这样，下查询出210000, 100时主键id，作为临时表temp_table，将article表与temp_table表进行inner join，查询出中文章相关的信息，并且去left Join content表查询文章内容相关的信息。第一次查询大概1.11s，后面每次查询大概0.15s

代码语言：javascript复制

SELECT
    a.*, c.*
FROM article a
INNER JOIN(
    SELECT  id FROM article a
    WHERE   a.author_id = 1111
    AND a.create_time < '2020-04-29 00:00:00'
    LIMIT 210000 ,
    100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id

优化结果

优化前，offset达到20万的量级时，查询时间过长，一直到超时。

优化后，offset达到20万的量级时，查询时间为1.11s。

利用范围查询条件来限制取出的数据

这种方法的大致思路如下，假设要查询test_table中offset为10000的后100条数据，假设我们事先已知第10000条数据的id，值为min_id_value

select * from test_table where id > min_id_value order by id limit 0, 100，就是即利用条件id > min_id_value在扫描索引是跳过10000条记录，然后取100条数据即可，这种处理方式的offset值便成为0了，但此种方式有限制，必须知道offset对应id，然后作为min_id_value，增加id > min_id_value的条件来进行过滤，如果是用于分页查找的话，也就是必须知道上一页的最大的id，所以只能一页一页得查，不能跳页，但是因为我们的业务需求就是每次100条数据，进行分批导数据，所以我们这种场景是可以使用。针对这种方法，我们的业务SQL改写如下：

代码语言：javascript复制

//先查出最大和最小的id
SELECT min(a.id) as min_id , max(a.id) as max_id 
FROM article a 
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
//然后每次循环查找
while(min_id<max_id) {
        SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id  WHERE a.author_id = 1111  AND a.id > min_id LIMIT 100
        //这100条数据导入完毕后，将100条数据数据中最大的id赋值给min_id，以便导入下100条数据
}

优化结果

优化前，offset达到20万的量级时，查询时间过长，一直到超时。

优化后，offset达到20万的量级时，由于知道第20万条数据的id，查询时间为0.34s。

4.2.2 联合索引问题优化

联合索引其实有两个作用：

1.充分利用where条件，缩小范围

例如我们需要查询以下语句：

代码语言：javascript复制

SELECT * FROM test WHERE a = 1 AND b = 2

如果对字段a建立单列索引，对b建立单列索引，那么在查询时，只能选择走索引a，查询所有a=1的主键id，然后进行回表，在回表的过程中，在聚集索引中读取每一行数据，然后过滤出b = 2结果集，或者走索引b，也是这样的过程。如果对a，b建立了联合索引(a,b),那么在查询时，直接在联合索引中先查到a=1的节点，然后根据b=2继续往下查，查出符合条件的结果集，进行回表。

2.避免回表(此时也叫覆盖索引)

这种情况就是假如我们只查询某几个常用字段，例如查询a和b如下：

代码语言：javascript复制

SELECT a,b FROM test WHERE a = 1 AND b = 2

对字段a建立单列索引，对b建立单列索引就需要像上面所说的，查到符合条件的主键id集合后需要去聚集索引下回表查询，但是如果我们要查询的字段本身在联合索引中就都包含了，那么就不用回表了。

3.减少需要回表的数据的行数

这种情况就是假如我们需要查询a>1并且b=2的数据

代码语言：javascript复制

SELECT * FROM test WHERE a > 1 AND b = 2

如果建立的是单列索引a，那么在查询时会在单列索引a中把a>1的主键id全部查找出来然后进行回表。如果建立的是联合索引(a,b),基于最左前缀匹配原则，因为a的查询条件是一个范围查找(=或者in之外的查询条件都是范围查找)，这样虽然在联合索引中查询时只能命中索引a的部分，b的部分命中不了，只能根据a>1进行查询，但是由于联合索引中每个叶子节点包含b的信息，在查询出所有a>1的主键id时，也会对b=2进行筛选，这样需要回表的主键id就只有a>1并且b=2这部分了，所以回表的数据量会变小。

我们业务中碰到的就是第3种情况，我们的业务SQL本来更加复杂，还会join其他表，但是由于优化的瓶颈在于建立联合索引，所以进行了一些简化，下面是简化后的SQL：

代码语言：javascript复制

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a
where
  a.create_time between '2020-03-29 03:00:00.003'
and '2020-04-29 03:00:00.003'
and a.status = 1

我们的需求其实就是从article表中查询出最近一个月，status为1的文章，我们本来就是针对create_time建了单列索引，结果在慢查询日志中发现了这条语句，查询时间需要0.91s左右，所以开始尝试着进行优化。

为了便于测试，我们在表中分别对create_time建立了单列索引create_time，对(create_time,status)建立联合索引idx_createTime_status。

强制使用idx_createTime进行查询

代码语言：javascript复制

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a  FORCE INDEX(idx_createTime)
where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

强制使用idx_createTime_status进行查询（即使不强制也是会选择这个索引）

代码语言：javascript复制

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a  FORCE INDEX(idx_createTime_status)
where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

优化结果：

优化前使用idx_createTime单列索引，查询时间为0.91s

优化前使用idx_createTime_status联合索引，查询时间为0.21s

EXPLAIN的结果如下：

id	type	key	key_len	rows	filtered	Extra
1	range	idx_createTime	4	311608	25.00	Using index condition; Using where
2	range	idx_createTime_status	6	310812	100.00	Using index condition

原理分析

先介绍一下EXPLAIN中Extra列的各种取值的含义

Using filesort

当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。

Using index

仅使用索引树中的信息从表中检索列信息，而不需要进行附加搜索来读取实际行(使用二级覆盖索引即可获取数据)。当查询仅使用作为单个索引的一部分的列时，可以使用此策略。

Using temporary

要解决查询，MySQL需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和ORDER BY子句，则通常会发生这种情况。官方解释：”为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过加临时表来辅助处理。

Using where

表示当where过滤条件中的字段无索引时，MySQL Sever层接收到存储引擎(例如innodb)的结果集后，根据where条件中的条件进行过滤。

Using index condition

Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

我们的实际案例中，其实就是走单个索引idx_createTime时，只能从索引中查出满足a.create_time between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'条件的主键id，然后进行回表，因为idx_createTime索引中没有status的信息，只能回表后查出所有的主键id对应的行。然后innodb将结果集返回给MySQL Sever，MySQL Sever根据status字段进行过滤，筛选出status为1的字段，所以第一个查询的Explain结果中的Extra才会显示Using where。

filtered字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，这个是预估值，因为status取值是null，1，2，3，4，所以这里给的25%。

所以第二个查询与第一个查询的区别主要在于一开始去idx_createTime_status查到的结果集就是满足status是1的id，所以去聚集索引下进行回表查询时，扫描的行数会少很多（大概是2.7万行与15万行的区别），之后innodb返回给MySQL Server的数据就是满足条件status是1的结果集（2.7万行），不用再进行筛选了，所以第二个查询才会快这么多，时间是优化前的23%。（两种查询方式的EXPLAIN预估扫描行数都是30万行左右是因为idx_createTime_status只命中了createTime，因为createTime不是查单个值，查的是范围）

代码语言：javascript复制

//查询结果行数是15万行左右
SELECT count(*) from article a 
where a.post_time 
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'

//查询结果行数是2万6行左右
SELECT count(*) from article a 
where a.post_time 
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003' 
and a.audit_status = 1

发散思考：如果将联合索引(createTime，status)改成(status，createTime)会怎么样？

代码语言：javascript复制

where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

根据最左匹配的原则，因为我们的where查询条件是这样，如果是(createTime，status)那么索引就只能用到createTime，如果是(status，createTime)，因为status是查询单个值，所以status，createTime都可以命中，在(status，createTime)索引中扫描行数会减少，但是由于(createTime，status)这个索引本身值包含createTime，status，id三个字段的信息，数据量比较小，而一个数据页是16k，可以存储1000个以上的索引数据节点，而且是查询到createTime后，进行的顺序IO，所以读取比较快，总得的查询时间两者基本是一致。下面是测试结果：

首先创建了(status，createTime)名叫idx_status_createTime，

代码语言：javascript复制

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a  FORCE INDEX(idx_status_createTime)
where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

查询时间是0.21，跟第二种方式(createTime，status)索引的查询时间基本一致。

Explain结果对比：

id	type	key	key_len	rows	filtered	Extra
2	range	idx_createTime_status	6	310812	100.00	Using index condition
3	range	idx_status_createTime	6	52542	100.00	Using index condition

扫描行数确实会少一些，因为在idx_status_createTime的索引中，一开始根据status = 1排除掉了status取值为其他值的情况。

5. 参考

（1）MySQL索引原理及慢查询优化 https://tech.meituan.com/2014/06/30/mysql-index.html （2）MySQL Explain详解 https://www.cnblogs.com/tufujie/p/9413852.html （3）MySQL慢查询优化（线上案例调优）https://www.cnblogs.com/notfound9/p/12928763.html

全文检索自动驾驶云数据库SQLServer sql 数据库

0 人点赞