如何給SQL做個(gè)優(yōu)化

本篇內(nèi)容主要講解“如何給SQL做個(gè)優(yōu)化”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“如何給SQL做個(gè)優(yōu)化”吧!

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

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

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

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

鎖等待

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

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

• 聰明的你肯定就知道了，既然 MyISAM 只支持表鎖，那么使用 InnoDB 不就好了?你以為 InnoDB 的行鎖不會(huì)升級(jí)成表鎖嘛?too young  too simple !

• 如果對(duì)一張表進(jìn)行大量的更新操作， mysql  就覺(jué)得你這樣用會(huì)讓事務(wù)的執(zhí)行效率降低，到最后還是會(huì)導(dǎo)致性能下降，這樣的話(huà)，還不如把你的行鎖升級(jí)成表鎖呢

• 還有一點(diǎn)，行鎖可是基于索引加的鎖，在執(zhí)行更新操作時(shí)，條件索引都失效了，那么這個(gè)鎖也會(huì)執(zhí)行從行鎖升級(jí)為表鎖

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

這個(gè)也比較常見(jiàn)了，啥是不恰當(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í)行比較慢的原因之后，接下來(lái)要做的就是對(duì)癥下藥了

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

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

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

廢話(huà)不多說(shuō)，咱們開(kāi)始了

先來(lái)個(gè)表

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

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

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

接下來(lái)咱們建表，就建個(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)備工作做好了，接下來(lái)開(kāi)始實(shí)戰(zhàn)

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

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

EXPLAIN SELECT * FROMtableWHERE id < 100 ORDER BY a;

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

• id :每個(gè)執(zhí)行計(jì)劃都會(huì)有一個(gè) id ，如果是一個(gè)聯(lián)合查詢(xún)的話(huà)，這里就會(huì)顯示好多個(gè) id

• select_type :表示的是 select 查詢(xún)類(lèi)型，常見(jiàn)的就是 SIMPLE (普通查詢(xún)，也就是沒(méi)有聯(lián)合查詢(xún)/子查詢(xún)), PRIMARY  (主查詢(xún)), UNION ( UNION 中后面的查詢(xún)), SUBQUERY (子查詢(xún))

• table :執(zhí)行查詢(xún)計(jì)劃的表，在這里我查的就是 table ，所以顯示的是 table， 那如果我給 table 起了別名 a ，在這里顯示的就是  a

• type :查詢(xún)所執(zhí)行的方式，這是咱們?cè)诜治?SQL 優(yōu)化的時(shí)候一個(gè)非常重要的指標(biāo)，這個(gè)值從好到壞依次是: system > const >  eq_ref > ref > range > index > ALL

• system/const :說(shuō)明表中只有一行數(shù)據(jù)匹配，這個(gè)時(shí)候根據(jù)索引查詢(xún)一次就能找到對(duì)應(yīng)的數(shù)據(jù)

• eq_ref :使用唯一索引掃描，這個(gè)經(jīng)常在多表連接里面，使用主鍵和唯一索引作為關(guān)聯(lián)條件時(shí)可以看到

• ref :非唯一索引掃描，也可以在唯一索引最左原則匹配掃描看到

• range :索引范圍掃描，比如查詢(xún)條件使用到了 < ， > ， between 等條件

• index :索引全表掃描，這個(gè)時(shí)候會(huì)遍歷整個(gè)索引樹(shù)

• 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)樗饕木壒剩話(huà)呙枇?99 行的數(shù)據(jù)

• Extra :額外的信息，常見(jiàn)的有以下幾種

• Using where :不用讀取表里面的所有信息，只需要通過(guò)索引就可以拿到需要的數(shù)據(jù)，這個(gè)過(guò)程發(fā)生在對(duì)表的全部請(qǐng)求列都是同一個(gè)索引部分時(shí)

• Using temporary :表示 mysql 需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于 group by / order by

• Using filesort :當(dāng)查詢(xún)的語(yǔ)句中包含 order by 操作的時(shí)候，而且 order by  后面的內(nèi)容不是索引，這樣就沒(méi)有辦法利用索引完成排序，就會(huì)使用"文件排序",就像例子中給出的，建立的索引是 id ， 但是我的查詢(xún)語(yǔ)句 order by 后面是  a ，沒(méi)有辦法使用索引

• Using join buffer :使用了連接緩存

• Using index :使用了覆蓋索引

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

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

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

可以通過(guò) SHOW PROFILES; 語(yǔ)句來(lái)查詢(xún)最近發(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  線(xiàn)程開(kāi)始讀取數(shù)據(jù)并且把這些數(shù)據(jù)返回到客戶(hù)端，在這個(gè)過(guò)程中會(huì)有大量磁盤(pán) I/O 操作

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

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

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

我這里給出的數(shù)據(jù)庫(kù)不是太好，因?yàn)樗?jiǎn)單了，看不出來(lái)有什么區(qū)別，我使用目前項(xiàng)目上正在用的表來(lái)做個(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;

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

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

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

你也太聰明了吧。子查詢(xún)就是在做這件事情

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

正確的 select 打開(kāi)方式

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

所以最好的 select 查詢(xún)就是，需要什么字段就查詢(xún)什么字段

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

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

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

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

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

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

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

很顯然，回表能夠產(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ǔ)句去查詢(xún)

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

啥?竟然還有?!

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

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

到此，相信大家對(duì)“如何給SQL做個(gè)優(yōu)化”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢(xún)，關(guān)注我們，繼續(xù)學(xué)習(xí)！