MySql Innodb存储引擎--架构和引擎介绍

Mysql架构图

1 Connectors指的是不同语言中与SQL的交互

 

2 Management Serveices & Utilities： 系统管理和控制工具

 

3 Connection Pool: 连接池。

管理缓冲用户连接，线程处理等需要缓存的需求

 

4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

 

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

主要功能：

a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 

b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

 

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。

用一个例子就可以理解： select uid,name from user where gender = 1;

这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤

将这两个查询条件联接起来生成最终查询结果

 

7 Cache和Buffer： 查询缓存。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

 

8 Engine ：存储引擎。

存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）

现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB

默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。

InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 

Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

 

 

 

InnoDB架构图

 

后台线程简介：

1、Master ThreadMaster Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERT BUFFER）、回滚页（UNDO PAGE）的回收等。

2、IO Thread在InnoDB存储引擎中大量使用了AIO（Async IO）来处理IO请求，这样可以极大提高数据库的性能。而IO Thread（insert buffer thread、log thread、read thread、write thread）的工作主要是负责这些IO请求的回调（call back）处理

3、缓冲池会有多个，默认为8个，多个池可以增加数据库的并发处理能力

4、缓冲池使用的是LRU算法，经过了修改，新读取到的页不是直接放到LRU首部，而是放在3/8处的位置，这个值可以通过

innodb_old_blocks_pct   设置，这个值默认是37，表示新读取的值放到LRU37%的位置

因为某些操作(比如遍历)只是一次性的，如果每次都把这种页放到LRU首部会影响正常的热点页

当读取到这个页经过了若干时间后会被放到LRU首部，这个时间又参数

innodb_old_blocks_time 来控制的

 

 

重做日志

Mysql默认情况下会有两个文件：ib_logfile0和ib_logfile1，这两个文件就是重做日志文件，或者事务日志。

重做日志的目的：万一实例或者介质失败，重做日志文件就能派上用场。

每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。

影响重做日志的参数：

Innodb_log_file_size、innodb_log_files_in_group、innodb_log_group_home_dir影响着重做日志文件的属性。

undo log是一种日志，日志中记录对于数据库的反向操作。

如果把数据库的内容当做一种状态机，那么数据的写操作就是修改状态机的命令，而undo 就对应修改状态机的反向命令。

所以理论上每一个对于状态机修改的命令都会产生对应一条相当的undo log,以便事务回滚的时候，能够把状态机修改到事务原来的样子。

和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。 

 

 

Checkpoint机制

1.主线程定期刷新一些页到磁盘

2.LRU队列的空闲页不够，参数 innodb_lru_scan_depth控制LRU列表中可用页的数量

3.重做日志不够了

4.脏也太多了，参数innodb_max_dirty_pages_pct控制脏也比列，值为75%

 

 

insert buffer

插入缓冲,并不是缓存的一部分,而是物理页,对于非聚集索引的插入或更新操作,不是每一次直接插入索引页.而是先判断插入的非聚集索引页是否在缓冲池中.如果在,则直接插入,如果不再,则先放入一个插入缓冲区中.然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作.使用条件:非聚集索引,非唯一

因为主键肯定都是顺序的，唯一索引插入的时候要先检查一下(肯定有一个随机IO)，对于非唯一所以只要插入就行了，而这个插入可能会产生随机IO，所以insert buffer的原理就是将多次随机IO合并，用顺序IO替代随机IO

Mysql代码  

1. -- 看看合并操作节省了多少IO请求,(1034310+3)/113909=9.08  
2. -------------------------------------  
3. INSERT BUFFER AND ADAPTIVE HASH INDEX  
4. -------------------------------------  
5. Ibuf: size 1, free list len 134, seg size 136, 113909 merges  
6. merged operations:  
7.  insert 3, delete mark 2319764, delete 1034310  
8. discarded operations:  
9.  insert 0, delete mark 0, delete 0  
10. Hash table size 288996893, node heap has 304687 buffer(s)  
11. 1923.58 hash searches/s, 1806.60 non-hash searches/s  

对于SSD这种优化随机IO的方式可能就不需要了

mysql高版本又加了一个增强的change buffer，支持delete，update等操作原理跟insert buffer一样 

 

 

double write

 InnoDB 的Page Size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。

很多DBA 会想到系统恢复后，MySQL 可以根据redolog 进行恢复，而mysql在恢复的过程中是检查page的checksum，checksum就是pgae的最后事务号，发生partial page write 问题时，page已经损坏，找不到该page中的事务号，就无法恢复。

double write架构

Innodb_dblwr_pages_written    写入多少次double write

Innodb_dblwr_writes                   实际写入的次数

这两个参数通过 SHOW STATUS LIKE 'innodb%';    查看

如果需要更快的性能，或者文件系统本身就提供部分写失效的问题，可以将双写关闭

通过参数 skip_innodb_doublewrite  设置

 

 

自适应hash

自适应哈希索引采用之前讨论的哈希表的方式实现，不同的是，这仅是数据库自身创建并使用的，DBA本身并不能对其进行干预。自适应哈希索引近哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如SELECT * FROM TABLE WHERE index_col='xxx'但是对于范围查找就无能为力。通过SHOW ENGINE INNODB STATUS 可以看到当前自适应哈希索引的使用情况

Mysql代码  

1. -- 这里显示了每秒使用自适应hash的次数，以及没有用到hash的次数  
2. Hash table size 4425293, node heap has 1337 buffer(s)  
3. 174.24 hash searches/s, 169.49 non-hash searches/s  

参数   innodb_adaptive_hash_index 禁用或启动此特性，默认是开启

Innodb还提供了刷新临近页面的功能，这是为了优化传统机械盘的，如果是SSD就不需要了

通过参数 innodb_flush_neighbors 开启或关闭这个功能

 

 

每个池前面有标示符

--- BUFFER POOL 0   显示

Mysql代码  

1. ---BUFFER POOL 0  
2. Buffer pool size   65528     --当前buffer一共有多少页(一页16K)  
3. Free buffers       48335     --当前缓冲池空闲页  
4. Database pages     16892     --当前缓冲池的LRU页数量  
5. Old database pages 6255  
6. Modified db pages  654  
7. Pending reads      0  
8. Pending writes: LRU 0, flush list 0, single page 0  
9. Pages made young 189, not young 0  
10. 0.00 youngs/s, 0.00 non-youngs/s  
11. Pages read 10029, created 6863, written 172659  
12. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s  
13. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000  
14. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s  

Pages made young 是从old变到首部的页数量

not young是因为innodb_old_blocks_time的限制没有变到首部的数量

Buffer pool hit rate 是命中率

youngs/s 和 non-youngs/s  是每秒变到首部和没有变到首部的页数量

LRU len:1539 ，  unzip_LRU len : 156    LRU长度是一共有多少页，unzip表示未压缩的页

information_schema库中

INNODB_BUFFER_POOL_STATUS记录了每个缓冲池的状态

INNODB_BUFFER_PAGE_LRU unzip LRU状态

 

 

 

 

 

 

Mysql引擎相关属性

Mysql代码  

1. mysql> show engine innodb status\G  
2. *************************** 1. row ***************************  
3.   Type: InnoDB  
4.   Name:  
5. Status:  
6. =====================================  
7. 2017-03-23 10:51:31 0x19f0 INNODB MONITOR OUTPUT  
8. =====================================  
9. Per second averages calculated from the last 4 seconds  
10. -----------------  
11. BACKGROUND THREAD  
12. -----------------  
13. srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 5996 srv_idle  
14. srv_master_thread log flush and writes: 5997  
15. ----------  
16. SEMAPHORES  
17. ----------  
18. OS WAIT ARRAY INFO: reservation count 34  
19. OS WAIT ARRAY INFO: signal count 20  
20. RW-shared spins 0, rounds 20, OS waits 3  
21. RW-excl spins 0, rounds 174, OS waits 1  
22. RW-sx spins 0, rounds 0, OS waits 0  
23. Spin rounds per wait: 20.00 RW-shared, 174.00 RW-excl, 0.00 RW-sx  
24. ------------  
25. TRANSACTIONS  
26. ------------  
27. Trx id counter 65283  
28. Purge done for trx's n:o < 58732 undo n:o < 0 state: running but idle  
29. History list length 277  
30. LIST OF TRANSACTIONS FOR EACH SESSION:  
31. ---TRANSACTION 281475142321968, not started  
32. 0 lock struct(s), heap size 1136, 0 row lock(s)  
33. --------  
34. FILE I/O  
35. --------  
36. I/O thread 0 state: wait Windows aio (insert buffer thread)  
37. I/O thread 1 state: wait Windows aio (log thread)  
38. I/O thread 2 state: wait Windows aio (read thread)  
39. I/O thread 3 state: wait Windows aio (read thread)  
40. I/O thread 4 state: wait Windows aio (read thread)  
41. I/O thread 5 state: wait Windows aio (read thread)  
42. I/O thread 6 state: wait Windows aio (write thread)  
43. I/O thread 7 state: wait Windows aio (write thread)  
44. I/O thread 8 state: wait Windows aio (write thread)  
45. I/O thread 9 state: wait Windows aio (write thread)  
46. Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,  
47.  ibuf aio reads:, log i/o's:, sync i/o's:  
48. Pending flushes (fsync) log: 0; buffer pool: 0  
49. 419 OS file reads, 53 OS file writes, 7 OS fsyncs  
50. 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s  
51. -------------------------------------  
52. INSERT BUFFER AND ADAPTIVE HASH INDEX  
53. -------------------------------------  
54. Ibuf: size 1, free list len 0, seg size 2, 0 merges  
55. merged operations:  
56.  insert 0, delete mark 0, delete 0  
57. discarded operations:  
58.  insert 0, delete mark 0, delete 0  
59. Hash table size 2267, node heap has 0 buffer(s)  
60. Hash table size 2267, node heap has 0 buffer(s)  
61. Hash table size 2267, node heap has 0 buffer(s)  
62. Hash table size 2267, node heap has 0 buffer(s)  
63. Hash table size 2267, node heap has 0 buffer(s)  
64. Hash table size 2267, node heap has 0 buffer(s)  
65. Hash table size 2267, node heap has 0 buffer(s)  
66. Hash table size 2267, node heap has 0 buffer(s)  
67. 0.00 hash searches/s, 0.00 non-hash searches/s  
68. ---  
69. LOG  
70. ---  
71. Log sequence number 12560144  
72. Log flushed up to   12560144  
73. Pages flushed up to 12560144  
74. Last checkpoint at  12560135  
75. 0 pending log flushes, 0 pending chkp writes  
76. 10 log i/o's done, 0.00 log i/o's/second  
77. ----------------------  
78. BUFFER POOL AND MEMORY  
79. ----------------------  
80. Total large memory allocated 8585216  
81. Dictionary memory allocated 1239320  
82. Buffer pool size   512  
83. Free buffers       254  
84. Database pages     258  
85. Old database pages 0  
86. Modified db pages  0  
87. Pending reads      0  
88. Pending writes: LRU 0, flush list 0, single page 0  
89. Pages made young 0, not young 0  
90. 0.00 youngs/s, 0.00 non-youngs/s  
91. Pages read 382, created 34, written 36  
92. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s  
93. No buffer pool page gets since the last printout  
94. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s  
95.   
96. LRU len: 258, unzip_LRU len: 0  
97. I/O sum[0]:cur[0], unzip sum[0]:cur[0]  
98. --------------  
99. ROW OPERATIONS  
100. --------------  
101. 0 queries inside InnoDB, 0 queries in queue  
102. 0 read views open inside InnoDB  
103. Process ID=2056, Main thread ID=2376, state: sleeping  
104. Number of rows inserted 0, updated 0, deleted 0, read 8  
105. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s  
106. ----------------------------  
107. END OF INNODB MONITOR OUTPUT  
108. ============================  
109.   
110. 1 row in set (0.00 sec)  

 

 

Innodb相关的参数

Mysql代码  

1. innodb_adaptive_flushing    ON  
2. innodb_adaptive_flushing_lwm    10  
3. innodb_adaptive_hash_index  ON  
4. innodb_adaptive_hash_index_parts    8  
5. innodb_adaptive_max_sleep_delay 150000  
6. innodb_api_bk_commit_interval   5  
7. innodb_api_disable_rowlock  OFF  
8. innodb_api_enable_binlog    OFF  
9. innodb_api_enable_mdl   OFF  
10. innodb_api_trx_level    0  
11. innodb_autoextend_increment 64  
12. innodb_autoinc_lock_mode    1  
13. innodb_buffer_pool_chunk_size   134217728  
14. innodb_buffer_pool_dump_at_shutdown ON  
15. innodb_buffer_pool_dump_now OFF  
16. innodb_buffer_pool_dump_pct 25  
17. innodb_buffer_pool_filename ib_buffer_pool  
18. innodb_buffer_pool_instances    8  
19. innodb_buffer_pool_load_abort   OFF  
20. innodb_buffer_pool_load_at_startup  ON  
21. innodb_buffer_pool_load_now OFF  
22. innodb_buffer_pool_size 8589934592  
23. innodb_change_buffer_max_size   25  
24. innodb_change_buffering all  
25. innodb_checksum_algorithm   crc32  
26. innodb_checksums    ON  
27. innodb_cmp_per_index_enabled    OFF  
28. innodb_commit_concurrency   0  
29. innodb_compression_failure_threshold_pct    5  
30. innodb_compression_level    6  
31. innodb_compression_pad_pct_max  50  
32. innodb_concurrency_tickets  5000  
33. innodb_data_file_path   ibdata1:12M:autoextend  
34. innodb_data_home_dir      
35. innodb_deadlock_detect  ON  
36. innodb_default_row_format   dynamic  
37. innodb_disable_sort_file_cache  OFF  
38. innodb_doublewrite  ON  
39. innodb_fast_shutdown    1  
40. innodb_file_format  Barracuda  
41. innodb_file_format_check    ON  
42. innodb_file_format_max  Barracuda  
43. innodb_file_per_table   ON  
44. innodb_fill_factor  100  
45. innodb_flush_log_at_timeout 1  
46. innodb_flush_log_at_trx_commit  2  
47. innodb_flush_method   
48. innodb_flush_neighbors  1  
49. innodb_flush_sync   ON  
50. innodb_flushing_avg_loops   30  
51. innodb_force_load_corrupted OFF  
52. innodb_force_recovery   0  
53. innodb_ft_aux_table   
54. innodb_ft_cache_size    8000000  
55. innodb_ft_enable_diag_print OFF  
56. innodb_ft_enable_stopword   ON  
57. innodb_ft_max_token_size    84  
58. innodb_ft_min_token_size    3  
59. innodb_ft_num_word_optimize 2000  
60. innodb_ft_result_cache_limit    2000000000  
61. innodb_ft_server_stopword_table   
62. innodb_ft_sort_pll_degree   2  
63. innodb_ft_total_cache_size  640000000  
64. innodb_ft_user_stopword_table     
65. innodb_io_capacity  200  
66. innodb_io_capacity_max  2000  
67. innodb_large_prefix ON  
68. innodb_lock_wait_timeout    50  
69. innodb_locks_unsafe_for_binlog  OFF  
70. innodb_log_buffer_size  8388608  
71. innodb_log_checksums    ON  
72. innodb_log_compressed_pages ON  
73. innodb_log_file_size    50331648  
74. innodb_log_files_in_group   2  
75. innodb_log_group_home_dir   ./  
76. innodb_log_write_ahead_size 8192  
77. innodb_lru_scan_depth   1024  
78. innodb_max_dirty_pages_pct  75.000000  
79. innodb_max_dirty_pages_pct_lwm  0.000000  
80. innodb_max_purge_lag    0  
81. innodb_max_purge_lag_delay  0  
82. innodb_max_undo_log_size    1073741824  
83. innodb_monitor_disable    
84. innodb_monitor_enable     
85. innodb_monitor_reset      
86. innodb_monitor_reset_all      
87. innodb_old_blocks_pct   37  
88. innodb_old_blocks_time  1000  
89. innodb_online_alter_log_max_size    134217728  
90. innodb_open_files   2000  
91. innodb_optimize_fulltext_only   OFF  
92. innodb_page_cleaners    4  
93. innodb_page_size    16384  
94. innodb_print_all_deadlocks  OFF  
95. innodb_purge_batch_size 300  
96. innodb_purge_rseg_truncate_frequency    128  
97. innodb_purge_threads    4  
98. innodb_random_read_ahead    OFF  
99. innodb_read_ahead_threshold 56  
100. innodb_read_io_threads  4  
101. innodb_read_only    OFF  
102. innodb_replication_delay    0  
103. innodb_rollback_on_timeout  OFF  
104. innodb_rollback_segments    128  
105. innodb_sort_buffer_size 1048576  
106. innodb_spin_wait_delay  6  
107. innodb_stats_auto_recalc    ON  
108. innodb_stats_method nulls_equal  
109. innodb_stats_on_metadata    OFF  
110. innodb_stats_persistent ON  
111. innodb_stats_persistent_sample_pages    20  
112. innodb_stats_sample_pages   8  
113. innodb_stats_transient_sample_pages 8  
114. innodb_status_output    OFF  
115. innodb_status_output_locks  OFF  
116. innodb_strict_mode  ON  
117. innodb_support_xa   ON  
118. innodb_sync_array_size  1  
119. innodb_sync_spin_loops  30  
120. innodb_table_locks  ON  
121. innodb_temp_data_file_path  ibtmp1:12M:autoextend  
122. innodb_thread_concurrency   8  
123. innodb_thread_sleep_delay   0  
124. innodb_tmpdir     
125. innodb_undo_directory   ./  
126. innodb_undo_log_truncate    OFF  
127. innodb_undo_logs    128  
128. innodb_undo_tablespaces 0  
129. innodb_use_native_aio   OFF  
130. innodb_version  5.7.15  
131. innodb_write_io_threads 4  

 

一些参数

Mysql代码  

1. -- 缓冲区实例个数  
2. SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'  
3.   
4. -- 缓冲区大小  
5. SHOW VARIABLES LIKE 'innodb_buffer_pool_size'  

  

 

 

 

 

 

参考

Mysql官网文档

Mysql架构介绍

Mysql重做日志

Mysql的Checkpoint机制

Insert buffer插入缓冲区

Insert buffer漫谈

innodb两次写实现解析

我理解的MySql double write

MySql数据库InnoDB存储引擎Log漫游

Mysql双向同步复制

Percona和MariaDB

聚集索引和非聚集索引

淘宝mysql官网