為什麼99%的程式設計師都做不好SQL優化?
- 連線層
最上層是一些客戶端和連結服務,包含本地sock 通訊和大多數基於客戶端/服務端工具實現的類似於 TCP/IP的通訊。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒 池的概念,為通過認證安全接入的客戶端提供執行緒。同樣在該層上可以實現基於SSL的安全連結。服務 器也會為安全接入的每個客戶端驗證它所具有的操作許可權。
- 服務層
第二層架構主要完成大多數的核心服務功能,如SQL介面,並完成快取的查詢,SQL的分析和優化,部 分內建函式的執行。所有跨儲存引擎的功能也在這一層實現,如 過程、函式等。在該層,伺服器會解 析查詢並建立相應的內部解析樹,並對其完成相應的優化如確定表的查詢的順序,是否利用索引等, 最後生成相應的執行操作。如果是select語句,伺服器還會查詢內部的快取,如果快取空間足夠大, 這樣在解決大量讀操作的環境中能夠很好的提升系統的效能。
- 引擎層
儲存引擎層, 儲存引擎真正的負責了MySQL中資料的儲存和提取,伺服器通過API和儲存引擎進行通 信。不同的儲存引擎具有不同的功能,這樣我們可以根據自己的需要,來選取合適的儲存引擎。資料庫 中的索引是在儲存引擎層實現的。
- 儲存層
資料儲存層, 主要是將資料(如: redolog、undolog、資料、索引、二進位制日誌、錯誤日誌、查詢 日誌、慢查詢日誌等)儲存在檔案系統之上,並完成與儲存引擎的互動。
和其他資料庫相比,MySQL有點與眾不同,它的架構可以在多種不同場景中應用併發揮良好作用。主要 體現在儲存引擎上,外掛式的儲存引擎架構,將查詢處理和其他的系統任務以及資料的儲存提取分離。 這種架構可以根據業務的需求和實際需要選擇合適的儲存引擎。
儲存引擎介紹
大家可能沒有聽說過儲存引擎,但是一定聽過引擎這個詞,引擎就是發動機,是一個機器的核心元件。 比如,對於艦載機、直升機、火箭來說,他們都有各自的引擎,是他們最為核心的元件。而我們在選擇 引擎的時候,需要在合適的場景,選擇合適的儲存引擎,就像在直升機上,我們不能選擇艦載機的引擎 一樣。 而對於儲存引擎,也是一樣,他是mysql資料庫的核心,我們也需要在合適的場景選擇合適的儲存引 擎。接下來就來介紹一下儲存引擎。 儲存引擎就是儲存資料、建立索引、更新/查詢資料等技術的實現方式 。儲存引擎是基於表的,而不是 基於庫的,所以儲存引擎也可被稱為表型別。我們可以在建立表的時候,來指定選擇的儲存引擎,如果 沒有指定將自動選擇預設的儲存引擎。
- 建表時指定儲存引擎
CREATE TABLE 表名(
欄位1 欄位1型別 [ COMMENT 欄位1註釋 ] ,
......
欄位n 欄位n型別 [COMMENT 欄位n註釋 ]
) ENGINE = INNODB [ COMMENT 表註釋 ] ;
- 查詢當前資料庫支援的儲存引擎
SHOW ENGINES;
- 建立表 my_myisam , 並指定MyISAM儲存引擎
CREATE TABLE my_myisam(
`id` INT,
`name` VARCHAR(10)
)ENGINE = MYISAM;
- 建立表 my_memory , 指定Memory儲存引擎
CREATE TABLE my_memory(
`id` INT,
`name` VARCHAR(10)
)ENGINE = MEMORY;
儲存引擎特點
上面我們介紹了什麼是儲存引擎,以及如何在建表時如何指定儲存引擎,接下來我們就來介紹下來上面 重點提到的三種儲存引擎 InnoDB、MyISAM、Memory的特點。
InnoDB
- 介紹
InnoDB是一種兼顧高可靠性和高效能的通用儲存引擎,在 MySQL 5.5 之後,InnoDB是預設的 MySQL 儲存引擎。
- 特點
- DML操作遵循ACID模型,支援事務;
- 行級鎖,提高併發訪問效能;
- 支援外來鍵FOREIGN KEY約束,保證資料的完整性和正確性;
- 檔案
xxx.ibd:xxx代表的是表名,innoDB引擎的每張表都會對應這樣一個表空間檔案,儲存該表的表結 構(frm-早期的 、sdi-新版的)、資料和索引。
引數:innodb_file_per_table
show variables like 'innodb_file_per_table';
Variable_name | Value |
---|---|
innodb_file_per_table | ON |
如果該引數開啟,代表對於InnoDB引擎的表,每一張表都對應一個ibd檔案。 我們直接開啟MySQL的 資料存放目錄: D:\DevelopTools\mysql-5.7.19-winx64\data
, 這個目錄下有很多檔案 夾,不同的資料夾代表不同的資料庫,我們直接開啟frx_db02資料夾。
可以看到裡面有很多的ibd檔案,每一個ibd檔案就對應一張表,比如:我們有一張表 account,就有這樣的一個account.ibd檔案,而在這個ibd檔案中不僅存放表結構、資料,還會存放該表對應的索引資訊。 而該檔案是基於二進位制儲存的,不能直接基於記事本開啟,我們可以使用mysql提供的一個指令 ibd2sdi ,通過該指令就可以從ibd檔案中提取sdi資訊,而sdi資料字典資訊中就包含該表的表結構。
ibd2sdi account.ibd
針對MySQL8有效
- 邏輯儲存結構
- 表空間 : InnoDB儲存引擎邏輯結構的最高層,ibd檔案其實就是表空間檔案,在表空間中可以包含多個Segment段。
- 段 : 表空間是由各個段組成的, 常見的段有資料段、索引段、回滾段等。InnoDB中對於段的管理,都是引擎自身完成,不需要人為對其控制,一個段中包含多個區。
- 區 : 區是表空間的單元結構,每個區的大小為1M。 預設情況下, InnoDB儲存引擎頁大小為16K, 即一個區中一共有64個連續的頁。
- 頁 : 頁是組成區的最小單元,頁也是InnoDB 儲存引擎磁碟管理的最小單元,每個頁的大小預設為 16KB。為了保證頁的連續性,InnoDB 儲存引擎每次從磁碟申請 4-5 個區。
- 行 : InnoDB 儲存引擎是面向行的,也就是說資料是按行進行存放的,在每一行中除了定義表時所指定的欄位以外,還包含兩個隱藏欄位(後面會詳細介紹)。
MyISAM
- 介紹
MyISAM是MySQL早期的預設儲存引擎。
- 特點
訪問速度快
不支援事務,不支援外來鍵
支援表鎖,不支援行鎖
- 檔案
xxx.sdi:儲存表結構資訊
xxx.MYD: 儲存資料
xxx.MYI: 儲存索引
Memory
- 介紹
Memory引擎的表資料時儲存在記憶體中的,由於受到硬體問題、或斷電問題的影響,只能將這些表作為 臨時表或快取使用。
- 特點
記憶體存放
hash索引(預設)
- 檔案
xxx.sdi:儲存表結構資訊
區別及特點
特點 | InnoDB | MyISAM | Memory |
---|---|---|---|
儲存限制 | 64TB | 有 | 有 |
事務安全 | 支援 | - | - |
鎖機制 | 行鎖 | 表鎖 | 表鎖 |
B+tree索引 | 支援 | 支援 | 支援 |
Hash索引 | - | - | 支援 |
全文索引 | 支援(5.6版本之後) | 支援 | - |
空間使用 | 高 | 底 | N/A |
記憶體使用 | 高 | 底 | 中等 |
批量插入速度 | 低 | 高 | 高 |
支援外來鍵 | 支援 | - | - |
儲存引擎選擇
在選擇儲存引擎時,應該根據應用系統的特點選擇合適的儲存引擎。對於複雜的應用系統,還可以根據 實際情況選擇多種儲存引擎進行組合。
- InnoDB: 是Mysql的預設儲存引擎,支援事務、外來鍵。如果應用對事務的完整性有比較高的要 求,在併發條件下要求資料的一致性,資料操作除了插入和查詢之外,還包含很多的更新、刪除操 作,那麼InnoDB儲存引擎是比較合適的選擇。
- MyISAM : 如果應用是以讀操作和插入操作為主,只有很少的更新和刪除操作,並且對事務的完 整性、併發性要求不是很高,那麼選擇這個儲存引擎是非常合適的。
- MEMORY:將所有資料儲存在記憶體中,訪問速度快,通常用於臨時表及快取。MEMORY的缺陷就是 對錶的大小有限制,太大的表無法快取在記憶體中,而且無法保障資料的安全性。
MySQL InnoDB引擎
邏輯儲存引擎
InnoDB的邏輯儲存結構如下圖所示:
- 表空間
表空間是InnoDB儲存引擎邏輯結構的最高層, 如果使用者啟用了引數 innodb_file_per_table(在8.0版本中預設開啟) ,則每張表都會有一個表空間(xxx.ibd),一個mysql例項可以對應多個表空間,用於儲存記錄、索引等資料。
- 段
段,分為資料段(Leaf node segment)、索引段(Non-leaf node segment)、回滾段(Rollback segment),InnoDB是索引組織表,資料段就是B+樹的葉子節點, 索引段即為B+樹的非葉子節點。段用來管理多個Extent(區)。
- 區
區,表空間的單元結構,每個區的大小為1M。 預設情況下, InnoDB儲存引擎頁大小為16K, 即一個區中一共有64個連續的頁。
- 頁
頁,是InnoDB 儲存引擎磁碟管理的最小單元,每個頁的大小預設為 16KB。為了保證頁的連續性,InnoDB 儲存引擎每次從磁碟申請 4-5 個區。
- 行
行,InnoDB 儲存引擎資料是按行進行存放的。
在行中,預設有兩個隱藏欄位:
- Trx_id:每次對某條記錄進行改動時,都會把對應的事務id賦值給trx_id隱藏列。
- Roll_pointer:每次對某條引記錄進行改動時,都會把舊的版本寫入到undo日誌中,然後這個隱藏列就相當於一個指標,可以通過它來找到該記錄修改前的資訊。
架構
概述
MySQL5.5 版本開始,預設使用InnoDB儲存引擎,它擅長事務處理,具有崩潰恢復特性,在日常開發中使用非常廣泛。下面是InnoDB架構圖,左側為記憶體結構,右側為磁碟結構。
記憶體架構
在左側的記憶體結構中,主要分為這麼四大塊兒: Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 下來介紹一下這四個部分。
- Buffer Pool
InnoDB儲存引擎基於磁碟檔案儲存,訪問物理硬碟和在記憶體中進行訪問,速度相差很大,為了儘可能彌補這兩者之間的I/O效率的差值,就需要把經常使用的資料載入到緩衝池中,避免每次訪問都進行磁碟I/O。
在InnoDB的緩衝池中不僅快取了索引頁和資料頁,還包含了undo頁、插入快取、自適應雜湊索引以及InnoDB的鎖資訊等等。
緩衝池 Buffer Pool,是主記憶體中的一個區域,裡面可以快取磁碟上經常操作的真實資料,在執行增 刪改查操作時,先操作緩衝池中的資料(若緩衝池沒有資料,則從磁碟載入並快取),然後再以一定頻 率重新整理到磁碟,從而減少磁碟IO,加快處理速度。
緩衝池以Page頁為單位,底層採用連結串列資料結構管理Page。根據狀態,將Page分為三種類型:
- free page:空閒page,未被使用。
- clean page:被使用page,資料沒有被修改過。
- dirty page:髒頁,被使用page,資料被修改過,也中資料與磁碟的資料產生了不一致。
在專用伺服器上,通常將多達80%的實體記憶體分配給緩衝池 。引數設定: show variables like 'innodb_buffer_pool_size';
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
- Change Buffer
Change Buffer,更改緩衝區(針對於非唯一二級索引頁),在執行DML語句時,如果這些資料Page沒有在Buffer Pool中,不會直接操作磁碟,而會將資料變更存在更改緩衝區 Change Buffer中,在未來資料被讀取時,再將資料合併恢復到Buffer Pool中,再將合併後的資料重新整理到磁碟中。
Change Buffer的意義是什麼呢?
先來看一幅圖,這個是二級索引的結構圖:
與聚集索引不同,二級索引通常是非唯一的,並且以相對隨機的順序插入二級索引。同樣,刪除和更新可能會影響索引樹中不相鄰的二級索引頁,如果每一次都操作磁碟,會造成大量的磁碟IO。有了ChangeBuffer之後,我們可以在緩衝池中進行合併處理,減少磁碟IO。
- Adaptive Hash Index
自適應hash索引,用於優化對Buffer Pool資料的查詢。MySQL的innoDB引擎中雖然沒有直接支援hash索引,但是給我們提供了一個功能就是這個自適應hash索引。因為前面我們講到過,hash索引在進行等值匹配時,一般效能是要高於B+樹的,因為hash索引一般只需要一次IO即可,而B+樹,可能需要幾次匹配,所以hash索引的效率要高,但是hash索引又不適合做範圍查詢、模糊匹配等。
InnoDB儲存引擎會監控對錶上各索引頁的查詢,如果觀察到在特定的條件下hash索引可以提升速度,則建立hash索引,稱之為自適應hash索引。
自適應雜湊索引,無需人工干預,是系統根據情況自動完成。
引數: adaptive_hash_index
- Log Buffer
Log Buffer:日誌緩衝區,用來儲存要寫入到磁碟中的log日誌資料(redo log 、undo log),預設大小為 16MB,日誌緩衝區的日誌會定期重新整理到磁碟中。如果需要更新、插入或刪除許多行的事務,增加日誌緩衝區的大小可以節省磁碟 I/O。
引數:
innodb_log_buffer_size:緩衝區大小
innodb_flush_log_at_trx_commit:日誌重新整理到磁碟時機,取值主要包含以下三個:
1
:日誌在每次事務提交時寫入並重新整理到磁碟,預設值。
0
:每秒將日誌寫入並重新整理到磁碟一次。
2
:日誌在每次事務提交後寫入,並每秒重新整理到磁碟一次。
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
磁碟結構
接下來,再來看看InnoDB體系結構的右邊部分,也就是磁碟結構:
System Tablespace
系統表空間是更改緩衝區的儲存區域。如果表是在系統表空間而不是每個表文件或通用表空間中建立的,它也可能包含表和索引資料。(在MySQL5.x版本中還包含InnoDB資料字典、undolog等)
引數:innodb_data_file_path
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
系統表空間,預設的檔名叫 ibdata1。
- File-Per-Table Tablespaces
如果開啟了innodb_file_per_table開關 ,則每個表的檔案表空間包含單個InnoDB表的資料和索引 ,並存儲在檔案系統上的單個數據檔案中。
開關引數:innodb_file_per_table
,該引數預設開啟。
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
那也就是說,我們每建立一個表,都會產生一個表空間檔案,如圖:
- General Tablespaces
通用表空間,需要通過 CREATE TABLESPACE 語法建立通用表空間,在建立表時,可以指定該表空間。
A. 建立表空間
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;
mysql> CREATE TABLESPACE ts_itheima ADD DATAFILE 'myitheima.ibd' ENGINE = innodb;
Query OK, 0 rows affected (0.00 sec)
B. 建立表時指定表空間
CREATE TABLE xxx ... TABLESPACE ts_name;
mysql> create table a(id int primary key auto_increment,name varchar(10)) engine=innodb tablespace ts_itheima;
Query OK, 0 rows affected (0.01 sec)
- Undo Tablespaces
撤銷表空間,MySQL例項在初始化時會自動建立兩個預設的undo表空間(初始大小16M),用於儲存 undo log日誌。
- Temporary Tablespaces
InnoDB 使用會話臨時表空間和全域性臨時表空間。儲存使用者建立的臨時表等資料。
- Doublewrite Buffer Files
雙寫緩衝區,innoDB引擎將資料頁從Buffer Pool重新整理到磁碟前,先將資料頁寫入雙寫緩衝區檔案中,便於系統異常時恢復資料。
- Redo Log
重做日誌,是用來實現事務的永續性。該日誌檔案由兩部分組成:重做日誌緩衝(redo log buffer)以及重做日誌檔案(redo log),前者是在記憶體中,後者在磁碟中。當事務提交之後會把所有修改資訊都會存到該日誌中, 用於在重新整理髒頁到磁碟時,發生錯誤時, 進行資料恢復使用。
以迴圈方式寫入重做日誌檔案,涉及兩個檔案:
-rw-r-----. 1 mysql mysql 50331648 10月 2 22:52 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 10月 2 22:52 ib_logfile1
前面我們介紹了InnoDB的記憶體結構,以及磁碟結構,那麼記憶體中我們所更新的資料,又是如何到磁碟中的呢? 此時,就涉及到一組後臺執行緒,接下來,就來介紹一些InnoDB中涉及到的後臺執行緒。
後臺執行緒
在InnoDB的後臺執行緒中,分為4類,分別是:Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。
- Master Thread
核心後臺執行緒,負責排程其他執行緒,還負責將緩衝池中的資料非同步重新整理到磁碟中, 保持資料的一致性,還包括髒頁的重新整理、合併插入快取、undo頁的回收 。
- IO Thread
在InnoDB儲存引擎中大量使用了AIO來處理IO請求, 這樣可以極大地提高資料庫的效能,而IOThread主要負責這些IO請求的回撥。
執行緒型別 | 默認個數 | 職責 |
---|---|---|
Read thread | 4 | 負責讀操作 |
Write thread | 4 | 負責寫操作 |
Log thread | 1 | 負責將日誌緩衝區重新整理到磁碟 |
Insert buffer thread | 1 | 負責將寫緩衝區內容重新整理到磁碟 |
我們可以通過以下的這條指令,檢視到InnoDB的狀態資訊,其中就包含IO Thread資訊。
show engine innodb status;
- Purge Thread
主要用於回收事務已經提交了的undo log,在事務提交之後,undo log可能不用了,就用它來回收。
- Page Cleaner Thread
協助 Master Thread 重新整理髒頁到磁碟的執行緒,它可以減輕 Master Thread 的工作壓力,減少阻塞
本文由
傳智教育博學谷狂野架構師
教研團隊釋出。如果本文對您有幫助,歡迎
關注
和點贊
;如果您有任何建議也可留言評論
或私信
,您的支援是我堅持創作的動力。轉載請註明出處!
- ElasticSearch還能效能調優,漲見識、漲見識了!!!
- 【必須收藏】別再亂找TiDB 叢集部署教程了,這篇保姆級教程來幫你!!| 博學谷狂野架構師
- 【建議收藏】7000 字的TIDB保姆級簡介,你見過嗎
- Tomcat架構設計剖析 | 博學谷狂野架構師
- 你可能不那麼知道的Tomcat生命週期管理 | 博學谷狂野架構師
- 大哥,這是併發不是並行,Are You Ok?
- 為啥要重學Tomcat?| 博學谷狂野架構師
- 這是一篇純講SQL語句優化的文章!!!| 博學谷狂野架構師
- 捲起來!!!看了這篇文章我才知道MySQL事務&MVCC到底是啥?
- 為什麼99%的程式設計師都做不好SQL優化?
- 如何搞定MySQL鎖(全域性鎖、表級鎖、行級鎖)?這篇文章告訴你答案!太TMD詳細了!!!
- 【建議收藏】超詳細的Canal入門,看這篇就夠了!!!
- 從菜鳥程式設計師到高階架構師,竟然是因為這個字final
- 為什麼95%的Java程式設計師,都是用不好Synchronized?
- 99%的Java程式設計師者,都敗給這一個字!
- 8000 字,就說一個字Volatile
- 98%的程式設計師,都沒有研究過JVM重排序和順序一致性
- 來一波騷操作,Java記憶體模型
- 時隔多年,這次我終於把動態代理的原始碼翻了個地兒朝天
- 再有人問你分散式事務,把這篇文章砸過去給他