MySQL 是怎样运行的：从根儿上理解 MySQL读书笔记

这是一本掘金小册，看目录确实更适合面向面试学习，写的不错。

前面三节分别是开篇介绍，介绍MySQL的安装，启动，连接，介绍了下存储引擎，配置文件，启动选项，系统变量，状态变量。这些不是特别重要。

字符集和比较规则

• 字符集指的是某个字符范围的编码规则。

• 比较规则是针对某个字符集中的字符比较大小的一种规则。

字符集和比较规则有四级，服务器级别，数据库级别，表级别，列级别。

三个重要的系统变量：

• character_set_client：服务器解码请求时使用的字符集
• character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results: 服务器向客户端返回数据时使用的字符集

采用这个语句将这三个变量设为同样的值。

SET NAMES 字符集名;

在Pg中只能指定数据库的编码，可以指定列的排序规则。

MySQL中的utf8和utf8mb4：

utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。

utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

InnoDB的记录结构

以记录为单位向表中插入数据，记录在磁盘的存放格式就是行格式。

行格式分为四种：Compact、Redundant、Dynamic和Compressed

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

COMPACT行格式

变长字段长度列表：由于变长字段长度变化，为了正确解析数据，需要额外记录列中的值的真实数据的长度，每个变长字段的真实字节长度按照逆序排列。至于这个字节长度占多少字节呢？要根据对应边长字段的字符集和允许长度考虑：如果该可变字段允许存储的最大字节数（M×W）超过255字节并且真实存储的字节数（L）超过127字节，则使用2个字节，否则使用1个字节。

假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用SHOW CHARSET语句的结果中的Maxlen列，比方说utf8字符集中的W就是3，gbk字符集中的W就是2，ascii字符集中的W就是1。

对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是M×W。

假设它实际存储的字符串占用的字节数是L。

• NULL值的列的长度不存储
• 如果没有变长的列，则不存储

注意：对于char(10)这样的定长的字段，也不是一定不存储变长字段长度列表的，如果使用Unicode这样的变长字符集，也是会存进去。

null值列表：记录允许为null的列的null值情况，如果全是非null列，则为空。用二进制位1表示列为null，0表示非null，也是逆序排列。

记录头信息：

记录的真实数据：除了显性的几个列外，还有DB_ROW_ID（如果没有主键，也找不到唯一键，就生成一个）、DB_TRX_ID（事务id）、DB_ROLL_PTR（回滚指针）

Redundant行格式

很老的一种格式，特点是使用偏移量。

行溢出数据

VARCHAR(M)最多能存储的数据：该列最多用65535字节，这里面不止数据本身，还有数据长度的字节，null的标识。如果是ASCII编码（一个字符一个字节），列为NOT NULL，那么数据本身就能存65532字节。如果是别的字符集，比如utf8，那就只能存65532/3个字符。

对于ASCII这种情况，如果真的插入65532个字符，则会产生行溢出，因为InnoDB中存取的单位是页，页大小为16KB，16384字节，一个页放不下了。

Dynamic和Compressed行格式

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

另外，Compressed行格式会采用压缩算法对页面进行压缩。

InnoDB数据页结构

1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做数据页。前面还提到过溢出页。
2. 一个数据页可以被大致划分为7个部分，分别是
   • File Header，表示页的一些通用信息，占固定的38字节。
   • Page Header，表示数据页专有的一些信息，占固定的56个字节。
   • Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。
   • User Records：真实存储我们插入的记录的部分，大小不固定。
   • Free Space：页中尚未使用的部分，大小不确定。
   • Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
   • File Trailer：用于检验页是否完整的部分，占用固定的8个字节。
3. 每个记录的头信息中都有一个next_record属性，从而使页中的所有记录串联成一个单链表。
4. InnoDB会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory（页目录）中，所以在一个页中根据主键查找记录是非常快的，分为两步：
   • 通过二分法确定该记录所在的槽
   • 通过记录的next_record属性遍历该槽所在的组中的各个记录。
5. 每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表。
6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

其中delete_mask是删除标记，n_owned是记录所在槽的记录个数，heap_no记录所在页中的位置，最小记录和最大记录的heap_no为0和1，record_type0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。

next_record表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，而且是恰好在行记录中真实数据和行记录头信息的中间，这样方便向两边读取数据，并且提高缓存命中率。

对于分组（槽），有一下规定：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 18 条之间，剩下的分组中记录的条数范围只能在是 48 条之间。

B+树索引

1. 页内部的记录使用单向链表，按照主键排序，普通记录record type=0，最大最小记录record type=2和3；
2. 页内部划分槽组成页目录
3. 页与页之间组成双向链表
4. 增加一个数据页（和普通的数据页一样，类型都是0x45BF）用于保存目录项记录，record type=1，这种页一样有页目录，只有主键值和页编号，此外页内主键值最小的记录的min_rec_mask值为1，其余为0
5. 叶子节点存储的是完整的用户记录

由这样的多级结构组成索引。其中主键排序组成的为聚簇索引。

同样一棵树，不再按照主键串联起来，而是其他需要所有的列排序串起来，就是二级索引，二级索引的叶子节点不存储完整记录，只存储列的值和对应的主键。

通过二级索引插到主键，再通过主键查询完整的记录称为回表

联合索引就是多个列都参与排序，叶子节点就是多个列+主键。

根节点是最先创建，固定不变的。

注意：创建二级索引时，索引列不一定像主键和唯一键那样是唯一，所以实际上二级索引的目录项记录中有索引列值，页号和主键。

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)
);

以这个三个列构成的联合索引为例。

• 全值匹配：SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';，where条件里的顺序无所谓。

• 匹配左边的列：SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';，这是可以用到索引的

• 匹配列前缀：SELECT * FROM person_info WHERE name LIKE 'As%';

• 范围值匹配：SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';这样子对于联合索引idx_name_birthday_phone_number来说，只能用到name列的部分，而用不到birthday列的部分，因为只有name值相同的情况下才能用birthday列的值进行排序，而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。

• 精确匹配左边的列并范围匹配另外一列：SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

• 用于分组

• 用于排序：SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;，这时的顺序需要和索引一样

不可以使用索引进行排序的几种情况:

• order by后面ASC，DESC混用
• 排序列不在一个索引中
• 排序列是表达式形式的

回表导致的代价：读取二级索引是顺序IO，但是查出来的主键是分散在各处的，是随机IO。

避免回表：覆盖索引，即查询的字段正好都在索引中。

索引的选择：

• 只为搜索，排序，分组的字段建索引
• 列的区分度越高越好
• 索引列的类型尽量小，运算快，占用小
• 索引字符串值的前缀，比如列类型是字符串，可能很长，但是可以只对前是个字符索引，节约下。但是就导致排序之类的不能用索引了。
• 让索引列在表达式中单独出现：WHERE my_col * 2 < 4这样不行。

数据目录

这节没啥内容，在数据目录下每个数据库有一个数据库命名的文件夹，里面有个idb文件，属于一个独立表空间（以前所有数据库都在一个系统表空间），此外还有通用表空间，undo表空间等，表空间对应一个或多个文件。

Inodb的表空间

文件头的内容

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字节	页属于哪个表空间

独立表空间结构

64个页组成一个区（1MB）。256个区为一组。

第一个组的前三个页类型固定的，FSP_HDR（表空间唯一，登记表空间整体信息和本组所有区的属性），IBUF_BITMAP，INODE

其余组的前两个页类型固定，XDES（登记本组所有区的信息），IBUF_BITMAP。

为什么要有区？简单说就是如果数据量大，有非常多个页组成，那就需要把这些页放在一起，满足顺序IO，这些页组成了区。为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区。

而考虑到范围查询时，是对叶子节点进行扫描，需要区分叶子节点和非叶子节点，所以又把这两种页所在的区合成段，分为两个段。

由于一个索引需要两个段，对应至少两个区，也就是2MB，如果表的数据很少，这样分配就浪费了。所以有种区叫碎片区。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。

当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。

由于碎片区的存在，区就分为四类：

• 空闲的区：现在还没有用到这个区中的任何页面。

• 有剩余空间的碎片区：表示碎片区中还有可用的页面。

• 没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。

• 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

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

为了管理这些区，出现了XDES Entry这个结构，每个区对应一个，这个结构记录了区的一些属性。其结构如下：

• Segment ID（8字节）：区所在的段编号
• List Node：12字节用于把XDES Entry穿成链表
• State：四字节，表明区的状态
• Page State Bitmap（16字节）：共128bit，每两个bit对应区内的一个页，这两个bit的第一个表示页是否有空闲，第二个没用到

插入数据本质上就是向表中各个索引的叶子节点段、非叶子节点段插入数据：

• 当段中数据较少的时候，首先会查看表空间中是否有状态为FREE_FRAG的区，也就是找还有空闲空间的碎片区，如果找到了，那么从该区中取一些零散的页把数据插进去；否则到表空间下申请一个状态为FREE的区，也就是空闲的区，把该区的状态变为FREE_FRAG，然后从该新申请的区中取一些零散的页把数据插进去。之后不同的段使用零散页的时候都会从该区中取，直到该区中没有空闲空间，然后该区的状态就变成了FULL_FRAG

  问题是如何判断哪些区是FREE的，尤其是在区非常多的时候，遍历XDES Entry链表显然不合理。

  做法是FREE状态的区的XDES Entry串成一个链表，FREE_FRAG的串成零一个链表，FULL_FRAG也串成一个链表。

  每当我们想找一个FREE_FRAG状态的区时，就直接把FREE_FRAG链表的头节点拿出来，从这个节点中取一些零散的页来插入数据，当这个节点对应的区用完时，就修改一下这个节点的State字段的值，然后从FREE_FRAG链表中移到FULL_FRAG链表中。同理，如果FREE_FRAG链表中一个节点都没有，那么就直接从FREE链表中取一个节点移动到FREE_FRAG链表的状态，并修改该节点的STATE字段值为FREE_FRAG，然后从这个节点对应的区中获取零散的页就好了。

• 段中数据已经占满了32个零散的页后，就直接申请完整的区来插入数据

  如何区分每个区是属于哪个段呢（这里隐含的意思是区已经分配给了段，不是碎片区那种）？也是建立三个链表：FREE链表：同一个段中，所有页面都是空闲的区对应的XDES Entry结构会被加入到这个链表。注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的；NOT_FULL链表：同一个段中，仍有空闲空间的区对应的XDES Entry结构会被加入到这个链表；FULL链表：同一个段中，已经没有空闲空间的区对应的XDES Entry结构会被加入到这个链表。

假设一个表有一个聚簇索引和一个二级索引，则有15个链表：每个索引两个段，共四个段，每个段三个链表，共12个，加上直属于表空间的三个链表

有了这一堆链表后，就能快速的知道表空间内各个区的使用情况。

此外还有一个List Base Node的结构用于定位链表的首尾节点：

某个链表对应的List Base Node是放在表空间中固定位置的。

综上所述，表空间是由若干个区组成的，每个区都对应一个XDES Entry的结构，直属于表空间的区对应的XDES Entry结构可以分成FREE、FREE_FRAG和FULL_FRAG这3个链表；每个段可以附属若干个区，每个段中的区对应的XDES Entry结构可以分成FREE、NOT_FULL和FULL这3个链表。每个链表都对应一个List Base Node的结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数。正是因为这些链表的存在，管理这些区才变成了一件so easy的事情。

段的结构：

每个段都有个INODE Entry结构来记录一下段中的属性：

它的各个部分释义如下：

• Segment ID: 就是指这个INODE Entry结构对应的段的编号（ID）。

• NOT_FULL_N_USED: 这个字段指的是在NOT_FULL链表中已经使用了多少个页面。

• 3个List Base Node: 分别为段的FREE链表、NOT_FULL链表、FULL链表定义了List Base Node，这样我们想查找某个段的某个链表的头节点和尾节点的时候，就可以直接到这个部分找到对应链表的List Base Node。so easy!

• Magic Number：这个值是用来标记这个INODE Entry是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。如果这个数字是值的97937874，表明该INODE Entry已经初始化，否则没有被初始化。（不用纠结这个值有啥特殊含义，人家规定的）。

• Fragment Array Entry: 我们前边强调过无数次段是一些零散页面和一些完整的区的集合，每个Fragment Array Entry结构都对应着一个零散的页面，这个结构一共4个字节，表示一个零散页面的页号。

上面这些特殊的结构，链表都是存放在前面说的每个区开始的三个或两个页里面。

第一个组的第一个页：FSP_HDR类型，页号为0，存储表空间的整体信息和第一个组内256个区的XDES Entry结构

其中File Space Header部分是表空间头部信息，共112字节：

名称	占用空间大小	描述
Space ID	4字节	表空间的ID
Not Used	4字节	这4个字节未被使用，可以忽略
Size	4字节	当前表空间占有的页面数
FREE Limit	4字节	尚未被初始化的最小页号，大于或等于这个页号的区对应的XDES Entry结构都没有被加入FREE链表
Space Flags	4字节	表空间的一些占用存储空间比较小的属性
FRAG_N_USED	4字节	FREE_FRAG链表中已使用的页面数量
List Base Node for FREE List	16字节	FREE链表的基节点 （这就是直属于表空间的三个链表的基节点之一）
List Base Node for FREE_FRAG List	16字节	FREE_FRAG链表的基节点 （同上）
List Base Node for FULL_FRAG List	16字节	FULL_FRAG链表的基节点 （同上）
Next Unused Segment ID	8字节	当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List	16字节	SEG_INODES_FULL链表的基节点
List Base Node for SEG_INODES_FREE List	16字节	SEG_INODES_FREE链表的基节点

第一个组的第二页和其余组的第二页：IBUF_BITMAP类型和Change buffer相关，先不管

第一个组的第三页：INODE类型：

其中：INODE Entry部分，我们前边已经详细介绍过这个结构的组成了，主要包括对应的段内零散页面的地址以及附属于该段的FREE、NOT_FULL和FULL链表的基节点。每个INODE Entry结构占用192字节，一个页面里可以存储85个这样的结构。而List Node for INODE Page List涉及到INODE页组成的链表：因为一个表空间中可能存在超过85个段，所以可能一个INODE类型的页面不足以存储所有的段对应的INODE Entry结构，所以就需要额外的INODE类型的页面来存储这些结构。这里就出现两个链表：

• SEG_INODES_FULL链表：该链表中的INODE类型的页面中已经没有空闲空间来存储额外的INODE Entry结构了。
• SEG_INODES_FREE链表：该链表中的INODE类型的页面中还有空闲空间来存储额外的INODE Entry结构了

这两个链表的基节点就在File Space Header中。

其余组的第一个页：XDES类型，除了没有File Space Header，其他都是一样的。

一个索引是被分为两个段的，那索引如何找到这两个段呢？在索引的根页面的Page Header部分存在PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP字段，分别指向叶子节点段和非叶子节点段的头部信息，这两个字段是一个结构：

名称	占用字节数	描述
Space ID of the INODE Entry	4	INODE Entry结构所在的表空间ID
Page Number of the INODE Entry	4	INODE Entry结构所在的页面页号
Byte Offset of the INODE Ent	2	INODE Entry结构在该页面中的偏移量

访问方法

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;

const:

SELECT * FROM single_table WHERE id = 1438;
-- key2有唯一二级索引（唯一键）
SELECT * FROM single_table WHERE key2 = 3841;

ref:

-- 普通二级索引
SELECT * FROM single_table WHERE key1 = 'abc';

ref_or_null:

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

index

-- key_part2不是联合索引的最左列，但是查询结果和查询条件都在一个索引里，直接遍历整个二级索引
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

all：扫描聚簇索引

注意事项：

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

虽然这里有两个带索引的搜索条件，但是只会用一个二级索引，一般ref效果比range好，这里先选择了key1，找到相对应的主键后去聚簇索引再根据key2的条件筛选。

提取出正确的索引区间：

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

条件之间取交集：key2 > 200

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

条件之间取并集：key2 > 100

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

common_field没有索引，使用key2的索引定位记录的阶段用不到它，所以该条件相当于true：

SELECT * FROM single_table WHERE key2 > 100 AND true;
-- 简化后
SELECT * FROM single_table WHERE key2 > 100;

而OR的情况就不一样了：

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
-- 替换为true
SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
-- 简化
SELECT * FROM single_table WHERE TRUE;

这意味着索引区间为负无穷到正无穷，再加上回表的代价，那不如直接全表扫描。一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。

一般执行查询只会用一个索引，但是也有使用多个二级索引的情况：index merge

1. Intersection合并：

   SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
   SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
   SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

   从两个索引中取出主键做交集再回表

   MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

   情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

   情况二：主键列可以是范围匹配

2. Union合并：

   SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'
   SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

   情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

   情况二：主键列可以是范围匹配

   情况三：使用Intersection索引合并的搜索条件

连接的原理

嵌套循环连接（Nested-Loop Join）：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录

这种查询显然代价很大，所以一种改进是使用索引。

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

这里是内连接，假定使用t1为驱动表，查出t2.m2 = 2或3两条结果，那么从t1查出结果后到t2查询的过程为：

SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';
SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd';

这里可以在m2上加索引，变成ref访问，或者在n2上加索引，变成range访问。特别的，如果m2是主键或唯一键，则访问方法是eq_ref。

基于成本的优化

成本分为两点，IO（读取数据页）和CPU（检查一条记录是否符合搜索条件，排序），前者的成本常数为1（读取一页为1），后者为0.2（计算一条记录为0.2）

1. 先计算全表扫描的成本：根据每个表的统计信息表（）找出表的页数和记录数算出来成本
2. 找出查询中使用到的索引
   1. 根据查询的条件判断同一个索引中有一个区间，每一个区间的成本为1，接着找出每个区间内的最左和最右记录，计算区间内的记录数，假设此时条件为key2 > 10 AND key2 < 1000，则此时的成本为1+97，97为区间内的记录数。有97条记录，就需要回表97次，回表的成本为1，接着检查这97条记录是否满足其他条件，成本为97*0.2
3. 对比各个索引的成本和全表扫描的成本

上面是单表的估算，多表连接查询也是类似，总体上都是估算页数，记录数，然后基于各种成本常数算出成本。

基于规则的优化

• 条件化简，具体来讲有，常量传递，移除不必要的括号，等值传递，表达式计算等
• 外连接消除：通过空值拒绝，将外连接转为内连接
• 子查询优化：
  • 如果是标量子查询或者行子查询，外层查询和子查询就是独立的，先从外层查询查出一条记录，再去执行子查询
  • 如果是不相关的in查询，由于in可能很大，所以在in的参数太多时，就会转为一个临时表然后用连接查询
  • 其他优化省略