Innodb引擎是多线程模型，不同后台线程, 负责处理不同业务

# 1. 前台线程（连接层）

show processlist ;

show full processlist;

select * from information_schema.processlist ;

 # 2. 后台线程（Server\Engine）

mysql> select * from performance_schema.threads\G

说明： 如何查询到连接线程和SQL线程关系

select * from information_schema.processlist ; ---> ID=10

select * from performance_schema.threads where processlist_id=10\G

1 master thread核心线程

负责

1. 控制刷新脏页到磁盘（CKPT）
2. 控制日志缓冲刷新到磁盘（log buffer ---> redo）
3. undo页回收
4. 合并插入缓冲(change buffer)
5. 控制IO刷新数量

说明：
innodb_io_capacity表示每秒刷新脏页的数量，默认为200。
innodb_max_dirty_pages_pct设置出发刷盘的脏页百分比，即当脏页占到缓冲区数据达到这个百分比时，就会刷新innodb_io_capacity个脏页到磁盘。
innodb_adaptive_flushing = ON（自适应地刷新），该值影响每秒刷新脏页的数量。

原来的刷新规则是：脏页在缓冲池所占的比例小于
innodb_max_dirty_pages_pct时，不刷新脏页；大于innodb_max_dirty_pages_pct时，刷新100个脏页。

随着innodb_adaptive_flushing参数的引入，InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。
buf_flush_get_desired_flush_rate通过判断产生重做日志（redolog）的速度来决定最合适的刷新脏页数量
因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。

2 IO thread

Innodb 使用 AIO（Async IO）来处理写IO请求，提高性能， 4个核心IO thread  read/write  insert buffer/log io thread

show variables like '%innodb_%io_threads';

show engine innodb status \G;

I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)

3 Purge Thread

事物提交后 需要purge  thread 来回收已经使用分配的undo页

show variables like '%innodb_purge_threads%';

4 page cleaner Thread

将刷脏页单独线程完成，较少 master thread 压力，提高性能

show variables like '%innodb_page_cleaners%';

5 其它线程： SQL线程/连接层的线程（前台）

其它

查询系统中所有业务表的存储引擎信息

查询系统中所有业务表的存储引擎信息
select table_schema, table_name , engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
巡检需求： 将业务表中所有非InnoDB查询出来 
mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine !='innodb';

1.	查询所有非InnoDB表 
mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb';
2.	备份所有非InnoDB表
 select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb'; 3. 修改存储引擎 
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb' into outfile '/tmp/a.sql'; mysql> source /tmp/a.sql

3. 修改存储引擎 
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb' into outfile '/tmp/a.sql'; mysql> source /tmp/a.sql

碎片情况

alter table world.xxx engine=innodb ALGORITHM=COPY; 
analyze table world.city;

转储表（推荐）
create table t1_bak like t1;
insert into t1_bak selewct * from t1; 
drop table t1 ; 
rename table t1_bak to t1;

mysqldump 导出 导入。 
或者工具 pt-os ghost 
注意：
1. 最好是空窗期做 
2. 准备double的存储空间 tmpdir 
3. 整理碎片只对 InnoDB 独立表空间方式有效