MySQL中的慢查詢是什么及有哪些危害

本文小編為大家詳細介紹“MySQL中的慢查詢是什么及有哪些危害”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL中的慢查詢是什么及有哪些危害”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

一、什么是慢查詢

什么是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）

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中的慢查詢是什么及有哪些危害”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。