[[337313]]

本文轉(zhuǎn)載自微信公眾號(hào)「Java極客技術(shù)」，作者鴨血粉絲 。轉(zhuǎn)載本文請(qǐng)聯(lián)系Java極客技術(shù)公眾號(hào)。 

在開始之前，咱們要知道：如果我的 SQL 語(yǔ)句執(zhí)行的足夠快，還有沒有必要去做優(yōu)化?

完全沒有必要對(duì)吧

所以我們一般說，要給 SQL 做個(gè)優(yōu)化，那肯定就是這條 SQL 語(yǔ)句執(zhí)行的比較慢了

那么，為什么它會(huì)執(zhí)行比較慢呢?

SQL 語(yǔ)句執(zhí)行較慢的 3 個(gè)原因

沒有建立索引，或者索引失效導(dǎo)致了 SQL 語(yǔ)句執(zhí)行較慢

這個(gè)應(yīng)該是比較好理解的，如果數(shù)據(jù)比較多，在千萬(wàn)級(jí)別以上，然后呢又沒有建立索引，在這千萬(wàn)級(jí)別的數(shù)據(jù)中查找你想要的內(nèi)容，簡(jiǎn)直就是在肉搏啊(哎呦，可了不得，竟然敢肉搏

索引失效這塊內(nèi)容說起來就比較多了，比如在查詢的時(shí)候，讓 like 通配符在前面了，比如經(jīng)常念叨的“最左匹配原則”，又比如我們?cè)诓樵儣l件中使用 or ，而且 or 前后條件中有一個(gè)列沒有索引，等等這些情況都會(huì)導(dǎo)致索引失效

鎖等待

常用的存儲(chǔ)引擎主要有 InnoDB 和 MyISAM 這兩種了，前者支持行鎖和表鎖，后者就只支持表鎖

如果數(shù)據(jù)庫(kù)操作都是基于表鎖的話，意思就是說，現(xiàn)在有個(gè)更新操作，就會(huì)把整張表鎖起來，那么查詢的操作都不被允許，所以就不要說提高系統(tǒng)的并發(fā)性能了

• 聰明的你肯定就知道了，既然 MyISAM 只支持表鎖，那么使用 InnoDB 不就好了?你以為 InnoDB 的行鎖不會(huì)升級(jí)成表鎖嘛?too young too simple !
• 如果對(duì)一張表進(jìn)行大量的更新操作， mysql 就覺得你這樣用會(huì)讓事務(wù)的執(zhí)行效率降低，到最后還是會(huì)導(dǎo)致性能下降，這樣的話，還不如把你的行鎖升級(jí)成表鎖呢
• 還有一點(diǎn)，行鎖可是基于索引加的鎖，在執(zhí)行更新操作時(shí)，條件索引都失效了，那么這個(gè)鎖也會(huì)執(zhí)行從行鎖升級(jí)為表鎖

不恰當(dāng)?shù)?SQL 語(yǔ)句

這個(gè)也比較常見了，啥是不恰當(dāng)?shù)?SQL 語(yǔ)句呢?就比如，明明你需要查找的內(nèi)容是 name ， age ，但是呢，為了省事，直接 select *，或者在 order by 時(shí)，后面的條件不是索引字段，這就是不恰當(dāng)?shù)?SQL 語(yǔ)句

優(yōu)化 SQL 語(yǔ)句

在知道了 SQL 語(yǔ)句執(zhí)行比較慢的原因之后，接下來要做的就是對(duì)癥下藥了

針對(duì) 沒有索引/索引失效 這塊，最有效的辦法就是 EXPLAIN 語(yǔ)法了，那你知不知道 Show Profile 也可以嘞

針對(duì) 鎖等待 這塊，沒辦法了，只能自己多注意

針對(duì) 不恰當(dāng)?shù)?SQL 語(yǔ)句 這塊，介紹幾個(gè)常用的 SQL 優(yōu)化，比如分頁(yè)查詢?cè)趺磧?yōu)化一下可以查詢的更快一些呀，你不是說 select * 不是正確的打開方式嘛?那什么是正確的 select 方式呢?別急嘛，阿粉下面都會(huì)說到的

廢話不多說，咱們開始了

先來個(gè)表

為了確保優(yōu)化后的結(jié)果和我寫的一樣(起碼 90% 是相符的

所以咱們用一樣的數(shù)據(jù)庫(kù)好不好?乖~

首先建個(gè) demo 的數(shù)據(jù)庫(kù)

接下來咱們建表，就建個(gè)非常簡(jiǎn)單的表好不好

CREATE TABLE demo.table( 
 id int(11) NOT NULL, 
 a int(11) DEFAULT NULL, 
 b int(11) DEFAULT NULL, 
 PRIMARY KEY(id) 
) ENGINE = INNODB 

然后插入 10 萬(wàn)條數(shù)據(jù)

DROP PROCEDURE IF EXISTS demo_insert; 
CREATE PROCEDURE demo_insert() 
BEGIN 
    DECLARE i INT;  
  SET i = 1; 
    WHILE i <= 100000 DO 
        INSERT INTO demo.`table` VALUES (i, i, i); 
        SET i = i + 1 ; 
    END WHILE; 
END; 
CALL demo_insert(); 

OK ，準(zhǔn)備工作做好了，接下來開始實(shí)戰(zhàn)

通過 EXPLAIN 分析 SQL 是怎樣執(zhí)行的

只要說 SQL 調(diào)優(yōu)，那就離不開 EXPLAIN

EXPLAIN SELECT * FROMtableWHERE id < 100 ORDER BY a; 

咱們能夠看到有好幾個(gè)參數(shù):

• id :每個(gè)執(zhí)行計(jì)劃都會(huì)有一個(gè) id ，如果是一個(gè)聯(lián)合查詢的話，這里就會(huì)顯示好多個(gè) id
• select_type :表示的是 select 查詢類型，常見的就是 SIMPLE (普通查詢，也就是沒有聯(lián)合查詢/子查詢), PRIMARY (主查詢), UNION ( UNION 中后面的查詢), SUBQUERY (子查詢)
• table :執(zhí)行查詢計(jì)劃的表，在這里我查的就是 table ，所以顯示的是 table， 那如果我給 table 起了別名 a ，在這里顯示的就是 a
• type :查詢所執(zhí)行的方式，這是咱們?cè)诜治?SQL 優(yōu)化的時(shí)候一個(gè)非常重要的指標(biāo)，這個(gè)值從好到壞依次是: system > const > eq_ref > ref > range > index > ALL
  • system/const :說明表中只有一行數(shù)據(jù)匹配，這個(gè)時(shí)候根據(jù)索引查詢一次就能找到對(duì)應(yīng)的數(shù)據(jù)
  • eq_ref :使用唯一索引掃描，這個(gè)經(jīng)常在多表連接里面，使用主鍵和唯一索引作為關(guān)聯(lián)條件時(shí)可以看到
  • ref :非唯一索引掃描，也可以在唯一索引最左原則匹配掃描看到
  • range :索引范圍掃描，比如查詢條件使用到了 < ， > ， between 等條件
  • index :索引全表掃描，這個(gè)時(shí)候會(huì)遍歷整個(gè)索引樹
  • ALL :表示全表掃描，也就是需要遍歷整張表才能找到對(duì)應(yīng)的行
• possible_keys :表示可能使用到的索引
• key :實(shí)際使用到的索引
• key_len :使用的索引長(zhǎng)度
• ref :關(guān)聯(lián) id 等信息
• rows :找到符合條件時(shí)，所掃描的行數(shù)，在這里雖然有 10 萬(wàn)條數(shù)據(jù)，但是因?yàn)樗饕木壒剩話呙枇?99 行的數(shù)據(jù)
• Extra :額外的信息，常見的有以下幾種
  • Using where :不用讀取表里面的所有信息，只需要通過索引就可以拿到需要的數(shù)據(jù)，這個(gè)過程發(fā)生在對(duì)表的全部請(qǐng)求列都是同一個(gè)索引部分時(shí)
  • Using temporary :表示 mysql 需要使用臨時(shí)表來存儲(chǔ)結(jié)果集，常見于 group by / order by
  • Using filesort :當(dāng)查詢的語(yǔ)句中包含 order by 操作的時(shí)候，而且 order by 后面的內(nèi)容不是索引，這樣就沒有辦法利用索引完成排序，就會(huì)使用"文件排序",就像例子中給出的，建立的索引是 id ， 但是我的查詢語(yǔ)句 order by 后面是 a ，沒有辦法使用索引
  • Using join buffer :使用了連接緩存
  • Using index :使用了覆蓋索引

如果對(duì)這些參數(shù)了解的非常不錯(cuò)，那么 EXPLAIN 這塊內(nèi)容就難不住你了

Show Profile 分析下 SQL 執(zhí)行性能

通過 EXPLAIN 分析執(zhí)行計(jì)劃，只能說明 SQL 的外部執(zhí)行情況，如果想要知道 mysql 具體是如何查詢的，需要通過 Show Profile 來分析

可以通過 SHOW PROFILES; 語(yǔ)句來查詢最近發(fā)送給服務(wù)器的 SQL 語(yǔ)句，默認(rèn)情況下是記錄最近已經(jīng)執(zhí)行的 15 條記錄，如下圖我們可以看到:

我想看具體的一條語(yǔ)句，看到 Query_ID 了嘛?然后運(yùn)行下 SHOW PROFILE FOR QUERY 82;這條命令就可以了:

可以看到，在結(jié)果中， Sending data 耗時(shí)是最長(zhǎng)的，這是因?yàn)榇藭r(shí) mysql 線程開始讀取數(shù)據(jù)并且把這些數(shù)據(jù)返回到客戶端，在這個(gè)過程中會(huì)有大量磁盤 I/O 操作

通過這樣的分析，我們就能知道， SQL 語(yǔ)句在查詢過程中，到底是 磁盤 I/O 影響了查詢速度，還是 System lock 影響了查詢速度，知道了病癥所在，接下來對(duì)癥下藥就容易多了

分頁(yè)查詢?cè)趺纯梢愿煲恍┰谑褂梅猪?yè)查詢時(shí)，都會(huì)使用 limit 關(guān)鍵字

但是對(duì)于分頁(yè)查詢，其實(shí)還可以優(yōu)化一步

我這里給出的數(shù)據(jù)庫(kù)不是太好，因?yàn)樗?jiǎn)單了，看不出來有什么區(qū)別，我使用目前項(xiàng)目上正在用的表來做個(gè)實(shí)驗(yàn)，可以看下區(qū)別(使用的 SQL 語(yǔ)句如下面):

EXPLAIN SELECT * FROM `te_paper_record` ORDER BY id LIMIT 10000, 20; 
 
EXPLAIN SELECT * FROM `te_paper_record` WHERE id >= ( SELECT id FROM `te_paper_record` ORDER BY id LIMIT 10000, 1) LIMIT 20; 

上面一張圖片，我沒有使用子查詢，可以看到執(zhí)行了 0.033s ，下面的查詢語(yǔ)句，我使用了子查詢?nèi)プ鰞?yōu)化，能夠看到執(zhí)行了 0.007s ，優(yōu)化的結(jié)果還是很顯而易見的

那么，為什么使用了子查詢，查詢的速度就提上來了呢，這是因?yàn)楫?dāng)我們沒有使用子查詢時(shí)，查詢到的 10020 行數(shù)據(jù)都返回回來了，接下來要對(duì)這 10020 行數(shù)據(jù)再進(jìn)行過濾操作

那可不可以直接就返回需要的 20 行數(shù)據(jù)呢，這樣就不需要再做過濾操作了，直接返回就可以了嘛

你也太聰明了吧。子查詢就是在做這件事情

所以查詢時(shí)間上有了一個(gè)很大的優(yōu)化

正確的 select 打開方式

在查詢時(shí)，有時(shí)為了省事，直接使用 select * from table where id = 1 這樣的 SQL 語(yǔ)句，但是這樣的寫法在一些環(huán)境下是會(huì)存在一定的性能損耗的

所以最好的 select 查詢就是，需要什么字段就查詢什么字段

一般在查詢時(shí)，都會(huì)有條件，按照條件查找

這個(gè)時(shí)候正確的 select 打開方式是什么呢?

如果可以通過主鍵索引的話， where 后面的條件，優(yōu)先選擇主鍵索引

為什么呢?這就要知道 MySQL 的存儲(chǔ)規(guī)則

MySQL 常用的存儲(chǔ)引擎有 MyISAM 和 InnoDB ， InnoDB 會(huì)創(chuàng)建主鍵索引，而主鍵索引屬于聚簇索引，也就是在存儲(chǔ)數(shù)據(jù)時(shí)，索引是基于 B+ 樹構(gòu)成的，具體的行數(shù)據(jù)則存儲(chǔ)在葉子節(jié)點(diǎn)

也就是說，如果是通過主鍵索引查詢的，會(huì)直接搜索 B+ 樹，從而查詢到數(shù)據(jù)

如果不是通過主鍵索引查詢的，需要先搜索索引樹，得到在 B+ 樹上的值，再到 B+ 樹上搜索符合條件的數(shù)據(jù)，這個(gè)過程就是“回表”

很顯然，回表能夠產(chǎn)生時(shí)間。

這也是為什么建議， where 后面的條件，優(yōu)先選擇主鍵索引

其他調(diào)優(yōu)

看完上面的，心里應(yīng)該就大概有數(shù)了， SQL 調(diào)優(yōu)主要就是建立索引/防止產(chǎn)生鎖等待/使用恰當(dāng)?shù)?SQL 語(yǔ)句去查詢

但是，如果問你除了索引，除了上面這些手段，還有沒有其他調(diào)優(yōu)方式

啥?竟然還有?!

有的，這就需要跳出來，不要局限在具體的 SQL 語(yǔ)句上了，需要在數(shù)據(jù)庫(kù)設(shè)計(jì)之初就考慮好

比如說，我們常說的要遵循三范式，但是在有的業(yè)務(wù)場(chǎng)景里面，如果在數(shù)據(jù)庫(kù)里面多幾個(gè)冗余字段的話，可能要比嚴(yán)格遵循三范式帶來的性能要好很多。

但是這點(diǎn)就及其考驗(yàn)平時(shí)的積累了，阿粉在這里把這一點(diǎn)提出來之后，希望讀者們可以看看自己項(xiàng)目上目前用的數(shù)據(jù)庫(kù)有沒有多余的字段，為什么要這樣設(shè)計(jì)呢?這樣多去觀察，你的技術(shù)能力想不提高都很難

以上，就這樣啦~