18000 字的 SQL 優化大全,收藏直接起飛!

語言: CN / TW / HK

點選進入“PHP開源社群”    

免費獲取進階面試、文件、影片資源

今天給大家分享一篇關於SQL優化的硬核文章,全文有點長,建議收藏後慢慢看。

很多朋友在做資料分析時,分析兩分鐘,跑數兩小時?

在使用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;

2、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_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" ;

結果如下:

5、優化示例

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)三表優化

大於等於張表,優化原則一樣

小表驅動大表

索引建立在經常查詢的欄位上

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

① 複合索引需要注意的點

複合索引,不要跨列或無序使用(最佳左字首)

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

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

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很猛,會讓自身索引和左右兩側的索引都失效。

7、一些其他的優化方法

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保證全部的排序欄位,排序的一致性(都是升序或降序)

篇幅很長,內容較多,建議收藏

來源:資料分析不是個事兒

END

PHP開源社群

掃描關注  進入”PHP資料“

免費獲取進階

面試、文件、影片資源