## 1、數據準備 **演示數據庫** 使用MySQL提供的sakila數據庫，可以通過以下URL獲取這個演示數據庫 [https://dev.mysql.com/doc/index-other.html](https://dev.mysql.com/doc/index-other.html) sakila數據庫的表結構信息可以通過以下網站查看 [https://dev.mysql.com/doc/sakila/en/sakila-installation.html](https://dev.mysql.com/doc/sakila/en/sakila-installation.html) 數據庫基于MySQL5.5 版本 why？不同MySQL版本的優化器有一定的差別。 ## 2、查詢mysql的操作信息 ``` show status -- 顯示全部mysql操作信息 show status like "com_insert%"; -- 獲得mysql的插入次數; show status like "com_delete%"; -- 獲得mysql的刪除次數; show status like "com_select%"; -- 獲得mysql的查詢次數; show status like "uptime"; -- 獲得mysql服務器運行時間 show status like 'connections'; -- 獲得mysql連接次數 ``` show [session|global] status like .... 如果你不寫 [session|global] 默認是session 會話，只取出當前窗口的執行，如果你想看所有(從mysql 啟動到現在，則應該 global) >通過查詢mysql的讀寫比例,可以做相應的配置優化; ## 3、慢查詢運行狀態 當Mysql性能下降時，通過開啟慢查詢來獲得哪條SQL語句造成的響應過慢，進行分析處理。**當然開啟慢查詢會帶來CPU損耗與日志記錄的IO開銷，所以我們要間斷性的打開慢查詢日志來查看Mysql運行狀態。** 慢查詢能記錄下所有執行超過long_query_time時間的SQL語句, 用于找到執行慢的SQL, 方便我們對這些SQL進行優化. ``` show variables like "%slow%";-- 是否開啟慢查詢; show status like "%slow%"; -- 查詢慢查詢SQL狀況; show variables like "long_query_time"; -- 慢查詢時間 ``` ## 4、慢查詢開啟設置 ``` mysql> show variables like 'long_query_time'; -- 默認情況下，mysql認為10秒才是一個慢查詢 +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ mysql> set long_query_time=1; -- 修改慢查詢時間,只能當前會話有效;(通常為100ms或者0.01m) mysql> set global slow_query_log='ON';-- 啟用慢查詢 ,加上global，不然會報錯的; set global slow_query_log_file=/'home/mysql/sql_log/mysql-slow.log' //記錄日志的log文件 set global log_queries_not_using_indexes=on;（開啟 記錄沒有使用索引查詢語句） ``` 也可以在配置文件中更改 修改mysql配置文件my.ini[windows]/my.cnf[Linux]加入,**注意必須在[mysqld]后面加入** ``` slow_query_log = on -- 開啟日志; slow_query_log_file = /data/f/mysql_slow_cw.log -- 記錄日志的log文件; 注意:window上必須寫絕對路徑,比如 D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log long_query_time = 2 -- 最長查詢的秒數; log-queries-not-using-indexes -- 表示記錄沒有使用索引的查詢 ``` ## 5、使用慢查詢 ``` SELECT * FROM `emp` where ename like '%mQspyv%'; -- 1.163s # Time: 150530 15:30:58 -- 該查詢發生在2015-5-30 15:30:58 // 執行SQL的主機信息 # User@Host: root[root] @ localhost [127.0.0.1] -- 是誰,在什么主機上發生的查詢 //SQL的執行信息 # Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 -- Query_time: 查詢總共用了多少時間,Lock_time: 在查詢時鎖定表的時間,Rows_sent: 返回多少rows數據,Rows_examined: 表掃描了400W行數據才得到的結果; //SQL執行事件 SET timestamp=1432971058; -- 發生慢查詢時的時間戳; // SQL的內容 SELECT * FROM `emp` where ename like '%mQspyv%'; ``` 開啟慢查詢后每天都有可能有好幾G的慢查詢日志,這個時候去人工的分析明顯是不實際的; ## 6、慢查詢日志分析工具之mysqldumpslow 該工具是慢查詢自帶的分析慢查詢工具，一般只要安裝了mysql，就會有該工具； ``` Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] -- 后跟參數以及log文件的絕對地址; -s, 是表示按照何種方式排序， c、記錄次數 t、時間 l、查詢時間 r、返回的記錄數 ac、at、al、ar，表示相應的倒敘； -t, 是top n的意思，即為返回前面多少條的數據； -g, 后邊可以寫一個正則匹配模式，大小寫不敏感的； ``` **常見用法** ``` mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10條慢查詢 mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查詢時間最慢的3條慢查詢 mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log # 得到按照時間排序的前10條里面含有左連接的查詢語句 mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照掃描行數最多的 ``` >注意: 使用mysqldumpslow的分析結果不會顯示具體完整的sql語句,只會顯示sql的組成結構; 假如: SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000; mysqldumpslow來顯示 ``` Count: 1 Time=1.91s (1s) Lock=0.00s (0s) Rows=1000.0 (1000), root[root]@[10.130.229.196] SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N; Count:執行次數、Time:執行時間、Lock：鎖定時間、ROWS：發送的行數、root：由那個服務器執行 SQL的具體內容 ``` ## 7、慢查詢日志分析工具之pt-query-digest pt-query-digest是用于分析mysql慢查詢的一個工具，它可以分析binlog、General log、slowlog，也可以通過SHOWPROCESSLIST或者通過tcpdump抓取的MySQL協議數據來進行分析。可以把分析結果輸出到文件中，分析過程是先對查詢語句的條件進行參數化，然后對參數化以后的查詢進行分組統計，統計出各查詢的執行時間、次數、占比等，可以借助分析結果找出問題進行優化。 ### 7.1、語法及重要選項 `pt-query-digest [OPTIONS] [FILES] [DSN]` ``` --create-review-table 當使用--review參數把分析結果輸出到表中時，如果沒有表就自動創建。 --create-history-table 當使用--history參數把分析結果輸出到表中時，如果沒有表就自動創建。 --filter 對輸入的慢查詢按指定的字符串進行匹配過濾后再進行分析 --limit限制輸出結果百分比或數量，默認值是20,即將最慢的20條語句輸出，如果是50%則按總響應時間占比從大到小排序，輸出到總和達到50%位置截止。 --host mysql服務器地址 --user mysql用戶名 --password mysql用戶密碼 --history 將分析結果保存到表中，分析結果比較詳細，下次再使用--history時，如果存在相同的語句，且查詢所在的時間區間和歷史表中的不同，則會記錄到數據表中，可以通過查詢同一CHECKSUM來比較某類型查詢的歷史變化。 --review 將分析結果保存到表中，這個分析只是對查詢條件進行參數化，一個類型的查詢一條記錄，比較簡單。當下次使用--review時，如果存在相同的語句分析，就不會記錄到數據表中。 --output 分析結果輸出類型，值可以是report(標準分析報告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。 --since 從什么時間開始分析，值為字符串，可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點，也可以是簡單的一個時間值：s(秒)、h(小時)、m(分鐘)、d(天)，如12h就表示從12小時前開始統計。 --until 截止時間，配合—since可以分析一段時間內的慢查詢。 ``` ### 7.2、標準的分析報告解釋 * 第一部分,總體統計結果;  ``` Overall: 總共有多少條查詢，上例為總共9100個查詢。 unique: 唯一查詢數量，即對查詢條件進行參數化以后，總共有多少個不同的查詢，該例為1170。 Time range: 查詢執行的時間范圍。 total: 總計 min:最小 max: 最大 avg:平均 95%: 把所有值從小到大排列，位置位于95%的那個數，這個數一般最具有參考價值。 median: 中位數，把所有值從小到大排列，位置位于中間那個數。 Exec time：總執行時間 Lock time:鎖定事件 Rows sent：發送的行數 Rows examine：掃描的行數 Query size： ``` * 第二部分,查詢分組統計結果;  由上圖可見，這部分對查詢進行參數化并分組，然后對各類查詢的執行情況進行分析，結果按總執行時長，從大到小排序。 ``` Response: 總的響應時間。 time: 該查詢在本次分析中總的時間占比。 calls: 執行次數，即本次分析總共有多少條這種類型的查詢語句。 R/Call: 平均每次執行的響應時間。 Item : 查詢對象 ``` * 第三部分,每一種查詢的詳細統計結果;  ` pct：占用百分比` 由上圖可見，25號查詢的詳細統計結果，最上面的表格列出了執行次數、最大、最小、平均、95%等各項目的統計。  ``` Databases: 庫名 Users: 各個用戶執行的次數（占比） Query\_time distribution : 查詢時間分布, 長短體現區間占比，本例中1s-10s之間查詢數量是10s以上的兩倍。 Tables: 查詢中涉及到的表 Explain: 示例 ``` ### 7.3、用法示例 (1)直接分析慢查詢文件: ``` pt-query-digest slow.log > slow_report.log ``` (2)分析最近12小時內的查詢： ``` pt-query-digest --since=12h slow.log > slow_report2.log ``` (3)分析指定時間范圍內的查詢： ``` pt-query-digest slow.log --since '2014-05-17 09:30:00' --until '2014-06-17 10:00:00'> > slow_report3.log ``` (4)分析只含有select語句的慢查詢 ``` pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log ``` (5) 針對某個用戶的慢查詢 ``` pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log ``` (6) 查詢所有所有的全表掃描或full join的慢查詢 ``` pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log ``` (7)把查詢保存到test數據庫的query_review表,如果沒有的話會自動創建; ``` pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table slow.log ``` (8)把查詢保存到query_history表 ``` pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history --create-review-table slow.log_20140401 ``` (9)通過tcpdump抓取mysql的tcp協議數據，然后再分析 ``` tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log ``` (10)分析binlog ``` mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log ``` (11)分析general log ``` pt-query-digest --type=genlog localhost.log > slow_report11.log ``` >另外,還有一款Query-digest-UI監控慢可視化查詢應用，后續再玩； ## 8、如何通過慢查詢日志發現有問題的SQL * 查詢次數多且每次查詢占用時間長的SQL * 通常為pt-query-digest分析的前幾個查詢 * 執行次數多，占用的百分比大 * IO大的SQL * 注意pt-query-digest分析中的Rows examine項 * 掃描行數越多，IO消耗越大 * 未命中索引的SQL * 注意pt-query-digest分析中Rows examine和Rows Send的對比 * Rows examine &gt; Rows Send時，說明索引命中率不高 ## 9、通過explain查詢和分析SQL的執行計劃 所謂索引就是為特定的mysql字段進行一些特定的算法排序,比如二叉樹的算法和哈希算法,哈希算法是通過建立特征值,然后根據特征值來快速查找,而用的最多,并且是mysql默認的就是二叉樹算法 BTREE,通過BTREE算法建立索引的字段,比如掃描20行就能得到未使用BTREE前掃描了2^20行的結果。 EXPLAIN可以幫助開發人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及連接表,可以幫助選擇更好的索引和寫出更優化的查詢語句.  **explain返回各列的含義** * select_type：查詢類型 * simple 簡單select(不使用union或子查詢) * primary 最外面的select * union union中的第二個或后面的select語句 * dependent union union中的第二個或后面的select語句,取決于外面的查詢 * union result union的結果。 * subquery 子查詢中的第一個select * dependent subquery 子查詢中的第一個select,取決于外面的查詢 * derived 導出表的select(from子句的子查詢) * table：顯示這一行的數據是關于哪張表的； * type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為： * const：常數查找，一般對主鍵或唯一索引。 * eq\_reg：范圍查找，一般對主鍵或唯一索引。 * ref：連接查詢，一個表是基于某一個索引的查找 * range：基于索引的范圍查找 * index：索引的掃描 * ALL：表掃描 * possible\_keys：顯示可能應用在這張表的索引。如果為空，沒有可能的索引。 * key：實際使用的索引。如果為NULL，則沒有使用索引。 * key\_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好。 * ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。 * rows：MYSQL認為必須檢查的用來返回請求數據的行數。 * extra：擴展列，需要注意以下兩個返回值。 * Using filesort：看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行 * Using temporary：看到這個的時候，查詢需要優化了。這里，MYSQL需要創建一個臨時表存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上。 ## 7、Count（）和Max（）的優化 **查詢最后支付時間-優化max\(\)函數** * explain select max\(payment\_date\) from payment; *  * 如上圖，該SQL進行全表掃描，共掃描了671行，由此可見效率不高。 * create index idx\_paydate on payment\(payment\_date\); *  * 由上圖的 Select tables optimized away 可以看出它并不需要實際的查詢表的數據，只需要通過索引就可以知道SQL的執行結果了。 **在一條SQL中同時查出2006年和2007年電影的數量--優化count\(\)函數** * 錯誤的方式 * SELECT COUNT\(release\_year='2006' OR release\_year='2007'\) FROM film; 該計算會返回一列，無法分開計算2006和2007年的電影數量。 * 正確的方式 * SELECT COUNT\(release\_year='2006' OR NULL\) AS '2006年電影數量',COUNT\(release\_year='2007' OR NULL\) AS '2007年電影數量' FROM film； * count\(\*\)和count\(id\)的區別 * count\(\*\)會統計所有行，包含NULL * count\(id\) 的統計，不含NULL ## 8、子查詢的優化 `通常情況下，需要把子查詢優化為join查詢，但在優化時要注意關聯鍵是否有一對多的關系，要注意重復數據。` ## 9、group by的優化 * 優化前 ```sql explain SELECT actorfirst_name,actor.last_name,COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id; ``` * 優化后 ```sql explain SELECT actor.first_name,actor.last_name,c.cnt FROM sakila.actor INNER JOIN ( SELECT actor_id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id ) AS c USING(actor_id); ``` ## 10、Limit查詢的優化 `limit常用于分頁處理，時常會伴隨order by從句使用，因此大多時候會使用Filesorts 這樣會造成大量的IO問題` * SELECT film\_id,description FROM sakila.film ORDER BY title LIMIT 50,5; *  * 如上圖，該SQL進行了表掃描的操作，同時使用了文件排序的方式，因此在數據量非常大的情況下會產生大量的IO。 * 優化1：使用主鍵索引進行排序 ```sql SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5 ``` *  * 可以看出改寫之后的SQL只掃描了55行。 * 優化2：記錄上次返回的主鍵，在下次查詢時使用主鍵過濾。 ```sql SELECT film_id,description FROM sakila.film WHERE film_id > 55 and film_id <=60 ORDER BY film_id LIMIT 1,5 ``` *  * 避免了數據量大時掃描過多的記錄 * 缺點是，主鍵必須順序排列。如果中間缺少，會出現過濾的記錄不足5行的情況。 * 可以建立一個index\_id，保證此列是自增的，并加上索引。