深入瞭解MySQL中的慢查詢

2022-10-20 14:03:49

一、什麼是慢查詢

什麼是MySQL慢查詢呢？其實就是查詢的SQL語句耗費較長的時間。

具體耗費多久算慢查詢呢？這其實因人而異，有些公司慢查詢的閾值是100ms，有些的閾值可能是500ms，即查詢的時間超過這個閾值即視為慢查詢。

正常情況下，MySQL是不會自動開啟慢查詢的，且如果開啟的話預設閾值是10秒

# slow_query_log 表示是否開啟
mysql> show global variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/0bd9099fc77f-slow.log |
+---------------------+--------------------------------------+

# long_query_time 表示慢查詢的閾值，預設10秒
show global variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

二、慢查詢的危害

既然我們這麼關注慢查詢，那它肯定是有一些不好的地方，常見的有這幾個：

1、使用者體驗差。

我們存取一個東西，或者儲存一個東西，都得等好久，那不得分分鐘棄坑？等等，我知道體驗是會差，但慢查詢的閾值設定為100ms似不似太低了，我存取一個東西1-2秒應該也能接受吧。其實這個閾值並不算太低，因為這是一條SQL的閾值，而你一個介面可能要查好幾次SQL，甚至調下外部介面都是很常見的。

2、佔用MySQL記憶體，影響效能

MySQL記憶體本來就是有限的（大記憶體要加錢！），SQL為什麼查詢慢呢？有時候就是因為你全表掃導致查詢的資料量很多，再加上各種篩選就變慢了，所以慢查詢往往也會意味著記憶體佔用的增高，記憶體一高，能夠承載的SQL查詢就變少了，效能也變差了。

3、造成DDL操作阻塞

眾所周知，InnoDB引擎預設加的是行鎖，但鎖其實都是加在索引上的，如果篩選條件沒有建立索引，會降級到表鎖。而慢查詢有一大部分原因都是因為沒加索引導致的，所以慢查詢時間過長，就會導致表鎖的時間也很長，如果這時候執行DDL就會造成阻塞。

三、慢查詢常見場景

既然慢查詢造成的問題這麼多，那一般什麼場景下會出現慢查詢呢？

1、沒加索引/沒利用好索引

在沒加索引的情況，就會造成全表掃描；又或者沒走到索引（或者走的不是最優索引），這兩張情況都會導致掃描行數增多，從而查詢時間變慢。

下面是我測試的一個例子：

# 這是我的表結構，算是一種比較常規的表
create table t_user_article
(
    id          bigint unsigned auto_increment
        primary key,
    cid         tinyint(2) default 0                 not null comment 'id',
    title       varchar(100)                         not null,
    author      varchar(15)                          not null,
    content     text                                 not null,
    keywords    varchar(255)                         not null,
    description varchar(255)                         not null,
    is_show     tinyint(1) default 1                 not null comment ' 1 0',
    is_delete   tinyint(1) default 0                 not null comment ' 1 0',
    is_top      tinyint(1) default 0                 not null comment ' 1 0',
    is_original tinyint(1) default 1                 not null,
    click       int(10)    default 0                 not null,
    created_at  timestamp  default CURRENT_TIMESTAMP not null,
    updated_at  timestamp  default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
)
    collate = utf8mb4_unicode_ci;

在上述表結構下，我通過 [Fill Database](https://filldb.info/) 這個網站隨機生成了一批資料進行測驗，可以看到，在沒加索引的前提下，基本5萬條資料後就會開始出現慢查詢了（假設閾值為100ms）

資料量	欄位數量	查詢型別	查詢時間
1000	*	全表（ALL）	約80ms
50000	*	全表（ALL）	約120ms
100000	*	全表（ALL）	約180ms

2、單表資料量太大

如果本身單表資料量太大，可能超千萬，或者達到億級別，可能加了索引之後，個別查詢還是存在慢查詢的情況，這種貌似沒啥好辦法，要麼就看索引設定得到底對不對，要麼就只能分表了。

3、Limit 深分頁

深分頁的意思就是從比較後面的位置開始進行分頁，比如每頁有10條，然後我要看第十萬頁的資料，這時候的分頁就會比較“深”

還是上面的 t_user_article 表，你可能會遇到這樣的一條深分頁查詢：

-- 個人測試： 106000條資料，耗時約 150ms
select * from t_user_article where click > 0 order by id limit 100000, 10;

在這種情況下，即使你的 click 欄位加了索引，查詢速度可能還是很慢（測試後和不加差不多），因為二級索引樹存的是主鍵ID，查到資料還需要進行回表才能決定是否丟棄，像上面的查詢，回表的次數就達到了100010次，可想而知速度是非常慢的。

結合上面的分析，目前的解決思路都是先查出主鍵欄位（id），避免回表，再根據主鍵查出所有欄位。

第一種，延遲關聯，此時SQL變為：

-- 個人測試： 106000條資料，耗時約 90ms
select * from t_user_article t1, (select id from t_user_article where click > 0 order by id limit 100000, 10) t2  WHERE t1.id = t2.id;

第二種，分開查詢，分開查詢的意思就是分兩次查，此時SQL變為：

-- 個人測試： 106000條資料，耗時約 80ms
select id from t_user_article where click > 0 order by id limit 100000, 10;

-- 個人測試： 106000條資料，耗時約 80ms
select * from t_user_article where id in (上述查詢得到的ID)

大家可能會很疑惑，為什麼要分開查呢，畢竟分開查可能最終耗時比一次查詢還要高！這是因為有些公司（比如我司）可能只對單條SQL的查詢時長有要求，但對整體的並沒有要求，這時候這種辦法就能達到一個折中的效果。

另外，大家在網上可能會看到利用子查詢解決的辦法，比如改成這樣：

select * from t_user_article where id in (select id from t_user_article where click > 0 limit 100000, 10)

但這時候執行你會發現丟擲一個錯誤： “This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery’”，翻譯過來就是子查詢不支援Limit，解決辦法也很簡單，多巢狀一層即可：

-- 個人測試： 106000條資料，耗時約 200ms
select * from t_user_article where id in (select t.id from (select id from t_user_article where click > 0 order by id limit 100000, 10) as t)

但問題是測試後發現耗時反而變長了，所以並沒有列舉為一種解決辦法。

4、使用FileSort查詢

什麼是FileSort查詢呢？其實就是當你使用 order by 關鍵字時，如果待排序的內容不能由所使用的索引直接完成，MySQL就有可能會進行FileSort。

當查詢的資料較少，沒有超過系統變數 sort_buffer_size 設定的大小，則直接在記憶體進行排序（快排）；如果超過該變數設定的大小，則會利用檔案進行排序（歸併）。

FileSort出現的場景主要有以下兩種：

4.1 排序欄位沒加索引

# click 欄位此時未加索引
explain select id, click from t_user_article where click > 0 order by click limit 10;

# explain 結果：
type：ALL  Extra：Using where; Using filesort

解決辦法就是在 click 欄位上加索引。

4.2 使用兩個欄位排序，但是排序規則不同，一個正序，一個倒序

# click 欄位此時已加索引
explain select id, click from t_user_article where click > 0 order by click desc, id asc limit 10;

# explain 結果：
type：range  Extra：Using where; Using index; Using filesort

這種場景常出現於排行榜中，因為排行榜經常需要按照 某個指標倒序 + 建立時間正序 排列。這種目前暫時無解，有解決辦法的大佬望在評論區留言。

總結

總的來說，看完本文應該對慢查詢有所瞭解了，慢查詢優化是一個經久不衰的話題，場景也非常多元化，需要對索引的原理以及索引命中有一定了解。

到此這篇關於深入瞭解MySQL中的慢查詢的文章就介紹到這了,更多相關MySQL慢查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

深入瞭解MySQL中的慢查詢

目錄

一、什麼是慢查詢

二、慢查詢的危害

三、慢查詢常見場景

總結

熱門文章