很多朋友在做數據分析時，分析兩分鐘，跑數兩小時？

在使用 SQL 過程中不僅要關注數據結果，同樣要注意 SQL 語句的執行效率。

本文涉及三部分：

• SQL 介紹

• SQL 優化方法

• SQL 優化實例

1、MySQL 的基本架構

1）MySQL 的基礎架構圖

左邊的 client 可以看成是客戶端，客戶端有很多，像我們經常你使用的 CMD 黑窗口，像我們經常用於學習的 WorkBench，像企業經常使用的 Navicat 工具，它們都是一個客戶端。右邊的這一大堆都可以看成是 Server(MySQL 的服務端)，我們將 Server 在細分爲 sql 層和存儲引擎層。

當查詢出數據以後，會返回給執行器。執行器一方面將結果寫到查詢緩存裏面，當你下次再次查詢的時候，就可以直接從查詢緩存中獲取到數據了。另一方面，直接將結果響應回客戶端。

2）查詢數據庫的引擎

① show engines;

② show variables like “%storage_engine%”;

3）指定數據庫對象的存儲引擎

create table tb(
    id int(4) auto_increment,
    name varchar(5),
    dept varchar(5),
    primary key(id)
) engine=myISAM auto_increment=1 default charset=utf8;

SQL 優化

1）爲什麼需要進行 SQL 優化？

在進行多表連接查詢、子查詢等操作的時候，由於你寫出的 SQL 語句欠佳，導致的服務器執行時間太長，我們等待結果的時間太長。基於此，我們需要學習怎麼優化 SQL。

2）mysql 的編寫過程和解析過程

① 編寫過程

select dinstinct  ..from  ..join ..on ..where ..group by ..having ..order by ..limit ..

② 解析過程

from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..

提供一個網站，詳細說明了 mysql 解析過程：

https://www.cnblogs.com/annsshadow/p/5037667.html

3）SQL 優化—主要就是優化索引

優化 SQL，最重要的就是優化 SQL 索引。

索引相當於字典的目錄。利用字典目錄查找漢字的過程，就相當於利用 SQL 索引查找某條記錄的過程。有了索引，就可以很方便快捷的定位某條記錄。

① 什麼是索引？

索引就是幫助 MySQL 高效獲取數據的一種【數據結構】。索引是一種樹結構，MySQL 中一般用的是【B + 樹】。

② 索引圖示說明 (這裏用二叉樹來幫助我們理解索引)

樹形結構的特點是：子元素比父元素小的，放在左側；子元素比父元素大的，放在右側。

這個圖示只是爲了幫我們簡單理解索引的，真實的關於【B + 樹】的說明，我們會在下面進行說明。

索引是怎麼查找數據的呢？兩個字【指向】，上圖中我們給 age 列指定了一個索引，即類似於右側的這種樹形結構。mysql 表中的每一行記錄都有一個硬件地址，例如索引中的 age=50，指向的就是源表中該行的標識符 (“硬件地址”)。

也就是說，樹形索引建立了與源表中每行記錄硬件地址的映射關係，當你指定了某個索引，這種映射關係也就建成了，這就是爲什麼我們可以通過索引快速定位源表中記錄的原因。

以【select * from student where age=33】查詢語句爲例。當我們不加索引的時候，會從上到下掃描源表，當掃描到第 5 行的時候，找到了我們想要找到了元素，一共是查詢了 5 次。

當添加了索引以後，就直接在樹形結構中進行查找，33 比 50 小，就從左側查詢到了 23，33 大於 23，就又查詢到了右側，這下找到了 33，整個索引結束，一共進行了 3 次查找。是不是很方便，假如我們此時需要查找 age=62，你再想想 “添加索引” 前後，查找次數的變化情況。

4）索引的弊端

1. 當數據量很大的時候，索引也會很大 (當然相比於源表來說，還是相當小的)，也需要存放在內存 / 硬盤中 (通常存放在硬盤中)，佔據一定的內存空間 / 物理空間。

2. 索引並不適用於所有情況：a. 少量數據；b. 頻繁進行改動的字段，不適合做索引；c. 很少使用的字段，不需要加索引；

3. 索引會提高數據查詢效率，但是會降低 “增、刪、改” 的效率。當不使用索引的時候，我們進行數據的增刪改，只需要操作源表即可，但是當我們添加索引後，不僅需要修改源表，也需要再次修改索引，很麻煩。儘管是這樣，添加索引還是很划算的，因爲我們大多數使用的就是查詢，“查詢”對於程序的性能影響是很大的。

5）索引的優勢

1. 提高查詢效率 (降低了 IO 使用率)。當創建了索引後，查詢次數減少了。

2. 降低 CPU 使用率。比如說【…order by age desc】這樣一個操作，當不加索引，會把源表加載到內存中做一個排序操作，極大的消耗了資源。但是使用了索引以後，第一索引本身就小一些，第二索引本身就是排好序的，左邊數據最小，右邊數據最大。

6）B + 樹圖示說明

MySQL 中索引使用的就是 B + 樹結構。

關於 B + 樹的說明：

首先，Btree 一般指的都是【B + 樹】，數據全部存放在葉子節點中。對於上圖來說，最下面的第 3 層，屬於葉子節點，真實數據部份都是存放在葉子節點當中的。

那麼對於第 1、2 層中的數據又是幹嘛的呢？答：用於分割指針塊兒的，比如說小於 26 的找 P1，介於 26-30 之間的找 P2，大於 30 的找 P3。

其次，三層【B + 樹】可以存放上百萬條數據。這麼多數據怎麼放的呢？增加 “節點數”。圖中我們只有三個節點。

最後，【B + 樹】中查詢任意數據的次數，都是 n 次，n 表示的是【B + 樹】的高度。

3、索引的分類與創建

1）索引分類

單值索引

唯一索引

複合索引

① 單值索引

利用表中的某一個字段創建單值索引。一張表中往往有多個字段，也就是說每一列其實都可以創建一個索引，這個根據我們實際需求來進行創建。還需要注意的一點就是，一張表可以創建多個 “單值索引”。

假如某一張表既有 age 字段，又有 name 字段，我們可以分別對 age、name 創建一個單值索引，這樣一張表就有了兩個單值索引。

② 唯一索引

也是利用表中的某一個字段創建單值索引，與單值索引不同的是：創建唯一索引的字段中的數據，不能有重複值。像 age 肯定有很多人的年齡相同，像 name 肯定有些人是重名的，因此都不適合創建 “唯一索引”。像編號 id、學號 sid，對於每個人都不一樣，因此可以用於創建唯一索引。

③ 複合索引

多個列共同構成的索引。比如說我們創建這樣一個 “複合索引”(name,age)，先利用 name 進行索引查詢，當 name 相同的時候，我們利用 age 再進行一次篩選。注意：複合索引的字段並不是非要都用完，當我們利用 name 字段索引出我們想要的結果以後，就不需要再使用 age 進行再次篩選了。

2）創建索引

① 語法

語法：create 索引類型 索引名 on 表 (字段);

建表語句如下：

查詢表結構如下：

② 創建索引的第一種方式

Ⅰ 創建單值索引

create index dept_index on tb(dept);

Ⅱ 創建唯一索引：這裏我們假定 name 字段中的值都是唯一的

create unique index name_index on tb(name);

Ⅲ 創建複合索引

create index dept_name_index on tb(dept,name);

③ 創建索引的第二種方式

先刪除之前創建的索引以後，再進行這種創建索引方式的測試；

語法：alter table 表名 add 索引類型 索引名 (字段)

Ⅰ 創建單值索引

alter table tb add index dept_index(dept);

Ⅱ 創建唯一索引：這裏我們假定 name 字段中的值都是唯一的

alter table tb add unique index name_index(name);

Ⅲ 創建複合索引

alter table tb add index dept_name_index(dept,name);

④ 補充說明

如果某個字段是 primary key，那麼該字段默認就是主鍵索引。

主鍵索引和唯一索引非常相似。相同點：該列中的數據都不能有相同值；不同點：主鍵索引不能有 null 值，但是唯一索引可以有 null 值。

3）索引刪除和索引查詢

① 索引刪除

語法：drop index 索引名 on 表名;

drop index name_index on tb;

② 索引查詢

語法：show index from 表名;

show index from tb;

結果如下：

4、SQL 性能問題的探索

人爲優化： 需要我們使用 explain 分析 SQL 的執行計劃。該執行計劃可以模擬 SQL 優化器執行 SQL 語句，可以幫助我們瞭解到自己編寫 SQL 的好壞。

SQL 優化器自動優化： 最開始講述 MySQL 執行原理的時候，我們已經知道 MySQL 有一個優化器，當你寫了一個 SQL 語句的時候，SQL 優化器如果認爲你寫的 SQL 語句不夠好，就會自動寫一個好一些的等價 SQL 去執行。

SQL 優化器自動優化功能【會干擾】我們的人爲優化功能。當我們查看了 SQL 執行計劃以後，如果寫的不好，我們會去優化自己的 SQL。當我們以爲自己優化的很好的時候，最終的執行計劃，並不是按照我們優化好的 SQL 語句來執行的，而是有時候將我們優化好的 SQL 改變了，去執行。

SQL 優化是一種概率問題，有時候系統會按照我們優化好的 SQL 去執行結果 (優化器覺得你寫的差不多，就不會動你的 SQL)。有時候優化器仍然會修改我們優化好的 SQL，然後再去執行。

1）查看執行計劃

語法：explain + SQL 語句

eg：explain select * from tb;

2）“執行計劃” 中需要知道的幾個 “關鍵字”

id ：編號

select_type ：查詢類型

table ：表

type ：類型

possible_keys ：預測用到的索引

key ：實際使用的索引

key_len ：實際使用索引的長度

ref ：表之間的引用

rows ：通過索引查詢到的數據量

Extra ：額外的信息

建表語句和插入數據：

# 建表語句
create table course
(    
    cid int(3),   
    cname varchar(20),   
    tid int(3)
);

create table teacher
(  
    tid int(3),  
    tname varchar(20),  
    tcid int(3)
);

create table teacherCard
(   
    tcid int(3),  
    tcdesc varchar(200)
);

# 插入數據
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

explain 執行計劃常用關鍵字詳解

1）id 關鍵字的使用說明

① 案例：查詢課程編號爲 2 或 教師證編號爲 3 的老師信息：

# 查看執行計劃
explain select t.*
from teacher t,course c,teacherCard tc
where t.tid = c.tid and t.tcid = tc.tcid
and (c.cid = 2 or tc.tcid = 3);

結果如下：

接着，在往 teacher 表中增加幾條數據。

insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);

再次查看執行計劃。

# 查看執行計劃
explain select t.*
from teacher t,course c,teacherCard tc
where t.tid = c.tid and t.tcid = tc.tcid
and (c.cid = 2 or tc.tcid = 3);

結果如下：

表的執行順序 ，因表數量改變而改變的原因：笛卡爾積。

a   b   c
2   3   4
最終：2 * 3 * 4  = 6 * 4 = 24
c   b   a
4   3   2
最終：4 * 3 * 2 = 12 * 2 = 24

分析：最終執行的條數，雖然是一致的。但是中間過程，有一張臨時表是 6，一張臨時表是 12，很明顯 6 < 12，對於內存來說，數據量越小越好，因此優化器肯定會選擇第一種執行順序。

結論：id 值相同，從上往下順序執行。表的執行順序因表數量的改變而改變。

② 案例：查詢教授 SQL 課程的老師的描述 (desc)

# 查看執行計劃
explain select tc.tcdesc from teacherCard tc 
where tc.tcid = 
(    
    select t.tcid from teacher t  
    where  t.tid =   
    (select c.tid from course c where c.cname = 'sql')
);

結果如下：

結論：id 值不同，id 值越大越優先查詢。這是由於在進行嵌套子查詢時，先查內層，再查外層。

③ 針對②做一個簡單的修改

# 查看執行計劃
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc 
where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;

結果如下：

結論：id 值有相同，又有不同。id 值越大越優先；id 值相同，從上往下順序執行。

2）select_type 關鍵字的使用說明：查詢類型

① simple：簡單查詢

不包含子查詢，不包含 union 查詢。

explain select * from teacher;

結果如下：

② primary：包含子查詢的主查詢 (最外層)

③ subquery：包含子查詢的主查詢 (非最外層)

④ derived：衍生查詢 (用到了臨時表)

a. 在 from 子查詢中，只有一張表；

b. 在 from 子查詢中，如果 table1 union table2，則 table1 就是 derived 表；

explain select  cr.cname    
from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

結果如下：

⑤ union：union 之後的表稱之爲 union 表，如上例

⑥ union result：告訴我們，哪些表之間使用了 union 查詢

3）type 關鍵字的使用說明：索引類型

system、const 只是理想狀況，實際上只能優化到 index --> range --> ref 這個級別。要對 type 進行優化的前提是，你得創建索引。

① system

源表只有一條數據 (實際中，基本不可能)；

衍生表只有一條數據的主查詢 (偶爾可以達到)。

② const

僅僅能查到一條數據的 SQL , 僅針對 Primary key 或 unique 索引類型有效。

explain select tid from test01 where tid =1 ;

結果如下：

刪除以前的主鍵索引後，此時我們添加一個其他的普通索引：

create index test01_index on test01(tid) ;
# 再次查看執行計劃
explain select tid from test01 where tid =1 ;

結果如下：

③ eq_ref

唯一性索引，對於每個索引鍵的查詢，返回匹配唯一行數據（有且只有 1 個，不能多 、不能 0），並且查詢結果和數據條數必須一致。

此種情況常見於唯一索引和主鍵索引。

delete from teacher where tcid >= 4;
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;
explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;

結果如下：

總結：以上 SQL，用到的索引是 t.tcid，即 teacher 表中的 tcid 字段；如果 teacher 表的數據個數和連接查詢的數據個數一致（都是 3 條數據），則有可能滿足 eq_ref 級別；否則無法滿足。條件很苛刻，很難達到。

④ ref

非唯一性索引，對於每個索引鍵的查詢，返回匹配的所有行（可以 0，可以 1，可以多）

準備數據：

創建索引，並查看執行計劃：

# 添加索引
alter table teacher add index index_name (tname) ;
# 查看執行計劃
explain select * from teacher     where tname = 'tz';

結果如下：

⑤ range

檢索指定範圍的行 ,where 後面是一個範圍查詢 (between,>, <, >=, in)

in 有時候會失效，從而轉爲無索引時候的 ALL

# 添加索引
alter table teacher add index tid_index (tid) ;
# 查看執行計劃：以下寫了一種等價SQL寫法，查看執行計劃
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;

結果如下：

⑥ index

查詢全部索引中的數據 (掃描整個索引)

⑦ ALL

查詢全部源表中的數據 (暴力掃描全表)

注意：cid 是索引字段，因此查詢索引字段，只需要掃描索引表即可。但是 tid 不是索引字段，查詢非索引字段，需要暴力掃描整個源表，會消耗更多的資源。

4）possible_keys 和 key

possible_keys 可能用到的索引。是一種預測，不準。瞭解一下就好。

key 指的是實際使用的索引。

# 先給course表的cname字段，添加一個索引
create index cname_index on course(cname);
# 查看執行計劃
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;

結果如下：

有一點需要注意的是：如果 possible_key/key 是 NULL，則說明沒用索引。

5）key_len

索引的長度，用於判斷複合索引是否被完全使用 (a,b,c)。

① 新建一張新表，用於測試

# 創建表
create table test_kl
(  
  name char(20) not null default ''
);
# 添加索引
alter table test_kl add index index_name(name) ;
# 查看執行計劃
explain select * from test_kl where name ='' ;

結果如下：

結果分析：因爲我沒有設置服務端的字符集，因此默認的字符集使用的是 latin1，對於 latin1 一個字符代表一個字節，因此這列的 key_len 的長度是 20，表示使用了 name 這個索引。

② 給 test_kl 表，新增 name1 列，該列沒有設置 “not null”

結果如下：

結果分析：如果索引字段可以爲 null，則 mysql 底層會使用 1 個字節用於標識。

③ 刪除原來的索引 name 和 name1，新增一個複合索引

# 刪除原來的索引name和name1
drop index index_name on test_kl ;
drop index index_name1 on test_kl ;
# 增加一個複合索引
create index name_name1_index on test_kl(name,name1);
# 查看執行計劃
explain select * from test_kl where name1 = '' ; --121
explain select * from test_kl where name = '' ; --60

結果如下：

結果分析： 對於下面這個執行計劃，可以看到我們只使用了複合索引的第一個索引字段 name，因此 key_len 是 20，這個很清楚。再看上面這個執行計劃，我們雖然僅僅在 where 後面使用了複合索引字段中的 name1 字段，但是你要使用複合索引的第 2 個索引字段，會默認使用了複合索引的第 1 個索引字段 name，由於 name1 可以是 null，因此 key_len = 20 + 20 + 1 = 41 呀！

④ 再次怎加一個 name2 字段，併爲該字段創建一個索引。

不同的是：該字段數據類型是 varchar

# 新增一個字段name2，name2可以爲null
alter table test_kl add column name2 varchar(20) ; 
# 給name2字段，設置爲索引字段
alter table test_kl add index name2_index(name2) ;
# 查看執行計劃
explain select * from test_kl where name2 = '' ;

結果如下：

結果分析： key_len = 20 + 1 + 2，這個 20 + 1 我們知道，這個 2 又代表什麼呢？原來 varchar 屬於可變長度，在 mysql 底層中，用 2 個字節標識可變長度。

6）ref

這裏的 ref 的作用，指明當前表所參照的字段。

注意與 type 中的 ref 值區分。在 type 中，ref 只是 type 類型的一種選項值。

# 給course表的tid字段，添加一個索引
create index tid_index on course(tid);
# 查看執行計劃
explain select * from course c,teacher t 
where c.tid = t.tid  
and t.tname = 'tw';

結果如下：

結果分析： 有兩個索引，c 表的 c.tid 引用的是 t 表的 tid 字段，因此可以看到顯示結果爲【數據庫名. t.tid】，t 表的 t.name 引用的是一個常量 "tw"，因此可以看到結果顯示爲 const，表示一個常量。

7）rows(這個目前還是有點疑惑)

被索引優化查詢的數據個數 (實際通過索引而查詢到的數據個數)

explain select *
from course c,teacher t  
where c.tid = t.tid
and t.tname = 'tz' ;

結果如下：

8）extra

表示其他的一些說明，也很有用。

① using filesort：針對單索引的情況

當出現了這個詞，表示你當前的 SQL 性能消耗較大。表示進行了一次 “額外” 的排序。常見於 order by 語句中。

Ⅰ 什麼是 “額外” 的排序？

爲了講清楚這個，我們首先要知道什麼是排序。我們爲了給某一個字段進行排序的時候，首先你得先查詢到這個字段，然後在將這個字段進行排序。

緊接着，我們查看如下兩個 SQL 語句的執行計劃。

# 新建一張表，建表同時創建索引
create table test02
(   
  a1 char(3),    
  a2 char(3),   
  a3 char(3),   
  index idx_a1(a1),  
  index idx_a2(a2),   
  index idx_a3(a3)
);
# 查看執行計劃
explain select * from test02 where a1 ='' order by a1 ;
explain select * from test02 where a1 ='' order by a2 ;

結果如下：

結果分析： 對於第一個執行計劃，where 後面我們先查詢了 a1 字段，然後再利用 a1 做了依次排序，這個很輕鬆。但是對於第二個執行計劃，where 後面我們查詢了 a1 字段，然而利用的卻是 a2 字段進行排序，此時 myql 底層會進行一次查詢，進行 “額外” 的排序。

總結：對於單索引，如果排序和查找是同一個字段，則不會出現 using filesort；如果排序和查找不是同一個字段，則會出現 using filesort；因此 where 哪些字段，就 order by 哪些些字段。

② using filesort：針對複合索引的情況

不能跨列 (官方術語：最佳左前綴)

# 刪除test02的索引
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;
# 創建一個複合索引
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
# 查看下面SQL語句的執行計劃
explain select *from test02 where a1='' order by a3 ;  --using filesort
explain select *from test02 where a2='' order by a3 ; --using filesort
explain select *from test02 where a1='' order by a2 ;

結果如下：

結果分析： 複合索引的順序是 (a1,a2,a3)，可以看到 a1 在最左邊，因此 a1 就叫做 “最佳左前綴”，如果要使用後面的索引字段，必須先使用到這個 a1 字段。對於 explain1，where 後面我們使用 a1 字段，但是後面的排序使用了 a3，直接跳過了 a2，屬於跨列；對於 explain2，where 後面我們使用了 a2 字段，直接跳過了 a1 字段，也屬於跨列；對於 explain3，where 後面我們使用 a1 字段，後面使用的是 a2 字段，因此沒有出現【using filesort】。

③ using temporary

當出現了這個詞，也表示你當前的 SQL 性能消耗較大。這是由於當前 SQL 用到了臨時表。一般出現在 group by 中。

explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary

結果如下：

結果分析： 當你查詢哪個字段，就按照那個字段分組，否則就會出現 using temporary。

針對 using temporary，我們在看一個例子：

using temporary 表示需要額外再使用一張表，一般出現在 group by 語句中。雖然已經有表了，但是不適用，必須再來一張表。

再次來看 mysql 的編寫過程和解析過程。

Ⅰ 編寫過程

select dinstinct  ..from  ..join ..on ..where ..group by ..having ..order by ..limit ..

Ⅱ 解析過程

from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..

很顯然，where 後是 group by，然後纔是 select。基於此，我們再查看如下兩個 SQL 語句的執行計劃。

explain select * from test03 where a2=2 and a4=4 group by a2,a4;
explain select * from test03 where a2=2 and a4=4 group by a3;

分析如下： 對於第一個執行計劃，where 後面是 a2 和 a4，接着我們按照 a2 和 a4 分組，很明顯這兩張表已經有了，直接在 a2 和 a4 上分組就行了。但是對於第二個執行計劃，where 後面是 a2 和 a4，接着我們卻按照 a3 分組，很明顯我們沒有 a3 這張表，因此有需要再來一張臨時表 a3。因此就會出現 using temporary。

④ using index

當你看到這個關鍵詞，恭喜你，表示你的 SQL 性能提升了。

using index 稱之爲 “索引覆蓋”。

當出現了 using index，就表示不用讀取源表，而只利用索引獲取數據，不需要回源表查詢。

只要使用到的列，全部出現在索引中，就是索引覆蓋。

# 刪除test02中的複合索引idx_a1_a2_a3
drop index idx_a1_a2_a3 on test02;
# 重新創建一個複合索引
idx_a1_a2create index idx_a1_a2 on test02(a1,a2);
# 查看執行計劃
explain select a1,a3 from test02 where a1='' or a3= '' ;
explain select a1,a2 from test02 where a1='' and a2= '' ;

結果如下：

結果分析： 我們創建的是 a1 和 a2 的複合索引，對於第一個執行計劃，我們卻出現了 a3，該字段並沒有創建索引，因此沒有出現 using index，而是 using where，表示我們需要回表查詢。對於第二個執行計劃，屬於完全的索引覆蓋，因此出現了 using index。

針對 using index，我們在查看一個案例：

explain select a1,a2 from test02 where a1='' or a2= '' ;
explain select a1,a2 from test02;

結果如下：

如果用到了索引覆蓋 (using index 時)，會對 possible_keys 和 key 造成影響：

a. 如果沒有 where，則索引只出現在 key 中；

b. 如果有 where，則索引 出現在 key 和 possible_keys 中。

⑤ using where

表示需要【回表查詢】，表示既在索引中進行了查詢，又回到了源表進行了查詢。

# 刪除test02中的複合索引idx_a1_a2
drop index idx_a1_a2 on test02;
# 將a1字段，新增爲一個索引
create index a1_index on test02(a1);
# 查看執行計劃
explain select a1,a3 from test02 where a1="" and a3="" ;

結果如下：

結果分析： 我們既使用了索引 a1，表示我們使用了索引進行查詢。但是又對於 a3 字段，我們並沒有使用索引，因此對於 a3 字段，需要回源表查詢，這個時候出現了 using where。

⑥ impossible where(瞭解)

當 where 子句永遠爲 False 的時候，會出現 impossible where

# 查看執行計劃
explain select a1 from test02 where a1="a" and a1="b" ;

結果如下：

6、優化示例

1）引入案例

# 創建新表
create table test03
(  
  a1 int(4) not null,   
  a2 int(4) not null,    
  a3 int(4) not null,   
  a4 int(4) not null
);
# 創建一個複合索引
create index a1_a2_a3_test03 on test03(a1,a2,a3);
# 查看執行計劃
explain select a3 from test03 where a1=1 and a2=2 and a3=3;

結果如下：

推薦寫法： 複合索引順序和使用順序一致。

下面看看【不推薦寫法】：複合索引順序和使用順序不一致。

# 查看執行計劃
explain select a3 from test03 where a3=1 and a2=2 and a1=3;

結果如下：

結果分析： 雖然結果和上述結果一致，但是不推薦這樣寫。但是這樣寫怎麼又沒有問題呢？這是由於 SQL 優化器的功勞，它幫我們調整了順序。

最後再補充一點：對於複合索引，不要跨列使用

# 查看執行計劃
explain select a3 from test03 where a1=1 and a3=2 group by a3;

結果如下：

結果分析： a1_a2_a3 是一個複合索引，我們使用 a1 索引後，直接跨列使用了 a3，直接跳過索引 a2，因此索引 a3 失效了，當使用 a3 進行分組的時候，就會出現 using where。

2）單表優化

# 創建新表
create table book
(    
    bid int(4) primary key,      
    name varchar(20) not null,     
    authorid int(4) not null,     
    publicid int(4) not null,    
    typeid int(4) not null 
);
# 插入數據
insert into book values(1,'tjava',1,1,2) ;
insert into book values(2,'tc',2,1,2) ;
insert into book values(3,'wx',3,2,1) ;
insert into book values(4,'math',4,2,3) ;

結果如下：

案例：查詢 authorid=1 且 typeid 爲 2 或 3 的 bid，並根據 typeid 降序排列。

explain 
select bid from book 
where typeid in(2,3) and authorid=1 
order by typeid desc ;

結果如下：

這是沒有進行任何優化的 SQL，可以看到 typ 爲 ALL 類型，extra 爲 using filesort，可以想象這個 SQL 有多恐怖。

優化：添加索引的時候，要根據 MySQL 解析順序添加索引，又回到了 MySQL 的解析順序，下面我們再來看看 MySQL 的解析順序。

from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..

① 優化 1：基於此，我們進行索引的添加，並再次查看執行計劃。

# 添加索引
create index typeid_authorid_bid on book(typeid,authorid,bid);
# 再次查看執行計劃
explain 
select bid from book 
where typeid in(2,3) and authorid=1  
order by typeid desc ;

結果如下：

結果分析： 結果並不是和我們想象的一樣，還是出現了 using where，查看索引長度 key_len=8，表示我們只使用了 2 個索引，有一個索引失效了。

② 優化 2：使用了 in 有時候會導致索引失效，基於此有了如下一種優化思路。

將 in 字段放在最後面。需要注意一點：每次創建新的索引的時候，最好是刪除以前的廢棄索引，否則有時候會產生干擾 (索引之間)。

# 刪除以前的索引
drop index typeid_authorid_bid on book;
# 再次創建索引
create index authorid_typeid_bid on book(authorid,typeid,bid);
# 再次查看執行計劃
explain 
select bid from book 
where authorid=1  and typeid in(2,3) 
order by typeid desc ;

結果如下：

結果分析： 這裏雖然沒有變化，但是這是一種優化思路。

總結如下：

a. 最佳做前綴，保持索引的定義和使用的順序一致性

b. 索引需要逐步優化 (每次創建新索引，根據情況需要刪除以前的廢棄索引)

c. 將含 In 的範圍查詢，放到 where 條件的最後，防止失效。

本例中同時出現了 Using where（需要回原表）; Using index（不需要回原表）：原因，where authorid=1 and typeid in(2,3)中 authorid 在索引 (authorid,typeid,bid) 中，因此不需要回原表（直接在索引表中能查到）；而 typeid 雖然也在索引 (authorid,typeid,bid) 中，但是含 in 的範圍查詢已經使該 typeid 索引失效，因此相當於沒有 typeid 這個索引，所以需要回原表（using where）；

例如以下沒有了 In，則不會出現 using where：

explain select bid from book 
where  authorid=1 and typeid =3
order by typeid desc ;

結果如下：

3）兩表優化

# 創建teacher2新表
create table teacher2
(      
    tid int(4) primary key,     
    cid int(4) not null
);
# 插入數據
insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);
# 創建course2新表
create table course2
(  
    cid int(4) ,  
    cname varchar(20)
);
# 插入數據
insert into course2 values(1,'java');
insert into course2 values(2,'python');
insert into course2 values(3,'kotlin');

案例：使用一個左連接，查找教 java 課程的所有信息。

explain 
select *
from teacher2 t
left outer join course2 c
on t.cid=c.cid 
where c.cname='java';

結果如下：

① 優化

對於兩張表，索引往哪裏加？答：對於表連接，小表驅動大表。索引建立在經常使用的字段上。

爲什麼小表驅動大表好一些呢？

    小表:10   
    大表:300
# 小表驅動大表
select ...where 小表.x10=大表.x300 ;
for(int i=0;i<小表.length10;i++)
{   
    for(int j=0;j<大表.length300;j++)  
    {       
        ...   
    }
}
# 大表驅動小表
select ...where 大表.x300=小表.x10 ;
for(int i=0;i<大表.length300;i++)
{  
    for(int j=0;j<小表.length10;j++)   
    {     
        ...   
    }
}

分析： 以上 2 個 FOR 循環，最終都會循環 3000 次；但是對於雙層循環來說：一般建議，將數據小的循環，放外層。數據大的循環，放內層。不用管這是爲什麼，這是編程語言的一個原則，對於雙重循環，外層循環少，內存循環大，程序的性能越高。

結論：當編寫【…on t.cid=c.cid】時，將數據量小的表放左邊（假設此時 t 表數據量小，c 表數據量大。）

我們已經知道了，對於兩表連接，需要利用小表驅動大表，例如【…on t.cid=c.cid】，t 如果是小表 (10 條)，c 如果是大表 (300 條)，那麼 t 每循環 1 次，就需要循環 300 次，即 t 表的 t.cid 字段屬於，經常使用的字段，因此需要給 cid 字段添加索引。

更深入的說明： 一般情況下，左連接給左表加索引。右連接給右表加索引。其他表需不需要加索引，我們逐步嘗試。

# 給左表的字段加索引
create index cid_teacher2 on teacher2(cid);
# 查看執行計劃
explain 
select *
from teacher2 t 
left outer join course2 c
on t.cid=c.cid
where c.cname='java';

結果如下：

當然你可以下去接着優化，給 cname 添加一個索引。索引優化是一個逐步的過程，需要一點點嘗試。

# 給cname的字段加索引
create index cname_course2 on course2(cname);
# 查看執行計劃
explain
select t.cid,c.cname
from teacher2 t
left outer join course2 c
on t.cid=c.cid 
where c.cname='java';

結果如下：

最後補充一個：Using join buffer 是 extra 中的一個選項，表示 Mysql 引擎使用了 “連接緩存”，即 MySQL 底層動了你的 SQL，你寫的太差了。

4）三表優化

• 大於等於張表，優化原則一樣

• 小表驅動大表

• 索引建立在經常查詢的字段上

7、避免索引失效的一些原則

① 複合索引需要注意的點

• 複合索引，不要跨列或無序使用 (最佳左前綴)

• 複合索引，儘量使用全索引匹配，也就是說，你建立幾個索引，就使用幾個索引

② 不要在索引上進行任何操作 (計算、函數、類型轉換)，否則索引失效

explain select * from book where authorid = 1 and typeid = 2;
explain select * from book where authorid*2 = 1 and typeid = 2 ;

結果如下：

③ 索引不能使用不等於（!= <>）或 is null (is not null)，否則自身以及右側所有全部失效 (針對大多數情況)。複合索引中如果有 >，則自身和右側索引全部失效。

# 針對不是複合索引的情況
explain select * from book where authorid != 1 and typeid =2 ;
explain select * from book where authorid != 1 and typeid !=2 ;

結果如下：

再觀看下面這個案例：

# 刪除單獨的索引
drop index authorid_index on book;
drop index typeid_index on book;
# 創建一個複合索引
alter table book add index idx_book_at (authorid,typeid);
# 查看執行計劃
explain select * from book where authorid > 1 and typeid = 2 ;
explain select * from book where authorid = 1 and typeid > 2 ;

結果如下：

結論：複合索引中如果有【>】，則自身和右側索引全部失效。

在看看複合索引中有【<】的情況：

我們學習索引優化 ，是一個大部分情況適用的結論，但由於 SQL 優化器等原因 該結論不是 100% 正確。一般而言， 範圍查詢（> < in），之後的索引失效。

④ SQL 優化，是一種概率層面的優化。至於是否實際使用了我們的優化，需要通過 explain 進行推測。

# 刪除複合索引
drop index authorid_typeid_bid on book;
# 爲authorid和typeid，分別創建索引
create index authorid_index on book(authorid);
create index typeid_index on book(typeid);
# 查看執行計劃
explain select * from book where authorid = 1 and typeid =2 ;

結果如下：

結果分析： 我們創建了兩個索引，但是實際上只使用了一個索引。因爲對於兩個單獨的索引，程序覺得只用一個索引就夠了，不需要使用兩個。

當我們創建一個複合索引，再次執行上面的 SQL：

# 查看執行計劃
explain select * from book where authorid = 1 and typeid =2 ;

結果如下：

⑤ 索引覆蓋，百分之百沒問題

⑥ like 儘量以 “常量” 開頭，不要以’%'開頭，否則索引失效

explain select * from teacher where tname like "%x%" ;
explain select * from teacher  where tname like 'x%';
explain select tname from teacher  where tname like '%x%';

結果如下：

結論如下： like 儘量不要使用類似 "%x%" 情況，但是可以使用 "x%" 情況。如果非使用 "%x%" 情況，需要使用索引覆蓋。

⑦ 儘量不要使用類型轉換（顯示、隱式），否則索引失效

explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;

結果如下：

⑧ 儘量不要使用 or，否則索引失效

explain select * from teacher where tname ='' and tcid >1 ;
explain select * from teacher where tname ='' or tcid >1 ;

結果如下：

注意：or 很猛，會讓自身索引和左右兩側的索引都失效。

8、一些其他的優化方法

1）exists 和 in 的優化

如果主查詢的數據集大，則使用 i 關鍵字，效率高。

如果子查詢的數據集大，則使用 exist 關鍵字, 效率高。

select ..from table where exist (子查詢) ;
select ..from table where 字段 in  (子查詢) ;

2）order by 優化

• IO 就是訪問硬盤文件的次數

• using filesort 有兩種算法：雙路排序、單路排序（根據 IO 的次數）

• MySQL4.1 之前默認使用雙路排序；雙路：掃描 2 次磁盤 (1：從磁盤讀取排序字段, 對排序字段進行排序 (在 buffer 中進行的排序)2：掃描其他字段)

• MySQL4.1 之後默認使用單路排序：只讀取一次（全部字段），在 buffer 中進行排序。但種單路排序會有一定的隱患 (不一定真的是 “單路 / 1 次 IO”，有可能多次 IO)。原因：如果數據量特別大，則無法將所有字段的數據一次性讀取完畢，因此會進行 “分片讀取、多次讀取”。

• 注意：單路排序 比雙路排序 會佔用更多的 buffer。

• 單路排序在使用時，如果數據大，可以考慮調大 buffer 的容量大小：

# 不一定真的是“單路/1次IO”，有可能多次IO
set max_length_for_sort_data = 1024

如果 max_length_for_sort_data 值太低，則 mysql 會自動從 單路 -> 雙路 (太低：需要排序的列的總大小超過了 max_length_for_sort_data 定義的字節數)

① 提高 order by 查詢的策略：

• 選擇使用單路、雙路 ；調整 buffer 的容量大小

• 避免使用 select * …（select 後面寫所有字段，也比寫 * 效率高）

• 複合索引，不要跨列使用 ，避免 using filesort 保證全部的排序字段，排序的一致性（都是升序或降序）

來源：撿田螺的小男孩

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/FDuj7eU-HneDi2e8onZdyA