面試問爛的 MySQL 查詢優化，面試官說就你了。

什么影響了資料庫查詢速度

1.1 影響資料庫查詢速度的四個因素

1.2 風險分析

QPS：QueriesPerSecond意思是“每秒查詢率”，是一臺服務器每秒能夠相應的查詢次數，是對一個特定的查詢服務器在規定時間內所處理流量多少的衡量標準，

TPS：是TransactionsPerSecond的縮寫，也就是事務數/秒，它是軟體測驗結果的測量單位，客戶機在發送請求時開始計時，收到服務器回應后結束計時，以此來計算使用的時間和完成的事務個數，網站性能測驗指標詳解，更多看這篇文章，

Tips：最好不要在主庫上資料庫備份，大型活動前取消這樣的計劃，

1、效率低下的 sql：超高的 QPS與 TPS，

2、大量的并發：資料連接數被占滿（ max_connection默認 100，一般把連接數設定得大一些），

并發量:同一時刻資料庫服務器處理的請求數量

3、超高的 CPU使用率：CPU資源耗盡出現宕機，

4、磁盤 IO：磁盤 IO性能突然下降、大量消耗磁盤性能的計劃任務，解決：更快磁盤設備、調整計劃任務、做好磁盤維護，

1.3 網卡流量：如何避免無法連接資料庫的情況

1、減少從服務器的數量（從服務器會從主服務器復制日志） 2、進行分級快取（避免前端大量快取失效） 3、避免使用 select 進行查詢 4、分離業務網路和服務器網路

1.4 大表帶來的問題（重要）

1.4.1 大表的特點

1、記錄行數巨大，單表超千萬 2、表資料檔案巨大，超過 10個 G

1.4.2 大表的危害

1、慢查詢：很難在短時間內過濾出需要的資料

查詢字區分度低 -> 要在大資料量的表中篩選出來其中一部分資料會產生大量的磁盤 io -> 降低磁盤效率

2.對 DDL影響：

建立索引需要很長時間：

MySQL-v<5.5 建立索引會鎖表 MySQL-v>=5.5 建立索引會造成主從延遲（ mysql建立索引，先在組上執行，再在庫上執行）

修改表結構需要長時間的鎖表：會造成長時間的主從延遲('480秒延遲')

1.4.3 如何處理資料庫上的大表

分庫分表把一張大表分成多個小表

難點：

1、分表主鍵的選擇 2、分表后跨磁區資料的查詢和統計

1.5 大事務帶來的問題（重要）

1.5.1 什么是事務

事務是資料庫系統區別于其他一切檔案系統的重要特性之一

事務是一組具有原子性的SQL陳述句，或是一個獨立的作業單元

事務要求符合：原子性、一致性、隔離性、持久性

1.5.2事務的 ACID屬性

1、原子性（ atomicity)：全部成功，全部回滾失敗，銀行存取款，

2、一致性（consistent)：銀行轉賬的總金額不變，3、隔離性（isolation)：

隔離性等級：

未提交讀( READ UNCOMMITED)臟讀,兩個事務之間互相可見；已提交讀(READ COMMITED)符合隔離性的基本概念,一個事務進行時，其它已提交的事物對于該事務是可見的，即可以獲取其它事務提交的資料，可重復讀( REPEATABLE READ)InnoDB的默認隔離等級，事務進行時，其它所有事務對其不可見，即多次執行讀，得到的結果是一樣的！可串行化（SERIALIZABLE） 在讀取的每一行資料上都加鎖，會造成大量的鎖超時和鎖征用，嚴格資料一致性且沒有并發是可使用，

查看系統的事務隔離級別：show variables like'%iso%';開啟一個新事務：begin;提交一個事務：commit;修改事物的隔離級別：setsession tx_isolation='read-committed';推薦：面試問爛的 MySQL 四種隔離級別，看完吊打面試官！關注Java技術堆疊微信公眾號，在后臺回復關鍵字：mysql，可以獲取更多堆疊長整理的MySQL技術干貨，

4、持久性( DURABILITY)：從資料庫的角度的持久性，磁盤損壞就不行了

redolog機制保證事務更新的一致性和持久性

1.5.3 大事務

運行時間長，操作資料比較多的事務；

風險：鎖定資料太多，回滾時間長，執行時間長，

1、鎖定太多資料，造成大量阻塞和鎖超時；

2、回滾時所需時間比較長，且資料仍然會處于鎖定；

3、如果執行時間長，將造成主從延遲，因為只有當主服務器全部執行完寫入日志時，從服務器才會開始進行同步，造成延遲，關注Java技術堆疊微信公眾號，在后臺回復關鍵字：mysql，可以獲取更多堆疊長整理的MySQL技術干貨，

解決思路：

1、避免一次處理太多資料，可以分批次處理；

2、移出不必要的 SELECT操作，保證事務中只有必要的寫操作，

什么影響了MySQL性能（非常重要）

2.1 影響性能的幾個方面

1、服務器硬體，

2、服務器系統（系統引數優化），

3、存盤引擎，MyISAM：不支持事務，表級鎖，InnoDB: 支持事務，支持行級鎖，事務 ACID，

4、資料庫引數配置，

5、資料庫結構設計和SQL陳述句，（重點優化）

2.2 MySQL體系結構

分三層：客戶端->服務層->存盤引擎

1、 MySQL是插件式的存盤引擎，其中存盤引擎分很多種，只要實作符合mysql存盤引擎的介面，可以開發自己的存盤引擎! 2、所有跨存盤引擎的功能都是在服務層實作的，

3、MySQL的存盤引擎是針對表的，不是針對庫的，也就是說在一個資料庫中可以使用不同的存盤引擎，但是不建議這樣做，

2.3 InnoDB存盤引擎

MySQL5.5及之后版本默認的存盤引擎：InnoDB，

2.3.1 InnoDB使用表空間進行資料存盤，

show variables like'innodb_file_per_table

如果innodbfileper_table 為 ON 將建立獨立的表空間，檔案為tablename.ibd；

如果innodbfileper_table 為 OFF 將資料存盤到系統的共享表空間，檔案為ibdataX（X為從1開始的整數）；

.frm：是服務器層面產生的檔案，類似服務器層的資料字典，記錄表結構，

2.3.2 (MySQL5.5默認)系統表空間與( MySQL5.6及以后默認)獨立表空間

1、系統表空間無法簡單的收縮檔案大小，造成空間浪費，并會產生大量的磁盤碎片，

2、獨立表空間可以通過 optimeze table 收縮系統檔案，不需要重啟服務器也不會影響對表的正常訪問，

3、如果對多個表進行重繪時，實際上是順序進行的，會產生IO瓶頸，

4、獨立表空間可以同時向多個檔案重繪資料，

強烈建立對Innodb 使用獨立表空間，優化什么的更方便，可控，

2.3.3 系統表空間的表轉移到獨立表空間中的方法

1、使用mysqldump 匯出所有資料庫資料（存盤程序、觸發器、計劃任務一起都要匯出 ）可以在從服務器上操作，

2、停止MYsql 服務器，修改引數（my.cnf加入innodbfileper_table），并洗掉Inoodb相關檔案（可以重建Data目錄），

3、重啟MYSQL，并重建Innodb系統表空間，

4、 重新匯入資料，

或者Altertable 同樣可以的轉移，但是無法回收系統表空間中占用的空間，

2.4 InnoDB存盤引擎的特性

2.4.1 特性一：事務性存盤引擎及兩個特殊日志型別：Redo Log 和 Undo Log

1、 Innodb是一種事務性存盤引擎，

2、完全支持事務的 ACID特性，3、支持事務所需要的兩個特殊日志型別：RedoLog 和 UndoLog

Redo Log：實作事務的持久性(已提交的事務)，Undo Log：未提交的事務，獨立于表空間，需要隨機訪問，可以存盤在高性能io設備上，

Undo日志記錄某資料被修改前的值，可以用來在事務失敗時進行 rollback；Redo日志記錄某資料塊被修改后的值，可以用來恢復未寫入 data file的已成功事務更新的資料，

2.4.2 特性二：支持行級鎖

1、InnoDB支持行級鎖，

2、行級鎖可以最大程度地支持并發，

3、行級鎖是由存盤引擎層實作的，

2.5 什么是鎖

2.5.1 鎖

鎖的主要作用是管理共享資源的并發訪問

鎖用于實作事務的隔離性

2.5.2 鎖型別

2.5.3 鎖的粒度

MySQL的事務支持不是系結在MySQL服務器本身，而是與存盤引擎相關

表級鎖

行級鎖

將table_name加表級鎖命令：locktable table_name write;寫鎖會阻塞其它用戶對該表的‘讀寫’操作，直到寫鎖被釋放：unlock tables；

1、鎖的開銷越大，粒度越小，并發度越高，2、表級鎖通常是在服務器層實作的，3、行級鎖是存盤引擎層實作的，innodb的鎖機制，服務器層是不知道的

2.5.4 阻塞和死鎖

1、阻塞是由于資源不足引起的排隊等待現象，

2、死鎖是由于兩個物件在擁有一份資源的情況下申請另一份資源，而另一份資源恰好又是這兩物件正持有的，導致兩物件無法完成操作，且所持資源無法釋放，

2.6 如何選擇正確的存盤引擎

參考條件：

1、事務 2、備份( Innobd免費在線備份) 3、崩潰恢復 4、存盤引擎的特有特性

總結:nnodb大法好，注意:別使用混合存盤引擎，比如回滾會出問題在線熱備問題，

2.7 配置引數

2.7.1 記憶體配置相關引數

確定可以使用的記憶體上限，

記憶體的使用上限不能超過物理記憶體，否則容易造成記憶體溢位；（對于32位作業系統，MySQL只能試用3G以下的記憶體，37 個 MySQL 資料庫小技巧，推薦看一下，關注Java技術堆疊微信公眾號，在后臺回復關鍵字：mysql，可以獲取更多堆疊長整理的MySQL技術干貨，

確定MySQL的每個連接 單獨使用的記憶體，

sort_buffer_size

定義了每個執行緒排序快取區的大小，MySQL在有查詢、需要做排序操作時才會為每個緩沖區分配記憶體（直接分配該引數的全部記憶體）；

join_buffer_size

定義了每個執行緒所使用的連接緩沖區的大小，如果一個查詢關聯了多張表，MySQL會為每張表分配一個連接緩沖，導致一個查詢產生了多個連接緩沖；

read_buffer_size

定義了當對一張MyISAM進行全表掃描時所分配讀緩沖池大小，MySQL有查詢需要時會為其分配記憶體，其必須是4k的倍數；

read_rnd_buffer_size

索引緩沖區大小，MySQL有查詢需要時會為其分配記憶體，只會分配需要的大小，

注意：以上四個引數是為一個執行緒分配的，如果有100個連接，那么需要×100，

MySQL資料庫實體：

MySQL是單行程多執行緒（而oracle是多行程），也就是說MySQL實體在系統上表現就是一個服務行程，即行程；

MySQL實體是執行緒和記憶體組成，實體才是真正用于操作資料庫檔案的；

一般情況下一個實體操作一個或多個資料庫；集群情況下多個實體操作一個或多個資料庫，

如何為快取池分配記憶體：

Innodb_buffer_pool_size

定義了Innodb所使用快取池的大小，對其性能十分重要，必須足夠大，但是過大時，使得Innodb 關閉時候需要更多時間把臟頁從緩沖池中重繪到磁盤中；

總記憶體-（每個執行緒所需要的記憶體*連接數）- 統保留記憶體

key_buffer_size

定義了MyISAM所使用的快取池的大小，由于資料是依賴存盤作業系統快取的，所以要為作業系統預留更大的記憶體空間；

select sum(index_length) from information_schema.talbes where engine='myisam'

注意：即使開發使用的表全部是Innodb表，也要為MyISAM預留記憶體，因為MySQL系統使用的表仍然是MyISAM表，

max_connections

控制允許的最大連接數， 一般2000更大，不要使用外鍵約束保證資料的完整性

2.8 性能優化順序

庫結構設計和SQL陳述句

資料庫存盤引擎的選擇和引數配置

系統選擇及優化

硬體升級

標籤：其他

上一篇：2021初、中、高級測驗工程師成長指南

下一篇：快速對比API版本