帶你學MySQL系列 | “數據分析師”面試最怕被問到的SQL優化問題（上）

SQL優化問題，一直是大家學習的難點。可能你會覺得，作為一名數據分析師，我只要知道怎么查詢即可，其實不然，恰恰SQL優化問題，是面試官最容易用來為難我們的一道關卡，今天我就帶大家講述一下這方面的知識。

由于這個篇章涉及到的知識點太多，為了您們閱讀的方便，我準備分為上、下兩篇為大家講述SQL優化知識。前方知識高能預警，大家做好準備哦。

本文大綱

下圖就是本文的大綱圖，大家先大致做一個了解。今天講述的是SQL優化問題的上篇，也就是大綱圖的第1-4個部分，剩下的5-8個部分我們在明天的下篇中為大家講述，盡情期待。

1. Mysql的基本架構

上面的client可以看成是客戶端，就是我們用來鏈接Mysql服務器，書寫SQL語句的窗口。這樣的客戶端其實有很多，像大家最常使用的CMD黑窗口，像安裝MySQL時系統自帶的WorkBench，還有大家最喜歡用的Navicat工具，它們都是一個客戶端。而右邊的這一大堆都可以看成是Server(MySQL的服務端)，我們將Server在細分為sql層和存儲引擎層。

下面我們

利用上圖來說明SQL語句的整個執行過程，

這個對于我們更深層次理解SQL，確實是很有幫助 。

首先，

需要建立客戶端與服務器之間的連接。這里通過一個【連接器】，我們建立的客戶端與服務器之間的連接，此時，你在客戶端寫的SQL語句，就可以發送到MySQL的服務了。

并不是連接上了MySQL服務器后，就立馬給我查詢出來底層的數據，這樣的話效率就太低下了。

接著，

這個SQL語句將會被交到這個【查詢緩存】中，如果可以查到，就直接響應回來給你；如果在查詢緩存中沒有查到，就需要接著往下走。

然后，

這個SQL語句將會被交到這個【分析器】中，這個分析器用于詞法分析、語法分析，檢查你寫的SQL語句有沒有單詞拼寫錯誤，語法書寫錯誤。如果都沒有錯誤，就需要接著往下走。

再接著，

這個SQL語句將會被交到這個【優化器】中，優化器如果覺得你的SQL寫的太差了，它會幫你寫一個性能高一些的等價SQL，去執行。如果優化器覺得你的SQL寫的還行，就不會動你的SQL語句。這個優化器與我們下面需要講解的“索引”有著千絲萬縷的關系。

再然后，

優化器將最終確定好的SQL方案，交給了【執行器】，執行器通過執行引擎調用“存儲引擎”。

最后，

“存儲引擎”最終調用【文件系統】，從底層去查詢出數據。

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

# 這里的engine就是指定引擎。 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;

1

2

3

4

5

6

7

2. SQL優化

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

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

1

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

1

提供一個網站，詳細說明了mysql解析過程：https://www.cnblogs.com/annsshadow/p/5037667.html

優化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，你再想想“添加索引”前后，查找次數的變化情況。

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

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

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

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

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

MySQL中索引使用的就是B+樹結構，我們現在就利用下圖來講述一下這種樹結構。

首先，

Btree一般指的都是【B+樹】，數據全部存放在葉子節點中。對于上圖來說，最下面的第3層，屬于葉子節點，真實數據部份都是存放在葉子節點當中的。那么對于第1、2層中的數據又是干嘛的呢？答：用于分割指針塊兒的，比如說小于26的找P1，介于26-30之間的找P2，大于30的找P3。

其次，

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

最后，

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

3. 索引的分類與創建

單值索引

唯一索引

復合索引

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

一張表可以創建多個“單值索引”。

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

也是利用表中的某一個字段創建單值索引，與單值索引不同的是：創建唯一索引的字段中的數據，不能有重復值。

像age肯定有很多人的年齡相同，像name肯定有些人是重名的，因此都不適合創建“唯一索引”。像編號id、學號sid，對于每個人都不一樣，因此可以用于創建唯一索引。

多個列共同構成的索引。比如說我們創建這樣一個“復合索引”(name,age)，先利用name進行索引查詢，當name相同的時候，我們利用age再進行一次篩選。

注意：復合索引的字段并不是非要都用完，當我們利用name字段索引出我們想要的結果以后，就不需要再使用age進行再次篩選了。

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

利用如下建表語句，完成創建索引的演示。

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;

1

2

3

4

5

6

查詢表結構

Ⅰ 創建單值索引

create index dept_index on tb(dept);

1

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

create unique index name_index on tb(name);

1

Ⅲ 創建復合索引

create index dept_name_index on tb(dept,name);

1

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

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

Ⅰ 創建單值索引

alter table tb add index dept_index(dept);

1

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

alter table tb add unique index name_index(name);

1

Ⅲ 創建復合索引

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

1

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

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

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

drop index name_index on tb;

1

語法：show index from 表名;

show index from tb;

1

結果如下：

4. SQL性能問題的探索

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

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

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

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

語法：explain + SQL語句

eg：explain select * from tb;

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') ;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

MySQL SQL 數據挖掘

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。