文章目录
1 MySQL体系结构
由几部分组成:
-
连接池组件
-
管理服务和工具组件
-
SQL接口组件
-
查询分析器组件
-
优化器组件
-
缓冲(Cache组件)
-
插件式存储引擎
-
物理文件
也可以说分为两层:
- MySQL Server层:连接池层(通信、线程、密码认证)和SQL层(权限判断、查询缓存、解析器、预处理、缓存、执行计划)
- 存储引擎层
对一条SQL的处理
权限判断,是否有库或表的访问权限,查询缓存,如果在Query Cache中,则直接返回客户端,否则用解析器判断语法正确性,预处理对无法解析的语义进行处理,生成最优的执行计划,通过存储引擎层访问数据。
Query Cache在5.6之后,默认关闭。
数据库压力测试工具:sysbench
2 存储引擎对比
InnoDB与MyISAM对比
区别 | InnoDB | MyISAM |
---|---|---|
事务的支持 | 支持事务 | 不支持事务 |
锁粒度 | 行锁 | 表锁 |
并发性 | 高并发 | 低并发 |
结构和索引机制 | 数据和索引都存在.idb文件,并且都缓存在内存 | 数据.MYD 索引.MYI,只缓存索引文件 |
select count(*) | 需要全表扫描,统计行数 | 只需要从计数器中读出行数 |
3 InnoDB体系结构
体系结构由磁盘文件、内存结构、线程三层组成。
后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。
3.1 存储结构
3.1.1 表空间
定义表空间路径、初始大小(默认10m)、自动扩展策略(默认64m)
Innodb_data_file_path
初始大小建议调整为1G。
mysql> show variables like '%file_path%'
-> ;
+----------------------------+------------------------+
| Variable_name | Value |
+----------------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+------------------------+
当前版本默认使用的是独立表空间文件,即每个表就有自己的表空间文件,而不是存储在ibdata1中。例如建立test-lgl库,并新建了student表,在路径下多出test@002dlgl文件夹,其中student.ibd对应student表的独立表空间文件:
[root@localhost db]# pwd
/home/lgl/docker/mysql/mysql_3306/db
[root@localhost db]# ll
总用量 188480
-rw-r-----. 1 polkitd ssh_keys 56 9月 6 17:29 auto.cnf
-rw-------. 1 polkitd ssh_keys 1676 9月 6 17:29 ca-key.pem
-rw-r--r--. 1 polkitd ssh_keys 1112 9月 6 17:29 ca.pem
-rw-r--r--. 1 polkitd ssh_keys 1112 9月 6 17:29 client-cert.pem
-rw-------. 1 polkitd ssh_keys 1676 9月 6 17:29 client-key.pem
-rw-r-----. 1 polkitd ssh_keys 387 1月 2 12:32 ib_buffer_pool
-rw-r-----. 1 polkitd ssh_keys 79691776 1月 19 22:39 ibdata1
-rw-r-----. 1 polkitd ssh_keys 50331648 1月 19 22:39 ib_logfile0
-rw-r-----. 1 polkitd ssh_keys 50331648 9月 6 17:29 ib_logfile1
-rw-r-----. 1 polkitd ssh_keys 12582912 1月 19 22:38 ibtmp1
drwxr-x---. 2 polkitd ssh_keys 4096 9月 6 17:29 mysql
drwxr-x---. 2 polkitd ssh_keys 4096 9月 6 17:29 performance_schema
-rw-------. 1 polkitd ssh_keys 1680 9月 6 17:29 private_key.pem
-rw-r--r--. 1 polkitd ssh_keys 452 9月 6 17:29 public_key.pem
-rw-r--r--. 1 polkitd ssh_keys 1112 9月 6 17:29 server-cert.pem
-rw-------. 1 polkitd ssh_keys 1680 9月 6 17:29 server-key.pem
drwxr-x---. 2 polkitd ssh_keys 12288 9月 6 17:29 sys
drwxr-x---. 2 polkitd ssh_keys 4096 1月 19 22:39 test@002dlgl
[root@localhost db]# ll test@002dlgl/
总用量 112
-rw-r-----. 1 polkitd ssh_keys 67 1月 19 22:23 db.opt
-rw-r-----. 1 polkitd ssh_keys 8586 1月 19 22:39 student.frm
-rw-r-----. 1 polkitd ssh_keys 98304 1月 19 22:39 student.ibd
使用独立表空间可以实现表空间的转移,回收表空间也很方便,使用
alter table student engine=innodb;
mysql> use test-lgl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> alter table student engine=innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.1.2 段
由N个区和32个零散的页组成。一个表空间有4个段。
3.1.3 区
有连续的页组成,即物理存储上连续分配的一段空间,每个区大小固定是1mb。
3.1.4 页(重点)
物理存储分配的最小单位是page,即磁盘IO的最小单位是page,页默认大小是16k,表示一次IO读取16k的数据。一个区由64个页组成,所以区大小为16k*64=1mb。
Buffer状态及其链表结构
磁盘IO的最小单位是页page,对应到内存中就是buffer。
buffer有三种状态:
1.free buffer,从未被使用的,像一张白纸。
2.clean buffer,和磁盘page的数据一致。
3.dirty buffer,和磁盘page的数据不一致,即脏数据。flush list会使用lru把最近最少使用的dirty buffer串联起来,刷新到磁盘后,释放更多的free buffer。
3.1.5 行
页里面最少可以存两行数据。
3.2 内存结构
3.2.1 缓冲池
缓冲池简单来说就是一块内存区域,通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。
在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时,首先判断该页是否在缓冲池中。若在缓冲池中,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。
对于数据库中页的修改操作,则首先修改在缓冲池中的页,然后再以一定的频率刷新到磁盘上。通过Checkpoink的机制刷新到磁盘中。
具体来看,缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index>、InnoDB存储的锁信息(lockinfo)、数据字典信息(data dictionary)等。
下图很好地显示了InnoDB存储引擎中内存的结构情况。
Innodb允许有多个缓冲池实例。通过参数innodb_buffer_pool_instances,默认为1。多个缓存冲池实例,可以减少数据库内部的资源竞争,增加数据库的并发处理能力。
3.2.2 LRU List、Free List和Flush List
那么InnoDB存储引擎是怎么对这么大的内存区域进行管理的呢?
通常来说,数据库中的缓冲池是通过**LRU(LatestRecentUsed,最近最少使用)**算法来进行管理的。即最频繁使用的页在LRU列表的前端,而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时,将首先释放LRU列表中尾端的页。
InnoDB的存储引擎中,LRU列表中还加人了midpoint 位置。新读取到的页,虽然是最新访问的页,但并不是直接放人到LRU列表的首部,而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储弓|擎’下称为midpoint insertion strategy。在默认配置下,该位置在LRU列表长度的5/8处。保证热点数据不被刷出缓冲池。
LRU列表用来管理已经读取的页,但当数据库刚启动时,LRU列表是空的,即没有任何的页。这时页都存放在Free列表中。
当需要从缓冲池中分页时,首先从Free列表中查找是否有可用的空闲页,若有则将该页从Free列表中删除,放入到LRU列表中。否则,根据LRU算法,淘汰LRU列表末尾的页,将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时,称此时发生的操作为page made young,而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。
**在LRU列表中的页被修改后,称该页为脏页(dirty page),即缓冲池中的页和磁盘上的页的数据产生了不一致。**这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘,而Flush列表中的页即为脏页列表。需要注意的是,脏页既存在于LRU列表中,也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性,Flush列表用来管理将页刷新回磁盘,二者互不影响。
3.2.3 重做日志缓冲
InnoDB 存储引擎的内存区域除了有缓冲池外,还有重做日志缓冲(redo log buffer)。 InnoDB 存储引擎首先将重做日志信息先放人到这个缓冲区,然后按一定频率将其刷新到重做日志文件。
innodb_log_buffer_size参数控制。
redo log buffer的刷新机制下文说明。
3.2.4 SGA与PGA
与Oracle类似,分为SGA系统全局区和PGA程序缓存区。
可以通过show variables like '%buffer%'查看。
mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
24 rows in set (0.01 sec)
-
SGA主要内存区域
上述介绍的缓冲池和重做日志都属于SGA。
innodb_buffer_pool,缓冲池相关
用来缓存InnoDB表的数据、索引、数据字典等信息。
innodb_log_buffer,重做日志相关
事务在内存中的缓冲,即redo log buffer的大小。
key_buffer_size
MyISAM相关的索引文件
-
PGA内存区域
sort_buffer_size
join_buffer_size
read_buffer_size,MyISAM相关
read_rnd_buffer_size
sql语句在内存中的临时排序。
3.2.5 内存刷新机制
主要有redo log buffer, binlog cache和data buffer的刷新机制。
Oracle和MySQL讲究日志先行策略。
数据丢失的问题,当前事务数据库系统普遍都采用了Write Ahead Log策略,即当事务提交时,先写重做日志,再修改页。当由于发生宕机而导致数据丢失时,通过重做日志来:完成数据的恢复。这也是事务ACID中D (Durability 持久性)的要求。
3.2.5.1 redo log buffer的刷新机制
redo log,重做日志文件,不管事务是否提交都会记录下来。如数据库掉电,重做日志就能排上用场。
默认情况下有两个redo log文件,ib_logfile0和ib_logfile1。redo log写满发生切换(redo log是循环使用的)时,触发checkpoint,导致脏页刷新。
[root@localhost db]# pwd
/home/lgl/docker/mysql/mysql_3306/db
[root@localhost db]# ll
总用量 188480
-rw-r-----. 1 polkitd ssh_keys 56 9月 6 17:29 auto.cnf
-rw-------. 1 polkitd ssh_keys 1676 9月 6 17:29 ca-key.pem
-rw-r--r--. 1 polkitd ssh_keys 1112 9月 6 17:29 ca.pem
-rw-r--r--. 1 polkitd ssh_keys 1112 9月 6 17:29 client-cert.pem
-rw-------. 1 polkitd ssh_keys 1676 9月 6 17:29 client-key.pem
-rw-r-----. 1 polkitd ssh_keys 387 1月 2 12:32 ib_buffer_pool
-rw-r-----. 1 polkitd ssh_keys 79691776 1月 20 22:20 ibdata1
-rw-r-----. 1 polkitd ssh_keys 50331648 1月 20 22:20 ib_logfile0
-rw-r-----. 1 polkitd ssh_keys 50331648 9月 6 17:29 ib_logfile1
-rw-r-----. 1 polkitd ssh_keys 12582912 1月 20 22:20 ibtmp1
drwxr-x---. 2 polkitd ssh_keys 4096 9月 6 17:29 mysql
drwxr-x---. 2 polkitd ssh_keys 4096 9月 6 17:29 performance_schema
-rw-------. 1 polkitd ssh_keys 1680 9月 6 17:29 private_key.pem
-rw-r--r--. 1 polkitd ssh_keys 452 9月 6 17:29 public_key.pem
-rw-r--r--. 1 polkitd ssh_keys 1112 9月 6 17:29 server-cert.pem
-rw-------. 1 polkitd ssh_keys 1680 9月 6 17:29 server-key.pem
drwxr-x---. 2 polkitd ssh_keys 12288 9月 6 17:29 sys
drwxr-x---. 2 polkitd ssh_keys 4096 1月 19 22:49 test@002dlgl
刷新条件
1.通过参数控制:
innodb_flush_log_at_trx_commit
mysql> show variables like '%commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| autocommit | ON |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_order_commits | ON |
| innodb_api_bk_commit_interval | 5 |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
| slave_preserve_commit_order | OFF |
+-----------------------------------------+-------+
8 rows in set (0.00 sec)
0-redo log thread每隔1S将redo log buffer写入redo log文件。性能最好。
1-每次事务提交触发写redo log,并flush到磁盘,是最安全的模式,保证数据不会丢失。
2-每次事务提交触发写redo log,但不flush到磁盘。
2.master thread:每秒刷新
3.redo log buffer:使用超过一半触发刷新
3.2.5.1 binlog cache的刷新机制
是MySQL的二进制日志文件。用于备份恢复和主从复制。
刷新条件:
通过参数控制:
sync_binlog
mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.01 sec)
0-自行决定binlog cache刷新
1-每进行1次事务提交,强制写入磁盘
双一模式:保证数据更安全
innodb_flush_log_at_trx_commit=1
sync_binlog=1
重做日志和二进制日志区别:
- redo log是循环使用的,最后一个文件满后,写第一个,因此说redo log发生切换。
binlog写满会写新的binlog文件。- redo log是异常宕机故障等,恢复使用。
binlog为了恢复数据,主从复制使用。
3.2.5.2 data buffer缓冲池的刷新机制
这里表示将内存脏页数据刷到磁盘。
刷新条件:
1.通过参数控制:
innodb_max_dirty_pages_pct,表示脏页所占的百分比。默认75%。
设置为25%-50%,避免后期刷新时影响TPS。
mysql> show variables like '%dirty_page%';
+--------------------------------+-----------+
| Variable_name | Value |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
+--------------------------------+-----------+
2 rows in set (0.01 sec)
innodb_adaptive_flushing,自适应刷新,默认开启的。
mysql> show variables like '%flushing%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_flushing_avg_loops | 30 |
+------------------------------+-------+
3 rows in set (0.01 sec)
2.重做日志写满后出发的checkpoint,出发的脏页刷新。
3.3 各大刷新线程
主要有主线程master thread和四大IO线程。
主线程Master Thread是一个非常核心的后台线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新、合并插人缓冲(INSERT BUFFER)。
其中包含loop主循环,分为每1s操作和每10s操作,主要操作包括:
-
刷新缓冲到磁盘
-
刷新一定比例的脏页到磁盘
-
产生checkpoint
-
删除无用的undo页
四大线程:
- redo log thread 负责日志缓冲刷新到redo log文件中。
- change buffer thread负责把**插入缓冲(innodb新特性insert buffer)**的内存刷新到磁盘。
- read/write thread是数据库的读写请求线程,分别默认是4个。
其他线程:
- page cheaner thread负责脏页刷新的线程。默认是1个。给主线程起到减压作用。
mysql> show variables like '%innodb_page%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
+----------------------+-------+
-
purge thread负责删除无用的undo页。DML语句会产出undo。
事务被提交后,其所使用的undolog可能不再需要,因此需要PurgeThread来回收已经使用并分配的undo页。默认是4个。
-
checkpoint线程作用是无外乎是将缓冲池的脏页刷到磁盘。
-
lock monitor thread锁监控线程。
3.4 Checkpoint技术
因此Checkpoint (检查点)技术的目的是解决以下几个问题:
- 缩短数据库的恢复时间;
- 缓冲池不够用时,将脏页刷新到磁盘;
- 重做日志发生切换时,刷新脏页。
当数据库发生宕机时,数据库不需要重做所有的日志,因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。
此外,当缓冲池不够用时,根据LRU算法会溢出最近最少使用的页,若此页为脏页,那么需要强制执行Checkpoint,将脏页也就是页的新版本刷回磁盘。
4 InnoDB的三大特性
由插入缓存(change buffer)、**两次写(double write)和自适应哈希索引(adaptive hash index)**构成了InnoDB的三大特性。
插入缓存:提高DML操作的性能。innodb_change_buffer_max_size,建议调为50。
两次写:保证数据安全,防止磁盘页损坏带来的redo log也无法恢复的问题。
自适应索引:InnodDB注意到查询可以通过建立哈希索引得到优化,就会自动完成这件事。innodb_adaptive_hash_index默认是开启的。
参考
《MySQL王者晋级之路》 张甦(sū)
《MySQL技术内幕 InnoDB存储引擎》第2版 姜承尧
原创文章转载请注明出处
MySQL体系结构与存储引擎
作者:坏蛋damn