MySQL体系结构与存储引擎

最新推荐文章于 2022-10-10 10:59:50 发布

坏蛋damn

最新推荐文章于 2022-10-10 10:59:50 发布

阅读量331

点赞数

分类专栏： MySQL

本文为博主原创文章，转载请注明出处。

本文链接：https://blog.csdn.net/qq_15738221/article/details/115362649

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

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

坏蛋damn

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL体系结构与存储引擎

MySQL体系结构分为两层：MySQL Server层：连接层(通信、线程、密码认证)和SQL层(权限判断、查询缓存、解析器、预处理、缓存、执行计划)存储引擎层对一条SQL的处理权限判断，是否有库或表的访问权限，查询缓存，如果在Query Cache中，则直接返回客户端，否则用解析器判断语法正确性，预处理对无法解析的语义进行处理，生成最优的执行计划，通过存储引擎层访问数据。Query Cache在5.6之后，默认关闭。数据库压力测试工具：sysbench存储引擎对比InnoDB
复制链接

扫一扫