MySQL体系结构与存储引擎

魔法海螺 2021-01-21 00:36:23
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 cachedata 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

作者:魔法海螺

版权声明
本文为[魔法海螺]所创,转载请带上原文链接,感谢
https://my.oschina.net/feistel/blog/4916974

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云