【前言】

經常有一些朋友向我諮詢，如何寫出高效的SQL，這不是三言兩語能說得清的，索性認真來寫一下，增刪查改方面的知識我不再贅述，如果有基礎薄弱的同學，可以好好的補一補再來看。

以MySQL為基礎，MySQL調優篇內容主要包含MySQL邏輯架構、索引知識、表關聯演算法、explain執行計劃解讀及SQL調優實戰等。

文章受眾主要為兩類人：

第一類人是工作中不可避免的會接觸到MySQL的人，比如說一些專案人員、開發人員、測試人員等。

第二類人是專職DBA。

其實不管是專職的還是非專職的，就我接觸到的情況而言，很多DBA平時維護MySQL看起來沒什麼問題，但其實沒有很好的理論支撐，知其然而不知其所以然，解釋一個簡單的問題就能問倒一大部分的人。

比如說：MySQL的邏輯架構，分析當前業務架構優缺點？SQL工作原理是什麼樣的？

而且很多公司招聘面試的時候，考驗的也是背後的原理居多，基本上沒有機試。面試官問一個問題，即便你會解決但就是說不出原理，那麼你肯定要不了高薪。

理論+實戰=高薪

文章能夠讓大家有所收穫、有所借鑑那是最好的。

【 SQL調優實戰】

1、環境準備

每張表模擬一些資料進去。

123456 article表 CREATE TABLE IF NOT EXISTS `article`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`author_id` INT (10) UNSIGNED NOT NULL,`category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSIGNED NOT NULL,`title` VARBINARY(255) NOT NULL,`content` TEXT NOT NULL);   class 表 CREATE TABLE IF NOT EXISTS ` class `(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL); book表 CREATE TABLE IF NOT EXISTS `book`(`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL); phone表CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL)ENGINE = INNODB; staffs表 CREATE TABLE staffs(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(24)NOT NULL DEFAULT '' COMMENT '姓名' ,`age` INT NOT NULL DEFAULT 0 COMMENT '年齡' ,`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '職位' ,`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間' )CHARSET utf8 COMMENT '員工記錄表' ; ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

2、單表最佳化案例

需求分析：

查詢category_id為1且comments大於1的情況下，views最多的article_id

select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

執行計劃：

5.7版本後新增了列filtered，意思是：指返回結果的行佔需要讀到的行(rows列的值)的百分比，filtered的數值其實越高，表示透過索引直接返回的行很多，數值較低時，一般出現在type=ALL或者index的情況。

分析下這個執行計劃，type=ALL全表掃，而且產生了filesort。

where條件加個複合索引看看：

create index idx_atc_ccv on article(category_id,comments,views);

再看執行計劃：

雖然走了索引，但也走到了filesort，還是不夠好；這個索引不起作用嗎？

在Mysql中，索引中出現了範圍查詢，後面就失效，comments出現了範圍，索引在找的時候，發現comments無法直接定位到，影響了order by views的索引排序，進而出現了filesort。

那假設我們把sql調整為comments = 1再看看執行計劃。

filesort沒有了，type一下從range變成了ref，執行計劃是好的，但是業務變了，不行！

那麼怎麼建立索引合適呢？既然範圍之後索引失效，那麼我們能不能繞過去？直接新建category_id, views的複合索引呢。（刪除之前建立的索引）

執行計劃告訴我們，這個索引加的很合適！

結論：type變成了range，這是可以忍受的，但是Extra裡出現了filesort是無法接受的，但是我們建立了索引為什麼沒有用，這是因為按照Mysql的BTREE工作原理，先排序category_id，如果遇到相同的，再排序comments，如果遇到相同的，再排序views，當comments位置處於聯合（複合）索引的中間位置時，Mysql無法對範圍（range）後面的欄位進行索引排序，從而後面的欄位索引失效！

3、兩表最佳化案例

來看個SQL：

select * from class left join book on class.card = book.card;

執行計劃：

明顯這個type為ALL，索引也沒有加。問題來了，索引加哪邊？是加class.card還是book.card？

我們都試試，先新增右邊book表的索引：

alter table book add index idx_b_card(card);

執行計劃走下：book的很明顯的改變，type變成了ref

此時我把book表的索引刪掉，而建立class左表的索引看看執行計劃：

明顯，加了class表的索引後，發現type是index，並且rows20行記錄，全索引掃描，效能不會有剛剛的好！

同樣的sql，同樣的索引列，左連線的時候，加的索引所在的表不同，效果不同；

結論：上面出現效果不同，這個是由左連線的特性決定的，left join 條件用於確定如何從右邊搜尋行，而左邊一定是都有的；左邊全有，確定核心的點在於確定如何從右表中搜尋資料行，右邊是關鍵點，要加索引！所以左連線索引加在右表上，同理，右連線也是相反加！

有沒有人好奇，如果兩個索引都建呢會是什麼樣？我們嘗試下加上看看：

現在book和class表上的card欄位都加了索引，效果比上面兩個都好！

4、三表最佳化案例

先把之前建立的索引都清除掉。

SQL如下：

select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

執行計劃：

此時三個表都沒有索引：我們走下執行計劃後發現，Extra欄位多了Using join buffer；首先join buffer意思是使用了連線快取。

在5.7之後，Mysql對錶和表之間的連線，做了最佳化快取，實際上在A left join B的過程，Mysql會更在意B的表往A中相同的部分，所以類似一個for迴圈，最外層for A，內層是for B，找到B中的每一行滿足A行的記錄，因為是要A的全部，所以最外層一定是A，然後合併行，最後輸出；而在3表中，等於3個for迴圈。

其中其實發現有個Block Nested-Loop Join——BNL演算法，這個演算法將外層迴圈的行/結果集存入join buffer, 內層迴圈的每一行與整個buffer中的記錄做比較，從而減少內層迴圈的次數。所以最外層的表是class，先for整個class，然後放在join buffer裡，接下來迴圈內表的時候，直接取buffer的行去比對，減少對磁碟的IO。

但是整個type=ALL，rows都是20，全表掃，這是我們無法接受的。

那麼三張表怎麼加索引呢？可以想想，左連線建右表上，那麼這個是不是說class左表，建立索引在book和phone上？試試！

走下執行計劃看看：很明顯，改善很多!

那麼很明顯這個原則也成立，總結下：

儘可能減少join語句中的NestedLoop迴圈總次數，永遠用小結果集驅動大的結果集，這裡的例子，就是左表儘量資料小於右表，外層for的次數就減少了，IO次數也會降低。

其實你可以試試，如果class表加了索引，效果會比右連線稍微好點，哈哈

5、索引失效案例

5.1建個複合索引

create index idx_s_nap on staffs( name,age,pos);

SQL如下：

select * from staffs where name= 'July';

索引會不會失效？執行計劃：

沒問題。
再來一個SQL：

select * from staffs where name='July' and age = 25;

執行計劃：

一樣沒問題。

但是這裡其實有個問題，Extra為Null。Extra為Null的時候，如果走了索引，說明這個查詢，進行了回表！

那麼什麼是回表呢？

簡單來說，如果你查詢的欄位，存在非索引欄位，那麼查詢的時候，Mysql雖然根據了你的條件得到了這個記錄，但是不在索引的欄位無法透過索引的方式直接得到，只能透過拿到該條記錄的主鍵索引，再從資料行裡讀，我們知道Mysql索引檔案和資料檔案是在兩個不同的檔案裡的，要去讀磁碟；所以索引檔案建立的效果，就是幫助我們對資料進行排序和查詢效率的最佳化，不至於去讀資料行進行額外的IO開銷；

所以這裡欄位我用select *，因為複合索引裡沒有add_time這個欄位，所以無法直接查出來add_time這個列的記錄，要透過定位到主鍵，然後再讀一次資料行才可以得到這個記錄，稱為回表。
如果SQL這麼寫，就不會出現回表，因為pos在索引列中！

select pos from staffs where name='July' and age = 25;

執行計劃：

我們來看一些特殊場景！
SQL如下：

select * from staffs where age = 23 and pos = 'dev';

執行計劃：

再來一個sql：

select * from staffs where name = 'zhangsan';

走索引了。

總結：如果查詢中沒有開頭的索引，不好意思，只能全表掃。違背了【最、佳左字首法則】

再看下這個sql：

select * from staffs where name = 'zhangsan' and pos = 'dev';

執行計劃顯示這個key_len和只有name的時候一樣，說明只走了name索引，Extra中出現Using index condition，這個是5.6後新加的特性，會先條件過濾索引，過濾完索引後找到所有符合索引條件的資料行，隨後用 WHERE 子句中的其他條件去過濾這些資料行；就是走到了索引上的意思。

5.2、勿在索引列做任何操作
不要在索引列上做任何操作，包括計算，函式，自動或者手動型別轉換，會導致索引失效而轉向全表掃描。
SQL：

select * from staffs where left(name, 4) = 'July';

查詢name左往右4個字元為July的行。索引失效了！

5.3、範圍之後全失效

SQL：

select * from staffs where name = 'July' and age > 14 and pos = 'manager';

age用到了索引，進行範圍查詢，但是後面的索引pos就失效了，這裡要注意，5.7以前的最佳化，是如果出現了範圍查詢，則當前範圍的索引也不走，而5.7後，範圍索引之後的才失效，所以這裡的key_len=78，單個name話是74，三個都走是140。
5.4、不等於場景下索引失效
SQL：

select * from staffs where name != 'July';

select * from staffs where name <> 'July';

在使用不等於的場景下，無法使用索引導致全表掃描。
5.5、is null、is not null無法使用索引
SQL：

select * from staffs where name is null;  select * from staffs where name is not null;

5.6、Like百分寫最右

like以萬用字元開頭('%abc...')時，Mysql索引會失效變成全表掃！

SQL：

select * from staffs where name like '%July%'; select * from staffs where name like '%July';  select * from staffs where name like 'July%';

因為like是範圍查詢，百分號在後面，Mysql會拿到字典序進行排序的方式查詢對應的情況，而百分號在前面，Mysql就不知道從哪個字母開始找，於是便全表掃描。

實際面試中經常會這麼問：如何解決like '%xxx%' 字元時索引不被使用的情況？

答案是用覆蓋索引避免索引失效，我們這裡的索引是(name, age, pos)，索引我們在查詢的時候不要寫select *，只要寫具體的欄位值，任何一個列被覆蓋索引覆蓋，就可以解決兩邊百分號的問題！！！
5.7、字串不加單引號索引失效
SQL：

explain select * from staffs where name = 222;

索引失效。

而這個是成功走到索引的：

select * from staffs where name = '222';

Mysql很聰明，你以為你給我的我就查不到了，你給我的Int型的時候，實際這個欄位是varchar型，傳入數字會隱式的幫你轉換成varchar型別，前面說過不要讓Mysql做這些自動或者手動的型別轉換，否則索引失效！當然查詢的結果，是不會有變化的，只是sql執行上有轉換。
5.8、少用or SQL：

select * from staffs where name = 'July' or name = 'z3';

少用or，會導致索引失效，不是不用；

【結語】
MySQL調優篇寫到這裡就差不多告一段落了，希望大家都能真真正正能寫出高效能的SQL， 結合實踐中不斷的實驗和摸索，早日晉級資、深或者架構師。
後面有機會講一些Mysql的其他知識點。 共勉！

【往期回顧】
MySQL調優篇 | EXPLAIN執行計劃解讀（4）
MySQL調優篇 | 表連線方式及演算法（3）
MySQL調優篇 | 索引知識解讀（2）
MySQL調優篇 | 邏輯架構解讀（1）

更多內容請關注“資料與人”

MySQL調優篇 | SQL調優實戰（5）

相關文章