DBA整理的萬字詳解MySQL性能優(yōu)化，值得收藏！

MySQL邏輯架構(gòu)

MySQL查詢過程

客戶端/服務(wù)端通信協(xié)議

查詢緩存

• 任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條SQL語句永遠(yuǎn)不會(huì)命中緩存
• 如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會(huì)將結(jié)果存入緩存，也會(huì)帶來額外的系統(tǒng)消耗

• 用多個(gè)小表代替一個(gè)大表，注意不要過度設(shè)計(jì)
• 批量插入代替循環(huán)單條插入
• 合理控制緩存空間大小，一般來說其大小設(shè)置為幾十兆比較合適
• 可以通過SQL_CACHE和SQL_NO_CACHE來控制某個(gè)查詢語句是否需要進(jìn)行緩存

語法解析和預(yù)處理

查詢優(yōu)化

mysql> select * from t_message limit 10;    
...省略結(jié)果集    
  
mysql> show status like 'last_query_cost';    
+-----------------+-------------+    
| Variable_name   | Value       |    
+-----------------+-------------+    
| Last_query_cost | 6391.799000 |    
+-----------------+-------------+    

• 重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢時(shí)，并不一定按照SQL中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）
• 優(yōu)化MIN()和MAX()函數(shù)（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值，具體原理見下文）
• 提前終止查詢（比如：使用Limit時(shí)，查找到滿足數(shù)量的結(jié)果集后會(huì)立即終止查詢）
• 優(yōu)化排序（在老版本MySQL會(huì)使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內(nèi)存中對其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序。對于I/O密集型應(yīng)用，效率會(huì)高很多）

查詢執(zhí)行引擎

返回結(jié)果給客戶端

• 客戶端向MySQL服務(wù)器發(fā)送一條查詢請求
• 服務(wù)器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段
• 服務(wù)器進(jìn)行SQL解析、預(yù)處理、再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃
• MySQL根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢
• 將結(jié)果返回給客戶端，同時(shí)緩存查詢結(jié)果

性能優(yōu)化建議

Scheme設(shè)計(jì)與數(shù)據(jù)類型優(yōu)化

• 通常來說把可為NULL的列改為NOT NULL不會(huì)對性能提升有多少幫助，只是如果計(jì)劃在列上創(chuàng)建索引，就應(yīng)該將該列設(shè)置為NOT NULL。
• 對整數(shù)類型指定寬度，比如INT(11)，沒有任何卵用。INT使用16為存儲(chǔ)空間，那么它的表示范圍已經(jīng)確定，所以INT(1)和INT(20)對于存儲(chǔ)和計(jì)算是相同的。
• UNSIGNED表示不允許負(fù)值，大致可以使正數(shù)的上限提高一倍。比如TINYINT存儲(chǔ)范圍是通常來講，沒有太大的必要使用DECIMAL數(shù)據(jù)類型。即使是在需要存儲(chǔ)財(cái)務(wù)數(shù)據(jù)時(shí)，仍然可以使用BIGINT。比如需要精確到萬分之一，那么可以將數(shù)據(jù)乘以一百萬然后使用TIMESTAMP使用4個(gè)字節(jié)存儲(chǔ)空間，DATETIME使用8個(gè)字節(jié)存儲(chǔ)空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多，而且TIMESTAMP的值因時(shí)區(qū)不同而不同。
• 大多數(shù)情況下沒有使用枚舉類型的必要，其中一個(gè)缺點(diǎn)是枚舉的字符串列表是固定的，添加和刪除字符串（枚舉選項(xiàng)）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
• schema的列不要太多。原因是存儲(chǔ)引擎的API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列，這個(gè)轉(zhuǎn)換過程的代價(jià)是非常高的。如果列太多而實(shí)際使用的列又很少的話，有可能會(huì)導(dǎo)致CPU占用過高。
• 大表ALTER TABLE非常耗時(shí)，MySQL執(zhí)行大部分修改表結(jié)果操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個(gè)張空表，從舊表中查出所有的數(shù)據(jù)插入新表，然后再刪除舊表。尤其當(dāng)內(nèi)存不足而表又很大，而且還有很大索引的情況下，耗時(shí)更久。當(dāng)然有一些奇淫技巧可以解決這個(gè)問題，有興趣可自行查閱。

創(chuàng)建高性能索引

索引相關(guān)的數(shù)據(jù)結(jié)構(gòu)和算法

頁是計(jì)算機(jī)管理存儲(chǔ)器的邏輯塊，硬件及OS往往將主存和磁盤存儲(chǔ)區(qū)分割為連續(xù)的大小相等的塊，每個(gè)存儲(chǔ)塊稱為一頁（許多OS中，頁的大小通常為4K）。主存和磁盤以頁為單位交換數(shù)據(jù)。當(dāng)程序要讀取的數(shù)據(jù)不在主存中時(shí)，會(huì)觸發(fā)一個(gè)缺頁異常，此時(shí)系統(tǒng)會(huì)向磁盤發(fā)出讀盤信號(hào)，磁盤會(huì)找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁或幾頁載入內(nèi)存中，然后異常返回，程序繼續(xù)運(yùn)行。

高性能策略

CREATE TABLE People(    
    last_name varchar(50) not null,    
    first_name varchar(50) not null,    
    dob date not null,    
    gender enum(`m`,`f`) not null,    
    key(last_name,first_name,dob)    
);    

1、MySQL不會(huì)使用索引的情況：非獨(dú)立的列

select * from where id + 1 = 5  

2、前綴索引

3、多列索引和索引順序

select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1 

select film_id,actor_id from film_actor where actor_id = 1    
union all   
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1  

• 當(dāng)出現(xiàn)多個(gè)索引做相交操作時(shí)（多個(gè)AND條件），通常來說一個(gè)包含所有相關(guān)列的索引要優(yōu)于多個(gè)獨(dú)立索引。
• 當(dāng)出現(xiàn)多個(gè)索引做聯(lián)合操作時(shí)（多個(gè)OR條件），對結(jié)果集的合并、排序等操作需要耗費(fèi)大量的CPU和內(nèi)存資源，特別是當(dāng)其中的某些索引的選擇性不高，需要返回合并大量數(shù)據(jù)時(shí)，查詢成本更高。所以這種情況下還不如走全表掃描。

SELECT * FROM payment where staff_id = 2 and customer_id = 584  

select count(distinct staff_id)/count(*) as staff_id_selectivity,  
       count(distinct customer_id)/count(*) as customer_id_selectivity,  
       count(*) from payment  

select user_id from trade where user_group_id = 1 and trade_amount > 0  

4、避免多個(gè)范圍條件

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;  

5、覆蓋索引

• 索引條目遠(yuǎn)小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪問量
• 索引是有按照列值順序存儲(chǔ)的，對于I/O密集型的范圍查詢要比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO要少的多

6、使用索引掃描來排序

-- 最左列為常數(shù)，索引：(date,staff_id,customer_id)  
select  staff_id,customer_id from demo where date = '2015-06-01' ``order by staff_id,customer_id

7、冗余和重復(fù)索引

8、刪除長期未使用的索引

特定類型查詢優(yōu)化

優(yōu)化COUNT()查詢

優(yōu)化關(guān)聯(lián)查詢

• 確保ON和USING字句中的列上有索引。在創(chuàng)建索引的時(shí)候就要考慮到關(guān)聯(lián)的順序。當(dāng)表A和表B用列c關(guān)聯(lián)的時(shí)候，如果優(yōu)化器關(guān)聯(lián)的順序是A、B，那么就不需要在A表的對應(yīng)列上創(chuàng)建索引。沒有用到的索引會(huì)帶來額外的負(fù)擔(dān)，一般來說，除非有其他理由，只需要在關(guān)聯(lián)順序中的第二張表的相應(yīng)列上創(chuàng)建索引（具體原因下文分析）。
• 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列，這樣MySQL才有可能使用索引來優(yōu)化。

SELECT A.xx,B.yy   
FROM A INNER JOIN B USING(c)  
WHERE A.xx IN (5,6)  

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);  
outer_row = outer_iterator.next;  
while(outer_row) {  
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;  
    inner_row = inner_iterator.next;  
    while(inner_row) {  
        output[inner_row.yy,outer_row.xx];  
        inner_row = inner_iterator.next;  
    }  
    outer_row = outer_iterator.next;  
} 

優(yōu)化LIMIT分頁

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;  

SELECT film.film_id,film.description  
FROM film INNER JOIN (  
    SELECT film_id FROM film ORDER BY title LIMIT 50,5  
) AS tmp USING(film_id);  

SELECT id FROM t LIMIT 10000, 10;  

SELECT id FROM t WHERE id > 10000 LIMIT 10;  

優(yōu)化UNION

結(jié)語

• 有非常多的程序員在分享時(shí)都會(huì)拋出這樣一個(gè)觀點(diǎn)：盡可能不要使用存儲(chǔ)過程，存儲(chǔ)過程非常不容易維護(hù)，也會(huì)增加使用成本，應(yīng)該把業(yè)務(wù)邏輯放到客戶端。既然客戶端都能干這些事，那為什么還要存儲(chǔ)過程？
• JOIN本身也挺方便的，直接查詢就好了，為什么還需要視圖呢？

參考資料

[1] 姜承堯 著；MySQL技術(shù)內(nèi)幕-InnoDB存儲(chǔ)引擎；機(jī)械工業(yè)出版社，2013
[2] Baron Scbwartz 等著；寧海元 周振興等譯；高性能MySQL（第三版）; 電子工業(yè)出版社， 2013
[3] 由 B-/B+樹看 MySQL索引結(jié)構(gòu)

作者：LanceToBigData
來源：cnblogs.com/zhangyinhua/p/7620964.html