sql 語句性能分析
1、看 sql 語句執(zhí)行時間
2、看 sql 的執(zhí)行計劃
3、查看 sql 的執(zhí)行中各個環(huán)節(jié)耗時時間
4、查看mysql的執(zhí)行進程，處理鎖表的情況，命令 show PROCESSLIST， state 為LOCKED，說明產(chǎn)生鎖表，ID為進程id，直接執(zhí)行kill ID，就可以停止這個進程；
MySQL整個查詢執(zhí)行過程：
1、客戶端同數(shù)據(jù)庫服務(wù)層建立TCP連接。
2、客戶端向MySQL服務(wù)器發(fā)送一條查詢請求。
3、連接線程接收到SQL語句之后，將語句交給SQL語句解析模塊進行語法分析和語義分析。
4、先看查詢緩存中是否有結(jié)果，如果有結(jié)果可以直接返回給客戶端。
5、如果查詢緩存中沒有結(jié)果，就需要真的查詢數(shù)據(jù)庫引擎層了，于是發(fā)給SQL優(yōu)化器，進行查詢的優(yōu)化，生成相應(yīng)的執(zhí)行計劃。
6、MySQL根據(jù)執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢
7、使用存儲引擎查詢時，先打開表，如果需要的話獲取相應(yīng)的鎖。 查詢緩存頁中有沒有相應(yīng)的數(shù)據(jù)，如果有則可以直接返回，如果沒有就要從磁盤上去讀取。
8、當(dāng)在磁盤中找到相應(yīng)的數(shù)據(jù)之后，則會加載到緩存中來，從而使得后面的查詢更加高效，由于內(nèi)存有限，多采用變通的LRU表來管理緩存頁，保證緩存的都是經(jīng)常訪問的數(shù)據(jù)。
9、最后，獲取數(shù)據(jù)后返回給客戶端，關(guān)閉連接，釋放連接線程。
Procedure Analyse優(yōu)化表結(jié)構(gòu)
PROCEDURE ANALYSE() ，在優(yōu)化表結(jié)構(gòu)時可以輔助參考分析語句。通過分析select查詢結(jié)果對現(xiàn)有的表的每一列給出優(yōu)化的建議。
利用此語句，MySQL 幫你去分析你的字段和其實際的數(shù)據(jù)，并會給你一些有用的建議。
【只有表中有實際的數(shù)據(jù)，這些建議才會變得有用，因為要做一些大的決定是需要有數(shù)據(jù)作為基礎(chǔ)的?！?/p>
PROCEDURE ANALYSE的語法如下：
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
max_elements:指定每列非重復(fù)值的最大值，當(dāng)超過這個值的時候，MySQL不會推薦enum類型。（默認值256）
max_memory （默認值8192）analyse()為每列找出所有非重復(fù)值所采用的最大內(nèi)存大小。
執(zhí)行返回中的Optimal_fieldtype列是mysql建議采用的列。
樣例程序
mysql> DESC user_account;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| USERID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| USERNAME | varchar(10) | NO | | NULL | |
| PASSSWORD | varchar(30) | NO | | NULL | |
| GROUPNAME | varchar(10) | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
rows in set (0.00 sec)
mysql> select * from user_account PROCEDURE ANALYSE(1)\G;
*************************** 1. row ***************************
Field_name: ibatis.user_account.USERID
Min_value: 1
Max_value: 103
Min_length: 1
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 51.7500
Std: 50.2562
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 2. row ***************************
Field_name: ibatis.user_account.USERNAME
Min_value: dfsa
Max_value: LMEADORS
.........................................................
從第一行輸出我們可以看到analyze分析ibatis.user_account.USERID列
最小值1，最大值103，最小長度1，最大長度3，字符串或0的個數(shù)為0；值為空的字段數(shù)為0；平均每個字段的值為51.7500
并給出了該字段的優(yōu)化建議：建議將該字段的數(shù)據(jù)類型改成TINYINT(3) UNSIGNED NOT NULL。
查詢語句出現(xiàn)sending data耗時解決
執(zhí)行SHOW PROFILES 查出Query_ID后在執(zhí)行 show profile for query Query_ID ，或者是show processlist，查看查詢所耗時資源
得知查詢到語句耗時主要集中在 sending data上
解決步驟：
1、查詢資料需要開啟查詢緩存，執(zhí)行命令查看開啟情況 show variables like '%query_cache%'，No則是未開啟，改配置文件設(shè)置 query_cache_type=YES后重啟
2、查看buffer 執(zhí)行 show variables like 'innodb_buffer_pool%'，可以看出innodb_buffer_pool_size只有8M大小
這里設(shè)置成3G，SET GLOBAL innodb_buffer_pool_size = 3221225472，然后重啟mysql（參考：https://www.cnblogs.com/wanbin/p/9530833.html）
3、查看表索引，show index from table_name發(fā)現(xiàn)cardinality的值為1，表的cardinality（可以翻譯為“散列程度”），它表示某個索引對應(yīng)的列包含多少個不同的值。如果cardinality大大少于數(shù)據(jù)的實際散列程度，那么索引就基本失效了。
使用命令：analyze table table_name修復(fù)索引，如果修復(fù)失敗，查看是否因為新添加的字段設(shè)置了相同的默認值
總結(jié)
從上面這個例子我們可以看出analyze能根據(jù)目前表中的數(shù)據(jù)情況給出優(yōu)化建議。當(dāng)數(shù)據(jù)庫在生產(chǎn)環(huán)境運行一定時間以后，開發(fā)或是DBA能參考analyze的分析結(jié)果來對表結(jié)構(gòu)做出一定的優(yōu)化。
where條件排序字段以及l(fā)imit使用索引
背景
我們先來看2條sql
第一條：
select * from acct_trans_log WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10
第二條：
select * from acct_trans_log WHERE acct_id = 1000000000009003061 order by create_time desc limit 0,10
表的索引及數(shù)據(jù)總情況：
索引：acct_id，create_time分別是單列索引，數(shù)據(jù)庫總數(shù)據(jù)為500w
通過acct_id過濾出來的結(jié)果集在1w條左右
查詢結(jié)果：第一條要5.018s，第二條0.016s
為什么會是這樣的結(jié)果呢？第一，acct_id和create_time都有索引，不應(yīng)該出現(xiàn)5s查詢時間這么慢啊
那么先來看執(zhí)行計劃
第一條sql執(zhí)行計劃：
第二條執(zhí)行計劃：
仔細觀察會發(fā)現(xiàn)，索引只使用了idx_create_time，沒有用到idx_acct_id
這能解釋第一條sql很慢，因為where查詢未用到索引，那么第二條為什么這么快？
看起來匪夷所思，其實搞清楚mysql查詢的原理之后，其實很簡單
我們來看這2條sql查詢，都用到了where order by limit
當(dāng)有l(wèi)imit存在時，查詢的順序就有可能發(fā)生變化，這時并不是從數(shù)據(jù)庫中先通過where過濾再排序再limit
因為如果這樣的話，從500萬數(shù)據(jù)中通過where過濾就不會是5s了。
此時的執(zhí)行順序是，先根據(jù)idx_create_time索引樹，從最右側(cè)葉子節(jié)點，反序取出n條，然后逐條去跟where條件匹配
若匹配上，則得出一條數(shù)據(jù)，直至取滿10條為止，為什么第二條sql要快，因為運氣好，剛好時間倒序的前幾條就全部滿足了。
搞清楚原理之后，我們了解了為什么第一條慢，第二條快的原因，但是問題又來了
為什么mysql不用idx_acct_id索引，這是一個問題，因為這樣的話，我們的建立的索引基本失效了，在此類sql下
查詢效率將會是相當(dāng)?shù)?/p>
因為通過acct_id過濾出來的結(jié)果集比較大，有上萬條，mysql認為按時間排序如果不用索引，將會是filesort,這樣會很慢，而又不能2個索引都用上，所以選擇了idx_create_time。
為什么mysql只用一個索引
這里為什么不能2個索引都用上，可能很多人也不知道為什么，其實道理很簡單，每個索引在數(shù)據(jù)庫中都是一個索引樹，其數(shù)據(jù)節(jié)點存儲了指向?qū)嶋H
數(shù)據(jù)的指針，如果用一個索引來查詢，其原理就是從索引樹上去檢索，并獲得這些指針，然后去取出數(shù)據(jù)，試想，如果你通過一個索引，得到過濾后的指針，這時，你的另一個條件索引如果再過濾一遍，將得到2組指針的集合，如果這時候取交集，未必就很快，因為如果每個集合都很大的話，取交集的時候，等于掃描2個集合，效率會很低，所以沒法用2個索引。當(dāng)然有時候mysql會考慮臨時建立一個聯(lián)合索引，將2個索引聯(lián)合起來用，但是并不是每種情況都能奏效，同樣的道理，用一個索引檢索出結(jié)果集之后，排序時，也無法用上另一個索引了。
實際上用索引idx_acct_id大多數(shù)情況還是要比用索引idx_create_time要快，我們舉個例子：
select * from acct_trans_log force index(idx_acct_id) WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10
耗時：0.057s
可以看出改情況用idx_acct_id索引是比較快的，那么是不是這樣就可以了呢，排序未用上索引，始終是有隱患的。
聯(lián)合索引，讓兩個字段都用上索引
我們來看下一條sql:
select * from acct_trans_log force index(idx_acct_id) WHERE acct_id = 3095 order by create_time desc limit 0,10
耗時: 1.999s
執(zhí)行計劃：
該sql通過acct_id過濾出來的結(jié)果集有100萬條，因此排序?qū)臅r較高，所幸這里只是取出前10條最大的然后排序
查詢概況，我們發(fā)現(xiàn)時間基本消耗在排序上，其實這是內(nèi)存排序，對內(nèi)存消耗是很高的。
解決辦法就是建立聯(lián)合索引：
alter table acct_trans_log add index idx_acct_id_create_time(acct_id,create_time)
然后執(zhí)行sql:
select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10
耗時: 0.016s
聯(lián)合索引使用的原理
但是為什么能解決這個問題呢，下面我們再看一個sql：
select * from acct_trans_log force index(idx_acct_id_create_time) WHERE acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10
耗時：1.391s
索引還是用idx_acct_id_create_time，時間居然慢下來了?？磮?zhí)行計劃，排序用到了filesort，也就是說，排序未用到索引。
那么我們還是來看看，索引排序的原理，我們先來看一個sql:
select * from acct_trans_log ORDER BY create_time limit 0,100
耗時：0.029s
執(zhí)行計劃為：
這里執(zhí)行的步驟是，先從索引樹中，按時間升序取出前100條，因為索引是排好序的，直接左序遍歷即可了
因此，這里mysql并沒有做排序動作，如果想降序，則右序遍歷索引樹，取出100條即可，查詢固然快，
那么聯(lián)合索引的時候，是怎樣的呢？
select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10
使用組合索引：idx_acct_id_create_time
這個時候，因為acct_id是聯(lián)合索引的前綴，因此可以很快實行檢索，
如果sql是
select * from acct_trans_log WHERE acct_id = 3095
出來的數(shù)據(jù)是按如下邏輯排序的
3095+time1
3095+time2
3095+time3
默認是升序的，也就是說，次sql相當(dāng)于
select * from acct_trans_log WHERE acct_id = 3095 order by create_time
他們是等效的。
如果我們把條件換成order by create_time desc limit 0,10呢
這時候，應(yīng)該從idx_acct_id_create_time樹右邊葉子節(jié)點倒序遍歷，取出前10條即可
因為數(shù)據(jù)的前綴都是3095，后綴是時間升序。那么我們倒序遍歷出的數(shù)據(jù)，剛好滿足order by create_time desc
因此也無需排序。
那么語句：
select * from acct_trans_log force index(idx_acct_id_create_time) WHERE acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10
為什么排序無法用索引呢？
我們先分析下索引的排序規(guī)則
已知：id1<id2<id3... time1<time2<time3....
查詢結(jié)果集排序如下：
id1+time1
id1+time2
id1+time3
id2+time1
id2+time2
id2+time3
索引出來的默認排序是這樣的，id是有序的，時間是無序的，因為有2個id，優(yōu)先按id排序，時間就是亂的了，
這樣排序?qū)胒ilesort，這就是慢的原因，也是排序沒有用到索引的原因。
查詢計劃使用以及使用說明
table：顯示這一行數(shù)據(jù)是關(guān)于哪張表的
type：顯示使用了何種類型，從最好到最差的連接類型為const,eq_ref,ref,range,index,all
possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引
key：實際使用的索引，如果為null，則沒有使用索引。
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)
rows：mysql認為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)
參考：
1、Navicat常用功能：https://blog.csdn.net/littlexiaoshuishui/article/details/82884094
2、MySQL查詢過程剖析：https://blog.csdn.net/z_ryan/article/details/82262761
3、分析診斷工具之Procedure Analyse：https://www.cnblogs.com/duanxz/p/3968639.html
4、sending data耗時解決：https://www.cnblogs.com/yaoxing92/p/11058420.html、https://www.cnblogs.com/rwxwsblog/p/5684213.html
5、where條件排序字段以及l(fā)imit使用索引：https://www.cnblogs.com/tangyanbo/p/6378741.html
