背景說明：

Mysql調優，是大家日常常見的調優工作。所以Mysql調優是一個非常、非常核心的面試知識點。

在30歲老架構師的讀者交流群(50+)中，其相關面試題是一個非常、非常高頻的交流話題。

只要一面試，基本就會問：

對mySQL調優了解嗎？

你是怎麼做調優的。

很多小夥伴，回答起來，就是乾巴巴的幾點。導致給面試官的用戶體驗，非常差。

這裡尼恩給大家調優，做一下系統化、體系化的梳理。

結合咱們社群中幾個P8大佬的優化策略、設計規範，統一為兩大部分：

P8大佬的62條 SQL語句性能優化策略
P8大佬的MySQL資料庫設計規範

收藏起來有大用，大家平時在SQL優化的時候，可以複習一下，減少生產事故的發生。

在面試之前，也可以複習一下，使得大家可以充分展示一下大家雄厚的「技術肌肉」，讓面試官愛到「不能自已、口水直流」。

也一併把這些寶貴內容作為「mysql調優的」參考答案，收入咱們的《Java面試寶典》，供後面的小夥伴參考，提升大家的 3高架構、設計、開發水平。

註：本文以 PDF 持續更新，最新Java 架構筆記、面試題的PDF文件，請後台私信【筆記】即可免費獲取！

回顧：MySQL的執行過程

回顧 MySQL的執行過程，幫助介紹如何進行sql優化。

（1）客戶端發送一條查詢語句到伺服器；

（2）伺服器先查詢緩存，如果命中緩存，則立即返回存儲在緩存中的數據；

（3）未命中緩存後，MySQL通過關鍵字將SQL語句進行解析，並生成一顆對應的解析樹，MySQL解析器將使用MySQL語法進行驗證和解析。

例如，驗證是否使用了錯誤的關鍵字，或者關鍵字的使用是否正確；

（4）預處理是根據一些MySQL規則檢查解析樹是否合理，比如檢查表和列是否存在，還會解析名字和別名，然後預處理器會驗證權限；

根據執行計劃查詢執行引擎，調用API接口調用存儲引擎來查詢數據；

（5）將結果返回客戶端，並進行緩存；

P8大佬的62條 SQL語句性能優化策略

1、為 WHERE 及 ORDER BY 涉及的列上建立索引

對查詢進行優化，應儘量避免全表掃描，首先應考慮在 WHERE 及 ORDER BY 涉及的列上建立索引。

2、where中使用默認值代替null

應儘量避免在 WHERE 子句中對欄位進行 NULL 值判斷，創建表時 NULL 是默認值，但大多數時候應該使用 NOT NULL，或者使用一個特殊的值，如 0，-1 作為默認值。

為啥建議where中使用默認值代替null，四個原因：

（1）並不是說使用了is null或者 is NOT null就會不走索引了，這個跟mysql版本以及查詢成本都有關；

（2）如果mysql優化器發現，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經常被認為讓索引失效；

（3）其實是因為一般情況下，查詢的成本高，優化器自動放棄索引的；

（4）如果把null值，換成默認值，很多時候讓走索引成為可能，同時，表達意思也相對清晰一點；

3、慎用 != 或 <> 操作符。

MySQL 只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的 LIKE。

所以：應儘量避免在 WHERE 子句中使用 != 或 <> 操作符，會導致全表掃描。

4、慎用 OR 來連接條件

使用or可能會使索引失效，從而全表掃描；

應儘量避免在 WHERE 子句中使用 OR 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，

可以使用 UNION 合併查詢：

select id from t where num=10 

union all 

select id from t where num=20

一個關鍵的問題是否用到索引。

他們的速度只同是否使用索引有關，如果查詢需要用到聯合索引，用 UNION all 執行的效率更高。

多個 OR 的字句沒有用到索引，改寫成 UNION 的形式再試圖與索引匹配。

5、慎用 IN 和 NOT IN

IN 和 NOT IN 也要慎用，否則會導致全表掃描。對於連續的數值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。

6、慎用左模糊like 『%...』

模糊查詢，程式設計師最喜歡的就是使用like，like很可能讓索引失效。

比如：

select id from t where name like『%abc%』 
select id from t where name like『%abc』

而select id from t where name like『abc%』才用到索引。

所以：

首先儘量避免模糊查詢，如果必須使用，不採用全模糊查詢，也應儘量採用右模糊查詢，即like 『…%』，是會使用索引的；
左模糊like 『%...』無法直接使用索引，但可以利用reverse + function index的形式，變化成 like 『…%』；
全模糊查詢是無法優化的，一定要使用的話建議使用搜尋引擎，比如 ElasticSearch。

備註：如果一定要用左模糊like 『%...』檢索，一般建議 ElasticSearch+Hbase架構，

7、WHERE條件使用參數會導致全表掃描。

如下面語句將進行全表掃描：

select id from t where num=@num

因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到 運行時；

它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。

所以，可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8、應避免WHERE 表達式操作/對欄位進行函數操作

任何對列的操作都將導致表掃描，它包括資料庫函數、計算表達式等等，

應儘量避免在 WHERE 子句中對欄位進行表達式操作，應儘量避免在 WHERE 子句中對欄位進行函數操作。

如：

select id from t where num/2=100
應改為:
select id from t where num=100*2

應儘量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。
如：

select id from t where substring(name,1,3)=『abc』

select id from t where datediff(day,createdate,『2005-11-30』)=0

應改為:

select id from t where name like 『abc%』

select id from t where createdate>=『2005-11-30』 and createdate<『2005-12-1』

9、用 EXISTS 代替 IN 是一個好的選擇

很多時候用exists 代替in 是一個好的選擇：

select num from a where num in(select num from b)
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)

10、索引並不是越多越好

索引固然可以提高相應的 SELECT 的效率，但同時也降低了 INSERT 及 UPDATE 的效。

因為 INSERT 或 UPDATE 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

一個表的索引數最好不要超過 6 個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

11、應儘可能的避免更新 clustered 索引數據列

應儘可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，

一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。

若應用系統需要頻繁更新 clustered 索引數據列，那麼需要考慮是否應將該索引建為 clustered 索引。

12、儘量使用數字型欄位

（1）因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符；

（2）而對於數字型而言只需要比較一次就夠了；

（3）字符會降低查詢和連接的性能，並會增加存儲開銷；

所以：

儘量使用數字型欄位，若只含數值信息的欄位儘量不要設計為字符型，這會降低查詢和連接的性能，並會增加存儲開銷。

13、儘可能的使用 VARCHAR, nvarchar 代替 char, nchar

（1）varchar變長欄位按數據內容實際長度存儲，存儲空間小，可以節省存儲空間；

（2）char按聲明大小存儲，不足補空格；

（3）其次對於查詢來說，在一個相對較小的欄位內搜索，效率更高；

因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

14、查詢SQL儘量不要使用select *，而是具體欄位

最好不要使用返回所有：select * from t ，用具體的欄位列表代替「*」，不要返回用不到的任何欄位。

select *的弊端：

（1）增加很多不必要的消耗，比如CPU、IO、內存、網絡帶寬；

（2）增加了使用覆蓋索引的可能性；

（3）增加了回表的可能性；

（4）當表結構發生變化時，前端也需要更改；

（5）查詢效率低；

15、儘量避免向客戶端返回大數據量

大數據量增加很多不必要的消耗，比如CPU、IO、內存、網絡帶寬

儘量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

16、使用表的別名（Alias）：

當在 SQL 語句中連接多個表時，請使用表的別名並把別名前綴於每個 Column 上。

這樣一來，就可以減少解析的時間並減少那些由 Column 歧義引起的語法錯誤。

17、使用「臨時表」暫存中間結果：

簡化 SQL 語句的重要方法就是採用臨時表暫存中間結果。

但是臨時表的好處遠遠不止這些，將臨時結果暫存在臨時表，後面的查詢就在 tempdb 中了，這可以避免程序中多次掃描主表，也大大減少了程序執行中「共享鎖」阻塞「更新鎖」，減少了阻塞，提高了並發性能。

18、一些 SQL 查詢語句應加上 nolock。

一些 SQL 查詢語句應加上 nolock，讀、寫是會相互阻塞的，為了提高並發性能。

對於一些查詢，可以加上 nolock，這樣讀的時候可以允許寫，但缺點是可能讀到未提交的髒數據。

使用 nolock 有3條原則：

查詢的結果用於「插、刪、改」的不能加 nolock；
查詢的表屬於頻繁發生頁分裂的，慎用 nolock ；
使用臨時表一樣可以保存「數據前影」，起到類似 Oracle 的 undo 表空間的功能，能採用臨時表提高並發性能的，不要用 nolock。

19、常見的簡化規則如下：

不要有超過 5 個以上的表連接（JOIN），考慮使用臨時表或表變量存放中間結果。

少用子查詢，視圖嵌套不要過深，一般視圖嵌套不要超過 2 個為宜。

20、將需要查詢的結果預先計算好

將需要查詢的結果預先計算好放在表中，查詢的時候再Select，而不是查詢的時候進行計算。

這在SQL7.0以前是最重要的手段，例如醫院的住院費計算。

21、IN後出現最頻繁的值放在最前面

如果一定用IN，那麼：

在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。

22、使用存儲過程進行數據處理

儘量將數據的處理工作放在伺服器上，減少網絡的開銷，如使用存儲過程。

存儲過程是編譯好、優化過、並且被組織到一個執行規劃里、且存儲在資料庫中的 SQL 語句，是控制流語言的集合，速度當然快。反覆執行的動態 SQL，可以使用臨時存儲過程，該過程（臨時表）被放在 Tempdb 中。

23、儘量使用 EXISTS 代替 select count(1) 來判斷是否存在記錄。

count 函數只有在統計表中所有行數時使用，而且 count(1) 比 count(*) 更有效率。

24、索引的使用規範：

索引的創建要與應用結合考慮，建議大的 OLTP 表不要超過 6 個索引；
儘可能的使用索引欄位作為查詢條件，尤其是聚簇索引，必要時可以通過 index index_name 來強制指定索引；
避免對大表查詢時進行 table scan，必要時考慮新建索引；
在使用索引欄位作為條件時，如果該索引是聯合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用；
要注意索引的維護，周期性重建索引，重新編譯存儲過程。

25、下列 SQL 條件語句中的列都建有恰當的索引，但執行速度卻非常慢：

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 

SELECT * FROM record WHERE amount/30 < 1000 --11秒 

SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒

分析：

WHERE 子句中對列的任何操作結果都是在 SQL 運行時逐列計算得到的，因此它不得不進行表搜索，而沒有使用該列上面的索引。

如果這些結果在查詢編譯時就能得到，那麼就可以被 SQL 優化器優化，使用索引，避免表搜索，因此將 SQL 重寫成下面這樣：

SELECT * FROM record WHERE card_no like '5378%' -- < 1秒 

SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 

SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒

26、用批量插入或批量更新

當有一批處理的插入或更新時，用批量插入或批量更新，絕不會一條條記錄的去更新。

（1）多條提交

INSERT INTO user (id,username) VALUES(1,'技術自由圈');
INSERT INTO user (id,username) VALUES(2,'瘋狂創客圈');

（2）批量提交

INSERT INTO user (id,username) VALUES(1,'技術自由圈'),(2,'瘋狂創客圈');

默認新增SQL有事務控制，導致每條都需要事務開啟和事務提交，而批量處理是一次事務開啟和提交，效率提升明顯，達到一定量級，效果顯著，平時看不出來。

27、存儲過程中慎用循環

在所有的存儲過程中，能夠用 SQL 語句的，我絕不會用循環去實現。

例如：列出上個月的每一天，我會用 connect by 去遞歸查詢一下，絕不會去用循環從上個月第一天到最後一天。

28、選擇最有效率的表名順序

選擇最有效率的表名順序（只在基於規則的優化器中有效）： Oracle 的解析器按照從右到左的順序處理 FROM 子句中的表名，FROM 子句中寫在最後的表（基礎表 driving table）將被最先處理，在 FROM 子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎表。

如果有 3 個以上的表連接查詢，那就需要選擇交叉表（interp table）作為基礎表，交叉表是指那個被其他表所引用的表。

29、將不需要的記錄在 GROUP BY 之前過濾掉

提高 GROUP BY 語句的效率，可以通過將不需要的記錄在 GROUP BY 之前過濾掉。

下面兩個查詢返回相同結果，但第二個明顯就快了許多。

低效：

SELECT JOB, AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'

高效：

SELECT JOB, AVG(SAL) 
FROM EMP
WHERE JOB = 'PRESIDENT' 
OR JOB = 'MANAGER' 
GROUP BY JOB

30、別名的使用，

別名是大型資料庫的應用技巧，就是表名、列名在查詢中以一個字母為別名，查詢速度要比建連接表快 1.5 倍。

31、避免死鎖，

在你的存儲過程和觸發器中訪問同一個表時總是以相同的順序；事務應經可能地縮短，在一個事務中應儘可能減少涉及到的數據量；永遠不要在事務中等待用戶輸入。

32、避免使用臨時表，可以使用表變量代替

避免使用臨時表，除非卻有需要，否則應儘量避免使用臨時表，相反，可以使用表變量代替。

大多數時候（99%），表變量駐紮在內存中，因此速度比臨時表更快，

臨時表駐紮在 TempDb 資料庫中，因此臨時表上的操作需要跨資料庫通信，速度自然慢。

33、最好不要使用觸發器：

觸發一個觸發器，執行一個觸發器事件本身就是一個耗費資源的過程；
如果能夠使用約束實現的，儘量不要使用觸發器；
不要為不同的觸發事件（Insert、Update 和 Delete）使用相同的觸發器；
不要在觸發器中使用事務型代碼。

34、索引創建規則：

表的主鍵、外鍵必須有索引；
數據量超過 300 的表應該有索引；
經常與其他表進行連接的表，在連接欄位上應該建立索引；
經常出現在 WHERE 子句中的欄位，特別是大表的欄位，應該建立索引；
索引應該建在選擇性高的欄位上；
索引應該建在小欄位上，對於大的文本欄位甚至超長欄位，不要建索引；
複合索引的建立需要進行仔細分析，儘量考慮用單欄位索引代替；
正確選擇複合索引中的主列欄位，一般是選擇性較好的欄位；
複合索引的幾個欄位是否經常同時以 AND 方式出現在 WHERE 子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；
如果複合索引中包含的欄位經常單獨出現在 WHERE 子句中，則分解為多個單欄位索引；
如果複合索引所包含的欄位超過 3 個，那麼仔細考慮其必要性，考慮減少複合的欄位；
如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引；
頻繁進行數據操作的表，不要建立太多的索引；
刪除無用的索引，避免對執行計劃造成負面影響；
表上建立的每個索引都會增加存儲開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低數據增加刪除時的性能，特別是對頻繁更新的表來說，負面影響更大。
儘量不要對資料庫中某個含有大量重複的值的欄位建立索引。

35、在寫 SQL 語句時，應儘量減少空格的使用

查詢緩衝並不自動處理空格，

因此，在寫 SQL 語句時，應儘量減少空格的使用，尤其是在 SQL 首和尾的空格（因為查詢緩衝並不自動截取首尾空格）。

36、member 用 mid 做標準進行分表方便查詢麼？

member 用 mid 做標準進行分表方便查詢麼？

一般的業務需求中基本上都是以 username 為查詢依據，正常應當是 username 做 hash 取模來分表。

而分表的話 MySQL 的 partition 功能就是幹這個的，對代碼是透明的；在代碼層面去實現貌似是不合理的。

37、每張表都設置一個 ID 做為其主鍵

我們應該為資料庫里的每張表都設置一個 ID 做為其主鍵，而且最好的是一個 INT 型的（推薦使用 UNSIGNED），並設置上自動增加的 AUTO_INCREMENT 標誌。

38、在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON

在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON，在結束時設置 SET NOCOUNT OFF。無需在執行存儲過程和觸發器的每個語句後向客戶端發送 DONE_IN_PROC 消息。

39、MySQL 查詢可以啟用高速查詢緩存

這是提高資料庫性能的有效MySQL優化方法之一。當同一個查詢被執行多次時，從緩存中提取數據和直接從資料庫中返回數據快很多。

40、EXPLAIN SELECT 查詢用來跟蹤查看效果

使用 EXPLAIN 關鍵字可以讓你知道 MySQL 是如何處理你的 SQL 語句的。

這可以幫你分析你的查詢語句或是表結構的性能瓶頸。

EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的數據表是如何被搜索和排序的。

41、當只要一行數據時使用 LIMIT 1

當你查詢表的有些時候，你已經知道結果只會有一條結果，但因為你可能需要去fetch游標，或是你也許會去檢查返回的記錄數。

在這種情況下，加上 LIMIT 1 可以增加性能。

這樣一來，MySQL 資料庫引擎會在找到一條數據後停止搜索，而不是繼續往後查少下一條符合記錄的數據。

42、選擇表合適存儲引擎：

myisam：應用時以讀和插入操作為主，只有少量的更新和刪除，並且對事務的完整性，並發性要求不是很高的。
InnoDB：事務處理，以及並發條件下要求數據的一致性。除了插入和查詢外，包括很多的更新和刪除。（InnoDB 有效地降低刪除和更新導致的鎖定）。
對於支持事務的 InnoDB類型的表來說，影響速度的主要原因是 AUTOCOMMIT 默認設置是打開的，而且程序沒有顯式調用 BEGIN 開始事務，導致每插入一條都自動提交，嚴重影響了速度。可以在執行 SQL 前調用 begin，多條 SQL 形成一個事物（即使 autocommit 打開也可以），將大大提高性能。

43、優化表的數據類型，選擇合適的數據類型：

原則：更小通常更好，簡單就好，所有欄位都得有默認值，儘量避免 NULL。

例如：資料庫表設計時候更小的占磁碟空間儘可能使用更小的整數類型。(mediumint 就比 int 更合適)

比如時間欄位：datetime 和 timestamp。datetime 占用8個字節，timestamp 占用4個字節，只用了一半。而 timestamp 表示的範圍是 1970—2037 適合做更新時間。

MySQL可以很好的支持大數據量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。

因此，在創建表的時候，為了獲得更好的性能，我們可以將表中欄位的寬度設得儘可能小。

例如：在定義郵政編碼這個欄位時，如果將其設置為 CHAR(255)，顯然給資料庫增加了不必要的空間。甚至使用VARCHAR 這種類型也是多餘的，因為 CHAR(6) 就可以很好的完成任務了。

同樣的，如果可以的話，我們應該使用 MEDIUMINT 而不是 BIGIN 來定義整型欄位，應該儘量把欄位設置為 NOT NULL，這樣在將來執行查詢的時候，資料庫不用去比較 NULL 值。

對於某些文本欄位，例如「省份」或者「性別」，我們可以將它們定義為 ENUM 類型。因為在 MySQL 中，ENUM 類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高資料庫的性能。

44、將大的DELETE，UPDATE、INSERT 查詢變成多個小查詢

能寫一個幾十行、幾百行的SQL語句是不是顯得逼格很高？然而，為了達到更好的性能以及更好的數據控制，你可以將他們變成多個小查詢。

45、關於臨時表

（1）避免頻繁創建和刪除臨時表，以減少系統表資源的消耗；

（2）在新建臨時表時，如果一次性插入數據量很大，那麼可以使用 select into 代替 create table，避免造成大量 log；

（3）如果數據量不大，為了緩和系統表的資源，應先create table，然後insert；

（4）如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除。先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

46、使用explain分析你SQL執行計劃

（1）type

system：表僅有一行，基本用不到；
const：表最多一行數據配合，主鍵查詢時觸發較多；
eq_ref：對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型；
ref：對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；
range：只檢索給定範圍的行，使用一個索引來選擇行。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range；
index：該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小；
all：全表掃描；
性能排名：system > const > eq_ref > ref > range > index > all。
實際sql優化中，最後達到ref或range級別。

（2）Extra常用關鍵字

Using index：只從索引樹中獲取信息，而不需要回表查詢；
Using where：WHERE子句用於限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where並且表聯接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。
Using temporary：mysql常建一個臨時表來容納結果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

47、使用合理的分頁方式以提高分頁的效率

select id,name from user limit 100000, 20

使用上述SQL語句做分頁的時候，隨著表數據量的增加，直接使用limit語句會越來越慢。

此時，可以通過取前一頁的最大ID，以此為起點，再進行limit操作，效率提升顯著。

select id,name from user where id> 100000 limit 20

48、儘量控制單表數據量的大小，建議控制在500萬以內

500萬並不是MySQL資料庫的限制，過大會造成修改表結構，備份，恢復都會有很大的問題。

可以用歷史數據歸檔（應用於日誌數據），分庫分表（應用於業務數據）等手段來控制數據量大小。

49、謹慎使用Mysql分區表

（1）分區表在物理上表現為多個文件，在邏輯上表現為一個表；

（2）謹慎選擇分區鍵，跨分區查詢效率可能更低；

（3）建議採用物理分表的方式管理大數據。

50、儘量做到冷熱數據分離，減小表的寬度

Mysql限制每個表最多存儲4096列，並且每一行數據的大小不能超過65535位元組。

減少磁碟IO,保證熱數據的內存緩存命中率（表越寬，把表裝載進內存緩衝池時所占用的內存也就越大,也會消耗更多的IO）；

更有效的利用緩存，避免讀入無用的冷數據；

經常一起使用的列放到一個表中（避免更多的關聯操作）。

51、禁止在表中建立預留欄位

（1）預留欄位的命名很難做到見名識義；

（2）預留欄位無法確認存儲的數據類型，所以無法選擇合適的類型；

（3）對預留欄位類型的修改，會對表進行鎖定；

52、禁止在資料庫中存儲圖片，文件等大的二進位數據

通常文件很大，會短時間內造成數據量快速增長，資料庫進行資料庫讀取時，通常會進行大量的隨機IO操作，文件很大時，IO操作很耗時。

通常存儲於文件伺服器，資料庫只存儲文件地址信息。

53、建議把BLOB或是TEXT列分離到單獨的擴展表中

Mysql內存臨時表不支持TEXT、BLOB這樣的大數據類型，如果查詢中包含這樣的數據，在排序等操作時，就不能使用內存臨時表，必須使用磁碟臨時表進行。而且對於這種數據，Mysql還是要進行二次查詢，會使sql性能變得很差，但是不是說一定不能使用這樣的數據類型。

如果一定要使用，建議把BLOB或是TEXT列分離到單獨的擴展表中，查詢時一定不要使用select * 而只需要取出必要的列，不需要TEXT列的數據時不要對該列進行查詢。

54、TEXT或BLOB類型只能使用前綴索引

因為MySQL對索引欄位長度是有限制的，所以TEXT類型只能使用前綴索引，並且TEXT列上是不能有默認值的。

55、建議使用預編譯語句進行資料庫操作

預編譯語句可以重複使用這些計劃，減少SQL編譯所需要的時間，還可以解決動態SQL所帶來的SQL注入的問題。

只傳參數，比傳遞SQL語句更高效。

相同語句可以一次解析，多次使用，提高處理效率。

56、表連接不宜太多，索引不宜太多，一般5個以內

（1）表連接不宜太多，一般5個以內

關聯的表個數越多，編譯的時間和開銷也就越大
每次關聯內存中都生成一個臨時表
應該把連接表拆開成較小的幾個執行，可讀性更高
如果一定需要連接很多表才能得到數據，那麼意味著這是個糟糕的設計了
阿里規範中，建議多表聯查三張表以下

（2）索引不宜太多，一般5個以內

索引並不是越多越好，雖其提高了查詢的效率，但卻會降低插入和更新的效率；
索引可以理解為一個就是一張表，其可以存儲數據，其數據就要占空間；
索引表的數據是排序的，排序也是要花時間的；
insert或update時有可能會重建索引，如果數據量巨大，重建將進行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來定；
一個表的索引數最好不要超過5個，若太多需要考慮一些索引是否有存在的必要；

57、資料庫和表的字符集統一使用UTF8

兼容性更好，統一字符集可以避免由於字符集轉換產生的亂碼，不同的字符集進行比較前需要進行轉換會造成索引失效，如果資料庫中有存儲emoji表情的需要，字符集需要採用utf8mb4字符集。

58、合理選擇索引列的順序

建立索引的目的是：

希望通過索引進行數據查找，減少隨機IO，增加查詢性能，索引能過濾出越少的數據，則從磁碟中讀入的數據也就越少。

區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）。

儘量把欄位長度小的列放在聯合索引的最左側（因為欄位長度越小，一頁能存儲的數據量越大，IO性能也就越好）。

使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）。

59對於頻繁的查詢優先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢欄位(where,select,ordery by,group by包含的欄位)的索引。

覆蓋索引的好處：

（1）避免Innodb表進行索引的二次查詢

Innodb是以聚集索引的順序來存儲的，對於Innodb來說，二級索引在葉子節點中所保存的是行的主鍵信息，如果是用二級索引查詢數據的話，在查找到相應的鍵值後，還要通過主鍵進行二次查詢才能獲取我們真實所需要的數據。

而在覆蓋索引中，二級索引的鍵值中可以獲取所有的數據，避免了對主鍵的二次查詢，減少了IO操作，提升了查詢效率。

（2）可以把隨機IO變成順序IO加快查詢效率

由於覆蓋索引是按鍵值的順序存儲的，對於IO密集型的範圍查找來說，對比隨機從磁碟讀取每一行的數據IO要少的多，因此利用覆蓋索引在訪問時也可以把磁碟的隨機讀取的IO轉變成索引查找的順序IO。

60、MySQL 查詢優化的一般策略：

使用慢查詢日誌去發現慢查詢，使用執行計劃去判斷查詢是否正常運行，總是去測試你的查詢看看是否他們運行在最佳狀態下。

久而久之性能總會變化，避免在整個表上使用 count(*)，它可能鎖住整張表，使查詢保持一致以便後續相似的查詢可以使用查詢緩存，在適當的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE、GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引簡單，不在多個索引中包含同一個列。

有時候 MySQL 會使用錯誤的索引，對於這種情況使用 USE INDEX，檢查使用 SQL_MODE=STRICT 的問題，對於記錄數小於5的索引欄位，在 UNION 的時候使用LIMIT不是是用OR。

為了避免在更新之前進行一次 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE；

不要用 UPDATE 去實現，不要使用 MAX；

使用索引欄位和 ORDER BY子句 LIMIT M，N 實際上可以減緩查詢在某些情況下，有節制地使用，在 WHERE 子句中使用 UNION 代替子查詢，在重新啟動的 MySQL，記得來溫暖你的資料庫，以確保數據在內存和查詢速度快，考慮持久連接，而不是多個連接，以減少開銷。

基準查詢，包括使用伺服器上的負載，有時一個簡單的查詢可以影響其他查詢，當負載增加在伺服器上，使用 SHOW PROCESSLIST 查看慢的和有問題的查詢，在開發環境中產生的鏡像數據中測試的所有可疑的查詢。

61、定期的進行 MySQL 資料庫的備份：

前段時間，有小夥伴來找尼恩，說他的資料庫被黑客挾持了，要支付1個比特幣才能要回來，我問他有定期的備份了嗎，他說沒有。

MySQL 資料庫的備份流程：

從二級複製伺服器上進行備份；
在進行備份期間停止複製，以避免在數據依賴和外鍵約束上出現不一致；
徹底停止 MySQL，從資料庫文件進行備份；
如果使用 MySQL dump 進行備份，請同時備份二進位日誌文件 – 確保複製沒有中斷；
不要信任 LVM 快照，這很可能產生數據不一致，將來會給你帶來麻煩；
為了更容易進行單表恢復，以表為單位導出數據——如果數據是與其他表隔離的。
當使用 mysqldump 時請使用 –opt；
在備份之前檢查和優化表；
為了更快的進行導入，在導入時臨時禁用外鍵約束。；
為了更快的進行導入，在導入時臨時禁用唯一性檢測；
在每一次備份後計算資料庫，表以及索引的尺寸，以便更夠監控數據尺寸的增長；
通過自動調度腳本監控複製實例的錯誤和延遲；
定期執行備份。

62、分庫分表與NOSqL結合使用

當數據量達到一定的數量之後，限制資料庫存儲性能的就不再是資料庫層面的優化就能夠解決的；

這個時候往往採用的是讀寫分離與分庫分表同時也會結合緩存一起使用，而這個時候資料庫層面的優化只是基礎。

一般的演進規則是：

讀寫分離適用於較小一些的數據量；
分表適用於中等數據量；
而分庫與分表一般是結合著用，這就適用於大數據量的存儲了，

這也是現在大型網際網路公司解決數據存儲的方法之一。

尼恩備註：

分庫分表與NOSqL結合使用，一般建議 ElasticSearch+Hbase架構。
左手大數據，右手雲原生，要掌握高並發架構達到技術自由，大數據、雲原生的技術必不可少。

P8大佬的MySQL資料庫設計規範

一：庫表設計規範

以下所有規範會按照【高危】、【強制】、【建議】三個級別進行標註，遵守優先級從高到低。對於不滿足【高危】和【強制】兩個級別的設計，DBA會強制打回要求修改。

1、庫名規範

【強制】庫的名稱必須控制在32個字符以內，相關模塊的表名與表名之間儘量提現join的關係，如user表和user_login表。
【強制】庫的名稱格式：業務系統名稱_子系統名，同一模塊使用的表名儘量使用統一前綴。
【強制】一般分庫名稱命名格式是庫通配名_編號，編號從0開始遞增，比如wenda_001以時間進行分庫的名稱格式是「庫通配名_時間」
【強制】創建資料庫時必須顯式指定字符集，並且字符集只能是utf8或者utf8mb4。創建資料庫SQL舉例：create database db1 default character set utf8;。

2、表結構規範

【強制】表和列的名稱必須控制在32個字符以內，表名只能使用字母、數字和下劃線，一律小寫。
【強制】表名要求模塊名強相關，如師資系統採用」sz」作為前綴，渠道系統採用」qd」作為前綴等。
【強制】創建表時必須顯式指定字符集為utf8或utf8mb4。
【強制】創建表時必須顯式指定表存儲引擎類型，如無特殊需求，一律為InnoDB。當需要使用除InnoDB/MyISAM/Memory以外的存儲引擎時，必須通過DBA審核才能在生產環境中使用。因為Innodb表支持事務、行鎖、宕機恢復、MVCC等關係型資料庫重要特性，為業界使用最多的MySQL存儲引擎。而這是其他大多數存儲引擎不具備的，因此首推InnoDB。
【強制】建表必須有comment
【建議】建表時關於主鍵：(1)強制要求主鍵為id，類型為int或bigint，且為auto_increment(2)標識表里每一行主體的欄位不要設為主鍵，建議設為其他欄位如user_id，order_id等，並建立unique key索引（可參考cdb.teacher表設計）。因為如果設為主鍵且主鍵值為隨機插入，則會導致innodb內部page分裂和大量隨機I/O，性能下降。
【建議】核心表（如用戶表，金錢相關的表）必須有行數據的創建時間欄位create_time和最後更新時間欄位update_time，便於查問題。
【建議】表中所有欄位必須都是NOT NULL屬性，業務可以根據需要定義DEFAULT值。因為使用NULL值會存在每一行都會占用額外存儲空間、數據遷移容易出錯、聚合函數計算結果偏差等問題。
【建議】建議對表里的blob、text等大欄位，垂直拆分到其他表里，僅在需要讀這些對象的時候才去select。
【建議】反範式設計：把經常需要join查詢的欄位，在其他表里冗餘一份。如user_name屬性在user_account，user_login_log等表里冗餘一份，減少join查詢。
【強制】中間表用於保留中間結果集，名稱必須以tmp_開頭。
備份表用於備份或抓取源表快照，名稱必須以bak_開頭。
中間表和備份表定期清理。
【強制】對於超過100W行的大表進行alter table，必須經過DBA審核，並在業務低峰期執行。
因為alter table會產生表鎖，期間阻塞對於該表的所有寫入，對於業務可能會產生極大影響。

3、列數據類型優化

【建議】表中的自增列（auto_increment屬性），推薦使用bigint類型。因為無符號int存儲範圍為-2147483648~2147483647（大約21億左右），溢出後會導致報錯。
【建議】業務中選擇性很少的狀態status、類型type等欄位推薦使用tinytint或者smallint類型節省存儲空間。
【建議】業務中IP位址欄位推薦使用int類型，不推薦用char(15)。因為int只占4位元組，可以用如下函數相互轉換，而char(15)占用至少15位元組。一旦表數據行數到了1億，那麼要多用1.1G存儲空間。 SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(『192.168.2.12』); long2ip(3530427185);
【建議】不推薦使用enum，set。因為它們浪費空間，且枚舉值寫死了，變更不方便。推薦使用tinyint或smallint。
【建議】不推薦使用blob，text等類型。它們都比較浪費硬碟和內存空間。在加載表數據時，會讀取大欄位到內存里從而浪費內存空間，影響系統性能。建議和PM、RD溝通，是否真的需要這麼大欄位。Innodb中當一行記錄超過8098位元組時，會將該記錄中選取最長的一個欄位將其768位元組放在原始page里，該欄位餘下內容放在overflow-page里。不幸的是在compact行格式下，原始page和overflow-page都會加載。
【建議】存儲金錢的欄位，建議用int，程序端乘以100和除以100進行存取。因為int占用4位元組，而double占用8位元組，空間浪費。
【建議】文本數據儘量用varchar存儲。因為varchar是變長存儲，比char更省空間。MySQL server層規定一行所有文本最多存65535位元組，因此在utf8字符集下最多存21844個字符，超過會自動轉換為mediumtext欄位。而text在utf8字符集下最多存21844個字符，mediumtext最多存224/3個字符，longtext最多存232個字符。一般建議用varchar類型，字符數不要超過2700。
【建議】時間類型儘量選取timestamp。
因為datetime占用8位元組，timestamp僅占用4位元組，但是範圍為1970-01-01 00:00:01到2038-01-01 00:00:00。更為高階的方法，選用int來存儲時間，使用SQL函數unix_timestamp()和from_unixtime()來進行轉換。

詳細存儲大小參加下圖：

4、索引設計

【強制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新。
【建議】主鍵的名稱以「pk_」開頭，唯一鍵以「uk_」或「uq_」開頭，普通索引以「idx_」開頭，一律使用小寫格式，以表名/欄位的名稱或縮寫作為後綴。
【強制】InnoDB和MyISAM存儲引擎表，索引類型必須為BTREE；MEMORY表可以根據需要選擇HASH或者BTREE類型索引。
【強制】單個索引中每個索引記錄的長度不能超過64KB。
【建議】單個表上的索引個數不能超過7個。
【建議】在建立索引時，多考慮建立聯合索引，並把區分度最高的欄位放在最前面。如列userid的區分度可由select count(distinct userid)計算出來。
【建議】在多表join的SQL里，保證被驅動表的連接列上有索引，這樣join執行效率最高。
【建議】建表或加索引時，保證表里互相不存在冗餘索引。對於MySQL來說，如果表里已經存在key(a,b)，則key(a)為冗餘索引，需要刪除。

5、分庫分表、分區表

【強制】分區表的分區欄位（partition-key）必須有索引，或者是組合索引的首列。
【強制】單個分區表中的分區（包括子分區）個數不能超過1024。
【強制】上線前RD或者DBA必須指定分區表的創建、清理策略。
【強制】訪問分區表的SQL必須包含分區鍵。
【建議】單個分區文件不超過2G，總大小不超過50G。建議總分區數不超過20個。
【強制】對於分區表執行alter table操作，必須在業務低峰期執行。
【強制】採用分庫策略的，庫的數量不能超過1024
【強制】採用分表策略的，表的數量不能超過4096
【建議】單個分表不超過500W行，ibd文件大小不超過2G，這樣才能讓數據分布式變得性能更佳。
【建議】水平分表儘量用取模方式，日誌、報表類數據建議採用日期進行分表。

6、字符集

【強制】資料庫本身庫、表、列所有字符集必須保持一致，為utf8或utf8mb4。
【強制】前端程序字符集或者環境變量中的字符集，與資料庫、表的字符集必須一致，統一為utf8。

二： SQL編寫規範

1、 DML語句

【強制】SELECT語句必須指定具體欄位名稱，禁止寫成*。因為select *會將不該讀的數據也從MySQL里讀出來，造成網卡壓力。且表欄位一旦更新，但model層沒有來得及更新的話，系統會報錯。
【強制】insert語句指定具體欄位名稱，不要寫成insert into t1 values(…)，道理同上。
【建議】insert into…values(XX),(XX),(XX)…。這裡XX的值不要超過5000個。值過多雖然上線很很快，但會引起主從同步延遲。
【建議】SELECT語句不要使用UNION，推薦使用UNION ALL，並且UNION子句個數限制在5個以內。因為union all不需要去重，節省資料庫資源，提高性能。
【建議】in值列表限制在500以內。例如select… where userid in(….500個以內…)，這麼做是為了減少底層掃描，減輕資料庫壓力從而加速查詢。
【建議】事務里批量更新數據需要控制數量，進行必要的sleep，做到少量多次。
【強制】事務涉及的表必須全部是innodb表。否則一旦失敗不會全部回滾，且易造成主從庫同步終端。
【強制】寫入和事務發往主庫，只讀SQL發往從庫。
【強制】除靜態表或小表（100行以內），DML語句必須有where條件，且使用索引查找。
【強制】生產環境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。
因為hint是用來強制SQL按照某個執行計劃來執行，但隨著數據量變化我們無法保證自己當初的預判是正確的，因此我們要相信MySQL優化器！
【強制】where條件里等號左右欄位類型必須一致，否則無法利用索引。
【建議】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查找。
【強制】生產資料庫中強烈不推薦大表上發生全表掃描，但對於100行以下的靜態表可以全表掃描。查詢數據量不要超過表行數的25%，否則不會利用索引。
【強制】WHERE 子句中禁止只使用全模糊的LIKE條件進行查找，必須有其他等值或範圍查詢條件，否則無法利用索引。
【建議】索引列不要使用函數或表達式，否則無法利用索引。如where length(name)='Admin'或where user_id+2=10023。
【建議】減少使用or語句，可將or語句優化為union，然後在各個where條件上建立索引。如where a=1 or b=2優化為where a=1… union …where b=2, key(a),key(b)。
【建議】分頁查詢，當limit起點較高時，可先用過濾條件進行過濾。如select a,b,c from t1 limit 10000,20;優化為:select a,b,c from t1 where id>10000 limit 20;。

2、多表連接

【強制】禁止跨db的join語句。因為這樣可以減少模塊間耦合，為資料庫拆分奠定堅實基礎。
【強制】禁止在業務的更新類SQL語句中使用join，比如update t1 join t2…。
【建議】不建議使用子查詢，建議將子查詢SQL拆開結合程序多次查詢，或使用join來代替子查詢。
【建議】線上環境，多表join不要超過3個表。
【建議】多表連接查詢推薦使用別名，且SELECT列表中要用別名引用欄位，資料庫.表格式，如select a from db1.table1 alias1 where …。
【建議】在多表join中，儘量選取結果集較小的表作為驅動表，來join其他表。

3、事務

【建議】事務中INSERT|UPDATE|DELETE|REPLACE語句操作的行數控制在2000以內，以及WHERE子句中IN列表的傳參個數控制在500以內。
【建議】批量操作數據時，需要控制事務處理間隔時間，進行必要的sleep，一般建議值5-10秒。
【建議】對於有auto_increment屬性欄位的表的插入操作，並發需要控制在200以內。
【強制】程序設計必須考慮「資料庫事務隔離級別」帶來的影響，包括髒讀、不可重複讀和幻讀。線上建議事務隔離級別為repeatable-read。
【建議】事務里包含SQL不超過5個（支付業務除外）。因為過長的事務會導致鎖數據較久，MySQL內部緩存、連接消耗過多等雪崩問題。
【建議】事務里更新語句儘量基於主鍵或unique key，如update … where id=XX; 否則會產生間隙鎖，內部擴大鎖定範圍，導致系統性能下降，產生死鎖。
【建議】儘量把一些典型外部調用移出事務，如調用webservice，訪問文件存儲等，從而避免事務過長。
【建議】對於MySQL主從延遲嚴格敏感的select語句，請開啟事務強制訪問主庫。

4、排序和分組

【建議】減少使用order by，和業務溝通能不排序就不排序，或將排序放到程序端去做。order by、group by、distinct這些語句較為耗費CPU，資料庫的CPU資源是極其寶貴的。
【建議】order by、group by、distinct這些SQL儘量利用索引直接檢索出排序好的數據。如where a=1 order by可以利用key(a,b)。
【建議】包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢。

5、線上禁止使用的SQL語句

【高危】禁用update|delete t1 … where a=XX limit XX; 這種帶limit的更新語句。因為會導致主從不一致，導致數據錯亂。建議加上order by PK。
【高危】禁止使用關聯子查詢，如update t1 set … where name in(select name from user where…);效率極其低下。
【強制】禁用procedure、function、trigger、views、event、外鍵約束。因為他們消耗資料庫資源，降低資料庫實例可擴展性。推薦都在程序端實現。
【強制】禁用insert into …on duplicate key update…在高並發環境下，會造成主從不一致。
【強制】禁止聯表更新語句，如update t1,t2 where t1.id=t2.id…。

30歲老架構師尼恩提示

這裡結合咱們社群中幾個P8大佬的優化策略，統一為兩大部分：

P8大佬的62條 SQL語句性能優化策略
P8大佬的MySQL資料庫設計規範