一、什么是慢查詢

什么是MySQL慢查詢呢？其實(shí)就是查詢的SQL語句耗費(fèi)較長的時(shí)間。

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

正常情況下，MySQL是不會(huì)自動(dòng)開啟慢查詢的，且如果開啟的話默認(rèn)閾值是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 表示慢查詢的閾值，默認(rèn)10秒
show global variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

二、慢查詢的危害

既然我們這么關(guān)注慢查詢，那它肯定是有一些不好的地方，常見的有這幾個(gè)：

1、用戶體驗(yàn)差。

我們?cè)L問一個(gè)東西，或者保存一個(gè)東西，都得等好久，那不得分分鐘棄坑？等等，我知道體驗(yàn)是會(huì)差，但慢查詢的閾值設(shè)置為100ms似不似太低了，我訪問一個(gè)東西1-2秒應(yīng)該也能接受吧。其實(shí)這個(gè)閾值并不算太低，因?yàn)檫@是一條SQL的閾值，而你一個(gè)接口可能要查好幾次SQL，甚至調(diào)下外部接口都是很常見的。

2、占用MySQL內(nèi)存，影響性能

MySQL內(nèi)存本來就是有限的（大內(nèi)存要加錢?。?，SQL為什么查詢慢呢？有時(shí)候就是因?yàn)槟闳頀邔?dǎo)致查詢的數(shù)據(jù)量很多，再加上各種篩選就變慢了，所以慢查詢往往也會(huì)意味著內(nèi)存占用的增高，內(nèi)存一高，能夠承載的SQL查詢就變少了，性能也變差了。

3、造成DDL操作阻塞

眾所周知，InnoDB引擎默認(rèn)加的是行鎖，但鎖其實(shí)都是加在索引上的，如果篩選條件沒有建立索引，會(huì)降級(jí)到表鎖。而慢查詢有一大部分原因都是因?yàn)闆]加索引導(dǎo)致的，所以慢查詢時(shí)間過長，就會(huì)導(dǎo)致表鎖的時(shí)間也很長，如果這時(shí)候執(zhí)行DDL就會(huì)造成阻塞。

三、慢查詢常見場(chǎng)景

既然慢查詢?cè)斐傻膯栴}這么多，那一般什么場(chǎng)景下會(huì)出現(xiàn)慢查詢呢？

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

在沒加索引的情況，就會(huì)造成全表掃描；又或者沒走到索引（或者走的不是最優(yōu)索引），這兩張情況都會(huì)導(dǎo)致掃描行數(shù)增多，從而查詢時(shí)間變慢。

下面是我測(cè)試的一個(gè)例子：

# 這是我的表結(jié)構(gòu)，算是一種比較常規(guī)的表
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;

在上述表結(jié)構(gòu)下，我通過 [Fill Database](https://filldb.info/) 這個(gè)網(wǎng)站隨機(jī)生成了一批數(shù)據(jù)進(jìn)行測(cè)驗(yàn)，可以看到，在沒加索引的前提下，基本5萬條數(shù)據(jù)后就會(huì)開始出現(xiàn)慢查詢了（假設(shè)閾值為100ms）

2、單表數(shù)據(jù)量太大

如果本身單表數(shù)據(jù)量太大，可能超千萬，或者達(dá)到億級(jí)別，可能加了索引之后，個(gè)別查詢還是存在慢查詢的情況，這種貌似沒啥好辦法，要么就看索引設(shè)置得到底對(duì)不對(duì)，要么就只能分表了。

3、Limit 深分頁

深分頁的意思就是從比較后面的位置開始進(jìn)行分頁，比如每頁有10條，然后我要看第十萬頁的數(shù)據(jù)，這時(shí)候的分頁就會(huì)比較“深”

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

-- 個(gè)人測(cè)試： 106000條數(shù)據(jù)，耗時(shí)約 150ms
select * from t_user_article where click > 0 order by id limit 100000, 10;

在這種情況下，即使你的 click 字段加了索引，查詢速度可能還是很慢（測(cè)試后和不加差不多），因?yàn)槎?jí)索引樹存的是主鍵ID，查到數(shù)據(jù)還需要進(jìn)行回表才能決定是否丟棄，像上面的查詢，回表的次數(shù)就達(dá)到了100010次，可想而知速度是非常慢的。

結(jié)合上面的分析，目前的解決思路都是先查出主鍵字段（id），避免回表，再根據(jù)主鍵查出所有字段。

第一種，延遲關(guān)聯(lián)，此時(shí)SQL變?yōu)椋?/p>

-- 個(gè)人測(cè)試： 106000條數(shù)據(jù)，耗時(shí)約 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;

第二種，分開查詢，分開查詢的意思就是分兩次查，此時(shí)SQL變?yōu)椋?/p>

-- 個(gè)人測(cè)試： 106000條數(shù)據(jù)，耗時(shí)約 80ms
select id from t_user_article where click > 0 order by id limit 100000, 10;

-- 個(gè)人測(cè)試： 106000條數(shù)據(jù)，耗時(shí)約 80ms
select * from t_user_article where id in (上述查詢得到的ID)

大家可能會(huì)很疑惑，為什么要分開查呢，畢竟分開查可能最終耗時(shí)比一次查詢還要高！這是因?yàn)橛行┕荆ū热缥宜荆┛赡苤粚?duì)單條SQL的查詢時(shí)長有要求，但對(duì)整體的并沒有要求，這時(shí)候這種辦法就能達(dá)到一個(gè)折中的效果。

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

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

但這時(shí)候執(zhí)行你會(huì)發(fā)現(xiàn)拋出一個(gè)錯(cuò)誤： “This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery’”，翻譯過來就是子查詢不支持Limit，解決辦法也很簡(jiǎn)單，多嵌套一層即可：

-- 個(gè)人測(cè)試： 106000條數(shù)據(jù)，耗時(shí)約 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)

但問題是測(cè)試后發(fā)現(xiàn)耗時(shí)反而變長了，所以并沒有列舉為一種解決辦法。

4、使用FileSort查詢

什么是FileSort查詢呢？其實(shí)就是當(dāng)你使用 order by 關(guān)鍵字時(shí)，如果待排序的內(nèi)容不能由所使用的索引直接完成，MySQL就有可能會(huì)進(jìn)行FileSort。

當(dāng)查詢的數(shù)據(jù)較少，沒有超過系統(tǒng)變量 sort_buffer_size 設(shè)定的大小，則直接在內(nèi)存進(jìn)行排序（快排）；如果超過該變量設(shè)定的大小，則會(huì)利用文件進(jìn)行排序（歸并）。

FileSort出現(xiàn)的場(chǎng)景主要有以下兩種：

4.1 排序字段沒加索引

# click 字段此時(shí)未加索引
explain select id, click from t_user_article where click > 0 order by click limit 10;

# explain 結(jié)果：
type：ALL  Extra：Using where; Using filesort

解決辦法就是在 click 字段上加索引。

4.2 使用兩個(gè)字段排序，但是排序規(guī)則不同，一個(gè)正序，一個(gè)倒序

# click 字段此時(shí)已加索引
explain select id, click from t_user_article where click > 0 order by click desc, id asc limit 10;

# explain 結(jié)果：
type：range  Extra：Using where; Using index; Using filesort

這種場(chǎng)景常出現(xiàn)于排行榜中，因?yàn)榕判邪窠?jīng)常需要按照 某個(gè)指標(biāo)倒序 + 創(chuàng)建時(shí)間正序 排列。這種目前暫時(shí)無解，有解決辦法的大佬望在評(píng)論區(qū)留言。

總結(jié)

總的來說，看完本文應(yīng)該對(duì)慢查詢有所了解了，慢查詢優(yōu)化是一個(gè)經(jīng)久不衰的話題，場(chǎng)景也非常多元化，需要對(duì)索引的原理以及索引命中有一定了解。

到此這篇關(guān)于深入了解MySQL中的慢查詢的文章就介紹到這了,更多相關(guān)MySQL慢查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論