程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

一、SQL執行順序以及常見SQL的join查詢

sql執行順序：

手寫

SELECT DISTINCT
         <select_list>
FROM
          <left table> <join type>
JOIN <right_table> ON <join_codition>
WHERE
          <where_condition>
HAVING
          <having_condition>
ORDER BY
          <  order_by_condition>
LIMIT       < limit number>

機讀順序

1 FROM <left_table>

2 ON <join_condition>

3 <join_type> JOIN <right_table>

4 WHERE <where_condition>

5 GROUP BY <group by_list>

6 HAVING <having_condition>

7 SELECT

8 DISTINCT <select_list>

9 ORDER BY <order_by_condition>

10 LIMIT <limit_number>

sql機器執行順序

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

七種join關系

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

二、索引

1、什麼是索引

索引是幫助MYSQL高效擷取資料的資料結構–>排好序的快速查找資料結構

我們平時所說的索引，沒有特别指明，都是指B樹（多路搜尋樹，不一定是二叉）

結構組織的索引。其中聚集索引，次要索引，覆寫索引，複合索引，字首索引，唯一索引預設都是使用B+樹索引，統稱索引，除了B+樹這種類型的索引外，還有哈希索引等。

2、優勢：

類似大學圖書館書目索引，提高資料檢索效率，降低資料庫的io成本

通過索引列對資料進行排序，降低資料排序的成本，降低了CPU的消耗

3、劣勢：

實際上索引也是一張表，該表儲存了主鍵與索引字段，并指向實體表的記錄，是以索引列也是要占用空間的

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERTE,UPDATE,和DELETE。因為更新表時，MYSQL不僅要儲存資料，還要儲存一下索引檔案每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化後的索引資訊。（維護B+樹索引結構）

索引隻是提高效率的一個因素，如果你的MYSQL有大資料量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句

4、索引分類：

單值索引：即一個索引隻包含單個列，一個表可以有多個單列索引

唯一索引：索引列的值必須唯一，但允許有空值

複合索引：即一個索引包含多個列

5、基本文法：

建立：

CREATE [UNIQUE] INDEX indexName ON myTable(clumn name(length));

使用ALTER指令建立：

1、添加主鍵（意味着索引值必須唯一，且不能為NULL）

ALTER TABLE mytable ADD PRIMARY KEY(column_list)

2、建立唯一索引（索引值唯一，可以為NULL,為NULL可重複出現）

ALTER TABLE mytable ADD UNIQUE index_name (column_list)

3、添加普通索引（索引值可以出現多次）

ALTER TABLE mytable ADD INDEX index_name(column_list)

4、指定索引為FULLTEXT,用于全文索引

ALTER TABLE mytable ADD FULL TEXT index_name(column_list)

删除：

DROP INDEX [indexName] ON mytable;

檢視：

SHOW INDEX FROM table_name

6、索引結構原理：

BTREE索引：

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

淺藍色的塊—>磁盤塊

黃色的塊------>指針

深藍色的塊----->資料項

P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊

真實的資料庫都存在與葉子節點，非葉子節點不存儲真實的資料，隻存儲指引搜尋方向的資料項

查找過程：

如果要查找資料項29，那麼首先會把磁盤塊1由磁盤加載到記憶體，此時發生一個IO,在記憶體中用二分繼續查找确定19在17和35之間，鎖定磁盤塊1的P2指針，記憶體實踐相比磁盤IO可忽略不計。通過磁盤塊1的P2指針的磁盤位址把磁盤塊3由磁盤加載到記憶體，發生第二次IO,二分确定29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到記憶體，發生第三次IO,同時在記憶體中二分查找到29，結束，總計三次io

2、HASH索引

3、full-text全文索引

4、R-TREE索引

哪些情況需要建立索引？

1、主鍵自動建立唯一索引

2、頻繁作為查詢條件的字段應該建立索引

3、查詢中與其他表關聯的字段，外鍵關系建立索引

4、頻繁更新的字段不适合建立索引（因為每次更新都要維護索引結構）

5、where條件裡用不到的字段不建立索引

6、在高并發下傾向建立組合索引

7、查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度

8、查詢中統計或者分組字段

哪些情況下不适合建立索引

1、表記錄太少

2、經常增删改的表

3、資料重複且分布平均的表字段，是以應該隻為最經常查詢和最經常排序的資料列建立索引。（如果某個資料列包含許多重複的内容，為它建立索引就沒有太大的實際效果）

三、 sql性能分析：

1、Sql性能下降原因：

1、查詢語句寫的太爛

2、索引失效

3、關聯查詢包含太多的join

4、伺服器調優及各個參數的設定（緩沖、線程數等）

2、explain關鍵字：

定義

使用EXPLAIN關鍵字可以模拟優化器執行SQL查詢語句，進而知道MYSQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸

作用

1、能夠擷取表的讀取順序

2、能夠擷取資料讀取操作的操作類型

3、能夠擷取哪些索引可以使用

4、能夠擷取哪些索引被實際是使用

5、能夠擷取表之間的引用

6、能夠擷取每張表有多少行被優化器查詢

指令

explain sql語句;

使用explain所擷取到的執行計劃包含的資訊：

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

執行計劃資訊各字段解釋

含義：select查詢的序列号，包含一組數字，表示查詢中執行select子句或操作表的順序

作用：解釋表的讀取順序

三種情況：

1、id相同，執行順序由上至下

2、id不同，如果是子查詢，id序号會遞增，id值越大優先級越高，越先被執行

3、id有相同有不同，優先級越大的越先執行，相同的自上而下執行

select_type

含義：查詢的類型，主要用于差別普通查詢、聯合查詢、子查詢等複雜查詢

作用：解釋資料讀取操作的操作類型

類型：

SIMPLE:簡單的select查詢，查詢中不包含子查詢或者UNION
PRIMARY:查詢中包含任何複雜的子部分，最外層查詢被标記為PEIMARY(即最後加載執行的表)
SUBQUERY:在SELECT或WHERE清單中包含的子查詢
DERIVED:在FROM清單中包含的子查詢被标記為DERIVED(衍生) MYSQL會遞歸執行這些子查詢，把結果放在臨時表中
UNION:若第二個SELECT出現在UNION之後，則被标記為UNION;若UNION包含在FROM子句的子查詢中，外層SELECT将被标記為DERIVED
UNION RESULT：從UNION表擷取結果的SELECT

table

作用：标注資料來源哪張表

type

作用：顯示查詢使用了何種類型

類型性能排序：system > const > eq_ref >ref >range >index > ALL

一般情況保證查詢至少達到range級别

類型解讀：

system:表隻有一行記錄（等于系統表），這是const類型的特例，平時不會出現，這個可以忽略不計

const

表示通過索引一次就找到了，const用于比較primary key或者unique索引。因為隻比對一行資料，是以很快。如将主鍵置于where條件查詢中，MYSQL就能将該查詢轉換為一個常量

eq_ref

唯一性索引掃描，對于每個索引鍵，表中隻有一條記錄與之比對。常見于主鍵或唯一索引掃描

ref

非唯一性掃描，傳回比對某個獨有值得所有行。本質上也是一種索引通路，它傳回所有比對某個單獨值得行。

range

隻檢索給定範圍得行，使用一個索引來選擇行。key列顯示了使用了哪個索引。一般就是在你的where語句中出現了between ，<， >， in等得查詢

index

FULL INDEX SCAN ,index與ALL差別為index類型隻周遊索引樹。這通常比ALL塊，因為索引檔案通常比資料檔案小。

ALL

FULL TABLE SCAN 将周遊全表找到比對得行

possible_keys

作用：顯示可能應用在這張表中得索引，一個或多個。查詢涉及到得字段上若存在索引，則該索引将被列出。但不一定被實際查詢中用到

key

作用：表明實際查詢中用到得索引

情況：

NULL:沒有使用索引（要麼沒建索引，要麼沒用索引，要麼用了索引失效）

注意：如果使用了覆寫索引：則possible_keys為null，而key有值

覆寫索引：即查詢得字段得順序與個數與建立得複合索引一緻

key_len

含義：表示索引中使用得位元組數，可通過該列計算查詢中使用得索引得長度。在不損失精确性得情況下，長度越短越好。key_len顯示得值為索引字段得最大可能長度，并非實際使用長度。即key_len是根據表定義計算而得，不是通過表内檢索出得

ref

含義：顯示索引得哪一列被使用了，如果可能得話，是一個常數。哪些列或常量被用于查找索引列上得值

rows

含義：根據表統計資訊及索引選用大緻估算找到所需記錄所需要讀取得行數

Extra

含義：包含不适合在其他列中顯示但是又十分重要得額外資訊

取值

Using filesort:說明MySQL會對資料使用一個外部得索引排序，而不是按照表内得索引順序進行讀取。即MySQL中無法利用索引完成得排序操作稱為”檔案排序“ （絕對避免）
Using temporary：使用了臨時表儲存中間結果，Mysql在對查詢結果排序時使用臨時表。常見于排序order by 和分組查詢group by (不可以有)
USING index：表示相應得select操作中使用了覆寫索引，避免通路了表得資料行，效率不錯。如果同時出現using where表命索引被用來執行索引鍵值得查找；如果沒有出現using where 表命索引用來讀取資料而非執行查找動作。（好現象）
Using where:表明使用了where過濾
using join buffer:表明使用了連接配接緩存
impossible where : where子句得值總為false，不能擷取到任何元組
select tables optimized away
distinct：優化distinct操作，在找到第一比對得元組後即停止找同樣得動作

join語句優化總結：

盡可能減少join語句中得NestedLoop得循環總次數：“永遠用小結果驅動大的資料集”

優先優化NestedLoop得内層循環
保證join語句中被驅動表上join條件字段已經被索引
當無法保證被驅動表得join條件字段被索引且記憶體資源充足得前提下，不要吝啬joinBuffer得設定

兩表關聯查詢：

左連接配接，索引建在右表；右連接配接，索引建在左表

三表關聯查詢：

左連接配接，索引建在右邊兩張表；右連接配接，索引建在左邊兩張表

四、索引優化（解決or避免索引失效）

1.最佳左字首法則：如果索引了多列（複合索引），要遵守最左字首法則。指的是查詢從複合索引的最左列開始并且不跳過複合索引中得列（防止索引失效）

2.不要再索引列上做任何操作（計算、函數、（自動or手動）類型轉型），會導緻索引失效而轉向全表掃描

3.存儲引擎不能使用索引中範圍條件右邊的列。即複合索引中若複合索引中某個列參與了範圍條件則該列後的所有列都會索引失效

例：複合索引—>(column1,column2,column3)，若有條件查詢 where cloumn1 = value1 and cloumn2>1 and column3=values3 則column3将失效

4.盡量使用覆寫索引（隻通路索引的查詢（索引列和查詢列一緻）），避免select *

5…mysql在使用不等于（!= 或者<>）的時候會導緻索引失效而全表掃描

6.is null 或者 is not null 都會導緻索引失效

7.like以通配符開始(’%abc%’)MySQL索引會失效而導緻全表掃描。但是通配符隻在右邊出現不會導緻索引失效（即通配符不可以出現在最左邊）

解決like '%字元串…'索引失效問題：使用覆寫索引，或者查詢複合索引中部分列

8.字元串不加單引号會導緻索引失效

9.少用or，用它連接配接時會導緻索引失效

五、查詢截取分析

查詢優化

1.切記小表驅動大表

in 和 exist用法場景區分：

select * from A where A.id in (select id from B)

當B表資料集小于A表資料集時,用in 優與exist

select * from A where exist (select 1 from B where B.id = A.id)

當A表資料集小于B表資料集時，用exist優與in

2.order by 關鍵字優化

mysql排序使用index和filesort兩種方式。盡量使用using Index,避免出現using filesort

order by在滿足下列條件會使用index方式排序：

order by 語句使用索引最左前列
使用Where子句和order by子句條件列組合滿足索引最左前列

如果order by字段不在索引列上，filesort有兩種算法：

雙路排序：mysql4.1之前的算法

單路排序：mysql4.1之後出現。從磁盤讀取查詢需要的所有列，按照order by 列在buffer對它們進行排序，然後掃描排序後的清單進行輸出。

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

3.group by 關鍵字優化

group by 實質是先排序後進行分組，遵照索引鍵的最佳左字首，where高于having能寫在where限定的條件就不要使用having了

六、慢查詢日志

含義：

MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中相應實踐超過門檻值的語句，具體指運作時間超過long_query_time值得SQL，則會被記錄到慢查詢日志中

具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。long_query_time的預設值為10s

通過自定義long_query_time收集追星shi的sql語句，結合explain進行分析調優

預設情況下，MySQL資料庫沒有開啟慢查詢日志。如果不需要調優，不建議啟動慢查詢日志。

使用

檢視是否開啟以及日志路勁：SHOW VARIABLES LIKE ‘%slow_query_log%’;

開啟：set global slow_query_log =1;

檢視long_query_time：SHOW VARIABLES LIKE ‘long_query_time%’;

更改long_query_time: SET global long_query_time = 門檻值；機關秒

注意：這裡設定後不會立馬看出變化，重新連接配接就可以看到最新設定

查詢目前系統有多少條慢查詢記錄：SHOW global status like ‘%Slow_queries%’;

以上配置在資料庫重新開機将會失效，若要永久有效則在[mysqlId]下配置

slow_query_log = 1;
slow_query_log_file = log file path
long_query_time=3;
log_output=FILE

日志分析工具：mysqldumpslow

使用參數(可通過mysqldumpslow --help檢視)

s:表示按照何種方式排序

c:通路次數

l:鎖定時間

r:傳回記錄

t:查詢時間

al:平均鎖定時間

ar:平均傳回記錄數

at:平均查詢時間

t:傳回前面多少條資料

g: 正則比對

常用參考：

1.得到傳回記錄集最多的10個sql

mysqldumpslow -s r -t 10 日志檔案路徑

2.得到通路次數最多的10個sql

mysqldumpslow -s c -t 10 日志檔案路徑

3 得到按照時間排序的前10條含有左連接配接的查詢sql

mysqldumpslow -s t -t 10 -g “left join” 日志檔案路徑

由于可能資料過多，建議使用以上指令與more和管道|結合使用

七、 Show Profile

是什麼

是MySQL提供可以用來分析目前會話中語句執行的資源消耗情況，可用于SQL的調優的測量。

預設情況下，參數處于關閉狀态，并儲存最近15次的運作結果。

分析步驟

1.檢視目前MySQL版本是否支援：SHOW variables like ‘profiling’ 預設為關閉

2.開啟功能:set profiling = on;

3.運作SQL

4.檢視結果：show profiles

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

5.診斷SQL：show profile cpu,block io for query 語句query_ID

可選參數:

ALL 顯示所有開銷資訊

BLOCK IO 顯示塊IO相關開銷

CONTEXT SWITHES 上下文切換相關開銷

CPU 顯示CPU相關開銷資訊

IPU 顯示發送和接收相關開銷

MEMORY 顯示記憶體相關開銷資訊

PAGE FAULTS 顯示頁面錯誤相關開銷資訊

SOURCE 顯示和Source_function,Source_file ,Source_line相關開銷

SWAPS 顯示交換次數相關開銷資訊

常出現的危險status：

converting HEAP to MyISAM ----- 查詢結果太大，記憶體不夠用了往磁盤上加

Creating tmp table ---- 建立臨時表

Coping to tmp table on disk ---- 把記憶體中臨時表複制到磁盤

locked

八、 Mysql鎖機制

定義

鎖是計算機協調多個程序或線程并發通路某一資源的機制

在資料庫中，除傳統的計算資源（CPU，RAM,I/O等）的争用以外，資料也是一種供許多使用者共享的資源。如何保證資料兵法通路的一緻性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫兵法通路性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要也更加複雜。

鎖的分類

從對資料操作的類型上分為讀鎖和寫鎖

讀鎖（共享鎖）：針對同一份資料，多個讀操作可以同時進行而不會互相影響

寫鎖（排他鎖）：目前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。
從對資料操作的粒度分為表鎖和行鎖

1、表鎖

**特點：**偏向MyISAM存儲引擎，開銷小，加速塊；無死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。

指令：

加鎖： lock table 表名1 read/write，表名2 read/write…

檢視表加鎖情況：show open tables;有鎖時In_user = 1

釋放表鎖：unlock tables

**讀鎖：**表被某一會話加上讀鎖後，大家都可以讀資料，但是不可以寫（包括加鎖者，且加鎖者會話不可以再操作其他表），其他會話發起寫資料請求将被阻塞到表鎖被釋放。

**寫鎖：**表被某一會話加上寫鎖後，加鎖者會話可以對此表進行讀操作，但不可以再操作其他表。而其他會話對此表進行寫操作，讀操作将被阻塞至寫鎖被釋放。

**總結：**讀鎖會阻塞寫，但是不會阻塞讀；而寫鎖會阻塞讀和寫

2、行鎖

特點：偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現思索；鎖定力度最小，發生鎖沖突的機率最低，并發度也最高

注意：當索引失效或者無索引時時會導緻行鎖變為表鎖

3、間隙鎖

定義

當我們用範圍條件而不是相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對于鍵值在條件範圍内但不存在的記錄，叫做：“間隙（GAP）”

InnoDB也會對這個間隙鎖加鎖

危害：

因為Query執行過程中通過範圍查找的話，他會鎖定整個範圍内所有的索引鍵值，即使這個鍵值不存在。

間隙鎖有一個比較緻命的弱點，就是當鎖定一個範圍鍵值之後，及時某些不存在鍵也會被無辜鎖定，而造成在鎖定的時候無法插入鎖定鍵值範圍内的任何資料。在某些場景下這可能會對性能造成很大的危害

優化建議

1.盡可能讓所有資料檢索都通過索引來完成，避免無索引行鎖更新為表鎖

2.合理設計索引，盡量縮小鎖的範圍

3.盡可能減少檢索條件，避免間隙鎖

4.盡量控制事物大小，減少鎖定資源量和時間長度

5.盡可能低級别事物隔離

寫在最後：

歡迎大家關注我新開通的公衆号【風平浪靜如碼】，海量Java相關文章，學習資料都會在裡面更新，整理的資料也會放在裡面。

覺得寫的還不錯的就點個贊，加個關注呗！點關注，不迷路，持續更新！！！

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

程式員看了都要收藏系列：Mysql進階知識幹貨筆記！

一、SQL執行順序以及常見SQL的join查詢

sql執行順序：

二、索引

1、什麼是索引

2、優勢：

3、劣勢：

4、索引分類：

5、基本文法：

6、索引結構原理：

三、 sql性能分析：

1、Sql性能下降原因：

2、explain關鍵字：

執行計劃資訊各字段解釋

join語句優化總結：

四、 索引優化（解決or避免索引失效）

五、查詢截取分析

查詢優化

六、 慢查詢日志

含義：

使用

七、 Show Profile

是什麼

分析步驟

八、 Mysql鎖機制

定義

鎖的分類

1、表鎖

2、行鎖

3、間隙鎖

優化建議

寫在最後：

繼續閱讀

四、索引優化（解決or避免索引失效）

六、慢查詢日志