手把手教你給 SQL 做個優化
本文轉載自微信公眾號「Java極客技術」，作者鴨血粉絲 。轉載本文請聯系Java極客技術公眾號。
在開始之前，咱們要知道：如果我的 SQL 語句執行的足夠快，還有沒有必要去做優化?
完全沒有必要對吧
所以我們一般說，要給 SQL 做個優化，那肯定就是這條 SQL 語句執行的比較慢了
那么，為什么它會執行比較慢呢?
SQL 語句執行較慢的 3 個原因
沒有建立索引，或者索引失效導致了 SQL 語句執行較慢
這個應該是比較好理解的，如果數據比較多，在千萬級別以上，然后呢又沒有建立索引，在這千萬級別的數據中查找你想要的內容，簡直就是在肉搏啊(哎呦，可了不得，竟然敢肉搏
索引失效這塊內容說起來就比較多了，比如在查詢的時候，讓 like 通配符在前面了，比如經常念叨的“最左匹配原則”，又比如我們在查詢條件中使用 or ，而且 or 前后條件中有一個列沒有索引，等等這些情況都會導致索引失效
鎖等待
常用的存儲引擎主要有 InnoDB 和 MyISAM 這兩種了，前者支持行鎖和表鎖，后者就只支持表鎖
如果數據庫操作都是基于表鎖的話，意思就是說，現在有個更新操作，就會把整張表鎖起來，那么查詢的操作都不被允許，所以就不要說提高系統的并發性能了
- 聰明的你肯定就知道了，既然 MyISAM 只支持表鎖，那么使用 InnoDB 不就好了?你以為 InnoDB 的行鎖不會升級成表鎖嘛?too young too simple !
- 如果對一張表進行大量的更新操作， mysql 就覺得你這樣用會讓事務的執行效率降低，到最后還是會導致性能下降，這樣的話，還不如把你的行鎖升級成表鎖呢
- 還有一點，行鎖可是基于索引加的鎖，在執行更新操作時，條件索引都失效了，那么這個鎖也會執行從行鎖升級為表鎖
不恰當的 SQL 語句
這個也比較常見了，啥是不恰當的 SQL 語句呢?就比如，明明你需要查找的內容是 name ， age ，但是呢，為了省事，直接 select *，或者在 order by 時，后面的條件不是索引字段，這就是不恰當的 SQL 語句
優化 SQL 語句
在知道了 SQL 語句執行比較慢的原因之后，接下來要做的就是對癥下藥了
針對 沒有索引/索引失效 這塊，最有效的辦法就是 EXPLAIN 語法了，那你知不知道 Show Profile 也可以嘞
針對 鎖等待 這塊，沒辦法了，只能自己多注意
針對 不恰當的 SQL 語句 這塊，介紹幾個常用的 SQL 優化，比如分頁查詢怎么優化一下可以查詢的更快一些呀，你不是說 select * 不是正確的打開方式嘛?那什么是正確的 select 方式呢?別急嘛，阿粉下面都會說到的
廢話不多說，咱們開始了
先來個表
為了確保優化后的結果和我寫的一樣(起碼 90% 是相符的
所以咱們用一樣的數據庫好不好?乖~
首先建個 demo 的數據庫
接下來咱們建表，就建個非常簡單的表好不好
- 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 萬條數據
- 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 ，準備工作做好了，接下來開始實戰
通過 EXPLAIN 分析 SQL 是怎樣執行的
只要說 SQL 調優，那就離不開 EXPLAIN
- EXPLAIN SELECT * FROMtableWHERE id < 100 ORDER BY a;
咱們能夠看到有好幾個參數:
- id :每個執行計劃都會有一個 id ，如果是一個聯合查詢的話，這里就會顯示好多個 id
- select_type :表示的是 select 查詢類型，常見的就是 SIMPLE (普通查詢，也就是沒有聯合查詢/子查詢), PRIMARY (主查詢), UNION ( UNION 中后面的查詢), SUBQUERY (子查詢)
- table :執行查詢計劃的表，在這里我查的就是 table ，所以顯示的是 table， 那如果我給 table 起了別名 a ，在這里顯示的就是 a
- type :查詢所執行的方式，這是咱們在分析 SQL 優化的時候一個非常重要的指標，這個值從好到壞依次是: system > const > eq_ref > ref > range > index > ALL
- system/const :說明表中只有一行數據匹配，這個時候根據索引查詢一次就能找到對應的數據
- eq_ref :使用唯一索引掃描，這個經常在多表連接里面，使用主鍵和唯一索引作為關聯條件時可以看到
- ref :非唯一索引掃描，也可以在唯一索引最左原則匹配掃描看到
- range :索引范圍掃描，比如查詢條件使用到了 < ， > ， between 等條件
- index :索引全表掃描，這個時候會遍歷整個索引樹
- ALL :表示全表掃描，也就是需要遍歷整張表才能找到對應的行
- possible_keys :表示可能使用到的索引
- key :實際使用到的索引
- key_len :使用的索引長度
- ref :關聯 id 等信息
- rows :找到符合條件時，所掃描的行數，在這里雖然有 10 萬條數據，但是因為索引的緣故，所以掃描了 99 行的數據
- Extra :額外的信息，常見的有以下幾種
- Using where :不用讀取表里面的所有信息，只需要通過索引就可以拿到需要的數據，這個過程發生在對表的全部請求列都是同一個索引部分時
- Using temporary :表示 mysql 需要使用臨時表來存儲結果集，常見于 group by / order by
- Using filesort :當查詢的語句中包含 order by 操作的時候，而且 order by 后面的內容不是索引，這樣就沒有辦法利用索引完成排序，就會使用"文件排序",就像例子中給出的，建立的索引是 id ， 但是我的查詢語句 order by 后面是 a ，沒有辦法使用索引
- Using join buffer :使用了連接緩存
- Using index :使用了覆蓋索引
如果對這些參數了解的非常不錯，那么 EXPLAIN 這塊內容就難不住你了
Show Profile 分析下 SQL 執行性能
通過 EXPLAIN 分析執行計劃，只能說明 SQL 的外部執行情況，如果想要知道 mysql 具體是如何查詢的，需要通過 Show Profile 來分析
可以通過 SHOW PROFILES; 語句來查詢最近發送給服務器的 SQL 語句，默認情況下是記錄最近已經執行的 15 條記錄，如下圖我們可以看到:
我想看具體的一條語句，看到 Query_ID 了嘛?然后運行下 SHOW PROFILE FOR QUERY 82;這條命令就可以了:
可以看到，在結果中， Sending data 耗時是最長的，這是因為此時 mysql 線程開始讀取數據并且把這些數據返回到客戶端，在這個過程中會有大量磁盤 I/O 操作
通過這樣的分析，我們就能知道， SQL 語句在查詢過程中，到底是 磁盤 I/O 影響了查詢速度，還是 System lock 影響了查詢速度，知道了病癥所在，接下來對癥下藥就容易多了
分頁查詢怎么可以更快一些在使用分頁查詢時，都會使用 limit 關鍵字
但是對于分頁查詢，其實還可以優化一步
我這里給出的數據庫不是太好，因為它太簡單了，看不出來有什么區別，我使用目前項目上正在用的表來做個實驗，可以看下區別(使用的 SQL 語句如下面):
- 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;
上面一張圖片，我沒有使用子查詢，可以看到執行了 0.033s ，下面的查詢語句，我使用了子查詢去做優化，能夠看到執行了 0.007s ，優化的結果還是很顯而易見的
那么，為什么使用了子查詢，查詢的速度就提上來了呢，這是因為當我們沒有使用子查詢時，查詢到的 10020 行數據都返回回來了，接下來要對這 10020 行數據再進行過濾操作
那可不可以直接就返回需要的 20 行數據呢，這樣就不需要再做過濾操作了，直接返回就可以了嘛
你也太聰明了吧。子查詢就是在做這件事情
所以查詢時間上有了一個很大的優化
正確的 select 打開方式
在查詢時，有時為了省事，直接使用 select * from table where id = 1 這樣的 SQL 語句，但是這樣的寫法在一些環境下是會存在一定的性能損耗的
所以最好的 select 查詢就是，需要什么字段就查詢什么字段
一般在查詢時，都會有條件，按照條件查找
這個時候正確的 select 打開方式是什么呢?
如果可以通過主鍵索引的話， where 后面的條件，優先選擇主鍵索引
為什么呢?這就要知道 MySQL 的存儲規則
MySQL 常用的存儲引擎有 MyISAM 和 InnoDB ， InnoDB 會創建主鍵索引，而主鍵索引屬于聚簇索引，也就是在存儲數據時，索引是基于 B+ 樹構成的，具體的行數據則存儲在葉子節點
也就是說，如果是通過主鍵索引查詢的，會直接搜索 B+ 樹，從而查詢到數據
如果不是通過主鍵索引查詢的，需要先搜索索引樹，得到在 B+ 樹上的值，再到 B+ 樹上搜索符合條件的數據，這個過程就是“回表”
很顯然，回表能夠產生時間。
這也是為什么建議， where 后面的條件，優先選擇主鍵索引
其他調優
看完上面的，心里應該就大概有數了， SQL 調優主要就是建立索引/防止產生鎖等待/使用恰當的 SQL 語句去查詢
但是，如果問你除了索引，除了上面這些手段，還有沒有其他調優方式
啥?竟然還有?!
有的，這就需要跳出來，不要局限在具體的 SQL 語句上了，需要在數據庫設計之初就考慮好
比如說，我們常說的要遵循三范式，但是在有的業務場景里面，如果在數據庫里面多幾個冗余字段的話，可能要比嚴格遵循三范式帶來的性能要好很多。
但是這點就及其考驗平時的積累了，阿粉在這里把這一點提出來之后，希望讀者們可以看看自己項目上目前用的數據庫有沒有多余的字段，為什么要這樣設計呢?這樣多去觀察，你的技術能力想不提高都很難
以上，就這樣啦~
