“資料分析師”面試最怕被問到的SQL優化問題（下）

前面我已經帶着大家學習了本文的第1-4個部分，今天就帶大家學習這剩下的5-8個部分。

《“資料分析師”面試最怕被問到的SQL優化問題（上）》

MySQL優化問題對于新手學習，一般是個難題！我的教程自認為已經是很通俗易懂的。如果你學習了這個教程後，仍然不太了解，可以去B站找到一個視訊浏覽一遍，然後再回頭看我的文章。

資料源

在上篇最後，我們已經給出了本文需要使用到的資料代碼，這裡我直接給出這3張表的圖示。

5. 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關鍵字的使用說明：查詢類型

select_type關鍵字共有如下常用的6種類型，下面我分别帶着大家梳理一下，它們各自的含義。

① simple：簡單查詢

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

explain select * from teacher;

複制

結果如下：

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

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

關于primary和subquery，我們就拿下面的這個例子進行示範。從代碼中可以看到這個SQL語句是存在子查詢的，換句話說，這個SQL語句包含子查詢。where内層(非最外層)使用到的c表屬于非最外層，是以是subquery關鍵字。where外層使用到了t表和tc表，是以是primary關鍵字。

# 檢視執行計劃
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') ;

複制

結果如下：

④ 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關鍵字的使用說明：索引類型

type關鍵字可以很好的說明，你寫的SQL語句好不好。system、const是我們達不到的理想狀況，實際上隻能優化到index --> range --> ref這個級别，也可以看到ALL是最差的一個級别。對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 ;

複制

結果如下：

可以發現：

當tid字段去掉主鍵索引，換為普通索引後，優化級别就不是const了。

③ 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”

# 新增一個字段name1，name1可以為null
alter table test_kl add column name1 char(20) ;  
# 給name1字段，設定為索引字段
alter table test_kl add index index_name1(name1) ;
# 檢視執行計劃
explain select * from test_kl where name1 ='' ;

複制

結果如下：

結果分析：

如果索引字段可以為null，則mysql底層會使用1個位元組用于辨別null。

③ 删除原來的索引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 = '' ; 
explain select * from test_kl where name = '' ;

複制

結果如下：

結果分析：

對于下面這個執行計劃，可以看到我們隻使用了複合索引的第一個索引字段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?imageView2/2/w/1620"，是以可以看到結果顯示為const，表示一個常量。

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

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

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

複制

結果如下：

8）extra

表示其他的一些說明，也非常有用，通過這個關鍵字也可以很好的說明，你寫的SQL語句到底好不好。

① 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_a2
create 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）三表優化

大于等于2張表，優化原則一樣；
小表驅動大表；
索引建立在經常查詢的字段上；

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

① 複合索引需要注意的點

a.複合索引，不要跨列或無序使用(最佳左字首)；
b.複合索引，盡量使用全索引比對，也就是說，你建立幾個索引，就使用幾個索引；

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

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%?imageView2/2/w/1620"情況。如果非使用 "%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查詢的政策：

a.選擇使用單路、雙路；調整buffer的容量大小；
b.避免使用select * ...（select後面寫所有字段，也比寫*效率高）
c.複合索引，不要跨列使用，避免using filesort
d.保證全部的排序字段，排序的一緻性（都是升序或降序）

“資料分析師”面試最怕被問到的SQL優化問題（下）

資料源

5. explain執行計劃常用關鍵字

1）id關鍵字的使用說明

① 案例：查詢課程編号為2 或 教師證編号為3 的老師資訊；

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

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

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

① simple：簡單查詢

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

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

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

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

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

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

① system

② const

③ eq_ref

④ ref

⑤ range

⑥ index

⑦ ALL

4）possible_keys和key

5）key_len

① 建立一張新表，用于測試

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

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

④ 再次怎加一個name2字段，并為該字段建立一個索引。不同的是：該字段資料類型是varchar

6）ref

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

8）extra

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

② using filesort：針對複合索引的情況(賊重要)

③ using temporary

④ using index

⑤ using where

⑥ impossible where(了解)

6、優化示例

1）引入案例

2）單表優化

① 優化1：基于此，我們進行索引的添加，并再次檢視執行計劃。

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

3）兩表優化

① 優化

4）三表優化

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

① 複合索引需要注意的點

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

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

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

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

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

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

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

8、一些其他的優化方法

1）exists和in的優化

2）order by優化

提高order by查詢的政策：

① 案例：查詢課程編号為2 或教師證編号為3 的老師資訊；