總結(jié)12個MySQL慢查詢的原因分析
1. SQL 沒加索引
很多時候，我們的慢查詢，都是因為沒有加索引。如果沒有加索引的話，會導(dǎo)致全表掃描的。因此，應(yīng)考慮在 where 的條件列，建立索引，盡量避免全表掃描。
反例：
select * from user_info where name =‘撿田螺的小男孩公眾號' ;
正例：
//添加索引 alter table user_info add index idx_name (name);
2. SQL 索引不生效
有時候我們明明加了索引了，但是索引卻不生效。
在哪些場景，索引會不生效呢？主要有以下十大經(jīng)典場景：
2.1 隱式的類型轉(zhuǎn)換，索引失效
我們創(chuàng)建一個用戶 user 表：
CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, userId varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid (userId) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
userId 字段為字串類型，是 B + 樹的普通索引，如果查詢條件傳了一個數(shù)字過去，會導(dǎo)致索引失效。
如下：
如果給數(shù)字加上’', 也就是說，傳的是一個字符串呢，當(dāng)然是走索引，如下圖：
為什么第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL 會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點數(shù)再做比較。隱式的類型轉(zhuǎn)換，索引會失效。
2.2 查詢條件包含 or，可能導(dǎo)致索引失效
我們還是用這個表結(jié)構(gòu)：
CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, userId varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid (userId) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
其中 userId 加了索引，但是 age 沒有加索引的。我們使用了 or，以下 SQL 是不走索引的，如下：
對于 or+ 沒有索引的 age 這種情況，假設(shè)它走了 userId 的索引，但是走到 age 查詢條件時，它還得全表掃描，也就是需要三步過程：全表掃描 + 索引掃描 + 合并。如果它一開始就走全表掃描，直接一遍掃描就完事。Mysql 優(yōu)化器出于效率與成本考慮，遇到 or 條件，讓索引失效，看起來也合情合理嘛。
注意：如果 or 條件的列都加了索引，索引可能會走也可能不走，大家可以自己試一試哈。但是平時大家使用的時候，還是要注意一下這個 or，學(xué)會用 explain 分析。遇到不走索引的時候，考慮拆開兩條 SQL。
2.3. like 通配符可能導(dǎo)致索引失效
并不是用了 like 通配符，索引一定會失效，而是 like 查詢是以 % 開頭，才會導(dǎo)致索引失效。
like 查詢以 % 開頭，索引失效
explain select * from user where userId like ‘%123';
把 % 放后面，發(fā)現(xiàn)索引還是正常走的，如下：
explain select * from user where userId like ‘123%';
既然 like 查詢以 % 開頭，會導(dǎo)致索引失效。我們?nèi)绾蝺?yōu)化呢？
使用覆蓋索引把 % 放后面 2.4 查詢條件不滿足聯(lián)合索引的最左匹配原則
MySQl 建立聯(lián)合索引時，會遵循最左前綴匹配的原則，即最左優(yōu)先。如果你建立一個（a,b,c）的聯(lián)合索引，相當(dāng)于建立了 (a)、(a,b)、(a,b,c) 三個索引。
假設(shè)有以下表結(jié)構(gòu)：
CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid_name (user_id,name) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
有一個聯(lián)合索引 idx_userid_name，我們執(zhí)行這個 SQL，查詢條件是 name，索引是無效：
explain select * from user where name =‘撿田螺的小男孩';
因為查詢條件列 name 不是聯(lián)合索引 idx_userid_name 中的第一個列，索引不生效
在聯(lián)合索引中，查詢條件滿足最左匹配原則時，索引才正常生效。
2.5 在索引列上使用 mysql 的內(nèi)置函數(shù)
表結(jié)構(gòu)：
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdvarchar(32) NOT NULL,login_timedatetime NOT NULL, PRIMARY KEY (id), KEYidx_userId(userId) USING BTREE, KEYidx_login_time(login_Time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
雖然 login_time 加了索引，但是因為使用了 mysql 的內(nèi)置函數(shù) Date_ADD()，索引直接 GG，如圖：
一般這種情況怎么優(yōu)化呢？可以把內(nèi)置函數(shù)的邏輯轉(zhuǎn)移到右邊，如下：
explain select * from user where login_time = DATE_ADD(‘2022-05-22 00:00:00',INTERVAL -1 DAY);
2.6 對索引進(jìn)行列運算（如，+、-、*、/）, 索引不生效
表結(jié)構(gòu)：
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdvarchar(32) NOT NULL,ageint(11) DEFAULT NULL, PRIMARY KEY (id), KEYidx_age(age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
雖然 age 加了索引，但是因為它進(jìn)行運算，索引直接迷路了
如圖：
所以不可以對索引列進(jìn)行運算，可以在代碼處理好，再傳參進(jìn)去。
2.7 索引字段上使用（！= 或者 < >），索引可能失效
表結(jié)構(gòu)：
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdint(11) NOT NULL,ageint(11) DEFAULT NULL,namevarchar(255) NOT NULL, PRIMARY KEY (id), KEYidx_age(age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
雖然 age 加了索引，但是使用了！= 或者 < >，not in 這些時，索引如同虛設(shè)。
如下：
其實這個也是跟 mySQL優(yōu)化器有關(guān)，如果優(yōu)化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，不如直接不走索引。平時我們用！= 或者 < >，not in 的時候，留點心眼哈。
2.8 索引字段上使用 is null， is not null，索引可能失效
表結(jié)構(gòu):
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,cardvarchar(255) DEFAULT NULL,namevarchar(255) DEFAULT NULL, PRIMARY KEY (id), KEYidx_name(name) USING BTREE, KEYidx_card(card) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
單個 name 字段加上索引，并查詢 name 為非空的語句，其實會走索引的，如下:
單個 card 字段加上索引，并查詢 name 為非空的語句，其實會走索引的，如下:圖片
但是它兩用 or 連接起來，索引就失效了，如下：
很多時候，也是因為數(shù)據(jù)量問題，導(dǎo)致了 MySQL 優(yōu)化器放棄走索引。同時，平時我們用 explain 分析 SQL 的時候，如果 type=range, 要注意一下哈，因為這個可能因為數(shù)據(jù)量問題，導(dǎo)致索引無效。
2.9 左右連接，關(guān)聯(lián)的字段編碼格式不一樣
新建兩個表，一個 user，一個 user_job
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,namevarchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,ageint(11) NOT NULL, PRIMARY KEY (id), KEYidx_name(name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; CREATE TABLE user_job ( id int(11) NOT NULL, userId int(11) NOT NULL, job varchar(255) DEFAULT NULL, name varchar(255) DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user 表的 name 字段編碼是 utf8mb4，而 user_job 表的 name 字段編碼為 utf8。
執(zhí)行左外連接查詢，user_job 表還是走全表掃描，如下：
如果把它們的 name 字段改為編碼一致，相同的 SQL，還是會走索引。
所以大家在做表關(guān)聯(lián)時，注意一下關(guān)聯(lián)字段的編碼問題哈。
2.10 優(yōu)化器選錯了索引
MySQL 中一張表是可以支持多個索引的。你寫 SQL 語句的時候，沒有主動指定使用哪個索引的話，用哪個索引是由 MySQL 來確定的。
我們?nèi)粘ｉ_發(fā)中，不斷地刪除歷史數(shù)據(jù)和新增數(shù)據(jù)的場景，有可能會導(dǎo)致 MySQL 選錯索引。那么有哪些解決方案呢？
使用 force index 強行選擇某個索引修改你的 SQl，引導(dǎo)它使用我們期望的索引優(yōu)化你的業(yè)務(wù)邏輯優(yōu)化你的索引，新建一個更合適的索引，或者刪除誤用的索引。
3. limit 深分頁問題
limit 深分頁問題，會導(dǎo)致慢查詢，應(yīng)該大家都司空見慣了吧。
3.1 limit 深分頁為什么會變慢
limit 深分頁為什么會導(dǎo)致 SQL 變慢呢？假設(shè)我們有表結(jié)構(gòu)如下：
CREATE TABLE account ( id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵Id', name varchar(255) DEFAULT NULL COMMENT ‘賬戶名', balance int(11) DEFAULT NULL COMMENT ‘余額', create_time datetime NOT NULL COMMENT ‘創(chuàng)建時間', update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新時間', PRIMARY KEY (id), KEY idx_name (name), KEY idx_create_time (create_time) //索引 ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT=‘賬戶表';
你知道以下 SQL，執(zhí)行過程是怎樣的嘛？
select id,name,balance from account where create_time> ‘2020-09-19' limit 100000,10;
這個 SQL 的執(zhí)行流程：
通過普通二級索引樹 idx_create_time，過濾 create_time 條件，找到滿足條件的主鍵 id。通過主鍵id，回到 id主鍵索引樹，找到滿足記錄的行，然后取出需要展示的列（回表過程）掃描滿足條件的 100010 行，然后扔掉前 100000 行，返回。
limit 深分頁，導(dǎo)致 SQL 變慢原因有兩個：
limit 語句會先掃描 offset+n 行，然后再丟棄掉前 offset 行，返回后 n 行數(shù)據(jù)。也就是說 limit 100000,10，就會掃描 100010 行，而 limit 0,10，只掃描 10 行。limit 100000,10 掃描更多的行數(shù)，也意味著回表更多的次數(shù)。 3.2 如何優(yōu)化深分頁問題
我們可以通過減少回表次數(shù)來優(yōu)化。一般有標(biāo)簽記錄法和延遲關(guān)聯(lián)法。
標(biāo)簽記錄法
就是標(biāo)記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。
假設(shè)上一次記錄到 100000，則 SQL 可以修改為：
select id,name,balance FROM account where id > 100000 limit 10;
這樣的話，后面無論翻多少頁，性能都會不錯的，因為命中了 id索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段。
延遲關(guān)聯(lián)法
延遲關(guān)聯(lián)法，就是把條件轉(zhuǎn)移到主鍵索引樹，然后減少回表。
如下：
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > ‘2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;
優(yōu)化思路就是，先通過 idx_create_time 二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID 內(nèi)連接，這樣后面直接走了主鍵索引了，同時也減少了回表。
4. 單表數(shù)據(jù)量太大
4.1 單表數(shù)據(jù)量太大為什么會變慢？
一個表的數(shù)據(jù)量達(dá)到好幾千萬或者上億時，加索引的效果沒那么明顯啦。性能之所以會變差，是因為維護(hù)索引的 B+ 樹結(jié)構(gòu)層級變得更高了，查詢一條數(shù)據(jù)時，需要經(jīng)歷的磁盤 IO 變多，因此查詢性能變慢。
4.2 一棵 B + 樹可以存多少數(shù)據(jù)量
大家是否還記得，一個 B + 樹大概可以存放多少數(shù)據(jù)量呢？
InnoDB 存儲引擎最小儲存單元是頁，一頁大小就是 16k。
B + 樹葉子存的是數(shù)據(jù)，內(nèi)部節(jié)點存的是鍵值 + 指針。索引組織表通過非葉子節(jié)點的二分查找法以及指針確定數(shù)據(jù)在哪個頁中，進(jìn)而再去數(shù)據(jù)頁中找到需要的數(shù)據(jù)；
假設(shè) B + 樹的高度為 2 的話，即有一個根結(jié)點和若干個葉子結(jié)點。這棵 B + 樹的存放總記錄數(shù)為 = 根結(jié)點指針數(shù) * 單個葉子節(jié)點記錄行數(shù)。
如果一行記錄的數(shù)據(jù)大小為 1k，那么單個葉子節(jié)點可以存的記錄數(shù) =16k/1k =16.非葉子節(jié)點內(nèi)存放多少指針呢？我們假設(shè)主鍵 ID 為 bigint 類型，長度為 8 字節(jié) (面試官問你 int 類型，一個 int 就是 32 位，4 字節(jié))，而指針大小在 InnoDB 源碼中設(shè)置為 6 字節(jié)，所以就是 8+6=14 字節(jié)，16k/14B =16*1024B/14B = 1170
因此，一棵高度為 2 的 B + 樹，能存放 1170 * 16=18720 條這樣的數(shù)據(jù)記錄。同理一棵高度為 3 的 B + 樹，能存放 1170 *1170 *16 =21902400，也就是說，可以存放兩千萬左右的記錄。B + 樹高度一般為 1-3 層，已經(jīng)滿足千萬級別的數(shù)據(jù)存儲。
如果 B + 樹想存儲更多的數(shù)據(jù)，那樹結(jié)構(gòu)層級就會更高，查詢一條數(shù)據(jù)時，需要經(jīng)歷的磁盤 IO 變多，因此查詢性能變慢。
4.3 如何解決單表數(shù)據(jù)量太大，查詢變慢的問題
一般超過千萬級別，我們可以考慮分庫分表了。
分庫分表可能導(dǎo)致的問題：
- 事務(wù)問題
- 跨庫問題
- 排序問題
- 分頁問題
- 分布式 ID
因此，大家在評估是否分庫分表前，先考慮下，是否可以把部分歷史數(shù)據(jù)歸檔先，如果可以的話，先不要急著分庫分表。如果真的要分庫分表，綜合考慮和評估方案。比如可以考慮垂直、水平分庫分表。水平分庫分表策略的話，range 范圍、hash 取模、range+hash 取?；旌?/strong>等等。
5. join 或者子查詢過多
一般來說，不建議使用子查詢，可以把子查詢改成 join 來優(yōu)化。而數(shù)據(jù)庫有個規(guī)范約定就是：盡量不要有超過 3 個以上的表連接。為什么要這么建議呢？我們來聊聊，join 哪些方面可能導(dǎo)致慢查詢吧。
MySQL 中，join 的執(zhí)行算法，分別是：Index Nested-Loop Join 和 Block Nested-Loop Join。
- Index Nested-Loop Join：這個 join 算法，跟我們寫程序時的嵌套查詢類似，并且可以用上被驅(qū)動表的索引。
- Block Nested-Loop Join：這種 join 算法，被驅(qū)動表上沒有可用的索引 , 它會先把驅(qū)動表的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 中，再掃描被驅(qū)動表，把被驅(qū)動表的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對比，滿足 join 條件的，作為結(jié)果集的一部分返回。
join 過多的問題：
一方面，過多的表連接，會大大增加 SQL 復(fù)雜度。另外一方面，如果可以使用被驅(qū)動表的索引那還好，并且使用小表來做驅(qū)動表，查詢效率更佳。如果被驅(qū)動表沒有可用的索引，join 是在 join_buffer 內(nèi)存做的，如果匹配的數(shù)據(jù)量比較小或者 join_buffer 設(shè)置的比較大，速度也不會太慢。但是，如果 join 的數(shù)據(jù)量比較大時，mysql 會采用在硬盤上創(chuàng)建臨時表的方式進(jìn)行多張表的關(guān)聯(lián)匹配，這種顯然效率就極低，本來磁盤的 IO 就不快，還要關(guān)聯(lián)。
一般情況下，如果業(yè)務(wù)需要的話，關(guān)聯(lián) 2~3 個表是可以接受的，但是關(guān)聯(lián)的字段需要加索引哈。如果需要關(guān)聯(lián)更多的表，建議從代碼層面進(jìn)行拆分，在業(yè)務(wù)層先查詢一張表的數(shù)據(jù)，然后以關(guān)聯(lián)字段作為條件查詢關(guān)聯(lián)表形成 map，然后在業(yè)務(wù)層進(jìn)行數(shù)據(jù)的拼裝。
6. in 元素過多
如果使用了 in，即使后面的條件加了索引，還是要注意 in 后面的元素不要過多哈。in 元素一般建議不要超過 500 個，如果超過了，建議分組，每次 500 一組進(jìn)行哈。
反例：
select user_id,name from user where user_id in (1,2,3…1000000);
如果我們對 in的條件不做任何限制的話，該查詢語句一次性可能會查詢出非常多的數(shù)據(jù)，很容易導(dǎo)致接口超時。尤其有時候，我們是用的子查詢，in 后面的子查詢，你都不知道數(shù)量有多少那種，更容易采坑（所以我把 in 元素過多抽出來作為一個小節(jié)）。如下這種子查詢：
select * from user where user_id in (select author_id from artilce where type = 1);
正例是，分批進(jìn)行，每批 500 個：
select user_id,name from user where user_id in (1,2,3…500);
如果傳參的 ids 太多，還可以做個參數(shù)校驗什么的
if (userIds.size() > 500) { throw new Exception(“單次查詢的用戶Id不能超過200”); }7. 數(shù)據(jù)庫在刷臟頁
7.1 什么是臟頁
當(dāng)內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內(nèi)容不一致的時候，我們稱這個內(nèi)存頁為 “臟頁”。內(nèi)存數(shù)據(jù)寫入到磁盤后，內(nèi)存和磁盤上的數(shù)據(jù)頁的內(nèi)容就一致了，稱為 “干凈頁”。一般有更新 SQL 才可能會導(dǎo)致臟頁，我們回憶一下：一條更新語句是如何執(zhí)行的
7.2 一條更新語句是如何執(zhí)行的？
以下的這個更新 SQL，如何執(zhí)行的呢？
update t set c=c+1 where id=666;
- 對于這條更新 SQL，執(zhí)行器會先找引擎取 id=666 這一行。如果這行所在的數(shù)據(jù)頁本來就在內(nèi)存中的話，就直接返回給執(zhí)行器。如果不在內(nèi)存，就去磁盤讀入內(nèi)存，再返回。
- 執(zhí)行器拿到引擎給的行數(shù)據(jù)后，給這一行 C 的值加一，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
- 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到 redo log 里面，但是此時 redo log 是處于 prepare 狀態(tài)的哈。
- 執(zhí)行器生成這個操作的 binlog，并把 binlog 寫入磁盤。
- 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。
InnoDB 在處理更新語句的時候，只做了寫日志這一個磁盤操作。這個日志叫作 redo log（重做日志）。平時更新 SQL 執(zhí)行得很快，其實是因為它只是在寫內(nèi)存和 redo log 日志，等到空閑的時候，才把 redo log 日志里的數(shù)據(jù)同步到磁盤中。
有些小伙伴可能有疑惑，redo log 日志不是在磁盤嘛？那為什么不慢？其實是因為寫 redo log 的過程是順序?qū)懘疟P的。磁盤順序?qū)憰p少尋道等待時間，速度比隨機(jī)寫要快很多的。
7.3 為什么會出現(xiàn)臟頁呢？
更新 SQL 只是在寫內(nèi)存和 redo log 日志，等到空閑的時候，才把 redo log 日志里的數(shù)據(jù)同步到磁盤中。這時內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內(nèi)容不一致，就出現(xiàn)臟頁。
7.4 什么時候會刷臟頁（flush）？
InnoDB 存儲引擎的 redo log 大小是固定，且是環(huán)型寫入的，如下圖（圖片來源于 MySQL 實戰(zhàn) 45 講）：
那什么時候會刷臟頁？有幾種場景：
- redo log 寫滿了，要刷臟頁。這種情況要盡量避免的。因為出現(xiàn)這種情況時，整個系統(tǒng)就不能再接受更新啦，即所有的更新都必須堵住。
- 內(nèi)存不夠了，需要新的內(nèi)存頁，就要淘汰一些數(shù)據(jù)頁，這時候會刷臟頁
InnoDB 用緩沖池（buffer pool）管理內(nèi)存，而當(dāng)要讀入的數(shù)據(jù)頁沒有在內(nèi)存的時候，就必須到緩沖池中申請一個數(shù)據(jù)頁。這時候只能把最久不使用的數(shù)據(jù)頁從內(nèi)存中淘汰掉：如果要淘汰的是一個干凈頁，就直接釋放出來復(fù)用；但如果是臟頁呢，就必須將臟頁先刷到磁盤，變成干凈頁后才能復(fù)用。
- MySQL 認(rèn)為系統(tǒng)空閑的時候，也會刷一些臟頁
- MySQL 正常關(guān)閉時，會把內(nèi)存的臟頁都 flush 到磁盤上
7.5 為什么刷臟頁會導(dǎo)致 SQL 變慢呢？
- redo log 寫滿了，要刷臟頁，這時候會導(dǎo)致系統(tǒng)所有的更新堵住，寫性能都跌為 0 了，肯定慢呀。一般要杜絕出現(xiàn)這個情況。
- 一個查詢要淘汰的臟頁個數(shù)太多，一樣會導(dǎo)致查詢的響應(yīng)時間明顯變長。
8. order by 文件排序
order by 就一定會導(dǎo)致慢查詢嗎？不是這樣的哈，因為 order by 平時用得多，并且數(shù)據(jù)量一上來，還是走文件排序的話，很容易有慢 SQL 的。聽我娓娓道來，order by 哪些時候可能會導(dǎo)致慢 SQL 哈。
8.1 order by 的 Using filesort 文件排序
我們平時經(jīng)常需要用到 order by ，主要就是用來給某些字段排序的。
比如以下 SQL:
select name,age,city from staff where city = ‘深圳' order by age limit 10;
它表示的意思就是：查詢前 10 個，來自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。
查看 explain 執(zhí)行計劃的時候，可以看到 Extra 這一列，有一個 Using filesort，它表示用到文件排序。
8.2 order by 文件排序效率為什么較低
order by 用到文件排序時，為什么查詢效率會相對低呢？
order by 排序，分為全字段排序和 rowid 排序。它是拿 max_length_for_sort_data 和結(jié)果行數(shù)據(jù)長度對比，如果結(jié)果行數(shù)據(jù)長度超過 max_length_for_sort_data 這個值，就會走 rowid 排序，相反，則走全字段排序。
rowid 排序
rowid 排序，一般需要回表去找滿足條件的數(shù)據(jù)，所以效率會慢一點。以下這個 SQL，使用 rowid 排序，執(zhí)行過程是這樣：
- select name,age,city from staff where city = ‘深圳’ order by age limit 10;
- MySQL 為對應(yīng)的線程初始化 sort_buffer，放入需要排序的 age 字段，以及主鍵id；
- 從索引樹 idx_city， 找到第一個滿足 city='深圳’條件的主鍵id，也就是圖中的 id=9；
- 到主鍵id索引樹拿到 id=9 的這一行數(shù)據(jù)， 取 age和主鍵id 的值，存到 sort_buffer；
- 從索引樹 idx_city 拿到下一個記錄的主鍵id，即圖中的 id=13；
- 重復(fù)步驟 3、4 直到 city 的值不等于深圳為止；
- 前面 5 步已經(jīng)查找到了所有 city 為深圳的數(shù)據(jù)，在 sort_buffer 中，將所有數(shù)據(jù)根據(jù) age 進(jìn)行排序；
- 遍歷排序結(jié)果，取前 10 行，并按照 id 的值回到原表中，取出 city、name 和 age 三個字段返回給客戶端。
全字段排序
同樣的 SQL，如果是走全字段排序是這樣的：
select name,age,city from staff where city = ‘深圳' order by age limit 10;
- MySQL 為對應(yīng)的線程初始化 sort_buffer，放入需要查詢的 name、age、city 字段；
- 從索引樹 idx_city， 找到第一個滿足 city='深圳’條件的主鍵 id，也就是圖中的 id=9；
- 到主鍵 id索引樹拿到 id=9 的這一行數(shù)據(jù)， 取 name、age、city 三個字段的值，存到 sort_buffer；
- 從索引樹 idx_city 拿到下一個記錄的主鍵 id，即圖中的 id=13；
- 重復(fù)步驟 3、4 直到 city 的值不等于深圳為止；
- 前面 5 步已經(jīng)查找到了所有 city 為深圳的數(shù)據(jù)，在 sort_buffer 中，將所有數(shù)據(jù)根據(jù) age 進(jìn)行排序；
- 按照排序結(jié)果取前 10 行返回給客戶端。
sort_buffer 的大小是由一個參數(shù)控制的：sort_buffer_size。
- 如果要排序的數(shù)據(jù)小于 sort_buffer_size，排序在 sort_buffer 內(nèi)存中完成
- 如果要排序的數(shù)據(jù)大于 sort_buffer_size，則借助磁盤文件來進(jìn)行排序。
借助磁盤文件排序的話，效率就更慢一點。因為先把數(shù)據(jù)放入 sort_buffer，當(dāng)快要滿時。會排一下序，然后把 sort_buffer 中的數(shù)據(jù)，放到臨時磁盤文件，等到所有滿足條件數(shù)據(jù)都查完排完，再用歸并算法把磁盤的臨時排好序的小文件，合并成一個有序的大文件。
8.3 如何優(yōu)化 order by 的文件排序
order by 使用文件排序，效率會低一點。我們怎么優(yōu)化呢？
- 因為數(shù)據(jù)是無序的，所以就需要排序。如果數(shù)據(jù)本身是有序的，那就不會再用到文件排序啦。而索引數(shù)據(jù)本身是有序的，我們通過建立索引來優(yōu)化 order by 語句。
- 我們還可以通過調(diào)整 max_length_for_sort_data、sort_buffer_size 等參數(shù)優(yōu)化；
9. 拿不到鎖
有時候，我們查詢一條很簡單的 SQL，但是卻等待很長的時間，不見結(jié)果返回。一般這種時候就是表被鎖住了，或者要查詢的某一行或者幾行被鎖住了。我們只能慢慢等待鎖被釋放。
舉一個生活的例子哈，你和別人合租了一間房子，這個房子只有一個衛(wèi)生間的話。假設(shè)某一時刻，你們都想去衛(wèi)生間，但是對方比你早了一點點。那么此時你只能等對方出來后才能進(jìn)去。
這時候，我們可以用 show processlist 命令，看看當(dāng)前語句處于什么狀態(tài)哈。
10. delete + in 子查詢不走索引！
之前見到過一個生產(chǎn)慢 SQL 問題，當(dāng) delete 遇到 in 子查詢時，即使有索引，也是不走索引的。而對應(yīng)的 select + in 子查詢，卻可以走索引。
MySQL 版本是 5.7，假設(shè)當(dāng)前有兩張表 account 和 old_account, 表結(jié)構(gòu)如下：
CREATE TABLEold_account(idint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵Id',namevarchar(255) DEFAULT NULL COMMENT ‘賬戶名',balanceint(11) DEFAULT NULL COMMENT ‘余額',create_timedatetime NOT NULL COMMENT ‘創(chuàng)建時間',update_timedatetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新時間', PRIMARY KEY (id), KEYidx_name(name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的賬戶表';
CREATE TABLE account ( id int (11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵 Id', name varchar (255) DEFAULT NULL COMMENT ‘賬戶名', balance int (11) DEFAULT NULL COMMENT ‘余額', create_time datetime NOT NULL COMMENT ‘創(chuàng)建時間', update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新時間', PRIMARY KEY (id), KEY idx_name (name) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='賬戶表'; `
執(zhí)行的 SQL 如下：
delete from account where name in (select name from old_account);
查看執(zhí)行計劃，發(fā)現(xiàn)不走索引：
但是如果把 delete 換成 select，就會走索引。
如下：
為什么 select + in 子查詢會走索引，delete + in 子查詢卻不會走索引呢？
我們執(zhí)行以下 SQL 看看：
explain select * from account where name in (select name from old_account); show WARNINGS; //可以查看優(yōu)化后,最終執(zhí)行的sql
結(jié)果如下：
selecttest2.account.idASid,test2.account.nameASname,test2.account.balanceASbalance,test2.account.create_timeAScreate_time,test2.account.update_timeASupdate_timefromtest2.accountsemi join (test2.old_account) where (test2.account.name=test2.old_account.name)
可以發(fā)現(xiàn)，實際執(zhí)行的時候，MySQL 對 select in 子查詢做了優(yōu)化，把子查詢改成 join 的方式，所以可以走索引。但是很遺憾，對于 delete in 子查詢，MySQL 卻沒有對它做這個優(yōu)化。
日常開發(fā)中，大家注意一下這個場景哈，大家有興趣可以看下這篇文章哈：生產(chǎn)問題分析！delete in 子查詢不走索引？！
11、group by 使用臨時表
group by 一般用于分組統(tǒng)計，它表達(dá)的邏輯就是根據(jù)一定的規(guī)則，進(jìn)行分組。日常開發(fā)中，我們使用得比較頻繁。如果不注意，很容易產(chǎn)生慢 SQL。
11.1 group by 的執(zhí)行流程
假設(shè)有表結(jié)構(gòu)：
CREATE TABLEstaff(idbigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵id',id_cardvarchar(20) NOT NULL COMMENT ‘身份證號碼',namevarchar(64) NOT NULL COMMENT ‘姓名',ageint(4) NOT NULL COMMENT ‘年齡',cityvarchar(64) NOT NULL COMMENT ‘城市', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT=‘員工表';
我們查看一下這個 SQL 的執(zhí)行計劃：
explain select city ,count(*) as num from staff group by city;
- Extra 這個字段的 Using temporary 表示在執(zhí)行分組的時候使用了臨時表
- Extra 這個字段的 Using filesort 表示使用了文件排序
group by 是怎么使用到臨時表和排序了呢？我們來看下這個 SQL 的執(zhí)行流程
select city ,count(*) as num from staff group by city;
- 創(chuàng)建內(nèi)存臨時表，表里有兩個字段 city和num；
- 全表掃描 staff 的記錄，依次取出 city = ‘X’ 的記錄。
- 判斷臨時表中是否有為 city=‘X’ 的行，沒有就插入一個記錄 (X,1);
- 如果臨時表中有 city=‘X’ 的行，就將 X 這一行的 num 值加 1；
遍歷完成后，再根據(jù)字段 city 做排序，得到結(jié)果集返回給客戶端。
這個流程的執(zhí)行圖如下：
臨時表的排序是怎樣的呢？
就是把需要排序的字段，放到 sort buffer，排完就返回。在這里注意一點哈，排序分全字段排序和 rowid 排序
如果是全字段排序，需要查詢返回的字段，都放入 sort buffer，根據(jù)排序字段排完，直接返回如果是 rowid 排序，只是需要排序的字段放入 sort buffer，然后多一次回表操作，再返回。
11.2 group by 可能會慢在哪里？
group by 使用不當(dāng)，很容易就會產(chǎn)生慢 SQL 問題。因為它既用到臨時表，又默認(rèn)用到排序。有時候還可能用到磁盤臨時表。
如果執(zhí)行過程中，會發(fā)現(xiàn)內(nèi)存臨時表大小到達(dá)了上限（控制這個上限的參數(shù)就是 tmp_table_size），會把內(nèi)存臨時表轉(zhuǎn)成磁盤臨時表。如果數(shù)據(jù)量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。
11.3 如何優(yōu)化 group by 呢？
從哪些方向去優(yōu)化呢？
方向 1：既然它默認(rèn)會排序，我們不給它排是不是就行啦。方向 2：既然臨時表是影響 group by 性能的 X 因素，我們是不是可以不用臨時表？
我們一起來想下，執(zhí)行 group by 語句為什么需要臨時表呢？group by 的語義邏輯，就是統(tǒng)計不同的值出現(xiàn)的個數(shù)。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統(tǒng)計就好了，就不用臨時表來記錄并統(tǒng)計結(jié)果啦？
可以有這些優(yōu)化方案：
group by 后面的字段加索引order by null 不用排序盡量只使用內(nèi)存臨時表使用 SQL_BIG_RESULT
12. 系統(tǒng)硬件或網(wǎng)絡(luò)資源
如果數(shù)據(jù)庫服務(wù)器內(nèi)存、硬件資源，或者網(wǎng)絡(luò)資源配置不是很好，就會慢一些哈。這時候可以升級配置。這就好比你的計算機(jī)有時候很卡，你可以加個內(nèi)存條什么的一個道理。如果數(shù)據(jù)庫壓力本身很大，比如高并發(fā)場景下，大量請求到數(shù)據(jù)庫來，數(shù)據(jù)庫服務(wù)器 CPU 占用很高或者 IO利用率很高，這種情況下所有語句的執(zhí)行都有可能變慢的哈。
最后
如果測試環(huán)境數(shù)據(jù)庫的一些參數(shù)配置，和生產(chǎn)環(huán)境參數(shù)配置不一致的話，也容易產(chǎn)生慢 SQL 哈。之前見過一個慢 SQL 的生產(chǎn)案例，就是測試環(huán)境用了 index merge，所以查看 explain 執(zhí)行計劃時，是可以走索引的，但是到了生產(chǎn)，卻全表掃描，最后排查發(fā)現(xiàn)是生產(chǎn)環(huán)境配置把 index merge 關(guān)閉了。
到此這篇關(guān)于總結(jié)12個MySQL慢查詢的原因分析的文章就介紹到這了,更多相關(guān) MySQL慢查詢 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
重裝MySQL最后一步失敗的完美解決方案(經(jīng)驗總結(jié))
使用MySQL都有過重裝的經(jīng)歷，要是重裝MySQL基本都是在最后一步通不過，究竟是什么原因呢？下面是我總結(jié)的一點經(jīng)驗，都是血的教訓(xùn)2014-06-06
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.12 安裝配置圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-11-11
文章講述了對 MySQL 存儲過程未執(zhí)行的問題排查過程,先后排查了數(shù)據(jù)庫版本升級、用戶權(quán)限不足,最終發(fā)現(xiàn)是存儲過程創(chuàng)建時的 SQL 模式導(dǎo)致,即使之后去掉該模式,存儲過程內(nèi)部仍沿用舊模式,重新創(chuàng)建存儲過程后問題解決,需要的朋友可以參考下2025-02-02
mysql連接器之mysql-connector-java問題
這篇文章主要介紹了mysql連接器之mysql-connector-java問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
接上篇：mysql使用mysql-udf-http效率測試筆記 ，這次不使用rest架構(gòu)，而是使用：libmemcached和memcached_functions_mysql2011-08-08
教你自動恢復(fù)MySQL數(shù)據(jù)庫的日志文件(binlog)
如果MySQL服務(wù)器啟用了二進(jìn)制日志，你可以使用mysqlbinlog工具來恢復(fù)從指定的時間點開始2014-05-05
這篇文章主要介紹了MySQL中組合字段之concat()，MySQL是關(guān)系型數(shù)據(jù)庫，我們在使用的時候往往會將對象的屬性映射成列存儲在表中，因此查詢的到的結(jié)果在不做任何處理的情況下，也是一個個單獨的屬性,需要的朋友可以參考一下2021-11-11
MySQL數(shù)據(jù)誤刪或者誤更新如何恢復(fù)詳細(xì)步驟(一看就會)
本文主要為開發(fā)人員提供在測試環(huán)境中恢復(fù)近期誤操作的少量數(shù)據(jù)的方法,首先介紹了如何下載并安裝MyFlash工具,然后詳細(xì)講解了如何利用該工具和MySQL的binlog日志來恢復(fù)誤刪或誤更新的數(shù)據(jù),介紹的非常詳細(xì),需要的朋友可以參考下2024-10-10
Mysql四種分區(qū)方式以及組合分區(qū)落地實現(xiàn)詳解
對用戶來說,分區(qū)表是一個獨立的邏輯表,但是底層由多個物理子表組成,下面這篇文章主要給大家介紹了關(guān)于Mysql四種分區(qū)方式以及組合分區(qū)落地實現(xiàn)的相關(guān)資料,需要的朋友可以參考下2022-04-04
MySQL8.0新加了很多功能,其中在用戶管理中增加了角色的管理,本文主要介紹了mysql8.0.30用戶與權(quán)限管理的實踐,具有一定的參考價值,感興趣的可以了解一下2024-04-04