使用MySQL数据库开发中常见问题（一）

学习笔记是学习了 极客时间 - 《MySQL实战45讲》整理的笔记。

使用delete语句，表文件大小不变？

主要是以 InnoDB 引擎来展开讨论。一个 InnoDB 表包 含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后 缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。

今天主要是来讨论使用delete语句后，达不到回收表空间的效果，首先要了解一个参数innodb_file_per_table 这个参数是用来控制表数据的存放位置。

• OFF：表的数据放在系统共享表空间，也就是跟数据字典放 在一起。
• ON：每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件 中（ MySQL 5.6.6 之后都默认为ON）

无论什么版本，大家都要将此参数设置为ON，因为，一个表单独存储 为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会 直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

我们以MYSQL 8 来讨论也就是 innodb_file_per_table 参数为ON的时候。

MySQL数据删除流程

delete 一条记录

delete from t where ID = 500

deleteOnRow.png

记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4 这条记录被删除后， 如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了。

delete 一个数据页

delete from t where ID between 300 and 700

page.png

当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。以图 1 为例，如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。
如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上， 另外一个数据页就被标记为可复用。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但 磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

插入数据影响空间利用率？

除了删除数据会造成“空洞”,插入数据也会。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就 可能造成索引的数据页分裂。

如下图所示

insert.png

解决“空洞”来收缩表空间

当我们理解了delete语句的删除流程的时候，我们就明白了表空间的复用机制以及“空洞”现象，那么如何来解决这种问题，就需要重建表。

使用命令，来重建表

 alter table A engine=InnoDB 

这个执行的流程如图所示

OnLineDDL.png

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应 的是图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相 同的数据文件，对应的就是图中 state3 的状态；
5. 用临时文件替换表 A 的数据文件。

三种重建表的区别

• optimize table: 等于 recreate table +analyze table。
• analyze table :对表的索引信息做重新统计,没有对表数据和空间做修改。
• alter table : recreate。

Order By 工作原理

假设我们一张用户表

CREATE TABLE `user` (  
    `id` integer NOT NULL,  
    `city` varchar(16) NOT NULL,  
    `name` varchar(16) NOT NULL,  
    `age` integer NOT NULL,  
    `addr` varchar(128) DEFAULT NULL,  
    PRIMARY KEY (`id`),  
    KEY `city` (`city`) 
)

我们要查询 查询城市是“杭州”的所有人名字，并且按 照姓名排序返回前 2个人的姓名、年龄。

select city,name,age from user where city='杭州' order by name limit 1000  ;

全字段排序

全字段排序.png

由于我们在city 字段添加了索引，通常情况下，这个语句执行流程如下所示 ：

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 city 的值不满足查询条件为止
6. 对 sort_buffer 中的数据按照字段 name 做快速排序
7. 按照排序结果取前 1000 行返回给客户端。

在步骤6的过程中可能在内存中完成，也可能需要使用外部排序，这取决 于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序 的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放 不下，则不得不利用磁盘临时文件辅助排序。

使用下面的方法来判断是否使用了临时文件

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 执行语句 */
select city, name,age from user where city='杭州' order by name limit 1000; 
 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;
 
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

得出的结果为

• 内存排序

trace结果.png

• 临时文件排序

临时文件trace结果.png

select @b-@a 的返回结果，也就是标红的字段表示整个执行过程中扫描了多少数据

主要看标红字段里的信息,查看TRACE字段里的对应信息为

内存排序.png

当使用磁盘临时文件辅助排序的结果是

临时文件排序.png

• number_of_tmp_files 表示的是，排序过程中使用的临时文件数。MySQL 将需要排序的数据分成 2 份，每一份单独排序后存在这些临时文件中。然后把这 2个有序文件再合并成一个有序的大文件。(归并算法)
• memory_available:排序空间大小，空间越小，临时文件越多。
• num_rows_found：参与排序的数据数量
• sort_mode: packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

rowid 排序

上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以如果单行很大，这个方法效率不够好。

如果单行长度太大，会如何呢？

由于MYSQL8中即将移除max_length_for_sort_data 参数，我暂时也无法找到控制长度大小的参数，因此给不出截图和例子。

新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。

但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

rowid排序算法.png

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止.
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

联合索引排序

如果在用户表上创建city 和 name 的联合索引，对应的 SQL 语句是：

alter table user add index city_user(city, name);

这样的话 city 取出来的行就是天然name排序的。这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。

排序过程如图所示

联合索引排序.png

1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
3. 从索引 (city,name) 取下一个记录主键 id；
4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束

使用覆盖索引

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

我们来创建 city、name 和 age 的联合索引，对应的 SQL 语句就是：

alter table user add index city_user_age(city, name, age);

排序过程如下

覆盖索引.png

小结

虽然给出了很多排序优化的方法，但是大家别忘了添加过多的索引会带来更多的空间消耗，因此不要光顾着优化排序而影响了查询的性能和更新的性能。

总结

MYSQL8以后更新了很多参数和做了很多优化，目前看的课程是针对MYSQL5.6之后的，因此可能有些不太适用或者本身MYSQL8已经做了优化，网上的资料还是很少，如果有精通MYSQL8+的大神，希望可以帮助我解答一下 rowId索引如何实现。