从理论到实践,Mysql查询优化剖析(联表查询)

转自:从理论到实践,Mysql查询优化剖析

1.关于Mysql

一旦涉及到查询优化,就离不开索引的应用,本文选取mysql常用的引擎InnoDB作为研究对象,针对InnoDB引擎利用的索引结构B+树做个简单说明。

1.1 InnoDB的B+树

假设我们创建表Student,主键为id:

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条数据:

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+树的索引结构,我们有如下图的抽象:

在这里插入图片描述如何理解图中结构的形态?
表数据首先会根据主键id的顺序存储在磁盘空间,图中叶节点存放表中每行的真实数据,可以认识到表数据本身属于主键索引的一部分,如下图,每行数据根据主键id按序存放:
在这里插入图片描述
我们设定id为Int类型占据4个字节,name字段为固定10字节的Char类型,Student表每行将占据14个字节的磁盘空间。在理想状况下,我们可以简化成这样的一个认识:假定图中第一行(1,XiaoHong)在磁盘地址0x01,那么第二行(2,XiaoMing)则在磁盘地址0x0f(0x01+14=0x0f),以此类推下去。非叶节点存放索引值和对应的指针,我们看到这12行数据根据主键id分成了五个节点(一个非叶节点四个叶节点),真实环境下Mysql利用磁盘按块读取的原理设定每个磁盘块(也可理解为页,一般为4kb,innodb中将页大小设定为16kb)为一个树节点大小,这样每次一个磁盘I/O产生的内容就可以获取对应节点所有数据。对于非叶节点每个索引值左边的指针指向小于这个索引值的对应数据的节点地址,索引值右边的指针指向大于或等于该索引值的对应数据的节点地址:
在这里插入图片描述
如上图,索引值为4的左边指针的指向结点数据必定都是小于4的,对应右指针指向节点范围必定是大于或等于4的。而且,在索引值数目一定的情况下,B+树为了控制树的高度尽可能小,会要求每个非页节点尽可能存放更多数据,一般要求非叶节点索引值的个数至少为(n-1)/2,n为一个页块大小最多能容纳的值个数。按照上图假设的构造形态,我们知道每个页块最多只能容纳三个索引值或三行数据(实际会大很多),在这样的前提下,如果继续插入行数据,那么首先是叶节点将没有空间容纳新数据,此时叶节点通过分裂来增加一个新叶节点完成保存:
在这里插入图片描述
可以想象的是,我们试图继续插入2条数据:

insert into Student(id,name) valuse(13,'XiaoRui')
insert into Student(id,name) valuse(14,'XiaoKe') 

最终将会变成如下形态:
在这里插入图片描述
因为每个非页节点最多容纳3个索引值和对应的4个指针(扇出),整个查询的复杂度为O(log4N),N为表的行数。对于拥有1000个学生数据的Student表来说,根据id查询的复杂度为log41000=5,在这里,查询复杂度在B+树中可以直观地理解为树的高度,通过非叶节点一层层的递进判断最终定位到目标数据所在的页块地址。因此,innodb引擎的表数据是通过主键索引结构来组织的,叶节点存放着行数据,是一种B+树文件组织,如果通过主键定位行数据将拥有极大的效率,所以在创建表时无论有没明确定义主键索引,引擎内部都会自动为表创建一个主键索引继而构造出一个B+树文件组织。在实际应用中,当通过主键去查询某些数据时,首先是通过B+树定位到具体的叶节点地址,因为叶节点刚好设定为磁盘块连续地址的整数倍大小,所以通过连续地址的快速I/O将整个节点内容加载到内存,然后从内存中对节点内容进行筛选找出目标数据!但innodb引擎还允许我们对表其它字段单独构建索引,也就是常说的辅助索引,比如我们这样创建Student表:

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; 

插入示例数据:

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字段索引结构存在的形式?直接上图:
在这里插入图片描述
可见,辅助索引同样会构建一个B+树索引结构,只不过叶节点存放的是主键id值,非数字的索引在索引结构中按照预先设定的字符集排序规则进行排序,比如name=A在对应排序规则中是比B要小的。
按照上图的结构,假定我们进行如下操作:

select * from Student where name=‘A’;
那么首先会利用辅助索引定位到叶节点1,然后加载到内存,在内存中检索发现有两个主键id:1、2 符合条件,然后通过主键id再从主键索引进行检索,把行数据全部加载出来!在辅助索引中,innodb还支持组合索引的形式,把多个字段按序组合而成一个索引,比如我们创建如下Student表:

 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+树索引结构有如下形式:
在这里插入图片描述
在该组合索引中,叶节点内容先是按照name字段进行排序,在name字段值相同情况下再按照age字段进行排序,这样在对name和age作为组合条件查询时将充分利用两个字段的排序关系实现多级索引的定位。好的,我们不在纠结B+树的更多细节,我们只需先在脑海中构建索引结构的大体形态,想象着索引的查询是在一个树状结构中层层递进最终定位到目标数据的过程,并且认识到查询复杂度和B+树非叶节点中指针个数存在着联系,这对于我们形成查询成本的敏感性是非常有帮助的。

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

体会了索引构造带来的查询效率的提升,在实际应用中我们又该如何了解每个查询Sql对索引的利用情况呢?Explain方法可以在执行前辅助我们进行判断,通过相关参数特别是对于多层嵌套或连接的复杂查询语句具有非常大的帮助。通过在查询sql前面加上explain关键字就可以完成计划分析:

 explain select id from Student where id=1;

执行后有如下结果:
在这里插入图片描述
我们看到结果表单有id、table、select_type…等10个参数,每个参数有对应的结果值,接下来我们一步步做好认识。

id:用于标识各个子查询的执行顺序,值越大执行优先级越高

上文查询只是一个简单查询,故id只有一个1,我们现在增加一个子查询后:

explain select name from Student where id=(select max(id) from Student);

有:
在这里插入图片描述可以看到有两个结果行,说明这个sql有两个查询计划,table字段用于指明该查询计划对应的表名,而id值的作用在于提示我们哪个查询计划是优先执行的。

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

select_type:标识查询计划的类型

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

对于:

 explain select id from Student where id=1;

select_type为SIMPLE,表示该sql是最简单形式的查询

对于:

explain select name from Student union select name from Course;

在这里插入图片描述
我们看到有两个查询计划,对于最外层Student表的查询为PRIMARY,表示该语句是复杂语句,包含着其它查询计划,而这个包含的查询计划就是Course查询计划,Course查询计划的select_type为UNION,印证了上面对UNION类型的说明。结合id字段代表的意义,我们了解到引擎先是执行Course表计划再是执行Student表计划。

对于

explain select id,(select count(*) from Course) as count from Student;

有:

在这里插入图片描述
这个语句的特别之处在于对Student表的子查询计划被外面包裹了一层,因此对应的select_type为DERIVED。到这里,我们认识到一个sql在执行过程中会被拆分一个以上的查询计划,计划间有一定的执行优先级,而select_type则很好地定义了不同计划存在的形式,这使得我们可以把复杂sql进行结构上的拆解,针对不同的查询计划一个个分析最后完成整体的优化。

接下来我们开始重点关注explian分析表单的其它几个字段:

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

这些字段和索引紧密联系,将真正为我们查询成本的分析提供参考,我们可以通过这些字段很好地判断索引的利用情况了。

type:对表进行数据查询时所利用的检索方式

type指明了该查询计划是否利用了索引结构,以及检索上存在的具体特点,具体类别有:

  • 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';

有:
在这里插入图片描述
type 为 ALL,即name在Student表中不是索引,为了查询name为’学生1’的数据,数据库将必要地对表数据进行全局检索,其中rows说明了需要检索的量级,我们可以理解为查询复杂度,因为数据库需要对表数据一行行处理,上面rows=699323我们可以判断出Student表大概是70万行的量级

对于

explain select name from Student; 

有:
type 为 index,因为我们对name已经事先构建了辅助索引,所以查询表中所有的name信息只需在name对应的B+树上扫描即可:
在这里插入图片描述

如上图,直接在辅助索引树的最左叶节点开始扫描,查询出所有name信息,查询出来的数据本身是按序排好的,如果你对sql刚好有排序需求:

select name from Student order by name asc; 

那么查询速度相较于从表数据结构获取将有大幅的提升!

对于

explain select * from Student where id>1 and id<5;

有:
type 为 range,这说明这个查询是先通过索引结构进行范围确定的,如下图:
在这里插入图片描述
对于

explain select name from Student where name='A'; 

有:
在这里插入图片描述
type 为 ref,表明 name 索引是非唯一索引,即表中可能存在多个name相同的记录,在通过name索引结构检索数据时会把匹配条件的所有记录都检索出来。
对于

explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id 

有:
在这里插入图片描述
我们注意到在此连接查询中,关于Student的主键id作为连接条件时,对应Student表的查询计划类型为eq_ref,指明利用的是唯一索引的特性,每次对Student的一次查询都将最终定位到一条结果。

对于

explain select id from Student where id=1; 

有:
在这里插入图片描述
type 为 const,一般sql对应查询条件是唯一索引时才出现此情况,说明引擎内部对语句做了特殊处理,在计划执行前将结果先查询出来并转化为一个常量,这样在实际执行过程中直接引用常量可免去重复的查询过程。我们再给个例子:

explain select Student.name,Score.score from Score join Student on Score.s_id=Student.id where Student.id=1

有:
在这里插入图片描述
对于此连接查询,在不考虑执行前const优化的情况下可利用伪代码表示成如下执行逻辑:

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表的一次查询消耗,因此可以优化成如下逻辑:

//将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表计划的结果提取到常量将避免循环检索中带来的查询消耗,由此带来的性能提升是非常可观的。目前为止我们把Explain方法做了个基本的介绍,通过对sql查询计划的划分和索引利用程度的判定已经能提供大部分优化的思路,接下来我们将结合真实的数据进行一次测试,我们将重点关注rows字段的变化来判定我们优化的效果并希望能在整个过程引申更多思考。

1.3 实战优化

之前本人在论坛看到一同学讨论关于数据库的优化过程,这里我们参照人家当时面对的表情况进行演示,我们假定基于mysql 5.5版本对一个庞大的教务系统的数据库进行优化,其中涉及3个表:

学生表(id:学生id;name:学生名)

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万行数据:

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

课程表(id:课程id;name:课程名称)


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行数据:

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

成绩表(id:记录id;s_id:学生id;c_id:课程id;score:分数)

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万行的数据:

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分的所有同学的名字,写了如下语句:

select Student.name from Student where id in (select s_id from Score where c_id = 1 and score = 100 );

笔者尝试运行了一下,经过长达几分钟的等待不得不终止这个执行,为何如此耗时?通过Explain分析有如下结果:
在这里插入图片描述
该执行计划包含两个查询计划,我们注意到对应的rows分别为十万级和百万级,对应的查询复杂度分别为O(100000)、O(2000000),刚好和表对应行数一样,说明都进行了全表扫描,我们看type字段为ALL,印证了我们的设想。接下来对子查询尝试建立索引:

 alter table Score add index index_cid(c_id) 

笔者再尝试了运行一下,经过长达几分钟的等待又放弃了,继续查看Explain分析结果:
在这里插入图片描述
看到Score表的查询复杂度降为O(200000),只带来了10倍的性能优化,通过type=ref我们知道这是一个非唯一索引,说明c_id在表中含有大量相同值其优化效果并不可观,我们再尝试对c_id和score建立一个二级索引:

alter table Score add index_cid_score (c_id,score) 

这次我们总共获取了1566条结果,总共耗时103s:
在这里插入图片描述对应Explain分析结果:
在这里插入图片描述
通过组合索引我们把Score表的查询复杂度降到了O(1565),较单个索引有了较大幅提升,但总体的执行时间依旧不能满意。我们再把目光投向Student表,发现对应的查询计划并没有利用到索引,根据Explain结果,Score表查询计划的id值为2,Student表的id值为1,按照优先级规则,应该是先执行Score计划:

 select s_id from Score where c_id = 1 and score = 100 

总共1566个结果,耗时45ms:
在这里插入图片描述再执行Student计划:

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

总共1566个结果,耗时1.06s:
在这里插入图片描述
我们预想两个查询计划总共的执行时间应该是1.06s+0.045s=1.105s,这与实际的103s却有很大的差距,如何解释?仔细观察Score表计划的select_type为DEPENDENT SUBQUERY,上文介绍过SUBQUERY的形式,即表示子查询中的第一个SELECT,这里的DEPENDENT标识区别于普通的子查询在于说明该计划存在依赖关系,即Score表计划的执行过程依赖于外面计划(Student表)的执行结果,整个逻辑过程用伪代码表示有:

//外部先对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左右呢?

从执行逻辑上看我们可以设想这样的情况:

//外部先对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信息,在查询复杂度上:

explain select Score.s_id from Score where c_id = 1 and score = 100

有:
在这里插入图片描述

explain select name from Student where id=? 

有:
在这里插入图片描述
猜想理论上有:O(1565)=O(1565)*O(1),我们试图将sql用连接查询来表示:

select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100

在这里插入图片描述
只花费0.048s!看下Explain分析:

果然满足了我们的期望,Student表计划用到了唯一索引、Score表用到了组合索引,最后的查询复杂度也控制在了O(1565),区别于开始示例的子查询,连接查询又为何充分利用了索引呢?内部的执行逻辑该如何去理解?

我们这里先理一下关于连接查询的问题,在mysql中,连接的实现本质是笛卡尔积的过程,笛卡尔积中两个表的所有行都将一一对应得到一次连接,比如语句:

select * from Student,Course; 

对应的逻辑过程:

//外部先对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中我们一般这样表示:

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来控制数据连接规模,具体为根据实际的数据筛选条件对结果行先进行过滤,然后在内部查询中结合索引完成优化。

好了,回来上面的问题:

select Student.name from Student inner join Score on Student.id=Score.s_id where Score.c_id=1 and Score.score=100

笔者之前看过资料,一般数据库进行join连接时会进行笛卡尔积过程,on字段作为行连接时的判断条件,最后再利用where条件进行结果行的筛选,具体逻辑过程为:

//外部先对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分析,我们看到执行过程都利用了两个表的索引结构:
在这里插入图片描述
Score表利用了组合索引index_cid_score,我们可以猜想到引擎是先尝试对where条件进行了先行判断,然后再对结果集和Student表进行连接操作,此连接过程中我们发现Student表有利用到主键索引,所以同样猜测on关键字的匹配条件被应用到Student表的查询计划中,逻辑过程这样描述

//外部先对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分析的结果。然而,笔者思考的一个问题是,对于Mysql来说并不是说join连接就一定能满足优化需求,一方面不同的引擎、不同的Mysql版本所采用的优化手段都可能存在差异,这没有一个固定标准,应对于这种变化在实际的业务处理中还得多结合Explain进行分析。笔者针对本次示例在Mysql 5.6版本也尝试执行了一下,发现对于sql:

select Student.name from Student where Student.id in (select s_id from Score where c_id = 1 and score = 100 )

在同样的索引构建下,Explain 分析结果为:
在这里插入图片描述
Score查询计划的id值为2,拥有更高的执行优先级,但select_type出现了之前在Mysql5.5没有过的字眼:MATERIALIZED,我们先看下执行结果:
在这里插入图片描述
这和我们在Mysql5.5版本关于join连接的结果是一样的!回到MATERIALIZED的思考,MATERIALIZED的官方描述是:

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.

大致意思是,优化控制器为了让子查询更高效,会将子查询的结果生成一个临时表,一般放置在内存中,同时对临时表生成相应的哈希索引来提高内存查询效率,示例的逻辑过程可以这样描述:

//先对子查询的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进行优化后的表达形式,在控制台一次输入两个语句:

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形式:

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方法进行分析!

到这里,整个示例的优化过程告一段落了,无论实际环境的查询需求多么复杂我们都可以先尝试进行查询计划的划分,观察各个计划的执行优先级,然后了解出引擎内部的执行逻辑,最后算出整体的查询成本一步步调整优化,大部分情况下笔者屡试不爽

2. 总结

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

已标记关键词 清除标记
<p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">ClickHouse是一个用于OLAP的数据分析引擎,由俄罗斯搜索巨头Yandex公司开源。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">1、可支持PB级超大容量的数据库管理系统。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">2、基于SQL语句, 使用成本低。 </p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">3、超亿级数据量分析的秒级响应,计算性能横向扩展。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">4、海量数据即查即用。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">5、提供数据的预聚合能力,进一步提升数据查询的效率。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">6、列式存储, 数据压缩,降低磁盘IO和网络IO,提升计算性能,节约70%物理存储。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">7、支持副本, 实现跨机房的数据容灾。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;"><br style="font-family: 'Helvetica Neue', Helvetica, 'Hiragino Sans GB', Arial, sans-serif; margin: 0px; padding: 0px;" />课程基于ClickHouse的**的20.x版本,从理论实践,由浅入深详解ClickHouse的原理和使用。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">1. 本课程从零开始,有Linux操作系统和SQL使用经验即可。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">2. 课程以案例驱动,包含大量的实践案例。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">3. 将ClickHouse与MySQL、Kafka、HDFS等第三方系统集成,增强ClickHouse的功能。</p> <p style="font-family: tahoma, 微软雅黑, arial, 宋体; margin: 0px; padding: 0px; box-sizing: border-box; border: 0px; outline: 0px; font-size: 14px; vertical-align: baseline; background: #ffffff; list-style-type: none; color: #333333; word-spacing: 1px;">4. 课程覆盖面广,既有基础功能的详细讲解, 也包含了集群部署、查询优化、运维管理等高级内容。</p>
相关推荐
CruiseYoung提供的带有详细书签的电子书籍目录 http://blog.csdn.net/fksec/article/details/7888251 深入理解MySQL(MySQL核心开发人员力作) 基本信息 原书名: Expert MySQL 原出版社: Apress 作者: (美)Charles A. Bell    译者: 杨涛 王建桥 杨晓云 韩兰 丛书名: 图灵程序设计丛书 数据库 出版社:人民邮电出版社 ISBN:9787115189103 上架时间:2009-12-31 出版日期:2010 年1月 开本:16开 页码:467 版次:1-1 编辑推荐    MySQL核心开发人员力作    带你深入MySQL源代码和底层架构    身临其境,透彻掌握数据库理论实践 内容简介    本书深入源代码,剖析mysql数据库系统的架构,并提供了分析、集成和修改mysql源代码的专家级建议。本书分三个部分:第一部分介绍开发和修改开源系统的概念,提供探讨更高级数据库概念所需的工具和资源;第二部分讨论mysql系统,阐明如何修改mysql源码,如何将mysql系统作为嵌入式数据库系统;第三部分更深入地探讨了mysql系统,讲述数据库工作的内部机理。    本书面向mysql数据库开发人员。 作译者 作者: Charles A.Bell    Charles A.Bell,MySQL核心开发人员,目前是Sun公司高级软件工程师;同时也是弗吉尼亚邦大学的客座教授,主要是为研究生讲授计算机科学课程。他主要从事新兴技术的研究,研究方向包括数据库系统、版本系统、语义网和敏捷软件开发等。 目录 封面 -10 封底 468 扉页 -9 版权 -8 前言 -7 目录 -4 第一部分 MySQL开发入门 1 第1章 MySQL与开源运动 2 1.1 什么是开源软件 2 1.1.1 为什么要使用开源软件 4 1.1.2 开源软件是否对商业软件构成真正的威胁 7 1.1.3 法律问题与《GNU宣言》 8 1.1.4 将开源进行到底 10 1.2 用MySQL进行开发 11 1.2.1 为什么修改MySQL 13 1.2.2 MySQL里哪些可以修改,有什么限制 14 1.2.3 MySQL的许可证问题 15 1.2.4 到底能否修改MySQL 16 1.2.5 修改MySQL的指导原则 17 1.3 实际的例子:TiVo 18 1.4 小结 19 第2章 数据库系统剖析 20 2.1 数据库系统的体系结构 20 2.2 数据库系统的类型 20 2.2.1 面向对象数据库系统 20 2.2.2 对象关系数据库系统 21 2.2.3 关系数据库系统 23 2.3 关系数据库系统的体系结构 24 2.3.1 客户端应用程序 25 2.3.2 查询接口 26 2.3.3 查询处理 27 2.3.4 查询优化器 29 2.3.5 查询的内部示 31 2.3.6 查询的执行 32 2.3.7 文件访问 33 2.3.8 查询结果 35 2.3.9 关系数据库的体系结构小结 35 2.4 MySQL数据库系统 35 2.4.1 MySQL系统体系结构 36 2.4.2 SQL接口 37 2.4.3 解析器 38 2.4.4 查询优化器 39 2.4.5 查询的执行 40 2.4.6 查询缓存 40 2.4.7 缓存和缓冲区 42 2.4.8 通过插件式存储引擎访问文件 43 2.5 小结 50 第3章 MySQL源代码 51 3.1 预备知识 51 3.1.1 了解许可证 51 3.1.2 获得MySQL源代码 52 3.2 MySQL源代码 56 3.2.1 预备知识 57 3.2.2 main()函数 59 3.2.3 处理连接和创建线程 62 3.2.4 解析查询 69 3.2.5 优化查询的准备工作 75 3.2.6 优化查询 78 3.2.7 执行查询 80 3.2.8 辅助库 82 3.2.9 重要的类和结构 83 3.3 编程指导 88 3.3.1 总体指导 89 3.3.2 文档 89 3.3.3 函数和参数 91 3.3.4 命名约定 92 3.3.5 分隔与缩进 92 3.3.6 文档工具 93 3.3.7 保持工作记录的习惯 95 3.3.8 追踪变化 95 3.4 第
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页