前言
近期在重新學習總結MySQL資料庫效能優化的相關知識，本文是根據自己學習以及日常效能測試調優過程中總結的經驗整理了一份平民化的優化指南，希望對大家在進行MySQL調優分析時有幫助！
MySQL查詢執行原理
一般情況下，我們優化MySQL,目的就是為了查詢資料更快，在優化MySQL效能之前，有必要先弄清楚MySQL是如何優化和執行查詢的。一旦理解了這一點，就會發現很多的查詢優化工作實際上就是遵循一些原則讓MySQL的優化器能夠按照預想的合理方式執行而已。
如上圖，整個MySQL查詢執行過程，總的來說分為5個步驟：
- 客戶端向MySQL伺服器傳送一條查詢請求。
- 伺服器首先檢查查詢快取，如果命中快取，則立刻返回儲存在快取中的結果。否則進入下一階段。
- 伺服器進行SQL解析、預處理、再由優化器生成對應的執行計劃。
- MySQL根據執行計劃，呼叫儲存引擎的API來執行查詢。
- 將查詢結果返回給客戶端，同時快取查詢結果。
MySQL效能優化導圖
先總結一下，MySql效能優化思路主要從以下幾個大的層面考慮優化：
【系統層面；儲存引擎；設計層面；架構層面；引數配置；SQL層面；快取機制】
系統優化
硬體環境
- 升級多核CPU,主頻高的CPU
- 更大的記憶體
- 更大的IOPS
- 叢集，雙機熱備，多機互備
網路環境
- 儘量將網路整體系統部署在區域網內
- SLB裝置優化
- 網路頻寬升級
軟體環境
- 開啟mysql複製，實現讀寫分離，負載均衡
- 分庫分表（垂直分表，水平分表）
- 利用分割槽功能進行大資料的拆分
- 系統核心優化，大多數MySQL都部署在linux系統上，所以作業系統的一些引數也會影響到MySQL效能
儲存引擎
在實際應用系統中，結合儲存引擎的優缺點選擇合適的儲存引擎（MyISAM，InnoDB，Memory）。
MySQL常用有兩種儲存引擎:
- 一個是MyISAM，不支援事務處理，讀效能處理快，表級別鎖。
- 一個是InnoDB，支援事務處理（ACID），設計目標是為處理大容量資料發揮最大化效能，行級別鎖。
表鎖：開銷小，鎖定粒度大，發生死鎖概率高，相對併發也低。
行鎖：開銷大，鎖定粒度小，發生死鎖概率低，相對併發也高。
為什麼會出現表鎖和行鎖呢？主要是為了保證資料的完整性，舉個例子，一個使用者在操作一張表，其他使用者也想操作這張表，那麼就要等第一個使用者操作完，其他使用者才能操作，表鎖和行鎖就是這個作用。否則多個使用者同時操作一張表，肯定會資料產生衝突或者異常。
根據一般公司的業務要求看來，使用InnoDB儲存引擎是最好的選擇，也是MySQL5.5以後版本中預設儲存引擎。
設計優化
表結構設計
總的原則：選擇合適的欄位屬性， 儘量使用數字型欄位。若只含數值資訊的欄位儘量不要設計為字元型，這會降低查詢和連線的效能，並會增加儲存開銷。這是因為引擎在處理查詢和連線時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。選擇資料型別只要遵循小而簡單的原則就好，越小的資料型別通常會更快，佔用更少的磁碟、記憶體，處理時需要的CPU週期也更少。越簡單的資料型別在計算時只需更少的CPU週期，比如，整型就比字元操作代價低，因而會使用整型來儲存ip地址，使用DATETIME來儲存時間，而不是使用字串。儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。
八點意見清單：
- 通常來說把可為NULL的列改為NOT NULL不會對效能提升有多少幫助，只是如果計劃在列上建立索引，就應該將該列設定為NOT NULL。
- 無需對整數型別指定寬度，比如INT(11)，用處不大。INT使用32位（4個位元組）儲存空間，那麼它的表示範圍已經確定，所以INT(1)和INT(20)對於儲存和計算是相同的。
- UNSIGNED表示不允許負值，大致可以使正數的上限提高一倍。比如TINYINT儲存範圍是-128 ~ 127，而UNSIGNED TINYINT儲存的範圍卻是0 - 255。
- 通常來講，沒有太大的必要使用DECIMAL資料型別。即使是在需要儲存財務資料時，仍然可以使用BIGINT。比如需要精確到萬分之一，那麼可以將資料乘以一百萬然後使用BIGINT儲存。這樣可以避免浮點數計算不準確和DECIMAL精確計算代價高的問題。
- TIMESTAMP使用4個位元組儲存空間，DATETIME使用8個位元組儲存空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的範圍小得多，而且TIMESTAMP的值因時區不同而不同。
- 大多數情況下沒有使用列舉型別的必要，其中一個缺點是列舉的字串列表是固定的，新增和刪除字串（列舉選項）必須使用ALTER TABLE。
- schema的列不要太多。原因是儲存引擎的API工作時需要在伺服器層和儲存引擎層之間通過行緩衝格式拷貝資料，然後在伺服器層將緩衝內容解碼成各個列，這個轉換過程的代價是非常高的。如果列太多而實際使用的列又很少的話，有可能會導致CPU佔用過高。
- 大表ALTER TABLE非常耗時，MySQL執行大部分修改表結果操作的方法是用新的結構建立一個張空表，從舊錶中查出所有的資料插入新表，然後再刪除舊錶。尤其當記憶體不足而表又很大，而且還有很大索引的情況下，耗時更久。
高效能索引設計
索引是提高MySQL查詢效能的一個重要途徑，但過多的索引可能會導致過高的磁碟使用率以及過高的記憶體佔用，從而影響應用程式的整體效能。可見索引的新增也是非常有技術含量的。
九點意見清單：
- 非獨立的列，mysql不會使用索引， “獨立的列”是指索引列不能是表示式的一部分，也不能是函式的引數，如select * from where id + 1 = 10；
- 使用短索引，如果列很長，通常可以指定一個字首長度只索引開始的部分字元 ，這樣可以有效節約索引空間，從而提高索引效率。 例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。
- 使用組合索引，在多數情況下，在多個列上建立獨立的索引並不能提高查詢效能。理由非常簡單，MySQL不知道選擇哪個索引的查詢效率更好。
- 避免多範圍條件查詢，如select * from user where createtime > '2020-01-01' and age between 18 and 30;
- 冗餘索引是指在相同的列上按照相同的順序建立的相同型別的索引，應當儘量避免這種索引，發現後立即刪除。
- 定期刪除一些長時間未使用過的索引。
- 索引不包含有NULL列的值。
- 排序的索引問題， mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引。
- 索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
架構調優
- 主從複製（讀寫分離）
一臺MySQL伺服器同一時間點支援的併發數是有限的，當大量併發(如秒殺活動等，很多使用者都同一時刻訪問資料庫)時，一臺資料庫處理不過來，所以增加MySQL伺服器的數量也是一種增強資料庫效能的方式。通過使用MySQL主從複製，增刪改操作走Master主伺服器，查詢走Slaver從伺服器，這樣就減少了只有一臺MySQL伺服器的壓力。
- 分庫分表
當一個表的資料量很大的時候，查詢就變的很慢，所以減少表裡的記錄的數量是優化的一種方式，大表對DDL操作有一定的影響，如建立索引，新增欄位；修改表結構需要長時間鎖表，會造成長時間的主從延遲，影響正常的資料操作。
分庫
把db分為按分散式劃分幾個DB來執行。
分表
水平分表：如按月份分。
垂直分表：如按訂單號取模，儲存視訊圖片，按欄位切分，形成多個路徑。
- 增加快取層
減少資料庫連線也是一種優化手段，有些查詢可以不用訪問資料庫，可以通過使用快取伺服器如redis、memcache、elasticsearch等增加快取，減少對資料庫服務的直接訪問連線。
引數配置調優
1.全域性引數設定
查詢mysql伺服器執行各種狀態：show global status
查詢mysql伺服器配置資訊：show variables
檢視是否啟用了日誌：show variables like 'log_%'
檢視日誌狀態：show master logs show master status
檢視當前會話的查詢成本：show status like 'last_query_cost'
2連線數設定
show variables like 'max_connection'
show global status like 'max_used_connections'
修改連線數：set GLOBAL max_connections=2000
3.表掃描
show global status like 'handler_read_rnd_next'
show global status like 'com_select'
表掃描率：handler_read_rnd_next/com_select <4000,超過4000，說明進行了太多的表掃描
4.日誌相關
檢查日誌列印型別：show variables like 'innodb_flush_log_at_trx_commit' ，當型別為0的時候，mysql crash可能會出現資料丟失，可靠性不高，主要測試1和2兩種模式
檢視每個日誌檔案大小引數：show variables like 'innodb_log_file_size' ,一般設定為64~512M
5.超時設定
show variables like 'wait_timeout' ,根據業務場景修改超時時間vi /etc/my.cnf
show variables like 'interactive_time'
SQL優化
主要定位SQL瓶頸的方式：
1.檢查是否開啟了慢查詢
show variables like '%query%'
常用的分析慢查詢的語句：
mysqldumpslow -s c -t 20 /var/log/mysql/slowquery.log 檢視訪問次數最多的20個SQL語句
mysqldumpslow -s r -t 20 /var/log/mysql/slowquery.log 檢視返回記錄集最多的20個SQL語句
mysqldumpslow -t 10 -s t -g "left join" /var/log/mysql/slowquery.log 按照時間返回前10條包含左連結的SQL語句
引數說明：-s 表示按照什麼方式排序，c,t,l,r 表示按照次數，時間，查詢時間，返回記錄數來排序；-t 表示top n的意思；-g 後面可以寫正規表示式，大小寫不敏感。
2.explain語句分析
SQL優化總的調優策略及注意事項：
1.檢查select語句中涉及到的表欄位上的索引，若沒有就考慮加上
2.涉及到範圍欄位不需要加索引，有則去掉
3.主鍵無需增加索引
4.select語句中有左右連線，應該首先考慮給後面關聯的表增加索引
5.sql語句過長且用到臨時表時，可拆分sql語句；（使用in操作拼接sql）
6.儘量避免select * 查全表欄位
7.儘量用join代替子查詢
8.儘量少使用or,用in或者out代替
9.儘量inout代替out
10.儘量早的將無用資料過濾，選擇更優化的索引，先分頁再join
20條奇技淫巧：
- 避免在索引列上進行運算， 這將導致引擎放棄使用索引而進行全表掃描。
- 不使用NOT IN和<>操作， NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id<>9則可使用id>9 or id<9來代替。
- 檢查where條件與order by 欄位，避免全表掃描。
- 應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：select id from t where num = 0
- 應儘量避免在 where 子句中使用 or 來連線條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描。可以拆分條件，進行子句的union all查詢，如：select id from t where num=10 or name = 'admin' 拆分select id from t where num = 10 union all select id from t where name = 'admin'
- in 和 not in 也要慎用，否則會導致全表掃描，如：select id from t where num in(1,2,3) 對於連續的數值，能用 between 就不要用 in 了：select id from t where num between 1 and 3，
- 用 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)
- like語句的%不要前置， 否則索引失效將導致全表掃描。
- 如果在 where 子句中使用引數，也會導致全表掃描。 因為SQL只有在執行時才會解析區域性變數，但優化程式不能將訪問計劃的選擇推遲到執行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。
- 應儘量避免在where子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描。
- 不要在 where 子句中的“=”左邊進行函式、算術運算或其他表示式運算，否則系統將可能無法正確使用索引。
- 在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。
- Update 語句，如果只更改1、2個欄位，不要Update全部欄位，否則頻繁呼叫會引起明顯的效能消耗，同時帶來大量日誌。
- 對於多張大資料量（這裡幾百條就算大了）的表JOIN，要先分頁再JOIN，否則邏輯讀會很高，效能很差。
- select count(*) from table；這樣不帶任何條件的count會引起全表掃描，並且沒有任何業務意義，是一定要杜絕的。
- 任何地方都不要使用 select * from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。
- 避免頻繁建立和刪除臨時表，以減少系統表資源的消耗。臨時表並不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重複引用大型表或常用表中的某個資料集時。但是，對於一次性事件， 最好使用匯出表。
- 在新建臨時表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然後insert。
- 儘量拆分大的 DELETE 或INSERT 語句，批量提交SQL語句。
- 儘量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。
快取機制分析優化
從4.0.1開始，MySQL提供了查詢緩衝機制。使用查詢緩衝，MySQL將SELECT語句和查詢結果存放在緩衝區中，今後對於同樣的 SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。根據MySQL使用者手冊，使用查詢緩衝最多可以達到238％的效率。
設定innodb_buffer_pool_size
這個引數主要作用是快取innodb表的索引，資料，插入資料時的緩衝，預設值128M，推薦作業系統記憶體的70%~80%為佳，設定方法vi my.cnf檔案。
query cache的執行狀態分析
show status like '%qcache%'
qcache_free_blocks：數目大說明可能有碎片。
query_cache_min_res_unit：是在4.1版本以後引入的，它指定分配緩衝區空間的最小單位，預設為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多，這就表明查詢結果都比較小，此時需要減小 query_cache_min_res_unit。
qcache_free_memory：快取中的空閒記憶體。
qcache_lowmem_prunes：快取出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數，這個數字如果在不斷增長，就表示可能碎片非常嚴重，或者記憶體很少 ，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩衝反而會影響效率，那麼可以考慮不用查詢緩衝。
qcache_hits：每次查詢在快取中命中時值增加，即命中快取數。
qcache_inserts：每次插入一個查詢到快取時值增加，即沒有命中快取數。
qcache_total_blocks：快取中塊的總數量。
query cache的設定狀態分析
show status like '%query_cache%'
query_cache_limit：允許cache的單條query結果集的最大容量，預設是1MB。
query_cache_size：設定query cache 所使用的記憶體大小，預設為0，大小必須是1024的整數倍。
query_cache_type：控制query cache功能的開關，可設定為0（OFF）,1(ON),2(DEMAND),其中0表示關閉query cache功能，任何情況下都不會使用query cache,1表示開啟query cache功能，但當select語句中使用sql_no_cache提示後，將不再使用query cache;2表示開啟query cache功能，但只有當select語句中使用了sql_cache提示後，才使用query cache，另外，對於寫密集型應用，不要輕易開啟查詢快取。如果你實在是忍不住，可以將query_cache_type設定為DEMAND。
幾個常用分析公式
- query cache命中率=qcache_hits/(qcache_hits+qcache_inserts)*100%
- 查詢快取碎片率=(qcache_free_blocks/qcache_total_blocks)*100% ,如果查詢快取碎片率超過20%，可以用FLUSH QUERYCACHE整理快取碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。
- 查詢快取利用率=（query_cache_size-qcache_free_memory）/query_cache_size*100%， 查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小;查詢快取利用率在80%以上而且Qcache_lowmem_prunes >50的話說明query_cache_size可能有點小，要不就是碎片太多。
優化提示
如果Qcache_lowmem_prunes 值比較大，表示查詢快取區大小設定太小，需要增大。
如果Qcache_free_blocks 較多，表示記憶體碎片較多，需要清理，flush query cache
高效能mysql中關於query_cache_min_res_unit大小調優給出的計算公式 query_cache_min_res_unit = (query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache
mysql在判斷是否命中快取時，任何字元的不同 ，不確定的函式都將導致快取無法命中，子查詢，儲存過程也將導致查詢快取無法命中。