15查询优化的百科全书 —— Explain 详解（上）
Explain 详解（上）标签： MySQL 是怎样运行的
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。设计MySQL的大叔贴心的为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，本章的内容就是为了帮助大家看懂EXPLAIN语句的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个EXPLAIN，就像这样：
1234567mysql> EXPLAIN SELECT 1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | partitions | type | pos ...
14不好看就要多整容 —— MySQL 基于规则的优化（内含关于子查询优化二三事儿）
基于规则的优化标签： MySQL 是怎样运行的
大家别忘了MySQL本质上是一个软件，设计MySQL的大叔并不能要求使用这个软件的人个个都是数据库高高手，就像我写这本书的时候并不能要求各位在学之前就会了里边儿的知识。
1吐槽一下：都会了的人谁还看呢，难道是为了精神上受感化？
也就是说我们无法避免某些同学写一些执行起来十分耗费性能的语句。即使是这样，设计MySQL的大叔还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写（就是人家觉得你写的语句不好，自己再重写一遍）。本章详细唠叨一下一些比较重要的重写规则。
条件化简我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。为了方便大家理解，我们后边举例子的时候都使用诸如a、b、c之类的简单字母代表某个表的列名。
移除不必要的括号有时候表达式里有许多无用的括号，比如这样：
1((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
看着就很烦，优化器会把那些用 ...
13兵马未动，粮草先行 —— InnoDB 统计数据是如何收集的
InnoDB 统计数据是如何收集的标签： MySQL 是怎样运行的
我们前边唠叨查询成本的时候经常用到一些统计数据，比如通过SHOW TABLE STATUS可以看到关于表的统计数据，通过SHOW INDEX可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？本章将聚焦于InnoDB存储引擎的统计数据收集策略，看完本章大家就会明白为啥前边老说InnoDB的统计信息是不精确的估计值了（言下之意就是我们不打算介绍MyISAM存储引擎统计数据的收集和存储方式，有想了解的同学自己个儿看看文档哈）。
两种不同的统计数据存储方式InnoDB提供了两种存储统计数据的方式：
永久性的统计数据
这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
非永久性的统计数据
这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。
设计MySQL的大叔们给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5 ...
12谁最便宜就选谁 —— MySQL 基于成本的优化
基于成本的优化标签： MySQL 是怎样运行的
什么是成本我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：
I/O成本
我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大叔规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，其余的成本常数我们后边再说哈。
123小贴士：需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 ...
11两个表的亲密接触 —— 连接的原理
连接的原理标签： MySQL 是怎样运行的
搞数据库一个避不开的概念就是Join，翻译成中文就是连接。相信很多小伙伴在初学连接的时候有些一脸懵逼，理解了连接的语义之后又可能不明白各个表中的记录到底是怎么连起来的，以至于在使用的时候常常陷入下边两种误区：
误区一：业务至上，管他三七二十一，再复杂的查询也用在一个连接语句中搞定。
误区二：敬而远之，上次 DBA 那给报过来的慢查询就是因为使用了连接导致的，以后再也不敢用了。
所以本章就来扒一扒连接的原理。考虑到一部分小伙伴可能忘了连接是个啥或者压根儿就不知道，为了节省他们百度或者看其他书的宝贵时间以及为了我的书凑字数，我们先来介绍一下 MySQL 中支持的一些连接语法。
连接简介连接的本质为了故事的顺利发展，我们先建立两个简单的表并给它们填充一点数据：
12345678910111213mysql> CREATE TABLE t1 (m1 int, n1 char(1));Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE t2 (m2 int, n2 cha ...
10条条大路通罗马 —— 单表访问方法
单表访问方法标签： MySQL 是怎样运行的
对于我们这些MySQL的使用者来说，MySQL其实就是一个软件，平时用的最多的就是查询功能。DBA时不时丢过来一些慢查询语句让优化，我们如果连查询是怎么执行的都不清楚还优化个毛线，所以是时候掌握真正的技术了。我们在第一章的时候就曾说过，MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。不过查询优化这个主题有点儿大，在学会跑之前还得先学会走，所以本章先来瞅瞅MySQL怎么执行单表查询（就是FROM子句后边只有一个表，最简单的那种查询～）。不过需要强调的一点是，在学习本章前务必看过前边关于记录结构、数据页结构以及索引的部分，如果你不能保证这些东西已经完全掌握，那么本章不适合你。
为了故事的顺利发展，我们先得有个表：
123456789101112131415CREATE TABLE si ...
9存放页面的大池子 —— InnoDB 的表空间
InnoDB 的表空间标签： MySQL 是怎样运行的
通过前边儿的内容大家知道，表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件。大家可以把表空间想象成被切分为许许多多个页的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。本章内容会深入到表空间的各个细节中，带领大家在InnoDB存储结构的池子中畅游。由于本章中将会涉及比较多的概念，虽然这些概念都不难，但是却相互依赖，所以奉劝大家在看的时候：
不要跳着看！
不要跳着看！
不要跳着看！
回忆一些旧知识页面类型再一次强调，InnoDB 是以页为单位管理存储空间的，我们的聚簇索引（也就是完整的表数据）和其他的二级索引都是以B+树的形式保存到表空间的，而B+树的节点就是数据页。我们前边说过，这个数据页的类型名其实是：FIL_PAGE_INDEX，除了这种存放索引数据的页面类型之外，InnoDB 也为了不同的目的设计了若干种不同类型的页面，为了唤醒大家的记忆，我们再一次把各种常用的页面类型提出来： ...
8数据的家 —— MySQL 的数据目录
MySQL 的数据目录标签： MySQL 是怎样运行的
数据库和文件系统的关系我们知道像InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又被称为文件系统，所以用专业一点的话来表述就是：像 InnoDB 、 MyISAM 这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。本章就是要唠叨一下InnoDB和MyISAM这两个存储引擎的数据如何在文件系统中存储的。
MySQL数据目录MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为数据目录，我们下边就要详细唠唠这个目录下具体都有哪些重要的东西。
数据目录和安装目录的区别我们之前只接触过MySQL的安装目录（在安装MySQL的时候我们可以自己指定），我们重点强调过这个安装目录下非常重要的bin目录，它里边存储了许多关于控制客户端程序和服务器程序的命令（许多可执行文件，比如mys ...
7好东西也得先学会怎么用 —— B+ 树索引的使用
B+树索引的使用标签： MySQL 是怎样运行的
我们前边详细、详细又详细的唠叨了InnoDB存储引擎的B+树索引，我们必须熟悉下边这些结论：
每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录按照先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个 ...
6快速查询的秘籍 —— B+ 树索引
B+树索引标签： MySQL是怎样运行的
前边我们详细唠叨了InnoDB数据页的7个组成部分，知道了各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录（如果你对这段话有一丁点儿疑惑，那么接下来的部分不适合你，返回去看一下数据页结构吧）。页和记录的关系示意图如下：
其中页a、页b、页c … 页n 这些页可以不在物理结构上相连，只要通过双向链表相关联即可。
没有索引的查找本集的主题是索引，在正式介绍索引之前，我们需要了解一下没有索引的时候是怎么查找记录的。为了方便大家理解，我们下边先只唠叨搜索条件为对某个列精确匹配的情况，所谓精确匹配，就是搜索条件中用等于=连接起的表达式，比如这样：
1SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;
在一个页中的查找假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根 ...