SYSTEM
表优化
src/content/notes/system/表优化.md 系统优化
表优化
分区表
基本介绍
分区表是将大表的数据按分区字段分成许多小的子集,建立一个以 ftime 年份为分区的表:
CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
INSERT INTO t VALUES('2017-4-1',1),('2018-4-1',1);-- 这两行记录分别落在 p_2018 和 p_2019 这两个分区上
这个表包含了一个.frm 文件和 4 个.ibd 文件,每个分区对应一个.ibd 文件
- 对于引擎层来说,这是 4 个表,针对每个分区表的操作不会相互影响
- 对于 Server 层来说,这是 1 个表
分区策略
打开表行为:第一次访问一个分区表时,MySQL 需要把所有的分区都访问一遍,如果分区表的数量很多,超过了 open_files_limit 参数(默认值 1024),那么就会在访问这个表时打开所有的文件,导致打开表文件的个数超过了上限而报错
通用分区策略:MyISAM 分区表使用的分区策略,每次访问分区都由 Server 层控制,在文件管理、表管理的实现上很粗糙,因此有比较严重的性能问题
本地分区策略:从 MySQL 5.7.9 开始,InnoDB 引擎内部自己管理打开分区的行为,InnoDB 引擎打开文件超过 innodb_open_files 时就会关掉一些之前打开的文件,所以即使分区个数大于 open_files_limit,也不会报错
从 MySQL 8.0 版本开始,就不允许创建 MyISAM 分区表,只允许创建已经实现了本地分区策略的引擎,目前只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略
Server 层
从 Server 层看一个分区表就只是一个表
-
Session A:
SELECT * FROM t WHERE ftime = '2018-4-1'; -
Session B:
ALTER TABLE t TRUNCATE PARTITION p_2017; -- blocked
现象:Session B 只操作 p_2017 分区,但是由于 Session A 持有整个表 t 的 MDL 读锁,就导致 B 的 ALTER 语句获取 MDL 写锁阻塞
分区表的特点:
- 第一次访问的时候需要访问所有分区
- 在 Server 层认为这是同一张表,因此所有分区共用同一个 MDL 锁
- 在引擎层认为这是不同的表,因此 MDL 锁之后的执行过程,会根据分区表规则,只访问需要的分区
应用场景
分区表的优点:
-
对业务透明,相对于用户分表来说,使用分区表的业务代码更简洁
-
分区表可以很方便的清理历史数据。按照时间分区的分区表,就可以直接通过
alter table t drop partition这个语法直接删除分区文件,从而删掉过期的历史数据,与使用 drop 语句删除数据相比,优势是速度快、对系统影响小
使用分区表,不建议创建太多的分区,注意事项:
- 分区并不是越细越好,单表或者单分区的数据一千万行,只要没有特别大的索引,对于现在的硬件能力来说都已经是小表
- 分区不要提前预留太多,在使用之前预先创建即可。比如是按月分区,每年年底时再把下一年度的 12 个新分区创建上即可,并且对于没有数据的历史分区,要及时的 drop 掉
参考文档:https://time.geekbang.org/column/article/82560
临时表
基本介绍
临时表分为内部临时表和用户临时表
-
内部临时表:系统执行 SQL 语句优化时产生的表,例如 Join 连接查询、去重查询等
-
用户临时表:用户主动创建的临时表
CREATE TEMPORARY TABLE temp_t like table_1;
临时表可以是内存表,也可以是磁盘表(多表操作 → 嵌套查询章节提及)
- 内存表指的是使用 Memory 引擎的表,建立哈希索引,建表语法是
create table … engine=memory,这种表的数据都保存在内存里,系统重启时会被清空,但是表结构还在 - 磁盘表是使用 InnoDB 引擎或者 MyISAM 引擎的临时表,建立 B+ 树索引,写数据的时候是写到磁盘上的
临时表的特点:
- 一个临时表只能被创建它的 session 访问,对其他线程不可见,所以不同 session 的临时表是可以重名的
- 临时表可以与普通表同名,会话内有同名的临时表和普通表时,执行 show create 语句以及增删改查语句访问的都是临时表
- show tables 命令不显示临时表
- 数据库发生异常重启不需要担心数据删除问题,临时表会自动回收
重名原理
执行创建临时表的 SQL:
create temporary table temp_t(id int primary key)engine=innodb;
MySQL 给 InnoDB 表创建一个 frm 文件保存表结构定义,在 ibd 保存表数据。frm 文件放在临时文件目录下,文件名的后缀是 .frm,前缀是 #sql{进程 id}_{线程 id}_ 序列号,使用 select @@tmpdir 命令,来显示实例的临时文件目录
MySQL 维护数据表,除了物理磁盘上的文件外,内存里也有一套机制区别不同的表,每个表都对应一个 table_def_key
- 一个普通表的 table_def_key 的值是由
库名 + 表名得到的,所以如果在同一个库下创建两个同名的普通表,创建第二个表的过程中就会发现 table_def_key 已经存在了 - 对于临时表,table_def_key 在
库名 + 表名基础上,又加入了server_id + thread_id,所以不同线程之间,临时表可以重名
实现原理:每个线程都维护了自己的临时表链表,每次 session 内操作表时,先遍历链表,检查是否有这个名字的临时表,如果有就优先操作临时表,如果没有再操作普通表;在 session 结束时对链表里的每个临时表,执行 DROP TEMPORARY TABLE + 表名 操作
执行 rename table 语句无法修改临时表,因为会按照 库名 / 表名.frm 的规则去磁盘找文件,但是临时表文件名的规则是 #sql{进程 id}_{线程 id}_ 序列号.frm,因此会报找不到文件名的错误
主备复制
创建临时表的语句会传到备库执行,因此备库的同步线程就会创建这个临时表。主库在线程退出时会自动删除临时表,但备库同步线程是持续在运行的并不会退出,所以这时就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行
binlog 日志写入规则:
- binlog_format=row,跟临时表有关的语句就不会记录到 binlog
- binlog_format=statment/mixed,binlog 中才会记录临时表的操作,也就会记录
DROP TEMPORARY TABLE这条命令
主库上不同的线程创建同名的临时表是不冲突的,但是备库只有一个执行线程,所以 MySQL 在记录 binlog 时会把主库执行这个语句的线程 id 写到 binlog 中,在备库的应用线程就可以获取执行每个语句的主库线程 id,并利用这个线程 id 来构造临时表的 table_def_key
- session A 的临时表 t1,在备库的 table_def_key 就是:
库名 + t1 +“M 的 serverid" + "session A 的 thread_id” - session B 的临时表 t1,在备库的 table_def_key 就是 :
库名 + t1 +"M 的 serverid" + "session B 的 thread_id"
MySQL 在记录 binlog 的时不论是 create table 还是 alter table 语句都是原样记录,但是如果执行 drop table,系统记录 binlog 就会被服务端改写
DROP TABLE `t_normal` /* generated by server */
跨库查询
分库分表系统的跨库查询使用临时表不用担心线程之间的重名冲突,分库分表就是要把一个逻辑上的大表分散到不同的数据库实例上
比如将一个大表 ht,按照字段 f,拆分成 1024 个分表,分布到 32 个数据库实例上,一般情况下都有一个中间层 proxy 解析 SQL 语句,通过分库规则通过分表规则(比如 N%1024)确定将这条语句路由到哪个分表做查询
select v from ht where f=N;
如果这个表上还有另外一个索引 k,并且查询语句:
select v from ht where k >= M order by t_modified desc limit 100;
查询条件里面没有用到分区字段 f,只能到所有的分区中去查找满足条件的所有行,然后统一做 order by 操作,两种方式:
- 在 proxy 层的进程代码中实现排序,拿到分库的数据以后,直接在内存中参与计算,但是对 proxy 端的压力比较大,很容易出现内存不够用和 CPU 瓶颈问题
- 把各个分库拿到的数据,汇总到一个 MySQL 实例的一个表中,然后在这个汇总实例上做逻辑操作,执行流程:
- 在汇总库上创建一个临时表 temp_ht,表里包含三个字段 v、k、t_modified
- 在各个分库执行:
select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100 - 把分库执行的结果插入到 temp_ht 表中
- 在临时表上执行:
select v from temp_ht order by t_modified desc limit 100