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ù)庫服務層建立TCP連接。
2、客戶端向MySQL服務器發(fā)送一條查詢請求。
3、連接線程接收到SQL語句之后，將語句交給SQL語句解析模塊進行語法分析和語義分析。
4、先看查詢緩存中是否有結(jié)果，如果有結(jié)果可以直接返回給客戶端。
5、如果查詢緩存中沒有結(jié)果，就需要真的查詢數(shù)據(jù)庫引擎層了，于是發(fā)給SQL優(yōu)化器，進行查詢的優(yōu)化，生成相應的執(zhí)行計劃。
6、MySQL根據(jù)執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢
7、使用存儲引擎查詢時，先打開表，如果需要的話獲取相應的鎖。 查詢緩存頁中有沒有相應的數(shù)據(jù)，如果有則可以直接返回，如果沒有就要從磁盤上去讀取。
8、當在磁盤中找到相應的數(shù)據(jù)之后，則會加載到緩存中來，從而使得后面的查詢更加高效，由于內(nèi)存有限，多采用變通的LRU表來管理緩存頁，保證緩存的都是經(jīng)常訪問的數(shù)據(jù)。
9、最后，獲取數(shù)據(jù)后返回給客戶端，關閉連接，釋放連接線程。
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ù)作為基礎的?！?/p>
PROCEDURE ANALYSE的語法如下：
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
max_elements:指定每列非重復值的最大值，當超過這個值的時候，MySQL不會推薦enum類型。（默認值256）
max_memory （默認值8192）analyse()為每列找出所有非重復值所采用的最大內(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則是未開啟，改配置文件設置 query_cache_type=YES后重啟
2、查看buffer 執(zhí)行 show variables like 'innodb_buffer_pool%'，可以看出innodb_buffer_pool_size只有8M大小
這里設置成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（可以翻譯為“散列程度”），它表示某個索引對應的列包含多少個不同的值。如果cardinality大大少于數(shù)據(jù)的實際散列程度，那么索引就基本失效了。
使用命令：analyze table table_name修復索引，如果修復失敗，查看是否因為新添加的字段設置了相同的默認值
總結(jié)
從上面這個例子我們可以看出analyze能根據(jù)目前表中的數(shù)據(jù)情況給出優(yōu)化建議。當數(shù)據(jù)庫在生產(chǎn)環(huán)境運行一定時間以后，開發(fā)或是DBA能參考analyze的分析結(jié)果來對表結(jié)構(gòu)做出一定的優(yōu)化。
參考：
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
