第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+树中页面和记录的排序方式就是这样的：

第6章 快速查询的秘籍-B+树索引

前面我们详细介绍了InnoDB数据页的7个组成部分，知道了各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录（如果你对这段话有一丁点儿疑惑，那么接下来的部分不适合你，返回去看一下数据页结构吧）。页和记录的关系示意图如下：

其中页a、页b、页c … 页n 这些页可以不在物理结构上相连，只要通过双向链表相关联即可。

没有索引的查找#

本集的主题是索引，在正式介绍索引之前，我们需要了解一下没有索引的时候是怎么查找记录的。为了方便大家理解，我们下面先只介绍搜索条件为对某个列精确匹配的情况，所谓精确匹配，就是搜索条件中用等于=连接起的表达式，比如这样： SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

在一个页中的查找#

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

以主键为搜索条件

这个查找过程我们已经很熟悉了，可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以其他列作为搜索条件

对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找#

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

1. 定位到记录所在的页。
2. 从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚介绍过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。所以祖国和人民都在期盼一种能高效完成搜索的方法，索引同志就要亮相登台了。

索引#

为了故事的顺利发展，我们先建一个表： mysql> CREATE TABLE index_demo( -> c1 INT, -> c2 INT, -> c3 CHAR(1), -> PRIMARY KEY(c1) -> ) ROW_FORMAT = Compact; Query OK, 0 rows affected (0.03 sec) 这个新建的index_demo表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用Compact行格式来实际存储记录的。为了我们理解上的方便，我们简化了一下index_demo表的行格式示意图：

我们只在示意图里展示记录的这几个部分：

record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记录、3表示最大记录、1我们还没用过，等会再说～

next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，为了方便大家理解，我们都会用箭头来表明下一条记录是谁。

第5章 盛放记录的大盒子-InnoDB数据页结构

不同类型的页简介#

前面我们简单提了一下页的概念，它是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB。InnoDB为了不同的目的而设计了许多种不同类型的页，比如存放表空间头部信息的页，存放Insert Buffer信息的页，存放INODE信息的页，存放undo日志信息的页等等等等。当然了，如果我说的这些名词你一个都没有听过，就当我放了个屁吧～ 不过这没有一毛钱关系，我们今儿个也不准备说这些类型的页，我们聚焦的是那些存放我们表中记录的那种类型的页，官方称这种存放记录的页为索引（INDEX）页，鉴于我们还没有了解过索引是个什么东西，而这些表中的记录就是我们日常口中所称的数据，所以目前还是叫这种存放记录的页为数据页吧。

数据页结构的快速浏览#

数据页代表的这块16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示：

从图中可以看出，一个InnoDB数据页的存储空间大致被划分成了7个部分，有的部分占用的字节数是确定的，有的部分占用的字节数是不确定的。下面我们用表格的方式来大致描述一下这7个部分都存储一些什么内容（快速的瞅一眼就行了，后边会详细介绍的）： 名称 中文名 占用空间大小 简单描述 File Header 文件头部 38字节 页的一些通用信息 Page Header 页面头部 56字节 数据页专有的一些信息 Infimum + Supremum 最小记录和最大记录 26字节 两个虚拟的行记录 User Records 用户记录 不确定 实际存储的行记录内容 Free Space 空闲空间 不确定 页中尚未使用的空间 Page Directory 页面目录 不确定 页中的某些记录的相对位置 File Trailer 文件尾部 8字节 校验页是否完整
小贴士：我们接下来并不打算按照页中各个部分的出现顺序来依次介绍它们，因为各个部分中会出现很多大家目前不理解的概念，这会打击各位读文章的信心与兴趣，希望各位能接受这种拍摄手法～

记录在页中的存储#

在页的7个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

为了更好的管理在User Records中的这些记录，InnoDB可费了一番力气呢，在哪费力气了呢？不就是把记录按照指定的行格式一条一条摆在User Records部分么？其实这话还得从记录行格式的记录头信息中说起。

记录头信息的秘密#

为了故事的顺利发展，我们先创建一个表： mysql> CREATE TABLE page_demo( -> c1 INT, -> c2 INT, -> c3 VARCHAR(10000), -> PRIMARY KEY (c1) -> ) CHARSET=ascii ROW_FORMAT=Compact; Query OK, 0 rows affected (0.03 sec) 这个新创建的page_demo表有3个列，其中c1和c2列是用来存储整数的，c3列是用来存储字符串的。需要注意的是，我们把 c1 列指定为主键，所以在具体的行格式中InnoDB就没必要为我们去创建那个所谓的 row_id 隐藏列了。而且我们为这个表指定了ascii字符集以及Compact的行格式。所以这个表中记录的行格式示意图就是这样的：

第4章 从一条记录说起-InnoDB记录结构

准备工作#

到现在为止，MySQL对于我们来说还是一个黑盒，我们只负责使用客户端发送请求并等待服务器返回结果，表中的数据到底存到了哪里？以什么格式存放的？MySQL是以什么方式来访问的这些数据？这些问题我们统统不知道，对于未知领域的探索向来就是社会主义核心价值观中的一部分，作为新一代社会主义接班人，不把它们搞懂怎么支援祖国建设呢？

我们前面介绍请求处理过程的时候提到过，MySQL服务器上负责对表中数据的读取和写入工作的部分是存储引擎，而服务器又支持不同类型的存储引擎，比如InnoDB、MyISAM、Memory什么的，不同的存储引擎一般是由不同的人为实现不同的特性而开发的，真实数据在不同存储引擎中存放的格式一般是不同的，甚至有的存储引擎比如Memory都不用磁盘来存储数据，也就是说关闭服务器后表中的数据就消失了。由于InnoDB是MySQL默认的存储引擎，也是我们最常用到的存储引擎，我们也没有那么多时间去把各个存储引擎的内部实现都看一遍，所以本集要介绍的是使用InnoDB作为存储引擎的数据存储结构，了解了一个存储引擎的数据存储结构之后，其他的存储引擎都是依葫芦画瓢，等我们用到了再说。

InnoDB页简介#

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

InnoDB行格式#

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。设计InnoDB存储引擎的大佬们到现在为止设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式，随着时间的推移，他们可能会设计出更多的行格式，但是不管怎么变，在原理上大体都是相同的。

指定行格式的语法#

我们可以在创建或修改表的语句中指定行格式： ``` CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称

ALTER TABLE 表名 ROW_FORMAT=行格式名称 比如我们在xiaohaizi数据库里创建一个演示用的表record_format_demo，可以这样指定它的行格式： mysql> USE xiaohaizi; Database changed

mysql> CREATE TABLE record_format_demo ( -> c1 VARCHAR $10), -> c2 VARCHAR(10) NOT NULL, -> c3 CHAR(10), -> c4 VARCHAR(10) ->$ CHARSET=ascii ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.03 sec) 可以看到我们刚刚创建的这个表的行格式就是Compact，另外，我们还显式指定了这个表的字符集为ascii，因为ascii字符集只包括空格、标点符号、数字、大小写字母和一些不可见字符，所以我们的汉字是不能存到这个表里的。我们现在向这个表中插入两条记录： mysql> INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES(‘aaaa’, ‘bbb’, ‘cc’, ’d’), (’eeee’, ‘fff’, NULL, NULL); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 现在表中的记录就是这个样子的： mysql> SELECT * FROM record_format_demo; +——+—–+——+——+ | c1 | c2 | c3 | c4 | +——+—–+——+——+ | aaaa | bbb | cc | d | | eeee | fff | NULL | NULL | +——+—–+——+——+ 2 rows in set (0.00 sec)

第3章 乱码的前世今生-字符集和比较规则

字符集和比较规则简介#

字符集简介#

我们知道在计算机中只能存储二进制数据，那该怎么存储字符串呢？当然是建立字符与二进制数据的映射关系了，建立这个关系最起码要搞清楚两件事儿：

1. 你要把哪些字符映射成二进制数据？ 也就是界定清楚字符范围。
2. 怎么映射？ 将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码。

人们抽象出一个字符集的概念来描述某个字符范围的编码规则。比方说我们来自定义一个名称为xiaohaizi的字符集，它包含的字符范围和编码规则如下：

包含字符'a'、'b'、'A'、'B'。

编码规则如下：

采用1个字节编码一个字符的形式，字符和字节的映射关系如下： 'a' -> 00000001 (十六进制：0x01) 'b' -> 00000010 (十六进制：0x02) 'A' -> 00000011 (十六进制：0x03) 'B' -> 00000100 (十六进制：0x04)

有了xiaohaizi字符集，我们就可以用二进制形式表示一些字符串了，下面是一些字符串用xiaohaizi字符集编码后的二进制表示： 'bA' -> 0000001000000011 (十六进制：0x0203) 'baB' -> 000000100000000100000100 (十六进制：0x020104) 'cd' -> 无法表示，字符集xiaohaizi不包含字符'c'和'd'

比较规则简介#

在我们确定了xiaohaizi字符集表示字符的范围以及编码规则后，怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小，比方说字符'a'的编码为0x01，字符'b'的编码为0x02，所以'a'小于'b'，这种简单的比较规则也可以被称为二进制比较规则，英文名为binary collation。

二进制比较规则是简单，但有时候并不符合现实需求，比如在很多场合对于英文字符我们都是不区分大小写的，也就是说'a'和'A'是相等的，在这种场合下就不能简单粗暴的使用二进制比较规则了，这时候我们可以这样指定比较规则：

1. 将两个大小写不同的字符全都转为大写或者小写。
2. 再比较这两个字符对应的二进制数据。

这是一种稍微复杂一点点的比较规则，但是实际生活中的字符不止英文字符一种，比如我们的汉字有几万之多，对于某一种字符集来说，比较两个字符大小的规则可以制定出很多种，也就是说同一种字符集可以有多种比较规则，我们稍后就要介绍各种现实生活中用的字符集以及它们的一些比较规则。

一些重要的字符集#

不幸的是，这个世界太大了，不同的人制定出了好多种字符集，它们表示的字符范围和用到的编码规则可能都不一样。我们看一下一些常用字符集的情况：

ASCII字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，我们看一些字符的编码方式： 'L' -> 01001100（十六进制：0x4C，十进制：76） 'M' -> 01001101（十六进制：0x4D，十进制：77）

ISO 8859-1字符集

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。

GB2312字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：

如果该字符在ASCII字符集中，则采用1字节编码。

第2章 MySQL的调控按钮-启动选项和系统变量

如果你用过手机，你的手机上一定有一个设置的功能，你可以选择设置手机的来电铃声、设置音量大小、设置解锁密码等等。假如没有这些设置功能，我们的生活将置于尴尬的境地，比如在图书馆里无法把手机设置为静音，无法把流量开关关掉以节省流量，在别人得知解锁密码后无法更改密码～ MySQL的服务器程序和客户端程序也有很多设置项，比如对于MySQL服务器程序，我们可以指定诸如允许同时连入的客户端数量、客户端和服务器通信方式、表的默认存储引擎、查询缓存的大小等设置项。对于MySQL客户端程序，我们之前已经见识过了，可以指定需要连接的服务器程序所在主机的主机名或IP地址、用户名及密码等信息。

这些设置项一般都有各自的默认值，比方说服务器允许同时连入的客户端的默认数量是151，表的默认存储引擎是InnoDB，我们可以在程序启动的时候去修改这些默认值，对于这种在程序启动时指定的设置项也称之为启动选项（startup options），这些选项控制着程序启动后的行为。在MySQL安装目录下的bin目录中的各种可执行文件，不论是服务器相关的程序（比如mysqld、mysqld_safe）还是客户端相关的程序（比如mysql、mysqladmin），在启动的时候基本都可以指定启动参数。这些启动参数可以放在命令行中指定，也可以把它们放在配置文件中指定。下面我们以mysqld为例，来详细介绍指定启动选项的格式。需要注意的一点是，我们现在要介绍的是设置启动选项的方式，下面出现的启动选项不论大家认不认识，先不用去纠结每个选项具体的作用是什么，之后我们会对一些重要的启动选项详细介绍。

在命令行上使用选项#

如果我们在启动客户端程序时在-h参数后边紧跟服务器的IP地址，这就意味着客户端和服务器之间需要通过TCP/IP网络进行通信。因为我的客户端程序和服务器程序都装在一台计算机上，所以在使用客户端程序连接服务器程序时指定的主机名是127.0.0.1的情况下，客户端进程和服务器进程之间会使用TCP/IP网络进行通信。如果我们在启动服务器程序的时候就禁止各客户端使用TCP/IP网络进行通信，可以在启动服务器程序的命令行里添加skip-networking启动选项，就像这样： mysqld --skip-networking 可以看到，我们在命令行中指定启动选项时需要在选项名前加上--前缀。另外，如果选项名是由多个单词构成的，它们之间可以由短划线-连接起来，也可以使用下划线_连接起来，也就是说skip-networking和skip_networking表示的含义是相同的。所以上面的写法与下面的写法是等价的： mysqld --skip_networking 在按照上述命令启动服务器程序后，如果我们再使用mysql来启动客户端程序时，再把服务器主机名指定为127.0.0.1（IP地址的形式）的话会显示连接失败： ``` mysql -h127.0.0.1 -uroot -p Enter password:

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (61) ``` 这就意味着我们指定的启动选项skip-networking生效了！

再举一个例子，我们前面说过如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行： mysqld --default-storage-engine=MyISAM 我们现在就已经把表的默认存储引擎改为MyISAM了，在客户端程序连接到服务器程序后试着创建一个表： mysql> CREATE TABLE sys_var_demo( -> i INT -> ); Query OK, 0 rows affected (0.02 sec) 这个定义语句中我们并没有明确指定表的存储引擎，创建成功后再看一下这个表的结构： mysql> SHOW CREATE TABLE sys_var_demo\G *************************** 1. row *************************** Table: sys_var_demo Create Table: CREATE TABLE sys_var_demo(i int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.01 sec) 可以看到该表的存储引擎已经是MyISAM了，说明启动选项default-storage-engine生效了。

第1章 装作自己是个小白-重新认识MySQL

MySQL的客户端／服务器架构#

以我们平时使用的微信为例，它其实是由两部分组成的，一部分是客户端程序，一部分是服务器程序。客户端可能有很多种形式，比如手机APP，电脑软件或者是网页版微信，每个客户端都有一个唯一的用户名，就是你的微信号，另一方面，腾讯公司在他们的机房里运行着一个服务器软件，我们平时操作微信其实都是用客户端来和这个服务器来打交道。比如狗哥用微信给猫爷发了一条消息的过程其实是这样的：

1. 消息被客户端包装了一下，添加了发送者和接收者信息，然后从狗哥的微信客户端传送给微信服务器；
2. 微信服务器从消息里获取到它的发送者和接收者，根据消息的接收者信息把这条消息送达到猫爷的微信客户端，猫爷的微信客户端里就显示出狗哥给他发了一条消息。

MySQL的使用过程跟这个是一样的，它的服务器程序直接和我们存储的数据打交道，然后可以有好多客户端程序连接到这个服务器程序，发送增删改查的请求，然后服务器就响应这些请求，从而操作它维护的数据。和微信一样，MySQL的每个客户端都需要提供用户名密码才能登录，登录之后才能给服务器发请求来操作某些数据。我们日常使用MySQL的情景一般是这样的：

1. 启动MySQL服务器程序。
2. 启动MySQL客户端程序并连接到服务器程序。
3. 在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

我们知道计算机很牛逼，在一台计算机上可以同时运行多个程序，比如微信、QQ、音乐播放器、文本编辑器等，每一个运行着的程序也被称为一个进程。我们的MySQL服务器程序和客户端程序本质上都算是计算机上的一个进程，这个代表着MySQL服务器程序的进程也被称为MySQL数据库实例，简称数据库实例。

每个进程都有一个唯一的编号，称为进程ID，英文名叫PID，这个编号是在我们启动程序的时候由操作系统随机分配的，操作系统会保证在某一时刻同一台机器上的进程号不重复。比如你打开了计算机中的QQ程序，那么操作系统会为它分配一个唯一的进程号，如果你把这个程序关掉了，那操作系统就会把这个进程号回收，之后可能会重新分配给别的进程。当我们下一次再启动 QQ程序的时候分配的就可能是另一个编号。每个进程都有一个名称，这个名称是编写程序的人自己定义的，比如我们启动的MySQL服务器进程的默认名称为mysqld， 而我们常用的MySQL客户端进程的默认名称为mysql。

MySQL的安装#

不论我们通过下载源代码自行编译安装的方式，还是直接使用官方提供的安装包进行安装之后，MySQL的服务器程序和客户端程序都会被安装到我们的机器上。不论使用上述两者的哪种安装方式，一定一定一定（重要的话说三遍）要记住你把MySQL安装到哪了，换句话说，一定要记住MySQL的安装目录。 小贴士：MySQL的大部分安装包都包含了服务器程序和客户端程序，不过在Linux下使用RPM包时会有单独的服务器RPM包和客户端RPM包，需要分别安装。

另外，MySQL可以运行在各种各样的操作系统上，我们后边会讨论在类UNIX操作系统和Windows操作系统上使用的一些差别。为了方便大家理解，我在macOS 操作系统（苹果电脑使用的操作系统）和Windows操作系统上都安装了MySQL，它们的安装目录分别是：

macOS操作系统上的安装目录： /usr/local/mysql/

Windows操作系统上的安装目录： C:\Program Files\MySQL\MySQL Server 5.7

下面我会以这两个安装目录为例来进一步扯出更多的概念，不过一定要注意，这两个安装目录是我的运行不同操作系统的机器上的安装目录，一定要记着把下面示例中用到安装目录的地方替换为你自己机器上的安装目录。 小贴士：类UNIX操作系统非常多，比如FreeBSD、Linux、macOS、Solaris等都属于UNIX操作系统的范畴，我们这里使用macOS操作系统代表类UNIX操作系统来运行MySQL。

bin目录下的可执行文件#

在MySQL的安装目录下有一个特别特别重要的bin目录，这个目录下存放着许多可执行文件，以macOS系统为例，这个bin目录的绝对路径就是（在我的机器上）： /usr/local/mysql/bin 我们列出一些在macOS中这个bin目录下的一部分可执行文件来看一下（文件太多，全列出来会刷屏的）： . ├── mysql ├── mysql.server -> ../support-files/mysql.server ├── mysqladmin ├── mysqlbinlog ├── mysqlcheck ├── mysqld ├── mysqld_multi ├── mysqld_safe ├── mysqldump ├── mysqlimport ├── mysqlpump ... (省略其他文件) 0 directories, 40 files Windows中的可执行文件与macOS中的类似，不过都是以.exe为扩展名的。这些可执行文件都是与服务器程序和客户端程序相关的，后边我们会详细介绍一些比较重要的可执行文件，现在先看看执行这些文件的方式。

对于有可视化界面的操作系统来说，我们拿着鼠标点点点就可以执行某个可执行文件，不过现在我们更关注在命令行环境下如何执行这些可执行文件，命令行通俗的说就是那些黑框框，这里的指的是类UNIX系统中的Shell或者Windows系统中的cmd.exe，如果你现在还不知道怎么启动这些命令行工具，网上搜搜吧～ 下面我们以macOS系统为例来看看如何启动这些可执行文件（Windows中的操作是类似的，依葫芦画瓢就好了）

使用可执行文件的相对／绝对路径
假设我们现在所处的工作目录是MySQL的安装目录，也就是/usr/local/mysql，我们想启动bin目录下的mysqld这个可执行文件，可以使用相对路径来启动： ./bin/mysqld 或者直接输入mysqld的绝对路径也可以： /usr/local/mysql/bin/mysqld