Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）

2018-09-1209:46:54数据库教程Comments1,947 views字数 15266阅读模式

一旦涉及到查询优化，就离不开索引的应用，本文选取mysql常用的引擎InnoDB作为研究对象，针对InnoDB引擎利用的索引结构B+树做个简单说明。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

InnoDB的B+树

假设我们创建表Student，主键为id：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

CREATE TABLE `Student` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;复制代码

插入12条数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

insert into Student(id,name) valuse(1,'XiaoHong')
insert into Student(id,name) valuse(2,'XiaoMing')
insert into Student(id,name) valuse(3,'XiaoFang')
....
insert into Student(id,name) valuse(12,'XiaoYou')
复制代码

此时，Innodb引擎将会根据主键id自行创建一个B+树的索引结构，我们有如下图的抽象：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

如何理解图中结构的形态？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

表数据首先会根据主键id的顺序存储在磁盘空间，图中叶节点存放表中每行的真实数据，可以认识到表数据本身属于主键索引的一部分，如下图，每行数据根据主键id按序存放：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

我们设定id为Int类型占据4个字节，name字段为固定10字节的Char类型，Student表每行将占据14个字节的磁盘空间。在理想状况下，我们可以简化成这样的一个认识：假定图中第一行（1，XiaoHong）在磁盘地址0x01，那么第二行（2，XiaoMing）则在磁盘地址0x0f（0x01+14=0x0f），以此类推下去。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

非叶节点存放索引值和对应的指针，我们看到这12行数据根据主键id分成了五个节点（一个非叶节点四个叶节点），真实环境下Mysql利用磁盘按块读取的原理设定每个磁盘块（也可理解为页，一般为4kb，innodb中将页大小设定为16kb）为一个树节点大小，这样每次一个磁盘I/O产生的内容就可以获取对应节点所有数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于非叶节点每个索引值左边的指针指向小于这个索引值的对应数据的节点地址，索引值右边的指针指向大于或等于该索引值的对应数据的节点地址：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

如上图，索引值为4的左边指针的指向结点数据必定都是小于4的，对应右指针指向节点范围必定是大于或等于4的。而且，在索引值数目一定的情况下，B+树为了控制树的高度尽可能小，会要求每个非页节点尽可能存放更多数据，一般要求非叶节点索引值的个数至少为（n-1)/2，n为一个页块大小最多能容纳的值个数。按照上图假设的构造形态，我们知道每个页块最多只能容纳三个索引值或三行数据（实际会大很多），在这样的前提下，如果继续插入行数据，那么首先是叶节点将没有空间容纳新数据，此时叶节点通过分裂来增加一个新叶节点完成保存：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

可以想象的是，我们试图继续插入2条数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

insert into Student(id,name) valuse(13,'XiaoRui')
insert into Student(id,name) valuse(14,'XiaoKe')复制代码

最终将会变成如下形态：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

因为每个非页节点最多容纳3个索引值和对应的4个指针（扇出），整个查询的复杂度为O(log4N)，N为表的行数。对于拥有1000个学生数据的Student表来说，根据id查询的复杂度为log41000=5，在这里，查询复杂度在B+树中可以直观地理解为树的高度，通过非叶节点一层层的递进判断最终定位到目标数据所在的页块地址。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

因此，innodb引擎的表数据是通过主键索引结构来组织的，叶节点存放着行数据，是一种B+树文件组织，如果通过主键定位行数据将拥有极大的效率，所以在创建表时无论有没明确定义主键索引，引擎内部都会自动为表创建一个主键索引继而构造出一个B+树文件组织。在实际应用中，当通过主键去查询某些数据时，首先是通过B+树定位到具体的叶节点地址，因为叶节点刚好设定为磁盘块连续地址的整数倍大小，所以通过连续地址的快速I/O将整个节点内容加载到内存，然后从内存中对节点内容进行筛选找出目标数据！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

但innodb引擎还允许我们对表其它字段单独构建索引，也就是常说的辅助索引，比如我们这样创建Student表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

CREATE TABLE `Student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;复制代码

插入示例数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

insert into Student(id,name) valuse(1,'A')
insert into Student(id,name) valuse(2,'A')
insert into Student(id,name) valuse(3,'B')
......
......
insert into Student(id,name) valuse(12,'F')复制代码

如何理解name字段索引结构存在的形式？直接上图：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

可见，辅助索引同样会构建一个B+树索引结构，只不过叶节点存放的是主键id值，非数字的索引在索引结构中按照预先设定的字符集排序规则进行排序，比如name=A在对应排序规则中是比B要小的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

按照上图的结构，假定我们进行如下操作：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select * from Student where name='A';复制代码

那么首先会利用辅助索引定位到叶节点1，然后加载到内存，在内存中检索发现有两个主键id：1、2 符合条件，然后通过主键id再从主键索引进行检索，把行数据全部加载出来！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

在辅助索引中，innodb还支持组合索引的形式，把多个字段按序组合而成一个索引，比如我们创建如下Student表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

CREATE TABLE `StudentTmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;复制代码

name和age组合构成一个索引，对应B+树索引结构有如下形式：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

在该组合索引中，叶节点内容先是按照name字段进行排序，在name字段值相同情况下再按照age字段进行排序，这样在对name和age作为组合条件查询时将充分利用两个字段的排序关系实现多级索引的定位。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

好的，我们不在纠结B+树的更多细节，我们只需先在脑海中构建索引结构的大体形态，想象着索引的查询是在一个树状结构中层层递进最终定位到目标数据的过程，并且认识到查询复杂度和B+树非叶节点中指针个数存在着联系，这对于我们形成查询成本的敏感性是非常有帮助的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

通过Explain方法来了解查询成本

体会了索引构造带来的查询效率的提升，在实际应用中我们又该如何了解每个查询Sql对索引的利用情况呢？Explain方法可以在执行前辅助我们进行判断，通过相关参数特别是对于多层嵌套或连接的复杂查询语句具有非常大的帮助。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

通过在查询sql前面加上explain关键字就可以完成计划分析：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

explain select id from Student where id=1;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

执行后有如下结果：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

我们看到结果表单有id、table、select_type...等10个参数，每个参数有对应的结果值，接下来我们一步步做好认识。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

id:用于标识各个子查询的执行顺序，值越大执行优先级越高文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

上文查询只是一个简单查询，故id只有一个1，我们现在增加一个子查询后：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

explain select name from Student where id=(select max(id) from Student);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

有:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

可以看到有两个结果行，说明这个sql有两个查询计划，table字段用于指明该查询计划对应的表名，而id值的作用在于提示我们哪个查询计划是优先执行的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

table:指定对应查询计划关联的表名文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

上文关于id字段的示例说明中，我们发现id=2的查询计划（select max(id) from Student）对应表名是空的，这似乎不符合常规，难道这个查询计划不涉及到表操作？我们在Extra字段中找到了这样一个说明：Select tables optimized away这个语句告诉我们，引擎对该查询计划做了优化，基于索引层面的优化像min/max操作或者count(*)操作，不需要等到执行阶段对表进行检索，该值可能预先保存在某些地方直接读取。笔者猜想的一种情况是，因为id字段本身属于Student表的主键索引，引擎本身实时保存着min(id)、max(id)的值供查询，或者直接读取主键索引树第一个、最后一个叶节点数据来获取，所以类似查询计划在实际执行中具有极大的执行效率。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select_type:标识查询计划的类型文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select_type主要有如下几种不同类型：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

  • SIMPLE：简单SELECT,不使用UNION或子查询等
  • PRIMARY：查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
  • UNION：UNION中的第二个或后面的SELECT语句
  • SUBQUERY：子查询中的第一个SELECT
  • DERIVED(派生表的SELECT, FROM子句的子查询)

对于 explain select id from Student where id=1;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select_type为SIMPLE，表示该sql是最简单形式的查询文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select name from Student union select name from Course;有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

我们看到有两个查询计划，对于最外层Student表的查询为PRIMARY，表示该语句是复杂语句，包含着其它查询计划，而这个包含的查询计划就是Course查询计划，Course查询计划的select_type为UNION，印证了上面对UNION类型的说明。结合id字段代表的意义，我们了解到引擎先是执行Course表计划再是执行Student表计划。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select id,(select count(*) from Course) as count from Student; 有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

这次同样是两个查询计划，但区别在于我们构建了一个对Course表的子查询语句，相应的select_type为SUBQUERY，通过id可知，该sql会优先执行Course表的查询计划再执行Student表的查询计划。

对于 explain select name from (select name from Student where id=1) tb;有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

这个语句的特别之处在于对Student表的子查询计划被外面包裹了一层，因此对应的select_type为DERIVED。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

到这里，我们认识到一个sql在执行过程中会被拆分一个以上的查询计划，计划间有一定的执行优先级，而select_type则很好地定义了不同计划存在的形式，这使得我们可以把复杂sql进行结构上的拆解，针对不同的查询计划一个个分析最后完成整体的优化。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

接下来我们开始重点关注explian分析表单的其它几个字段：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

  • type
  • possible_keys：查询计划可能用到的索引
  • key：查询计划实际采用的索引
  • rows：查询复杂度，亦可简单理解为查询计划需要处理的行数

这些字段和索引紧密联系，将真正为我们查询成本的分析提供参考，我们可以通过这些字段很好地判断索引的利用情况了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type:对表进行数据查询时所利用的检索方式文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type指明了该查询计划是否利用了索引结构，以及检索上存在的具体特点，具体类别有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

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

对于 explain select name from Student where name='学生1';有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type 为 ALL，即name在Student表中不是索引，为了查询name为'学生1'的数据，数据库将必要地对表数据进行全局检索，其中rows说明了需要检索的量级，我们可以理解为查询复杂度，因为数据库需要对表数据一行行处理，上面rows=699323我们可以判断出Student表大概是70万行的量级。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select name from Student; 有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type 为 index，因为我们对name已经事先构建了辅助索引，所以查询表中所有的name信息只需在name对应的B+树上扫描即可：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

如上图，直接在辅助索引树的最左叶节点开始扫描，查询出所有name信息，查询出来的数据本身是按序排好的，如果你对sql刚好有排序需求：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select name from Student order by name asc;复制代码

那么查询速度相较于从表数据结构获取将有大幅的提升！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select * from Student where id>1 and id<5;有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type 为 range，这说明这个查询是先通过索引结构进行范围确定的，如下图：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select name from Student where name='A'; 有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type 为 ref，表明 name 索引是非唯一索引，即表中可能存在多个name相同的记录，在通过name索引结构检索数据时会把匹配条件的所有记录都检索出来。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id 有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

我们注意到在此连接查询中，关于Student的主键id作为连接条件时，对应Student表的查询计划类型为eq_ref，指明利用的是唯一索引的特性，每次对Student的一次查询都将最终定位到一条结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于 explain select id from Student where id=1; 有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

type 为 const，一般sql对应查询条件是唯一索引时才出现此情况，说明引擎内部对语句做了特殊处理，在计划执行前将结果先查询出来并转化为一个常量，这样在实际执行过程中直接引用常量可免去重复的查询过程。我们再给个例子：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id where Student.id=1；有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对于此连接查询，在不考虑执行前const优化的情况下可利用伪代码表示成如下执行逻辑：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

outerIterator=select A.s_id,A.score from Score as A;
//对Score表进行全局的行扫描
while (outerRow=outerIterator.next){
    innerIterator=select A.id,A.name from Student as A where A.id=1;
    innerRow=innerIterator.next;
    if (innerRow.id=outerRow.s_id){
        //将符合条件的结果记录输出
        print(outerRow.score,innerRow.name);
    }
}复制代码

如上所示，首先是对Score表进行全局查询，期间每一行都需要和Student表对应id的数据进行比对，但每次比对都是Student表的一次查询消耗，因此可以优化成如下逻辑：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//将Student表的查询计划优先执行，并将结果赋值到常量
constIterator=select A.id,A.name from Student as A where A.id=1;
constRow=constIterator.next;constId=constRow.id;constName=constRow.name;

//查询计划执行过程
outerIterator=select A.s_id,A.score from Score as A;
while (outerRow=outerIterator.next){
    //计划执行过程中只需和对应常量比较，大大提高执行效率
    if (innerRow.id=constId){
        print(outerRow.score,constName);
    }
}复制代码

通过把Student表计划的结果提取到常量将避免循环检索中带来的查询消耗，由此带来的性能提升是非常可观的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

目前为止我们把Explain方法做了个基本的介绍，通过对sql查询计划的划分和索引利用程度的判定已经能提供大部分优化的思路，接下来我们将结合真实的数据进行一次测试，我们将重点关注rows字段的变化来判定我们优化的效果并希望能在整个过程引申更多思考。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

实战优化

之前本人在论坛看到一同学讨论关于数据库的优化过程，这里我们参照人家当时面对的表情况进行演示，我们假定基于mysql 5.5版本对一个庞大的教务系统的数据库进行优化，其中涉及3个表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

学生表（id:学生id；name:学生名）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

CREATE TABLE `Student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;复制代码

Student表初始化有10万行数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

INSERT INTO `Student` (`id`, `name`)
VALUES
	(1, '学生0'),
	(2, '学生1'),
	(3, '学生2'),
        .....
        .....
        .....
        (700000,'学生699999')复制代码

课程表（id:课程id；name:课程名称）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

CREATE TABLE `Course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;复制代码

课程表初始化有100行数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

INSERT INTO `Course` (`id`, `name`)
VALUES
	(1, '课程0'),
	(2, '课程1'),
	(3, '课程2'),
        .....
        .....
        .....
        (100,'课程99')
复制代码

成绩表（id:记录id；s_id:学生id；c_id:课程id；score:分数)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

CREATE TABLE `Score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;复制代码

成绩表记录着不同学生对应课程的分数，我们在表里初始化了10万学生其中20门课程总共200万行的数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

INSERT INTO `Score` (`id`, `s_id`, `c_id`, `score`)
VALUES
	(1, 1, 1, 63),
	(2, 2, 1, 67),
	(3, 3, 1, 40),
        .....
        .....
        (20000000,100000,20,95)复制代码

实际应用中有这么个需求，需要查询出某门课程考了100分的所有同学的名字，写了如下语句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select Student.name from Student where id in (select s_id from Score where c_id = 1 and score = 100 );文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

笔者尝试运行了一下，经过长达几分钟的等待不得不终止这个执行，为何如此耗时？通过Explain分析有如下结果：

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

该执行计划包含两个查询计划，我们注意到对应的rows分别为十万级和百万级，对应的查询复杂度分别为O(100000)、O(2000000），刚好和表对应行数一样，说明都进行了全表扫描，我们看type字段为ALL，印证了我们的设想。接下来对子查询尝试建立索引：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

alter table Score add index index_cid(c_id)复制代码

笔者再尝试了运行一下，经过长达几分钟的等待又放弃了，继续查看Explain分析结果：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

看到Score表的查询复杂度降为O(200000)，只带来了10倍的性能优化，通过type=ref我们知道这是一个非唯一索引，说明c_id在表中含有大量相同值其优化效果并不可观，我们再尝试对c_id和score建立一个二级索引：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

alter table Score add index_cid_score (c_id,score)复制代码

这次我们总共获取了1566条结果，总共耗时103s：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

对应Explain分析结果：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

通过组合索引我们把Score表的查询复杂度降到了O(1565)，较单个索引有了较大幅提升，但总体的执行时间依旧不能满意。我们再把目光投向Student表，发现对应的查询计划并没有利用到索引，根据Explain结果，Score表查询计划的id值为2，Student表的id值为1，按照优先级规则，应该是先执行Score计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select s_id from Score where c_id = 1 and score = 100复制代码

总共1566个结果，耗时45ms：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

再执行Student计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select Student.name from Student  where id in (55,68,104,243......99688)复制代码

总共1566个结果，耗时1.06s：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

我们预想两个查询计划总共的执行时间应该是1.06s+0.045s=1.105s，这与实际的103s却有很大的差距，如何解释？仔细观察Score表计划的select_type为DEPENDENT SUBQUERY，上文介绍过SUBQUERY的形式，即表示子查询中的第一个SELECT，这里的DEPENDENT标识区别于普通的子查询在于说明该计划存在依赖关系，即Score表计划的执行过程依赖于外面计划（Student表）的执行结果，整个逻辑过程用伪代码表示有：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//外部先对Student表进行全局的行扫描
outerIterator=select Student.id,Student.name from Student

while (outerRow=outerIterator.next){    
    //内部Score的执行过程依赖于外部Student表的执行结果
    innerIterator=select Score.s_id from Score where c_id = 1 and score = 100
    innerRow=innerIterator.next;
    if (innerRow.s_id=outerRow.id){
        //将符合条件的结果记录输出
        print(outerRow.name);
    }
}复制代码

先是Student表进行全表扫描，然后内部Score的查询次数取决于Student表的结果行数，由此得出的查询复杂度为O(总）=O(Student)*O(Score)，此处实例需要Mysql承担的计算成本为：O(157337275)=O(100535)*O(1565)，这是需要大量查询时间的原因！那么有没有办法让Student表的id索引也发挥作用，至少理论上按照我们前面的设想，我们可以让整个查询控制在1s左右呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

从执行逻辑上看我们可以设想这样的情况：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//外部先对Score表按条件查询
outerIterator=select Score.s_id from Score where c_id = 1 and score = 100

while (outerRow=outerIterator.next){    
    //内部Student的执行过程依赖于外部Score表的执行结果
    innerIterator=select Student.id,Student.name from Student where id=outerRow.s_id
    innerRow=innerIterator.next;
    if (innerRow!=null){
        //将符合条件的结果记录输出
        print(innerRow.name);
    }
}复制代码

先是利用Score表的组合索引检索出c_id=1、score=100的数据，然后在循环匹配中利用Student表的id索引检索name信息，在查询复杂度上：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

explain select Score.s_id from Score where c_id = 1 and score = 100有：Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）explain select name from Student where id=? 有：Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）

猜想理论上有：O(1565)=O(1565)*O(1)，我们试图将sql用连接查询来表示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

只花费0.048s！看下Explain分析：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

果然满足了我们的期望，Student表计划用到了唯一索引、Score表用到了组合索引，最后的查询复杂度也控制在了O(1565)，区别于开始示例的子查询，连接查询又为何充分利用了索引呢？内部的执行逻辑该如何去理解？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

我们这里先理一下关于连接查询的问题，在mysql中，连接的实现本质是笛卡尔积的过程，笛卡尔积中两个表的所有行都将一一对应得到一次连接，比如语句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select * from Student,Course;复制代码

对应的逻辑过程：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//外部先对Student表进行全局的行扫描
outerIterator=select Student.id,Student.name from Student;

while (outerRow=outerIterator.next){    
    //循环中外部结果每一行都将和Course表每一行进行一次连接
    innerIterator=select Course.id,Course.name from Course;
    while (innerRow=innerIterator.next){
        //获取对应连接结果
        print(outerRow.id,outerRow.name,innerRow.id,innerRow.name)
    }  
}复制代码

在mysql中我们一般这样表示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select Student.id,Student.name,Course.id,Course.name from Student join Course;复制代码

可知连接查询的复杂度最大可达到O(Student表行数)*O(Course表行数)，加入n个表进行连接查询，那么复杂度模型有O=O(表2行数)*O(表2行数)......*O(表n行数)，这将是一个接近指数级的爆发增长！而在实际应用中，往往会通过关键字on和where来控制数据连接规模，具体为根据实际的数据筛选条件对结果行先进行过滤，然后在内部查询中结合索引完成优化。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

好了，回来上面的问题：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

笔者之前看过资料，一般数据库进行join连接时会进行笛卡尔积过程，on字段作为行连接时的判断条件，最后再利用where条件进行结果行的筛选，具体逻辑过程为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//外部先对Student表进行全局的行扫描
outerIterator=select Student.id,Student.name from Student;

while (outerRow=outerIterator.next){    
    //内部Score的执行过程依赖于外部Student表的执行结果
    innerIterator=select Score.s_id,Score.c_id,Score.score from Score;
    while(innerRow=innerIterator.next){
         //on字段条件在此处决定是否进行连接
         if (outerRow.id=innerRow.s_id){
            //将符合连接条件的结果保存
            tmpArr[]=(outerRow.name,innerRow.c_id,innerRow.score);
         }
    }
}

//接下来开始where条件的结果过滤
for i:=0;i<n;i++{
     if (tmpArr[i].c_id=1&&tmpArr[i].score=100){
         resultArr[]=tmpArr[i];
     }
}
//完成最后的结果输出
print(resultArr)复制代码

按照上述过程，我们预测的查询复杂度应该为O=O(Student表行数)*O(Score表行数)；但mysql可没这么简单，通过上文连接查询的Explain分析，我们看到执行过程都利用了两个表的索引结构：Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Score表利用了组合索引index_cid_score，我们可以猜想到引擎是先尝试对where条件进行了先行判断，然后再对结果集和Student表进行连接操作，此连接过程中我们发现Student表有利用到主键索引，所以同样猜测on关键字的匹配条件被应用到Student表的查询计划中，逻辑过程这样描述：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//外部先对Score表进行where条件筛选，查询中利用到组合索引
outerIterator=select Score.s_id,Score.c_id,Score.score from Score where c_id=1 and score=100;
while (outerRow=outerIterator.next){    
    //内部Student的执行过程利用到主键索引，on字段的判断条件此时体现在Student查询计划的where条件中
    innerIterator=select Student.name from Student where id=outerRow.s_id
    //如果存在对应行则保留
    if(innerRow=innerIterator.next){       
        resultArr[]=(innerRow.name,outerRow.c_id,outerRow.score);
    }
}
//完成最后的结果输出
print(resultArr)复制代码

很明显，上诉逻辑过程的复杂度取决于Score表条件检索后的行数，也符合我们实际Explain分析的结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

然而，笔者思考的一个问题是，对于Mysql来说并不是说join连接就一定能满足优化需求，一方面不同的引擎、不同的Mysql版本所采用的优化手段都可能存在差异，这没有一个固定标准，应对于这种变化在实际的业务处理中还得多结合Explain进行分析。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

笔者针对本次示例在Mysql 5.6版本也尝试执行了一下，发现对于sql：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select Student.name from Student where Student.id in (select s_id from Score where c_id = 1 and score = 100 )文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

在同样的索引构建下，Explain 分析结果为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Score查询计划的id值为2，拥有更高的执行优先级，但select_type出现了之前在Mysql5.5没有过的字眼：MATERIALIZED，我们先看下执行结果：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

Mysql查询优化实战剖析（附InnoDB引擎利用的索引结构B+树说明）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

这和我们在Mysql5.5版本关于join连接的结果是一样的！回到MATERIALIZED的思考，MATERIALIZED的官方描述是：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index is unique, which eliminates duplicates and makes the table smaller.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

大致意思是，优化控制器为了让子查询更高效，会将子查询的结果生成一个临时表，一般放置在内存中，同时对临时表生成相应的哈希索引来提高内存查询效率，示例的逻辑过程可以这样描述：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

//先对子查询的Score表进行物化操作，即查询结果放置内存中
materalizedRows=select Score.s_id from Score where c_id = 1 and score = 100

for i=0;i<n;i++{
      //内存中取出对应数据
      materalizedRow=materalizedRows[i]
      //内部Student的执行过程利用到主键索引，on字段的判断条件此时体现在Student查询计划的where条件中    
      innerIterator=select Student.name from Student where id=materalizedRow.s_id   
      //如果存在对应行则保留
      if(innerRow=innerIterator.next){      
          resultArr[]=(innerRow.name,outerRow.c_id,outerRow.score);
      }
}
//完成最后的结果输出
print(resultArr)复制代码

这个逻辑过程和上文join连接是相似的，这里介绍一种方法用于查看sql进行优化后的表达形式，在控制台一次输入两个语句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

explain select s.name from Student s where s.id in (select s_id from Score sc where sc.c_id = 1 and sc.score = 100 );
show warnings;复制代码

得到优化后的sql形式：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

select `test`.`Student`.`name` AS `name` from `test`.`Student` semi 
    join (`test`.`Score`) 
    where (
            (`test`.`Student`.`id` = `<subquery2>`.`s_id`) 
            and (`test`.`Score`.`score` = 100) 
            and (`test`.`Score`.`c_id` = 1)
    )复制代码

果然，Mysql5.6中，会对子查询转化为join连接形式，而所谓的MATERIALIZED优化，笔者猜想不过是借用join连接所采用的优化形式而已，这说明不同mysql版本对sql语句的结构还会进行调整，笔者建议在面对复杂查询的时候可以利用此方法先进行了解，然后结合Explain方法进行分析！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

到这里，整个示例的优化过程告一段落了，无论实际环境的查询需求多么复杂我们都可以先尝试进行查询计划的划分，观察各个计划的执行优先级，然后了解出引擎内部的执行逻辑，最后算出整体的查询成本一步步调整优化，大部分情况下笔者屡试不爽！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

总结

全文一开始，我们先是了解innodb引擎的索引构造，目的在于形成查询成本的敏感性，具备查询复杂度判断的理论支撑，而Explain方法则具体到实际应用的过程中，这是笔者所能想到的最干脆的优化手段。最后的实例演示体现了优化过程的灵活性，这个灵活体现在Mysql不同版本的支持上，这些都需要在实际应用中积累经验更好应对。笔者需要提醒的是，索引结构同时在影响着数据库的维护成本，除了提高查询效率外，在数据删改和插入上都增加了数据库的负担，这个需要结合实际情况做好权衡！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

作者：Zerui
链接：https://juejin.im/post/5b8ba5dbe51d4538d63b9345
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/4565.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/4565.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定