第17章 神兵利器-optimizer trace表的神器功效

对于MySQL 5.6以及之前的版本来说，查询优化器就像是一个黑盒子一样，你只能通过EXPLAIN语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。这对于一部分喜欢刨根问底的小伙伴来说简直是灾难：“我就觉得使用其他的执行方案比EXPLAIN输出的这种方案强，凭什么优化器做的决定和我想的不一样呢？”

在MySQL 5.6以及之后的版本中，设计MySQL的大佬贴心的为这部分小伙伴提出了一个optimizer trace的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量optimizer_trace决定，我们看一下： mysql> SHOW VARIABLES LIKE 'optimizer_trace'; +-----------------+--------------------------+ | Variable_name | Value | +-----------------+--------------------------+ | optimizer_trace | enabled=off,one_line=off | +-----------------+--------------------------+ 1 row in set (0.02 sec) 可以看到enabled值为off，表明这个功能默认是关闭的。 小贴士：one_line的值是控制输出格式的，如果为on那么所有输出都将在一行中展示，不适合人阅读，所以我们就保持其默认值为off吧。 如果想打开这个功能，必须首先把enabled的值改为on，就像这样： mysql> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) 然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程。这个OPTIMIZER_TRACE表有4个列，分别是： - QUERY：表示我们的查询语句。 - TRACE：表示优化过程的JSON格式文本。 - MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。 - INSUFFICIENT_PRIVILEGES：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1，我们暂时不关心这个字段的值。

完整的使用optimizer trace功能的步骤总结如下： ```

1. 打开optimizer trace功能 (默认情况下它是关闭的):

SET optimizer_trace=“enabled=on”;

2. 这里输入你自己的查询语句

SELECT …;

3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

第16章 查询优化的百科全书-Explain详解（下）

执行计划输出中各列详解#

本章紧接着上一节的内容，继续介绍EXPLAIN语句输出的各个列的意思。

Extra#

顾名思义，Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了（都介绍了感觉我们的文章就跟文档差不多了～），所以我们只挑一些平时常见的或者比较重要的额外信息介绍给大家。

No tables used

当查询语句的没有FROM子句时将会提示该额外信息，比如： mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)

第15章 查询优化的百科全书-Explain详解（上）

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。设计MySQL的大佬贴心的为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，本章的内容就是为了帮助大家看懂EXPLAIN语句的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前面加一个EXPLAIN，就像这样：

mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.01 sec) 然后这输出的一大坨东西就是所谓的执行计划，我的任务就是带领大家看懂这一大坨东西里边的每个列都是干什么用的，以及在这个执行计划的辅助下，我们应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句前面都可以加上EXPLAIN这个词儿，用来查看这些语句的执行计划，不过我们这里对SELECT语句更感兴趣，所以后边只会以SELECT语句为例来描述EXPLAIN语句的用法。为了让大家先有一个感性的认识，我们把EXPLAIN语句输出的各个列的作用先大致罗列一下： 列名 描述 id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id select_type SELECT关键字对应的那个查询的类型 table 表名 partitions 匹配的分区信息 type 针对单表的访问方法 possible_keys 可能用到的索引 key 实际上使用的索引 key_len 实际使用到的索引长度 ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 rows 预估的需要读取的记录条数 filtered 某个表经过搜索条件过滤后剩余记录条数的百分比 Extra 一些额外的信息
需要注意的是，大家如果看不懂上面输出列含义，那是正常的，千万不要纠结～。我在这里把它们都列出来只是为了描述一个轮廓，让大家有一个大致的印象，下面会细细道来，等会儿说完了不信你不会～ 为了故事的顺利发展，我们还是要请出我们前面已经用了n遍的single_table表，为了防止大家忘了，再把它的结构描述一遍： CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8; 我们仍然假设有两个和single_table表构造一模一样的s1、s2表，而且这两个表里边儿有10000条记录，除id列外其余的列都插入随机值。为了让大家有比较好的阅读体验，我们下面并不准备严格按照EXPLAIN输出列的顺序来介绍这些列分别是干嘛的，大家注意一下就好了。

第14章 不好看就要多整容-MySQL基于规则的优化（内含关于子查询优化二三事儿）

大家别忘了MySQL本质上是一个软件，设计MySQL的大佬并不能要求使用这个软件的人个个都是数据库高高手，就像我写这本书的时候并不能要求各位在学之前就会了里边儿的知识。 吐槽一下：都会了的人谁还看呢，难道是为了精神上受感化？ 也就是说我们无法避免某些同学写一些执行起来十分耗费性能的语句。即使是这样，设计MySQL的大佬还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写（就是人家觉得你写的语句不好，自己再重写一遍）。本章详细介绍一下一些比较重要的重写规则。

条件化简#

我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。为了方便大家理解，我们后边举例子的时候都使用诸如a、b、c之类的简单字母代表某个表的列名。

移除不必要的括号#

有时候表达式里有许多无用的括号，比如这样： ((a = 5 AND b = c) OR ((a > c) AND (c < 5))) 看着就很烦，优化器会把那些用不到的括号给干掉，就是这样： (a = 5 and b = c) OR (a > c AND c < 5)

常量传递（constant_propagation）#

有时候某个表达式是某个列和某个常量做等值匹配，比如这样： a = 5 当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中的a的值替换为5，比如这样： a = 5 AND b > a 就可以被转换为： a = 5 AND b > 5 小贴士：为什么用OR连接起来的表达式就不能进行常量传递呢？自己想想～

等值传递（equality_propagation）#

有时候多个列之间存在等值匹配的关系，比如这样： a = b and b = c and c = 5 这个表达式可以被简化为： a = 5 and b = 5 and c = 5

第13章 兵马未动，粮草先行-InnoDB统计数据是如何收集的

我们前面介绍查询成本的时候经常用到一些统计数据，比如通过SHOW TABLE STATUS可以看到关于表的统计数据，通过SHOW INDEX可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？本章将聚焦于InnoDB存储引擎的统计数据收集策略，看完本章大家就会明白为什么前面老说InnoDB的统计信息是不精确的估计值了（言下之意就是我们不打算介绍MyISAM存储引擎统计数据的收集和存储方式，有想了解的同学自己个儿看看文档）。

两种不同的统计数据存储方式#

InnoDB提供了两种存储统计数据的方式：

永久性的统计数据

这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

非永久性的统计数据

这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

设计MySQL的大佬们给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。

不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式： CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0); ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0); 当STATS_PERSISTENT=1时，表明我们想把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明我们想把该表的统计数据临时的存储到内存中。如果我们在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值。

基于磁盘的永久性统计数据#

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：

mysql> SHOW TABLES FROM mysql LIKE 'innodb%'; +---------------------------+ | Tables_in_mysql (innodb%) | +---------------------------+ | innodb_index_stats | | innodb_table_stats | +---------------------------+ 2 rows in set (0.01 sec) 可以看到，这两个表都位于mysql系统数据库下面，其中： - innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。 - innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

我们下面的任务就是看一下这两个表里边都有什么以及表里的数据是如何生成的。

innodb_table_stats#

直接看一下这个innodb_table_stats表中的各个列都是干嘛的： 字段名 描述 database_name 数据库名 table_name 表名 last_update 本条记录最后更新时间 n_rows 表中记录的条数 clustered_index_size 表的聚簇索引占用的页面数量 sum_of_other_index_sizes 表的其他索引占用的页面数量
注意这个表的主键是(database_name,table_name)，也就是innodb_table_stats表的每条记录代表着一个表的统计信息。我们直接看一下这个表里的内容： mysql> SELECT * FROM mysql.innodb_table_stats; +---------------+---------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+---------------+---------------------+--------+----------------------+--------------------------+ | mysql | gtid_executed | 2018-07-10 23:51:36 | 0 | 1 | 0 | | sys | sys_config | 2018-07-10 23:51:38 | 5 | 1 | 0 | | xiaohaizi | single_table | 2018-12-10 17:03:13 | 9693 | 97 | 175 | +---------------+---------------+---------------------+--------+----------------------+--------------------------+ 3 rows in set (0.01 sec) 可以看到我们熟悉的single_table表的统计信息就对应着mysql.innodb_table_stats的第三条记录。几个重要统计信息项的值如下： - n_rows的值是9693，表明single_table表中大约有9693条记录，注意这个数据是估计值。 - clustered_index_size的值是97，表明single_table表的聚簇索引占用97个页面，这个值是也是一个估计值。 - sum_of_other_index_sizes的值是175，表明single_table表的其他索引一共占用175个页面，这个值是也是一个估计值。

第12章 谁最便宜就选谁-MySQL基于成本的优化

什么是成本#

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在MySQL中一条查询语句的执行成本是由下面这两个方面组成的：

I/O成本

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大佬规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，其余的成本常数我们后边再说。 小贴士：需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

单表查询的成本#

准备工作#

为了故事的顺利发展，我们还得把之前用到的single_table表搬来，怕大家忘了这个表长什么样，再给大家抄一遍： CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8; 还是假设这个表里边儿有10000条记录，除id列外其余的列都插入随机值。下面正式开始我们的表演。

基于成本的优化步骤#

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样： 1. 根据搜索条件，找出所有可能使用的索引 2. 计算全表扫描的代价 3. 计算使用不同索引执行查询的代价 4. 对比各种执行方案的代价，找出成本最低的那一个

下面我们就以一个实例来分析一下这些步骤，单表查询语句如下： SELECT * FROM single_table WHERE key1 IN ('a', 'b', 'c') AND key2 > 10 AND key2 < 1000 AND key3 > key2 AND key_part1 LIKE '%hello%' AND common_field = '123'; 乍看上去有点儿复杂，我们一步一步分析一下。

第10章 条条大路通罗马-单表访问方法

对于我们这些MySQL的使用者来说，MySQL其实就是一个软件，平时用的最多的就是查询功能。DBA时不时丢过来一些慢查询语句让优化，我们如果连查询是怎么执行的都不清楚还优化个毛线，所以是时候掌握真正的技术了。我们在第一章的时候就曾说过，MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是什么样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。不过查询优化这个主题有点儿大，在学会跑之前还得先学会走，所以本章先来看看MySQL怎么执行单表查询（就是FROM子句后边只有一个表，最简单的那种查询～）。不过需要强调的一点是，在学习本章前务必看过前面关于记录结构、数据页结构以及索引的部分，如果你不能保证这些东西已经完全掌握，那么本章不适合你。

为了故事的顺利发展，我们先得有个表： CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8; 我们为这个single_table表建立了1个聚簇索引和4个二级索引，分别是：

为id列建立的聚簇索引。

为key1列建立的idx_key1二级索引。

为key2列建立的idx_key2二级索引，而且该索引是唯一二级索引。

为key3列建立的idx_key3二级索引。

为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是一个联合索引。

然后我们需要为这个表插入10000行记录，除id列外其余的列都插入随机值就好了，具体的插入语句我就不写了，自己写个程序插入吧（id列是自增主键列，不需要我们手动插入）。

访问方法（access method）的概念#

想必各位都用过高德地图来查找到某个地方的路线吧（此处没有为高德地图打广告的意思，他们没给我钱，大家用百度地图也可以啊），如果我们搜西安钟楼到大雁塔之间的路线的话，地图软件会给出n种路线供我们选择，如果我们实在闲的没事儿干并且足够有钱的话，还可以用南辕北辙的方式绕地球一圈到达目的地。也就是说，不论采用哪一种方式，我们最终的目标就是到达大雁塔这个地方。回到MySQL中来，我们平时所写的那些查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则，至于MySQL背地里是怎么把查询结果搞出来的那是MySQL自己的事儿。对于单个表的查询来说，设计MySQL的大佬把查询的执行方式大致分为下面两种：

使用全表扫描进行查询

这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是什么查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。

使用索引进行查询

因为直接使用全表扫描的方式执行查询要遍历好多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式五花八门，又可以细分为许多种类：

+  

针对主键或唯一二级索引的等值查询

第9章 存放页的大池子-InnoDB的表空间

通过前面儿的内容大家知道，表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件。大家可以把表空间想象成被切分为许许多多个页的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。本章内容会深入到表空间的各个细节中，带领大家在InnoDB存储结构的池子中畅游。由于本章中将会涉及比较多的概念，虽然这些概念都不难，但是却相互依赖，所以奉劝大家在看的时候：

不要跳着看！

不要跳着看！

不要跳着看！

回忆一些旧知识#

页类型#

再一次强调，InnoDB是以页为单位管理存储空间的，我们的聚簇索引（也就是完整的表数据）和其他的二级索引都是以B+树的形式保存到表空间的，而B+树的节点就是数据页。我们前面说过，这个数据页的类型名其实是：FIL_PAGE_INDEX，除了这种存放索引数据的页类型之外，InnoDB也为了不同的目的设计了若干种不同类型的页，为了唤醒大家的记忆，我们再一次把各种常用的页类型提出来： 类型名称 十六进制 描述 FIL_PAGE_TYPE_ALLOCATED 0x0000 最新分配，还没使用 FIL_PAGE_UNDO_LOG 0x0002 Undo日志页 FIL_PAGE_INODE 0x0003 段信息节点 FIL_PAGE_IBUF_FREE_LIST 0x0004 Insert Buffer空闲列表 FIL_PAGE_IBUF_BITMAP 0x0005 Insert Buffer位图 FIL_PAGE_TYPE_SYS 0x0006 系统页 FIL_PAGE_TYPE_TRX_SYS 0x0007 事务系统数据 FIL_PAGE_TYPE_FSP_HDR 0x0008 表空间头部信息 FIL_PAGE_TYPE_XDES 0x0009 扩展描述页 FIL_PAGE_TYPE_BLOB 0x000A BLOB页 FIL_PAGE_INDEX 0x45BF 索引页，也就是我们所说的数据页
因为页类型前面都有个FIL_PAGE或者FIL_PAGE_TYPE的前缀，为简便起见我们后边介绍页类型的时候就把这些前缀省略掉了，比方说FIL_PAGE_TYPE_ALLOCATED类型称为ALLOCATED类型，FIL_PAGE_INDEX类型称为INDEX类型。

页通用部分#

我们前面说过数据页，也就是INDEX类型的页由7个部分组成，其中的两个部分是所有类型的页都通用的。当然我不能寄希望于你把我说的话都记住，所以在这里重新强调一遍，任何类型的页都有下面这种通用的结构：

从上图中可以看出，任何类型的页都会包含这两个部分：

File Header：记录页的一些通用信息

File Trailer：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。

对于File Trailer我们不再做过多强调，全部忘记了的话可以到将数据页的那一章回顾一下。我们这里再强调一遍File Header的各个组成部分： 名称 占用空间大小 描述 FIL_PAGE_SPACE_OR_CHKSUM 4字节 页的校验和（checksum值） FIL_PAGE_OFFSET 4字节 页号 FIL_PAGE_PREV 4字节 上一个页的页号 FIL_PAGE_NEXT 4字节 下一个页的页号 FIL_PAGE_LSN 8字节 页被最后修改时对应的日志序列位置（英文名是：Log Sequence Number） FIL_PAGE_TYPE 2字节 该页的类型 FIL_PAGE_FILE_FLUSH_LSN 8字节 仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值 FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4字节 页属于哪个表空间
现在除了名称里边儿带有LSN的两个字段大家可能看不懂以外，其他的字段肯定都是倍儿熟了，不过我们仍要强调这么几点：

第8章 数据的家-MySQL的数据目录

数据库和文件系统的关系#

我们知道像InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又被称为文件系统，所以用专业一点的话来表述就是：像 InnoDB 、 MyISAM 这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。本章就是要介绍一下InnoDB和MyISAM这两个存储引擎的数据如何在文件系统中存储的。

MySQL数据目录#

MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为数据目录，我们下面就要详细唠唠这个目录下具体都有哪些重要的东西。

数据目录和安装目录的区别#

我们之前只接触过MySQL的安装目录（在安装MySQL的时候我们可以自己指定），我们重点强调过这个安装目录下非常重要的bin目录，它里边存储了许多关于控制客户端程序和服务器程序的命令（许多可执行文件，比如mysql，mysqld，mysqld_safe等等等等好几十个）。而数据目录是用来存储MySQL在运行过程中产生的数据，一定要和本章要讨论的安装目录区别开！一定要区分开！一定要区分开！一定要区分开！

如何确定MySQL中的数据目录#

那说了半天，到底MySQL把数据都存到哪个路径下呢？其实数据目录对应着一个系统变量datadir，我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了： mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/var/mysql/ | +---------------+-----------------------+ 1 row in set (0.00 sec) 从结果中可以看出，在我的计算机上MySQL的数据目录就是/usr/local/var/mysql/，你用你的计算机试试呗～

数据目录的结构#

MySQL在运行过程中都会产生哪些数据呢？当然会包含我们创建的数据库、表、视图和触发器等等的用户数据，除了这些用户数据，为了程序更好的运行，MySQL也会创建一些其他的额外数据，我们接下来细细的品味一下这个数据目录下的内容。

数据库在文件系统中的表示#

每当我们使用CREATE DATABASE 数据库名语句创建一个数据库的时候，在文件系统上实际发生了什么呢？其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时，MySQL会帮我们做这两件事儿：

在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹）。

在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是什么。

比方说我们查看一下在我的计算机上当前有哪些数据库： mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | charset_demo_db | | dahaizi | | mysql | | performance_schema | | sys | | xiaohaizi | +--------------------+ 7 rows in set (0.00 sec) 可以看到在我的计算机上当前有7个数据库，其中charset_demo_db、dahaizi和xiaohaizi数据库是我们自定义的，其余4个数据库是属于MySQL自带的系统数据库。我们再看一下我的计算机上的数据目录下的内容： ``` . ├── auto.cnf ├── ca-key.pem ├── ca.pem ├── charset_demo_db ├── client-cert.pem ├── client-key.pem ├── dahaizi ├── ib_buffer_pool ├── ib_logfile0 ├── ib_logfile1 ├── ibdata1 ├── ibtmp1 ├── mysql ├── performance_schema ├── private_key.pem ├── public_key.pem ├── server-cert.pem ├── server-key.pem ├── sys ├── xiaohaizideMacBook-Pro.local.err ├── xiaohaizideMacBook-Pro.local.pid └── xiaohaizi

第7章 好东西也得先学会怎么用-B+树索引的使用

我们前面详细、详细又详细的介绍了InnoDB存储引擎的B+树索引，我们必须熟悉下面这些结论：

每个索引都对应一棵B+树，B+树分为好多层，最下面一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。

InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前面的列排序，如果该列值相同，再按照联合索引后边的列排序。

通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

如果你读上面的几点结论有些任何一点点疑惑的话，那下面的内容不适合你，回过头先去看前面的内容去。

索引的代价#

在熟悉了B+树索引原理之后，本篇文章的主题是介绍如何更好的使用索引，虽然索引是个好东西，可不能乱建，在介绍如何更好的使用索引之前先要了解一下使用这玩意儿的代价，它在空间和时间上都会拖后腿：

空间上的代价

这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那可是很大的一片存储空间呢。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收什么的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这还能不给性能拖后腿么？

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们先得学学这些索引在哪些条件下起作用的。

B+树索引适用的条件#

下面我们将介绍许多种让B+树索引发挥最大效能的技巧和注意事项，不过大家要清楚，所有的技巧都是源自你对B+树索引本质的理解，所以如果你还不能保证对B+树索引充分的理解，那么再次建议回过头把前面的内容看完了再来，要不然读文章对你来说是一种折磨。首先，B+树索引并不是万能的，并不是所有的查询语句都能用到我们建立的索引。下面介绍几个我们可能使用B+树索引来进行查询的情况。为了故事的顺利发展，我们需要先创建一个表，这个表是用来存储人的一些基本信息的： CREATE TABLE person_info( id INT NOT NULL auto_increment, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR(11) NOT NULL, country varchar(100) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name, birthday, phone_number) ); 对于这个person_info表我们需要注意两点：

表中的主键是id列，它存储一个自动递增的整数。所以InnoDB存储引擎会自动为id列建立聚簇索引。

我们额外定义了一个二级索引idx_name_birthday_phone_number，它是由3个列组成的联合索引。所以在这个索引对应的B+树的叶子节点处存储的用户记录只保留name、birthday、phone_number这三个列的值以及主键id的值，并不会保存country列的值。

从这两点注意中我们可以再次看到，一个表中有多少索引就会建立多少棵B+树，person_info表会为聚簇索引和idx_name_birthday_phone_number索引建立2棵B+树。下面我们画一下索引idx_name_birthday_phone_number的示意图，不过既然我们已经掌握了InnoDB的B+树索引原理，那我们在画图的时候为了让图更加清晰，所以在省略一些不必要的部分，比如记录的额外信息，各页面的页号等等，其中内节点中目录项记录的页号信息我们用箭头来代替，在记录结构中只保留name、birthday、phone_number、id这四个列的真实数据值，所以示意图就长这样（留心的同学看出来了，这其实和《高性能MySQL》里举的例子的图差不多，我觉得这个例子特别好，所以就借鉴了一下）：

为了方便大家理解，我们特意标明了哪些是内节点，哪些是叶子节点。再次强调一下，内节点中存储的是目录项记录，叶子节点中存储的是用户记录（由于不是聚簇索引，所以用户记录是不完整的，缺少country列的值）。从图中可以看出，这个idx_name_birthday_phone_number索引对应的B+树中页面和记录的排序方式就是这样的：