下面内容为《MySQL是怎样运行的 从根儿上理解MySQL》学习笔记。
说实话老实研究MySQL不如好好地把postgres使用搞透彻。
MySQL服务器程序的进程称为MySQL数据库实例(instance)
在安装MySQL时，无论用源码编译安装还是官方提供的安装包，无论采用哪种安装方式，一定要记住MySQL安装在哪里，一定要记住MySQL的安装目录。
[gaowanlu@vhost02 a5game_data]$ which mysql
/usr/bin/mysql
[gaowanlu@vhost02 a5game_data]$ which mysqld
/usr/sbin/mysqld
[gaowanlu@vhost02 a5game_data]$ ./bin/mysqld或者绝对路径
/usr/sbin/mysqld比如，环境变量PATH的值为 /usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin。 这个值表明，在我输入某个命令时，系统会在 /usr/local/bin、/usr/bin、/bin、/usr/sbin和/sbin目录下按照顺序依次寻找输入的这个命令.如果寻找成功，则执行该命令.
也可以修改这个环境变量PATH.把MySQL安装目录下的bin目录的绝对路径添加到PATH中.修改后的环境变量PATH的值为 /usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/sbin/mysqld• 这样一来，无论命令行解释器的当前工作目录是啥，都可以直接输入可执行文件的名字来启动，比如下面这样.
mysqld在类UNIX系统，用来启动MYSQL服务器程序的可执行文件有很多，大部分都位于MYSQL安装目录的bin目录下。
mysqld可执行文件就表示MySQL服务器程序，运行这个可执行文件就可以直接启动一个MySQL服务器进程.但这个可执行文件并不常用。
mysqld_safe是一个启动脚本，它会间接调用my叫ld并持续监控服务器的运行状态.当服务器进程出现错误时，它还可以帮助重启服务器程序.另外，使用mysqld_safe启动MySQL服务器程序时，它会将服务器程序的出错信息和其他诊断信息输出到错误日志，以方便后期查找发生错误的原因.
[gaowanlu@vhost02 bin]$ ps -ef | grep mysqld
mysql 1595 1 0 Feb27 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 1760 1595 0 Feb27 ? 00:03:11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
gaowanlu 37846 17788 0 15:12 pts/5 00:00:00 grep --color=auto mysqld出错日志默认写到一个以.err为扩展名的文件中，该文件位于MySQL的数据目录中。
mysql.server也是一个启动脚本，它会间接调用mysqld_safe。有些安装了MySQL可能没有。
mysql.server start
mysql.server stop其实我们在一台计算机上也可以运行多个服务器实例，也就是运行多个MySQL服务器进程。mysqld_multi 可执行文件可以启动或停止多个服务器进程，也能报告它们的运行状态.
它提供了两种启动方法,手动启动和以服务的形式启动。
在Windows系统中安装完MySQL之后，MySQL安装目录的bin目录下也会存在mysqld可执行文件·在命令行解释器中输入mysqld，或者直接在bin目录下双击该文件，就可以启叨MySQL服务器程序了。
如果我们需要在计算机上长时间运行某个程序，并且无论是谁在使用这台计算机，程序的运行都不受影响，就可以把它注册为一个Windows服务，由操作系统帮我们管理。
"完整的可执行文件路径" --install [-manual] [服务名]如果我们添加了-manual边项，就表示在Windows系统启动的时候不自动启动该服务，否则会自动启动·
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --install
net start MySQL
net stop MySQL如果你喜欢图形界面，可以通过Windows的服务管理器并用鼠标点击的方式来启动和停止服务.
bin目录下有许多客户端程序，如mysqladmin、mysqldump、mysqlcheck等。主要关注mysql。
mysql -h主机名 -u用户 -p密码 -P端口连接成功后将会进入一个mysql终端输入以下任意一个命令可以退出连接
mysql> quit
mysql> exit
mysql> \q本质上是进程间的通信。
端口号是一个整数值，取值范围是0~65535,MySQL服务器在启动时会默认申请3306端口号，之后就在这个端口号上等待客户端进程进行连接。
指定监听其他端口
mysqld -P3307如果是Windows用户，本地客户端连接本地服务器，可以使用命名管道和共享内存。
如下面例子
#服务器
mysqld --socket=/tmp/a.txt
#客户端
mysqld -hlocalhost -u root --socket=/tmp/a.txt -p下面是客户端向服务器发送命令过程
每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程专门处理与这个客户端的交互;当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端.这样就不用频繁地创建和销毁线程，从而节省了开销.
在客户端程序发起连续时，需要携带主机信息、用户名、密码等信息，服务器程序会对客户端程序提供的这些信息进行认证.如果认证失败，服务辑程序会拒绝连接.另外，如果客户端程序和服务器程序不运行在一台计算机上，我们还可以通过采用传输层安全性(TransportLayer Security, TLS)协议对连接进行加密，从而保证数据传输的安全性。
MySQL服务器程序处理查询请求的过程也是这样，会把刚刚处理过的查询请求和结果缓存起来.如果下一次有同样的请求过来，直接从缓存中查找结果就好了，就不用再去底层的表中查找了.这个查询缓存可以在不同的客户端之间共享，也就是说，如果客户端A刚刚发送了一个查询请求，而客户端B之后发送了同样的查询请求，那么客户端B的这次查询就可以直接使用查询缓存中的数据了.
MySQL服务器并没有人那么聪明，如果两个查询请求有任何字符上的不同，例如空格、注释、大小写，都会导致缓存不会命中，如果查询请求中包含某些系统函数、用户自定义变量和函数、系统表，如mysql、information_schema、performance_schema数据库中的表，则这个请求不会被缓存。
不过既然是缓存，那就有缓存失效的时候.MySQL的缓存系统会监测涉及的每张衰，只要该袤的结构或者数据被修改，比如对该表使用了的INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，则与该表有关的所有查询缓存都将变为无效并从查询缓存中删除!
从MySQL5.7.20开始，不才在荐使用查询缓存，在小贴士MySQL8.0中直接将其删除.
如果查询缓存没有命中，接下来就需要进入正式的查询阶段了.因为客户端程序发送过来的请求只是一段文本，所以MySQL服务器程序首先妥对这段文本进行分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上.
从本质上来说，这个从指定的文本中提取出需妥的信息算是一个编译过程，涉及词曾习、法解析、语法分析、语义分析等阶段
在语法解析后，服务器程序获得到了需要的信息，比如要查询的表和列是哪些、搜索条件是什么等。这些并不够，因为我们写的MySQL语句执行起来效率可能并不是很高，MySQL的优化程序会对语句进行一些优化，如外连接转换为内连接、表达式简化、子查询转为连接等一堆东西。
MySQL服务器把数据的存储和提取操作都封装到了一个名为存储引擎的模块中.
表是由一行一行的记录组成的，但这只是一个逻辑上的概念.在物理上如何表示记录，怎么从表中读取数据，以及怎么把数据写入具体的物理存储器上，都是存储引擎负责的事情.为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表可能有不同的存储结构，采用的存取算法也可能不同.
MySQL服务器处理请求的过程简单地划分为server层和存储引擎层.连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存取的功能划分为server层的功能，存取真实数据的功能划分为存储引擎层的功能.各种不同的存储引擎为server层提供统一的调用接口，其中包含了几十个不同用途的底层函数，比如”读取索引第一条记录1111读取索引下一条记录”“插入记录”等.
不同存储引擎对于某些功能支持情况也不相同。
InnoDB从MySQL5.5.5版本开始作为MySQL的默认存储引擎，之前版本的默认存储引擎为MyISAM.
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)Transactions 是否支持事务、XA 是否支持分布式事务、Savepoints 是否支持事务的部分回滚。
CREATE TABLE 表名(
建表语句；
) ENGINE = 存储引擎名称;ALTER TABLE 表名 ENGINE = 存储引擎名称;mysql设置选项一般都有各自的默认值，比如服务器允许同时连入的客户端的默认数量是151，表的默认存储引擎是InnoDB。 可以在程序启动的时候修改这些默认值，对于这种在程序启动时指定的设置项也称之为启动选项 startup option,启动选项一般也可以在配置文件中指定。
如在启动时就禁止各客户端使用TCP/IP网络进行通信，
mysqld --skip-networking指定存储引擎
mysqld --default-storage-engine-MyISAM不需要记忆，使用 –help查看即可
mysqld --help
mysqld_safe --help常用的长形式和短形式含义
--host -h 主机名
--user -u 用户名
--password -p 密码
--port -P 端口
--version -V 版本信息配置文件的路径
MySQL程序在启动时会在多个路径下寻找配置文件，这些路径有的是固定的，有的可以在命令行中指定。
类UNIX操作系统中的配置文件
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf 特定于服务器选项
default-extra-file 命令行指定的额外配置文件路径
~/.my.cnf 特定于用户的选项
~/.mylogin.cnf 特定于用户的登录路径选项配置文件内容,如下面这样
[server]
[具体的启动选项...]
[mysqld]
[具体的启动选项...]
[mysqld_safe]
[具体的启动选项...]
[client]
[具体的启动选项...]
[mysql]
[具体的启动选项...]
[mysqladmin]
[具体的启动选项...]程序的对应类别和能读取的组
mysqld [mysqld] [server]
mysqld_safe [mysqld] [server] [mysqld_safe]
mysql_server [mysqld] [server] [mysql.server]
mysql [mysql] [client]
mysqladmin [mysqladmin] [client]
mysqldump [mysqldump] [client]内容不是很重要
MySQL服务器程序在运行过程中会用到许多影响程序行为的变量，它们被称为系统变量。比如，允许同时连入的客户端数量用系统变盘max_connections表示;表的默认存储引擎用系统变量default_storage_engine表示，查询缓存的大小用系统变量query_ _cache_size表示MySQL服务器程序的系统变量有好几百个，这里不再一一列举.每个系统变量都看一个默认值，我们可以使用命令行或者配置文件中的选项在启动服务器时改变一些系统变量的值·大多数系统变量的值也可以在程序运行过程中修改，而无须停止并重新启动服务器.
SHOW VARIABLES;
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)模糊查询
mysql> SHOW VARIABLES LIKE 'default%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
| default_password_lifetime | 0 |
| default_storage_engine | InnoDB |
| default_table_encryption | OFF |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
+-------------------------------+-----------------------+
7 rows in set, 1 warning (0.00 sec)设计MySQL的大叔提出了系统变量的作用范围的概念，具体来说，作用范围分为以下两种。
SET [GLOBAL|SESSION] 系统变量名 = 值
# 默认为SESSION范围的查看不同作用范围的系统变量
SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式]MySQL服务器程序中维护了许多关于程序运行状态的变量，它们被称为状态变量。
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式]mysql> SHOW STATUS LIKE 'thread%' ;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 4 |
| Threads_created | 4 |
| Threads_running | 2 |
+-------------------+-------+
4 rows in set (0.00 sec)一般是以下情况直接比较二进制数据，如果不区分大小写的话，则
共收录128个字符，包括空格、标点符号、数字、大小写字母和-些不可见字符。由于ASCJJ字符集总共才128个字符，所以可以使用一个字节来进行编码.我们来看几个字符的编码方式:
'L' -> 01001100 (十六进制 0x4c 十进制76)
'M' -> 01001101 (十六进制 0x4D 十进制77)共收录256个字符，它在ASCIl字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母).ISO 8859-1字符集也可以使用一个字节来进行编码 这个字符集也有一个别名Latin1.
收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄i吾西里尔字母，收录汉字6763个，收录其他文字符号682个.这种字符集同时又兼容ASCJJ字符集，所以在编码方式上显得有些奇怪:如果该字符在ASCIl字符集中，则采用一字节编码:否则采用两字节编码. 这种使用不同字节数来表示一个字符的编码方式称为变长编码方式.
GBK字符集只是在收录的字符范围上对GB2312字符集进行了扩充，编码方式兼容GB2312字符集.
几乎收录了当今世界各个国家/地区使用的字符，而且还在不断扩充.这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符时需要使用1-4字节，比如下面这样:
'L' -> 01001100 (1宇节，十六进制0x4C)
'啊'-> 111001011001010110001010 （3宇节，十六进制0xE5958A）UTF-8只是Unicode字符集的一种编码方案，Unicode字符集可以采用UTF-8、UTF-16、UTF-32这几种编码方案。UTF-8使用1~4字节编码一个字符，UTF-16使用2 或4字节编码一个字符，UTF-32使用4字节编码一个字符。
对同一个字符，不同字符集可能采用不同的编码方式，对于汉字 ‘我’，ASCII字符集中没有这个字符，UTF-8和GB2312字符集对汉字 ’我’的编码方式不同
UTF-8 3字节，十六进制形式为 0xE68891
GB2312 2字节，十六进制形式为 0xCED2MySQL定义了下面两个概念。
在MySQL中，utf8是utf8mb3的别名，所以后文在MySQL中提到utf8时，就意味着使用1~3字节来表示一个字符。如果有使用4字节编码一个字符的情况，比如存储一些emoji表情，请使用uf8mb4。
在MySQL8.0中，设计MySQL的大叔已经很大程度地优化了utf8mb4字符集的性能，而且已经将其设置为默认的字符集。
查看当前MySQL中支持的字符集
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式]mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.08 sec)其中Default collation列表示这种字符集中一种默认的比较规则，Maxlen列，它代表这种字符集最多需要几个字节来表示一个字符。
查看MySQL中支持的比较规则
SHOW COLLATION [LIKE 匹配的模式];mysql> SHOW COLLATION LIKE 'utf8%';
+-----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb3_bin | utf8mb3 | 83 | | Yes | 1 | PAD SPACE |
| utf8mb3_croatian_ci | utf8mb3 | 213 | | Yes | 8 | PAD SPACE |
| utf8mb3_czech_ci | utf8mb3 | 202 | | Yes | 8 | PAD SPACE |
| utf8mb3_danish_ci | utf8mb3 | 203 | | Yes | 8 | PAD SPACE |
| utf8mb3_esperanto_ci | utf8mb3 | 209 | | Yes | 8 | PAD SPACE |
...
...
117 rows in set (0.00 sec)后缀 英文意义 描述
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitive 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较在执行 SHOW COLLATION语句后返回的结果中，Default列的值为YES的比较规则，就是该字符集的默认比较规则，比如utf8字符集默认的比较规则就是utf8_general_ci。
MySQL有4个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别、列级别。
MySQL提供了两个系统变量表示服务器级别的字符集和比较规则
系统变量 描述
character_set_server 服务器级别的字符集
collation_server 服务器级别的比较规则mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.15 sec)
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.01 sec)在启动服务器程序时，可以通过启动选项或者在服务器程序运行过程中使用SET语句来修改这两个变量的值。
在创建和修改数据库时可以指定该数据库的字符集和比较规则
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];
ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];其中DEFAULT可以省略。
如果想查看当前数据库使用的字符集和比较规则，可以查看两个系统变量的值，前提是使用USE语句选择当前的默认数据库。如果没有默认数据库，则变量与服务器级别下相应的系统变量具有相同的值。
系统变量 描述
character_set_database 当前数据库的字符集
collation_database 当前数据库的比较规则mysql> use db_log;
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 VARIABLES LIKE 'character_set_database';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_database';
+--------------------+--------------------+
| Variable_name | Value |
+--------------------+--------------------+
| collation_database | utf8mb4_0900_ai_ci |
+--------------------+--------------------+
1 row in set (0.00 sec)不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则.
在数据库的创建语句中不指定字符集和比较规则，将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。
可以在创建和修改表的时候指定表的字符集和比较规则
CREATE TABLE 表名(列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];
ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];如果创建表的语句中没有指明字符集和比较规则，贝u使用该表所在数据库的字符集和比较规则作为该袤的字符集和比较规则.
CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];在修改列的字符集时需要注意，如果列中存储的数据不能用修改后的字符集进行表示，则会发生错误.比如，最初使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为回CII的话就会出错，因为脑Cll字符集并不能表示汉字字符.
由于字符集和比较规则之间相互关联，因此如果只修改字符集，比较规则也会跟着变化:如果只修改比较规则，字符集也会跟着变化.具体规则如下
对于给定的表，应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定每个列存储的实际数据所占用的存储空间大小。
mysql> INSERT INTO t(col) VALUES('我我');如果列col使用的字符集是gbk,一个字符’我’在gbk中的编码为0xCED2,占用2字节，则两个字符就占用了4字节，如果把该列字符集修改为utf8,这两个字符实际占用的存储空间就是6字节了。
如果使用不同地字符集去解码这个字节序列，最后得到地结果可能让你挠头。
如UTF-8字符集下编码地字节序 0xE68891,程序A把字节序发送到程序B，程序B使用不同的字符集解码这个字节序列使用GBK字符集。
看第一个字节0xE6,它的值大于0x7F(127),说明待取字符是两字节编码，GBK编码表查找0xE688对应字符。
继续读0x91,值也大于0x7F试图读下一个字节，发现后面没有了，所以这是个半字符。根本解不出来字符’我’。
如果接收 0xE68891 这个字节序列的程序按照UTF-8字符集进行解码，然后又把它按照GBK字符集进行编码，则编码后的字节序列就是 0xCED2.我们把这个过程称为字符集的转换，也就是字符串’我’从UTF-8字符集转换为GBK字符集.
用户角度看，客户端发送的请求以及服务器返回的响应都是一个字符串。机器角度看，客户端发送的请求和服务器返回的响应本质就是一个字节序列。
一般情况下，客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致。
当使用类UNIX操作系统时,环境变量 LC_ALL LC_CTYPE LANG 的值决定了操作系统当前使用的是那种字符集。
优先级 LC_ALL > LC_CTYPE > LANG
root@kTY-HK3-QL-86139:/home/root/note# echo $LC_ALL
root@kTY-HK3-QL-86139:/home/root/note# echo $LC_CTYPE
root@kTY-HK3-QL-86139:/home/root/note# echo $LANG
C.UTF-8获取类UNIX操作系统当前使用的字符集时，调用的是系统函数 nl_langinfo(CODESET), man 3 nl_langinfo 可以查看详情
服务器接收到的请求就是一个字节序列，服务器将这个字节序列看作是使用系统变量 character_set_client代表的字符集进行编码的字节序列，每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的 character_set_client变量，这个变量是SESSION级别的。
加入客户端实际使用UTF-8字符集来编码请求的字符串，可以通过命令将character_set_client设置为latin1字符集
SET character_set_client=latin1;服务器解析失败是会发出警告的。
mysql> SET character_set_client=ascii;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT '我';
+-----+
| ??? |
+-----+
| ??? |
+-----+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1300
Message: Cannot convert string '\xE6\x88\x91' from ascii to utf8mb4
1 row in set (0.00 sec)服务器会将请求的字节序当作采用 character_set_client 对应的字符集进行编码的字节序，在真正处理请求时又会将其转换为使用SESSION级别的系统变量 charater_set_connection 对应的字符集进行编码的字节序列。
假如有一个表
CREATE TABLE tt(
c VARCHAR(100)
) ENGINE=INNODB CHARSET=utf8;列c采用字符集和表级别字符集utf8一致，这里采用默认的比较规则utf8_general_ci。
mysql> SELECT * FROM tt;
+--+
|c |
+--+
|我|
+--+如果现在将 character_set_connection 和 collation_connection 的值分别设置为 gbk 和 gbk_chinese_ci
SELECT * FROM tt WHERE c = '我';上面语句中’我’时gbk编码的，列c是utf8编码的，MySQL规定，在这种情况下，列的字符集和排序规则的优先级更高，这里需要将请求中的字符串 从gbk转换为utf8，然后再使用列c的比较规则utf8_genneral_ci进行比较。
服务器为客户端响应发送的结果编码，这取决于SESSION级别的系统变量 character_set_results的值。
系统变量 描述
character_set_client 服务器认为请求是按照该系统变量指定的字符集进行编码的
character_set_connection 服务器在处理请求时，会把请求字节序列从character_set_client转换为character_set_connection
character_set_results 服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码每个MySQL客户端都维护着一个客户端默认字符集，客户端在启动时会自动检测所在操作系统当前使用的字符集，并按照一定的规则映射成MySQL支持的字符集，然后将该字符集作为客户端默认的字符集.
通常的情况是，操作系统当前使用什么字符集，就映射为什么字符集.但是总存在一些特殊情况.假如操作系统当前使用的是asCll字符集，则会被映射为MySQL支持的latinl字符集.如果MySQL不支持操作系统当前使用的字符集，则会将客户端默认的字符集设置为MySQL的默认字符集.
在MySQL5.7以及之前的版本中，MySQL的默认字符集为latin1，自MySQL8.0开始默认字符集为utf8mb4。
另外，如果在启动MySQL客户端时设置了default-cbaracter-set启动选项，那么服务器会忽视操作系统当前使用的字符集，直接将default-cbaracter-set启动选项中指定的值作为客户端的默认字符集。
在连接服务器时，客户端将默认的字符集信息与用户名、密码等信息一起发送给服务器，服务器接收到后将三个系统变量的值初始化为客户端的默认字符集。
SET NAMES charset_name;
上面这条语句与下面这3条语句的效果一样:
set character_set_client = charset_name;
set character_set_connection = charset_name;
set character_set_results = charset_name;SET NAMES
不会改变客户端实际使用的字符集。客户端需要自行确保发送的字符串与声明的字符集一致。
如果客户端的默认字符集与 SET NAMES
声明的字符集不一致，可能会导致字符编码问题。
为了避免字符集不一致的问题，建议在客户端连接时明确指定字符集。例如，在 MySQL 客户端连接时可以使用：
mysql --default-character-set=utf8mb4 -u username -p或者在代码中设置字符集
<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
$mysqli->set_charset("utf8mb4");如果操作系统当前使用的字符集为UTF-8,在启动MySQL客户端时使用了--default-character-set=gbk,客户端默认字符集会被设置为gbk,服务器的character_set_results也会被设置为gbk,假设服务器发送
‘我’，发送的为 gbk编码 0xCED2
对于类UNIX操作系统来说，会把接收到的字节序列(也就是0xCED2)直接写到黑框框中，并默认使用操作系统当前使用的字符集(UTF-8)来解释这个字符·很显然无法解释，所以我们在屏幕上看到的就是乱码。
大多数用于字符串字符排序
SELECT * FROM t ORDER BY col;MySQL 服务器中负责对表中的数据进行读取和写入工作的部分是存储引擎，而服务器又支持不同类型的存储引擎，比如 InnoDB、MyISAM、MEMORY啥的。用的最多的就是InnoDB我们下面就学这个。
InnoDB是一个将表中的数据存储在磁盘上的存储引擎，从磁盘中读到内存也是它做的事情。当我们想从表中获取某些记录时，InnoDB不可能一条条把记录从磁盘上读出来，InnoDB采取的方式是， 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位。InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
服务器运行过程中不可以更改页面大小。系统变量 innodb_page_size表明了InnoDB存储引擎中的页大小，默认值为16384,该变量只能在第一次初始化MySQL数据目录时指定。
目前为止，设计了4中不同类型的行格式，分别为 COMPACT、REDUNDANT、DYNAMIC、COMPRESSED。
📌 行格式常见选项说明：
| 格式名 | 说明 |
|---|---|
| COMPACT | 默认格式，InnoDB 默认使用的行格式（5.0 以后） |
| REDUNDANT | 老旧格式，仅用于兼容 MySQL 4.1 之前版本 |
| DYNAMIC | 支持更长的行，TEXT/BLOB 存储在页外，推荐使用 |
| COMPRESSED | 数据压缩存储，节省磁盘空间 |
可以在创建或修改表的语句中指定记录所使用的行格式。
CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称。例如
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE users ROW_FORMAT=COMPRESSED;一条完整的记录分为记录的额外信息和记录的真实数据两大部分。
分别是变长字段长度列表、NULL值列表和记录头信息。
MySQL支持一些变长的数据类型，比如 VARCHAR(M)、VARBINARY(M)、各种TEXT类型、各种BLOB类型。这些变长字段占用的存储空间分为两部分。
在COMPACT行格式中，所有变长字段的真实数据占用的字节数都存放在记录的开头位置，从而形成一个变长字段长度列表，各变长字段的真实数据占用的字节数按照列的顺序逆序存放.再次强调一遍，是逆序存放!
例如有个表
c1 VARCHAR(10) | c2 VARCHAR(10) | c3 VARCHAR(10)
'aaaa'|'bbb'|'d'
实际占用字节数
0x04 0x03 0x01在COMPACT行，头部变长信息部分就会存储
01 03 04这三个字段的长度较短，其实际长度用一个字节就可以存储，但是长度大的话一个字就存不下了，InnoDB有一套字节的规则。
引入W、M和L这几个符号，先分别看看这些符号的意思.
InnoDB在读取记录的变长字段长度列表时会先看表结构，先查看表结构，如果变长字段允许存储的最大字节数不大于255,则直接认为使用1个字节表示真实数据占用的字节数。
InnoDB 第一个二进制位作为标志位：如果字节的第一个位为0，那该字节就是一个单独的字段长度（使用一个字节表示不大于127的二进制的第一个位都为0），如果该字节第一个位为1，那么该字节就是半个字段长度。
对于占用字节数非常多的字段，比如某个字段长度大于16KB，那么如果记录在单个页面内无法存储时，InnoDB会把一部分数据存放到所谓的溢出页中，在变长字段长度列表处只存储留在本页面中的长度。
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)
mysql>
并不是所有记录都有这个 变长字段长度列表 部分，比如表中所有的列都不是变长的数据类型，这一部分就不需要有。
COMPACT行格式把一条记录中值为NULL的列统一管理起来，存储到NULL值列表中。
一个位就能表示一个字段是否为NULL，标记NOT NULL的列不用存标记位。在顺序按照列的顺序的逆序。高位补零。
除了 变长字段长度列表、NULL值列表 之外，还有一个用于描述记录的记录头信息，由固定的5个字节组成，40个二进制位。
| 大小bit | 名称 | 描述 |
|---|---|---|
| 1 | 预留位1 | 没有使用 |
| 1 | 预留位2 | 没有使用 |
| 1 | delete_mask | 标记该记录是否被删除 |
| 1 | min_rec_mask | B+树的每层非叶子节点中的最小记录都会添加该标记 |
| 4 | n_owned | 表示当前记录拥有的记录数 |
| 13 | heap_no | 表示当前记录在记录堆的位置信息 |
| 3 | record_type | 0表示普通记录、1表示B+树非叶子节点记录、2表示最小记录、3表示最大记录 |
| 16 | next_record | 表示下一条记录的相对位置 |
记录的真实数据 除了 c1、c2、c3、c4 这几个我们自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列）
| 列名 | 是否必须 | 占用空间 | 描述 |
|---|---|---|---|
| row_id | 否 | 6字节 | 行ID，唯一标识一条记录 |
| transaction_id | 是 | 6字节 | 事务ID |
| roll_pointer | 是 | 字节 | 回滚指针 |
实际上这几个列真正名称是: DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。
InnoDB 表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义，InnoDB会为表默认添加一个名为 row_id 的隐藏列作为主键。
表使用的是 ascii 字符集，其中 0x61616161 就表示字符串
aaaa。第一条记录c3列的值它是 CHAR(10)
类型的，实际存储的字符串是 cc, ascii字符集中字节表示是
0x6363,虽然只用2字节，
但整个c3列仍然占用了10个字节空间。其他字符都用空格字符填充ascii字符集表示就是
0x20。
第2条记录中，c3和c4列的值都为NULL，它们被存在了前边的NULL值列表处。
c1、c2、c4 列的类型是 VARCHAR(10)，而 c3 列的类型是 CHAR(10)。Compact行格式下只会把变长类型的列的长度逆序存到变长字段长度列表中，
但如果不是 ascii 这种定长字符集，那么存N个字符所需的空间可能是变长的。
mysql> ALTER TABLE record_format_demo MODIFY COLUMN c3 CHAR(10) CHARACTER SET utf8;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0上面将 c3 列的字符编码改为 utf8。
对于 CHAR(M) 类型的列来说，当列用的字符集为定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。
变长字符集的 CHAR(M) 类型的列要求至少占用M个字节，而 VARCHAR(M) 却没有这个要求。例如，使用utf8字符集的 CHAR(10) 的列来说， 该列存储的数据字节长度的范围是10~30个字节。
Redundant 译为 冗余的。
Redundant行是 MySQL5.0之前用的一种行格式，也就是说它已经非常老了，看看乐呵就好。
把表 record_format_demo 的行格式修改为 Redundant
mysql> ALTER TABLE record_format_demo ROW_FORMAT=Redundant;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0Redundant行格式的开头是 字段长度偏移列表
比如第一条记录的 字段长度偏移列表 就是：
25 24 1A 17 13 0C 06因为它是逆序排放的，所以按照列的顺序排列就是
06 0C 13 17 1A 24 25说实话这些东西如果不是真正的是数据库底层开发者，我想快速过一遍了解下已经很不错了，先把上层的经验搞充足，这些都是留给DB开发来做的事情，况且Redundant行格式几乎现在已经不用了。
“一行记录 约等于 塞进16KB的盒子，如果塞不下，就必须外包。”
VARCHAR(M) 类型的列最多可以占用65536个字节。
MySQL对一条记录占用的最大存储空间是有限制的，处理 BLOB或者TEXT类型的列以外， 其他的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65536个字节。
页并不是16KB都给你，一页里 还有 页头、页目录、行指针、行记录元数据，真正留给一行数据的空间大约只有8KB左右（经验值）。
MySQL中磁盘和内存交互的基本单位是页，MySQL是以页为基本单位来管理存储空间的，
记录都会被分配到某个页中存储，而一个页大小一般是16KB,也就是 16384 字节，
而一个 VARCHAR(M) 类型的列最多可以存 65535
个字节，这样就可能造成一个页存不下情况，
无论是 Compact和Reduntant 行格式，对于占用存储空间非常大的列， 在记录的真实数据只会存储该列的一部分数据，其他剩余部分分散存储在几个其他页中， 然后记录的页的地址。
数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做 行溢出 ，存储超出 768 字节的那些页面也被称为 溢出页 。
你不用关注这个临界点是什么，只要知道如果我们想要一个行中存储了很大的数据时， 可能发生 行溢出 的现象。
这两格式和Compact行格式挺像，但在处理 行溢出 数据它们不会记录真实数据存储字段真实数据的前 768字节，而是把所有字节都存储到其他页面中，只记录数据储存其他页面的地址。
Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。
InnoDB为了不同地目的而设计了许多种不同类型地页，先聚焦 存放表种记录的那种页，官方称为这种存放记录的页为索引(INDEX)页。
数据页代表的16KB大小的存储空间可以被划分为多个部分，如
| 区域 | 中文名 | 大致大小 | 作用 |
|---|---|---|---|
| File Header | 文件头 | 38 B | 页级元信息（页号、LSN、校验等） |
| Page Header | 页头 | 56 B | 页内状态、记录数量、链表指针 |
| Infimum + Supremum | 最小/最大记录 | 26 B | 页内记录边界哨兵 |
| User Records | 用户记录 | 不固定 | 真正的行数据 |
| Free Space | 空闲空间 | 不固定 | 插入新记录用 |
| Page Directory | 页目录 | 不固定 | 记录槽（slot），加速查找 |
| File Trailer | 文件尾 | 8 B | 校验页完整性 |
看看就行了，没必要钻牛角尖。
一开始生成新的页时，是没有UserRecords的，只有FreeSpace，当 记录多了 UserRecord会使用剩余FreeSpace。
InnoDB 的行记录头（record header）当成一张“微型身份证”来看。它藏在每一行记录的最前面，体积极小，却决定了这行数据在 B+Tree 里怎么活、怎么死、怎么被遍历。
在 InnoDB 的索引页（page）里，记录是按链表串起来的，同时又服务于 B+Tree。记录头就是“链表 + 树结构 + MVCC”的交汇点。
这是“这行是不是已经被判死刑”的标志位。
当你执行 DELETE FROM t ... 时，InnoDB
并不会立刻物理删除这行，而是把 delete_mask 置 1。
它的哲学是：“先宣布死亡，等合适的实际再收尸。”
好处：
delete_mask = 1 ≠ 这行不存在，只是“逻辑已删”。
当数据页中存在多条被删除的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，已备之后重用这部分存储空间。
这是一个非常冷门但很关键的标志。 它只会出现在非叶子节点（internal node）中。
在 B+Tree 的内部页里，每个子节点都需要一个“下界 key”。
min_rec_mask = 1 表示：
这条记录是这个 page 中的“最小 key 代表”
换句话说，它是路标，不是普通居民。
普通表数据的叶子节点里，这个标志永远是 0。
这个字段和 page directory（二分查找加速结构）有关。
在一个 page 里，并不是每条记录都会出现在 page directory 中。 directory 里的某条记录，会“代表”它后面的一小段记录。
n_owned 表示：
这条记录在 directory 视角下，管着后面多少条记录
可以把它理解成：“你是小组长，名下有几个人？”
它的存在，让 InnoDB 能在 page 内做二分查找 + 链表遍历，避免从头扫到尾。
这是记录在页内的逻辑编号，按插入顺序分配。
几个重要事实：
所以 heap_no 不是“当前顺序”，而是“出生编号”。 它更像对象 ID，不是座位号。
这个字段决定了：“你到底是个什么东西？”
常见取值语义是：
infimum / supremum 这两条是 InnoDB 的“宇宙边界”， 保证链表永远有头有尾，算法可以少写很多 if。
这是页内单向链表的核心。
每条记录都知道：
“我后面是谁（以字节偏移量表示）”
而且next_record刚好指下一个记录的，记录头和真实数据的中间，向左就是记录头向右就是真实数据。
注意关键点：
当你做 WHERE id BETWEEN 10 AND 20， MySQL
找到第一条后，就顺着 next_record 一路溜达。
InnoDB的一行记录，本质上是
典型的“数据库世界观”：空间换时间，复杂换确定性。
Page Directory 是 InnoDB 在每个 16KB 页里，悄悄塞的一层微型索引，用极小的代价，让页内查找从“盲走”变成“先定位再微调”。
把 Page Directory（页目录） 想成 InnoDB 在一个 page 里偷偷放的一本“索引的索引”。 page 里已经有记录了，但记录太多、全靠链表走会慢，于是 InnoDB 在页尾又加了一层结构，专门用来加速页内查找。
大白话版结论：
Page Directory = 用很少的指针，帮你在一个 page 内做“近似二分查找”。
不是完整索引，是“路标集合”。
一个 InnoDB page（16KB）的大致布局是：
| File Header |
| Page Header |
| Infimum |
| User Records (链表) |
| Free Space |
| Page Directory | <-- 在页尾，倒着长
| File Trailer |关键点：
page 内的记录是这样组织的：
物理上：乱序存放（heap）
逻辑上：靠 next_record 串成 有序链表
如果没有 Page Directory： 查一条记录 = 从 infimum 开始顺链表一个个走 最坏情况 ≈ 几百条比较
Page Directory 的目标只有一个：别从头扫。
它是一个数组，每个元素是：
2 字节：record offset（相对 page 起始位置）这些 offset 指向的是 “某些关键记录”，而不是全部记录。
这些被选中的记录，满足两个条件：
n_owned，正是为 Page Directory 服务的。
从这条记录开始，后面有多少条记录归它“管”
Directory 指向：
R1 (n_owned = 4) -> R1, R2, R3, R4
R5 (n_owned = 4) -> R5, R6, R7, R8
R9 (n_owned = 3) -> R9, R10, R11Directory 里只有 R1、R5、R9 三个入口。
查找流程
当你在一个 page 里查 key = 7：
于是复杂度变成：
O(log D) + O(k)D = 目录项数量（很小） k = n_owned（通常 4～8）
这就是 InnoDB 的“折中艺术”。
如果每条记录一个目录项：
InnoDB 的取舍是：
少量目录项 + 少量线性扫描 = 总体最优
工程上，这是极其成熟的选择。
重要但容易混淆的一点：
一个管“城市导航”，一个管“楼内导视”。
InnoDB 存储引擎，它不存“业务数据”，而是存“关于这个页面本身的信息”。
它主要回答这些问题：
没有 Page Header，InnoDB 连“怎么在这一页里走路”都不知道。
PAGE_N_RECS
这一页里有多少条用户记录 * 不包括 Infimum / Supremum
* 插入、删除都会更新
* B+Tree 分裂时，它是判断“这页是不是太挤了”的重要指标
一句话：页的人口普查表
PAGE_FREE
空闲记录链表的头指针 * 页里被删除的记录不会立刻抹掉 * 而是挂到“free list”里，等下次复用 * PAGE_FREE 指向这条链表的第一条记录 这就是 InnoDB “删除不是真删除”的物理原因之一。
PAGE_LAST_INSERT
最近一次插入的位置 * 用来优化顺序插入（典型：自增主键） * 如果你一直往右边插，InnoDB 会偷懒少做查找 这也是为什么 自增主键 对 InnoDB 非常友好。
PAGE_DIRECTION + PAGE_N_DIRECTION
插入趋势统计 * 记录最近插入是：向左？向右？随机？ * 连续多少次同一方向 它不参与逻辑正确性，只参与性能优化。可以理解为：页在“观察人类的插入习惯”。
PAGE_HEAP_TOP
当前堆空间的顶部
PAGE_GARBAGE
页内碎片大小 * 删除、更新（变长字段）会产生碎片 * 超过阈值，InnoDB 可能会触发页整理 碎片不是 bug，是生命周期的副作用。
PAGE_LEVEL
当前页在B+树中所处的层级
这是判断“这一页是存数据行，还是存索引指针”的关键字段。
PAGE_INDEX_ID
这个页属于哪个索引
把 InnoDB 页想成一个小型城市：
城市运转，全靠它。
你平时写 SQL 不会直接碰它，但很多“数据库玄学问题”都和它有关：
在 InnoDB 里，几乎所有“文件型对象”——表空间页（page）、undo 页、索引页——开头 38 个字节都是同一套结构，这一段就叫 FIL Header。给存储引擎和崩溃恢复用的“生命体征监测仪”。
File Header = 每个 InnoDB 页的“身份证 + 体检表”
无论这是：
开头一定是同样的 38 字节。
按字节偏移来讲最清楚：
偏移 长度 含义
0 4 FIL_PAGE_SPACE_OR_CHKSUM
4 4 FIL_PAGE_OFFSET
8 4 FIL_PAGE_PREV
12 4 FIL_PAGE_NEXT
16 8 FIL_PAGE_LSN
24 2 FIL_PAGE_TYPE
26 8 FIL_PAGE_FILE_FLUSH_LSN
34 4 FIL_PAGE_ARCH_LOG_NO_OR_SPACE_IDFIL_PAGE_SPACE_OR_CHKSUM（4B）
页校验和
用来判断：“这页是不是在磁盘上被写坏了？”
InnoDB 会在读页时校验它，校验失败就认为页损坏。
FIL_PAGE_OFFSET（4B）
文件偏移 = page_no * 16KBFIL_PAGE_PREV / FIL_PAGE_NEXT（各 4B）
比如：
B+Tree、undo、free list 全靠这个“页级指针”在磁盘上跳转。
FIL_PAGE_LSN（8B）
恢复时逻辑是：
redo log 的 LSN > 页的 LSN,说明这页没来得及刷盘,要 redo。
FIL_PAGE_TYPE（2B）
FIL_PAGE_INDEX 索引页（最常见）
FIL_PAGE_UNDO_LOG undo 页
FIL_PAGE_INODE 段 inode 页
FIL_PAGE_IBUF_FREE_LIST
FIL_PAGE_TYPE_SYS 系统页看到这个字段，InnoDB 才知道：“哦，这是索引页，那我按 B+Tree 结构解析。”
FIL_PAGE_FILE_FLUSH_LSN（8B）
普通数据页基本不关心，系统页才重要。
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID（4B）
这就是为什么：同一个页号,在不同表空间里不会冲突
它不只存在于 .ibd 文件
它存在于：
只要是InnoDB页，就有File Header。
这套设计解决了三个硬核问题：
LSN + redo log
不需要 fsync 每一页
页号 → 直接定位
prev / next → 页级遍历
checksum 防 silent corruption
page type 防解析错位
在 MySQL 里，File Trailer 是个听起来像“文件尾巴”的东西，实际上也确实如此，但它不是随便加的尾注，而是 InnoDB 数据文件里一个非常关键的安全装置。
一个直觉版结论：
File Trailer 是 InnoDB 页（page）末尾的 8 个字节，用来防止“写了一半就断电”的那种宇宙级尴尬。
InnoDB 的每个 page 默认是 16KB，结构大概是：
| File Header | Page Header | Page Body | Page Directory | File Trailer |File Trailer 固定占 8 字节，内容是：
这 8 字节和 File Header 里的对应字段是镜像关系。
想象一个经典灾难场景：
如果没有 File Trailer，InnoDB 很可能把这个 残缺 page 当成完整 page 使用，那就不是报错，是直接悄悄写坏数据。
File Trailer 的作用就是：“你写完了吗？写完整了吗？和我头部说的一样吗？”
启动或读page时，InnoDB会做三件事：
如果不一致，结论只有一个：这个Page在写入过程中被中断过
接下来就轮到 redo log 登场，尝试恢复；实在不行，这个 page 会被标记为损坏。
前面说了，InnoDB数据页的7个组成部分，知道各数据页可以组成一个双向链表，而每个数据页 中的记录会按照主键值从小到大的顺序组成一个 单向链表 ，每个数据页都会为存储在它里边儿的记录生成一个 页目录 ，在通过主键查找某条记录的时候可以在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对 应分组中的记录即可快速找到指定的记录。
下面的查找假设以
SELECT [列名列表] FROM 表名 WHERE 列名 = xxx
以主键为搜索条件，可以在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
以其他列作为搜索条件对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的 页目录 ，所以我们无法通过二分法快速定位相应的 槽 。这种情况下只能从 最小记录 开始依次遍历单链表中的每条记录，
可分为两个步骤：
在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找
因为要遍历所有的数据页，所以这种方式显然是超级耗时的。
先按照下面的表来说
mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
“下一个数据页中用户记录的主键必须大于上一个页中用户记录的主键值。”
如果进行一些增删改操作，必须通过通过一些诸如记录移动的操作来始终保 证这个状态一直成立：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程 我们也可以称为 页分裂 。
给所有的页建立一个目录项。
这样就能根据主键值快速查找某条记录了。
上面介绍了一个简易粗暴的索引方案，为了在根据主键值进行查找时使用二分法快速定位具体的目录 项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：
复用之前存储用户记录的数据页来存储目录项。
用页里的record_type属性区分
但是随着数据越来越多，目录项记录也太多了，无法支撑快速查找了，能不是在为目录再建立一层索引。
这玩意就是数据结构中的 B+ 树。注意是 B+树 不是 B树。
实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为 叶子节点 或 叶节点，其余用来存放目录项的 节点称为 非叶子节点 或者 内节点，其中 B+ 树最上面的节点也称为根节点。
我们来算一下，树层数和能容纳的最大记录量。
存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的。
假设，假设，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：
上边介绍的 B+ 树本身就是一个目录，或者说本身就是一个索引。它有两个特点：
我们把具有这两种特性的 B+ 树称为 聚簇索引 ，所有完整的用户记录都存放在这个 聚簇索引 的叶子节点处。
这种聚簇索引并不需要我们在MySQL语句中显式使用INDEX语句去创建。
InnoDB存储引擎会自动地为我们创建聚簇索引。在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。
上边地 聚簇索引 只能在搜索条件是主键值时才能发挥作用，其他搜索条件怎么办。
可以多建几棵B+树，不同地B+树中的数据采用不同的排序规则。
数据叶子节点只有，这棵树的排序方式还有主键，没有其他列。
在根据c2查找时，只会查找c2数据对应的主键，然后再拿主键去主键的B+树去查找。
由于使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为 为c2列建立的索引。
也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比如想让B+树按照c2和c3列的大小排序：
千万要注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。
实际上B+树的形成过程是这样的：
一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的 根节点 的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从那个固定的地方取出 根节点 的页号，从而来访问这个索引。
对于二级索引，只拿 索引列 + 页号 的搭配是不靠谱的。
例如又想插入 9 1 c，该插到 页4还是页5，懵逼了。
为了让新插入记录能找到自己在那个页里，对于二级索引的节点的目录项记录的内容实际上是由三部分组成的
插入时，先把新记录的c2列的值和页3中各目录项记录的c2列值作比较，如果c2相同可以接着比较主键值， 因为B+树同一层中不同目录记录的c2列+主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录。
InnoDB 的一个数据页至少可以存放两条记录。
聚簇索引的那棵 B+ 树的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形 结构，但是却将索引和数据分开存储：
将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为 数据文件 。这个文件并不划分为若干个 数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。
由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为 索引文件 的另一个文件中。 MyISAM 会单独为 表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是 主键值 + 行号 的组 合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！
这一点和 InnoDB 是完全不相同的，在 InnoDB 存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查 找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着 MyISAM 中建立的索引相当于全 部都是 二级索引。
如果有需要的话，我们也可以对其它的列分别建立索引或者建立 联合索引，原理和 InnoDB 中的索引差不 多，不过在叶子节点处存储的是 相应的列 + 行号 。这些索引也全部都是 二级索引 。
MyISAM中却是索引是索引、数据是数据。
InnoDB和MyISAM会自动为 主键 或 声明为 UNIQUE 的列去自动建立B+树索引，但如果想为其他列建立索引就需要我们 显式的去指明。
每建立一个索引都会建立一棵B+树，每插入一条记录都要维护各个记录、数据页的排序关系，这很费性能和存储空间。
可以在创建表时指定需要建立索引的 单个列 或 建立联合索引的多个列
CREATE TALBE 表名 (
各种列的信息 ··· ,
[KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)
# 其中的 KEY 和 INDEX 是同义词，任意选用一个就可以。也可以在修改表结构的时候添加索引:
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);也可以在修改表结构的时候删除索引：
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;比如在创建 index_demo 表时就为 c2 和 c3 列添加一个联合索引
CREATE TABLE index_demo(
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1),
INDEX idx_c2_c3 (c2, c3)
);后续删除这个索引
ALTER TABLE index_demo DROP INDEX idx_c2_c3;虽然索引是个好东西，可不能乱建，如何更好的使用索引之前要了解一下使用它的代价。
空间上的代价
每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认16KB，一棵很大的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 表：
索引 idx_name_birthday_phone_number 先按照 name 列的值进行排序， 如果name列相同，则按照birthday列值排序，如果birthday列的值也相同则按照phone_number值进行排序。
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';你能够想象查询过程，先看name再看birthday再看phone_numer，有疑问，如果WHERE子句中的几个搜索条件顺序对查询结果有影响吗， 调换name、birthday、phone_number
SELECT * FROM person_info WHERE birthday = '1990-09-27' AND phone_number = '15123983239' AND name = 'Ashburn';没什么影响，MySQL有一个查询优化器的东西，会分析这些搜索条件并且按照可以使用的索引中列的顺序来 决定使用哪个搜索条件，后使用哪个搜索条件。
在搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行
SELECT * FROM person_info WHERE name = 'Ashburn';或者包含多个左边的列也行
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';如果这样就不行了
SELECT * FROM person_info WHERE birthday = '1990-09-27'; 因为直接看 birthday 索引是无序的，因为先按照name先排的序。
需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中尽可能多的列，
搜索条件中的各个列必须是联合索引中从左边连续的列，比如联合索引
idx_name_birthday_phone_number 中列的定义顺序是
name、birthday、phone_number
SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239'; 很不幸这样就只能利用到 name 列的索引，birthday和phone_number的索引就用不上了。
比较字符串大小就用到了该列的字符集和比较规则，所以一个排好序的字符串列其实有这样的特点：
也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的
SELECT * FROM person_info WHERE name LIKE 'As%';如果只给出后缀或者中间的某个字符串，比如这样：
SELECT * FROM person_info WHERE name LIKE '%As%';MySQL就无法快速定位了，只能全表扫描。
有些情况下我们需要思考如果 有一个url列 存了许多url
www.baidu.com
www.google.cn
www.gov.cn检索出com后缀的
WHERE url LIKE '%com';很明显索引会失效，可以优化为逆序存储
moc.udiab.www
moc.elgoog.www
nc.vog.www然后使用前缀匹配
WHERE url LIKE 'moc%';例如
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';查询过程是这样的，找到name值为Asa的记录，找到name值为Barlow的记录，所有记录用链表连起来的（记录之间单链表、数据页之间双链表），找到这些记录的主键值，再到 聚簇索引 中回表查找完整记录。
如果对多个列同时进行范围查找，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引。
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';birthday索引是失效的，只有name值相同情况下才能用birthday列的值进行排序。
如果左边的列是精确查找，右边列可以进行范围查找
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday
< '2000-12-31' AND phone_number > '15100000000'; 上面的 name、birthday索引都能被利用，phone_number就不行了。
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND AND phone_number > '15100000000';上面的完全利用了，idx_name_birthday_phone_number联合索引。
查询语句经常需要对查询出来的记录通过 ORDER BY 子句按某种规则排序，
一般情况下，需要把记录加载到内存，用排序算法，进行排序，结果集太大还要暂时借助磁盘空间，MySQL中把在内存中或磁盘上进行排序的方式统称为文件排序。
但如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤。
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10; idx_name_birthday_phone_number，这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表取出该索引中不包含的列就好了。
联合索引情况
ORDER BY phone_number, birthday, name上面是用不了B+树索引的，原因很明显。和上面说的WHERE条件差不多。
同理,下面两个就可以
ORDER BY name
ORDER BY name, birthday当联合索引左边列的值为常量，也可以使用后边的列进行排序
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;这个查询能使用联合索引进行排序是因为name列的值相同的记录是按照birthday，phone_number排序的。
ASC、DESC混用
B+树索引的排序升序降序规则是一致的，所有字段要升序都升序，要降序都降序。
ORDER BY name, birthday LIMIT 10;上面情况，直接从索引的最左边往右读10行记录就可以了，
ORDER BY name DESC, birthday DESC LIMIT 10,这种情况从索引最右边开始往左读10行记录就可以了。
SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;上面的 name默认是ASC，过程是这样的
先从索引的最左边确定name列最小的值，然后找到name列等于该值的记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
如果name列等于最小值的记录不足10条，再继续往右找name值第二小的，重复过程，知道找到10条记录或找完所有记录位置。
WHERE子句中出现了非排序使用到的索引列
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;这个查询只能将 country='China'
记录提取出来再进行排序。
但下面的可以正确利用索引
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10; 排序列包含非同一个索引的列
有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序
SELECT * FROM person_info ORDER BY name, country LIMIT 10; 排序列使用了复杂的表达式
这很明显根本没法利用索引，使用了UPPER函数修饰过的列就不是单独的列了。
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;对表中的记录按某些列进行分组
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number;这也和 WHERE、ORDER BY 什么时候正确利用 B+树 索引差不多。
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';从二级索引idx_name_birthday_phone_number 中检索主键时很快就行检索出来，因为是顺序I/O。
但是用拿到的一些主键去聚簇索引那记录时，就变成了随机IO。
需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不适用二级索引。
什么时候采用全表扫描，什么时候使用二级索引+回表，这就是查询优化器做得工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10; 添加了 LIMIT 10 的查询更容易让优化器采用 二级索引 + 回表 的方式进行查询。
对于有排序需求的查询
SELECT * FROM person_info ORDER BY name, birthday, phone_number;由于查询列表是 *
，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这
样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（ filesort
）低，所以优化器会倾向于使用 全表扫 描 的方式执行查询。
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlo w';上面的结果字段都在 idx_name_birthday_phone_number中，通过二级索引就能拿到全部字段，不必再回表了。
同样排序操作也优先使用 覆盖索引 的方式进行查询
SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_number;在建立索引时或编写查询语句时应该注意的一些事项。
只为出现在WHERE中子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引， 而出现在查询列表中的列就没必要建立索引了。
SELECT birthday, country FROM person_info WHERE name='Ashbrun';像查询列表中的birthday、country这两列就不需要建立索引了。
列的基础 指的是某列中不重复数据的个数，比如说某个列包含值 2,5,8,2,5,8,2,5,8 虽然有9条记录，但该列的基数是3.
在记录行数一定的情况下，列的基数越大，该列的值越分散，列的基数越小，该列中的值越集中。
假设某个列的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了。
而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。
结论：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
数据类型越小，在查询时进行的比较操作越快，能用 TINYINT存得下得就不用 MEDIUMINT,能用 INT的就不用BIGINT
数据类型越小，索引占用的空间越少，在一个数据页内可以放下更多的记录，减少磁盘I/O带来的性能损耗，意味着可以把更多的数据页缓存在内存中，加快读写效率。
这个建议对表的主键更加使用，不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值。
字符串列，字符串长度可能非常长，只在B+树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间， 还能大概解决排序的问题，何乐而不为
CREATE TABLE person_info(
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);name(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值前缀的策略是非常鼓励的，尤其是字符串类型能存储的字符比较多的时候。
索引列前缀对排序的影响
如果使用了索引列前缀，比如之前把name列的前10个字符放到了二级索引中，下面查询就尴尬了
SELECT * FROM person_info ORDER BY name LIMIT 10;因为二级索引中不包含完整的name列信息，无法对前十个字符相同，后面的字符不同的记录进行排序。
假设表中有一个整数列 my_col，我们为这个列建立了索引，下面两个WHERE子句虽然语义是一致的，但效率上却有差别
WHERE my_col * 2<4
WHERE my_col < 4/2第一个，my_col列不是以单独列形式出现的，无法使用索引，第二个可以。
InnoDB，表中的数据实际上都存在聚簇索引的叶子节点，而记录又是存储在数据页中，数据页和记录又是按照记录主键值从小到大顺序进行排列，
如果插入的记录的主键值依次增大，没插满一个数据页就换到下一个数据页继续插，如果插入的主键值忽大忽小，就比较麻烦。
依次增大，插入效率比较高，可以让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入。
例如
CREATE TABLE person_info(
id INT UNSIGNED 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(10), birthday, phone_number)
);有时候有意或无意的就对同一个列创建了多个索引，比如
CREATE TABLE person_info(
id INT UNSIGNED 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(10), birthday, phone_number),
KEY idx_name (name(10))
);很明显name列索引冗余了，维护这个索引只会增加维护成本，并不会对搜索有什么好处。
CREATE TABLE repeat_index_demo (
c1 INT PRIMARY KEY,
c2 INT,
UNIQUE uidx_c1 (c1),
INDEX idx_c1 (c1)
);c1既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
InnoDB、MyISM存储引擎把表存储在磁盘上，而操作系统用来管理磁盘的东西叫 文件系统。
InnoDB、MyISAM存储引擎把表存储在文件系统上。
MySQL服务器程序启动时回到文件系统某个目录加载一些文件，之后运行过程产生的数据也存储在那个目录下的某些文件， 这个目录就称为 数据目录。
安装目录：下非常重要的bin目录，存储着许多关于控制客户端程序和服务器程序的命令，如 mysql、mysqld、mysqld_safe等。
数据目录：用来存储MySQL在运行过程中产生的数据。
通过系统变量查看。当然你用Docker容器数据到底存在哪里那就是另一回事了。
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.098 sec)
mysql> 当使用CREATE DATABASE 创建一个数据库时，每个数据库都对应数据目录下的一个子目录
bash-5.1# pwd
/var/lib/mysql
bash-5.1# ls
'#ib_16384_0.dblwr' auto.cnf ca-key.pem dbname mysql performance_schema server-key.pem
'#ib_16384_1.dblwr' binlog.000001 ca.pem ib_buffer_pool mysql.ibd private_key.pem sys
'#innodb_redo' binlog.000002 client-cert.pem ibdata1 mysql.sock public_key.pem undo_001
'#innodb_temp' binlog.index client-key.pem ibtmp1 mysql_upgrade_history server-cert.pem undo_002我有一个 名字为 dbname 的数据库，其下面有一个dbname的文件夹。
数据都是以记录的形式插入到表中的，每个表的信息其实可以分为两种：
表结构：如表名、表里多少列、每列数据类型、约束条件、索引、字符集、比较规则等等。
具体存在哪里，版本不同答案也不同，这些不用关心，了解就好了。
InnoDB使用页为基本单位来管理存储空间，默认页大小为16KB。
回顾一下前面内容：
InnoDB提出了一个 表空间 或 文件空间 的概念。
每一个表空间可以划分为很多很多页，表数据就存放在某个 表空间 下的某些页里。
系统表空间
数据目录下有一个名为 ibdata1 的文件，就是对应的 系统表空间 在文件系统上的表示。
独立表空间
在MySQL5.6.6之后，InnoDB不会默认把各个表数据存在系统表空间，而是每个表建立一个独立表空间，
在数据库目录下有表对应的.ibd文件。
bash-5.1# cd dbname
bash-5.1# ls
dbuserrecord.ibd这些表空间可以转换，把表从系统表空间移到独立表空间、从独立表空间移到系统表空间。
除了上面两种表空间，还有一些 如 通用表空间、undo表空间、临时表空间 等等。
MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的，在文件系统中使用不同的文件来存储数据文件和索引文件。
MyISAM并没有所谓的表空间，表数据都存到数据库子目录下
如有个test表
test.frm test.MYD(数据文件) test.MYI(索引文件)MYSQL中的视图是虚拟的表，也就是某个查询语句的别名而已，存储视图时不需要存储真实数据，只需要 把其结构存储起来。描述视图结构的文件会被存在所属数据库对应的子目录下 视图名.frm 问文件。
如服务器进程文件存进程PID、服务器日志文件 如查询日志、错误日志、二进制日志、redo日志等等。
默认、自动生成的SSL和RSA证书和密钥文件。
MySQL数据都是存在文件系统中的，不得不受文件系统的一些制约，在数据库和表命名、表的大小和性能方面体现比较明显。
MySQL的几个系统数据库，
可以把表空间想象成被切分为许许多多页的池子，想为某个表插入一条记录的时候，就从池子中捞出一个对应的页把数据写进去。
| 描述 | 数值 | 用途 |
|---|---|---|
| FIL_PAGE_TYPE_ALLOCATED | 0x000 | 最新分配，还没使用 |
| FIL_PAGE_UNDO_LOG | 0x0002 | Undo日志页 |
| FIL_PAGE_INODE | 0x0003 | 段信息节点 |
| FIL_PAGE_IBUF_FREE_LIST | 0x0004 | Insert Buffer空闲列表 |
| FIL_PAGE_IBUF_BITMAP | 0x0005 | Inser 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 | 索引页 |
INDEX类型的页由7部分组成，其中的两个部分任何类型的页都会包含
FileHeader:
| 名称 | 占用空间大小 | 描述 |
|---|---|---|
| FIL_PAGE_SPACE_OR_CHKSUM | 4 字节 | 页的校验和（checksum值） |
| FIL_PAGE_OFFSET | 4 字节 | 页号 |
| FIL_PAGE_PREV | 4 字节 | 上一个页的页号 |
| FIL_PAGE_NEXT | 4 字节 | 下一个页的页号 |
| FIL_PAGE_LSN | 8 字节 | 页面被最后修改时对应的日志序列位置（英文名是：Log SequenceNumber） |
| FIL_PAGE_TYPE | 2 字节 | 该页的类型 |
| FIL_PAGE_FILE_FLUSH_LSN | 8 字节 | 仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值 |
| FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID | 4 字节 | 页属于哪个表空间 |
一个表空间最多可以拥有2的32次方个页，如果按照页默认16KB、一个表空间最多支持64TB的数据。
表空间中页很多，为更好管理这些页，InnoDB提出 区（extent）的概念，连续的64个页就是一个区，，一个区默认占用1MB大小。
每256个区为一组。
为了解决在B+树链表中相邻的两个页的物理位置离得非常远，就是所谓的随机I/O,随机I/O是非常慢的，应尽量让链表中相邻的页物理位置也相邻，尽可能的顺序I/O,为此引入了区的概念。
为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区 为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区。
如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，范围查询进行范围扫描的效果就大打折扣了。
叶子节点有自己独有的 区 ，非叶子节点也有自己独有的 区 。存放叶子节点的区的集合就算是一个 段 （ segment ），存放非叶子节点的区的集合也算是一个 段 。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。
具体来说，B+树中会分配两个不同的段：
叶子节点段：专门存储叶子节点的区。
非叶子节点段：专门存储非叶子节点的区。
这两个段分别用于存储不同类型的索引节点，使得数据的存储更加高效，尤其是在进行范围扫描时，可以减少不必要的随机I/O。
除了叶子节点和非叶子节点段，还有“碎片区”。碎片区是存储一些不连续或者不常用的数据块区域，通常用于处理空间未被充分利用的情况。
大体可分为4类：
InnoDB为这4种状态的区定义了特定的名词
|状态名|含义| |FREE|隶属于表空间 空闲的区| |FREE_FRAG|隶属于表空间 有剩余空间的碎片区| |FULL_FRAG|隶属于表空间 没有剩余空间的碎片区| |FSEG|隶属于段 附属于某个段的区|
表空间<-段<-区<-页为了方便管理这些区，InnnoDB设计了XSES Entry（Extent Descriptor Entry），每个区都对应着一个XDES Entry结构，
表空间会拉三个链表，
InnoDB为每个段的区对应的XDES Entry结构也建立了三个链表
每一个索引都对应两个段，每个段都会维护上述的3个链表
CREATE TABLE t (
c1 INT NOT NULL AUTO_INCREMENT,
c2 VARCHAR(100),
c3 VARCHAR(100),
PRIMARY KEY (c1),
KEY idx_c2 (c2)
)ENGINE=InnoDB;这个表有两个索引，一个聚簇索引、一个二级索引idx_c2,这个表共有4个段。每个段维护3个链表，整个独立表空间共需要维护15个链表。
优先找NOT_FULL的，用完了将其移到FULL链表中。
链表基节点
为了找到这些链表的头节点或尾节点，InnoDB设计了 List Base Node的结构。
结构里记录了链表的头、尾节点的位置以及链表中包含的节点数。
段不对应表空间中某一个连续的物理空间，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。
InnoDB为每个段都定义了一个INODE Entry记录段中属性。
它的各个部分解释
现在清楚了 表空间、段、区、XDES Entry、INODE Entry、各种以XDES Entry为节点的链表。
每个区对应的XDES Entry结构存储在表空间什么地方。上面看过段内的，区内也有相关
FSP_HDR 页处在表空间第一个页，它存储了表空间的一些整体属性以及组内256个区的对应XDESEntry
XDES Entry部分
XDESC Entry0对应 extent 0, XDES Entry 1对应 extent 1 以此类推。
表空间的区分为了若干个组，每组开头的一个页面记录着本组内所有的区对应的 XDES Entry 结构。
第一组第一个页面有些特殊，除了记录本组中的所有区对应XDES Entry外，还记录其他内容 就是 FSP_HDR 类型。
除去第一个分组以外，之后的每个分组的第一个页面只需要记录本组内所有的区对应的 XDES Entry 结构即可。
IBUF_BITMAP 类型
每组第二个页面都是IBUF_BITMAP
INODE 类型
第一个分组的第三个页面类型是INODE，每个段设计了一个INODE Entry结构
InnoDB将 INODE类型页面串成两个不同链表：SEG_INODES_FULL 链表：该链表中的 INODE 类型的页面中已经没有空闲空间来存储额外的 INODE Entry 结构了。 SEG_INODES_FREE 链表：该链表中的 INODE 类型的页面中还有空闲空间来存储额外的 INODE Entry 结构. 这两个链表的基节点就存储在 File Space Header 里边，这两个链表的基节点的位置是固定的。
创建一个段时，都会创建一个INODE Entry结构与之对应。
Segment Header结构
Space ID of the INODE Entry 4字节
Page Number of the INODE Entry 4字节
Bytes Offset of the INODE Entry 2字节跳过 Segment Header结构的运用
MySQL 中的 系统表 是存储在 系统表空间 上的，尤其是在 InnoDB 存储引擎下。
整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，所以会比独立表空间多出一些记录这些信息的页面。因为这个系统表空间最牛逼，相当于是表空间之首，所以它的 表空间 ID （Space ID）是 0 。
跳过 系统表空间的整体结构
这章节直接他妈的吐了，搞得读者都是搞数据库开发的一样，像时一个看过源码的再和你说源码里的各个数据结构，每个字段干嘛的，说实话这些对于90%的开发者平时都不用考虑，这是数据库底层优化专家要做的，写应用的知道这些玩意也用处不大，只有开头的一些目录和文件介绍还有点用。
对于开发，数据库就是一个软件，平时用的最多的就是查询，DBA时不时丢过来一些慢查询让优化。
先有个表
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;上面 signle_table 表建立了1个 聚簇索引 和 4个二级索引。
对于单个表的查询来说，MySQL把查询的执行方式大致分为两种：
同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能差老鼻子远了。
可以通过主键列来定位一条记录
# 将会利用聚簇索引
SELECT * FROM single_table WHERE id = 1438;利用key2二级索引
# 通过二级索引找到逐渐
# 通过主键回表 区聚簇索引查找
SELECT * FROM single_table WHERE key2 = 3841;这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const 意思是常数级别的，代价是可以忽略不计的。
但也有特殊情况
# 这样背后检索方式不是const访问方法
# 因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录
SELECT * FROM single_table WHERE key2 IS NULL;二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref
SELECT * FROM single_table WHERE key1='abc';会先去二级索引找出符合要求的主键，再拿着主键回表。
找出的主键数量少，效率还是很高的，如果拿出主键数量多，可能就尴尬了，有可能导致再回表还不如全表扫描。
key IS NULL这种形式的搜索条件最多只能使用red访问方法，而不是const的访问方法。
只要是最左边的连续索引列是与常熟的等值比较就可能采用ref的访问方法。
SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary'
AND key_part3 = 'penta kill';但如果最左边的连续索引列并不全部是等值比较，访问方法就不能称为ref了
SELECT * FROM single_table
WHERE key_part1 = 'god like' AND key_part2 > 'legendary'; ref_or_null 像下面的查询，等值 + or + null
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;先从 idx_key1 索引B+树找出 key1 IS NULL 和 key1 = 'abc'
的两个连续的记录范围，再拿着主键值找完整的用户记录。
有时候面对的搜索条件更复杂，比如
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);当然可以全表扫描方式执行查询，但也可以使用二级索引然后回表的方式执行。
MySQl把这种利用索引进行范围匹配的访问方法称为 range。
SELECT key_part1, key_part2, key_part3 FROM single_table
WHERE key2_part2 = 'abc';由于 key_part2 并不是联合索引 idx_key_part 最左索引列，所以无法使用 ref 或 range 访问方法。
但是查询列表只有3个列， key_part1、key_part2、key_part3, 而索引 idx_key_part 又包含这三个列。
可以直接遍历 idx_key_part 索引的叶子节点记录来比较 key_part2 是否满足，然后直接从索引叶子中获取结果列，不需要回表。
对于InnoDB表来说也就是直接扫描聚簇索引，MySQL把这种全表扫描执行查询的方式称之为：all。
一般情况下只能利用单个二级索引执行查询
SELECT * FROM single_table
WHERE key1='abc' AND key2>1000;优化器一般会根据single_table表的统计数据来判断到底使用哪个条件对应的二级索引中查询扫描的行数会少。
用key1二级索引，找到主键然后在回表然后过滤 key2条件。
对于B+树索引，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=、<>
或者 LIKE 操作符连接起来，就可以产生一个所谓的区间。
LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。
下面的两个语句效果是一样的。
SELECT * FROM single_table WHERE key2 IN (1438, 6328);
SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;# 取 (200, +∞)
SELECT * FROM single_table WHERE key2>100 AND key2>200;# 取(100， +∞)
SELECT * FROM single_table WHERE key2>100 OR key2>200;SELECT *
FROM single_table
WHERE key2>100 AND common_filed='abc';能利用的索引只有 idx_key2一个，先走idx_key2索引然后再回表然后过滤。
SELECT *
FROM single_table
WHERE key2>100 OR common_filed='abc';走过key2索引后，再回表还是的全表扫描，这样很显然直接去全表扫描效率更好。
有的查询搜索条件可能特别复杂，找出范围匹配的各个区间就挺烦
SELECT * FROM single_table WHERE
(key1 > 'xyz' AND key2 = 748) OR
(key1 < 'abc' AND key1 > 'Imn') OR
(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ; 查询的搜索条件涉及到了 key1、key2、common_filed这3个列，
key1有普通的二级索引 idx_key1、key2列有唯一二级索引idx_key2。
上面的查询除了有关key2和common_filed列不能使用到idx_key1索引外，key1 LIKE '%suf'也使用不到索引，
把这些搜索条件替换为TRUE
(key1 > 'xyz' AND TRUE) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))进行简化
(key1 > 'xyz') OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 > 'zzz')
(key1 > 'xyz') OR (key1 > 'zzz')
key1 > 'xyz'如果使用 idx_key1 索引执行查询，需要把满足 key1 > xyz
的二级索引记录都取出来，然后拿着记录主键回表，得到完整用户记录后再使用其他搜索条件进行过滤。
假设使用 idx_key2 执行查询
(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))
key2 < 8000 OR TRUE
TRUE这个结果意味着，如果使用 idx_key2 索引执行查询语句后，需要扫描 idx_key2 二级索引的所有记录， 然后再回表，得不偿失，所以这种情况下不会使用 idx_key2 索引的。
MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但也有一个查询中使用多个二级索引， MySQL把这种使用多个索引来完成查询的执行方法称为 index merge。具体有以下三种。
SELECT * FROm single_table
WHERE key1='a' AND key3='b';key1='a' 的记录key3='b' 的记录然后取交集，然后再回表
MySQL在某些特定的情况下才可能会使用到 intersection 索引合并
情况一： 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
# idx_key1 和 idx_key_part1 两个二级索引可以进行 intersection 索引合并
SELECT * FROM single_table
WHERE key1 = 'a' AND
key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';下面的这两个则不能
# 对key1进行了范围匹配
SELECT * FROM single_table WHERE key1 > 'a'
AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
# 联合索引 idx_key_part 中的 key_part2 列并没有出现在搜索条件中
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a'; 情况二： 主键列可以是范围匹配
下面的只会用 key1的索引B+树，因为id也参与了B+树节点大小比较，记录主键是有序的。
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a'; 记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由 索引列 + 主键 构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键 的值进行排序的。在 索引列相同时，主键是有序的。
主键也参与二级索引的大小比较，这样二级索引检索出来的主键会是直接有序的，两个结果求主键交集效率是非常高的，也就是 O(n) 的时间复杂度。
按照有序的主键值去回表取记录有个专有名词交 Rowid Ordered Retrieval 简称 ROR
SELECT * FROM
single_table
WHERE key1='a' OR key3='b';MySQL 在某些特定的情况下才可能会使用到 Union 索引合并：
情况一： 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
SELECT * FROM single_table
WHERE key1 = 'a' OR
( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');下面两个查询则就不能进行 Union 索引合并：
# 对 key1进行了范围匹配
SELECT * FROM single_table
WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');
# 联合索引 idx_key_part 中的 key_part2 列并没有出现在搜索条件中
SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';情况二： 主键可以是范围匹配
情况三： 使用Intersection索引合并的搜索条件
SELECT * FROM single_table
WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 ='c'
OR (key1 = 'a' AND key3 = 'b');这应该一眼就能看懂的。但查询条件符合了这些情况也不一定会采用 Union索引合并，得看优化器的心情，优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过 Union 索引合并后进行访问的代价比全表扫描更小时才会使用 Union 索引合并。
SELECT * FROM single_table
WHERE key1<'a' OR key3>'z';就不能从 idx_key1索引中获取二级索引记录的主键值不是排好序的，只有索引值相同的记录主键值才有序。key3同理。
这种方式称为 Sort-Union索引合并。很明显这样多了排序步骤。只有Sort-Union 没有 Sort-Intersection。
Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高。
SELECT * FROM single_table
WHERE key1='1' AND key3='b';这个查询之所以可能使用 Intersection 索引合并的方式执行，还不是因为 idx_key1 和 idx_key3 是两个单独 的 B+ 树索引，你要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合 并呢，就像这样：
ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);但对于有单独对key3列进行查询的业务场景，这样不得不再把key3列的单独索引给加上。
避不开的概念就是JOIN
按下面表来讲
mysql> CREATE TABLE t1 (m1 int, n1 char(1)); Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO
t1 VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO
t2 VALUES
(2, 'b'),
(3, 'c'),
(4, 'd');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0两个表内容是这样
mysql> SELECT * FROM t1;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+------+
| m2 | n2 |
+------+------+
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
3 rows in set (0.00 sec)连接的本质就是把各个连接表中的记录都取出来一次匹配的组合加入结果集并返回给用户。
结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，这样的结果可以称为
之笛卡尔积。
3x3=9行记录。
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 1 | a | 2 | b |
| 2 | b | 2 | b |
| 3 | c | 2 | b |
| 1 | a | 3 | c |
| 2 | b | 3 | c |
| 3 | c | 3 | c |
| 1 | a | 4 | d |
| 2 | b | 4 | d |
| 3 | c | 4 | d |
+------+------+------+------+
9 rows in set (0.00 sec)可以连接任意数量张表，但连接起来产生的笛卡尔积可能是非常巨大的。比如3个100行记录的表连接起来产生的笛卡尔积就有 100x100x100=1000000行。
在连接的时候过滤掉特定记录组合是有必要的，在连接查询中的过滤条件可以分为两种。
t1.m1 > 1t1.m1 = t2.m2、t1.n1 > t2.n2SELECT *
FROM t1, t2
WHERE
t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
+------+------+------+------+从上面步骤可以看出，两表连接查询共需要查询1次t1表、2次t2表。
按下面两张表讲
CREATE TABLE student (
number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
name VARCHAR(5) COMMENT '姓名',
major VARCHAR(30) COMMENT '专业',
PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';
CREATE TABLE score (
number INT COMMENT '学号',
subject VARCHAR(30) COMMENT '科目',
score TINYINT COMMENT '成绩',
PRIMARY KEY (number, score)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';两张表中有下面数据
mysql> SELECT * FROM student;
+----------+-----------+--------------------------+
| number | name | major |
+----------+-----------+--------------------------+
| 20180101 | 杜子腾 | 软件学院 |
| 20180102 | 范统 | 计算机科学与工程 |
| 20180103 | 史珍香 | 计算机科学与工程 |
+----------+-----------+--------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM score;
+----------+-----------------------------+-------+
| number | subject | score |
+----------+-----------------------------+-------+
| 20180101 | 母猪的产后护理 | 78 |
| 20180101 | 论萨达姆的战争准备 | 88 |
| 20180102 | 论萨达姆的战争准备 | 98 |
| 20180102 | 母猪的产后护理 | 100 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)通过两表中的number连接
SELECT *
FROM student,score
WHERE student.number = score.number;
SELECT s1.number, s2.number, s2.subject, s2.score
FROM student AS s1, score AS s2
WHERE s1.number = s2.number;上面这样查询，学号 number为 20180103的同学是没有结果的因为score表中没有其记录。
为了解决这个问题，就有了内连接和外连接。
外连接可以细分为2种：
不同地方的过滤条件有不同语义：
WHERE子句种的过滤条件，不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。
ON子句种的过滤条件，对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记 录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。
把 ON 子句放到内连接中， MySQL 会把它和 WHERE 子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。
一般把只涉及单表的过滤条件放到WHERE子句，涉及两表的过滤条件都放到ON子句。
SELECT *
FROM t1 LEFT [OUTER]
JOIN t2
ON 连接条件
[WHERE 普通过滤条件];放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。例子中 t1 就是外表或者驱动表， t2 就是内表或者被驱动表。
mysql>
SELECT s1.number, s1.name, s2.subject, s2.score
FROM student AS s1
LEFT JOIN score AS s2
ON s1.number = s2.number;
+----------+-----------+-----------------------------+-------+
| number | name | subject | score |
+----------+-----------+-----------------------------+-------+
| 20180101 | 杜子腾 | 母猪的产后护理 | 78 |
| 20180101 | 杜子腾 | 论萨达姆的战争准备 | 88 |
| 20180102 | 范统 | 论萨达姆的战争准备 | 98 |
| 20180102 | 范统 | 母猪的产后护理 | 100 |
| 20180103 | 史珍香 | NULL | NULL |
+----------+-----------+-----------------------------+-------+驱动表是右边的表，被驱动表是左边的表。
SELECT * FROM t1
RIGHT [OUTER] JOIN t2
ON 连接条件 [WHERE 普通过滤条件]; 内连接和外连接的根本区别就是在驱动表中的记录不符合 ON 子句中的连接条件时不会把该记录加入到最后的结果集。
有很多种写法。
SELECT *
FROM t1 [INNER|CROSS] JOIN t2
[ON 连接条件]
[WHERE 普通过滤条件];下面的几种内连接写法都是等价的。
SELECT * FROM t1 JOIN t2;
SELECT * FROM t2 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;推荐写 INNER JOIN 的写法，语法目的比较明确，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。
左外连接和右外连接的驱动表和被驱动表不能轻易互换，内连接即使表顺序交换后也是等价的。
上面回顾了SQL种的内连接、左外连接、右外连接。下面就是探索 MySQL 采用了什么样的算法来进行表与表之间的连接，知道原理知识， 才明白为什么有的连接查询快、有的却非常慢。
对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。
比如有3个表进行连接
for each row in t1 { #此处表示遍历满足对t1单表查询结果集中的每一条记录
for each row in t2 { #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录
for each row in t3 { #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询
if row satisfies join conditions, send to client
}
}
}这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为 嵌套循环连接 （ Nested-Loop Join ）。
比如还是这个例子
SELECT *
FROM t1, t2
WHERE
t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';在t1
能用索引就用索引，先进行单表查询出结果，再进行t2多次查询时，也可以使用t2的某些索引进行单表查询，因为在查
t2时 每次查询 t1.m1 都是已经确定的 相当于
常量 = t2.m2 AND t2.n2 < 'd'。
也就是驱动表查询的结果先缓存到join buffer一批，然后拿着一批去被驱动表查与过滤，减少访问被驱动表的次数。
尽量减少访问被驱动表的次数。
之前说 MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。
但一条查询语句的执行成本是由 I/O成本 读磁盘、CPU成本 读取以及检测记录是否满足对应的搜索条件、结果集排序等等。
为了用值衡量，MySQL规定读取一个页面花费的成本默认为1.0,读取以及检测一条记录是否符合搜索条件的成本默认是0.2。 1.0、0.2 称为成本常数。
仍旧使用下面这张表来讲
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;在一条单表查询语句真正执行之前，查询优化器会找出执行该语句所有可能使用的方案，找出成本低的
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';B+树索引，只要索引列和常数使用
= 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、 > 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符
连接起来，就可以产生一个所谓的 范围区间 LIKE
匹配字符串前缀也行），这些搜索条件都可能使用到索引。
把一个查询中，可能使用到的索引称为 possible keys。
key1 IN ('a', 'b', 'c') 可以使用二级索引 idx_key1key2 > 10 AND key2 < 1000 可以使用二级索引
idx_key2key3>key2
这个搜索条件的索引列由于没和常数比较不能直接使用索引，使用也是先把key2过滤掉之后再去key3索引里。key_part1 LIKE '%hello%' 不适用，不是与前缀匹配common_field = '123' 该列压根没有索引上面的 possible keys 只有 idx_key1 和 idx_key2。
全表扫描的成本代码需要两个信息：
# 查看表的统计信息
SHOW TABLE STATUS LIKE 'single_table'\G
Name: single_table
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 9693
Avg_row_length: 163
Data_length: 1589248
Max_data_length: 0
Index_length: 2752512
Data_free: 4194304
Auto_increment: 10001
Create_time: 2018-12-10 13:37:23
Update_time: 2018-12-10 13:38:03
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:其中的Rows就是表中的记录条数，对于MyISAM的表该值是准确的，对于InnoDB表该值是估计值。
Data_length = 聚簇索引的页面数量 x 每个页面的大小
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
I/O 成本
97 x 1.0 + 1.1 = 98.1
97 指的是聚簇索引占用的页面数， 1.0 指的是加载一个页面的成本常数，后边的 1.1 是一个微调值
CPU成本
9693 x 0.2 + 1.0 = 1939.6
9693 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估计值， 0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值
总成本
98.1 + 1939.6 = 2037.7表中的记录都存储在聚簇索引对应B+树的叶子节点，通过根节点获得最左边的叶子节点，沿着叶子节点组成的双向链表把所有记录都查一遍，全表扫描过程有的B+树内节点是不需要访问的。
不论某个范围区间的二级索引到底占用了多少页，查询优化器粗暴认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
关于具体成本数值的计算在此不进行了，属实没必要，我们只要多理解被后查询原理就好了。
key2 > 10 AND key2 < 1000
内节点中的页访问也是需要成本的
通过 key2 的索引，找出范围内最左叶子节点，范围内最右叶子节点，然后左右之间的就是符合要求的，统计一下页数记录数之类的信息。然后拿到逐渐后，进行回表，每一次回表成本按一次页面I/O。回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立。
使用 idx_key1 执行查询 key1 IN ('a', 'b', 'c')
相当于3个单节点区间 ['a', 'a'] ['b', 'b'] ['c', 'c']
然后根据这些记录里的主键值到聚簇索引中做回表操作。
是否有可能使用 索引合并（Index Merge）
key1和key2搜索条件使用 AND,对于 idx_key1 和 idx_key2 都是范围查询，查到的二级索引记录并不是按照主键值进行排序的，并不满足使用 Intersection 索引合并的条件
对比全表扫描的成本,使用 idx_key2 的成本,使用 idx_key1的成本
找出成本较低的那个方案,源码真正的工作原理不一定是这样,上面的内容只是帮助我们理解背后的机制罢了不必过于认真
SELECT * FROM single_table
WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz'); 查询可能使用到的索引就是 idx_key1 ，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少。
通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称为 index dive。
如果 IN 里面有20000个参数，如果为了计算这些单点区间对应的索引记录条数，要进行20000次 index dive 操作，性能损耗就大了，搞不好比全表扫描成本都高。
有一个系统变量
mysql> SHOW VARIABLES LIKE '%dive%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200 |会为每个表维护一份统计数据一样， MySQL 也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名 的语法。
mysql> SHOW INDEX FROM single_table;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Card inality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| single_table | 0 | PRIMARY | 1 | id | A | 9693 | NULL | NULL | | BTREE | | |
| single_table | 0 | idx_key2 | 1 | key2 | A | 9693 | NULL | NULL | YES | BTREE | | |
| single_table | 1 | idx_key1 | 1 | key1 | A | 968 | NULL | NULL | YES | BTREE | | |
| single_table | 1 | idx_key3 | 1 | key3 | A | 799 | NULL | NULL | YES | BTREE | | |
| single_table | 1 | idx_key_part | 1 | key_part1 | A | 9673 | NULL | NULL | YES | BTREE | | |
| single_table | 1 | idx_key_part | 2 | key_part2 | A | 9999 | NULL | NULL | YES | BTREE | | |
| single_table | 1 | idx_key_part | 3 | key_part3 | A | 10000 | NULL | NULL | YES | BTREE | | |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)single_table表中有10000条数据，某个索引列的 Cardinality 属性是 10000 ，那意味着该列中没有重复的值，如果 Cardinality 属性是 1 的话，就意味着该列的值全部是重复的。
平均一个值重复多少次，
一个值的重复次数 ≈ Rows ÷ Cardinality例如key1索引
9693 ÷ 968 ≈ 10（条）假设 IN 语句中有20000个参数的话,回表次数估算
20000 x 10 = 200000当查询中使用到了IN查询，但是却实际没有用到索引，应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。
连接查询至少要有两个表。
直接构造一个和single_table表一模一样的single_table2表，暂且把 single_table 表称为s1表，把single_table2表称为s2表。
MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次。
两表连接查询，它的查询成本由两部分构成：
把驱动表进行查询后得到的记录条数称之为驱动表的 扇出(fanout), 很明显删除之越小，对被驱动表的查询次数越少，连接查询的成本越低。
SELECT * FROM single_table AS s1
INNER JOIN single_table AS s2;上面的查询进行了，全表x全表
SELECT * FROM single_table AS s1
INNER JOIN single_table2 AS s2
WHERE s1.key2 > 10 AND s2.key2 < 1000;上面的查询，删除值是前面 满足 key2大于10的部分 x 后者 key2小于1000的。
SELECT * FROM single_table AS s1
INNER JOIN single_table2 AS s2
WHERE s1.common_field > 'xyz';优化器只会猜 single_table 有多少条记录满足 common_field 大于 xyz条件。
SELECT * FROM single_table AS s1
INNER JOIN single_table2 AS s2
WHERE s1.key2 > 10 AND s1.key2 < 1000
AND s1.common_field > 'xyz';查询可以使用 idx_key2
索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合
common_field > 'xyz' 条件
SELECT * FROM single_table AS s1 INNER JOIN
single_table2 AS s2
WHERE s1.key2 > 10 AND s1.key2 < 1000
AND s1.key1 IN ('a', 'b', 'c') AND s1.common_field > 'xyz';在驱动表 s1 选取 idx_key2
索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：
key1 IN ('a', 'b', 'c') 和
common_field > 'xyz'
MySQL把这个猜的过程称为 condition filtering。
连接查询的成本计算公式
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本对于左（外）连接和 右（外）连接查询，驱动表是固定的，想要得到最优的查询方案只需要：分别为驱动表和被驱动表选择成本最低的访问方法。
对于内连接，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：
SELECT * FROM single_table AS s1 INNER JOIN
single_table2 AS s2
ON s1.key1 = s2.common_field
WHERE s1.key2 > 10 AND s1.key2 < 1000
AND s2.key2 > 1000 AND s2.key2 < 2000;可以选择的连接顺序有两种
s1表单表搜索条件,如果扇出数量少于s2的，肯定把s1放到前面，s1的删除连接s2时，
每次查s2 都是 常数 = s2.common_field 然后 判断条件
s2.key2 > 1000 AND s2.key2 < 2000。
所以使用 single_table 作为驱动表的成本
使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本使用single_table2作为驱动表的成本
使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本对于两表连接，比如表A和表B连接,AB、BA两种顺序
2x1,对于3个表 3x2x1，很明显是n的阶乘。
MySQL不会把n的阶乘种方法成本都计算的。
MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。
系统变量 optimizer_search_depth，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析。很明显越大越好，但会花费更多的分析时间。
启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，提供了一个系统变量 optimizer_prune_level 来控制到底是不是用这些启发式规则。
之前假设了读一个页面花费成本默认是1，检测一条记录是否符合搜索条件的成本默认是 0.2，其实除了这两个成本常数， MySQL 还支持好多呢，它们被存储到了 mysql 数据库。
mysql> SHOW TABLES FROM mysql LIKE '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost |
| server_cost |
+--------------------------+
2 rows in set (0.00 sec)这两张表提供的信息有助于分析查询执行过程中的瓶颈和优化方向。
mysql.server_cost 表用于存储与查询执行过程中服务器级别的成本估算信息。这些信息对于查询优化器非常重要，能够帮助 MySQL 选择最优的执行计划。该表包含了优化器在执行计划生成过程中估算的服务器成本数据。
mysql> select * from server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name | cost_value | last_update | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost | NULL | 2025-12-31 03:43:36 | NULL | 20 |
| disk_temptable_row_cost | NULL | 2025-12-31 03:43:36 | NULL | 0.5 |
| key_compare_cost | NULL | 2025-12-31 03:43:36 | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 2025-12-31 03:43:36 | NULL | 1 |
| memory_temptable_row_cost | NULL | 2025-12-31 03:43:36 | NULL | 0.1 |
| row_evaluate_cost | NULL | 2025-12-31 03:43:36 | NULL | 0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.002 sec)表字段解析：
主要配置项说明：
mysql.engine_cost 表主要存储与查询执行过程中的存储引擎相关的成本估算信息。这些信息包括存储引擎的 I/O 开销、内存使用情况、锁的竞争等因素。
mysql> select * from engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name | cost_value | last_update | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default | 0 | io_block_read_cost | NULL | 2025-12-31 03:43:36 | NULL | 1 |
| default | 0 | memory_block_read_cost | NULL | 2025-12-31 03:43:36 | NULL | 0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.002 sec)表字段解析：
主要配置项说明：
前面讲查询成本时经常用到一些统计数据，比如通过
SHOW TABLE STATUS
可以看到关于表的统计数据，这些统计数据是怎么来的呢？
先看下下面主要讲的什么，然后再具体看
永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。
CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);STATS_PERSISTENT为1，统计数据永久存到磁盘，为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)innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。
下面看这个 innodb_table_stats 表中各列都是干什么的
mysql> desc innodb_table_stats;
+--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(199) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| n_rows | bigint unsigned | NO | | NULL | |
| clustered_index_size | bigint unsigned | NO | | NULL | |
| sum_of_other_index_sizes | bigint unsigned | NO | | NULL | |
+--------------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
6 rows in set (0.017 sec)mysql> select * from innodb_table_stats;
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| dbname | dbuserrecord | 2026-01-23 02:51:44 | 4 | 1 | 1 |
| mysql | component | 2025-12-31 03:43:36 | 0 | 1 | 0 |
| sys | sys_config | 2025-12-31 03:43:37 | 6 | 1 | 0 |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
3 rows in set (0.004 sec)InnoDB 统计一个表中有多少行记录的套路是这样的：
按照一定算法（并不是纯粹随机的），选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键的记录数量， 然后乘以全部叶子节点的数量就算是该表的 n_rows 值。
n_rows值精确与否取决于统计时采样的页面数量，MySQL 设计了一个名为 innodb_stats_persistent_sample_pages 的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的 n_rows 值越精确，但是统计耗时也就最久；该值设置的越小，统计出的 n_rows 值越不精确，但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是 20 。
也可以为某个单独的表单独设置
CREATE TABLE 表名 (...)
Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量; 这两个统计项的收集过程：
从叶子节点和非叶子节点段的 Segment Header 中找到这两个段对应的 INODE Entry结构。
从对应的 INODE Entry 结构中可以找到该段对应所有零散的页面地址以及 FREE、NOT_FULL、FULL 链表的基节点。
直接统计零散的页面有多少个，然后从那三个链表的List Length字段中读出该段占用的区的大小， 每个区占用64个页面，就可以统计出整个段占用的页面。
分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数，它们的和就是 clustered_index_size 的值，按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是 sum_of_other_index_sizes 的值。
一个段的数据在非常多时（超过32个页面），就会以区为单位申请空间，以区为单位申请空间中有一些页可能并没有使用，但是在统计 clustered_index_size 和 sum_of_other_index_sizes 时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。
mysql> desc innodb_index_stats;
+------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(199) | NO | PRI | NULL | |
| index_name | varchar(64) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| stat_name | varchar(64) | NO | PRI | NULL | |
| stat_value | bigint unsigned | NO | | NULL | |
| sample_size | bigint unsigned | YES | | NULL | |
| stat_description | varchar(1024) | NO | | NULL | |
+------------------+-----------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.010 sec)mysql> select * from innodb_index_stats;
+---------------+--------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+--------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| dbname | dbuserrecord | PRIMARY | 2026-01-23 02:51:44 | n_diff_pfx01 | 4 | 1 | id |
| dbname | dbuserrecord | PRIMARY | 2026-01-23 02:51:44 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| dbname | dbuserrecord | PRIMARY | 2026-01-23 02:51:44 | size | 1 | NULL | Number of pages in the index |
| dbname | dbuserrecord | uk_userId | 2026-01-23 02:51:44 | n_diff_pfx01 | 4 | 1 | userId |
| dbname | dbuserrecord | uk_userId | 2026-01-23 02:51:44 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| dbname | dbuserrecord | uk_userId | 2026-01-23 02:51:44 | size | 1 | NULL | Number of pages in the index |
| mysql | component | PRIMARY | 2025-12-31 03:43:36 | n_diff_pfx01 | 0 | 1 | component_id |
| mysql | component | PRIMARY | 2025-12-31 03:43:36 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| mysql | component | PRIMARY | 2025-12-31 03:43:36 | size | 1 | NULL | Number of pages in the index |
| sys | sys_config | PRIMARY | 2025-12-31 03:43:37 | n_diff_pfx01 | 6 | 1 | variable |
| sys | sys_config | PRIMARY | 2025-12-31 03:43:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| sys | sys_config | PRIMARY | 2025-12-31 03:43:37 | size | 1 | NULL | Number of pages in the index |
+---------------+--------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
12 rows in set (0.006 sec)一些统计项：
随着不断地对表进行增删改，表中的数据也一直在变化，MySQL提供了两种更新统计数据的方式：
CREATE TABLE 表名 (...)
Engine=InnoDB,
STATS_AUTO_RECALC = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0); mysql> ANALYZE TABLE single_table;
+------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| xiaohaizi.single_table | analyze | status | OK |
+------------------------+---------+----------+----------+
1 row in set (0.08 sec)ANALYZE TABLE 语句会立即重新计算统计数据，过程是同步的，在表中索引多或采样页面特别多时这个过程会特别慢，不要没事就运行一下 ANALYZE TABLE语句。 要运行也要在，业务不繁忙的时候再运行。
innodb_table_stats 和 innodb_index_stats 表相当于一个普通的表一样，能对它们做增删改查操作。
如手动更新某个表或索引的统计数据
UPDATE innodb_table_stats
SET n_rows = 1
WHERE table_name = 'single_table';FLUSH TABLE single_table;之后再用 SHOW TABLE STATUS 语句查看表的统计数据就会看到Rows行变为了1
与永久性的统计数据不同，非永久性的统计数据采样的页面数量是由 innodb_stats_transient_sample_pages 控制的，这个系统变量的默认值是 8 。
索引列不重复的值的数量，这个统计数据对于MySQL查询优化器十分重要，因为通过它可以计算出在索引列中平均中平均一个值重复了多少行，应用场景主要有两个：
单表查询中单点区间太多如
SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');当IN里的参数量太多时，采用 index dive的方式直接访问 B+ 树索引去统计每个单点区间对应的记录的数量就太耗费性能了，所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。
连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用 ref 访问方法来对被驱动表进行查询，比方说这样：
SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...; 在真正执行对t2表的查询前，t1.comumn的值是不确定的，所以我们也不能通过 index dive 的方式直接访问 B+ 树索引去统计每个单点区间对应的记录的数量，所以也只能依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。
在统计索引列不重复的值的数量时，索引列中出现NULL值怎么办,比如某个索引列的内容是这样
+------+
| col |
+------+
| 1 |
| 2 |
| NULL |
| NULL |
+------+MySQL认为任何和NULL值比较的表达式的值都为NULL
mysql> SELECT 1 = NULL;
+----------+
| 1 = NULL |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)
mysql> SELECT 1 != NULL;
+-----------+
| 1 != NULL |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT NULL != NULL;
+--------------+
| NULL != NULL |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)每一个NULL值都是独一无二的，在统计索引列不重复的值的数量时，上面col列的不重复的值的数量就是
4,(分别是 1、2、NULL、NULL这四个值)
到底是
MySQL提供了一个名为 innodb_stats_method的系统变量，在计算某个索引列不重复值的数量时如何对待 NULL 值
MySQL依据一些规则，把糟糕的语句转换成某种可以比较高效执行的形式，这个过程可以被称作 查询重写。
编写的查询语句的搜索条件本质上是一个表达式，表达式可能比较复杂或不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。
表达式里有许多无用的括号，比如
((a = 5 AND b = c) OR ((a > c) AND (c < 5)))优化器会把那些用不到的括号给干掉
(a = 5 and b = c) OR (a > c AND c < 5)有时候某个表达式是某个列和某个常量做等值比较
a = 5当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中的a的值替换为5
a = 5 AND b > a就可以被转换为
a = 5 AND b > 5有时候多个列之间存在等值匹配的关系，比如
a = b and b = c and c = 5简化为
a = 5 and b = 5 and c = 5一些明显永远为 TRUE 或者 FALSE 的表达式，优化器会移除掉它们，比如
(a < 1 and b = b) OR (a = 6 OR 5 != 5)优化后
(a < 1 and TRUE) OR (a = 6 OR FALSE)继续简化
a < 1 OR a = 6在查询开始执行前，如果表达式中包含常量，它的值会被先计算出来
a = 5 + 1简化为
a = 6如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中,出现在某个更复杂表达式中,优化器不会尝试对这些表达式进行简化
ABS(a) > 5或者
-a < -8写SQL如果可以的话，最好让索引列以单独的形式出现在表达式中。
如果查询语句中没有出现诸如 SUM、MAX 等等的聚集函数以及 GROUP BY 子句，优化器就把 HAVING 子句和 WHERE 子句合并起来。
SQL的逻辑执行顺序是
FROM
→ WHERE
→ GROUP BY
→ HAVING
→ SELECT
→ ORDER BY简单例子
SELECT *
FROM user
HAVING age > 18;
# 等价于
SELECT *
FROM user
WHERE age > 18;优化器为什么那么干
下面的这种就不行
SELECT *
FROM user
HAVING COUNT(*) > 1;
# 或者
SELECT dept, COUNT(*)
FROM user
HAVING COUNT(*) > 10;一旦出现：
HAVING 就回到它真正的岗位，过滤分组结果，这时候它不能，也不允许合并到 WHERE。
HAVING 本来是给 分组后 用的。你在没分组的时候硬塞一个，优化器只能叹气然后帮你擦屁股。
有两种查询特别快
SELECT * FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2
WHERE table1.primary_key = 1;可以使用主键和常量值的等值匹配来查询 table1 表，table1在这就是常量表，因为主键对应记录是唯一的，要么没有，要么只有一条。
在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉
SELECT table1表记录的各个字段的常量值, table2.*
FROM table1
INNER JOIN table2
ON table1表column1列的常量值 = table2.column2;内连接 的驱动表和被驱动表的位置可以相互转换，而 左（外）连接 和 右（外）连接 的驱动表和被驱动表是固定的。 这就导致 内连接 可能通过优化表的连接顺序来降低整体的查询成本，而 外连接 却无法优化表的连接顺序。
下面用这两个表
CREATE TABLE t1 (
m1 int,
n1 char(1)
) Engine=InnoDB, CHARSET=utf8;
CREATE TABLE t2 (
m2 int,
n2 char(1)
) Engine=InnoDB, CHARSET=utf8;
mysql> SELECT * FROM t1;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
mysql> SELECT * FROM t2;
+------+------+
| m2 | n2 |
+------+------+
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+内连接
mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
+------+------+------+------+左连接
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
| 1 | a | NULL | NULL |
+------+------+------+------+如果在WHERE中加 t2的非NULL条件呢
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
+------+------+------+------+上边的t1和t2表的左（外）连接查询和内连接查询是一样的
把这种在外连接查询中，执行的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为 空值拒绝。
在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转化。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。
先回顾子查询基本内容。
在一个查询语句里的某个位置也可以有另一个查询条件，这个出现在某个查询语句的某个位置中的查询就被称为子查询。
mysql> SELECT (SELECT m1 FROM t1 LIMIT 1);
+-----------------------------+
| (SELECT m1 FROM t1 LIMIT 1) |
+-----------------------------+
| 1 |
+-----------------------------+其中 SELECT m1 FROM t1 LIMIT 1 就是子查询语句
SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;
+------+------+
| m | n |
+------+------+
| 4 | c |
| 5 | d |
+------+------+这里把子查询的结果当作是一个表，子查询后边的AS t表明这个子查询的结果相当于一个名称为t的表， 这种表 MySQL把这种由查询结果集组成的表称之为 派生表。
mysql> SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
+------+------+
| m1 | n1 |
+------+------+
| 2 | b |
| 3 | c |
+------+------+虽然语法支持，但没啥意义
子查询本身也是一个查询，按照它们返回的不同结果集类型而把这些子查询分为不同的类型
那些只返回一个单一值得子查询称为 标量子查询
SELECT (SELECT m1 FROM t1 LIMIT 1);
SELECT * FROM t1
WHERE m1 = (SELECT MIN(m2) FROM t2);返回一条记录的子查询,记录需要包含多个列
SELECT * FROM t1
WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);查询出来一个列的数据，列的数据需要包含多条记录，不然就成了标量子查询
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);子查询的结果既包含很多条记录，又包含很多个列
SELECT * FROM t1
WHERE (m1, n1)
IN (SELECT m2, n2 FROM t2);SELECT * FROM t1
WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);其中子查询有条件 n1 = n2，n1是表t1的列。
子查询在布尔表达式中的使用场景，看看就好了
SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);
SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);
SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);
SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);
SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);mysql> SELECT SELECT m1 FROM t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corr esponds
to your MySQL server version for the right syntax to use near 'SELECT m1 FROM t1' at line 1mysql> SELECT (SELECT m1, n1 FROM t1);
ERROR 1241 (21000): Operand should contain 1 column(s) [NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有
LIMIT 语句mysql> SELECT * FROM t1 WHERE m1 IN (SELECT * FROM t2 LIMIT 2);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SO
ME subquery'SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 ORDER BY m2);SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2);
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2);mysql> DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause 下面用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;假设有两个表s1、s2与这个表结构相同。
年少无知可能是觉得子查询执行方式是这样的
如果子查询是不相关子查询
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s1);可能任务，先单独执行 (SELECT common_field FROM s1)
这个子查询，然后将上一步子查询结果当作外层查询的参数
SELECT * FROM s1 WHERE key1
IN (...) 如果该子查询是相关子查询
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2); 先从s1表获取一条记录，确当s1.key2，然后执行子查询，最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。再执行第一步，依此类推。
但MySQL中是有一系列办法来优化子查询的执行的。大部分情况下这些优化措施其实挺有效的，但是保不齐有的时候马失前蹄，
包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。
SELECT * FROM s1
WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1); 对于相关的标量子查询或者行子查询来说，比如下边这个查询：
SELECT * FROM s1 WHERE
key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1); 这个也和上面预想的那样，先从外层查询中获取一条数据，那然确定s1.key3然后得到子查询结果，与外查询获取的那条记录判断条件，符合就加入结果集，否则舍弃。
对于IN子查询如
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');IN子查询没想得那么简单
MySQL整了一些招数，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：该临时表的列就是子查询结果集中的列。写入临时表的记录会被去重。
一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。
MySQL 把这个将子查询结果集中的记录保存到临时表的过程称之为 物化 。
然后就可以让表和另一个临时表进行连接了。
不过上面的SQL可以直接转换为连接
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
SELECT s1.* FROM s1 INNER JOIN s2
ON s1.key1 = s2.common_field
WHERE s2.key3 = 'a';只不过我们不能保证对于 s1 表的某条记录来说，在 s2
表（准确的说是执行完 WHERE s2.key3 = 'a'
之后的结果集）中有多少条记录满足 s1.key1 = s2.common_field
这个条件，不过我们可以分三种情况讨论：
s1.key1 = s2.common_field
这个条件，那么该记录自然也不会加入到最后的结果集。s1.key1 = s2.common_field
这个条件，那么该记录会被加入最终的结果集。这里有个新概念 SEMI JOIN将 s1 表和 s2 表进行半连接的意思就是：对于 s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 表的记录。
SELECT s1.* FROM s1 SEMI JOIN s2
ON s1.key1 = s2.common_field
WHERE key3 = 'a';semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法。
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表 上拉 到外层查询的 FROM 子句 中，并把子查询中的搜索条件合并到外层查询的搜索条件中
SELECT * FROM s1
WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');
# key2有唯一索引
SELECT s1.* FROM s1 INNER JOIN s2
ON s1.key2 = s2.key2
WHERE s2.key3 = 'a';对于
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');转换为半连接查询后， s1 表中的某条记录可能在 s2 表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，比方说这个临时表长这样：
CREATE TABLE tmp (
id PRIMARY KEY
);SELECT * FROM s1
WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b'); s2 表的访问可以使用到 key1 列的索引，而恰好子查询的查询列表处就是 key1 列,在将该查询转换为半连接查询后，如果将 s2 作为驱动表执行查询的话，那么执行过程就是这样：
这就是“松散”，不是按物理连续扫，而是按逻辑唯一值跳跃。只取值相同的记录的第一条去做匹配操作的方式称之为 松散索引扫描 。
先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则 将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢 弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。
不是所有IN子查询都 适用 semi-join,具体的不要深究了，说实话意义真不大，能把SQL写出来就不错了，跑得性能不行再想解决办法。
听过有半连接这东西就可以了，想学深入可以去问ChatGPT。
如果ANY/ALL子查询是不相关子查询的话，它们在很多场合都能转换成我们熟悉的方式去执行
< ANY (SELECT inner_expr ...) < (SELECT MAX(inner_expr) ...)
> ANY (SELECT inner_expr ...) > (SELECT MIN(inner_expr) ...)
< ALL (SELECT inner_expr ...) < (SELECT MIN(inner_expr) ...)
> ALL (SELECT inner_expr ...) > (SELECT MAX(inner_expr) ...)说实话真没必要在这卡折腾钻牛角尖，这都是DBMS开发人员做的，更何况每个数据库系统软件的背后优化方式又是不同， 大多数情况下直接用就好了。知道背后有各种优化就是了，写过SQL大不了问问ChatGPT你的SQL背后运行时会是怎样的。
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化后生成一个所谓的 执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
MySQL提供了EXPLAIN语句帮助用户查看某个查询语句的具体执行计划。MySQL8的默认FORMAT是TREE
mysql> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM dbuserrecord WHERE userId='2';
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | dbuserrecord | NULL | const | uk_userId | uk_userId | 258 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.007 sec)
mysql> EXPLAIN SELECT * FROM dbuserrecord WHERE userId='2';
+-------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------+
| -> Rows fetched before execution (cost=0..0 rows=1)
|
+-------------------------------------------------------+
1 row in set (0.001 sec)
mysql> EXPLAIN SELECT * FROM dbuserrecord WHERE userId LIKE '%';
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Filter: (dbuserrecord.userId like '%') (cost=0.65 rows=1)
-> Table scan on dbuserrecord (cost=0.65 rows=4)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
mysql> EXPLAIN FORMAT=JSON SELECT * FROM dbuserrecord WHERE userId='2';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query": "/* select#1 */ select '1767164171823' AS `id`,'2' AS `userId`,'2' AS `password`,'\b\u0002\u0012\b\n\u0006\b\u0001\u0010??\u0010' AS `baseInfo` from `dbname`.`dbuserrecord` where true",
"query_plan": {
"operation": "Rows fetched before execution",
"access_type": "rows_fetched_before_execution",
"estimated_rows": 1.0,
"estimated_total_cost": 0.0,
"estimated_first_row_cost": 0.0
},
"query_type": "select",
"json_schema_version": "2.0"
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.005 sec)
mysql> EXPLAIN FORMAT=TREE SELECT * FROM dbuserrecord WHERE userId='2';
+-------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------+
| -> Rows fetched before execution (cost=0..0 rows=1)
|
+-------------------------------------------------------+
1 row in set (0.002 sec)除了以SELECT开头的查询语句，其余的 DELETE、INSERT、REPLACE以及UPDATE语句前边都可以加上EXPLAIN 看语句的执行计划。
| 列名 | 描述 |
|---|---|
| id | 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id |
| select_type | SELECT 关键字对应的那个查询的类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际使用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外的信息 |
下面还是用两个表s1、s2 表结构如下
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;无论查询语句再复杂，包含了多少表，最后也需要对每个表进行单表访问的。
MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。
mysql> EXPLAIN SELECT * FROM s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | r ows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9
688 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)上面查询语句只涉及表s1表的单表查询，EXPLAIN输出只有一条记录
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)这个连接查询的执行计划中有两条记录，分别为 table分别为s1和s2。
有的语句只有一个SELECT有的有多个SELECT，为每个SELECT 分配了一个EXPLAIN的id
SELECT * FROM s1 WHERE key1 = 'a';
###
SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';
###
SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2);
###
SELECT * FROM s1 UNION SELECT * FROM s2;EXPLAIN结果分别为
mysql> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.001 sec)
mysql> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM s1 INNER JOIN s2
-> ON s1.key1 = s2.key1
-> WHERE s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | test_db.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.007 sec)
mysql> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-----------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | test_db.s1.key1 | 1 | 100.00 | Using index; FirstMatch(s1) |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-----------------------------+
2 rows in set, 1 warning (0.005 sec)
mysql> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM s1 UNION SELECT * FROM s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 3 | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.003 sec)在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。
UNION子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，MySQL使用的是内部的临时表。
SIMPLE（简单查询）
最常见的查询类型，不包含子查询或 UNION
EXPLAIN SELECT * FROM s1 WHERE id = 1;
+----+-------------+-------+------+
| id | select_type | table | ... |
+----+-------------+-------+------+
| 1 | SIMPLE | s1 | ... |
+----+-------------+-------+------+PRIMARY（主查询）
复杂查询中最外层的查询
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2);
+----+-------------+-------+------+
| id | select_type | table | ... |
+----+-------------+-------+------+
| 1 | PRIMARY | s1 | ... | ← 外层查询
| 2 | SUBQUERY | s2 | ... | ← 子查询
+----+-------------+-------+------+DEPENDENT SUBQUERY（相关子查询）
⚠️ 性能警告：每处理外层一行，子查询就执行一次
EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2 WHERE s2.key2 = s1.key2);
-- ↑ 依赖外层 s1.key2
+----+--------------------+-------+------+
| id | select_type | table | ... |
+----+--------------------+-------+------+
| 1 | PRIMARY | s1 | ... |
| 2 | DEPENDENT SUBQUERY | s2 | ... | ← 性能较差！
+----+--------------------+-------+------+DERIVED（派生表）
FROM 子句中的子查询（临时表）
EXPLAIN SELECT * FROM (
SELECT * FROM s1 WHERE key1 = 'a'
) AS derived_s1;
+----+-------------+------------+------+
| id | select_type | table | ... |
+----+-------------+------------+------+
| 1 | PRIMARY | <derived2> | ... | ← 使用派生表
| 2 | DERIVED | s1 | ... | ← 派生表来源
+----+-------------+------------+------+UNION（联合查询）
UNION 中的第二个及后续 SELECT
EXPLAIN SELECT * FROM s1
UNION
SELECT * FROM s2;
+----+--------------+------------+------+
| id | select_type | table | ... |
+----+--------------+------------+------+
| 1 | PRIMARY | s1 | ... | ← 第一个查询
| 2 | UNION | s2 | ... | ← UNION 的第二个查询
| NULL | UNION RESULT | <union1,2> | ... | ← 合并结果
+----+--------------+------------+------+UNION RESULT（联合结果）
UNION 的临时结果表
-- 见上面 UNION 示例DEPENDENT UNION（相关联合查询）
UNION 中依赖外层查询的部分
EXPLAIN SELECT * FROM s1 WHERE key1 IN (
SELECT key1 FROM s2
UNION
SELECT key1 FROM s3 WHERE s3.key2 = s1.key2
-- ↑ 依赖外层
);MATERIALIZED（物化子查询）
子查询被物化（转为临时表）
✅ 优化技术：子查询只执行一次，结果存入临时表
EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2);
-- MySQL 优化器可能将子查询物化
+----+--------------+-------------+------+
| id | select_type | table | ... |
+----+--------------+-------------+------+
| 1 | SIMPLE | s1 | ... |
| 1 | SIMPLE | <subquery2> | ... | ← 使用物化表
| 2 | MATERIALIZED | s2 | ... | ← 物化的子查询
+----+--------------+-------------+------+UNCACHEABLE SUBQUERY（不可缓存子查询）
结果无法缓存的子查询（如包含随机函数）
EXPLAIN SELECT * FROM s1
WHERE key1 = (SELECT RAND() * 100 FROM s2 LIMIT 1);
-- ↑ 随机函数，结果不可缓存UNCACHEABLE UNION
UNION 中包含不可缓存的查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (
SELECT key1 FROM s2
UNION
SELECT RAND() * 100
);性能优先级（从好到差）
✅ SIMPLE # 最优
✅ PRIMARY + MATERIALIZED # 较好（子查询物化）
⚠️ PRIMARY + SUBQUERY # 一般
⚠️ DERIVED # 需要临时表
❌ DEPENDENT SUBQUERY # 较差（N×M 复杂度）
❌ UNCACHEABLE SUBQUERY # 最差实例对比
-- ❌ 差：DEPENDENT SUBQUERY
SELECT * FROM s1
WHERE EXISTS (SELECT 1 FROM s2 WHERE s2.id = s1.id);
-- ✅ 好：改用 JOIN
SELECT DISTINCT s1.*
FROM s1 INNER JOIN s2 ON s1.id = s2.id;
-- ✅ 好：或者让优化器物化
SELECT * FROM s1
WHERE id IN (SELECT id FROM s2); -- 可能变成 MATERIALIZED分区是将一个大表的数据物理上分割成多个小片段存储，但逻辑上仍然是一张表。
partitions 列显示查询将访问哪些分区，只在表使用了分区时才有意义。
基础概念
未分区表
EXPLAIN SELECT * FROM s1 WHERE id = 100;
+----+-------------+-------+------------+------+---------------+
| id | select_type | table | partitions | type | ... |
+----+-------------+-------+------------+------+---------------+
| 1 | SIMPLE | s1 | NULL | ref | ... |
+----+-------------+-------+------------+------+---------------+
↑ NULL 表示未分区创建分区表示例
-- 按范围分区（按年份）
CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p_future VALUES LESS THAN MAXVALUE
);分区裁剪生效（最优情况）
✅ 性能提升：只扫描 1 个分区，而非全部 5 个分区
EXPLAIN SELECT * FROM orders
WHERE order_date = '2022-06-15';
+----+-------------+--------+------------+------+
| id | select_type | table | partitions | type |
+----+-------------+--------+------------+------+
| 1 | SIMPLE | orders | p2022 | ALL |
+----+-------------+--------+------------+------+
↑ 只扫描 p2022 分区（分区裁剪成功）分区裁剪部分生效
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2021-01-01' AND '2022-12-31';
+----+-------------+--------+---------------+------+
| id | select_type | table | partitions | type |
+----+-------------+--------+---------------+------+
| 1 | SIMPLE | orders | p2021,p2022 | ALL |
+----+-------------+--------+---------------+------+
↑ 扫描 2 个分区分区裁剪失效（全表扫描）
⚠️ 性能问题：扫描全部 5 个分区
-- ❌ 没有使用分区键
EXPLAIN SELECT * FROM orders WHERE amount > 1000;
+----+-------------+--------+----------------------------------+------+
| id | select_type | table | partitions | type |
+----+-------------+--------+----------------------------------+------+
| 1 | SIMPLE | orders | p2020,p2021,p2022,p2023,p_future | ALL |
+----+-------------+--------+----------------------------------+------+
↑ 扫描所有分区（分区裁剪失败）不同分区类型示例
RANGE 分区（范围）
CREATE TABLE sales (
id INT,
sale_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (TO_DAYS(sale_date)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))
);
EXPLAIN SELECT * FROM sales WHERE sale_date = '2023-02-15';
-- partitions: p202302LIST 分区（列表）
CREATE TABLE employees (
id INT,
name VARCHAR(50),
region VARCHAR(20)
)
PARTITION BY LIST COLUMNS(region) (
PARTITION p_north VALUES IN ('北京', '天津', '河北'),
PARTITION p_south VALUES IN ('广东', '广西', '海南'),
PARTITION p_east VALUES IN ('上海', '江苏', '浙江')
);
EXPLAIN SELECT * FROM employees WHERE region = '上海';
-- partitions: p_eastHASH 分区（哈希）
CREATE TABLE users (
id INT,
username VARCHAR(50)
)
PARTITION BY HASH(id)
PARTITIONS 4;
EXPLAIN SELECT * FROM users WHERE id = 100;
-- partitions: p2 (根据 id % 4 计算)KEY 分区
CREATE TABLE logs (
id INT,
log_time DATETIME,
message TEXT
)
PARTITION BY KEY(id)
PARTITIONS 8;
EXPLAIN SELECT * FROM logs WHERE id = 12345;
-- partitions: p5 (MySQL 内部哈希算法)分区裁剪优化技巧
好的查询（使用分区键）
-- 精确匹配
WHERE order_date = '2022-06-15' -- partitions: p2022
-- 范围查询
WHERE order_date >= '2022-01-01' -- partitions: p2022,p2023,p_future
-- IN 子句
WHERE YEAR(order_date) IN (2021, 2022) -- partitions: p2021,p2022差的查询（不适用分区键）
-- 不包含分区键
WHERE amount > 1000 -- partitions: ALL
-- 对分区键使用函数
WHERE DATE_ADD(order_date, INTERVAL 1 DAY) > NOW() -- partitions: ALL
-- 隐式类型转换
WHERE order_date = 20220615 -- 可能导致全分区扫描查看分区信息
-- 查看表的分区定义
SHOW CREATE TABLE orders;
-- 查看分区详情
SELECT
PARTITION_NAME,
PARTITION_EXPRESSION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';实战案例
-- 创建按月分区的订单表
CREATE TABLE order_records (
id BIGINT AUTO_INCREMENT,
user_id INT,
order_date DATE,
amount DECIMAL(10,2),
PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01'))
);
-- 分区裁剪生效
EXPLAIN SELECT * FROM order_records
WHERE order_date BETWEEN '2023-02-01' AND '2023-02-28';
-- partitions: p202302
-- 分区裁剪失效
EXPLAIN SELECT * FROM order_records WHERE user_id = 100;
-- partitions: p202301,p202302,p202303,p202304在 MySQL 的 EXPLAIN 执行计划中，type 列表示 表的访问方式（连接类型），它是判断 SQL 性能好坏的非常重要指标。
system（最好）
SELECT * FROM sys_config;const
SELECT * FROM user WHERE id = 1;eq_ref
SELECT *
FROM order o
JOIN user u ON o.user_id = u.id;ref
SELECT * FROM user WHERE age = 18;fulltext
SELECT * FROM article WHERE MATCH(content) AGAINST('mysql');ref_or_null
SELECT * FROM user WHERE age = 18 OR age IS NULL;index_merge
SELECT * FROM user WHERE age = 18 OR sex = 'M';unique_subquery
SELECT * FROM user
WHERE id IN (SELECT user_id FROM order);index_subquery
range
BETWEEN、>、<、>=、<=、INSELECT * FROM user WHERE age BETWEEN 18 AND 30;index
SELECT id FROM user;ALL（最差）
SELECT * FROM user;性能排序总结
目标：至少达到ref、尽量避免ALL
system
const
eq_ref
ref
range
index
ALLpossible_keys (可能使用的索引)
key（实际使用的索引）
示例
-- 创建示例表
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50),
INDEX idx_name (name),
INDEX idx_age (age),
INDEX idx_city (city)
);
-- 示例1: possible_keys 和 key 都有值
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 25;| possible_keys | key | 说明 |
|---|---|---|
| idx_name, idx_age | idx_name | 可以用 name 或 age 索引，实际选择了 name |
-- 示例2: 有 possible_keys 但 key 为 NULL
EXPLAIN SELECT * FROM users WHERE age + 1 = 26;| possible_keys | key | 说明 |
|---|---|---|
| idx_age | NULL | 索引列使用了函数，索引失效 |
-- 示例3: key 不在 possible_keys 中
EXPLAIN SELECT id FROM users WHERE age > 20;| possible_keys | key | 说明 |
|---|---|---|
| idx_age | PRIMARY | 使用了覆盖索引优化 |
常见情况对比
| 情况 | possible_keys | key | 含义 |
|---|---|---|---|
| 正常使用索引 | idx_name | idx_name | 选择了可能的索引 |
| 索引失效 | idx_name | NULL | 有索引但未使用 |
| 全表扫描 | NULL | NULL | 无可用索引 |
| 覆盖索引 | idx_age | PRIMARY | 优化器选择更优方案 |
| 强制索引 | idx_name | idx_age | 使用 FORCE INDEX |
注意事项
possible_keys 多不一定好
key 为 NULL 需要关注
-- 可能原因：
- 索引列使用函数: WHERE YEAR(date) = 2024
- 隐式类型转换: WHERE varchar_col = 123
- 使用 NOT、!=、<> 操作符
- LIKE 以通配符开头: WHERE name LIKE '%John'
- OR 条件未全部建索引优化器可能不选 possible_keys 中的索引
实用示例
-- 查看为什么没用索引
EXPLAIN SELECT * FROM users WHERE age != 25;
-- possible_keys: idx_age
-- key: NULL (!= 可能不走索引)
-- 强制使用索引
EXPLAIN SELECT * FROM users FORCE INDEX(idx_age)
WHERE age != 25;
-- key: idx_age
-- 检查是否是类型转换问题
EXPLAIN SELECT * FROM users WHERE id = '123';
-- 如果 id 是 INT，字符串 '123' 会自动转换，通常仍能用索引
EXPLAIN SELECT * FROM users WHERE name = 123;
-- 如果 name 是 VARCHAR，可能导致索引失效理解这两个字段有助于 优化查询性能 和 诊断索引问题
什么是 key_len
key_len 表示 MySQL
在索引中使用的字节数，它显示了查询优化器决定使用索引的哪些列以及使用了多少字节。
基本数据类型长度
-- 整数类型
TINYINT -- 1 字节
SMALLINT -- 2 字节
MEDIUMINT -- 3 字节
INT -- 4 字节
BIGINT -- 8 字节
-- 日期时间类型
DATE -- 3 字节
TIMESTAMP -- 4 字节
DATETIME -- 8 字节
-- 字符类型
CHAR(n) -- n * 字符集字节数 (utf8mb4 = n*4, utf8 = n*3)
VARCHAR(n) -- n * 字符集字节数 + 2 (长度标识)额外字节
-- 1. 允许 NULL：额外 +1 字节
-- 2. VARCHAR/VARBINARY：额外 +2 字节（存储长度）
-- 3. 变长字段：根据类型不同有所区别计算示例
-- 创建测试表
CREATE TABLE test (
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
age INT NULL,
email VARCHAR(50) NULL,
created_at DATETIME NOT NULL,
INDEX idx_name (name),
INDEX idx_age (age),
INDEX idx_composite (name, age),
INDEX idx_email (email)
) ENGINE=InnoDB CHARSET=utf8mb4;
-- 示例 1: NOT NULL 的 INT
EXPLAIN SELECT * FROM test WHERE id = 1;
-- key_len = 4 (INT 占 4 字节)
-- 示例 2: NULL 的 INT
EXPLAIN SELECT * FROM test WHERE age = 20;
-- key_len = 5 (4 字节 + 1 字节 NULL 标识)
-- 示例 3: NOT NULL 的 VARCHAR(20) utf8mb4
EXPLAIN SELECT * FROM test WHERE name = 'test';
-- key_len = 82 (20 * 4 + 2 = 82)
-- 20 个字符 * 4 字节(utf8mb4) + 2 字节(长度)
-- 示例 4: NULL 的 VARCHAR(50) utf8mb4
EXPLAIN SELECT * FROM test WHERE email = 'test@example.com';
-- key_len = 203 (50 * 4 + 2 + 1 = 203)
-- 50 * 4 + 2(长度) + 1(NULL标识)
-- 示例 5: 复合索引
EXPLAIN SELECT * FROM test WHERE name = 'test' AND age = 20;
-- key_len = 87 (82 + 5)
-- name(82) + age(5)实际案例分析
-- 案例 1: 部分索引使用
CREATE INDEX idx_multi ON test(name, age, email);
EXPLAIN SELECT * FROM test WHERE name = 'John';
-- key_len = 82 (只使用了 name 列)
EXPLAIN SELECT * FROM test WHERE name = 'John' AND age = 30;
-- key_len = 87 (使用了 name + age)
EXPLAIN SELECT * FROM test WHERE name = 'John' AND age = 30 AND email = 'a@b.com';
-- key_len = 290 (使用了全部三列: 82 + 5 + 203)
-- 案例 2: 不同字符集对比
CREATE TABLE test_utf8 (
name VARCHAR(20) NOT NULL
) CHARSET=utf8;
-- key_len = 62 (20 * 3 + 2)
CREATE TABLE test_utf8mb4 (
name VARCHAR(20) NOT NULL
) CHARSET=utf8mb4;
-- key_len = 82 (20 * 4 + 2)key_len的重要意义
判断索引使用情况
-- 通过 key_len 判断复合索引使用了几个列
CREATE INDEX idx_abc ON table(a, b, c);
-- 如果 key_len = a的长度，只用了 a
-- 如果 key_len = a的长度 + b的长度，用了 a, b
-- 如果 key_len = a的长度 + b的长度 + c的长度，用了 a, b, c优化建议
-- key_len 越小越好（在满足查询的前提下）
-- 原因：
-- 1. 占用内存更少
-- 2. 磁盘 I/O 更少
-- 3. 一个页面能存储更多索引项
-- 优化示例
-- 不好的设计
CREATE INDEX idx_name ON users(name VARCHAR(255));
-- 更好的设计（如果实际不需要 255 长度）
CREATE INDEX idx_name ON users(name VARCHAR(50));快速计算公式
key_len = 字段长度 + 可空标识(0或1) + 变长标识(0或2)
具体：
- 定长字段（INT, BIGINT等）：字段长度 + (NULL? 1:0)
- VARCHAR字段：字符数 * 字符集长度 + 2 + (NULL? 1:0)
- CHAR字段：字符数 * 字符集长度 + (NULL? 1:0)在 MySQL 的 EXPLAIN 执行计划中，ref 列表示：
在使用索引进行查找时，MySQL 实际拿 “什么值” 去和 索引列 进行比较
它和 type（连接类型）一起看，能判断索引是如何被使用的。
ref列的含义
ref 显示的是：索引列 与 哪个值/列/常量 进行等值匹配
常见形式包括：const table.column
func NULL
const
表示索引与常量值比较
EXPLAIN
SELECT * FROM user WHERE id = 10;
type: const
ref: const含义：
table.column
表示当前表的索引列，引用了前一个表的某个列
EXPLAIN
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id;结果（对 orders 表）：
type: ref
key: idx_user_id
ref: u.id含义：
func
表示索引列与函数计算结果比较
EXPLAIN
SELECT * FROM user WHERE DATE(create_time) = '2025-01-01';可能出现
ref: func含义：
建议改为
WHERE create_time >= '2025-01-01'
AND create_time < '2025-01-02';NULL
ref为NULL并不一定是坏事，取决于type
type = index / ALL
EXPLAIN SELECT * FROM user;
type: ALL
ref: NULL表示：全表扫描，没有用到索引
type = range
EXPLAIN
SELECT * FROM user WHERE age > 30;
type: range
ref: NULL使用范围扫描，ref不适用于range类型
ref 与 type 的关系速查表
| type | ref 常见值 | 含义 |
|---|---|---|
| const | const | 主键 / 唯一索引等值 |
| ref | const / table.col | 普通索引等值 |
| eq_ref | table.col | JOIN 中唯一索引 |
| range | NULL | 范围查询 |
| index | NULL | 全索引扫描 |
| ALL | NULL | 全表扫描 |
怎么看 ref 是否好
好的情况： - ref = const -
ref = 某个表.某个列 -
type = const / eq_ref / ref
需要警惕： - ref = func - type = ALL -
明明有索引但 ref = NULL
ref告诉你：MySQL 用索引时，拿什么值去查索引
什么是 rows
rows 是 EXPLAIN 执行计划中的一个重要字段，表示
MySQL 估计需要扫描的行数。
这是一个估算值
影响因素
-- 示例表
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50),
INDEX idx_age (age)
);rows 值的含义示例
-- 示例 1: 全表扫描
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';| type | rows | 说明 |
|---|---|---|
| ALL | 10000 | 需要扫描全表 10000 行 |
-- 示例 2: 使用索引
EXPLAIN SELECT * FROM users WHERE age = 25;| type | key | rows | 说明 |
|---|---|---|---|
| ref | idx_age | 100 | 通过索引估计匹配 100 行 |
-- 示例 3: 使用主键
EXPLAIN SELECT * FROM users WHERE id = 1;| type | rows | 说明 |
|---|---|---|
| const | 1 | 主键查询，只有 1 行 |
优化原则
-- 差: rows 很大
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023;
-- rows: 1000000 (全表扫描)
-- 好: rows 较小
EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01'
AND order_date < '2024-01-01';
-- rows: 50000 (使用索引)JOIN 查询中的 rows
EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.age > 18;| table | type | rows | 总扫描量估算 |
|---|---|---|---|
| u | range | 5000 | 5000 |
| o | ref | 10 | 5000 × 10 = 50000 |
优化建议
重要提示
ANALYZE TABLE table_name;filtered 字段看实际过滤后的行数相关命令
-- 更新统计信息以获得更准确的 rows 估算
ANALYZE TABLE users;
-- 查看实际执行情况（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;总结: rows 是评估查询性能的关键指标，rows 值越小通常表示查询效率越高。
什么是 filtered
filtered 是 EXPLAIN 结果中的一个重要字段，表示经过 WHERE 条件过滤后，剩余记录数占读取总记录数的百分比。
基本含义
EXPLAIN SELECT * FROM users WHERE age > 20 AND status = 'active';| 字段 | 含义 |
|---|---|
| rows | 预计需要扫描的行数 |
| filtered | 经过 WHERE 过滤后剩余的行数百分比 |
| 实际影响行数 | rows × filtered / 100 |
取值范围
实际案例
案例 1: 高 filtered 值（好）
EXPLAIN SELECT * FROM orders WHERE order_id = 12345;| id | type | rows | filtered | Extra |
|---|---|---|---|---|
| 1 | const | 1 | 100.00 | - |
filtered = 100%: 使用主键查询，扫描即命中
案例 2: 低 filtered 值（差）
EXPLAIN SELECT * FROM users WHERE age > 18;| id | type | rows | filtered | Extra |
|---|---|---|---|---|
| 1 | ALL | 10000 | 10.00 | Using where |
filtered = 10%: 扫描 10000 行，只有 1000 行满足条件
案例 3: 多表连接
EXPLAIN SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';| id | table | rows | filtered | 实际处理行数 |
|---|---|---|---|---|
| 1 | o | 5000 | 20.00 | 1000 |
| 1 | u | 1 | 100.00 | 1 |
连接总成本: 1000 × 1 = 1000 次连接操作
filtered 的重要性
评估查询效率
-- 低效查询示例
filtered = 5%，rows = 100000
-- 实际有用: 5000 行
-- 浪费扫描: 95000 行多表连接优化
-- Table A: rows=1000, filtered=10% → 100行
-- Table B: rows=500, filtered=50% → 250行
-- 连接成本: 100 × 250 = 25000索引效果验证
-- 无索引
filtered = 10%
-- 添加索引后
filtered = 90% 如何改善 filtered 值
方法 1: 添加索引
-- 优化前
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- filtered: 10%
-- 添加索引
CREATE INDEX idx_email ON users(email);
-- 优化后
-- filtered: 100%方法 2: 调整 WHERE 条件顺序
-- MySQL 会优先使用选择性高的条件
WHERE status = 'active' -- filtered: 10%
AND create_time > '2024-01-01' -- filtered: 30%方法 3: 使用复合索引
CREATE INDEX idx_status_time ON users(status, create_time);
-- filtered 从 10% 提升到 80%+方法 4: 数据分区
-- 将大表分区，减少扫描范围
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);注意事项
ANALYZE TABLErows 字段一起分析才有意义最佳实践
-- 1. 查看执行计划
EXPLAIN SELECT ...;
-- 2. 关注 filtered < 30% 的情况
-- 3. 检查是否缺少索引
-- 4. 更新统计信息
ANALYZE TABLE your_table;
-- 5. 再次验证
EXPLAIN SELECT ...;总结
这个额外信息很多，挑一些平时常见的或者比较重要的额外信息介绍下
Using index(最优)
EXPLAIN SELECT id, name FROM users WHERE name = 'John';Using where
EXPLAIN SELECT * FROM users WHERE age > 18;Using index condition (ICP)
EXPLAIN SELECT * FROM users WHERE name LIKE 'J%' AND age > 20;Using filesort (需要优化)
EXPLAIN SELECT * FROM users ORDER BY age;Using temporary (需要优化)
EXPLAIN SELECT DISTINCT name FROM users;
EXPLAIN SELECT name FROM users GROUP BY name;Using join buffer
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;Impossible WHERE
EXPLAIN SELECT * FROM users WHERE id = 1 AND id = 2;Using union / Using intersect
EXPLAIN SELECT * FROM users WHERE id = 1 OR id = 2;Select tables optimized away
EXPLAIN SELECT MAX(id) FROM users;
EXPLAIN SELECT COUNT(*) FROM users;No tables used
EXPLAIN SELECT 1 + 1;性能优先级总结
| Extra信息 | 性能 | 是否需要优化 |
|---|---|---|
| Using index | ⭐⭐⭐⭐⭐ | ✅ 最优 |
| Select tables optimized away | ⭐⭐⭐⭐⭐ | ✅ 最优 |
| Using index condition | ⭐⭐⭐⭐ | ✅ 好 |
| Using where | ⭐⭐⭐ | 🟡 可接受 |
| Using filesort | ⭐⭐ | ❌ 需要优化 |
| Using temporary | ⭐ | ❌ 需要优化 |
| Using join buffer | ⭐⭐ | ❌ 需要优化 |
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;完整示例
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.00"
},
"table": {
"table_name": "users",
"access_type": "const",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"used_key_parts": ["id"],
"key_length": "4",
"ref": ["const"],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.10",
"prefix_cost": "0.00",
"data_read_per_join": "1K"
},
"used_columns": ["id", "name", "email"]
}
}
}主要字段说明
query_block（查询块）
{
"query_block": {
"select_id": 1, // SELECT标识符
"cost_info": {
"query_cost": "1.00" // 总查询成本
}
}
}table（表信息）
| 字段 | 说明 |
|---|---|
table_name |
表名 |
access_type |
访问类型（system, const, eq_ref, ref, range, index, ALL） |
possible_keys |
可能使用的索引 |
key |
实际使用的索引 |
key_length |
使用索引的长度 |
ref |
索引比较的列 |
rows_examined_per_scan |
每次扫描检查的行数 |
filtered |
过滤后的行百分比 |
cost_info（成本信息）
{
"cost_info": {
"read_cost": "10.50", // 读取成本
"eval_cost": "2.00", // 评估成本
"prefix_cost": "12.50", // 前缀成本
"data_read_per_join": "16K" // 每次连接读取的数据量
}
}JOIN查询
EXPLAIN FORMAT=JSON
SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "50.50"
},
"nested_loop": [
{
"table": {
"table_name": "u",
"access_type": "ref",
"key": "idx_status",
"rows_examined_per_scan": 100
}
},
{
"table": {
"table_name": "o",
"access_type": "ref",
"key": "idx_user_id",
"rows_examined_per_scan": 5
}
}
]
}
}子查询
EXPLAIN FORMAT=JSON
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);{
"query_block": {
"select_id": 1,
"table": {
"table_name": "users"
},
"query_specifications": {
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"table": {
"table_name": "orders",
"materialized_from_subquery": {
"using_temporary_table": true
}
}
}
}
}
}聚合查询
EXPLAIN FORMAT=JSON
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "200.00"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"table": {
"table_name": "employees",
"access_type": "index",
"key": "idx_department"
}
}
}
}临时表和排序
{
"using_temporary_table": true, // 使用临时表
"using_filesort": true // 使用文件排序
}索引条件下推
{
"attached_condition": "(`db`.`table`.`col` > 100)",
"using_index_condition": true // 使用索引条件下推
}覆盖索引
{
"using_index": true // 使用覆盖索引
}美化输出（MySQL 8.0+）
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;| 特性 | 传统格式 | JSON格式 |
|---|---|---|
| 可读性 | 表格形式 | 结构化数据 |
| 成本信息 | 部分 | 详细 |
| 程序解析 | 困难 | 容易 |
| 嵌套查询 | 难以理解 | 层次清晰 |
Extended EXPLAIN 提供了额外的优化器信息，包括
传统方式（MySQL 5.7之前）
-- 执行Extended EXPLAIN
EXPLAIN EXTENDED
SELECT * FROM users WHERE name = 'John';
-- 查看优化后的查询
SHOW WARNINGS;现代方式（MySQL 5.7+）
-- MySQL 5.7+ 默认就是Extended模式
EXPLAIN
SELECT * FROM users WHERE name = 'John';
-- 查看详细信息
SHOW WARNINGS;SHOW WARNINGS 输出解析
基本示例
EXPLAIN SELECT * FROM users WHERE age > 18 AND status = 'active';
SHOW WARNINGS;输出示例
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`users`.`id` AS `id`,
`test`.`users`.`name` AS `name`,
`test`.`users`.`age` AS `age`,
`test`.`users`.`status` AS `status`
from `test`.`users`
where ((`test`.`users`.`status` = 'active')
and (`test`.`users`.`age` > 18))信息类型
| Level | 说明 |
|---|---|
| Note | 优化器重写后的查询 |
| Warning | 潜在的性能问题 |
| Error | 查询错误 |
MySQL Optimizer Trace 是MySQL提供的一种诊断工具，用于分析查询优化器如何选择执行计划。通过启用优化器追踪，用户可以查看优化器在 选择执行计划时的决策过程，包括其做出的每个步骤、评估的成本等信息。
mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.00 sec)enabled为off,说明这个功能默认是关闭的。one_line 是控制输出格式的，为on就所有输出都将在一行中展示。
mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name | Value |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+
1 row in set (0.00 sec)当查询语句执行完成后，就可以到 information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完整的优化过程，
mysql> use information_schema;
Database changed
mysql> desc OPTIMIZER_TRACE;
+-----------------------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------+------------+------+-----+---------+-------+
| QUERY | longtext | NO | | NULL | |
| TRACE | longtext | NO | | NULL | |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int(20) | NO | | 0 | |
| INSUFFICIENT_PRIVILEGES | tinyint(1) | NO | | 0 | |
+-----------------------------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)示例：
假设执行了一下查询
SELECT * FROM employees WHERE department_id = 10;查看 OPTIMIZER_TRACE 表内容
SELECT * FROM OPTIMIZER_TRACE;可能会看到类似以下类似追踪信息
{
"steps": [
{
"id": 1,
"kind": "index_scan",
"table": "employees",
"used_key": "idx_department_id",
"cost": 10.0
},
{
"id": 2,
"kind": "filter",
"table": "employees",
"condition": "department_id = 10",
"cost": 2.0
}
],
"selected_plan": {
"plan_id": 1,
"steps": ["index_scan", "filter"]
}
}这个信息告诉我们优化器选择了对 emplyees
表进行索引扫描，并在该扫描后应用 department_id=10
的过滤条件，最终选择了这个执行计划。
实际的数据都存在抽象的出来的页中，页都存在磁盘上，当需要访问某个页的数据时，就会把完整的页数据全部加载到内存中， 只需要访问一个页的一条记录，也需要把整个页的数据加载到内存中，然后再读写访问，在进行完读写访问之后不着急把该页对应的 内存空间释放掉，而是将其 缓存 起来，这样再次访问该页面，就可以省去磁盘IO的开销。
在MySQL服务器启动时就向操作系统申请了一片连续的内存，来做 Buffer Pool。默认情况只有 128MB，可以在启动服务器时配置 innodb_buffer_pool_size 参数值
[server]
innodb_buffer_pool_size = 268435456上面配置了 Buffer Pool的大小为 256MB。
Buffer Pool中的默认的缓存页和磁盘上默认页大小都是16KB,为了更好的管理缓存页 为每一个缓存页都创建了一些所谓的 控制信息，包括 该页的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息。
为了维护内存中有哪些还没有使用的缓存页，使用双向链表将空闲缓存页的控制块链起来，如下图这样
链表的集结点占用的内存空间并不包含在为 Buffer Pool 申请的一大片连续内存空间之内， 而是单独申请的一块内存空间。
每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并将该缓存页对应的控制块的信息填上（页所在的表空间、页号之类的信息）， 然后将该缓存页对应的free链表节点从链表中移除，表示缓存页已经被使用了。
如果要访问的页已经在Buffer Pool中了，就不用读磁盘了，但是怎么知道该页在不在Buffer Pool中呢
根据 表空间号 + 页号 来定位一个页的，二者组合起来就是一个key，缓存页 就是对应的value，哈希表。
在需要访问某个页的数据时，先从哈希表中根据 表空间号 + 页号 看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有就从 free 链表 中选一个空闲的缓存页，然后把 磁盘对应的页加载到该缓存页的位置。
当修改过内存的缓存页后，内存缓存页和磁盘上的页内容就不同步了。
虽然可以修改内存后，马上同步到磁盘，但是为了优化，使用了 脏标记，在未来某个时刻统一进行入磁盘。
凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫flush链表。
Buffer Pool对应的内存大小毕竟是有限的，如果满了没空闲页了就要把已经用的返还给free链表，为了 缓存命中率， 应该把最不经常访问的优先踢除，最不应该把经常被访问的剔除。
维护一个链表，LRU链表，如果不存在Buffer Pool中加载到缓存页后把缓存页对应控制块放到LRU链表投入插入， 如果缓存页已经在Buffer Pool中，则直接把对应的控制块移动到LRU链表头部， 这样LRU越靠近头部的控制块对应的缓存页说经最经常被访问。
当Buffer Pool中的空闲缓存页使用完时，到LRU链表的尾部找些缓存页淘汰。
上面的简单的LRU链表有两种比较尴尬的情况。
情况一
预读，InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到Buffer Pool中，触发方式不同，预读又分为 线性预读 和 随机预读。
顺序预读：InnoDB有一个系统变量 innodb_read_ahead_threshold,如果顺序访问了某个区（extent）的页面超过了这个系统变量的值，就会触发一次异步读取下一个区中全部的页面到Buffer Pool的请求。
随机预读：如果Buffer Pool中已经缓存了某个区的13个连续的页面，不论这些页面是不是顺序读取的，都会触发一次异步读取本区中的所有其他的页面到Buffer Pool的请求。 InnoDB提供了 innodb_random_read_ahead系统变量，默认值为OFF，但是有问题，
这些预读的页都会放到LRU链表头部，但如果此时 Buffer Pool容量不太大且很多预读的页面都没用到，就会导致处在 LRU 链表尾部的一些缓存页会很快的被淘汰掉。
情况二
全表扫描，当表中记录非常多，表中页非常多，当访问这些页时，会把它们统统加载到Buffer Pool中，意味着 Buffer Pool中的页吧唧一下就被换血了。 InnoDB把LRU链表按照一定比较分为两截。使用频率高的缓存页 热数据，young区域，反之 old区域。
InnoDB，可以通过查看系统变量 innodb_old_blocks_pct 的值来确定 old 区域在 LRU 链表中所占的比例
mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37 |
+-----------------------+-------+
1 row in set (0.00 sec)InnoDB规定，当磁盘上的某个页面在初次加载到 Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部，防止严重干预到 young 区域。
在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下这个访问时间，如果后续的访问时间与第一次访问时间在某个时间间隔内，那么该页面就不会被从 old区域移动到young区域的头部，否则将它移动到young区域的头部。这个时间间隔由系统变量 innodb_old_blocks_time 控制
mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_time';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000 | 单位毫秒
+------------------------+-------+
1 row in set (0.00 sec)如果第一次和最后一次访问该页面的时间间隔小于 1s （很明显在一次全表扫描的过程中，多次访问一个页面中的时间不会超过 1s ），那么该页是不会被加入到 young 区域的。
每次访问一个缓存页都把它移到LRU链表头部，可能开销太大了，可以有一些优化策略，比如 只有被访问的缓存页位于young区域的1/4的后边，才会被移动到LRU链表 头部，这样就可以降低调整LRU链表的频率，从而提升性能。
为了更好的管理 Buffer Pool中的缓存页，InnoDB还引入了一些其他链表，如 unzip LRU链表用于管理解压页， zip clean链表用于管理没有被解压的压缩页，zip free数组中每一个元素都代表一个链表它们组成所谓的 伙伴系统 来为压缩页提供内存空间等等，反正是为了 更好的管理这个 Buffer Pool 引入了各种链表或其他数据结构。
后台有专门的任务每隔一段时间负责把脏页刷新到磁盘，主要有两种。
刷新路径：从LRU链表的冷数据中刷新一部分页面到磁盘，从LRU链表尾部开始扫描一些页，扫描页数量可以通过系统变量 innodb_lru_scan_depth 指定。这种方式被称为 BUF_FLUSH_LRU。
从flush链表中刷新一部分：从 flush链表 中刷新一部分页面到磁盘，刷新的速率取决于当时系统是不是很繁忙，被称之为 BUF_FLUSH_LIST。
后台线程刷新脏页的进度比较慢，导致用户线程在准备加载一个磁盘页到Buffer Pool时没有可用的缓存，只能从LRU链表尾部释放一个，如果尾部的修改了则要同步刷新到磁盘， 这种刷新单个页面到磁盘中的刷新方式被称为 BUF_FLUSH_SINGLE_PAGE。
单一的Buffer Pool可能会影响处理速度，在Buffer Pool特别大的时候，可以把它们拆分成若干个小的Buffer Pool,每个Buffer Pool都称为一个实例，它们都是独立的， 独立的去申请内存，独立的管理各种链表。
[server]
innodb_buffer_pool_instances=2每个Buffer Pool实例占用多少内存空间
innodb_buffer_pool_size/innodb_buffer_pool_instances
InnoDB规定，当 innodb_buffer_pool_size的值小于1G时，设置多个实例是无效的。
新版本的MySQL可以在服务器运行过程中调整 Buffer Pool 大小，但有个问题，每次调整 Buffer Pool 大小时，都需要重新向操作系统申请一块连续的内存空间， 然后将旧的Buffer Pool中的内容复制到这一块新空间，极其耗时。
为了解决这一问题，Buffer Pool实例申请一大片连续的内存空间，称为 一个chunk,这样在调整 Buffer Pool大小时，申请新的chunk就好了
在服务器运行期间调整 Buffer Pool的大小时就是以chunk为单位增加或删除内存空间。chunk的大小通过 innodb_buffer_pool_chunk_size启动参数指定，只能在服务器启动时指定，运行中不能修改。
innodb_buffer_pool_size 必须是
innodb_buffer_pool_chunk_size x innodb_buffer_pool_instances
如果不合适，服务器自己会调控数值做到合适。
Buffer Pool的缓存页除了用来缓存磁盘上的页面之外，还可以存储锁信息、自适应哈希索引等信息
MySQL提供了 SHOW ENGINE INNODB STATUS 语句来查看关于 InnoDB 存储引擎运行过程中的一些状态信息，其中包括Buffer Pool的一些信息
mysql> SHOW ENGINE INNODB STATUS\G
...
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 861628
Buffer pool size 8191
Free buffers 3819
Database pages 4360
Old database pages 1606
Modified db pages 76
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2670, created 1690, written 12304186
0.00 reads/s, 0.00 creates/s, 5.33 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4360, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...
...知道有这回事就好了，一般也用不到。
比如用户之间转账
CREATE TABLE account (
id INT NOT NULL AUTO_INCREMENT COMMENT '自增id',
name VARCHAR(100) COMMENT '客户名称',
balance INT COMMENT '余额',
PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;两条语句只执行一条，忽然服务器断电，一人钱扣了，但没给目标转过去。
即使对于单独的一条语句，之前说的Buffer Pool时也说过，在对某个页面进行读写访问时，都会把这个页面加载到Buffer Pool， 之后如果修改了某个页面，也不会立即同步到磁盘，只是把修改了的页面加载到Buffer Pool的flush链表中，在之后的某个时间点才会刷新到磁盘。
要么全做，要么全不做的规则称之为原子性。
同时进行两次转账操作，分别为T1和T2，在现实世界中T1和T2是应该没有关系的，可以先执行完T1，在执行T2，或者先执行完T2, 再执行T1，对应的数据库操作就像这样。
真实的数据库中T1和T2的操作可能交替执行，比如这样
保证其它的状态转换不会影响到本次状态转换，这个规则被称之为隔离性。
如果数据库中的数据全部符合现实世界中的约束，就说这些说句是一致的，符合一致性的。
如像 NOT NULL、CHECK
CREATE TABLE account (
id INT NOT NULL AUTO_INCREMENT COMMENT '自增id',
name VARCHAR(100) COMMENT '客户名称',
balance INT COMMENT '余额',
PRIMARY KEY (id),
CHECK (balance >= 0)
);更多的一致性需求需要靠写业务代码的程序员自己保证。
当现实世界的一个状态转换完成后，这个转换的结果将永久的保留，称为持久性。
把需要保证 原子性、隔离性、一致性、持久性 的一个或多个数据库操作称之为一个 事务（transaction）。
不同阶段把 事务 大致上划分成了这几个状态：
事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态。
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在 部分提交的 状态。
当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在 失败的 状态。
如果事务执行了半截而变为 失败的 状态,撤销失败事务对当前数据库造成的影响。把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事 务之前的状态，就说该事务处在了 中止的 状态。
BEGIN [WORK];或者
START TRANSACTION;其中 WORK 可以选：
START TRANSACTION READ ONLY;
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;COMMIT [WORK];例如
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)ROLLBACK 语句是手动的去回滚事务时才用的，如果事务在执行过程中遇到了某些错误而无法继续执行，事务自身会自动的回滚。
ROLLBACK [WORK];例如
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)MySQL中并不是所有存储引擎都支持事务，只有InnoDB和NDB存储引擎支持，MyISAM是不支持事务的。
# tbl1支持事务
CREATE TABLE tbl1 (
i int
) engine=InnoDB;
# tbl2不支持事务
CREATE TABLE tbl2 (
i int
) ENGINE=MyISAM;MySQL中有一个系统变量 autocommit
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)默认值是ON，不显式的使用 START TRANSACTION 或 BEGIN 语句开始一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。
想关闭这种 自动提交 的功能，可以使用下边两种方法之一
显示的使用 START TRANSACTION 或 BEGIN 语句开启一个事务，这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
把系统变量 autocommit 的值设置为 OFF，就像
SET autocommit = OFF;写入的多条语句就算是属于同一个事务了，直到我们显式的写出 COMMIT 语句来把这个事务提交掉，或者显式的写出 ROLLBACK 语句来把这个事务回滚掉。
最好不要碰这些东西，用默认的就好了。
当使用 START TRANSACTION 或 BEGIN 语句开启了一个事务，或把系统变量 autocommit 的值设置为 OFF，事务就不会进行自动提交，但是 输入了某些语句之后就会悄悄地提交掉，就像输入了COMMIT一样。 这种因为某些特殊地语句而导致事务提交地情况称为 隐式提交。
保存点 savepoint，在事务对应的数据库语句中打几个点，在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。
SAVEPOINT 保存点名称;回滚到保存点
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;删除某个保存点
RELEASE SAVEPOINT 保存点名称;START TRANSACTION;
-- 执行一些操作
INSERT INTO users (name) VALUES ('Alice');
-- 创建一个保存点
SAVEPOINT before_update;
-- 执行一些操作
UPDATE users SET name = 'Bob' WHERE name = 'Alice';
-- 回滚到保存点
ROLLBACK TO SAVEPOINT before_update;
-- 继续执行其他操作
UPDATE users SET name = 'Charlie' WHERE name = 'Bob';
-- 提交事务
COMMIT;InnoDB存储引擎以页为单位来管理存储空间的，进行的增删查改操作其实本质都是在访问页（包括读页面、写页面、创建新页面等操作）。之前讲 Buffer Pool ，在真正访问页面之前， 需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。
对于一个已经提交的事务，在事务提交后即使系统发生崩溃，这个事务对数据库中所做的更改也不能丢失。
只需要把修改了哪些东西记录一下就好了，比如某个事务将系统表空间的第100号页面中偏移量为1000处的那个字节的值1改成2，只需记录
将第0号表空间的100号页面的偏移量为1000处的值更新为 2
这样在事务提交时，把上述内容刷新到磁盘中，即使之后系统崩溃，重启之后只要按照上述内容所记录的步骤重新更新一下数据页，那么该事务对数据库中所做的修改又可以被恢复出来，也就意味着满足 持久性 的要求。
存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小的
在执行事务的过程中，每执行一条语句，就可能产生若干条 redo 日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO。
当没有为某个表显式的定义主键，并且表中没有定义Unique键，那么InnoDB会自动为表添加一个称之为 row_id的隐藏列作为主键。
为这个 row_id 隐藏列赋值的方式如下：
服务器会在内存中维护一个全局变量，每当向某个包含隐藏列的 row_id 列的表中插入一条记录时，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1.
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个称为 Max Row ID的属性处。
当系统启动时，会将上面提到的 Max Row ID 属性加载到内容中，将该值加上256之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Row ID 属性值）。
写入这个 Max Row ID也是通过 Buffer Pool 也需要redo日志。
redo 日志中只需要记录一下在某个页面的某个偏移量处修改了几个字节的值，具体被修改的内容是啥，InnoDB 把这种极其简单的 redo 日志称之为 物理日志
有时候执行一条语句会修改非常多的页面，包括系统数据页面和用户数据页面（用户数据指的就是聚簇索引和二级索引对应的 B+ 树）
在语句执行过程中， INSERT 语句对所有页面的修改都得保存到 redo 日志中去。
把一条记录插入到一个页面时需要更改的地方非常多。这时我们如果使用上边介绍的简单的物理 redo 日志来记录这些修改时，可以有两种解决方案：
方案一：在每个修改的地方都记录一条 redo 日志。
方案二：将整个页面的 第一个被修改的字节 到 最后一个修改的字节 之间所有的数据当成是一条物理 redo 日志中的具体数据。
MLOG_REC_INSERT （对应的十进制数字为 9 ）：表示插入一条使用非紧凑行格式的记录时的 redo 日志类型。
MLOG_COMP_REC_INSERT （对应的十进制数字为 38 ）：表示插入一条使用紧凑行格式的记录时的 redo 日志类型。
MLOG_COMP_PAGE_CREATE （ type 字段对应的十进制数字为 58 ）：表示创建一个存储紧凑行格式记录的页面的 redo 日志类型。
MLOG_COMP_REC_DELETE （ type 字段对应的十进制数字为 42 ）：表示删除一条使用紧凑行格式记录的redo 日志类型。
MLOG_COMP_LIST_START_DELETE （ type 字段对应的十进制数字为 44 ）：表示从某条给定记录开始删除页面中的一系列使用紧凑行格式记录的 redo 日志类型。
MLOG_COMP_LIST_END_DELETE （ type 字段对应的十进制数字为 43 ）：与 MLOG_COMP_LIST_START_DELETE类型的 redo 日志呼应，表示删除一系列记录直到 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志对应的记录为止。
MLOG_ZIP_PAGE_COMPRESS （ type 字段对应的十进制数字为 51 ）：表示压缩一个数据页的 redo 日志类型。
等等
上面一大堆关于redo日志格式的内容，如果不是为了写一个解析redo日志的工具或者自己开发一套redo日志系统的话， 就没必要把InnoDB中的各种类型的redo日志都研究透，没必要。
目的其实就是知道：redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。
在执行语句的过程中产生的redo日志被InnoDB划分成了若干个不可分割的组
例如向某个索引对应的B+树插入一条记录为例，在向B+树中插入这条记录之前，需要先定位到这条记录应该被插入到哪个叶子节点代表的数据页中，定位到具体的数据页之后，有两种可能的情况
为了防止在悲观插入的过程中只记录了一部分redo日志在系统崩溃重启时会将索引对应的B+树恢复成一种不正确的状态。规定在执行这些需要保证原子性的操作时必须以组的形式来记录的redo日志，在进行系统奔溃重启恢复时，针对某个组中的 redo 日志，要么把全部的日志都恢复掉，要么一条也不恢复。
如何把这些 redo 日志划分到一个组里，就是在该组中的最后一条 redo 日志后边加上一条特殊类型的 redo 日志，该类型名称为 MLOG_MULTI_REC_END ， type 字段对应的十进制数字为 31 ，该类型的 redo 日志结构很简单，只有一个 type 字段。
只有当解析到类型为 MLOG_MULTI_REC_END 的 redo 日志，才认为解析到了一组完整的 redo 日志，才会进行恢复。否则的话直接放弃前边解析到的 redo 日志。
MySQL把对底层页面中的一次原子访问的过程称为一个Mini-Transaction
InnoDB为了更好的及逆行系统崩溃恢复，把mtr生成的redo日志都放在了大小512字节的页中。为了和前边的表空间中的页做区别， 把这里用来存储redo日志的页称为block。 redo log block的示意图如下：
log block header 中属性：
log block trailer 中属性：
InnoDB为了解决磁盘速度过慢的问题引入了 Buffer Pool，写入redo日志时也不能直接写到磁盘上，服务器在启动时向操作系统 申请了一大片称为 redo log buffer的连续内存空间。这片内存空间被划分成若干个连续的redo log block，可以通过启动参数 innodb_log_buffer_size 来指定 log buffer 的大小。
向 log buffer写redo日志的过程是顺序的，先往前边的block中写，空闲空间用完后，再往下一个block中写。
当往log buffer中写入redo日志时，第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以设计InnoDB的提供了一个称为 buf_free 的全局变量。
一个mtr执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组。
并不是每生成一条redo日志，就将其插入到log buffer中，而是每个mtr运行过程中产生的日志先存到一个地方， 当mtr结束的时候，将过程中产生的一组redo日志再全部复制到log buffer中。
不同事务可能是并发执行的，所以 T1、T2之间的mtr可能是交替执行的，每当一个mtr执行完成时，伴随该mtr生成一组redo日志就需要 被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer的。
mtr运行过程中产生的一组redo日志在mtr结束时被复制到log buffer中， 内存里呆着不是办法，一些情况下会被刷新到磁盘里，比如 log buffer 空间不足时，InnoDB如果当前写入log buffer的redo日志已经占满了 log buffer的一半左右，就需要把这些日志刷新到磁盘上。
MySQL数据目录，使用
SHOW VARIABLES LIKE 'datadir';下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件，log buffer 中的日志默认情况下就是刷新到这两个磁盘文件中。
从 ib_file0 开始写，如果 ib_logfile0 写满了，就接着
ib_logfile1 写，如果 ib_logfile1 写满了就去写 ib_logfile2,
最后一个文件写满了 重新转到 ib_logfile0 继续写。
log buffer本质上是一片连续的内存空间，被划分成了若干个 512 字节大小的block。
redo日志文件其实也是由若干个512字节大小的block组成。
redo日志文件组中的每个文件大小都一样，格式也一样，前2028字节， 前4个block是用来存储一些管理信息的。
头部有 log file header、checkpoint1、没用、checkpoint2
checkpoint1,记录关于checkpoint的一些属性，它的具体结构
第3个block未使用，checkpoint2结构和checkpoint1一样。
InnoDB为了记录已经写入的redo日志量， 设计了一个称之为 Log Sequeue Number 的全局变量。
日志序列号，简称 lsn。
上面的是一个block能容下 mtr的情况，还有一个block容不下的情况。
每一组由mtr生成的redo日志都有一个唯一的LSN值与其对应，LSN值越小，说明redo日志产生的越早。
InnoDB提出了一个称为 buf_next_to_write 的全局变量， 标记当前 log buffer 中已经有哪些日志被刷新到磁盘中了。
lsn表示当前系统中写入的redo日志量，这包括log buffer而没有刷新到磁盘的日志。
InnoDB提出了一个表示刷新到磁盘中的redo日志量的全局变量，称为 flushed_to_disk_lsn，系统第一次启动，该变量的值和初始的lsn值是相同的，都是 8704。
当有新的 redo 日志写入到 log buffer 时，首先 lsn 的值会增长， 但 flushed_to_disk_lsn 不变，随后随着不断有 log buffer 中的日志被刷新到磁盘上， flushed_to_disk_lsn 的值也跟着增长。如果两者的值相同时， 说明 log buffer 中的所有redo日志都已经刷新到磁盘中了。
当然其中会涉及到 操作系统 fsync 的问题。
因为 lsn 的值代表系统写入的 redo 日志量的一个总和， 一个 mtr 中产生多少日志，lsn的值就增加多少（当然有时要加上 log block header 和 log block trailer的大小）， 这样 mtr 产生的日志写到磁盘中时，很容易计算某一个lsn值在 redo日志文件组中的偏移量。
mtr代表一次对底层页面的原子访问，在访问过程中可能会产生一组 不可分割的redo日志，在mtr结束时会把这一组redo日志写入到 log buffer 中，除此外 在mtr结束时还有一件非常重要的事做，就是把在 mtr执行过程中可能修改过的页面加入到 Buffer Pool 的flush链表。
在修改缓存页过程中，会在缓存页对应的控制块中记录两个关于页面何时修改的 属性：
假设mtr2执行过程中又修改了页b和页c两个页面， 那么在mtr2执行结束时，就会将页b和页c对应的控制块都加入 到flush链表的头部。并且将mtr2开始时对应的lsn也就是8916写入 页b和页c对应的控制块的 oldest_moditication属性中，把mtr2结束时 对应的lsn，也就是 9948 写入页b和页c对应的控制块的newest_modification属性中。
假设又修改 页b 还有 页d
flush链表中的脏页按照修改发生的时间顺序进行排序， 也就是按照 oldest_modification 代表的LSN进行排序， 被多次更新的页面不会重复插入到flush链表中， 但是会更新 newest_modification 属性的值。
redo日志只是为了系统崩溃后恢复脏页用的， 如果对应的脏页已经刷新到了磁盘，也就是说即使现在系统崩溃， 那么在重启后也用不着使用redo日志恢复该页面了， 所以该redo日志也就没有存在的必要了， 那么它占用的磁盘空间就可以被后续的redo日志所重用。
虽然mtr1和mtr2生成的redo日志都已经被写到了磁盘上，但它们修改的脏页仍留在Buffer Pool中，所以它们生成的redo日志在磁盘上的空间是不可以被覆盖的。随着系统运行，页a被刷新到磁盘
log buffer中mtr1的空间就可以被覆盖使用了。
InnoDB维护一个全局变量 checkpoint_lsn 来代表当前系统可以被覆盖的redo日志总量是多少。
比如说现在页a被刷新到了磁盘，mtr1生成的redo日志就可以被覆盖了，可以进行一个增加checkpoint_lsn的操作， 把这个过程称之为做一次checkpoint。
做一次checkpoint可以分为两个步骤：
Buffer Pool 一般情况都是后台线程对LRU链表和flush链表 进行刷脏操作。
但如果当前系统修改页面的操作十分频繁，这样导致日志操作十分频繁，系统lsn值增长速度过快，后台如果刷脏操作不能将脏页刷出，那么系统无法及时checkpoint，系统必要时会进行同步操作，以至于可以checkpoint。
可以使用 SHOW ENGINE INNODB STATUS 命令查看当前 InnoDB 存储引擎中的各种 LSN 值得情况。
mysql> SHOW ENGINE INNODB STATUS\G
(...省略前边的许多状态)
LOG
---
Log sequence number 124476971
Log flushed up to 124099769
Pages flushed up to 124052503
Last checkpoint at 124052494
0 pending log flushes, 0 pending chkp writes
24 log i/o's done, 2.00 log i/o's/second
----------------------
(...省略后边的许多状态)为了保证事务的持久性，用户线程在事务提交时需要将该事务执行过程中产生的所有redo日志都刷到磁盘上，这个要求太狠了，如果对事务持久性要求不强可以选择修改 innodb_flush_log_at_trx_commit 的系统变量，有三个值可选。
在服务器不挂的情况下，redo日志就是个累赘，万一出现数据库挂了，就可以在重启时根据redo日志中的记录将页面恢复到系统崩溃前的状态。
checkpoint_lsn之前的redo日志都可以被覆盖，也就是这些redo日志对应的脏页都已经被刷新到磁盘中了。
对于checkpoint_lsn之后的redo日志，它们对应的在脏页可能没被刷到磁盘，也可能刷了，所以需要从 checkpoint_lsn 开始读取redo日志来恢复页面。
redo 日志文件组的第一个文件的管理信息中有两个block都存储了 checkpoint_lsn 的信息。
要选取最近发生的那次checkpoint的信息。衡量 checkpoint 发生时间早晚的信息就是所谓的 checkpoint_no ，把 checkpoint1 和 checkpoint2 这两个block中的 checkpoint_no 值读出来比一下大小，哪个的checkpoint_no 值更大，说明哪个block存储的就是最近的一次 checkpoint 信息。
就能拿到最近发生的 checkpoint 对应的 checkpoint_lsn 值以及它在 redo 日志文件组中的偏移量 checkpoint_offset 。
log block结构，写redo日志是顺序写的， 写满一个block之后再往下一个block写， 普通block的log block header部分有一个称为 LOG_BLOCK_HDR_DATA_LEN 的属性， 该属性值记录了当前block里使用了多少字节空间，对于填满的block, 该属性值为 512 不为512 说明，它就是此次崩溃恢复中需要扫描的最后一个block。
由于redo0 在 checkpoint_lsn之前，恢复时可以不管。
现在可以按照redo日志的顺序依次扫描 checkpoint_lsn 之后的各条redo日志， 按照日志中记载的内容将对应的页面恢复出。
InnoDB使用的哈希表，根据redo日志的spaceID和page number属性进行散列。
恢复时，怎么直到某个redo日志对应的脏页是否在崩溃发生时已经刷新到磁盘了呢？
每个页面都有一个称为 File Header 的部分，在 File Header 里有一个称为FIL_PAGE_LSN的属性，该属性记载了最后依次修改页面时对应的lsn值（其实就是页面控制块中的newest_modification值），如果在做了某次checkpoint之后有脏页刷新到磁盘中，那么该页对应的 FIL_PAGE_LSN 代表的lsn值肯定大于 checkpoint_lsn的值，这种情况的页面就不需要重复执行 lsn值小于 FIL_PAGE_LSN的redo日志了，可以进一步加快恢复。
log block header处有一个称为 LOG_BLOCK_HDR_NO 的属性，代表block的唯一编号。
LOG_BLOCK_DHR_NO = ((lsn / 512) & 0x3FFFFFFFUL) + 1& 0x3FFFFFFFUL 用于只取低位的30位，再加1 肯定在 1 和
0x40000000UL 之间，
这个值只有1GB，InnoDB规定redo日志文件组包含的所有文件大小综合不得超过
512GB, 一个block 512字节，redo日志文件组中包含的block块最多位1GB个。
LOG_BLOCK_HDR_NO 值的第一个比特位比较特殊，称之为 flush bit ，如果该值为1，代表着本block是在某次将 log buffer 中的block刷新到磁盘的操作中的第一个被刷入的block。
事务 需要保证 原子性，也就是事务中的操作要么全部完成，要么什么也不做。
但可能出现以外情况：
每当要对一条记录做改动时，改动可以是 INSERT、DELETE、UPDATE，都要留一手，把回滚时所需的东西都给记下来。
这些为了回滚而记录的这些东西称之为撤销日志，undo log。
如果某个事务执行过程中对某个表执行了增、删、改操作，那么InnoDB存储引擎会给它分配一个独一无二的事务id。分配方式如下：
开启一个读写事务，但在这个事务中全是查询语句，并没有执行增、删、改的语句，意味着这个事务并不会被分配一个事务id。
只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务id。
事务id本质上是一个数字。服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务id时，就会把该变量的值当作事务id分配给该事务，并且把该变量自增1。
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为5的页面中一个称为 Max Trx ID 的属性处，这个属性占用8字节的存储空间。
下一次重启时，会将 Max Trx ID 属性加载到内存然后加上256再赋到全局变量上(因为在上次关机时该全局变量的值可能大于Max Trx ID属性值)。
聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为
trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为
row_id 的隐藏列。
trx_id 列就是某个对这个聚簇索引记录做改动的语句所在的事务对应的事务id。
一个事务在执行过程中可能 新增、删除、更新 若干条记录，需要记录很多条对应的undo日志， 这些undo日志会被从 0开始编号，根据生成的顺序分别被称为 第0号undo日志、第1号undo日志、…、第n条undo日志等，这个编号称为 undo no。
undo日志 是被记录到类型为 FIL_PAGE_UNDO_LOG 的页面中，这些页面可以从系统表空间中分配，也可以从专门存放undo日志的表空间 undo table space 中分配。
CREATE TABLE undo_demo (
id INT NOT NULL,
key1 VARCHAR(100),
col VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1)
)Engine=InnoDB CHARSET=utf8;上面标有3列，id是主键，为key1列建立一个二级索引，col列是一个普通列。每个表都会被分配一个唯一的 table id
mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name = 'xiaohaizi/undo_dem o';
+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+
| 138 | xiaohaizi/undo_demo | 33 | 6 | 482 | Barracuda | Dynamic | 0 | Single |
+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.01 sec)上面结果显示，undo_demo 表对应table id为138。
InnoDB设计了一个类型为 TRX_UNDO_INSERT_REC 的undo日志
向某个表插入一条记录时，实际上需要向聚簇索引和所有的二级索引都插入一条记录，记录undo日志时，只需要考虑向聚簇索引插入记录时的情况就好了。
在回滚插入操作时，只需要直到这条记录的主键信息，然后根据主键信息做对应的删除操作，做删除操作时就会把顺带着把所有二级索引中相应的记录也删除掉。
BEGIN;
INSERT INTO undo_demo(id, key1, col)
VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');会产生两条类型为 TRX_UNDO_INSERT_REC 的undo日志。
roll_pointer隐藏列的含义
roll_pointer,占用7个字节的字段，本质是一个指向记录对应的undo日志的一个指针。
Page Header部分有一个称之为PAGE_FREE的属性，它指向由被删除记录组成的垃圾链表中的头节点。
假设准备使用DELETE语句把 正常记录链表 中的最后一条记录删掉，这个删除过程需要两个阶段：
两个阶段都执行了，这条记录就算是真正被删除掉了。
在删除语句所在的事务提交之前，只会经历阶段1，回滚只需考虑对删除操作的阶段1做的影响进行回滚。
InnoDB设计了称之为 TRX_UNDO_DEL_MARK_REC 类型的undo日志。
在对一条记录进行 delete mark 操作前，会把记录旧的trx_id 和 roll_pointer 隐藏列的值记录到对应undo日志。好处就是可以通过 undo 日志的 old roll_pointer 找到记录在修改之前对应的 undo 日志，比如一个事务中，先插入了一条记录，然后又执行对该记录的删除操作。
BEGIN;
INSERT INTO undo_demo(id, key1, col)
VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');
DELETE FROM undo_demo WHERE id = 1;这个delete mark操作对应的undo日志的结构就是这样：
还有另一种称为 TRX_UNDO_UPD_DEL_REC 的undo日志类型。
在执行UPDATE语句时，InnoDB对更新主键和不更新主键这两种情况有截然不同的处理方案。
就地更新，更新记录时，对于被更新的每个列来说，如果更新后的列和更新前的列占用的存储空间都一样大，就可以就地更新。直接在原记录的基础上修改对应列的值。
如果有任何一个被更新的列更新前和更新后占用的存储空间大小不一致，那么就需要先把这条旧的记录从聚簇索引页面中删除掉，然后根据更新后的列的值创建一条新的记录插入到页面中。
针对UPDATE不更新主键的情况，InnoDB设计了一种类型为 TRX_UNDO_UPD_EXIST_REC 的undo日志
BEGIN;
# 插入两条记录
INSERT INTO undo_demo(id, key1, col)
VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');
# 删除一条记录
DELETE FROM undo_demo WHERE id = 1;
# 更新一条记录
UPDATE undo_demo
SET key1 = 'M249', col = '机枪'
WHERE id = 2;
在聚簇索引中，记录是按照主键值的大小连成了一个单向链表的， 如果更新了某条记录的主键值，意味着这条记录在聚簇索引中的位置将会发生改变。
针对UPDATE语句更新了记录主键值的情况，InnoDB在聚簇索引中分了两步处理：
在对该记录进行 delete mark操作前，会记录一条类型为 TRX_UNDO_DEL_MARK_REC 的undo日志。
插入新记录，会记录一条类型为 TRX_UNDO_INSERT_REC 的undo日志。
上一章，讲了为什么需要undo日志，以及 INSERT、DELETE、UPDATE 对数据改动的语句都会 产生什么类型的日志，以及不同类型的undo日志的具体格式是什么。
本章将会讲undo日志会被具体写到什么地方。
在写入undo日志的过程中，会使用多个链表，很多链表都有同样的节点结构。
Pre Node Page Number 和 Pre Node Offset的组合就是指向前一个节点的指针。
Next Node Page Number 和 Next Node Offset 的组合就是指向后一个节点的指针。
为了更好管理链表，InnoDB还管理了一个基节点结构，存储了链表的头节点。
List Length 表明链表一共有多少节点。
First Node Page Number 和 First Node Offset 的组合就是指向链表头节点的指针。
Last Node Page Number 和 Last Node Offset 的组合就是指向链表尾节点的指针。
FIL_PAGE_UNDO_LOG 类型页面是专门用来存储undo日志的，这种类型的页面的通用结构如下图所示：
Undo Page Header是Undo页面所特有的，结构如下
TRX_UNDO_PAGE_TYPE 就是存上章节的 undo日志类型的。有两大类，
把undo日志分成两大类，是因为类型 TRX_UNDO_INSERT_REC 的undo日志在事务提交后 可以删除掉，而其他类型undo日志还需要为所谓的MVCC服务，不能直接删除掉。
一个事务可能包含多个语句，一个语句可能对应若干条记录改动，对每条记录进行改动前， 都要记录1条或2条的undo日志，一个事务执行过程中可能产生很多undo日志。
一个事务执行过程中就可能需要2个Undo页面的链表，一个称为 insert undo链表， 一个称为update undo链表，如下图所示
临时表的undo有自己的链表，和普通表有区分
为了提高undo日志的写入效率，不同事务执行过程中产生的undo日志需要被写入到 不同的undo页面链表，有更多事务就意味着可能会产生更多的undo页面链表。
段 是一个逻辑上的概念，本质上由若干个零散页面和若干个完整的区组成。
B+树索引被划分成两个段，一个叶子节点段、一个非叶子节点段，这样叶子 节点就可以被尽可能存到一起，非叶子节点尽可能存到一起。
每一个段对应一个INode Entry结构，描述段的各种信息，段ID、段内各种链表 基节点、零散页面的页号有哪些信息等等。
为了定位一个INODE Entry,InnoDB设计了 Segment Header结构
每一个Undo页面链表都对应着一个段，称之为 Undo Log Segment，链表中 的页面都是从这个段里面申请的。
Undo页面链表的第一个页面 first undo page 中设计了一个称为 Undo Log Segment Header 部分
第一个页面比普通页面多了个Undo Log Segment Header。
TRX_UNDO_STATE 本Undo页面链表处在什么状态
事务的PREPARE阶段实在所谓的分布式事务中才出现的。
TRX_UNDO_LAST_LOG 本Undo页面链表中最后一个Undo Log Header的位置
TRX_UNDO_FSEG_HEADER 本Undo页面链表对应的段的Segment Header信息
TRX_UNDO_PAGE_LIST Undo页面链表的基节点
一个事务向Undo页面写入undo日志时，写完一条接着写另一条，写完一个Undo页面后， 再从段里申请一个新页面，然后把这个页插入到Undo页面链表中，继续往新申请的页面中写。
InnoDB同一个事务向一个Undo页面链表中写入undo日志是一组
在每写入一组undo日志时，都会在这组undo日志前先记录一下关于这个组的一些属性， 存到称之为Undo Log Header的地方。
Undo页面链表的第一个页面在真正写入undo日志前，会被填充 Undo Pag Header、Undo Log Segment Header、Undo Log Header这三个部分
一般一个Undo页面链表只存储一个事务执行过程中产生的一组undo日志， 某些情况，可能会在一个事务提交后，之后开启的事务重复利用这个Undo页面 链表，导致一个Undo页面中可能存放多组Undo日志， TRX_UNDO_NEXT_LOG和TRX_UNDO_PREV_LOG就是用来标记下一组和上一组undo日志 在页面中的偏移量。
每开启一个事务就创建一个Undo页面链表，即使链表只有一个页面， 存一丢丢undo日志可能太浪费了。
InnoDB在事务提交后在某些情况下重用该事务的Undo页面链表。
一个Undo页面链表是否可以被重用的条件很简单：该链表中只包含一个Undo页面。
insert undo链表这样重用是没问题的
update undo链表，在一个事务提交后undo日志不能马上删除掉。如果之后的事务重用 update undo链表时，就不能覆盖之前事务写入的undo日志，如下图这样
一个事务在执行过程中最多可以分配4个Undo页面链表，在同一时刻不同事务 拥有的Undo页面链表是不一样的，同一时刻由许许多多的Undo页面链表存在。
为了更好的管理这些链表，InnoDB设计了称为 Rollback Segment Header的页面 在这个页面中放了各个Undo页面链表的first undo page的页号，把这些页号称为 undo slot.
每个Undo页面链表相当于一个班，first undo page相当于班长，找到班的班长就可以找班里其他同学。 学校向班级传达通知，召集班长到会议室，Rollback Segment Header相当于是一个会议室。
InnoDB规定，每一个Rollback Segment Header页面都对应着一个段，这个段称为 Rollback Segment。
Rollback Segment 回滚段里只有一个页面。
在未向任何事务分配任何undo页面链表，对于Rollback Segment
Header页面来说，其各个undo slot都被设置成
FIL_NULL=0xFFFFFFFF，表示该 undo slot 不指向任何页面。
有十五需要分配undo页面链表了，从回滚段的第一个undo slot开始，看是不是FIL_NULL 是的话就在表空间中 新创建一个段，然后从段里申请一个页面作为undo页面链表的first undo page，然后将刚才 undo slot设置为 刚申请的页面的地址。
一个 Rollback Segment Header页面中的 undo slot 个数是有限的，如果全部都用了的话，新事务无法再获得新的undo页面链表，就会回滚这个事务并且给用户报错:
Too many active concurrent transactions用户看到这个错误，可以选择重新执行这个事务。
当一个事务提交时，所占用的undo slot有两种命运：如果undo slot指向的Undo页面链表 符合被重用条件 就处于被缓存状态，被缓存的 undo slot都会被加入到一个链表，根据对应undo页面链表类型不同，也会被加入到不同的链表：
如果对应Undo页面链表是insert undo链表，则undo slot会被加入 insert undo cached链表。
如果对应Undo页面链表是update undo链表，则undo slot会被加入 update undo cached链表。
新事物分配undo slot会优先从cached链表中找。
不符合被重用条件，如果是 insert undo链表，则链表的 TRX_UNDO_STATE属性会被设置为 TRX_UNDO_TO_FREE 然后 undo slot设置为 FIL_NULL。 如果是 update undo链表，Undo页面链表的 TRX_UNDO_STATE属性会被设置为 TRX_UNDO_TO_PRUGE,然后 undo slot的值设置为 FIL_NULL，然后将本次事务写入的一组undo日志放到 History链表中。
一个事务执行过程中最多分配4个Undo页面链表，而一个回滚段里只有1024个undo slot。
假设读写事务执行过程只分配1个Undo页面链表，那么1024个undo slot也只能支持1024个读写事务同时执行，再过就崩了。
InnoDB设计了128个回滚段，128 x 1024 = 131072个undo slot。
假设一个读写事务执行过程中只分配1个Undo页面链表，那么可以同时支持131072个读写事务并发执行。
InnoDB在系统表空间的第5号页面某个区域,存了 128个
spaceID(4字节)|Page Number(4字节)，每8个字节大小的格子相当于一个指针，指向某个表空间中的某个页面，这些页面就是
Rollback Segment Header。
128个回滚段是分类的，有一部分是回滚段必须在临时表空间，也就是针对普通表和临时表划分了不同的回滚段。
因为向Undo页面写undo日志本身也是一个写页面的过程，InnnoDB为此设计了多种 redo日志类型，也就是对 Undo 页面 做的任何改动都会记录相应类型的 redo日志。
对于临时表来说，修改临时表产生的undo日志只需要在系统运行过程中有效，系统崩溃了再重启也不需要恢复这些undo日志所在的页面。
下面以事务对普通表的记录做改动为例，梳理事务执行过程中分配Undo页面 链表时的完整过程。
临时表的记录改动步骤和上面一样。
如果事务执行过程，既对普通表改动，又对临时表改动，就要为这个记录分配2个回滚段，并发执行的不同事务也可以被分配相同的回滚段，只要分配不同的undo slot就可以了。
128个回滚段，这是默认值，可以用 启动参数 innodb_rollback_segments 配置回滚段的数量 可配置范围 1-128。
这个参数无论怎么配置，不会影响临时表的回滚段一直都是32。
默认情况，针对普通表设立的回滚段(0号以及33-127)都是分配到系统表空间。
除了第0号回滚段一直分配在系统表空间，第33-127号可以通过配置放到自定义的undo表空间。
启动参数
为了顺利讲解，需要创建一个表
CREATE TABLE hero (
number INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8;然后向这个表里插入一条数据
INSERT INTO hero VALUES(1, '刘备', '蜀');目前表里的数据就是这样的
mysql> SELECT * FROM hero;
+--------+--------+---------+
| number | name | country |
+--------+--------+---------+
| 1 | 刘备 | 蜀 |
+--------+--------+---------+
1 row in set (0.00 sec)MySQL是一个客户端、服务器架构的软件，对于一个服务器可以有若干个客户端与之连接。
每个客户端与服务器连接，就可以称之为一个对话 session。
每个客户端都可以请求服务器执行语句，对于服务器来说可能同时处理多个事务。
事务有一个称为 隔离性 的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当事务提交之后，其他事务才可以继续访问这个数据。
这样对性能影响太大，需要 舍弃一部分 隔离性 换取性能。
脏写（Dirty Write）
如果一个事务修改了另一个未提交事务修改过的数据，就意味着发生了脏写
这样，Session A中的事务就会很懵逼，明明把数据更新了，最后也提交了事务，最后等于啥也没干。
脏读（Dirty Read）
如果一个事务读到了另一个未提交事务修改过的数据，就意味着脏读
Session A中的事务相当于读到了一个不存在的数据。
不可重复读（Non-Repeatable Read）
如果一个事务只读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读。
幻读（Phantom）
如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，就意味着发生了幻读。
如果Session B中删除一些符合 number > 0
的记录，而不是插入新记录，Session A中之后再根据
number > 0 的条件读取的记录变少了，这种现象不是
幻读。
幻读 强调的是一个事务按照某个相同的条件读取多次记录时，读取到之前没有读到的记录。
四种问题严重性排序
脏写 > 脏读 > 不可重复读 > 幻读
SQL标准，在标准中设立了4个隔离级别：
SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题
| 隔离级别 | 脏写 | 脏读 | 不可重复读 |
|---|---|---|---|
| READ UNCOMMITTED | Not Possible | Possible | Possible |
| READ COMMITTED | Not Possible | Not Possible | Possible |
| REPEATABLE READ | Not Possible | Not Possible | Not Possible |
| SERIALIZABLE | Not Possible | Not Possible | Not Possible |
脏写这个问题太严重了，不论是那种隔离级别，都不允许脏写的情况发生。
不同的数据库厂商对SQL标准中规定的四种隔离级别支持不一样，Oracle只支持 READ COMMITTED和SERIALIZABLE隔离级别。
MySQL虽然支持4种隔离级别，但与SQL标准种所规定的各级隔离级别允许发生的问题有些出入。
MySQL在REPEATABLE READ 隔离级别下，是可以禁止幻读问题发生的。
MySQL的默认隔离级别为 REPEATABLE READ。
SQL标准支持最好的还得看Postgres。
可以通过下边的语句修改事务的隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中的 level 可选值有4个：
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}在SET关键字后面的 GLOBAL、SESSION、什么都不放，会对不同范围的事务产生不同的影响。
可以在服务器启动时改变事务的默认隔离级别，可以修改启动参数 transaction-isolation的值。
--transaction-isolation=SERIALIZABLE要查看当前会话默认的隔离级别可以通过查看系统变量 transaction_isolation 的值确定：
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)
#或者使用更简便的写法
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)对于InnoDB存储引擎的表，聚簇索引记录中都包含两个必要的隐藏列
mysql> SELECT * FROM hero;
+--------+--------+---------+
| number | name | country |
+--------+--------+---------+
| 1 | 刘备 | 蜀 |
+--------+--------+---------+
1 row in set (0.07 sec)
insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，占用的Undo Log Segment也会被系统回收。
假设之后两个事务id分别为 100、200 的事务对这条记录进行 UPDATE 操作，操作流程如下
InnoDB使用锁来保证不会有脏写情况的发生，在第一个事务更新了某条记录后，就给这条记录加锁，另一个事务再次更新时就需要等待第一个事务提交了，把锁释放之后才可以继续更新。
每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个 roll_pointer 属性（INSERT操作对应的undo日志没有该属性）。
所有的版本都会被roll_pointer属性连接成一个链表，把这个链表称之为 版本链，版本链的头节点就是当前记录最新的值。
需要判断版本链中的哪个版本是当前事务可见的，InnoDB提出了ReadView，
只有在对表中的记录做改动时（执行 INSERT、DELETE、UPDATE）才会为事务分配事务id
有了这个 ReadView,在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见
如果某个版本的数据对当前事务不可见，就顺着版本链找到下一个版本的数据，继续按照上面步骤判断可见性，直到版本链中最后一个版本，依旧不可见，意味着该条记录对事务完全不可见，查询结果就不该包含该记录。
READ COMMITTED 每次读取数据前都生成一个ReadView
假设 hero表只有一条由 事务id 80 的事务插入的一条记录
mysql> SELECT * FROM hero;
+--------+--------+---------+
| number | name | country |
+--------+--------+---------+
| 1 | 刘备 | 蜀 |
+--------+--------+---------+
1 row in set (0.07 sec)比如现在系统中有两个事务id 100、200在执行
# Transaction 100
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;
# Transaction 200
BEGIN;
# 更新了一些别的表的记录
假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行
# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1: Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为 刘备这个SELECT1执行过程如下：
[100、200],min_trx_id 为100,max_trx_id为201,creator_trx_id
为0，然后从版本链中挑选可见记录之后把事务100提交
# Transaction 100
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;
COMMIT;再到事务200更新表hero中number为1的记录
# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...
UPDATE hero SET name = '赵云' WHERE number = 1;
UPDATE hero SET name = '诸葛亮' WHERE number = 1;此刻hero中number为1的记录版本链就长这样：
回到刚才READ COMMITTED隔离级别的那个事务
# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'
# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞'SELECT2执行过程，生成ReadView,m_ids里只有200，min_trx_id为200，max_trx_id为201，creator_trx_id 为0，最后找到张飞那个版本，满足了可见性要求。
用 READ COMMITTED 隔离级别的事务在每次查询开始时都会生成一个独立的 ReadView。
REPEATABLE READ 在第一次读取数据时生成一个ReadView
在使用 REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个Read View，之后查询就不会重复生成了。
看过上面 READ COMMITTED 部分，脑部一下这边的 ReadView 和 版本链，也能想出来为什么。
所谓 MVCC (Multi-Version Concurrency Control) 多版本并发控制，指的就是在使用 READ COMMITTED、REPEATABLE READ两种隔离级别的事务在执行普通 SELECT 操作时访问记录的版本链过程，可以使不同事务的 读-写、写-读 操作并发执行，提升系统性能。
insert undo 在事务提交之后就可以被释放掉了
update undo由于还需要支持MVCC，不能立即删除掉。
为了支持 MVCC，对于 delete mark操作，仅仅在记录上打一个删除标记，并没有真正将它删除掉。
随着系统的运行，在确定系统中包含最早产生的那个ReadView的事务不会再访问某些update undo日志以及被打了删除标记的记录后，后台运行的 purge 线程会把它们真正删除掉。
对于第三种情况，有两种解决方式
采用MVCC方式，读-写操作彼此并不冲突，性能更高
采用加锁方式，读-写操作彼此需要排队执行，影响性能。
一般情况采用MVCC来解决 读-写 操作并发执行的问题，特殊业务下必须采用 加锁 方式执行，那也没有办法。
事务利用MVCC进行读取操作称之为 一致性读，或者 一致性无锁读，有的地方也称为 快照读。
所有普通 SELECT语句在 READ COMMITTED、REPEATABLE READ 隔离级别下都算是 一致性读。
SELECT * FROM t;
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col2;一致性读不会对表中的任何记录加锁操作，其他事务可以自由对表中的记录做改动。
并发事务，对于 读-读 不会引起什么问题，对于 写-写、读-写、写-读 可能会引起一些问题，需要使用 MVCC 或 加锁 的方式来解决它们。
既要允许 读-读 情况不受影响，又要使用 写-写、读-写、写-读 情况中的操作相互阻塞，MySQL为锁分了类。
两条事务可以同时有一条记录的 S锁，但 S锁和X锁是冲突的，X锁和X锁也是冲突的。
T1有S锁，T2也能获得S锁。
T1有S锁，T2无法获得X锁。
T1有X锁，T2无法获得S锁与X锁。
对读取的记录加S锁,如果当前事务执行了该语句，它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁，但不能获取这些记录的X锁。
如果别的事务想要获取这些记录的X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。
SELECT ... LOCK IN SHARE MODE;对记录加X锁,既不允许别的事务获取这些记录的S锁，也不允许获取这些记录的X锁，直到当前事务提交之后将这些记录上的X锁释放掉。
SELECT ... FOR UPDATE;平时用到的写操作无非 DELETE、UPDATE、INSERT三种；
前边提到的锁都是针对记录的，也就是 行级锁 或 行锁。
也可以在 表 级别加锁，表级锁 或 表锁。表锁也可以分为 共享锁（S锁） 和 独占锁（X锁）。
InnoDB有一种称为 意向锁（Intention Locks）：
IS、IX锁是表级锁，它们仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，IS锁和IX锁并不冲突。
表级别的各种锁的兼容性
| 表锁类型 | X | IX | S | IS |
|---|---|---|---|---|
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
| IX | 不兼容 | 兼容 | 不兼容 | 兼容 |
| S | 不兼容 | 不兼容 | 兼容 | 兼容 |
| IS | 不兼容 | 兼容 | 兼容 | 兼容 |
上面的都算理论知识，MySQL支持多种存储引擎，不同存储引擎对锁的支持也是不一样的。
终点讨论InnoDB存储引擎中的锁。
对于 MyISAM、MEMORY、MERGE 这些存储引擎，只支持表级锁，这些引擎并不支持事务。这些存储引擎的锁一般都是针对当前会话来说的。
MyISAM、MEMORY、MERGE引擎的表在同一时刻只允许一个会话对标进行写操作，最好用在 只读、大部分都是读操作、或单用户的情境下。
MyISAM存储引擎有一个称为 Consurrent Inserts的特性，支持在对MyISAM表读取时同时插入记录，可以提升一些插入速度。
InnoDB存储引擎 既支持 表锁，也支持行锁。
表锁实现简单，占用资源少，粒度很粗。
行锁粒度细，可以实现更精准的并发控制。
表级别的 S锁、X锁
在对某个表执行 SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎时不会为这个表添加表级别的S锁 或 X锁。
在对表执行一些诸如 ALTER TABLE、DROP TABLE 这类DDL语句时，其他事务对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，反过来同理。这个过程通过server层使用称之为 元数据锁（Metadata Locks，简称 MDL）来实现的，一般情况下也不会使用InnoDB存储引擎自己提供的表级别的S锁和X锁。
DDL语句 执行时会隐式的提交当前会话中的事务，这主要是DDL语句的执行一般都会在若干个特殊事务中完成，在开启这些特殊事务前，需要将当前会话中的事务提交掉。
InnoDB存储引擎提供的表级别 S锁 或 X锁 相当鸡肋，只会在特殊情况下，比如崩溃恢复过程中使用到。
手动获取，比如在 系统变量 autocommit=0
innodb_table_locks=1 时，手动获取 InnoDB存储引擎提供的表t的
S 锁 或 X锁 可以这么写。
尽量避免使用InnoDB的表级别S锁和X锁
# InnoDB对表t加表级别的S锁
LOCK TABLES t READ;
# InnoDB对表t加表级别的X锁
LOCK TABLES t WRITE;MySQL可以为表某个列添加 AUTO_INCREMENT 属性，之后插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值。
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT,
c VARCHAR(100),
PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;
INSERT INTO t(c) VALUES('aa'), ('bb');其中的原理主要是两个：
采用AUTO-INC锁，就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉，这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
AUTO-INC 锁的作用范围只是单个插入语句，插入语句执行完成后这个锁就被释放了。
INSERT ... SELECT、REPLACE ... SELECT 或者
LOAD DATA 这种插入语句，一般都是使用 AUTO-INC锁为
AUTO_INCREMENT 修饰的列生成对应的值系统变量 innodb_autoinc_lock_mode
当 innodb_autoinc_lock_mode 为2时，可能会造成不同事务中的插入语句为 AUTO_INCREMENT 修饰的列生成的值是交叉的，在主从复制的场景中是不安全的。
还用这张表讲
CREATE TABLE hero (
number INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number),
KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;插入几条记录
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'),
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴');hero表中的聚簇索引的示意图
把B+树的索引结构做了超级简化，只把索引中的记录拿出来，强调聚簇索引中的记录是按照主键大小排序的，并且省略调用聚簇索引中的隐藏列。
官方名称 LOCK_REC_NOT_GAP，有 S锁 和 X锁之分，只锁某一条记录。
MySQL在 REPEATABLE READ隔离级别下，是可以解决幻读问题的，解决方案有两种，使用MVCC方案解决，也可以采用加锁方案。提出了 Gap Locks锁。官方名称 LOCK_GAP。
图中为number值为8的记录加了gap锁，意味着不允许别的事务在number值为8的记录前边的间隙插入新记录。其实就是
(3,8) 这个区间的新记录是不允许立即插入的。
Gap锁 提出仅仅是为了防止插入幻影记录而提出的。
两端，通过两条伪记录
想锁住某条记录，又想阻止其他事务在该记录前边的间隙擦汗如记录，用Next-Key Locks，官方名 LOCK_ORDINARY
LOCK_REC_NOT_GAP 和 LOCK_GAP
的结合体，既可以保护该条记录，又能阻止别的事务将新纪录插入被保护记录前边的间隙。
一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap 锁，有的话需要等待，直到拥有 gap 锁的那个事务提交。
InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想要某个 间隙 中插入新纪录，但是现在等待，这样的锁命名为 Insert Intention Locks,官方名 LOCK_INSERT_INTENTION，插入意向锁。
下面是三个事务，在记录8位置都有锁
T1持有gap锁，所以T2和T3需要生成一个插入意向锁的所借都并且处于等待状态。当 T1提交后会把获取的锁都释放掉，T2和T3就能获取对应的插入意向锁了，T2和T3之间 不会相互阻塞，可以同时获取number值为8的插入意向锁，然后执行插入操作。
插入意向锁不会阻止别的事务继续获取该记录上任何类型的锁。
对一条记录加锁的本质就是在内存中创建一个 所结构 与之关联。
# 事务T1
SELECT * FROM hero LOCK IN SHARE MODE;这条语句需要为hero表中的所有记录进行加锁，InnoDB为了节约空间，在对不同记录加锁时，如果符合下边条件：
那么这些记录的锁就可以被放到一个锁结构中。
深究这些内存字段没有必要，直到有这么回事就好了，把上层的业务使用掌握好才是重中之重。
很难评价，不能说里面没干货，解释太多底层代码里的字段确实没有必要，既没有使讲的知识变得通俗易懂，反而使得读起来很吃力。不如将知识抽象出来然后再讲。
其中的B+树索引部分、以及崩溃恢复、事务MVCC、锁 部分，才是最有价值的内容，着重了解这几个部分就好了。