引言

架構調優，在系統設計時首先需要充分考慮業務的實際情況，是否可以把不適合數據庫做的事情放到數據倉庫、搜索引擎或者緩存中去做；然后考慮寫的并發量有多大，是否需要采用分布式；最后考慮讀的壓力是否很大，是否需要讀寫分離。對于核心應用或者金融類的應用，需要額外考慮數據安全因素，數據是否不允許丟失。所以在進行優化時，首先需要關注和優化的應該是架構，如果架構不合理，即使是DBA能做的事情其實是也是比較有限的。

MySQL調優，需要確認業務表結構設計是否合理，SQL語句優化是否足夠，該添加的索引是否都添加了，是否可以剔除多余的索引等等

本文已如上角度進行分析，當然，關于業務表結構是否合理的話題，在今后深入到MySQL底層再討論。

同時，本文將介紹一些常用的提高索引效率的方案。

慢查詢基礎-數據訪問

查詢花費大量時間，超過long_query_time參數設定的時間閾值的SQL語句。

一般查詢性能低下的最基本原因是訪問的數據太多。

故，我們對于低效的查詢一般通過兩種步驟分析：是否檢索了大量不需要的數據、是否超過需要的數據行。

檢索了大量不需要的數據-列

總是取出全部列？

每次看到SELECT*的時候都需要用懷疑的眼光審視，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，會讓優化器無法完成索引覆蓋掃描這類優化,還會為服務器帶來額外的I/O、內存和CPU的消耗。因此，一些DBA是嚴格禁止SELECT *的寫法的，這樣做有時候還能避免某些列被修改帶來的問題。

尤其是使用二級索引，使用*的方式會導致回表，導致性能低下。

什么時候可以使用？SELECT*如果應用程序使用了某種緩存機制，或者有其他考慮，獲取超過需要的數據也可能有其好處，但不要忘記這樣做的代價是什么。獲取并緩存所有的列的查詢，相比多個獨立的只獲取部分列的查詢可能就更有好處。

重復查詢相同的數據-加緩存

不斷地重復執行相同的查詢，然后每次都返回完全相同的數據。比較好的方案是，當初次查詢的時候將這個數據緩存起來，需要的時候從緩存中取出，這樣性能顯然會更好。

衡量查詢開銷的指標

響應時間（服務時間+排隊時間）、掃描行數、返回行數、訪問類型。

如果發現查詢需要掃描大量的數據但只返回少數的行，那么通常可以嘗試下面的技巧去優化它:

1、使用索引覆蓋掃描，把所有需要用的列都放到索引中，這樣存儲引擎無須回表獲取對應行就可以返回結果了

2、改變庫表結構。例如使用單獨的匯總表。

3、重寫這個復雜的查詢，讓MySQL優化器能夠以更優的方式執行該查詢。

慢查詢配置

set global long_query_time=10; （10秒）

l slow_query_log 啟動停止慢查詢日志

l slow_query_log_file 指定慢查詢日志得存儲路徑及文件（默認和數據文件放一起）

l long_query_time 指定記錄慢查詢日志SQL執行時間得伐值（單位：秒，默認10秒）

l log_queries_not_using_indexes 是否記錄未使用索引的SQL

l log_output 日志存放的地方可以是TABLE[FILE,TABLE]

Explian執行計劃

EXPLAIN語句來幫助我們查看某個查詢語句的具體執行計劃，我們需要搞懂EPLATNEXPLAIN的各個輸出項都是干嘛使的，從而可以有針對性的提升我們查詢語句的性能。

分析查詢語句或是表結構的性能瓶頸，總的來說通過EXPLAIN我們可以：

l 表的讀取順序

l 數據讀取操作的操作類型

l 哪些索引可以使用

l 哪些索引被實際使用

l 表之間的引用

l 每張表有多少行被優化器查詢

type

我們前邊說過執行計劃的一條記錄就代表著MySQL對某個表的執行查詢時的訪問方法/訪問類型，其中的type列就表明了這個訪問方法/訪問類型是個什么東西，是較為重要的一個指標，結果值從最好到最壞依次是：

出現比較多的是system>const>eq_ref>ref>range>index>ALL

一般來說，得保證查詢至少達到range級別，最好能達到ref。

查詢優化器

SQL語句在MySQL中執行過程如圖所示。

優化：優化SQL語句，例如重寫查詢，決定表的讀取順序，以及選擇需要的索引等。這一階段用戶是可以查詢的，查詢服務器優化器是如何進行優化的，便于用戶重構查詢和修改相關配置，達到最優化。這一階段還涉及到存儲引擎，優化器會詢問存儲引擎，比如某個操作的開銷信息、是否對特定索引有查詢優化等。

高性能索引使用策略

不在索引列進行任何操作

盡量全值匹配

最佳左前綴法則

范圍條件放在最后

覆蓋索引盡量用

不等于慎用

Null/Not對查詢有影響

小心Like查詢

字符類型加上引號

小心使用or關鍵字

使用索引掃描來排序或分組

排序注意點

盡可能按照主鍵順序插入行

優化Count查詢

優化limit分頁

NULL特別說明

不在索引列進行任何操作：

在索引列上使用函數，是無法利用索引的；索引列不進行計算操作，MySQL無法自動解析方程式。

盡量全值匹配：

建立了聯合索引列后，如果我們的搜索條件中的列和索引列一致的話，這種情況就稱為全值匹配。查詢優化器會決定先用哪個后用哪個查詢的條件~

最佳左前綴法則：

建立了聯合索引列，如果搜索條件不夠全值匹配怎么辦？在我們的搜索語句中也可以不用包含全部聯合索引中的列，但要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。PS：建立聯合索引只會產生一顆B+樹

范圍條件放最后

所有記錄都是按照索引列的值從小到大的順序排好序的，而聯合索引則是按創建索引時的順序進行分組排序。如果對多個列同時進行范圍查找的話，只有對索引最左邊的那個列進行范圍查找的時候才能用到B+樹索引。對于一個聯合索引來說，雖然對多個列都進行范圍查找時只能用到最左邊那個索引列，但是如果左邊的列是精確查找，則右邊的列可以進行范圍查找；而中間有范圍查詢會導致后面的列全部失效，無法充分利用這個聯合索引

覆蓋索引盡量用（不回表）

覆蓋索引是非常有用的工具，能夠極大地提高性能，三星索引里最重要的那顆星就是寬索引星。如果查詢只需要掃描索引而無須回表，

索引條目通常遠小于數據行大小，所以如果只需要讀取索引，那 MySQL就會極大地減少數據訪問量。這對緩存的負載非常重要，因為這種情況下響應時間大部分花費在數據拷貝上。覆蓋索引對于I/O密集型的應用也有幫助，因為索引比數據更小,更容易全部放入內存中。

因為索引是按照列值順序存儲的，所以對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行數據的I/O要少得多。

由于InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致))，不是必要的情況下減少select*，除非是需要將表中的全部列檢索后，進行緩存。

慎用不等于（mysql 在使用不等于(!= 或者<>)的時候無法使用索引會導致全表掃描）

小心Like查詢

like以通配符開頭('%abc...')，mysql索引失效會變成全表掃描的操作。解決方案是使用覆蓋索引涉及到的列進行like查詢。

字符類型加引號，不加單引號導致索引失效奧

原因：MySQL的查詢優化器，會自動的進行類型轉換，自然造成索引失效。

使用or關鍵字時要注意

使用同一列進行or，沒啥。如果or的兩列不是同一列，某個不是索引，就只能全盤掃描。這種情況下可以通過union all 或者 覆蓋掃描 改善這種問題。

使用索引掃描來排序和分組

MySQL有兩種方式可以生成有序的結果﹔通過排序操作﹔或者按索引順序掃描施﹔如果EXPLAIN出來的type列的值為“index”，則說明MySQL使用了索引掃描來做排序。

掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對應的行。這基本上都是隨機I/O，因此按索引順序讀取數據的速度通常要比順序地全表掃描慢，尤其是在IO密集型的工作負載時。

MySQL可以使用同一個索引既滿足排序，又用于查找行。因此，如果可能，設計索引時應該盡可能地同時滿足這兩種任務，這樣是最好的。

只有當索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向（倒序或正序）都一樣時，MySQL才能夠使用索引來對結果做排序。如果查詢需要關聯多張表，則只有當ORDER BY子句引用的字段全部為第一個表時，才能使用索引做排序。

排序小心

排序列包含非同一個索引的列的情況，不能用索引進行排序。

盡可能按主鍵順序插入行

最好避免隨機的（不連續且值的分布范圍非常大）聚簇索引，特別是對于I/O密集型的應用。例如，從性能的角度考慮，使用UUID來作為聚簇索引則會很糟糕，它使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得數據沒有任何聚集特性。

最簡單的方法是使用AUTO_INCREMENT自增列。這樣可以保證數據行是按順序寫入，對于根據主鍵做關聯操作的性能也會更好。

注意到向UUID主鍵插入行不僅花費的時間更長，而且索引占用的空間也更大。這一方面是由于主鍵字段更長﹔另一方面毫無疑問是由于頁分裂和碎片導致的。

因為主鍵的值是順序的，所以InnoDB把每一條記錄都存儲在上一條記錄的后面。當達到頁的最大填充因子時(InnoDB默認的最大填充因子是頁大小的15/16，留出部分空間用于以后修改)，下一條記錄就會寫入新的頁中。一旦數據按照這種順序的方式加載,主鍵頁就會近似于被順序的記錄填滿,這也正是所期望的結果。

如果新行的主鍵值不一定比之前插入的大，所以InnoDB無法簡單地總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置-—通常是已有數據的中間位置——并且分配空間。這會增加很多的額外工作，并導致數據分布不夠優化。下面是總結的一些缺點:

寫入的目標頁可能已經刷到磁盤上并從緩存中移除，或者是還沒有被加載到緩存中，InnoDB在插入之前不得不先找到并從磁盤讀取目標頁到內存中。這將導致大量的隨機IO。

因為寫入是亂序的，InnoDB不得不頻繁地做頁分裂操作，以便為新的行分配空間。頁分裂會導致移動大量數據，一次插入最少需要修改三個頁而不是一個頁。

所以使用InnoDB時應該盡可能地按主鍵順序插入數據，并且盡可能地使用單調增加的聚簇鍵的值來插入新行。

優化count查詢

COUNT()是一個特殊的函數，有兩種非常不同的作用:它可以統計某個列值的數量，也可以統計行數。

通常來說，COUNT()都需要掃描大量的行（意味著要訪問大量數據）才能獲得精確的結果，因此是很難優化的。在MySQL層面能做的基本只有索引覆蓋掃描了。如果這還不夠,就需要考慮修改應用的架構，可以用估算值取代精確值，可以增加匯總表，或者增加類似Redis這樣的外部緩存系統。

優化limit分頁查詢

先查詢翻頁中需要的N條數據的主鍵值，然后根據主鍵值回表查詢所需要的N條數據，在此過程中查詢N條數據的主鍵id在索引中完成，所以效率會高一些。

EXPLAIN SELECT * FROM (select id from order_exp limit 10000,10) b,order_exp

a where a.id = b.id;

select * from order_exp limit 10000,10; 這種偏移量大，相當于舍棄1w條數據，只要后面10條，這也是查詢遠超出需要的數據列。

關于NULL的特別說明

NULL 在MySQL中是獨一無二的！MySQL8 在進行索引列的數據統計行為把null視為nulls_equal情況（NULL值在業務上就是代表沒有，所有的NULL值和起來算一份），看起來，MySQL中對Null值的處理也很分裂。所以總的來說，對于列的聲明盡可能的不要允許為null。