学习笔记是学习了 极客时间 - 《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
- 建立一个临时文件，扫描表 A 主键的所有数据页；
- 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
- 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应 的是图中 state2 的状态；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相 同的数据文件，对应的就是图中 state3 的状态；
- 用临时文件替换表 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 字段添加了索引，通常情况下，这个语句执行流程如下所示 ：
- 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
- 从索引 city 找到第一个满足 city='杭州’条件的主键 id
- 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
- 从索引 city 取下一个记录的主键 id；
- 重复步骤 3、4 直到 city 的值不满足查询条件为止
- 对 sort_buffer 中的数据按照字段 name 做快速排序
- 按照排序结果取前 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
- 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
- 从索引 city 找到第一个满足 city='杭州’条件的主键 id
- 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
- 从索引 city 取下一个记录的主键 id；
- 重复步骤 3、4 直到不满足 city='杭州’条件为止.
- 对 sort_buffer 中的数据按照字段 name 进行排序；
- 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
联合索引排序
如果在用户表上创建city 和 name 的联合索引，对应的 SQL 语句是：
alter table user add index city_user(city, name);
这样的话 city 取出来的行就是天然name排序的。这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。
排序过程如图所示
联合索引排序.png
- 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
- 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
- 从索引 (city,name) 取下一个记录主键 id；
- 重复步骤 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索引如何实现。
网友评论