MySql Innodb儲存引擎–架構和引擎介紹

NO IMAGE

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官網