mysql服务器多线程模型_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.00sec)

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_ci1 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不均衡

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;