MySQL体系结构与存储引擎

目录

MySQL体系结构

存储引擎对比

InnoDB体系结构

InnoDB存储结构

内存结构

各大刷新线程及其作用

InnoDB的三大特性

---

MySQL体系结构

分为两层：

1. MySQL Server层：连接层(通信、线程、密码认证)和SQL层(权限判断、查询缓存、解析器、预处理、缓存、执行计划)
2. 存储引擎层

对一条SQL的处理

权限判断，是否有库或表的访问权限，查询缓存，如果在Query Cache中，则直接返回客户端，否则用解析器判断语法正确性，预处理对无法解析的语义进行处理，生成最优的执行计划，通过存储引擎层访问数据。

Query Cache在5.6之后，默认关闭。

数据库压力测试工具：sysbench

存储引擎对比

InnoDB与MyISAM对比

区别	InnoDB	MyISAM
事务的支持	支持事务	不支持事务
锁粒度	行锁	表锁
并发性	高并发	低并发
结构和索引机制	数据和索引都存在.idb文件，并且都缓存在内存	数据.MYD 索引.MYI，只缓存索引文件
select count(*)	需要全表扫描，统计行数	只需要从计数器中读出行数

InnoDB体系结构

体系结构由磁盘文件、内存结构、线程三层组成。

InnoDB存储结构

表空间

定义表空间路径、初始大小(默认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

段

由N个区和32个零散的页组成。一个表空间有4个段。

区

有连续的页组成，即物理存储上连续分配的一段空间，每个区大小固定是1mb。

页

物理存储分配的最小单位是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。

行

页里面最少可以存两行数据。

内存结构

与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主要内存区域

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语句在内存中的临时排序。

各大刷新线程及其作用

主要有主线程master thread和四大IO线程。

主线程其中包含loop主循环，分为每1s操作和每10s操作，主要操作包括：

1. 刷新缓冲到磁盘
2. 刷新脏页到磁盘
3. 产生checkpoint
4. 删除无用的undo页

四大线程：

1. redo log thread 负责日志缓冲刷新到redo log文件中。
2. change buffer thread负责把插入缓冲的内存刷新到磁盘。
3. read/write thread是数据库的读写请求线程，默认是4个。

其他线程：

1. page cheaner thread负责脏页刷新的线程。默认是1个。

mysql> show variables like '%innodb_page%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_page_cleaners | 1     |
| innodb_page_size     | 16384 |
+----------------------+-------+

1. purge thread负责删除无用的undo页。DML语句会产出undo。
2. checkpoint线程作用是在redo log发生切换时或文件快写满时，触发脏页刷新到磁盘。
3. lock monitor thread锁监控线程。

内存刷新机制

主要有redo log buffer, binlog cache和data buffer的刷新机制。

Oracle和MySQL讲究日志先行策略。

redo log

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：使用超过一半触发刷新

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

重做日志和二进制日志区别：

1. redo log是循环使用的，最后一个文件满后，写第一个，因此说redo log发生切换。 binlog写满会写新的binlog文件。
2. redo log是异常宕机故障等，恢复使用。

binlog为了恢复数据，主从复制使用。

data buffer

这里表示将内存脏页数据刷到磁盘。

刷新条件：

1.通过参数控制：

innodb_max_dirty_pages_pct，表示脏页所占的百分比。

设置为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，出发的脏页刷新。

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ū)

转载请注明出处： 第3章 MySQL体系结构与存储引擎.md

作者：魔法海螺