MySQL慢查詢的坑

更新時(shí)間：2021年04月28日 09:55:55 作者：_陳哈哈

這篇文章主要介紹了MySQL慢查詢的坑，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

一條慢查詢會(huì)造成什么后果？年輕時(shí)，我一直覺(jué)得不就是返回?cái)?shù)據(jù)會(huì)慢一些么，用戶體驗(yàn)變差？其實(shí)遠(yuǎn)遠(yuǎn)不止，我經(jīng)歷過(guò)幾次線上事故，有一次就是由一條SQL慢查詢導(dǎo)致的。

記得那是一條查詢SQL，數(shù)據(jù)量萬(wàn)級(jí)時(shí)還保持在0.2秒內(nèi)，隨著某一段時(shí)間數(shù)據(jù)猛增，耗時(shí)一度達(dá)到了2-3秒！沒(méi)有命中索引，導(dǎo)致全表掃描。explain 中extra顯示：Using where; Using temporary; Using filesort，被迫使用了臨時(shí)表排序，由于是高頻查詢，并發(fā)一起來(lái)很快就把DB線程池打滿了，導(dǎo)致大量查詢請(qǐng)求堆積，DB服務(wù)器cpu長(zhǎng)時(shí)間100%+，大量請(qǐng)求timeout。。最終系統(tǒng)崩潰。老板登場(chǎng)~

對(duì)了，那次是十月二日晚上8點(diǎn)半，我在老家棗莊，和哥兒幾個(gè)正坐在大排檔吹著牛B！你猜，我將面臨什么尷尬局面？

可見(jiàn)，團(tuán)隊(duì)如果對(duì)慢查詢不引起足夠的重視，風(fēng)險(xiǎn)是很大的。經(jīng)過(guò)那次事故我們老板就說(shuō)了：誰(shuí)的代碼再出現(xiàn)類似事故，開(kāi)發(fā)和部門(mén)領(lǐng)導(dǎo)一起走人，嚇得一大堆領(lǐng)導(dǎo)心發(fā)慌，趕緊招了兩位DBA同事🙂🙂🙂。

慢查詢，顧名思義，執(zhí)行很慢的查詢。有多慢？超過(guò) long_query_time 參數(shù)設(shè)定的時(shí)間閾值（默認(rèn)10s），就被認(rèn)為是慢的，是需要優(yōu)化的。慢查詢被記錄在慢查詢?nèi)罩纠铩?/p>

慢查詢?nèi)罩灸J(rèn)是不開(kāi)啟的，如果你需要優(yōu)化SQL語(yǔ)句，就可以開(kāi)啟這個(gè)功能，它可以讓你很容易地知道哪些語(yǔ)句是需要優(yōu)化的（想想一個(gè)SQL要10s就可怕）。

墨菲定律：會(huì)出錯(cuò)的事情就一定會(huì)出錯(cuò)。

這是太真實(shí)的事情之一了。為了防患于未然，一起來(lái)看看慢查詢?cè)撛趺刺幚?。本文很干，記得接杯水，沒(méi)時(shí)間看的先收藏哦！

一、慢查詢配置

1-1、開(kāi)啟慢查詢

MySQL支持通過(guò)

1、輸入命令開(kāi)啟慢查詢（臨時(shí)），在MySQL服務(wù)重啟后會(huì)自動(dòng)關(guān)閉；
2、配置my.cnf（windows是my.ini）系統(tǒng)文件開(kāi)啟，修改配置文件是持久化開(kāi)啟慢查詢的方式。

方式一：通過(guò)命令開(kāi)啟慢查詢

步驟1、查詢 slow_query_log 查看是否已開(kāi)啟慢查詢?nèi)罩荆?/p>

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、開(kāi)啟慢查詢命令：

set global slow_query_log='ON';

步驟3、指定記錄慢查詢?nèi)罩維QL執(zhí)行時(shí)間得閾值（long_query_time 單位：秒，默認(rèn)10秒）

如下我設(shè)置成了1秒，執(zhí)行時(shí)間超過(guò)1秒的SQL將記錄到慢查詢?nèi)罩局?/p>

set global long_query_time=1;

步驟4、查詢 “慢查詢?nèi)罩疚募娣盼恢谩?/p>

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 指定慢查詢?nèi)罩镜拇鎯?chǔ)路徑及文件（默認(rèn)和數(shù)據(jù)文件放一起）

步驟5、核對(duì)慢查詢開(kāi)啟狀態(tài)

需要退出當(dāng)前MySQL終端，重新登錄即可刷新；

配置了慢查詢后，它會(huì)記錄以下符合條件的SQL：

查詢語(yǔ)句
數(shù)據(jù)修改語(yǔ)句
已經(jīng)回滾的SQL

方式二：通過(guò)配置my.cnf（windows是my.ini）系統(tǒng)文件開(kāi)啟

（版本：MySQL5.5及以上）

在my.cnf文件的[mysqld]下增加如下配置開(kāi)啟慢查詢，如下圖

# 開(kāi)啟慢查詢功能
slow_query_log=ON
# 指定記錄慢查詢?nèi)罩維QL執(zhí)行時(shí)間得閾值
long_query_time=1
# 選填，默認(rèn)數(shù)據(jù)文件路徑
# slow_query_log_file=/var/lib/mysql/localhost-slow.log

重啟數(shù)據(jù)庫(kù)后即持久化開(kāi)啟慢查詢，查詢驗(yàn)證如下：

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-2、慢查詢?nèi)罩窘榻B

如上圖，是執(zhí)行時(shí)間超過(guò)1秒的SQL語(yǔ)句（測(cè)試）

第一行：記錄時(shí)間
第二行：用戶名、用戶的IP信息、線程ID號(hào)
第三行：執(zhí)行花費(fèi)的時(shí)間【單位：秒】、執(zhí)行獲得鎖的時(shí)間、獲得的結(jié)果行數(shù)、掃描的數(shù)據(jù)行數(shù)
第四行：這SQL執(zhí)行的時(shí)間戳
第五行：具體的SQL語(yǔ)句

二、Explain分析慢查詢SQL

分析mysql慢查詢?nèi)罩?,利用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語(yǔ)句，來(lái)分析sql慢查詢語(yǔ)句，下面我們的測(cè)試表是一張137w數(shù)據(jù)的app信息表，我們來(lái)舉例分析一下；

SQL示例如下：

-- 1.185s
SELECT * from vio_basic_domain_info where app_name like '%陳哈哈%' ;

這是一條普通的模糊查詢語(yǔ)句，查詢耗時(shí)：1.185s，查到了148條數(shù)據(jù)；
我們用Explain分析結(jié)果如下表，根據(jù)表信息可知：該SQL沒(méi)有用到字段app_name上的索引，查詢類型是全表掃描，掃描行數(shù)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)

當(dāng)這條SQL使用到索引時(shí)，SQL如下：查詢耗時(shí)：0.156s，查到141條數(shù)據(jù)

-- 0.156s
SELECT * from vio_basic_domain_info where app_name like '陳哈哈%' ;

Explain分析結(jié)果如下表；根據(jù)表信息可知：該SQL用到了idx_app_name索引，查詢類型是索引范圍查詢，掃描行數(shù)141行。由于查詢的列不全在索引中（select *），因此回表了一次，取了其他列的數(shù)據(jù)。

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)

當(dāng)這條SQL使用到覆蓋索引時(shí)，SQL如下：查詢耗時(shí)：0.091s，查到141條數(shù)據(jù)

-- 0.091s
SELECT app_name from vio_basic_domain_info where app_name like '陳哈哈%' ;

Explain分析結(jié)果如下表；根據(jù)表信息可知：和上面的SQL一樣使用到了索引，由于查詢列就包含在索引列中，又省去了0.06s的回表時(shí)間。

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)

那么是如何通過(guò)EXPLAIN解析結(jié)果分析SQL的呢？各列屬性又代表著什么？一起往下看。

2-1、各列屬性的簡(jiǎn)介：

id:SELECT的查詢序列號(hào)，體現(xiàn)執(zhí)行優(yōu)先級(jí)，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行
select_type:表示查詢的類型。
table:輸出結(jié)果集的表，如設(shè)置了別名，也會(huì)顯示
partitions:匹配的分區(qū)
type:對(duì)表的訪問(wèn)方式
possible_keys:表示查詢時(shí)，可能使用的索引
key:表示實(shí)際使用的索引
key_len:索引字段的長(zhǎng)度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表?xiàng)l件過(guò)濾的行百分比
Extra:執(zhí)行情況的描述和說(shuō)明

以上標(biāo)星的幾類是我們優(yōu)化慢查詢時(shí)常用到的

2-2、慢查詢分析常用到的屬性

1、type：
對(duì)表訪問(wèn)方式，表示MySQL在表中找到所需行的方式，又稱“訪問(wèn)類型”。

存在的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從低到高），介紹三個(gè)咱們天天見(jiàn)到的：

ALL：(Full Table Scan) MySQL將遍歷全表以找到匹配的行，常說(shuō)的全表掃描
index: (Full Index Scan) index與ALL區(qū)別為index類型只遍歷索引樹(shù)
range:只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行

2、key

key列顯示了SQL實(shí)際使用索引，通常是possible_keys列中的索引之一，MySQL優(yōu)化器一般會(huì)通過(guò)計(jì)算掃描行數(shù)來(lái)選擇更適合的索引，如果沒(méi)有選擇索引，則返回NULL。當(dāng)然，MySQL優(yōu)化器存在選擇索引錯(cuò)誤的情況，可以通過(guò)修改SQL強(qiáng)制MySQL“使用或忽視某個(gè)索引”。

強(qiáng)制使用一個(gè)索引：FORCE INDEX (index_name)、USE INDEX (index_name)
強(qiáng)制忽略一個(gè)索引：IGNORE INDEX (index_name)

3、rows
rows是MySQL估計(jì)為了找到所需的行而要讀取（掃描）的行數(shù)，可能不精確。

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

Using index
查詢的列被索引覆蓋，并且where篩選條件是索引的是前導(dǎo)列，Extra中為Using index。意味著通過(guò)索引查找就能直接找到符合條件的數(shù)據(jù)，無(wú)須回表。

注：前導(dǎo)列一般指聯(lián)合索引中的第一列或“前幾列”，以及單列索引的情況；這里為了方便理解我統(tǒng)稱為前導(dǎo)列。

Using where
說(shuō)明MySQL服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾；即沒(méi)有用到索引，回表查詢。

可能的原因：

查詢的列未被索引覆蓋；
where篩選條件非索引的前導(dǎo)列或無(wú)法正確使用到索引；

Using temporary
這意味著MySQL在對(duì)查詢結(jié)果排序時(shí)會(huì)使用一個(gè)臨時(shí)表。

Using filesort
說(shuō)明MySQL會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行。

Using index condition
查詢的列不全在索引中，where條件中是一個(gè)前導(dǎo)列的范圍

Using where;Using index
查詢的列被索引覆蓋，并且where篩選條件是索引列之一，但不是索引的前導(dǎo)列或出現(xiàn)了其他影響直接使用索引的情況（如存在范圍篩選條件等），Extra中為Using where; Using index，意味著無(wú)法直接通過(guò)索引查找來(lái)查詢到符合條件的數(shù)據(jù)，影響并不大。

三、一些慢查詢優(yōu)化經(jīng)驗(yàn)分享

3-1、優(yōu)化LIMIT分頁(yè)

在系統(tǒng)中需要分頁(yè)的操作通常會(huì)使用limit加上偏移量的方法實(shí)現(xiàn)，同時(shí)加上合適的order by 子句。如果有對(duì)應(yīng)的索引，通常效率會(huì)不錯(cuò)，否則MySQL需要做大量的文件排序操作。

一個(gè)非常令人頭疼問(wèn)題就是當(dāng)偏移量非常大的時(shí)候，例如可能是limit 1000000,10這樣的查詢，這是mysql需要查詢1000000條然后只返回最后10條，前面的1000000條記錄都將被舍棄，這樣的代價(jià)很高，會(huì)造成慢查詢。

優(yōu)化此類查詢的一個(gè)最簡(jiǎn)單的方法是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。對(duì)于偏移量很大的時(shí)候這樣做的效率會(huì)得到很大提升。

對(duì)于下面的查詢：

-- 執(zhí)行耗時(shí)：1.379s
SELECT * from vio_basic_domain_info LIMIT 1000000,10;

Explain分析結(jié)果：

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)

該語(yǔ)句存在的最大問(wèn)題在于limit M,N中偏移量M太大，導(dǎo)致每次查詢都要先從整個(gè)表中找到滿足條件的前M條記錄，之后舍棄這M條記錄并從第M+1條記錄開(kāi)始再依次找到N條滿足條件的記錄。如果表非常大，且篩選字段沒(méi)有合適的索引，且M特別大那么這樣的代價(jià)是非常高的。

那么如果我們下一次的查詢能從前一次查詢結(jié)束后標(biāo)記的位置開(kāi)始查找，找到滿足條件的10條記錄，并記下下一次查詢應(yīng)該開(kāi)始的位置，以便于下一次查詢能直接從該位置開(kāi)始，這樣就不必每次查詢都先從整個(gè)表中先找到滿足條件的前M條記錄，舍棄掉，再?gòu)腗+1開(kāi)始再找到10條滿足條件的記錄了。

處理分頁(yè)慢查詢的方式一般有以下幾種

思路一：構(gòu)造覆蓋索引

通過(guò)修改SQL，使用上覆蓋索引，比如我需要只查詢表中的app_name、createTime等少量字段，那么我秩序在app_name、createTime字段設(shè)置聯(lián)合索引，即可實(shí)現(xiàn)覆蓋索引，無(wú)需全表掃描。適用于查詢列較少的場(chǎng)景，查詢列數(shù)過(guò)多的不推薦。
耗時(shí)：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)

思路二：優(yōu)化offset

無(wú)法用上覆蓋索引，那么重點(diǎn)是想辦法快速過(guò)濾掉前100w條數(shù)據(jù)。我們可以利用自增主鍵有序的條件，先查詢出第1000001條數(shù)據(jù)的id值，再往后查10行；適用于主鍵id自增的場(chǎng)景。
耗時(shí)：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條數(shù)據(jù)對(duì)應(yīng)的主鍵id的值，然后直接通過(guò)該id的值直接查詢?cè)搃d后面的10條數(shù)據(jù)。下方EXPLAIN 分析結(jié)果中大家可以看到這條SQL的兩步執(zhí)行流程。

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)

方法三：“延遲關(guān)聯(lián)”

耗時(shí)：0.439s
延遲關(guān)聯(lián)適用于數(shù)量級(jí)較大的表，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);

這里我們利用到了覆蓋索引+延遲關(guān)聯(lián)查詢，相當(dāng)于先只查詢id列，利用覆蓋索引快速查到該頁(yè)的10條數(shù)據(jù)id，然后再把返回的10條id拿到表中通過(guò)主鍵索引二次查詢。（表數(shù)據(jù)增速快的情況對(duì)該方法影響較小。）

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     | <derived2>            | 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)

3-2、排查索引沒(méi)起作用的情況

模糊查詢盡量避免用通配符'%'開(kāi)頭，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

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

優(yōu)化方式：盡量在字段后面使用模糊查詢。如下：

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

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

使用MySQL內(nèi)置函數(shù)INSTR(str,substr) 來(lái)匹配，作用類似于java中的indexOf()，查詢字符串出現(xiàn)的角標(biāo)位置。
使用FullText全文索引，用match against 檢索
數(shù)據(jù)量較大的情況，建議引用ElasticSearch、solr，億級(jí)數(shù)據(jù)量檢索速度秒級(jí)
當(dāng)表數(shù)據(jù)量較少（幾千條兒那種），別整花里胡哨的，直接用like ‘%xx%'。

但不得不說(shuō)，MySQL模糊匹配大字段是硬傷，畢竟保證事務(wù)的ACID特性耗費(fèi)了太多性能，因此，如果實(shí)際場(chǎng)景中有類似業(yè)務(wù)需求，建議果斷更換大數(shù)據(jù)存儲(chǔ)引擎如ElasticSearch、Hbase等。這里和情懷無(wú)關(guān)~

盡量避免使用 not in，會(huì)導(dǎo)致引擎走全表掃描。建議用 not exists 代替，如下：

-- 不走索引
SELECT * FROM t WHERE name not IN ('提莫','隊(duì)長(zhǎng)');

-- 走索引
select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','隊(duì)長(zhǎng)') and t1.id = t2.id);

盡量避免使用 or，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

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

優(yōu)化方式：可以用union代替or。如下：

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

盡量避免進(jìn)行null值的判斷，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

SELECT * FROM t WHERE score IS NULL

優(yōu)化方式：可以給字段添加默認(rèn)值0，對(duì)0值進(jìn)行判斷。如下：

SELECT * FROM t WHERE score = 0

盡量避免在where條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。
可以將表達(dá)式、函數(shù)操作移動(dòng)到等號(hào)右側(cè)。如下：

-- 全表掃描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

當(dāng)數(shù)據(jù)量大時(shí)，避免使用where 1=1的條件。通常為了方便拼裝查詢條件，我們會(huì)默認(rèn)使用該條件，數(shù)據(jù)庫(kù)引擎會(huì)放棄索引進(jìn)行全表掃描。如下：

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

優(yōu)化方式：用代碼拼裝sql時(shí)進(jìn)行判斷，沒(méi) where 條件就去掉 where，有where條件就加 and。

查詢條件不要用 <> 或者 !=
使用索引列作為條件進(jìn)行查詢時(shí)，需要避免使用<>或者!=等判斷條件。如確實(shí)業(yè)務(wù)需要，使用到不等于符號(hào)，需要在重新評(píng)估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

where條件僅包含復(fù)合索引非前導(dǎo)列
如：復(fù)合（聯(lián)合）索引包含key_part1，key_part2，key_part3三列，但SQL語(yǔ)句沒(méi)有包含索引前置列"key_part1"，按照MySQL聯(lián)合索引的最左匹配原則，不會(huì)走聯(lián)合索引。

-- 不走索引
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

隱式類型轉(zhuǎn)換造成不使用索引
如下SQL語(yǔ)句由于索引對(duì)列類型為varchar，但給定的值為數(shù)值，涉及隱式類型轉(zhuǎn)換，造成不能正確走索引。

select col1 from table where col_varchar=123;

總結(jié)

好了，通過(guò)這篇文章，希望你Get到了一些分析MySQL慢查詢的方法和心得。慢查詢，在MySQL中始終是繞不開(kāi)的話題，慢的方式多種多樣，如果你想完全避免慢查詢？年輕人，我建議你耗子尾汁~

我們需要做的是及時(shí)發(fā)現(xiàn)并解決慢查詢，其實(shí)很多慢查詢是被動(dòng)出現(xiàn)的，比如由于某業(yè)務(wù)數(shù)據(jù)量猛增數(shù)量級(jí)變化、由于業(yè)務(wù)需求變化而改了字段或操作了既有索引等。雖然不是你的錯(cuò)，但這鍋可能還得你來(lái)背

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

您可能感興趣的文章: