啥？我寫的一條SQL讓公司網站癱瘓了...

【51CTO.com原創稿件】一條慢查詢會造成什麼後果?之前我一直覺得不就是返回數據會慢一些麼，用戶體驗變差?

圖片來自 Pexels

其實遠遠不止，我經歷過幾次線上事故，有一次就是由一條 SQL 慢查詢導致的。

那次是一條 SQL 查詢耗時達到 2-3 秒「沒有命中索引，導致全表掃描」，由於是高頻查詢，併發一起來很快就把 DB 線程池打滿了，導致大量查詢請求堆積，DB 服務器 CPU 長時間 100%+，大量請求 timeout...

最終系統崩潰，老闆登場!可見，團隊如果對慢查詢不引起足夠的重視，風險是很大的。

經過那次事故我們老闆就說了：誰的代碼再出現類似事故，開發和部門領導一起走人，嚇得一大堆領導心發慌，趕緊招了兩位 DBA 同事。

慢查詢，顧名思義，執行很慢的查詢。有多慢?超過 long_query_time 參數設定的時間閾值(默認 10s)，就被認為是慢的，是需要優化的。慢查詢被記錄在慢查詢日誌裡。

慢查詢日誌默認是不開啟的，如果你需要優化 SQL 語句，就可以開啟這個功能，它可以讓你很容易地知道哪些語句是需要優化的(想想一個 SQL 要 10s 就可怕)。好了，下面我們就一起來看看怎麼處理慢查詢。

慢查詢配置

開啟慢查詢

MySQL 支持通過以下方式開啟慢查詢：

• 輸入命令開啟慢查詢(臨時)，在 MySQL 服務重啟後會自動關閉。
• 配置 my.cnf(Windows 是 my.ini)系統文件開啟，修改配置文件是持久化開啟慢查詢的方式。

方式一：通過命令開啟慢查詢

步驟 1：查詢 slow_query_log 查看是否已開啟慢查詢日誌：

show variables like '%slow_query_log%';

mysql> show variables like '%slow_query_log%';

+---------------------+-----------------------------------+

| Variable_name | Value |

+---------------------+-----------------------------------+

| slow_query_log | OFF |

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

+---------------------+-----------------------------------+

2 rows in set (0.01 sec)

步驟 2：開啟慢查詢命令：

set global slow_query_log='ON';

步驟 3：指定記錄慢查詢日誌 SQL 執行時間得閾值(long_query_time 單位：秒，默認 10 秒)。

如下我設置成了 1 秒，執行時間超過 1 秒的 SQL 將記錄到慢查詢日誌中：

set global long_query_time=1;

步驟 4：查詢 “慢查詢日誌文件存放位置”。

show variables like '%slow_query_log_file%';

mysql> show variables like '%slow_query_log_file%';

+---------------------+-----------------------------------+

| Variable_name | Value |

+---------------------+-----------------------------------+

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

+---------------------+-----------------------------------+

1 row in set (0.01 sec)

slow_query_log_file 指定慢查詢日誌的存儲路徑及文件(默認和數據文件放一起)。

步驟 5：核對慢查詢開啟狀態，需要退出當前 MySQL 終端，重新登錄即可刷新。

配置了慢查詢後，它會記錄以下符合條件的 SQL：

• 查詢語句
• 數據修改語句
• 已經回滾的 SQL

方式二：通過配置 my.cnf(Windows 是 my.ini)系統文件開啟(版本：MySQL 5.5 及以上)。

在 my.cnf 文件的 [mysqld] 下增加如下配置開啟慢查詢，如下圖：

# 開啟慢查詢功能

slow_query_log=ON

# 指定記錄慢查詢日誌SQL執行時間得閾值

long_query_time=1

# 選填，默認數據文件路徑

# slow_query_log_file=/var/lib/mysql/localhost-slow.log

重啟數據庫後即持久化開啟慢查詢，查詢驗證如下：

mysql> show variables like '%_query_%';

+------------------------------+-----------------------------------+

| Variable_name | Value |

+------------------------------+-----------------------------------+

| have_query_cache | YES |

| long_query_time | 1.000000 |

| slow_query_log | ON |

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

+------------------------------+-----------------------------------+

6 rows in set (0.01 sec)

慢查詢日誌介紹

如上圖，是執行時間超過 1 秒的 SQL 語句(測試)：

• 第一行：記錄時間。
• 第二行：用戶名 、用戶的 IP 信息、線程 ID 號。
• 第三行：執行花費的時間【單位：秒】、執行獲得鎖的時間、獲得的結果行數、掃描的數據行數。
• 第四行：這 SQL 執行的時間戳。
• 第五行：具體的 SQL 語句。

Explain 分析慢查詢 SQL

分析 MySQL 慢查詢日誌，利用 Explain 關鍵字可以模擬優化器執行 SQL 查詢語句，來分析 SQL 慢查詢語句。

下面我們的測試表是一張 137w 數據的 app 信息表，我們來舉例分析一下。

SQL 示例如下：

-- 1.185s

SELECT * from vio_basic_domain_info where app_name like '%翻譯%' ;

這是一條普通的模糊查詢語句，查詢耗時：1.185s，查到了 148 條數據。

我們用 Explain 分析結果如下表，根據表信息可知：該 SQL 沒有用到字段 app_name 上的索引，查詢類型是全表掃描，掃描行數 137w。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%翻譯%' ;

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

| 1 | SIMPLE | vio_basic_domain_info | NULL | ALL | NULL | NULL | NULL | NULL | 1377809 | 11.11 | Using where |

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

當這條 SQL 使用到索引時，SQL 如下：查詢耗時：0.156s，查到 141 條數據：

-- 0.156s

SELECT * from vio_basic_domain_info where app_name like '翻譯%' ;

Explain 分析結果如下表;根據表信息可知：該 SQL 用到了 idx_app_name 索引，查詢類型是索引範圍查詢，掃描行數 141 行。

由於查詢的列不全在索引中(select *)，因此回表了一次，取了其他列的數據。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '翻譯%' ;

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

| 1 | SIMPLE | vio_basic_domain_info | NULL | range | idx_app_name | idx_app_name | 515 | NULL | 141 | 100.00 | Using index condition |

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)

當這條 SQL 使用到覆蓋索引時，SQL 如下：查詢耗時：0.091s，查到 141 條數據。

-- 0.091s

SELECT app_name from vio_basic_domain_info where app_name like '翻譯%' ;

Explain 分析結果如下表;根據表信息可知：和上面的 SQL 一樣使用到了索引，由於查詢列就包含在索引列中，又省去了 0.06s 的回表時間。

mysql> EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '翻譯%' ;

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

| 1 | SIMPLE | vio_basic_domain_info | NULL | range | idx_app_name | idx_app_name | 515 | NULL | 141 | 100.00 | Using where; Using index |

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

那麼是如何通過 EXPLAIN 解析結果分析 SQL 的呢?各列屬性又代表著什麼?一起往下看。

各列屬性的簡介

各列屬性的簡介如下：

• id：SELECT 的查詢序列號，體現執行優先級，如果是子查詢，id的序號會遞增，id 值越大優先級越高，越先被執行。
• select_type：表示查詢的類型。
• table：輸出結果集的表，如設置了別名，也會顯示。
• partitions：匹配的分區。
• type：對錶的訪問方式。
• possible_keys：表示查詢時，可能使用的索引。
• key：表示實際使用的索引。
• key_len：索引字段的長度。
• ref：列與索引的比較。
• rows：掃描出的行數(估算的行數)。
• filtered：按表條件過濾的行百分比。
• Extra：執行情況的描述和說明。

以上標星的幾類是我們優化慢查詢時常用到的。

慢查詢分析常用到的屬性

①type

對錶訪問方式，表示 MySQL 在表中找到所需行的方式，又稱“訪問類型”。

存在的類型有：ALL、index、range、ref、eq_ref、const、system、NULL(從左到右，性能從低到高)。

介紹三個咱們天天見到的：

• ALL：(Full Table Scan)MySQL 將遍歷全表以找到匹配的行，常說的全表掃描。
• Index：(Full Index Scan)Index 與 ALL 區別為 Index 類型只遍歷索引樹。
• Range：只檢索給定範圍的行，使用一個索引來選擇行。

②key

key 列顯示了 SQL 實際使用索引，通常是 possible_keys 列中的索引之一，MySQL 優化器一般會通過計算掃描行數來選擇更適合的索引，如果沒有選擇索引，則返回 NULL。

當然，MySQL 優化器存在選擇索引錯誤的情況，可以通過修改 SQL 強制MySQL“使用或忽視某個索引”：

• 強制使用一個索引：FORCE INDEX (index_name)、USE INDEX (index_name)。
• 強制忽略一個索引：IGNORE INDEX (index_name)。

③rows

rows 是 MySQL 估計為了找到所需的行而要讀取(掃描)的行數，可能不精確。

④Extra

這一列顯示一些額外信息，很重要。

Using index：查詢的列被索引覆蓋，並且 where 篩選條件是索引的是前導列，Extra 中為 Using index。意味著通過索引查找就能直接找到符合條件的數據，無須回表。

注：前導列一般指聯合索引中的第一列或“前幾列”，以及單列索引的情況;這裡為了方便理解我統稱為前導列。

Using where：說明 MySQL 服務器將在存儲引擎檢索行後再進行過濾;即沒有用到索引，回表查詢。

可能的原因：

• 查詢的列未被索引覆蓋。
• where 篩選條件非索引的前導列或無法正確使用到索引。

Using temporary：這意味著 MySQL 在對查詢結果排序時會使用一個臨時表。

Using filesort：說明 MySQL 會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。

Using index condition：查詢的列不全在索引中，where 條件中是一個前導列的範圍。

Using where;Using index：查詢的列被索引覆蓋，並且 where 篩選條件是索引列之一，但不是索引的前導列或出現了其他影響直接使用索引的情況(如存在範圍篩選條件等)，Extra 中為 Using where;Using index，意味著無法直接通過索引查找來查詢到符合條件的數據，影響並不大。

一些慢查詢優化經驗分享

優化 LIMIT 分頁

在系統中需要分頁的操作通常會使用 limit 加上偏移量的方法實現，同時加上合適的 order by 子句。

如果有對應的索引，通常效率會不錯，否則 MySQL 需要做大量的文件排序操作。

一個非常令人頭疼問題就是當偏移量非常大的時候，例如可能是 limit 1000000，10 這樣的查詢。

這是 MySQL 需要查詢 1000000 條然後只返回最後 10 條，前面的 1000000 條記錄都將被捨棄，這樣的代價很高，會造成慢查詢。

優化此類查詢的一個最簡單的方法是儘可能的使用索引覆蓋掃描，而不是查詢所有的列。

然後根據需要做一次關聯操作再返回所需的列。對於偏移量很大的時候這樣做的效率會得到很大提升。

對於下面的查詢：

-- 執行耗時：1.379s

SELECT * from vio_basic_domain_info LIMIT 1000000,10;

Explain 分析結果：

mysql> EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10;

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+

| 1 | SIMPLE | vio_basic_domain_info | NULL | ALL | NULL | NULL | NULL | NULL | 1377809 | 100.00 | NULL |

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+

1 row in set, 1 warning (0.00 sec)

該語句存在的最大問題在於 limit M，N 中偏移量 M 太大，導致每次查詢都要先從整個表中找到滿足條件的前 M 條記錄，之後捨棄這 M 條記錄並從第 M+1 條記錄開始再依次找到 N 條滿足條件的記錄。

如果表非常大，且篩選字段沒有合適的索引，且 M 特別大那麼這樣的代價是非常高的。

那麼如果我們下一次的查詢能從前一次查詢結束後標記的位置開始查找，找到滿足條件的 10 條記錄，並記下下一次查詢應該開始的位置，以便於下一次查詢能直接從該位置開始。

這樣就不必每次查詢都先從整個表中先找到滿足條件的前 M 條記錄，捨棄掉，再從 M+1 開始再找到 10 條滿足條件的記錄了。

處理分頁慢查詢的方式一般有以下幾種：

思路一：構造覆蓋索引

通過修改 SQL，使用上覆蓋索引，比如我需要只查詢表中的 app_name、createTime 等少量字段，那麼我秩序在 app_name、createTime 字段設置聯合索引，即可實現覆蓋索引，無需全表掃描。

適用於查詢列較少的場景，查詢列數過多的不推薦，耗時：0.390s。

mysql> EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10;

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+

| 1 | SIMPLE | vio_basic_domain_info | NULL | index | NULL | idx_app_name | 515 | NULL | 1377809 | 100.00 | Using index |

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

思路二：優化 offset

無法用上覆蓋索引，那麼重點是想辦法快速過濾掉前 100w 條數據。我們可以利用自增主鍵有序的條件，先查詢出第 1000001 條數據的 id 值，再往後查 10 行。

適用於主鍵 id 自增的場景，耗時：0.471s。

SELECT * from vio_basic_domain_info where

id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

原理：先基於索引查詢出第 1000001 條數據對應的主鍵 id 的值，然後直接通過該 id 的值直接查詢該 id 後面的 10 條數據。

下方 EXPLAIN 分析結果中大家可以看到這條 SQL 的兩步執行流程：

mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

| 1 | PRIMARY | vio_basic_domain_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 10 | 100.00 | Using where |

| 2 | SUBQUERY | vio_basic_domain_info | NULL | index | NULL | PRIMARY | 8 | NULL | 1000001 | 100.00 | Using index |

+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

2 rows in set, 1 warning (0.40 sec)

方法三：“延遲關聯”

耗時：0.439s，延遲關聯適用於數量級較大的表。

SQL 如下：

SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);

這裡我們利用到了覆蓋索引+延遲關聯查詢，相當於先只查詢 id 列，利用覆蓋索引快速查到該頁的 10 條數據 id，然後再把返回的 10 條 id 拿到表中通過主鍵索引二次查詢。(表數據增速快的情況對該方法影響較小)

mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);

+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+

| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 1000010 | 100.00 | NULL |

| 1 | PRIMARY | vio_basic_domain_info | NULL | eq_ref | PRIMARY | PRIMARY | 8 | myNew.id | 1 | 100.00 | NULL |

| 2 | DERIVED | vio_basic_domain_info | NULL | index | NULL | PRIMARY | 8 | NULL | 1000010 | 100.00 | Using index |

+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+

3 rows in set, 1 warning (0.00 sec)

排查索引沒起作用的情況

①模糊查詢儘量避免用通配符'%'開頭，會導致數據庫引擎放棄索引進行全表掃描

如下：

SELECT * FROM t WHERE username LIKE '%陳%'

優化方式：儘量在字段後面使用模糊查詢。如下：

SELECT * FROM t WHERE username LIKE '陳%'

如果需求是要在前面使用模糊查詢：

使用 MySQL 內置函數 INSTR(str，substr)來匹配，作用類似於 Java 中的 indexOf()，查詢字符串出現的角標位置。

使用 FullText 全文索引，用 match against 檢索。

數據量較大的情況，建議引用 ElasticSearch、Solr，億級數據量檢索速度秒級。

當表數據量較少(幾千條兒那種)，別整花裡胡哨的，直接用 like '%xx%'。

②儘量避免使用 not in，會導致引擎走全表掃描。建議用 not exists 代替

如下：

-- 不走索引

SELECT * FROM t WHERE name not IN ('提莫','隊長');

-- 走索引

select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','隊長') and t1.id = t2.id);

③儘量避免使用 or，會導致數據庫引擎放棄索引進行全表掃描

如下：

SELECT * FROM t WHERE id = 1 OR id = 3

優化方式：可以用 union 代替 or。如下：

SELECT * FROM t WHERE id = 1

UNION

SELECT * FROM t WHERE id = 3

④儘量避免進行 null 值的判斷，會導致數據庫引擎放棄索引進行全表掃描

如下：

SELECT * FROM t WHERE score IS NULL

優化方式：可以給字段添加默認值 0，對 0 值進行判斷。如下：

SELECT * FROM t WHERE score = 0

⑤儘量避免在 where 條件中等號的左側進行表達式、函數操作，會導致數據庫引擎放棄索引進行全表掃描

可以將表達式、函數操作移動到等號右側。如下：

-- 全表掃描

SELECT * FROM T WHERE score/10 = 9

-- 走索引

SELECT * FROM T WHERE score = 10*9

⑥當數據量大時，避免使用 where 1=1 的條件。通常為了方便拼裝查詢條件，我們會默認使用該條件，數據庫引擎會放棄索引進行全表掃描

如下：

SELECT username, age, sex FROM T WHERE 1=1

優化方式：用代碼拼裝 SQL 時進行判斷，沒 where 條件就去掉 where，有 where 條件就加 and。

⑦查詢條件不能用 <> 或者 !=

使用索引列作為條件進行查詢時，需要避免使用<>或者!=等判斷條件。

如確實業務需要，使用到不等於符號，需要在重新評估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

⑧where 條件僅包含複合索引非前導列

如：複合(聯合)索引包含 key_part1，key_part2，key_part3 三列，但 SQL 語句沒有包含索引前置列"key_part1"，按照 MySQL 聯合索引的最左匹配原則，不會走聯合索引。

-- 不走索引

select col1 from table where key_part2=1 and key_part3=2

-- 走索引

select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2

⑨隱式類型轉換造成不使用索引

如下 SQL 語句由於索引對列類型為 varchar，但給定的值為數值，涉及隱式類型轉換，造成不能正確走索引。

select col1 from table where col_varchar=123;

結語

好了，通過這篇文章，希望你 Get 到了一些分析 MySQL 慢查詢的方法和心得，如果你覺得這篇文章不錯，記得分享給朋友或同事，讓大家少踩點坑。

作者：陳哈哈

簡介：MySQL 社區的非著名貢獻者，善於白嫖知識;陪伴 MySQL 五年，致力於高性能 SQL、事務鎖優化方面的研究;長路漫漫，希望通過自己的分享讓大家少踩一些坑。我是陳哈哈，一個愛笑的程序員。

編輯：陶家龍

徵稿：有投稿、尋求報道意向技術人請聯絡 [email protected]

【51CTO原創稿件，合作站點轉載請註明原文作者和出處為51CTO.com】