MySQL优化-InnoDB存储引擎
InnoDB简介
和Oracle很相似。
基于B+树的聚集索引的IOT表。
支持行锁（基于索引实现）。
支持ACID、MVCC、Crash Recovery等特性。
支持4种事务隔离级别，RR下可以避免幻读。
支持外键、全文检索。
支持多种文件格式及数据压缩。
高性能、高并发、高效的buffer pool管理。
log buffer = redo log buffer
dirty page 对innodb表修改的数据页先放在这里。
free page 还没有被使用的空闲内存页。
cache page 缓存读出来还没有被修改的热点数据。
undo log 数据被修改之前，旧版本数据会放在这里。
存储结构
系统表空间文件ibdata*
通用表空间文件*.ibd
用户表空间文件*.ibd
全文索引fts*ibd
全局undo log、用户自定义undo log *.ibu
redo log
全局、会话级临时表空间文件（ibtmp1、*.ibt）
buffer pool dump file
innodb stauts file
tablespace，表空间
表存储对象。
每个tablespace都至少包含2种segment（叶子/非叶子page file segment）
segment，段
对象单位，例如rollback seg，undo seg，index seg等。
每个segment由n个extent以及32个零散page组成。
segment最小以extent为单位扩展。
extent，簇/区
空间管理单位。
每个extent为1/2/4MB，由n个page组成。
page，页
最小I/O单位，16KB，5.6版本起可自定义page size。
表空间
所有的数据都需要存储在表空间中。
表空间分类：
系统表空间
用户独立表空间
undo tablespace，MySQL 5.6+
temporary tablespace，MySQL 5.7+
general tablespace，MySQL 5.7+
系统表空间
总是必须的
文件名ibdata1, ibdata2, ibdata3, ...
innodb_data_home_dir = /data/mysql/3306/innodb_ts
innodb_data_file_path = /data/mysql/3306/innodb_ts/ibdata1:100M:autoextend
随着undo space分离出去，初始化大小100MB左右就够用了。
写多个ibdataN的格式是加分号;
ibdata1:100M:autoextend;ibdata2:100M:autoextend;ibdata3:100M:autoextend
主要存储对象
data dictionary
double write buffer
insert buffer/change buffer
rollback segments
undo space
foreign key constraint system tables
user data，if innodb_file_per_table = 0
用户独立表空间
innodb_file_per_table = 1（默认）
innodb_file_per_table = 0 共享表空间，用户数据存储在系统表空间ibdataN文件中，或者自定义的通用表空间general tablespace中。
存储聚集索引（row data）以及辅助普通索引数据。
每个table都有各自的.ibd文件。
rollback segments，doublewrite buffer，change buffer等仍存储在系统表空间文件里。
表空间可更方便回收。
透明表空间文件迁移。
不过需要更多的file handler，当table数量很大（尤其是很多表分区）时，可能会是个麻烦。
删除大表时的代价也更大。
通用表空间，general tablespace
类似Oracle的做法。
多个table放在同一个表空间中。
可以定义多个通用表空间，并且分别放在不同磁盘上。
可以减少metadata的存储开销。
和系统表空间类似，已分配的表空间无法收缩归还给操作系统（需重建）
8.0后，所有metadata都存储在mysql这个通用表空间中。
create tablespace ts01 add datafile 'ts01.ibd' engine=innodb;
create table app01.t1(id int) tablespace ts01 row_format=dynamic engine=innodb;
select * from information_schema.innodb_tablespaces where name='ts01'\G
show create table app01.t1\G
undo表空间
管理回滚段和undo log。
实例初始化时，默认创建两个undo表空间，最大127个。
可以在线被truncate，因此最少需要两个undo表空间。
选项innodb_rollback_segments用于定义每个undo表空间中的回滚段数量，默认是(即最大值) 是128个。
支持undo表空间加密。
8.0.14后，可以在线手动创建新的undo表空间。
create undo tablespace undo_03 add datafile 'undo_03.ibu';
mysqladmin var | grep -i undo
mysqladmin var | grep -i rollback
移动undo表空间文件
用户自行创建的undo表空间，在实例重启期间可以进行移动（只要目录能被InnoDB识别）。
如果想移动系统自动创建的两个undo表空间，则需要在实例重启后，同时修改选项innodb_undo_directory指向的路径。
实例启动时，会自动扫描数据目录下的undo表空间文件。
MySQL实例启动过程中，会去扫描以下目录：
扫描顺序 innodb_directories、innodb_data_home_dir、datadir、innodb_undo_directory。
查看undo表空间
select * from information_schema.innodb_tablespaces where name='undo_01';
select * from information_schema.files where file_type='undo log';
手动在线删除undo表空间
1.先设置不活跃
alter undo tablespace undo_03 set inactive;
2.而后再删除
drop undo tablespace undo_03;
innodb_undo_log_truncate = ON 时会自动清理undo表空间。
超过innodb_max_undo_log_size时会触发。
或purge线程执行了innodb_purge_rseg_truncate_frequency(128)次。
全局临时表空间，global temporary tablespace
ibtmp1文件。
存储用户临时表的rollback segment。
用户session断开，或删除临时表后，回滚段会删除释放，但表空间不会自动收缩归还给OS，需要等到重启后自动重建。
innodb_temp_data_file_path选项定义路径、大小等，初始值12MB。
限制其最大尺寸会造成无法创建临时表，或者大SQL执行过程中报错。
实例关闭后，文件会被删除；实例启动后，文件重新创建。
无需参与crash recovery，因此也不记录redo log。
select * from information_schema.files where tablespace_name='innodb_temporary'\G
会话级临时表空间，session temporary tablespace
存储用户创建的临时表，及用户执行sql过程中生成的内部（磁盘）临时表。
internal_tmp_disk_storage_engine 设置内部磁盘临时表引擎类型，8.0.16开始强制用InnoDB，该选项也随之失效。
innodb_temp_tablespace_dir = "./#innodb_tmp/" 选项设置会话级用户临时表空间存储路径。
会话级临时表空间（池）初始共有10个，会随着用户连接数及创建临时表的情况按需增加。实例重启后会删除这些文件。
每个session随机分配到某个临时表空间中，例如A用户连接后分配到temp_1.ibt，B用户连接后分配到temp_2.ibt。
每个session最多分配两个临时表空间，一个用于存储用户主动创建的临时表（create temporary table），另一个用于存储用户执行sql过程中生成的内部（磁盘）临时表。
session断开后，会话级用户临时表空间会直接释放，不用再担心撑爆磁盘。
select * from information_schema.innodb_session_temp_tablespaces;
show processlist;
select * from information_schema.innodb_temp_table_info;
共享表空间模式（不推荐使用）
设置innodb_file_per_table = 0
用户数据存储在系统表空间ibdata*文件中，或者自定义的通用表空间（general tablespace）中。
删除大表或删除大量数据时开销更小，drop table / truncate table。
可以使用裸设备，据称性能可能有提升。
删除大表方法：
1.硬链接 ln x.ibd y.ibd
2.执行drop
3.删除y.ibd
查看数据表碎片率
Data_length + Index_length + Data_free, 对比物理ibd文件实际大小。
或者，Rows * Avg_row_length, 对比物理ibd文件实际大小。
5.7.5开始增加选项innodb_fill_factor，可设置100提高填充率。
碎片率 = 1 - 理论大小/实际大小，碎片率大于30%认为碎片率很高。
[dba@localhost:mysql.sock] [app01]> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2019-12-16 10:58:35
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
降低数据表碎片，回收表空间
独立表空间
alter table ** engine = innodb;
optimize table **;
尽量用pt-osc/gh-ost来操作。
共享表空间
整个实例重新逻辑备份导出，再导入。
最好是初始化时，就提前规划系统表空间尺寸，避免后期暴涨。
表空间文件迁移
目标服务器上：alter table xx discard tablespace;
源服务器上：flush table x for export; 备份过去
目标服务器上：alter table xx import tablespace;
表结构务必一致。
innodb_page_size也要一致。
数据在page里是怎么组织存储的，理解从page到row的概念。
page，页（不建议调整）
最小I/O单位，默认16KB。
innodb_page_size
<5.6版本时，不可调整。
5.6版本时，可自定义为8KB、4KB，但不能调大。
5.7以上，才可以改成32KB，64KB。
全局选项，无法在运行过程中动态修改，实例初始化后也无法再修改。
# innblock ./app01/t1.ibd scan 16
cp -a t1.ibd /tmp/
vim -b /tmp/t1.ibd
:%!xxd
或者hexdump -C -v /tmp/t1.ibd | less
row_format
Redundant，最早的行格式。
Compact，将长字段超过768字节的部分off-page存储，5.0之后的默认行格式。
Dynamic，将长字段完全off-page存储，5.7之后的默认行格式。
Compressed，将data、index pages进行压缩，但buffer pool中的page则不压缩（Compressed模式压缩比约1/2，MySQL 5.1期间测试tps下降到原来的1/10，影响巨大；MySQL 5.7版本测试性能相差不大，有时甚至更好。建议自行实际测试后再决定）。
查看行格式：show table status 或查看元数据。
[dba@localhost:mysql.sock] [(none)]> show table status from app01;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------+
| t1 | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | 5 | 2020-04-12 17:43:24 | NULL | NULL | utf8mb4_general_ci | NULL | | 测试表 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------+
1 row in set (0.00 sec)
行溢出，overflow（行迁移、行链接）
行记录长度大约超过page一半时，依次将最长的列拆分到多个page存储，直到不再超过page的一半为止。
溢出的列放在一个page中不够的话，还会继续放在新的page中。
Compact格式下，溢出的列只存储前768字节。
Dynamic格式下，溢出的列只存储前20字节（指针）。
select * 会同时读取这些溢出的列，因此代价很高。
text列上出现filesort或temporary table时，一般都无法放在内存中，需要变成disk tmp table，I/O代价更高。
行格式
默认采用Dynamic格式即可。
少用blob/text长字段类型。
少用select *。
InnoDB表数据字典有2份。
server层，frm文件。
DDL过程中crash的话，有可能会来不及刷新，导致元数据不一致，sync_frm=1。
8.0起全部存储在InnoDB引擎中。
InnoDB层
InnoDB_SYS_DATAFILES
InnoDB_SYS_TABLESTATS
InnoDB_SYS_INDEXES
InnoDB_SYS_FIELDS
InnoDB_SYS_TABLESPACES
InnoDB_SYS_FOREIGN_COLS
InnoDB_SYS_FOREIGN
InnoDB_SYS_TABLES
InnoDB_SYS_COLUMNS
工具：innblock、innodb_ruby
InnoDB内存结构
buffer pool 怎么设置才合理，理解InnoDB的内存管理机制。
在有的文章里写着LRU list、flush list、free list。
LRU list = page cache
flush list = dirty page
free list = free page
innodb内存组织：
innodb_buffer_pool : page cache、dirty page、free page、change buffer、double write buffer、page hash、adaptive hash index、lock system、file system、dictionary cache、recovery system。
redo log buffer
查看表的数据和索引使用情况
select engine, count(*) as TABLES,
concat(round(sum(table_rows)/1000000,2),'M') rs,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
from information_schema.TABLES
where table_schema not in ('mysql', 'performance_schema', 'information_schema', 'test')
group by engine
order by sum(data_length+index_length)
desc limit 10;
[dba@localhost:mysql.sock] [(none)]> select engine, count(*) as TABLES,
-> concat(round(sum(table_rows)/1000000,2),'M') rs,
-> concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
-> concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
-> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
-> round(sum(index_length)/sum(data_length),2) idxfrac
-> from information_schema.TABLES
-> where table_schema not in ('mysql', 'performance_schema', 'information_schema', 'test')
-> group by engine
-> order by sum(data_length+index_length)
-> desc limit 10;
+--------+--------+-------+-------+-------+------------+---------+
| engine | TABLES | rs | DATA | idx | total_size | idxfrac |
+--------+--------+-------+-------+-------+------------+---------+
| InnoDB | 28 | 0.05M | 0.00G | 0.00G | 0.01G | 0.50 |
| NULL | 107 | NULL | NULL | NULL | NULL | NULL |
+--------+--------+-------+-------+-------+------------+---------+
2 rows in set (0.24 sec)
查看buffer pool*有多少个page
[dba@localhost:mysql.sock] [(none)]> select count(*) from information_schema.innodb_buffer_page;
+----------+
| count(*) |
+----------+
| 6400 |
+----------+
1 row in set (0.03 sec)
查看buffer pool的组成
[dba@localhost:mysql.sock] [(none)]> select page_type as Page_Type,
-> sum(data_size)/1024/1024 as Size_in_MB
-> from information_schema.innodb_buffer_page
-> group by Page_Type
-> order by Size_in_MB desc;
+-------------------+------------+
| Page_Type | Size_in_MB |
+-------------------+------------+
| INDEX | 1.08798695 |
| IBUF_INDEX | 0.00000000 |
| INODE | 0.00000000 |
| SYSTEM | 0.00000000 |
| UNKNOWN | 0.00000000 |
| FILE_SPACE_HEADER | 0.00000000 |
| UNDO_LOG | 0.00000000 |
| TRX_SYSTEM | 0.00000000 |
| IBUF_BITMAP | 0.00000000 |
+-------------------+------------+
9 rows in set (0.03 sec)
统计buffer pool里每个索引情况
select table_name as Table_Name,
index_name as Index_Name,
count(*) as Page_Count,
sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by table_name, index_name
order by Size_in_MB desc;
[dba@localhost:mysql.sock] [(none)]> select table_name as Table_Name,
-> index_name as Index_Name,
-> count(*) as Page_Count,
-> sum(data_size)/1024/1024 as Size_in_MB
-> from information_schema.innodb_buffer_page
-> group by table_name, index_name
-> order by Size_in_MB desc;
+----------------------------------------+-----------------------+------------+------------+
| Table_Name | Index_Name | Page_Count | Size_in_MB |
+----------------------------------------+-----------------------+------------+------------+
| `SYS_TABLES` | CLUST_IND | 102 | 1.42480850 |
| `SYS_COLUMNS` | CLUST_IND | 3 | 0.02044487 |
| `mysql`.`innodb_index_stats` | PRIMARY | 3 | 0.01853943 |
| `sakila`.`city` | PRIMARY | 2 | 0.01307106 |
| `sakila`.`customer` | PRIMARY | 2 | 0.01018715 |
| `sakila`.`address` | PRIMARY | 2 | 0.00889492 |
| `student`.`student` | PRIMARY | 1 | 0.00844097 |
| `student`.`achievement` | PRIMARY | 1 | 0.00768566 |
| `sakila`.`actor` | PRIMARY | 1 | 0.00715923 |
| `SYS_INDEXES` | CLUST_IND | 1 | 0.00706387 |
| `SYS_FIELDS` | CLUST_IND | 1 | 0.00527191 |
| `sakila`.`rental` | PRIMARY | 2 | 0.00480556 |
| `sakila`.`film` | PRIMARY | 2 | 0.00367451 |
| `sakila`.`country` | PRIMARY | 1 | 0.00352764 |
| `sakila`.`payment` | PRIMARY | 2 | 0.00313568 |
| `SYS_DATAFILES` | SYS_DATAFILES_SPACE | 1 | 0.00310993 |
| `SYS_TABLESPACES` | SYS_TABLESPACES_SPACE | 1 | 0.00305367 |
| `world`.`city` | PRIMARY | 2 | 0.00262070 |
| `SYS_TABLES` | ID_IND | 1 | 0.00226593 |
| `SYS_FOREIGN` | ID_IND | 1 | 0.00185108 |
| `mysql`.`innodb_table_stats` | PRIMARY | 1 | 0.00170231 |
| `SYS_FOREIGN_COLS` | ID_IND | 1 | 0.00165081 |
| `sakila`.`inventory` | PRIMARY | 2 | 0.00123596 |
| `SYS_FOREIGN` | FOR_IND | 1 | 0.00107098 |
| `SYS_FOREIGN` | REF_IND | 1 | 0.00104332 |
| `student`.`course` | PRIMARY | 1 | 0.00090599 |
| `student`.`lecturer` | PRIMARY | 1 | 0.00079823 |
| `sakila`.`category` | PRIMARY | 1 | 0.00046444 |
| `dba`.`bank_card` | PRIMARY | 1 | 0.00043392 |
| `dba`.`t1` | PRIMARY | 1 | 0.00028992 |
| `app01`.`t1` | PRIMARY | 1 | 0.00028229 |
| `mysql`.`server_cost` | PRIMARY | 1 | 0.00026608 |
| `sakila`.`language` | PRIMARY | 1 | 0.00025177 |
| `sakila`.`FTS_0000000000000042_CONFIG` | FTS_COMMON_TABLE_IND | 1 | 0.00022221 |
| `sakila`.`staff` | PRIMARY | 1 | 0.00020218 |
| `mysql`.`engine_cost` | PRIMARY | 1 | 0.00010681 |
| `mysql`.`gtid_executed` | PRIMARY | 1 | 0.00006771 |
| `sakila`.`store` | PRIMARY | 1 | 0.00004959 |
| `mysql`.`plugin` | PRIMARY | 1 | 0.00003719 |
| `mysql`.`slave_worker_info` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`time_zone_transition` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`time_zone_transition_type` | PRIMARY | 1 | 0.00000000 |
| `SYS_VIRTUAL` | BASE_IDX | 1 | 0.00000000 |
| `mysql`.`servers` | PRIMARY | 1 | 0.00000000 |
| `query_rewrite`.`rewrite_rules` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`time_zone_leap_second` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`slave_relay_log_info` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`time_zone_name` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`slave_master_info` | PRIMARY | 1 | 0.00000000 |
| `mysql`.`time_zone` | PRIMARY | 1 | 0.00000000 |
| NULL | NULL | 6237 | 0.00000000 |
+----------------------------------------+-----------------------+------------+------------+
51 rows in set (0.02 sec)
内存管理
IBP（innodb buffer pool）一般最高设置物理内存的50%-70%，实际使用值还会上浮约10%-15%。
IBP是innodb_buffer_pool_chunk_size（默认128MB）的整数倍。
从5.7开始，可以在线动态调整IBP，因此不建议一开始就设置过大。
设置过大还有可能会导致swap问题。
使用多个instance降低并发内存争用。（前提是buffer pool设置必须超过1G）
page采用hash算法分配到多个instance中读写。
每个缓冲区池管理其自己的数据。
选项innodb_buffer_pool_instances不可动态调整。
每个instance管理自己的free list、flush list、LRU list及其他，并由各自的buffer pool mutex负责并发控制。
[dba@localhost:mysql.sock] [(none)]> show engine innodb status\G
free list => Free buffers
LRU list => Database pages
flush list => Modified db pages
多少内存建议分配几个instance？
总内存数/instances ≈ 8G-16GB
IBP动态resize
MySQL 5.7版本新特性连载（四）http://t.cn/Ew1T6Nm
加大buffer pool
以chunk_size为单位，分配新的内存pages。
扩展AHI（adaptive hash index）链表，将新分配的pages包含进来。
将新分配的pages添加到free list中。
缩减buffer pool
重整buffer pool，准备回收pages（等待这些page里的脏页刷新完毕）。
以chunk_size为单位，释放删除这些pages（这个过程会有一点点耗时）。
调整AHI链表，使用新的内存地址。
latch
latch作用
内存锁，在内存中保护list的内存锁结构。
两种latch
mutex：lock_sys/trx_sys/log_sys，X，串行访问。
rw-lock：S、X、SX，类似行锁的并行访问（读写互斥、读读并行）。
想要获得latch（rounds），需要先spin wait（spins），如果获取不到会进入sleep状态（OS waits）。
innodb_spin_wait_delay，两次spin wait之间的随机等待时间。设置为0可禁止等待间隙。
热门mutex加log_sys->mutex，fil_system->mutex。
查看当前latch争用情况
[dba@localhost:mysql.sock] [(none)]> show engine innodb mutex;
+--------+------------------------+---------+
| Type | Name | Status |
+--------+------------------------+---------+
| InnoDB | rwlock: log0log.cc:846 | waits=2 |
+--------+------------------------+---------+
1 row in set (0.00 sec)
如何降低latch？
优化、提高SQL效率，减少持有latch的数量。
事务尽快结束，缩短持有latch时长。
适当调高IBP instances，缩短latch list长度，降低搜索开销。
buffer pool预加载&导出
innodb_buffer_pool_load_now
innodb_buffer_pool_load_at_startup
------------------------------------
innodb_buffer_pool_dump_pct
innodb_buffer_pool_filename
innodb_buffer_pool_dump_now
innodb_buffer_pool_dump_at_shutdown
预读
随机预读
同一个extent里有13个热点page，就预读整个extent。
yound list的前1/4算热点数据。
innodb_random_read_ahead
线性预读
一个extent中有连续56个page都被顺序访问，则预读下一个extent。
innodb_read_ahead_threshold
设置buffer pool刷新机制
innodb_flush_method = O_DIRECT
预写
脏页刷盘前检查相邻页是否也是脏页，一起刷盘。
SSD设备上通常关闭，没必要采用预写。
SSD设备上建议禁用innodb_flush_neighbors。
8.0的新变化
开始拆分buffer pool mutex（Percona贡献的patch）。
以往是有个全局的buffer pool mutext保护。
现在拆分到各个不同链表中，分为：
LRU_list_mutex for the LRU_list
zip_free mutex for the zip_free arrays
zip_hash mutex for the zip_hash hash and in_zip_hash flag
free_list_mutex for the free_list and withdraw list
flush_state_mutex for init_flush, n_flush, no_flush arrays
redo log system采用lock-free机制，redo log checkpoint不再是顺序的，可能是在某个日志的中间，给崩溃恢复带来了一定的复杂度（需要回溯日志）
新增选项innodb_fsync_threshold，在创建或truncate文件之类的操作时调用，表示每写到这么多个字节时，要fsync一次，避免对系统产生冲击。