分析總結
慢查詢開啟并捕獲
explan+慢sql分析
show profile查詢SQL在mysql伺服器裡面的執行細節和生命周期情況
sql資料庫伺服器的參數調優
1.查詢優化
優化原則：小表驅動大表，即小的資料集驅動大的資料集
EXISTS
SELECT * FROM table WHERE EXISTS (subquery)
該文法了解為：将主查詢的資料，放在子查詢中做條件驗證，根據驗證結果(true或false)來決定主查詢的資料結果是否得以保留
提示：
1.EXISTS(subquery)隻傳回true或false，是以子查詢中的select * 也可以寫成select 1 或select 'X' 等，官方說法是實際執行時會忽略select清單，是以沒有差別
2.EXISTS子查詢的實際執行過程可能經過了優化而不是我們了解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以确定是否有效率問題
3.EXISTS子查詢往往也可以用條件表達式，其他子查詢或者JOIN來替代，何種最優需要具體問題具體分析
#當B表的資料集必須小于A表的資料集時，用in優于exists.
select * from A where id in (select id fromB)
等價于for select id fromBfor select * from A where A.id = B.id
#當A表的資料集小于B表的資料集時，用exists優于in
select * from A where exists (select 1 from B where B.id =A.id)
等價于for select * fromAfor select * from B where B.id = A.id
ORDER BY關鍵字優化
盡量使用index方式來排序，避免使用filesort方式排序
mysql支援二種方式排序，filesort和index，index效率高，它指mysql掃描索引本身完成排序.filesort方式效率較低
order by 滿足兩種情況會用index方式排序：
order by語句使用索引最左字首
使用where子句與order by 子句條件列組合滿足索引最左字首
盡可能在索引列上完成排序操作，遵照索引建的最佳左字首
如果不在索引列上，filesort有兩種算法
雙路排序
mysql4.1之前是使用雙路排序，字面意思就是兩次掃描磁盤，最終得到資料，讀取行指針和orderby列，對他們進行排序，然後掃描已經排序好的清單，按照清單中的值重新從清單中讀取對應的資料輸出
從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段
單路排序
從磁盤讀取查詢需要的所有列，按照order by 列在 buffer對它們進行排序，然後掃描排序後的清單進行輸出，它的效率更快一些，避免了第二次讀取資料。并且把随機IO變成了順序IO，但它會使用更多的空間，
因為它把每一行都儲存在記憶體中
優化政策
增大sort_buffer_size參數的設定
增大max_length_for_sort_data參數的設定
提高order by 的速度
1.order by 時select * 是一個大忌，隻查詢需要的字段，這點很重要
1.1當查詢的字段大小總和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB類型時，會用改進後的算法--單路排序，否則用老算法--多路排序
1.2兩種算法的資料都有可能超出sort_buffer的容量，超出之後，會建立tmp檔案進行合并排序，導緻多次I/O,但用單排序算法 的風險會更大一些，是以要送出sort_buffer_size
2.嘗試提高sort_buffer_size
不管用哪種算法，提高這個參數都會提高效率，當然，要根據系統的能力去提高 ，因為這個參數是針對每個程序的
3.嘗試提高max_length_for_sort_data
提高這個參數，會增加用改進算法的效率，但是如果設的太高，資料總容量超出sort_buffer_size的機率就會增大，明顯症狀是高的磁盤I/O活動和低的處理器使用率
GROUP BY關鍵字優化
group by 實質是先排序後進行分組，遵照索引建的最佳左字首
當無法使用索引列，增大sort_buffer_size和max_length_for_sort_data的參數設定
where高于having，能寫在where限定條件就不要去having限定了
2.慢查詢日志
mysql的慢查詢日志是mysql提供的一種日志記錄，它用來記錄在mysql中響應時間超過閥值的語句，具體指運作時間超過long_query_time值的sql，則會被記錄到查詢日志中
具體指運作時間超過long_query_time值的sql，則會被記錄到慢查詢日志中，long_query_time的預設值為10，意思是運作10秒以上的語句
由他來檢視哪些SQL超出了我們的最大忍耐時間值，就記錄下SQL，結合之前explain進行全面分析
預設情況下，MySQL資料庫沒有開啟慢查詢日志，需要手動來設定這個參數
如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。慢查詢日志支援将日志記錄寫入檔案
指令：檢視 SHOW VARIABLES LIKE '%slow_query_log%'
啟動 set global slow_query_log = 1
檢視慢查詢long_query_time值 SHOW VARIABLES LIKE '%long_query_time%'
設定時間 set global long_query_time=3(秒)
得到傳回記錄集最多的10個sql
mysqldumpslow -s r -t 10 慢sql檔案
得到通路次數最多的10個sql
mysqldumpslow -s c -t 10 慢sql檔案
得到按照時間排序的前10條裡面含有左連接配接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" 慢sql檔案
建議在使用這些指令時結合|和more使用
mysqldumpslow -s r -t 10 慢sql檔案 | more
3.批量資料腳本
設定參數log_bin_trust_function_creators
建立函數，如果報錯This function has none of DETERMINISTIC......
由于開啟過慢查詢日志，因為開啟了bin-log，就必須為function指定一個參數
show variables like 'log_bin_trust_function_creators'
set global log_bin_trust_function_creators=1
這樣添後，重新開機mysql，就失效了，永久設定
windows下my.ini [mysqld]加上log_bin_trust_function_creators=1
linux下/etc/my.cnf [mysqld]加上log_bin_trust_function_creators=1
4.Show Profile
是mysql提供可以用來分析目前會話中語句執行的資源消耗情況。可以用于sql調優的測量
分析步驟
1)是否支援，看目前mysql版本是否支援(預設關閉)
show variables like 'profiling'
2)開啟
set profiling=on
3)運作相關sql
4)檢視結果
show profiles
5)診斷sql
類型
ALL：顯示所有的開銷資訊
BLOCK IO：顯示塊IO相關開銷
CONTEXT SWITCHES：上下文切換相關開銷
CPU：顯示CPU相關開銷資訊
IPC：顯示發送和接收相關開銷資訊
MEMORY：顯示記憶體相關開銷資訊
PAGE FAULTS：顯示頁面錯誤相關開銷資訊
SOURCE：顯示和soure_function,source_file,source_line相關的開銷資訊
SWAPS：顯示交換次數相關開銷資訊
常用的查詢分析：show profile cpu,block io for query Query_ID
6)日常開發需要注意的結論
converting HEAP to MyISAM 查詢結果太大，記憶體都不夠用了往磁盤上搬
Creating tmp table 建立臨時表
拷貝資料到臨時表
用完再删除
Copying to tmp table on disk 把記憶體中臨時表複制到磁盤，危險
locked
5.全局查詢日志(不要在生産環境中開啟這個功能)
配置啟用
在配置檔案下my.cnf
#開啟
general_log=1
#記錄日志檔案的路徑
general_log_file=/path/logfile
#輸出格式
log_output=FILE
編碼啟用
set global general_log=1;
set global log_output='TABLE';
此後，你所編寫的sql語句，都将記錄到mysql庫裡的general_log表裡
select * from mysql.general_log;