MySQL高级篇二

InnoDB数据存储结构

1. 数据库的存出结构：页

索引结构给我们提供了高效的索引方式，不过索引信息以及数据记录都是保存在文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL服务器上的存储引擎负责对表中数据的读取和写入工作。不同存储引擎中存放的格式一般是不同的，甚至有的存储引擎比如Memory都不用磁盘来存储数据。

由于InndDB是MySQL的默认存储引擎，所以本章剖析InnoDB存储引擎的数据存储结构。

1.1 磁盘与内存交互的基本单位：页

InnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page)，数据库I/0操作的最小单位是页。一个页中可以存储多个行记录。

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次/0操作）只能处理一行数据，效率会非常低。

1.2 页结构概述

页a、页b、页c.页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

1.3 页的大小

不同的**数据库管理系统（简称DBMS)**的页大小不同。比如在MySQL的InnoDB存储引擎中，默认页的大小是16KB,我们可以通过下面的命令来进行查看：

mysql> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

SQL Server中页的大小为8KB,而在Oracle中我们用术语“块”(Block)来代表“页”，Oralce支持的块大小为2KB，4KB，8KB，，16KB，32KB和64KB.

1.4 页的上层结构

另外在数据库中，还存在着区(Extent)、段(Segment)和表空间(Tablespace)的概念。行、页、区、段、表空间的关系如下图所示：

**区(Extent)**是比页大一级的存储结构，在InnoDB存储擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB,所以一个区的大小是64*16KB=1MB。

**段(Segment)**由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页）不过在段中不要求区与区之间是相邻的。段是数据库中的分配单体，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

**表空间(Tablespace)**是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

2. 页的内部结构（重点）

页如果按类型划分的话，常见的有数据页（保存B+树节点）、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。
数据页的16KB大小的存储空间被划分为七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Inflmum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）。
页结构的示意图如下所示：

2.1 文件头部 和 文件尾部

2.1.1 File Header（文件头）（38字节）

作用：描述各种页的通用信息。（比如页的编号、其上一页、下一页是谁等）

构成：

名称	占用空间大小	描述
FIL_PAGE_SPACE_OR_CHKSUM	4字节	页的校验和（checksum值）
FIL_PAGE_OFFSET	4字节	页号
FIL_PAGE_PREV	4字节	上一个页的页号
FIL_PAGE_NEXT	4字节	下一个页的页号
FIL_PAGE_LSN	8字节	页面被最后修改时对应的日志序列位置
FIL_PAGE_TYPE	2字节	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	8字节	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4字节	页属于哪个表空间

• FIL_PAGE_OFFSET（4字节）：每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号可以唯一定位一个页。
• FIL_PAGE_TYPE（2字节）：代表当前页的类型。

FIL_PAGE_PREV（4字节）和FIL_PAGE_NEXT（4字节）：InnoDB都是以页为单位存放数据的，如果数据分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，保证这些页之间不需要是物理上的连续，而是逻辑上的连续。

FIL_PAGE_SPACE_OR_CHKSUM（4字节）：当前页面的校验和（checksum）。

什么是校验和？（类似于Hash）

就是对于一个很长的字节串来说，我们会通过某种算法来计算一个比较短的值来代表这个很长的字节串，这个比较短的值就称为校验和。
在比较两个很长的字节串之前，先比较这两个长字节串的校验和，如果校验和都不一样，则两个长字节串肯定是不同的，所以省去了直接比较两个比较长的字节串的时间损耗。

文件头部和文件尾部都有属性：FIL_PAGE_SPACE_OR_CHKSUM
作用：
InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。
为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。

具体的：
每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。这里，校验方式就是采用 Hash 算法进行校验。

• FIL_PAGE_LSN（8字节）： 页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）

2.1.2 File Trailer（文件尾部）（8字节）

• 前4个字节代表页的校验和：这个部分是和File Header中的校验和相对应的。
• 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）：这个部分也是为了校验页的完整性的，如果首部和尾部的LSN值校验不成功的话，就说明同步过程出现了问题

2.2 User Records（用户记录）、最大最小空间、Free Space（空闲空间）

2.2.1 Free Space（空闲空间）

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

2.2.2 User Records（用户记录）

User Records中的这些记录按照指定的行格式一条一条摆在User Records部分，相互之间形成单链表。

2.2.3 Infimum + Supremum（最小最大记录）

记录可以比较大小吗？

是的，记录可以比大小，对于一条完整的记录来说，比较记录的大小就是比较主键的大小。比方说我们插入的4行记录的主键值分别是：1、2、3、4，这也就意味着这4条记录是从小到大依次递增。

InnoDB规定的最小记录与最大记录这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的，如图所示：

这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分，如图所示：

2.3 Page Directory（页目录）、Page Header（页面头部）

2.3.1 Page Directory（页目录）

为什么需要页目录？ 在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此在页结构中专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。

需求：根据主键值查找页中的某条记录，如何实现快速查找呢？

SELECT * FROM page_demo WHERE c1 = 3;

• 方式1：顺序查找

从Infimum记录（最小记录）开始，沿着链表一直往后找，总有一天会找到（或者找不到），在找的时候还能投机取巧，因为链表中各个记录的值是按照从小到大顺序排列的，所以当链表的某个节点代表的记录的主键值大于你想要查找的主键值时，你就可以停止查找了，因为该节点后边的节点的主键值依次递增。

如果一个页中存储了非常多的记录，这么查找性能很差。

• 方式2：使用页目录，二分法查找

1. 将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。
2. 第 1 组，也就是最小记录所在的分组只有 1 个记录；
   最后一组，就是最大记录所在的分组，会有 1-8 条记录；
   其余的组，记录数量在 4-8 条之间。
   这样做的好处是，除了第 1 组（最小记录所在组）以外，其余组的记录数会尽量平分。
3. 在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。
4. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

举例2：
现在的page_demo表中正常的记录共有6条，InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录。如下图：

从这个图中我们需要注意这么几点：

• 现在页目录部分中有两个槽，也就意味着我们的记录被分成了两个组，槽1中的值是112，代表最大记录的地址偏移量（就是从页面的0字节开始数，数112个字节）；槽0中的值是99，代表最小记录的地址偏移量。
• 注意最小和最大记录的头信息中的n_owned属性
  • 最小记录的n_owned值为1，这就代表着以最小记录结尾的这个分组中只有1条记录，也就是最小记录本身。
  • 最大记录的n_owned值为5，这就代表着以最大记录结尾的这个分组中只有5条记录，包括最大记录本身还有我们自己插入的4条记录。

用箭头指向的方式替代数字，这样更易于我们理解，修改后如下：

再换个角度看一下：（单纯从逻辑上看一下这些记录和页目录的关系）

问题1. 页目录分组的个数如何确定？

问题：为什么最小记录的n_owned值为1，而最大记录的n_owned值为5呢？

InnoDB规定：对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

分组是按照下边的步骤进行的：

• 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
• 之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
• 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

问题2. 页目录结构下如何快速查找记录？

现在向page_demo表中添加更多的数据。如下：

INSERT INTO page_demo 
VALUES
(5, 500, 'zhou'), 
(6, 600, 'chen'), 
(7, 700, 'deng'), 
(8, 800, 'yang'), 
(9, 900, 'wang'), 
(10, 1000, 'zhao'), 
(11, 1100, 'qian'), 
(12, 1200, 'feng'), 
(13, 1300, 'tang'), 
(14, 1400, 'ding'), 
(15, 1500, 'jing'), 
(16, 1600, 'quan');

添加了12条记录，现在页里一共有18条记录了（包括最小和最大记录），这些记录被分成了5个组，如图所示：

这里只保留了16条记录的记录头信息中的n_owned和next_record属性，省略了各个记录之间的箭头。

现在看怎么从这个页目录中查找记录。因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用二分法来进行快速查找。5个槽的编号分别是：0、1、2、3、4，所以初始情况下最低的槽就是low=0，最高的槽就是high=4。比方说我们想找主键值为6的记录，过程是这样的：

• 计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。

• 重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。

• 因为high - low的值为1，所以确定主键值为6的记录在槽2对应的组中。此刻我们需要找到槽2中主键值最小的那条记录，然后沿着单向链表遍历槽2中的记录。

  但是我们前边又说过，每个槽对应的记录都是该组中主键值最大的记录，这里槽2对应的记录是主键值为8的记录，怎么定位一个组中最小的记录呢？别忘了各个槽都是挨着的，我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录，直到找到主键值为6的那条记录即可。

  由于一个组中包含的记录条数只能是1~8条，所以遍历一个组中的记录的代价是很小的。

3. 小结

在一个数据页中查找指定主键值的记录的过程分为两步：

1. 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
2. 通过记录的next_record属性遍历该槽所在的组中的各个记录。

2.3.2 Page Header（页面头部）

为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫Page Header的部分，这个部分占用固定的56个字节，专门存储各种状态信息。

PAGE_DIRECTION（2字节）：假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。

PAGE_N_DIRECTION（2字节）：假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

3. InnoDB行格式或记录格式（重点）

我们平时的数据以行为单位来向表中插入数据，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

查看MySQL8的默认行格式：

mysql> select @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+
1 row in set (0.00 sec)

也可以使用如下语法查看具体表使用的行格式：

mysql> show table status like 'student';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options     | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
| student | InnoDB |      10 | Dynamic    |   27 |            606 |       16384 |               0 |            0 |         0 |             96 | 2023-09-09 02:00:36 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=DYNAMIC |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
1 row in set (0.00 sec)

3.1 指定行格式的语法

在创建或修改表的语句中指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

举例：

mysql> CREATE TABLE record_test_table (
    ->     col1 VARCHAR(8),
    ->     col2 VARCHAR(8) NOT NULL,
    ->     col3 CHAR(8),
    ->     col4 VARCHAR(8)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.03 sec)

向表中插入两条记录：

INSERT INTO record_test_table(col1, col2, col3, col4) 
VALUES
('zhangsan', 'lisi', 'wangwu', 'songhk'), 
('tong', 'chen', NULL, NULL);

3.2 COMPACT行格式

在MySQL 5.1版本中，默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

1. 变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

注意：这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。

以record_test_table表中的第一条记录举例：因为record_test_table表的col1、col2、col4列都是VARCHAR(8)类型的，所以这三个列的值的长度都需要保存在记录开头处，注意record_test_table表中的各个列都使用的是ascii字符集（每个字符只需要1个字节来进行编码）

列名	存储内容	内容长度（十进制表示）	内容长度
col1	zhangsan	8	0x08
col2	lisi	4	0x04
col3	songhk	6	0x06

又因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是（各个字节之间实际上没有空格，用空格隔开只是方便理解）：06 04 08

把这个字节串组成的变长字段长度列表填入上边的示意图中的效果就是：

2. NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。

为什么定义NULL值列表？之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在**行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，**格式如下：

1. 二进制位的值为1时，代表该列的值为NULL。
2. 二进制位的值为0时，代表该列的值不为NULL。

例如： 字段 a、b、c，其中a是主键，在某一行中存储的数依次是 a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存储：01。第一个0表示c不为null，第二个1表示b是null。这里之所以没有a是因为数据库会自动跳过主键，因为主键肯定是非NULL且唯一的，在NULL值列表的数据中就会自动跳过主键。

record_test_table的两条记录的NULL值列表就如下：

INSERT INTO record_test_table(col1, col2, col3, col4) 
VALUES
('zhangsan', 'lisi', 'wangwu', 'songhk'), 
('tong', 'chen', NULL, NULL);

第一条记录：

第二条记录：

3. 记录头信息

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)

这个表中记录的行格式示意图：

这些记录头信息中各个属性如下：

简化后的行格式示意图：

插入数据：

INSERT INTO page_demo 
VALUES
(1, 100, 'song'), 
(2, 200, 'tong'), 
(3, 300, 'zhan'), 
(4, 400, 'lisi');

图示如下：

1. 记录头信息字段介绍

• delete_mask

  这个属性标记着当前记录是否被删除，占用1个二进制位。

  • 值为0：代表记录并没有被删除
  • 值为1：代表记录被删除掉了

问题：被删除的记录为什么还在页中存储呢？

你以为它删除了，可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后其他的记录在磁盘上需要重新排列，导致性能消耗。所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

• min_rec_mask

  B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值为1。

  我们自己插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。

• record_type

  这个属性表示当前记录的类型，一共有4种类型的记录：

  0：表示普通记录

  1：表示B+树非叶节点记录

  2：表示最小记录

  3：表示最大记录

从图中我们也可以看出来，我们自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的record_type值分别为2和3。record_type为1时作为B+树非叶子节点的记录，在索引的数据结构章节讲过。

• heap_no
  这个属性表示当前记录在本页中的位置。

  从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。

问题：怎么不见heap_no值为0和1的记录呢？

MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。**这两个伪记录一个代表最小记录，一个代表最大记录。**最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前。

• n_owned
  页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。
• next_record
  记录头信息里该属性非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

比如：第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。

注意，下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）。下图用箭头代替偏移量表示next_record。

next_record演示：删除操作
从表中删除掉一条记录，这个链表也是会跟着变化：

mysql> DELETE FROM page_demo WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)

删掉第2条记录后的示意图就是：

从图中可以看出来，删除第2条记录前后主要发生了这些变化：

• 第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。
• 第2条记录的next_record值变为了0，意味着该记录没有下一条记录了。
• 第1条记录的next_record指向了第3条记录。
• 最大记录的n_owned值从 5 变成了 4 。

所以，不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。

next_record演示：添加操作

主键值为2的记录被我们删掉了，但是存储空间却没有回收，如果我们再次把这条记录插入到表中，会发生什么事呢？

mysql> INSERT INTO page_demo VALUES(2, 200, 'tong');
Query OK, 1 row affected (0.00 sec)

我们看一下记录的存储情况：

直接复用了原来被删除记录的存储空间。

说明： 当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间。

4. 记录的真实数据

记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列：

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

• 一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。
• 事务ID和回滚指针在后面的《MySQL高级篇13【MySQL事务日志】》章节中讲解。

举例：分析Compact行记录的内部结构：

CREATE TABLE mytest(
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 CHAR(10),
col4 VARCHAR(10)
)ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;

INSERT INTO mytest
VALUES('a','bb','bb','ccc');

INSERT INTO mytest
VALUES('d','ee','ee','fff');
 
INSERT INTO mytest
VALUES('d',NULL,NULL,'fff');

在Windows操作系统下，可以选择通过程序UltraEdit打开表空间文件mytest.ibd这个二进制文件。内容如下：

0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00|supremum........|
0000c080 2c 00 00 00 2b 68 00 00 00 00 00 06 05 80 00 00|,...+h..........|
0000c090 00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20|.2..abbbb|
0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00|ccc........+...|
0000c0b0 2b 68 01 00 00 00 00 06 06 80 00 00 00 32 01 10|+h...........2..|
0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66|deeeefff|
0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 2b 68 02 00 00|..........+h...|
0000c0e0 00 00 06 07 80 00 00 00 32 01 10 64 66 66 66 00|........2..dfff.|

该行记录从0000c078开始，若整理一下，相信大家会有更好的理解：

03 02 01                     	/*变长字段长度列表，逆序*/
00                              /*NULL标志位，第一行没有NULL值*/
00 00 10 00 2c            		/*Record Header，固定5字节长度*/
00 00 00 2b 68 00       		/*RowID InnoDB自动创建，6字节*/
00 00 00 00 06 05       		/*TransactionID*/
80 00 00 00 32 01 10   			/*Roll Pointer*/
61                             	/*列1数据'a'*/
62 62                          	/*列2数据'bb'*/
62 62 20 20 20 20 20 20 20 20	/*列3数据'bb'*/
63 63 63                     	/*列4数据'ccc'*/

注意1：InnoDB每行有隐藏列TransactionID和Roll Pointer。

注意2：固定长度CHAR字段在未能完全占用其长度空间时，会用0x20来进行填充。

接着再来分析下Record Header的最后两个字节，这两个字节代表next_recorder，0x2c代表下一个记录的偏移量，即当前记录的位置加上偏移量0x2c就是下条记录的起始位置。

第二行将不做整理，除了RowID不同外，它和第一行大同小异，现在来分析有NULL值的第三行：

03 								/*变长字段长度列表，逆序*/
06                              /*NULL标志位，第三行有NULL值*/
00 00 20 ff 98                  /*Record Header*/
00 00 00 2b 68 02           	/*RowID*/
00 00 00 00 06 07           	/*TransactionID*/
80 00 00 00 32 01 10       		/*Roll Pointer*/
64                              /*列1数据'd'*/
66 66 66                        /*列4数据'fff'*/

第三行有NULL值，因此NULL标志位不再是00而是06，转换成二进制为00000110，为1的值代表第2列和第3列的数据为NULL。在其后存储列数据的部分，用户会发现没有存储NULL列，而只存储了第1列和第4列非NULL的值。

因此这个例子很好地说明了：不管是CHAR类型还是VARCHAR类型，在compact格式下NULL值都不占用任何存储空间。

3.3 Dynamic和Compressed行格式（重点）

1. 行溢出

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。

很多DBA喜欢MySQL数据库提供的VARCHAR(M)类型，认为可以存放65535字节。这是真的吗？如果我们使用 ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用。

CREATE  TABLE  varchar_size_demo(
 c  VARCHAR(65535)
 )  CHARSET=ascii  ROW_FORMAT=Compact;

结果如下：

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, 
is 65535. This includes storage overhead, check the manual. You have  to  change  some  columns  to  TEXT or  BLOBs

报错信息表达的意思是：MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外， 其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。

这个65535个字节除了列本身的数据之外，还包括一些其他的数据，以Compact行格式为例，比如说我们为了存储一个VARCHAR(M)类型的列，除了真实数据占有空间以外，还需要记录的额外信息。

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用 2个字节，NULL值标识需要占用1个字节。

CREATE  TABLE  varchar_size_demo(
    c  VARCHAR(65532)
)  CHARSET=ascii  ROW_FORMAT=Compact;

如果有not null属性，那么就不需要NULL值标识，也就可以多存储一个字节，即65533个字节

CREATE  TABLE  varchar_size_demo( 
  c  VARCHAR(65533)  not  null
)  CHARSET=ascii  ROW_FORMAT=Compact; 

通过上面的案例，我们可以知道一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中进行分页存储，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。
这称为页的扩展，举例如下：

2. Dynamic和Compressed行格式

在MySQL 8.0中，默认行格式就是Dynamic，Dynamic、Compressed行格式和Compact行格式挺像，只不过在处理行溢出数据时有分歧：

Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图，在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。

Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

3.4 Redundant行格式（了解）

4. 区、段与碎片区

4.1 为什么要有区？
我们每向表中插入一条记录，本质上就是向该表的聚簇索引以及所有二级索引代表的B+树的节点中插入数据。而B+树的每一层中的质都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远就是所谓的随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。

引入区的概念，一个区就是在物理位置上连续的64个页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机I/O，功大于过！

4.2 为什么要有段？
我们提到的范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。

存放叶子节点的区的集合就算是一个段（segment)，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非索引节点。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

4.3 为什么要有碎片区？

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M（64*16KB=1024KB）存储空间，所以默认情况下一个只存在几条记录的小表也需要2M的存储空间么？ 以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

为了考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片（fragment）区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页面用于段A，有些页面用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的：

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
• 当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面已经一些完整的区的集合。

区（连续的64个页）-> 段（叶子节点+非叶子节点段（索引段+非索引段））-> 一个段至少一个区 -> 至少2M

4.4 区的分类

区大体上可以分为4种类型：

• 空闲的区(FREE)：现在还没有用到这个区中的任何页面。
• 有剩余空间的碎片区(FREE_FRAG)：表示碎片区中还有可用的页面。
• 没有剩余空间的碎片区(FULL_FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面。
• 附属于某个段的区(FSEG)：每一索引都可以分为叶子节点段和非叶子节点段

处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区都是独立的，直属于表空间。而处于FSEG状态的区是附属于某个段的。

如果把表空间比作是一个集团军，段就相当于师，区就相当于团。一般的团都是隶属于某个师的，就像是处于FSEG的区全都隶属于某个段，而处于FREE、FREE_FRAG以及 FULL_FRAG这三种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。

5. 表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。

表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为**系统表空间(System tablespace)、独立表空间(File-per-table tablespace)、撤销表空间(Undo Tablespace)和临时表空间(Temporary Tablespace)**等。

5.1 独立表空间

独立表空间，即每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间(即:单表)可以在不同的数据库之间进行迁移。

空间可以回收(DROPTABLE操作可自动回收表空间;其他情况，表空间不能自己回收)。如果对于统计分析或是日志表，删除大量数据后可以通过: alter table TableName engine=innodb;回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

独立表空间结构

独立表空间由段、区、页组成。前面已经讲解过了。

真实表空间对应的文件大小

我们到数据目录里看，会发现一个新建的表对应的.ibd文件只占用了96K，才6个页面大小(MySQL5.7中)，这是因为一开始表空间占用的空间很小，因为表里边都没有数据。不过别忘了这些.ibd文件是自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大。|

查看InnoDB的表空间类型：

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

你能看到innodb_file_per_table=ON，这就意味着每张表都会单独保存为一个.ibd文件。

5.2 系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。

lnnoDB数据字典

每当我们向一个表中插入一条记录的时候，MySQL校验过程如下:

先要校验一下插入语句对应的表存不存在，插入的列和表中的列是否符合，如果语法没有问题的话，还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应索引的B+树中。所以说，MySQL除了保存着我们插入的用户数据之外，还需要保存许多额外的信息，比方说:

• 某个表属于哪个表空间,表里边有多少列
• 表对应的每一个列的类型是什么
• 该表有多少索引，每个索引对应哪几个字段，该索引对应的根页面在哪个表空间的哪个页面
• 该表有哪些外键，外键对应哪个表的哪些列
• 某个表空间对应文件系统上文件路径是什么
• …

上述这些数据并不是我们使用INSERT语句插入的用户数据，实际上是为了更好的管理我们这些用户数据而不得已引入的一些额外数据，这些数据也称为元数据。InnoDB存储引擎特意定义了一些列的内部系统表（internalsystem table)来记录这些这些元数据:

这些系统表也被称为数据字典，它们都是以B+树的形式保存在系统表空间的某些页面中，其中 SYS_TABLES、SYS_COLUNNS、SYS_INDEXES、SYS_FIELDS这四个表尤其重要，称之为基本系统表(basic system tables)，我们先看看这4个表的结构:

SYS_TABLES表结构

SYS_COLUNNS表结构

SYS_INDEXES表结构

SYS_FIELDS表结构

列名	描述
INDEX_ID	该索引列所属的索引的D。(与P0S一起构成联合主键)
POS	该索引列在某个索引中是第几列
COL_NAME	该索引列的名称

注意：用户是不能直接访问InnoDB的这些内部系统表，除非你直接去解析系统表空间对应文件系统上的文件。不过考虑到查看这些表的内容可能有助于大家分析问题，所以在系统数据库information_schema中提供了一些以innodb_sys开头的表：（MySQL8.0中不是这个）

mysql> use information_schema 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables like 'innodb_sys%';
Empty set (0.00 sec)

在information_schema数据库中的这些以INNODB_SYS开头的表并不是真正的内部系统表（内部系统表就是我们上边以SYS开头的那些表)，而是在存储引擎启动时读取这些以SYS开头的系统表，然后填充到这些以INNODB_SYS开头的表中。以INNODB_SYS开头的表和以SYS开头的表中的字段并不完全一样，但供大家参考已经足矣。

附录：数据页加载的三种方式

InnoDB从磁盘中读取数据的最小单位是数据页。而你想得到的id=xxx的数据，就是这个数据页众多行中的一行。

对于MySQL存放的数据，逻辑概念上我们称之为表，在磁盘等物理层面而言是按数据页形式进行存放的，当其加载到MySQL中我们称之为缓存页。

如果缓冲池中没有该页数据，那么缓冲池有以下三种读取数据的方式，每种方式的读取效率都是不同的：

1. 内存读取

如果该数据存在于内存中，基本上执行时间在1ms左右，效率还是很高的。

2.随机读取
如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在10ms左右，这10ms中有6ms是磁盘的实际繁忙时间（包括了寻道和半圈旋转时间），有3ms是对可能发生的排队时间的估计值，另外还有1ms的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。这10ms看起来很快，但实际上对于数据库来说消耗的时间已经非常长了，因为这还只是一个页的读取时间。

3.顺序读取
顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘1/0操作了。如果一个磁盘的吞吐量是40MB/S，那么对于一个16KB大小的页来说，一次可以顺序读取2560（40MB/16KB）个页，相当于一个页的读取时间为0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。

索引的创建与设计原则

1. 索引的声明与使用

1.1 索引的分类

• 从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
• 按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
• 按照作用字段个数进行划分，分成单列索引和联合索引。

1.2 创建索引

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
• index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC或DESC指定升序或者降序的索引值存储。

1. 创建普通索引

CREATE TABLE book( 
    book_id INT , 
    book_name VARCHAR(100), 
    authors VARCHAR(100), 
    info VARCHAR(100) , 
    comment VARCHAR(100), 
    year_publication YEAR, 
    INDEX(year_publication) 
);

2. 创建唯一索引

CREATE TABLE test1( 
    id INT NOT NULL, 
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) 
);

3. 主键索引

CREATE TABLE student ( 
    id INT(10) UNSIGNED AUTO_INCREMENT, 
    student_no VARCHAR(200),
    student_name VARCHAR(200), 
    PRIMARY KEY(id) 
);

# 删除主键索引
ALTER TABLE student drop PRIMARY KEY ;

4. 创建单列索引

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20)) 
);

5. 创建组合索引

CREATE TABLE test3( 
    id INT(11) NOT NULL, 
    name CHAR(30) NOT NULL, 
    age INT(11) NOT NULL, 
    info VARCHAR(255), 
    INDEX multi_idx(id,name,age) 
);

6. 创建全文索引

CREATE TABLE `papers` ( 
    id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `title` varchar(200) DEFAULT NULL, 
    `content` text, PRIMARY KEY (`id`), 
    FULLTEXT KEY `title` (`title`,`content`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

7. 创建空间索引

CREATE TABLE test5( 
    geo GEOMETRY NOT NULL, 
    SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE=MyISAM;

2. 在已经存在的表上创建索引

1. 使用ALTER TABLE语句创建索引

ALTER TABLE table_name 
ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

2. 使用CREATE INDEX创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name 
ON table_name (col_name[length],...) [ASC | DESC]

1.3 删除索引

1. 使用ALTER TABLE删除索引

ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

2. MySQL8.0索引新特性

2.1 支持降序索引

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

2.2 隐藏索引

从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

1. 创建表时直接创建

CREATE TABLE tablename( 
    propname1 type1[CONSTRAINT1], 
    propname2 type2[CONSTRAINT2], 
    ……
    propnamen typen, 
    INDEX [indexname](propname1 [(length)]) INVISIBLE 
);

2. 在已经存在的表上创建

CREATE INDEX indexname 
ON tablename(propname[(length)]) INVISIBLE;

3. 通过ALTER TABLE语句创建

ALTER TABLE tablename 
ADD INDEX indexname (propname [(length)]) INVISIBLE;

4. 切换索引可见状态

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

3. 索引的设计原则

3.1 哪些情况适合创建索引

1. 字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的

2. 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4. UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6. 多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

7. 使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。

• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8. 使用字符串前缀创建索引

区分度计算公式：

count(distinct left(列名, 索引长度))/count(*)

拓展：Alibaba《Java开发手册》

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上。

10. 使用最频繁的列放到联合索引的左侧

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

3.2 限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：

• 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
• 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
• 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

3.3 哪些情况不适合创建索引

1. 在where中使用不到的字段，不要设置索引

2. 数据量小的表最好不要使用索引

3. 有大量重复数据的列上不要建立索引

4. 避免对经常更新的表创建过多的索引

5. 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引

性能分析工具的使用

1. 统计SQL的查询成本：last_query_cost

mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 3.699000 |
+-----------------+----------+
1 row in set (0.00 sec)

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

1. 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2. 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

2. 定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

2.1 开启慢查询日志参数

1. 开启slow_query_log

set global slow_query_log='ON';

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/d214d4b78066-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

2. 修改long_query_time阈值

show variables like '%long_query_time%';

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql> set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

2.2 查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

2.3 慢查询日志分析工具：mysqldumpslow

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2.4 关闭慢查询日志

方式1：永久性方式

[mysqld] 
slow_query_log=OFF
#或
[mysqld] 
#slow_query_log =OFF

方式2：临时性方式

SET GLOBAL slow_query_log=off;

3. 查看 SQL 执行成本：SHOW PROFILE

show variables like 'profiling';
#开启
set profiling = 'ON';
#查看
show profiles;
show profile cpu,block io for query 2;

4. 分析查询语句：EXPLAIN

4.1 基本语法

EXPLAIN SELECT select_options 
#或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

4.2 EXPLAIN各列作用

1. table

不论我们的查询语句有多复杂，包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

2. id

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

3. select_type

4. partitions

5. type（重点）

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是consts级别。（阿里巴巴开发手册要求）

6. possible_keys和key

7. key_len（重点）

key_len的长度计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

8. ref

9. rows（重点）

预估的需要读取的记录条数

10. filtered

11. Extra

5. EXPLAIN的进一步使用

5.1 EXPLAIN四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

1. 传统格式

2. JSON格式

JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。用于查看执行成本cost_info

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

4. 可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。

5.2 SHOW WARNINGS的使用

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
# 查看优化后的执行语句
mysql> SHOW WARNINGS\G

6. 分析优化器执行计划：trace

# 开启
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
# 设置大小
set optimizer_trace_max_mem_size=1000000;
# 使用
select * from student where id < 10;
select * from information_schema.optimizer_trace\G

7. MySQL监控分析视图-sys schema

7.1 Sys schema视图使用场景

索引情况

#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

索引优化与查询优化

1、索引失效案例

1.1 最左匹配原则
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用，如果查询条件中没有使用这些字段中第一个字段时，联合索引将不会被使用。当然，在一些情况下就算查询语句没有按照索引顺序来，查询优化器是会自动帮我们优化成按字段顺序来的，也会使用到索引

1.2 计算、函数导致索引失效

计算失效案例

select … where stu_no + 1 = 1001;

正常我们会这样写，这样就能使用索引了

select … where stu_no = 1000;

函数失效案例

select … where SUBTRING(name,1,3) = ‘abc’;

实际情况可以通过 explain 分析查看，前面章节已经介绍其字段的含义

1.3 类型转换导致索引失效

类型转换的失效场景有两种，分别是

1. 查询条件类型与字段类型不匹配
2. 连接查询两张表的字段类型不一致
   这个我们在前面也讲到了，如果两张表通过某个字段关联，字段类型必须一致，否则会出现无法使用索引的情况

select … where name = 123; name为字符串，如果要使用索引，应该把条件改为字符串 ‘123’

1.4 范围条件右边的列索引失效

我们创建一个联合索引 (age,classId,name)，并对这个sql进行查询

select … where age=20 and classId>11 and name=‘abc’

虽然我们是按照索引顺序进行的查询，但只会用到classId，name是用不到的，因为我们对classId做了范围查询，如果想要name起作用，可以在建索引时将范围查找的字段（classId）放在最后，这样就能使用到全部了
注意：查询优化器会帮我们优化where条件，因此要关注的是索引建立的顺序，而不是where语句，像日期、金额等会涉及范围查询的就放到最后

1.5 不等于（!=或<>）索引失效

这个很好理解，索引是等值查询用的

1.6 is null 可以使用索引，is not null 无法使用索引

可以和1.5对比理解，但某些资料表示，is not null也能使用，只是回表数据太多，查询优化器直接优化为全表扫描了。
如果没有为null的需求，可以将字段的约束设置为NOT NULL，如果有，也可以用空字符串代替null

1.7 like以通配符%开头索引失效

使用 like 时，'ab%'是可以使用到索引的

阿里巴巴开发手册中，严禁使用左模糊或者全模糊，如果需要应使用搜索引擎来解决

1.8 OR 前后存在非索引的列，索引失效

我们一个字段创建了索引，一个没有，当使用OR时，索引的列按理是可以使用索引的，但非索引列得全表扫描，因此，直接就使用全表扫描，不用索引。（使用or的时候一个使用索引，一个非索引）

1.9 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同字符集进行比较前需要进行替换会造成索引失效

最后，我们可以通过这个表格来加强理解，我们建立索引 index(a,b,c)

2、关联查询优化

2.1 采用左外连接

如果a表有20条数据，b表有30条数据，对a，b表进行左外连接查询时，实际上要进行20 * 30时次筛选，因为要先取出驱动表（a）的一条数据，再与被驱动表（b）的所有数据连接，然后再条件过滤

连接的时候就和“嵌套循环”一样
每次从驱动表a里选取一条记录去被驱动表b里整个遍历一遍
将符合连接条件的放到结果集中
驱动表和被驱动表–>EXPLAIN执行结果的记录中，上面的是驱动表，下面的是被驱动表
EXPLAIN SELECT SQL_NO_CACHE * FROM a LEFT JOIN b ON a.card = b.card;

ALTER TABLE b ADD INDEX Y ( card); #【被驱动表】，

可以避免全表扫描 EXPLAIN SELECT SQL_NO_CACHE * FROM a LEFT JOIN b ON a.card = b.card;

当我们给被驱动表（b）连接的字段添加一个索引，此时可以使用索引，因为左外连接是以左表为主，优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定需要建立索引 。

此时我们可以给驱动表a添加索引，再删除被驱动表b的索引Y，我们可以发现索引不能被使用。

2.2 采用内连接

内连接与外连接在索引上的区别是，内连接时，查询优化器会根据情况调整驱动表和被驱动表，不像外连接一样，内连接是都有数据才返回，因此：

1、在两个表的连接字段都存在索引或都不存在索引时，查询优化器会将数据少的作为驱动表，数据多的作为被驱动表（小表驱动大表）
2、如果只有一个表的连接字段有索引，会将有索引的作为被驱动表

总结：都无索引小表驱动大表（大表作为被驱动表），有索引则有索引表作为被驱动表

2.3 join语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配，MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后，MySQL通过引入BNLJ算法来优化嵌套执行

2.3.1 驱动表和被驱动表

所谓的驱动表和被驱动表是通过 explain 分析得出的结果来确定的，上面的是驱动表，下面的是被驱动表

对于外连接，一般来讲是按照我们的SQL来确认，form 后的是驱动表，join 后的是被驱动表。但是，如果两种表数据一样（生产不存在），会被优化为内连接，这时候就是内连接的情况了

2.3.2 Simple Nested-Loop Join（简单嵌套循环连接）

从A表中取出一条数据，遍历B表，将匹配到的数据放到result，以此类推，驱动表A中的每一条记录与被驱动表B表的记录进行判断。这种方式的效率很低，具体开销如下

2.3.3 Index Nested-Loop Join（索引嵌套循环连接）

该方式优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数，具体开销如下

这种方式，外表还是会扫描一次，而内表因为有索引的原因，不会再去全表扫描，因此为0，读取的记录数看匹配情况，JOIN比较次数：A*索引高度，回表读取记录次数：如果是主键作为关联，那就不需要回表了

2.3.4 Block Nested-Loop Join（块嵌套循环连接）

如果存在索引，那么会使用index的方式进行连接，如果连接的列没有索引，那么被驱动表要扫描的次数就太多了，每次加载驱动表的一条数据，就要扫描一次被驱动表，这样就会进行大量的I/O，如果我们将驱动表的数据成块的取出（不在一条一条的取），放到join buffer缓冲区，那么就可以减少被驱动表的扫描次数了

注意：

缓存的不只是关联表的列，查询的字段也会被缓存起来
join了多少（n）张表，就会分配n-1个join buffer，所以查询的时候尽量减少不必要的字段，可以让join buffer中存放更多的列

这种方式的开销如下

2.3.5 小结

1、整体效率比较：INLJ > BNLJ > SNLJ
2、永远用小结果集驱动大结果集（其本质就是减少外层循环的数据量）
3、为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
4、增大join buffer size 的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
5、减少驱动表不必要的字段查询（提升join buffer的缓存量）

2.3.6 Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
Hash Join是做大数据集连接的常用方式，而Nested Loop适用于数据子集小的情况，具体细节需要阅读相关书籍了，他们之间的对比如下

3、子查询优化

子查询是MySQL的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询，但是，子查询的执行效率都不高，原因在于

1、执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
2、子查询结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
3、对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

因此，可以使用连接(JOIN) 查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。
其次，当存在NOT IN 或者 NOT EXISTS，也可以用 LEFT JOIN xxx ON xx WHERE xx is NULL 代替

4、排序优化

在 MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。
Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort 排序则一股在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件/0 到磁盘进行排序的情况，效率较低。

优化建议:

SQL中，可以在 WHERE 子句和 ORDER BY子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在1.ORDER BY 子句避免使用 FileSort 排。当然，某些情况下全表扫描，FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2.尽量使用Index完成ORDER BY排序。如果 WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
3.无法使用Index 时，需要对 FileSot 方式进行调优

案例

我们先创建好学生表，给表的字段创建 联合索引 (age,class_id,name)，案例中，我们都遵循最左前缀原则

不会使用索引：

SELECT SOL NO CACHE * FROM student ORDER BY age,classid;
原因在于查询了所有字段，还需回表操作，且数据量大，因此，查询优化器直接优化为全部扫描

使用索引——覆盖索引（查询字段为索引字段）

SELECT SOL NO CACHE age,name FROM student ORDER BY age,classid;
原因是查询的字段为索引字段，可以直接返回

使用索引——limit（少量数据）

SELECT SOL NO CACHE * FROM student ORDER BY age,classid limit 10;
使用了limit，减少了数据量，查询优化器优化为走索引。
SELECT SOL NO CACHE * FROM student class_id = 11 ORDER BY age limit 10;
上面这种情况看似没有遵循最左前缀，但它仍然使用了索引，可以理解为先通过age排序，然后取出班级id为11的数据，由于只取10条，查询优化器选择了索引，否则就不会选择索引。
因此，到底走不走索引，没有绝对的规则，最终还是查询优化器说了算

不使用索引——order by时规则不一致（一个升序，一个降序），索引失效

SELECT SOL NO CACHE * FROM student ORDER BY age desc,classid asc limit 10;

filesort算法: 双路排序和单路排序

双路排序（慢）
MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对
他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在bufer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行两次扫描，众所周知，10是很耗时的，所以在mysq14.1之后，出现了第二种改进的算法，就是单路排序。
单路排序（快但更占内存）
从磁盘读取查询需要的所有列，按照order by列在bufer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序10，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
因此，在优化策略上

1、尝试提高 sort_buffer_size

MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。提高内存，可以加载更多的数据。

2、尝试提高 max length for_sort data

如果需要返回的列的总长度大于may_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。

3、Order by 时select *是一个大忌。最好只Query需要的字段。

让列的总长度尽量的小，放更多的数据到内存中。

5、GROUP BY优化

• group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
• group by 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大max_length_for_sort_data和 sort_buffer_size 参数的设置
• where效率高于having，能写在where限定的条件就不要写在having中了
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

6、优先考虑覆盖索引

索引列+主键 包含查询的所有列，这样做的原因是不需要再回表拿其他数据，对于这种情况，我觉得严格意义上不能叫做使用到了索引，只是因为这棵树有需要的全部数据，直接用这棵树而已（走了索引树的全表扫描）

1、前面我们讲到尽量不要使用 <>（!=），因为可能导致索引失效，什么情况下不会失效呐，这里举一些例子，我们建立联合索引 (age,name)

select name,age from student where age <> 30;
使用的原因就是因为查询的字段都在索引内，直接查询这棵B+树就能拿到数据，而不用回表，如果查询了其他字段，还需要回表，还不如直接走全表扫描，当然，这一切都和数据量等有关系，不是绝对的

2、% 左前缀匹配情况，建立联合索引 (age,name)

select name,age from student where name like ‘%abc’;
能使用索引的原因和上面一样

虽然是一种很不错的优化方式，但在实际开发中想加以应用，还是比较难的，因为需求稍微一变化，你需要查询的字段变更或增加，该索引就要重新维护。

7、索引下推（ICP）

Index Condition Pushdown(lCP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式

• 如果没有ICP，存储引警会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 WHERE 后面的条件是否保留行。
• 启用ICP 后，如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分WIHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

好处: ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
但是，ICP的 加速效果 取决于在存储引擎内通过 ICP筛选的数据的比例。

7.1 例子

文字描述还是比较抽象，可以和覆盖索引做对比，覆盖索引是直接使用到非聚簇索引树，而索引下推是使用过后还需要回表操作，这里举两个实例
我们给商品表建立索引 (type,name,desc)

select * from product where type = ‘P001’ and name like ‘%鼠标%’ and desc like ‘%64色氛围灯%’ ;
我们知道，字符串前缀模糊匹配是使用不到索引的，因此，这里只能使用 type 这个字段，但当我们的数据量非常大时，直接找到 type 为 P001 的就进行回表操作（随机IO），然后在过滤，仍是非常大的工作量，因此，查询优化器将其优化为先继续过滤 name（逐个遍历），这时过滤后的数据变少，在进行回表，这样效率就提高了。

当然，我们也可以开启和关闭索引下推，默认情况下启用索引条件下推，可以通过设置系统变量 optimizer_switch 控制：index_condition_pushdown

7.2 ICP的使用条件

1. 如果表访问的类型为 range、 ref、 eq_ref和ref_or_null 可以使用ICP
2. ICP可以用于InnoDB 和MyISAM表，包括分区表 InnoDB和 MyISAM 表
3. 对于InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少I/O 操作
4. 当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP 不会减少I/0.
5. 相关子查询的条件不能使用ICP

8、其他查询优化策略

8.1 EXISTS 和 IN 的区别

用 IN 还是 EXISTS 都是依据 小表驱动大表这条标准来的

select * from A
where id in(select id from B)
先查 B 表，在查A，所以 B 表小，A表循环匹配的次数少，适合B表更小的情况

select a.* from A a
where exists(select id from B b where a.id=b.id)
将A表的数据一条条放到里面匹配判断，所以 A 表小，B表循环匹配的次数少，适合A表更小的情况

8.2 COUNT(*)、COUNT(1) 和 COUNT(具体字段) 效率

效率比对的前提是: 如果你要统计的是某个字段的非空数据行数，就没有必要讨论了，我们考虑是记录数的统计
1、 OUNT()和 COUNT(1) 都是对所有结果进行 COUNT，COUNT()和COUNT(1) 本质上并没有区别。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计;如果没有 WHERE 子句，则是对数据表的数据行数进行统计。
2、如果是 MySAM 存储引警，统计数据表的行数只需要 0(1)的复杂度，这是因为每张 MISAM 的数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。
如果是InnoDB 存储引擎，因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，所以无法像 MyISAM 一样，维护一个row_count变量，因此需要采用 扫描全表，是0(n)的复杂度，进行循环+计数的方式来完成统计。
3、在innoDB 引擎中，如果采用 COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚族索引，聚族索引包含的信息多，明显会大于二级索引(非聚族索引)。
对于 COUNT()和 COUNT(1)来说，它们不需要章找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

MySQL mvcc机制

MySQL的MVCC（多版本并发控制）是一种用于实现数据库并发控制的机制。它允许**读操作和写操作同时进行，提高了数据库的并发性能和事务隔离级别。**用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MVCC的核心思想是为每个事务创建一个可见性视图，这个视图决定了事务能够看到哪些数据版本。在MVCC中，每个数据行都会保存多个版本，每个版本都有一个时间戳或者事务ID来标识。当一个事务开始时，它会获得一个全局唯一的事务ID，并且只能看到在该事务开始之前已经提交的数据版本。

在读操作中，MVCC会根据事务的时间戳或者事务ID来选择合适的数据版本。如果某个数据版本的创建时间晚于当前事务的开始时间，则该数据对当前事务不可见。这保证了读操作不会受到正在进行的写操作的影响。

在写操作中，MVCC会为新写入的数据创建一个新的数据版本，并将旧的数据版本标记为过期。这样可以避免写操作与读操作互相阻塞，提高了并发性能。

需要注意的是，MVCC机制只适用于使用InnoDB存储引擎的表，而不适用于MyISAM等其他存储引擎。此外，MVCC仅适用于具有一定事务隔离级别的事务，如可重复读（REPEATABLE READ）和序列化（SERIALIZABLE）级别。

总结来说，MySQL的MVCC机制通过为每个事务创建可见性视图，使用多个数据版本来实现并发控制，提高了数据库的并发性能和事务隔离级别。

8.3 关于 SELECT(*)

不建议使用，原因有两点

1、MySQL 在解析的过程中，会通过 查询数据字典 将“*”按序转换成所有列名，这会大大的耗费资源和时间
2、无法使用 覆盖索引

8.4 LIMIT 1 对优化的影响

针对的是会扫描全表的 SOL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT1了。

8.5 多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源:

• 回滚段上用于恢复数据的信息
• 被程序语句获得的锁
• redo / undo log buffer 中的空间
• 管理上述3 种资源中的内部花费

在MySQL中，commit是指将之前的数据库事务提交到数据库，让其永久保存下来。在MySQL中，默认情况下，每一个查询都会自动提交（autocommit）。

当我们使用事务操作时，需要通过commit语句来显式地提交事务，以确保之前的操作被永久保存。如果没有明确地执行commit语句，则事务中所做的更改将不会被保存，并且回滚（rollback）到之前的状态。

示例代码：

Copy CodeSTART TRANSACTION;    -- 开始事务
UPDATE users SET balance = balance - 100 WHERE id = 1;   -- 修改用户余额
UPDATE products SET stock = stock - 1 WHERE id = 2;      -- 减少库存
COMMIT;               -- 提交事务

以上代码中，我们使用了START TRANSACTION语句来开始一个事务，然后分别更新了用户余额和产品库存两个表。最后，使用COMMIT语句将整个事务提交到数据库中。如果在执行这个事务过程中出现了任何错误，可以使用ROLLBACK语句来回滚这个事务，使得所有更改都不会被保存到数据库中。

9、淘宝数据库，主键如何设计的?

我们知道，主键是全局唯一且单调递增的，如果是小的系统，我们可以采用MySQL自带的主键自增功能，虽然有一些问题，但可以接受，面对淘宝这类数据量和并发量都非常大的系统，在使用自增主键是不可靠的

1、唯一性得不到保障，这类大系统不可能是一个库，可能存在主键冲突
2、不安全，别人很容易通过对外接口猜测对应的信息，比如通过id获取，/user/1，从而暴露信息
3、性能差，需要在数据库服务器端生成

综上，我们引出雪花算法，这种基于分布式的主键策略很好的解决了这些问题，详细可以阅读 雪花算法（SnowFlake）