3天豪肝5萬字！阿里P7大佬手寫MySQL超全筆記原文開放，收藏一波

作者：由牛哄哄的java大師發表于數碼日期：2021-09-08

下面對於MySQL進行相關介紹，文件的內容較為基礎，針對剛開始學習的同學，僅僅涉及操作，少量原理，大佬請繞道哦。因字數太多，分為兩篇發放（第二篇）。

2）覆蓋索引（Covering Index）

一說為索引覆蓋。

類比鍋和鍋蓋

，查詢的欄位要小於等於建立的索引（

注意：主鍵id天生自帶索引，故而主鍵欄位對與覆蓋索引無影響

）。

理解方式一：就是select的資料列只用從索引中就能夠取得，不必讀取資料行，

MySQL可以利用索引返回select列表中的欄位，而不必根據索引再次讀取資料檔案

，換句話說

查詢列要被所建的索引覆蓋

。

理解方式二：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的資料，因此它不必讀取整個行。畢竟索引葉子節點儲存了它們索引的資料；當能透過讀取索引就可以得到想要的資料，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結果的資料就叫做覆蓋索引。

注意：

如果要使用覆蓋索引，一定要注意select列表中只取出需要的列，不可select*，因為如果將所有欄位一起做索引會導致索引檔案過大，查詢效能下降。

小結

解決like ‘%字串%’時索引不被使用的方法？複合索引，然後覆蓋索引。

⑧數目字串不加單引號

數目字串不加單引號索引失效

。 varchar型別一定要加單引號！！！

MySQL底層傳送了隱式的型別轉換，影響效率。

⑨用關鍵字OR

少用or，用它來連線時會索引失效

。

⑩小總結where之後order by之前

小總結

假設建立的索引：index（a，b，c）

Where 語句

索引是否被使用

where a = 3

Y，使用到 a

where a = 3 and b = 5

Y，使用到 a，b

where a = 3 and b = 5 and c = 4

Y，使用到 a，b，c

where b = 3 或者 where b = 3 and c = 4 或者 where c = 4

N （

沒有大哥a

）

where a = 3 and c = 5

使用到 a，但是 c 不可以，b 中間斷了

where a = 3 and b > 4 and c = 5

使用到 a 和 b， c 不能用在範圍之後，b 斷了

where a is null and b is not null

is null 支援索引但是 is not null 不支援，所以 a 可以使用索引，但是 b 不可以使用

where a <> 3

不能使用索引

where abs（a） =3

不能使用索引

where a = 3 and b like ‘kk%’ and c = 4

Y，使用到 a，b，c

where a = 3 and b like ‘%kk’ and c = 4

Y，只用到 a

where a = 3 and b like ‘%kk%’ and c = 4

Y，只用到 a

where a = 3 and b like ‘k%kk%’ and c = 4

Y，使用到 a，b，c

最佳化總結口訣

全值匹配我最愛，最左字首要遵守；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計算，範圍之後全失效；

LIKE 百分寫最右，覆蓋索引不寫 *；

不等空值還有 OR，索引影響要注意；

VAR 引號不可丟， SQL 最佳化有訣竅。

8-索引面試題分析

問題：我們建立了複合索引idx_test03_c1234 （c1、c2、c3、c4），根據以下SQL分析下索引使用情況？

①案例1

為什麼我建的是1、2、3、4，但是我查詢的是4、3、2、1，依舊可以使用到索引呢？MySQL中的第二層中的Optimizer會把MySQL中的命令自動的進行調整和最佳化。。

②案例2

限定範圍

③案例3

c3作用在排序而不是查詢

上面兩個explain的相同。

上述兩條說明：和c4=‘a4’這個沒啥關係

直接在三樓（c3）排序就行，不找了。

order by c3換成order by c4

用到Using filesort。

中間的兄弟斷了，但是MySQL要交出結果，只好整一次內排序 Using filesort

④ order by的欄位順序與索引欄位的順序

只用c1一個欄位索引，但是c2、c3用於排序，無filesort。

將order by c2，c3換成order by c3，c2。

出現了filesort，

我們建的索引是1234，它沒有按照順序來，3，2顛倒了。

⑤order by中的特例

mysql> explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c2，c3；mysql> explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c2，c3；

用c1、c2兩個欄位索引，但是c2、c3用於排序，無filesort

和c5=‘a5’沒啥關係

上述第二條SQL的order by違反了建立索引的順序，那麼為什麼沒有產生using filesort呢？

原因是：

一般情況下：只要order by欄位順序與建立索引的順序不一致就會產生using filesort；

特例是：

排序的欄位已經是一個常量了，上述情況就是 c3和c2=‘a2’兩個欄位排序。

對比說明：

跳過c2，就用c3，就出現Using filesort。

⑥group by

group by表面上是分組，但是分組之前必排序，所以group by最佳化原則幾乎和order by相同

定值、範圍還是排序，一般order by是給個範圍

group by基本上都需要進行排序，會有臨時表產生

（建的索引搞不定，MySQL自己內部再次排序）

一般性建議

對於單鍵索引，儘量選擇針對當前query過濾性更好的索引。

在選擇組合索引的時候，當前Query中過濾性最好的欄位在索引欄位順序中，位置越靠

左

越好。

在選擇組合索引的時候，儘量選擇可以能夠包含當前query中的where字句中更多欄位的索引。

儘可能透過分析統計資訊和調整query的寫法來達到選擇合適索引的目的。

9-索引最佳化答疑補充和總結口訣

假設index（a， b， c）

Y，使用到a，b，c

#百分號在右邊，相當於說左邊有常量了，實際上用到了c2索引mysql> explain select * from test03 where c1=‘a1’ and c2 like ‘kk%’ and c3=‘a3’；

Y，使用到a

#根據百分號在左邊 c2直接用不了，導致中間兄弟斷了，所以c3也用不了mysql> explain select * from test03 where c1=‘a1’ and c2 like ‘%kk’ and c3=‘a3’；

Y，使用到a

Y，使用到a，b，c

三、查詢擷取分析

1-查詢最佳化

①小表驅動大表

通常SQL調優過程：

（面試問：平時是咋樣進行SQL最佳化的？）

觀察，至少跑1天，看看生產的慢SQL情況。

開啟慢查詢日誌，設定闕值，比如超過5秒鐘的就是慢SQL，並將它抓取出來。

（找出慢SQL）

explain + 慢SQL分析。

show profile。

運維經理 or DBA，進行SQL資料庫伺服器的引數調優。

總結：

慢查詢的開啟並捕獲

explain + 慢SQL分析

show profile查詢SQL在Mysql伺服器裡面的執行細節和生命週期情況

SQL資料庫伺服器的引數調優。

最佳化原則：小表驅動大表，即小的資料集驅動大的資料集。

類似上述兩個for迴圈，儘量選擇上面的那種。

資料庫最耗時的是多表之間關係的建立

。第一種：相當於兩張表建立了5次連線；第二種：相當於兩張表建立了1000次連線。

也就是說被巢狀在裡面的應該是小表！（因為是裡面巢狀的先執行，相當於被巢狀在裡面的SQL是外層for迴圈）

RBO原理

當B表的資料集小於A表的資料集時，用in優於exists。

當B表的資料集大於A表的資料集時，用exists優於in。

注意：A表與B表的ID欄位應建立索引。

EXISTS關鍵字

該語法可以理解為：

先將主查詢的資料，放到子查詢中做條件驗證，根據驗證結果（TRUE或FALSE）來決定主查詢的資料結果是否得以保留。

提示

EXSTS（subquey）只返回TRUE或FALSE

，因此子查詢中的SELECT * 也可以是 SELECT 1 或select ‘X’，

官方說法是實際執行時會忽略SELECT清單，因此沒有區別

。

EXISTS子查詢的實際執行過程可能經過了最佳化而不是我們理解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以確定是否有效率問題。

EXISTS子查詢往往也可以用條件表示式，其他子查詢或者JOIN來替代，何種最優需要具體問題具體分析

②in和exists

示例表：

in和exists用法

③為排序使用索引OrderBy最佳化

ORDER BY子句，儘量使用Index方式排序，避免使用FileSort方式排序。

新建SQL

檢視驗證：

分析SQL

重點在於會不會產生UsingFileSort

：

最後一種情況：儘管order by的順序和建立索引的順序保持一致，但是由於order by預設的是升序，而DESC是降序

，MySQL建立索引的順序預設的就是升序，現在需要降序，用不上了，那麼MySQL只有產生一次內部排序了。

MySQL支援二種方式的排序，FileSort和lIndex，

Index效率高

，它指MySQL掃描索引本身完成排序。

FileSort方式效率較低

。

結論：

ORDER BY滿足兩情況，會使用Index方式排序：

ORDER BY語句使用索引最左前列。

使用where子句與Order BY子句條件列組合滿足索引最左前列。

④MySQL的Filesort

如果Order by查詢的列不在索引列上，MySQL的Filesort有兩種演算法：

1）雙路排序

MySQL4。1之前是使用雙路排序，字面意思就是

兩次掃描磁碟

，最終得到資料，讀取行指標和OrderBy列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀對應的資料輸出。

從磁碟取排序欄位，在buffer進行排序，再從磁碟取其他欄位。

取一批資料，要對磁碟進行了兩次掃描，眾所周知，

I\O是很耗時的

，所以在mysql4。1之後，出現了第二種改進的演算法，就是單路排序。

2）單路排序

從磁碟讀取查詢需要的所有列，

按照order by列在buffer對它們進行排序

，然後掃描排序壓的列表進行輸出，它的效率更快一些，避免了第二次讀取資料。並且把隨機IO變成了順序IO，但是它會使用更多的空間，因為它把每一行都儲存在記憶體中了。

結論及引申出的問題

由於單路是後出的，總體而言好過雙路

但是用單路有問題

在sort_buffer（

在my。cnf中有配置緩衝區大小

）中，方法B比方法A要多佔用很多空間，

因為方法B是把所有欄位都取出

，所以有可能取出的資料的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的資料，進行排序（建立tmp檔案，多路合併），排完再取取

sort_buffer容量大小，再排……從而多次I/O。（

類比於緩衝區大小不夠，那只有取多次了

）

本來想省一次I/O操作，反而導致了大量的I/O操作，反而得不償失。

⑤最佳化策略

增大sort_buffer_size引數的設定

增大max_length_for_sort_data引數的設定

為什麼設定sort_buffer_size、max_length_for_sort_data引數能最佳化排序？

提高Order By的速度：

Order by時select * 是一個大忌只Query需要的欄位，這點非常重要（

很容易把緩衝區佔滿

）。在這裡的影響是；當Query的欄位大小總和小於max_length_for_sort_data而且排序欄位不是TEXT|BLOB型別時，會用改進後的演算法——單路排序，否則用老演算法——多路排序。兩種演算法的資料都有可能超出sort_buffer的容量，

超出之後，會建立臨時tmp檔案進行合併排序

，導致多次IO，但是用單路排序演算法的風險會更大一些，所以要提高sort_buffer__size。

嘗試提高sort_buffer_size，不管用哪種演算法，提高這個引數都會提高效率，當然，要根據系統的能力去提高，因為這個引數是針對每個程序的。

嘗試提高max_length_for_sort_data，提高這個引數，會增加用改進演算法的機率。但是如果設的太高，資料總容量超出sort_buffer_size的機率就增大，明顯症狀是高的磁碟I/O活動和低的處理器使用率。

⑥索引和Order by小結

為排序使用索引

MySql兩種排序方式∶檔案排序或掃描有序索引排序

MySql能為排序與查詢使用相同的索引

建立複合索引 a_b_c （a， b， c）

order by能使用索引最左字首

ORDER BY a

ORDER BY a， b

ORDER BY a， b， c

ORDER BY a DESC， b DESC， c DESC （

都是升序或都是降序

）

如果WHERE使用素引的最左字首定義為常量，則order by能使用索引（

不產生FileSort

）

WHERE a = const ORDER BY b，c

WHERE a = const AND b = const ORDER BY c

WHERE a = const ORDER BY b， c

WHERE a = const AND b > const ORDER BY b， c

這裡雖然是>但是依舊是一個常量

不能使用索引進行排序

ORDER BY a ASC， b DESC， c DESC //排序不—致

WHERE g = const ORDER BY b， c //產丟失a索引

WHERE a = const ORDER BY c //

中間兄弟斷了

產丟失b索引

WHERE a = const ORDER BY a， d //d不是素引的一部分

WHERE a in （…） ORDER BY b， c //對於排序來說，多個相等條件也是範圍查詢不是常量

2-GroupBy最佳化

GroupBy最佳化

group by

實質是先排序後進行分組

，遵照索引建的最佳左字首。

當無法使用索引列，增大max_length_for_sort_data引數的設定 + 增大sort_buffer_size引數的設定。

where高於having，能寫在where限定的條件就不要去having限定了。

3-慢查詢日誌

MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。

具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是執行10秒以上的語句。

由他來檢視哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，

希望能收集超過5秒的sql，結合之前explain進行全面分析。

①如何操作

預設情況下，MySQL資料庫沒有開啟慢查詢日速，

需要我們手動來設定這個引數

。

當然，

如果不是調優需要的話，一般不建議啟動該引數

，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案。

1）檢視是否開啟及如何開啟

預設 - SHOW VARIABLES LIKE ‘%slow_query_log%’；

一次性開啟 - set global slow_query_log=1；，只對當前資料庫生效，

如果MySQL重啟後則會失效

。只針對本次生效。

如果要永久生效，就必須修改配置檔案my。cnf（其它系統變數也是如此）

修改my。cnf檔案，［mysqld］下增加或修改引數slow_query_log和slow_query_log_file後，然後重啟MySQL伺服器。也即將如下兩行配置進my。cnf檔案

關於慢查詢的引數slow_query_log_file，它指定慢查詢日誌檔案的存放路徑，系統預設會給一個預設的檔案host_name-slow。log（如果沒有指定引數slow_query_log_file的話）

2）開啟了慢查詢日誌

什麼樣的SQL才會記錄到慢查詢日誌裡面呢？

這個是由引數long_query_time控制，

預設情況下long_query_time的值為10秒

，命令：SHOW VARIABLES LIKE ‘long_query_time%’；

可以使用命令修改，也可以在my。cnf引數裡面修改。

假如執行時間

正好等於long_query_time的情況，並不會被記錄下來

。也就是說，在mysql原始碼裡是判斷大於long_query_time，而非大於等於。

3）設定慢SQL閾值時間：

set global long_query_time=3；

為什麼設定後看不出變化？

需要重新連線或新開一個會話才能看到修改值。

4）記錄慢SQL並後續分析

假設我們成功設定慢SQL閾值時間為3秒（set global long_query_time=3；）。

模擬超時SQL：

日誌記錄：

查詢當前系統中有多少條慢查詢記錄

在配置檔案中設定慢SQL閾值時間

永久生效！

②日誌分析工具mysqldumpslow

在生產環境中，如果要手工分析日誌，查詢、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具mysqldumpslow。

檢視mysqldumpslow的幫助資訊，mysqldumpslow ——help。

s：是表示按照何種方式排序

c：訪問次數

l：鎖定時間

r：返回記錄

t：查詢時間

al：平均鎖定時間

ar：平均返回記錄數

at：平均查詢時間

t：即為返回前面多少條的資料

g：後邊搭配一個正則匹配模式，大小寫不敏感的

工作常用參考

得到返回記錄集最多的10個SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow。log

得到訪問次數最多的10個SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow。log

得到按照時間排序的前10條裡面含有左連線的查詢語句

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow。log

另外建議在使用這些命令時結合│和more 使用，否則有可能出現爆屏情況（

也可以結合管道把結果寫到一個檔案裡面

）mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow。log | more

3-批次插入資料指令碼（儲存過程）

①建立SQL

②設定引數log_bin_trust_function_creators

建立函式，假如報錯：This function has none of DETERMINISTIC…

由於開啟過慢查詢日誌，因為我們開啟了bin-log，我們就必須為我們的function指定一個引數。

這樣添加了引數以後，如果mysqld重啟，上述引數又會消失，

永久方法：

windows下my。ini［mysqld］加上log_bin_trust_function_creators=1

linux下/etc/my。cnf 下my。cnf［mysqld］加上log_bin_trust_function_creators=1

③建立函式，保證每條資料都不同

1）隨機產生字串

2）隨機產生部門編號

執行結果

mysql> delimiter $$ # 宣告兩個 $$ 表示結束

mysql> create function rand_string（n int） returns varchar（255）

-> begin

-> declare chars_str varchar（100） default ‘abcdefghijklmnopqrstuvwxyz’；

-> declare return_str varchar（255） default ‘’；

-> declare i int default 0；

-> while i < n do

-> set return_str = concat（return_str，substring（chars_str，floor（1+rand（）*52），1））；

-> set i=i+1；

-> end while；

-> return return_str；

-> end $$

Query OK， 0 rows affected （0。00 sec）

mysql> select rand_string（2）；

-> ；

-> $$

+————————+

| rand_string（2） |

+————————+

| af |

+————————+

1 row in set （0。00 sec）

mysql> delimiter $$

mysql> create function rand_num（） returns int（5）

-> begin

-> declare i int default 0；

-> set i=floor（100+rand（）*10）；

-> return i；

-> end $$

Query OK， 0 rows affected （0。00 sec）

mysql> select rand_num（）$$

+——————+

| rand_num（） |

+——————+

| 105 |

+——————+

1 row in set （0。00 sec）

④建立儲存過程

建立往emp表中插入資料的儲存過程

注意：設定autocommit設定為0；

delimiter $$

create procedure insert_emp（in start int（10），in max_num int（10））#沒有返回值

begin

declare i int default 0；

set autocommit = 0；#把自動提交設定為0 ，預設是寫一次提交一次，需要批處理只提交一次

repeat

set i = i+1；

insert into emp（empno，ename，job，mgr，hiredate，sal，comm，deptno） values（（start+i），rand_string（6），‘salesman’，0001，curdate（），2000，400，rand_num（））；

until i=max_num

end repeat；

commit；#注意要commit

end $$

建立往dept表中插入資料的儲存過程

delimiter $$

create procedure insert_dept（in start int（10），in max_num int（10））

begin

declare i int default 0；

set autocommit = 0；

repeat

set i = i+1；

insert into dept（deptno，dname，loc） values（（start+i），rand_string（10），rand_string（8））；

until i=max_num

end repeat；

commit；

end $$

⑤呼叫儲存過程

往dept表中插入資料

往emp表中插入50萬資料

執行結果

mysql> select * from dept；

+——+————+————-+————+

| id | deptno | dname | loc |

+——+————+————-+————+

| 1 | 101 | mqgfy | ck |

| 2 | 102 | wgighsr | kbq |

| 3 | 103 | gjgdyj | brb |

| 4 | 104 | gzfug | p |

| 5 | 105 | keitu | cib |

| 6 | 106 | nndvuv | csue |

| 7 | 107 | cdudl | tw |

| 8 | 108 | aafyea | aqq |

| 9 | 109 | zuqezjx | dpqoyo |

| 10 | 110 | pam | cses |

+——+————+————-+————+

10 rows in set （0。00 sec）

mysql> select * from emp limit 20；

+——+————+————-+——————+——-+——————+————-+————+————+

| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |

+——+————+————-+——————+——-+——————+————-+————+————+

| 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 108 |

| 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 109 |

| 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 105 |

| 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 100 |

| 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 107 |

| 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 108 |

| 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 102 |

| 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 102 |

| 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 107 |

| 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 106 |

| 11 | 100012 | re | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 108 |

| 12 | 100013 | qip | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 105 |

| 13 | 100014 | bvaf | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 108 |

| 14 | 100015 | g | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 106 |

| 15 | 100016 | qt | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 102 |

| 16 | 100017 | bzy | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 109 |

| 17 | 100018 | gf | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 102 |

| 18 | 100019 | r | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 108 |

| 19 | 100020 | ydokg | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 100 |

| 20 | 100021 | ee | salesman | 1 | 2021-04-05 | 2000。00 | 400。00 | 107 |

+——+————+————-+——————+——-+——————+————-+————+————+

20 rows in set （0。00 sec）

4-用Show Profile進行SQL分析

Show Profile是MySQL提供可以

用來分析當前會話中語句執行的資源消耗情況

。可以用於SQL的調優的測量。（

比Explain更加詳細

）

官方文件

預設情況下，

引數處於關閉狀態，並儲存最近15次的執行結果

。

慢查詢日誌抓取有查詢較慢的SQL；

Explain

Show Profile

伺服器資料和指令碼重要屬性的調優（比如my。cnf的配置檔案）

分析步驟

1。是否支援，看看當前的mysql版本是否支援。

預設是關閉，使用前需要開啟。

2。開啟功能，預設是關閉，使用前需要開啟。

3。執行SQL

mysql> select * from emp group by id%10 limit 150000；