目录
MySQL体系结构
分为两层:
- MySQL Server层:连接层(通信、线程、密码认证)和SQL层(权限判断、查询缓存、解析器、预处理、缓存、执行计划)
- 存储引擎层
对一条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操作,主要操作包括:
- 刷新缓冲到磁盘
- 刷新脏页到磁盘
- 产生checkpoint
- 删除无用的undo页
四大线程:
- redo log thread 负责日志缓冲刷新到redo log文件中。
- change buffer thread负责把插入缓冲的内存刷新到磁盘。
- 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。
- checkpoint线程作用是在redo log发生切换时或文件快写满时,触发脏页刷新到磁盘。
- 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
重做日志和二进制日志区别:
- redo log是循环使用的,最后一个文件满后,写第一个,因此说redo log发生切换。 binlog写满会写新的binlog文件。
- 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
作者:魔法海螺