MySQL 存储引擎特性和线程模型
InnoDB 存储引擎核心特性
MVCC ： 多版本并发控制
聚簇索引 ： 用来组织存储数据和优化查询
支持事务 ： 数据安全保证
支持行级锁 ： 控制并发
外键
多缓冲区支持
自适应Hash索引： AHI
复制中支持高级特性。
备份恢复： 支持热备。
自动故障恢复：CR Crash Recovery
双写机制： Double Write
存储引擎管理
查询支持的、默认使用的存储引擎
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
查询某表的存储引擎
mysql> show create table test\G; *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) NOT NULL, `d` varchar(10) DEFAULT NULL, `e` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `idx` (`a`,`b`,`c`,`d`,`e`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
查看所有业务表的存储引擎信息
mysql> select table_name,table_schema,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); +----------------------+--------------+--------+ | TABLE_NAME | TABLE_SCHEMA | ENGINE | +----------------------+--------------+--------+ | current_dept_emp | employees | NULL | | departments | employees | InnoDB | | dept_emp | employees | InnoDB | | dept_emp_latest_date | employees | NULL | | dept_manager | employees | InnoDB | | employees | employees | InnoDB | | salaries | employees | InnoDB | | t2 | employees | InnoDB | | titles | employees | InnoDB | | student | school | InnoDB | | t100w | test | InnoDB | | test | test | InnoDB | | city | world | InnoDB | | country | world | InnoDB | | countryLanguage | world | InnoDB | | cours | world | InnoDB | | order_info | world | InnoDB | | score | world | InnoDB | | student | world | InnoDB | | t1 | world | InnoDB | | t3 | world | InnoDB | | t4 | world | InnoDB | | t4_log | world | InnoDB | | test_json | world | InnoDB | | vscc | world | NULL | +----------------------+--------------+--------+ 25 rows in set (0.00 sec)
建表时设置存储引擎
mysql> create table xxx (id int) engine=innodb charset=utf8mb4;
修改已有表的存储引擎
mysql> alter table xxx engine=myisam;
mysql> alter table world.xxx engine=innodb;
将所有非InnoDB存储引擎的表修改为InnoDB
1、查出所有非InnoDB引擎的表
mysql> select table_name,table_schema,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine != 'innodb';
2、备份非InnoDB的表
mysql> select concat('mysqldump -uroot -p123456 ',table_schema,' ',table_name,' > /backup/',table_schema,'_',table_name,'_',curdate(),'.sql') from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performancee_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';
修复表碎片
alter table world.xxx engine=innodb;
analyze table world.city;
InnoDB体系结构---线程详解
线程结构-Master Thread
MySQL的master线程在后台执行多种I/O相关的任务。
从buffer pool刷新脏页到磁盘（CKPT） 将日志缓冲刷新到磁盘（log buffer ---> redo） undo页回收 合并插入缓冲(change buffer)
innodb_io_capacity、innodb_adaptive_flushing
参数innodb_io_capacity表示每秒刷新脏页的数量，默认为200，单位页。此值不宜设置过大，设置多大会导致磁盘I/O的write/s过高。
innodb_max_dirty_pages_pct设置出发刷盘的脏页百分比，即当脏页占到缓冲区数据达到这个百分比时，就会刷新innodb_io_capacity个脏页到磁盘。
官方建议当触发checkpoint的时候，将innodb_flush_sync这个参数关闭
innodb_io_capacity_max这个默认是2000，官方建议从innodb_io_capacity的两倍值开始设置
mysql8.0中，脏页从buffer pool中刷新到磁盘是由cleaner线程在后台运行的。 innodb_max_dirty_pages_pct_lwm 定义了最大脏页刷新百分比的低水位值，默认是buffer pool的10%，当达到这个值时，开始刷新脏页。 参数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通过判断产生重做日志（redo log）的速度来决定最合适的刷新脏页数量。因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。
innodb_io_capacity导致的I/O不均衡
https://blog.csdn.net/kadwf123/article/details/82083020
I/O线程
在InnoDB存储引擎中大量使用Async IO来处理写IO请求,IO Thread的工作主要是负责这些IO请求的回调处理。
写线程和读线程分别由innodb_write_threads和innodb_read_threads参数控制，默认都为4。
Purge Thread
事务在提交之前，通过undolog(回滚日志)记录事务开始之前的状态，当事务被提交后，undolog便不再需要，因此需要Purge Thread线程来回收已经使用并分配的undo页。
可以在配置文件中添加innodb_purge_threads=1来开启独立的Purge Thread，等号后边控制该线程数量，默认为4个。
Page Cleaner Thread
InnoDB 1.2.X版本以上引入，脏页刷新，减轻master的工作，提高性能。 查看方法： 客户端版本： mysql -V server 版本： select @@version; engine 版本： SELECT * FROM information_schema.plugins; SELECT @@innodb_version;
posted on 2020-07-07 13:22 hopeless-dream 阅读(331) 评论(0) 编辑 收藏 举报
