9 表相关

《MySQL实战45讲》

https://mp.weixin.qq.com/s/M1dLLuePpdM9vA3F1uJGyw

表空间：https://www.jianshu.com/p/48c63241effb

1 数据库表的空间回收

问题：为什么表数据删掉⼀半，表文件大小不变

⼀个InnoDB表包含两部分，即：表结构定义和数据

在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里；
而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了；
因为表结构定义占用的空间很小，所以主要讨论的是表数据。

delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的；
也就是说，通过delete命令是不能回收表空间的。

这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

不止是删除数据会造成空洞，插⼊数据也会，即可能造成数据页分裂

重建表，就可以把这些空洞去掉，达到收缩表空间的目的；
其流程为：

新建⼀个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中；

由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了；
显然地，表B的主键索引更紧凑，数据页的利用率也更高；
如果把表B作为临时表，数据从表A导⼊表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用；

可以使用alter table A engine=InnoDB命令来重建表，MySQL5.5之前就是上述流程

MySQL 5.6引入的Online DDL，对这个操作流程做了优化：

1. 建立⼀个临时文件，扫描表A主键的所有数据页；
2. 用数据页中表A的记录生成B+树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对A的操作记录在⼀个日志文件（row log）中；
   对应的是图中state2的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到⼀个逻辑数据上与表A相同的数据文件；
   对应的就是图中state3的状态；
5. 用临时文件替换表A的数据文件。
   
   与原先过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在（流程中加黑部分就是这两个功能），这个方案在重建表的过程中，允许对表A做增删改操作，这也就是Online DDL名字的来源。

流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷数据之前就退化成读锁了；
为什么要退化呢？为了实现Online，MDL读锁不会阻塞增删改操作；
为什么不直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。

MySQL5.6之前的流程中，把表A中的数据导出来的存放位置叫作tmp_table。这是⼀个临时表，是在server层创建的；
MySQL5.6之后的流程中，根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。整个DDL过程都在InnoDB内部完成，对于server层来说，没有把数据挪动到临时表，是⼀个“原地”操作，这就是“inplace”名称的来源。

DDL和inplace的关系：

1. DDL过程如果是Online的，就⼀定是inplace的；（在重建表这个逻辑中相同）
2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。
   截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

optimize table、analyze table和alter table这三种方式重建表的区别：

从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）默认的就是上图Online DDL的流程了；

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；

optimize table t 等于recreate+analyze。

2 临时表

和内存表的区别：

内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory；
表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在；

临时表，可以使用各种引擎类型 ；
如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。

2.1 特性

1. 建表语法是create temporary table …。
2. ⼀个临时表只能被创建它的session访问，对其他线程不可见，在这个session结束的时候，会自动删除临时表。
3. 临时表可以与普通表同名，不同担心线程之间的重名冲突
4. session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表；
5. show tables命令不显示临时表。

为什么可以重名？

创建临时表时，MySQL要给这个InnoDB表创建⼀个frm文件保存表结构定义，还要有地方保存表数据；

frm⽂件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_{线程id}_序列号”；

表中数据的存放方式，在不同的MySQL版本中有着不同的处理方式：
在5.6以及之前的版本里，MySQL会在临时文件目录下创建⼀个相同前缀、以.ibd为后缀的文件，用来存放数据文件；
从 5.7版本开始，MySQL引入了⼀个临时文件表空间，专门用来存放临时文件的数据，就不需要再创建ibd文件了。

为什么优先操作临时表？

在实现上，每个线程都维护了自己的临时表链表，每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；

在session结束的时候，对链表里的每个临时表，执行DROP TEMPORARY TABLE +表名操作。

临时表需要写到binlog里面，用来主备复制；
格式要为statment/mixed 的时候，binlog中才会记录临时表的操作；
row则不会记录到binlog里，只会保留最终的逻辑；
如果格式为row，那么主库传drop table命令时其binlog会被改写，因为drop table可以一次删除多个表，如果涉及了临时表和正常表，则只会剩下正常表，因为row格式中临时表是不存在的，备库不会有临时表，这样传给备库时才不会导致备库同步线程停⽌

备库线程在执行的时候，会把主库传来的两个同名的不同线程的临时表当做两个不同的临时表来处理，其实现为：

MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中，这样，在备库的应用线程就能够知道执行每个语句的主库线程id，并利⽤这个线程id来构造临时表的table_def_key

2.2 应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中；
其中，分库分表系统的跨库查询就是⼀个典型的使用场景

分库分表的架构图：比如将⼀个大表ht，按照字段f，拆分成1024个分表，然后分布到32个数据库实例上

如果以分区字段f来进行查询，则可以通过分表规则（比如，N%1024)来确认需要的数据被放在了哪个分表上，这种语句只需要访问⼀个分表

select v from ht where f=N

如果没有用到分区字段f：

select v from ht where k >= M order by t_modified desc limit 100;# k为索引

那么只能到所有的分区中去查找满足条件的所有行，然后统⼀做order by 的操作

两种思路：

1 在proxy层的进程代码中实现排序。

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算；
不过，这个方案的缺点也比较明显：

1. 需要的开发工作量比较大，如果涉及到更复杂的操作，比如group by，甚至join这样的操作，对中间层的开发能比要求比较高；
2. 对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。

2 把各个分库拿到的数据，汇总到⼀个MySQL实例的⼀个表中，然后在这个汇总实例上做逻辑操作。

如上语句的执行流程：

1 在汇总库上创建⼀个临时表temp_ht，表里包含三个字段v、k、t_modified；
2 在各个分库上执行：

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

3 把分库执行的结果插⼊到temp_ht表中；
4 然后执行：

select v from temp_ht order by t_modified desc limit 100;

在实践中往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某⼀个上

2.3 内部临时表

使用union的时候可能会使用，因为要去重，如果使用union all的话就不用，因为不用去重，执行的时候依次执行子查询，得到的结果直接作为结果集的⼀部分，发给客户端。因此也就不需要临时表了。

使用group by也可能会使用，可以通过排序进行优化（能加索引则加索引，不能则直接排序（SQL_BIG_RESULT））

3 复制表

三种将⼀个表的数据导⼊到另外⼀个表中的方法：物理拷贝、musqldump、select…into outfile

后两种方式都是逻辑备份方式，是可以跨引擎使用的。

1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法；
   如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法；
   但是，这种方法的使用也有⼀定的局限性：
   1 必须是全表拷贝，不能只拷贝部分数据；
   2 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
   3 由于是通过拷贝物理⽂件实现的，源表和目标表都是使用InnoDB引擎时才能使⽤。
2. 用mysqldump⽣成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据；
   这个方式的不足之⼀是，不能使用join这种比较复杂的where条件写法。
3. ⽤select … into outfile的方法是最灵活的，⽀持所有的SQL写法；
   这个方法的缺点之⼀是每次只能导出⼀张表的数据，⽽且表结构也需要另外的语句单独备份。

4 分区表

1. MySQL在第⼀次打开分区表的时候，需要访问所有的分区；
2. 在server层，认为这是同⼀张表，因此所有分区共用同⼀个MDL锁；
3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区，减小锁粒度