在上一篇文章中學習了如何使用pt-query-digest分析RDS慢查詢日志，而在本文中我們將進一步學習如何來優化慢查詢
<dfn id='hpQVW'><optgroup id='MQCa3'></optgroup></dfn><tfoot id='pIeUF'><bdo id='vIgdu'><div id='D4Tp5'></div><i id='xk35I'><dt id='xxMnW'></dt></i></bdo></tfoot> 一、什么是慢查詢？
慢查詢是指在數據庫中執行的查詢操作，其執行時間超過了一定的閾值（這個閾值通常是根據實際情況設定的在MySQL中受long_query_time控制）。
二、慢查詢會有什么現象？
監控層面:
慢查詢現象出現的時候，一般伴隨著多個監控指標同時飆升，例如：CPU 使用率、慢查詢數量、innodb行讀。
業務層面:
- 系統響應遲緩：在執行相關查詢時，整個系統的響應變得很慢，其他操作也受到影響。
- 資源占用異常：可能會導致 CPU、內存等資源被過度占用，影響其他進程的正常運行。
- 頁面加載緩慢：如果涉及到數據庫查詢的網頁或應用界面，會出現加載緩慢甚至長時間無響應的情況。
- 查詢執行時間過長：明顯超出正常預期的時間來完成查詢操作，導致用戶等待時間增加。
- 大量并發時性能急劇下降：在高并發場景下，慢查詢會更加突出，導致整體性能大幅下滑。
三、慢查詢相關的參數
slow_query_log用于開啟或關閉慢查詢日志。
slow_query_log_file指定慢查詢日志的存儲文件路徑。
long_query_time慢查詢閾值，超過這個時間的查詢將被記錄為慢查詢，默認值通常是 10 秒。
log_queries_not_using_indexes如果設置為 ON，則會將沒有使用索引的查詢記錄到慢查詢日志中，有助于提前規避一些隱患。
innodb_buffer_pool_size緩沖池大小，該部分緩存是 InnoDB 引擎最重要的緩存區域，是通過內存來彌補物理數據文件的重要手段，在云數據庫 MySQL 上會采用實例規格配置的25%作為該部分大小。其中主要包含數據頁、索引頁、undo 頁、insert buffer、自適應哈希索引、鎖信息以及數據字典等信息。在進行 SQL 讀和寫的操作時，首先并不是對物理數據文件操作，而是先對 buffer_pool 進行操作，再通過 checkpoint 等機制寫回數據文件。該空間的優點是可以提升數據庫的性能、加快 SQL 運行速度，缺點是故障恢復速度較慢。對查詢性能有較大影響，具體說明可以查下MySQL官方文檔，根據業務情況一般建議設置在65%左右。
query_cache_size 主要用于控制查詢緩存的大小，當相同的查詢再次執行時，如果結果在查詢緩存中，就可以直接從緩存中獲取結果，避免了重復執行查詢的開銷，從而可以提高查詢性能。這個參數限制也是有的，比如對于查詢SQL的匹配度要完全相同才會命中，其次對于經常需要更新的表也不適用。當查詢緩存變得過大時，管理和維護它也會消耗一定資源。不是所有類型的查詢都適合緩存，一些復雜的、動態性強的查詢可能不太適合放入查詢緩存。它的工作流程如下：-
- 當一個SELECT語句執行時，MySQL會先檢查查詢緩存，是否有該查詢的結果。
- 如果緩存中有該查詢的結果，則直接返回結果給客戶端。
- 如果緩存中沒有該查詢的結果，則執行查詢語句，將結果存儲到緩存中，并返回結果給客戶端。
thread_cache_size該參數用于指定線程緩存的大小。優點就是可以減少頻繁創建和銷毀連接線程的開銷，提高連接處理的效率。當有新的連接請求時，首先會嘗試從線程緩存中獲取可用的線程，而不是立即創建新的線程。需要注意的是需要根據系統的實際負載和連接情況來合理設置。如果設置得太小，可能無法充分利用線程緩存的優勢；如果設置得太大，可能會浪費系統資源。要結合服務器的并發連接數、連接的活躍程度等因素來綜合考慮。通常需要通過實際測試和觀察來確定一個較為合適的值，以在性能和資源利用之間達到較好的平衡。結合物理內存設置：根據系統的物理內存大小來調整 thread_cache_size。可以參考以下則：
1G 內存：thread_cache_size=8
2G 內存：thread_cache_size=16
3G 內存：thread_cache_size=32
大于 3G 內存：thread_cache_size=60+4
四、慢查詢通常情況下是 SQL 語句的執行效率不夠高，導致大量的請求堆積在云數據庫 MySQL 中，常見原因有兩個
- 原因1：SQL 設計不合理，語句沒有利用索引或者沒有用較佳的索引，簡單舉例一下走索引和不走索引的區別。
1.準備一張t1的測試表
CREATE TABLE "t1" (
"id" int(11) NOT NULL,
"name" varchar(100) DEFAULT NULL,
"insert_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ("id")
) ;2.通過explain分析慢SQL，explain字段詳細說明
mysql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10161 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)| id | 表示查詢中每個子查詢或操作的編號 |
| select_type | 描述查詢的類型，如簡單查詢、子查詢等 |
| table | 涉及的表名 |
| partitions | 相關的分區信息 |
| type | 表示索引的訪問類型，如全表掃描、索引范圍掃描等，它反映了查詢的效率 |
| possible_keys | 可能用到的索引 |
| key | 實際使用的索引 |
| key_len | 索引鍵的長度 |
| ref | 表示與索引進行比較的對象 |
| rows | 估計需要掃描的行數 |
| filtered | 表示過濾后剩余結果的比例 |
| Extra | 包含一些額外的執行信息，如是否使用了索引等 |
2.用id字段和name字段作為條件，分別查看他們的執行計劃有什么區別。
mysql> explain select * from t1 where id =2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 where name ='dengmeng';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10161 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)3.通過對比我們發現以id為條件，走了主鍵索引，掃描行數是1行執行效率很不錯的，而通過name為條件時進行了全表掃描，沒有走到索引。那么業務就需要通過name條件查詢時，該如何優化呢？我們可以給name列也加上一個索引。
alter table t1 add index index_name (name);4.在查看執行計劃發現效果很明顯，從全表掃描降低到只掃描1行數據，大大提升了SQL執行效率。對于大表查詢沒有走索引是很容易造成慢查詢，從而導致業務響應慢。還有一種情況是走了索引缺還是查詢慢，這種就需要根據業務情況具體分析，是索引失效或是索引區分度不高，再或者是表的數據量非常之大本身就很慢。
mysql> explain select * from t1 where name='dengmeng';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | index_name | index_name | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)5.索引優化參考官方文檔：
優化建議：
- 單表的索引數量不應超過5個，單個索引中的字段數不應超過5個，以避免索引過多的過濾作用和資源消耗。
- 確保索引字段長度固定且不宜過長。過長的索引字段會占用更多磁盤空間，并影響索引的性能。
- 避免冗余索引，即存在兩個索引 (a,b) 和 (a) 的情況，若查詢條件為a列，只需建立 (a,b) 索引即可，不需要額外建立 (a) 索引。
- 對于高過濾性的字段，考慮在其上加索引。高過濾性字段的索引可以提高查詢效率。
- 注意選擇性和數據類型。選擇性高的字段和合適的數據類型可以提高索引效果和查詢性能。
- 合理利用覆蓋索引來減少IO開銷，通過創建包含所需列的復合索引，避免回表操作。
注意事項：
- 禁止在更新頻繁且區分度低的列上建立索引，這會降低數據庫性能。
- 在使用LIKE關鍵字時，避免在搜索模式字符串之前使用前置%符號。這樣做會導致索引失效，影響查詢性能。
- 避免在具有空值的列上創建索引。一般情況下，索引不會建立在含有空值的列上。
- 在使用OR關鍵字時，確保OR操作符的左右字段都有索引。如果一個字段有索引而另一個字段沒有索引，有索引的字段也會失效。
- 避免使用!=操作符。范圍的不確定性導致使用索引效率低下，數據庫引擎會自動將其改為全表掃描。
- 不要在索引字段上進行運算。這會導致索引失效，降低查詢性能。
- 使用復合索引時，應該遵循最左前綴原則，并確保查詢中使用索引的第一個字段，否則索引將失效。為了最大化索引的效率，應該盡量保持查詢中字段的順序與索引的順序一致。
- 原因2：QPS 壓力超過當前實例的承載上限
處理措施：提升云數據庫 MySQL 配置。
1.登錄管理控制臺。
2.單擊管理控制臺左上角的資源池，選擇區域和項目。
3.選擇“數據庫 > 關系數據庫MySQL版”，進入關系數據庫MySQL版控制臺。
4.在“實例管理”頁面， 選擇指定的實例，單擊“更多”選擇規格擴容操作，進入規格擴容頁面，對實例進行擴容。
