mysql索引與調優

前提

預設已經具備sql基本知識，能夠編寫簡單sql語句

運作流程

在z級資料中如何找到我們想要找的資料呢？

計算機是如何加載的資料的

io、如何加快io的通路（減少io次數，減少IO的量）、資料的存儲原理、資料量大了計算機是如何讀取資料

mysql以page的方式加載資料

索引介紹

什麼是Mysql索引

• MySQL官方對于索引的定義：索引是幫助MySQL高效擷取資料的資料結構。例如我們書籍的目錄、字典
• MySQL在存儲資料之外，資料庫系統中還維護着滿足特定查找算法的資料結構，這些資料結構以某種引用(指向)表中的資料，這樣我們就可以通過資料結構上實作的進階查找算法來快速找到我們想要的資料。而這種資料結構就是索引。
• 簡單了解為“排好序的可以快速查找資料的資料結構”。

索引分類

1. 主鍵索引

表中的列設定為主鍵後，資料庫會自動建立主鍵索引。

-- 建立主鍵索引文法： 
alter table 表名 add primary key (字段);
-- 删除主鍵索引文法： 
alter table 表名 drop primary key;           

1. 唯一索引

表中的列建立了唯一限制時，資料庫會自動建立唯一索引。

-- 建立唯一索引文法：
alter table 表名 add unique 索引名(字段)；
-- 删除唯一索引文法：
alter table 表名 drop index 索引名 on 表名;           

1. 單值索引

即一個索引隻包含單個列，一個表可以有多個單值索引，建表時可随表一起建立單值索引

-- 建立單值索引：
alter table 表名 add index 索引名(字段);
-- 删除單值索引：
alter table 表名 drop index 索引名 on 表名;           

1. 複合索引

即一個索引包含多個列，建表時可随表一起建立複合索引

-- 建立複合索引：
alter table 表名 add index 索引名(字段,字段2);
-- 删除複合索引：
alter table 表名 drop index 索引名 on 表名;           

索引優勢和劣勢

優勢

• 提高資料檢索的效率，降低資料庫的IO成本。
• 通過索引對資料進行排序，降低資料排序的成本，降低了CPU的消耗。

劣勢

• 索引實際上也是一張表，儲存了主鍵和索引的字段，并且指向實體表的記錄，是以索引也是需要占用空間的。
• 在索引大大提高查詢速度的同時，卻會降低表的更新速度，在對表進行資料增删改的同時，MySQL不僅要更新資料，還需要儲存一下索引檔案。
• 每次更新添加了的索引列的字段，都會去調整因為更新帶來的減值變化後的索引的資訊。

索引使用場景

• 主鍵自動建立唯一索引
• 頻繁作為查詢條件的字段應該建立索引(where 後面的語句)
• 查詢中與其它表關聯的字段，外鍵關系建立索引
• 多字段查詢下傾向建立複合索引
• 查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度
• 查詢中統計或者分組字段

常見的索引資料結構

hash、二叉樹、BST(二叉搜尋樹)、AVL(自平衡二叉查找樹)、紅黑樹、B樹、B+樹

hash資料結構

memory存儲引擎是用的該資料結構

缺點：無法支援範圍查詢，需要解決哈希碰撞(擾動函數)

二叉樹、BST、AVL、紅黑樹

二叉樹

當極端情況下，資料遞增插入時，會一直向右插入，形成連結清單，查詢效率會降低

BST

BST相較一般的二叉樹，特點概括其來隻有一條：處處滿足順序性。

AVL

他的左右子樹都是AVL樹，左右子樹高度差(又稱平衡因子)的絕對值不操作1

如果一顆二叉搜尋樹是高度平衡的，他就是AVL樹，如果他有n個節點，其高度可保持在O(log2n),搜尋時間複雜度O(log2n)。

紅黑樹

是一種二叉搜尋樹，但在每個結點上增加一個存儲位表示結點的顔色，可以是Red或Black。 通過任何一條從根到葉子的路徑上各個結點着色方式的限制，紅黑樹確定沒有一條路徑會比其他路徑長出倆倍，因而是接近平衡的

上面樹的缺點：隻有兩個分支，資料量大後樹的深度較大，io操作頻繁

B樹、B+樹

B樹

• 三層結構B樹能存多少資料

• 如何加大資料的存儲
• 加大資料存儲後的缺點是什麼

B+樹

• 與B樹相比有什麼差別
• 三層B+樹能存多少資料
• 生産環境我們一般是幾層結構
• 主鍵我們應該怎麼設定，使用自增ID還是uuid,或者是其他的，他們在插入操作是有什麼差別

問題

1. 一個表中可以有幾個索引？（多個）
2. 一個索引對應一顆樹，還是多個索引對應一顆樹？
3. 在B+樹葉子結點存儲資料，那麼實際的資料有幾份？（那為什麼不是多份呢？按照我們的目前的了解是不是多個索引下，每個索引的葉子結點要存儲資料，那innodb是怎麼做的呢）
4. 索引的葉子結點存儲什麼？

在innodb存儲引擎中，資料在進行插入的時候必須要跟某一個索引的列綁定，如果有主鍵就使用主鍵，如果沒有使用唯一索引，如果還是沒有innodb會生成一個6位元組的rowId,其他索引的葉子結點存儲的是跟資料綁定存儲的索引列的值

常見概念

聚簇索引、非聚簇索引

聚簇索引：跟資料綁定的索引就是聚簇索引

非聚簇索引：跟非聚簇索引綁定的索引就是非聚簇索引

回表、索引覆寫、最左比對、索引下推

回表

先根據索引查詢到聚簇索引列值，然後根據聚簇索引列去查詢對應的資料的過程叫做回表

索引覆寫

發現索引的葉子結點包含了所有的查詢列，不需要回表，這個過程就是索引覆寫，要盡量使用

最左比對

組合索引就像一個漏鬥會減少我們過濾資料，就像我們的級聯選擇一樣

索引下推

select *
from tb_user
where name='a766ed' and age>20           

5.6之前版本的執行流程是通過組合索引查找name='a766ed',然後到記憶體中過濾age大于20的資料

5.6版本之後預設開啟索引下推(Using index condition),通過組合索引查找name='a766ed',然後直接在組合索引上過濾age>20的資料

SQL性能分析

MySQL常見瓶頸

• SQL中對大量資料進行比較、關聯、排序、分組時CPU的瓶頸。
• 執行個體記憶體滿足不了緩存資料或排序等需要，導緻産生大量的實體IO。查詢資料時掃描過多資料行，導緻查詢效率低。

Explain

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

• 表的讀取順序
• 哪些索引可以使用
• 資料讀取操作的操作類型
• 那些索引被實際使用
• 表之間的引用
• 每張表有多少行被優化器查詢

EXPLAIN關鍵字使用起來比較簡單： explain + SQL語句：

Explain重要字段名

id字段：

• select查詢的序列号，表示查詢中執行select子句或操作表的順序。
• id相同時，執行順序由上至下。
• id不同，如果是子查詢，id的序号會遞增，id值越大優先級越高，則先被執行。
• id相同和不同都存在時，id相同的可以了解為一組，從上往下順序執行，所有組中，id值越大，優先級越高越先執行。

select_type字段：

查詢的類型，常見值有：

• SIMPLE :簡單的 select 查詢,查詢中不包含子查詢或者UNION。
• PRIMARY:查詢中若包含任何複雜的子部分，最外層查詢則被标記為Primary。
• DERIVED:在FROM清單中包含的子查詢被标記為DERIVED(衍生),MySQL會遞歸執行這些子查詢, 把結果放在臨時表裡。(mysql5.7+過後)
• SUBQUERY: 在SELECT或WHERE清單中包含了子查詢。
• MATERIALIZED 物化子查詢,子查詢來自視圖
• UNION：在 UNION 查詢語句中的第二個和緊随其後的 SELECT。
• UNION RESULT：組中union結果的臨時表
• DEPENDENT SUBQUERY 子查詢依賴外部查詢(慎用)
• DEPENDENT UNION 子查詢中包含union

table字段：

• 顯示這一行的資料是關于哪張表的。

type字段：

• 通路類型排序(從左往右索引效率越高)：

• 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和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的。
• all：Full Table Scan，将周遊全表以找到比對的行。

從最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般來說，最好保證查詢能達到range級别，最好能達到ref。

possible_keys字段：

顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上如果存在索引，則該索引将會被列出來，但不一定會被查詢實際使用上。

key字段：

查詢中實際使用的索引，如果為NULL，則沒有使用索引。

key_len字段：

查詢中實際使用索引的性能，越大越好。

ref字段：

顯示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段：

rows列顯示MySQL認為它執行查詢時必須檢查的行數。一般越少越好。

extra字段：

一些常見的重要的額外資訊：

• Using filesort：MySQL無法利用索引完成的排序操作稱為“檔案排序”。
• Using temporary：Mysql在對查詢結果排序時使用臨時表，常見于排序order by和分組查詢group by。
• Using index：表示索引被用來執行索引鍵值的查找，避免通路了表的資料行，效率不錯。
• Using where：表示使用了where過濾。
• Using index condition：索引下推

盡量避免Using filesort!

查詢優化

索引失效

• 最佳左字首法則：如果索引了多列，要遵循最左字首法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。
• 不在索引列上做任何計算、函數操作，會導緻索引失效而轉向全表掃描。
• 存儲引擎不能使用索引中範圍條件右邊的列。

因為 >,<,>=,<=不一定走索引，因為優化器會計算，符合條件的值的總和，與總資料量的比值，比值<=0.30，才會走索引

• Mysql在使用不等于時無法使用索引會導緻全表掃描。
• is null可以使用索引，但是is not null無法使用索引。
• like以通配符開頭會使索引失效導緻全表掃描。
• 字元串不加單引号索引會失效。
• 使用or連接配接時索引失效。

示範：

複合索引練習

需求查詢年齡為20歲姓名叫“883dea”的男生

在tb_user表建立複合索引

alter table tb_user
    add index ind_name_age_sex (name,age, sex);           

單表查詢優化

explain
select *
from tb_order
where goods_id='20230613103906GO00000001' and order_status>1
order by count desc
limit 10;           

執行計劃是

在goods_id、order_status、count建立組合索引再次測試

在goods_id、count重新建立索引這裡保證兩個索引之間沒有其他的索引列 使key_len效率最高

關聯查詢優化

内連接配接時，mysql會自動把小結果集的選為驅動表，是以大表的字段最好加上索引。左外連接配接時，左表會全表掃描，是以右邊大表字段最好加上索引，右外連接配接同理。我們最好保證被驅動表上的字段建立了索引。

explain
select A.user_id,A.name,B.order_id
from tb_user A left join tb_order B on A.user_id=B.user_id
where name ='a766ed' and age>20 and age<22
limit 100;           

執行計劃

排序優化

• 盡量避免使用Using FileSort方式排序。
• order by語句使用索引最左前列或使用where子句與order by子句條件組合滿足索引最左前列。
• where子句中如果出現索引範圍查詢會導緻order by索引失效。

explain
select *
from tb_user
where name='a766ed'
order by age           

執行計劃

分組優化

explain
select sex,count(1)
from tb_user
where name='a766ed' and age>20
group by sex;           

執行計劃

添加索引name、age、sex

慢查詢日志

介紹：MySQL的慢查詢日志是MySQL提供的一種日志記錄，他用來記錄在MySQL中響應時間超過閥值的語句，具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。可以由它來檢視哪些SQL超出了我們最大忍耐時間值。

預設情況下，MySQL資料庫沒有開啟慢查詢日志，需要手動設定參數：

• 檢視是否開啟：show variables like '%slow_query_log%';
• 開啟日志：set global slow_query_log = 1;
• 設定時間: set global long_query_time = 1;
• 檢視時間: SHOW VARIABLES LIKE 'long_query_time%';
• 檢視逾時的sql記錄日志：Mysql的資料檔案夾下/data/...-slow.log;在Navicat中輸入show variables like '%slow_query_log%指令'，就可以得到檔案目錄;

注意：非調優場景下，一般不建議啟動改參數，慢查詢日志支援将日志記錄寫入檔案，開啟慢查詢日志會或多或少帶來一定的性能影響。

前提

預設已經具備sql基本知識，能夠編寫簡單sql語句

運作流程

在z級資料中如何找到我們想要找的資料呢？

計算機是如何加載的資料的

io、如何加快io的通路（減少io次數，減少IO的量）、資料的存儲原理、資料量大了計算機是如何讀取資料

mysql以page的方式加載資料

索引介紹

什麼是Mysql索引

• MySQL官方對于索引的定義：索引是幫助MySQL高效擷取資料的資料結構。例如我們書籍的目錄、字典
• MySQL在存儲資料之外，資料庫系統中還維護着滿足特定查找算法的資料結構，這些資料結構以某種引用(指向)表中的資料，這樣我們就可以通過資料結構上實作的進階查找算法來快速找到我們想要的資料。而這種資料結構就是索引。
• 簡單了解為“排好序的可以快速查找資料的資料結構”。

索引分類

1. 主鍵索引

表中的列設定為主鍵後，資料庫會自動建立主鍵索引。

-- 建立主鍵索引文法： 
alter table 表名 add primary key (字段);
-- 删除主鍵索引文法： 
alter table 表名 drop primary key;           

1. 唯一索引

表中的列建立了唯一限制時，資料庫會自動建立唯一索引。

-- 建立唯一索引文法：
alter table 表名 add unique 索引名(字段)；
-- 删除唯一索引文法：
alter table 表名 drop index 索引名 on 表名;           

1. 單值索引

即一個索引隻包含單個列，一個表可以有多個單值索引，建表時可随表一起建立單值索引

-- 建立單值索引：
alter table 表名 add index 索引名(字段);
-- 删除單值索引：
alter table 表名 drop index 索引名 on 表名;           

1. 複合索引

即一個索引包含多個列，建表時可随表一起建立複合索引

-- 建立複合索引：
alter table 表名 add index 索引名(字段,字段2);
-- 删除複合索引：
alter table 表名 drop index 索引名 on 表名;           

索引優勢和劣勢

優勢

• 提高資料檢索的效率，降低資料庫的IO成本。
• 通過索引對資料進行排序，降低資料排序的成本，降低了CPU的消耗。

劣勢

• 索引實際上也是一張表，儲存了主鍵和索引的字段，并且指向實體表的記錄，是以索引也是需要占用空間的。
• 在索引大大提高查詢速度的同時，卻會降低表的更新速度，在對表進行資料增删改的同時，MySQL不僅要更新資料，還需要儲存一下索引檔案。
• 每次更新添加了的索引列的字段，都會去調整因為更新帶來的減值變化後的索引的資訊。

索引使用場景

• 主鍵自動建立唯一索引
• 頻繁作為查詢條件的字段應該建立索引(where 後面的語句)
• 查詢中與其它表關聯的字段，外鍵關系建立索引
• 多字段查詢下傾向建立複合索引
• 查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度
• 查詢中統計或者分組字段

常見的索引資料結構

hash、二叉樹、BST(二叉搜尋樹)、AVL(自平衡二叉查找樹)、紅黑樹、B樹、B+樹

hash資料結構

memory存儲引擎是用的該資料結構

缺點：無法支援範圍查詢，需要解決哈希碰撞(擾動函數)

二叉樹、BST、AVL、紅黑樹

二叉樹

當極端情況下，資料遞增插入時，會一直向右插入，形成連結清單，查詢效率會降低

BST

BST相較一般的二叉樹，特點概括其來隻有一條：處處滿足順序性。

AVL

他的左右子樹都是AVL樹，左右子樹高度差(又稱平衡因子)的絕對值不操作1

如果一顆二叉搜尋樹是高度平衡的，他就是AVL樹，如果他有n個節點，其高度可保持在O(log2n),搜尋時間複雜度O(log2n)。

紅黑樹

是一種二叉搜尋樹，但在每個結點上增加一個存儲位表示結點的顔色，可以是Red或Black。 通過任何一條從根到葉子的路徑上各個結點着色方式的限制，紅黑樹確定沒有一條路徑會比其他路徑長出倆倍，因而是接近平衡的

上面樹的缺點：隻有兩個分支，資料量大後樹的深度較大，io操作頻繁

B樹、B+樹

B樹

• 三層結構B樹能存多少資料

• 如何加大資料的存儲
• 加大資料存儲後的缺點是什麼

B+樹

• 與B樹相比有什麼差別
• 三層B+樹能存多少資料
• 生産環境我們一般是幾層結構
• 主鍵我們應該怎麼設定，使用自增ID還是uuid,或者是其他的，他們在插入操作是有什麼差別

問題

1. 一個表中可以有幾個索引？（多個）
2. 一個索引對應一顆樹，還是多個索引對應一顆樹？
3. 在B+樹葉子結點存儲資料，那麼實際的資料有幾份？（那為什麼不是多份呢？按照我們的目前的了解是不是多個索引下，每個索引的葉子結點要存儲資料，那innodb是怎麼做的呢）
4. 索引的葉子結點存儲什麼？

在innodb存儲引擎中，資料在進行插入的時候必須要跟某一個索引的列綁定，如果有主鍵就使用主鍵，如果沒有使用唯一索引，如果還是沒有innodb會生成一個6位元組的rowId,其他索引的葉子結點存儲的是跟資料綁定存儲的索引列的值

常見概念

聚簇索引、非聚簇索引

聚簇索引：跟資料綁定的索引就是聚簇索引

非聚簇索引：跟非聚簇索引綁定的索引就是非聚簇索引

回表、索引覆寫、最左比對、索引下推

回表

先根據索引查詢到聚簇索引列值，然後根據聚簇索引列去查詢對應的資料的過程叫做回表

索引覆寫

發現索引的葉子結點包含了所有的查詢列，不需要回表，這個過程就是索引覆寫，要盡量使用

最左比對

組合索引就像一個漏鬥會減少我們過濾資料，就像我們的級聯選擇一樣

索引下推

select *
from tb_user
where name='a766ed' and age>20           

5.6之前版本的執行流程是通過組合索引查找name='a766ed',然後到記憶體中過濾age大于20的資料

5.6版本之後預設開啟索引下推(Using index condition),通過組合索引查找name='a766ed',然後直接在組合索引上過濾age>20的資料

SQL性能分析

MySQL常見瓶頸

• SQL中對大量資料進行比較、關聯、排序、分組時CPU的瓶頸。
• 執行個體記憶體滿足不了緩存資料或排序等需要，導緻産生大量的實體IO。查詢資料時掃描過多資料行，導緻查詢效率低。

Explain

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

• 表的讀取順序
• 哪些索引可以使用
• 資料讀取操作的操作類型
• 那些索引被實際使用
• 表之間的引用
• 每張表有多少行被優化器查詢

EXPLAIN關鍵字使用起來比較簡單： explain + SQL語句：

Explain重要字段名

id字段：

• select查詢的序列号，表示查詢中執行select子句或操作表的順序。
• id相同時，執行順序由上至下。
• id不同，如果是子查詢，id的序号會遞增，id值越大優先級越高，則先被執行。
• id相同和不同都存在時，id相同的可以了解為一組，從上往下順序執行，所有組中，id值越大，優先級越高越先執行。

select_type字段：

查詢的類型，常見值有：

• SIMPLE :簡單的 select 查詢,查詢中不包含子查詢或者UNION。
• PRIMARY:查詢中若包含任何複雜的子部分，最外層查詢則被标記為Primary。
• DERIVED:在FROM清單中包含的子查詢被标記為DERIVED(衍生),MySQL會遞歸執行這些子查詢, 把結果放在臨時表裡。(mysql5.7+過後)
• SUBQUERY: 在SELECT或WHERE清單中包含了子查詢。
• MATERIALIZED 物化子查詢,子查詢來自視圖
• UNION：在 UNION 查詢語句中的第二個和緊随其後的 SELECT。
• UNION RESULT：組中union結果的臨時表
• DEPENDENT SUBQUERY 子查詢依賴外部查詢(慎用)
• DEPENDENT UNION 子查詢中包含union

table字段：

• 顯示這一行的資料是關于哪張表的。

type字段：

• 通路類型排序(從左往右索引效率越高)：

• 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和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的。
• all：Full Table Scan，将周遊全表以找到比對的行。

從最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般來說，最好保證查詢能達到range級别，最好能達到ref。

possible_keys字段：

顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上如果存在索引，則該索引将會被列出來，但不一定會被查詢實際使用上。

key字段：

查詢中實際使用的索引，如果為NULL，則沒有使用索引。

key_len字段：

查詢中實際使用索引的性能，越大越好。

ref字段：

顯示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段：

rows列顯示MySQL認為它執行查詢時必須檢查的行數。一般越少越好。

extra字段：

一些常見的重要的額外資訊：

• Using filesort：MySQL無法利用索引完成的排序操作稱為“檔案排序”。
• Using temporary：Mysql在對查詢結果排序時使用臨時表，常見于排序order by和分組查詢group by。
• Using index：表示索引被用來執行索引鍵值的查找，避免通路了表的資料行，效率不錯。
• Using where：表示使用了where過濾。
• Using index condition：索引下推

盡量避免Using filesort!

查詢優化

索引失效

• 最佳左字首法則：如果索引了多列，要遵循最左字首法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。
• 不在索引列上做任何計算、函數操作，會導緻索引失效而轉向全表掃描。
• 存儲引擎不能使用索引中範圍條件右邊的列。

因為 >,<,>=,<=不一定走索引，因為優化器會計算，符合條件的值的總和，與總資料量的比值，比值<=0.30，才會走索引

• Mysql在使用不等于時無法使用索引會導緻全表掃描。
• is null可以使用索引，但是is not null無法使用索引。
• like以通配符開頭會使索引失效導緻全表掃描。
• 字元串不加單引号索引會失效。
• 使用or連接配接時索引失效。

示範：

複合索引練習

需求查詢年齡為20歲姓名叫“883dea”的男生

在tb_user表建立複合索引

alter table tb_user
    add index ind_name_age_sex (name,age, sex);           

單表查詢優化

explain
select *
from tb_order
where goods_id='20230613103906GO00000001' and order_status>1
order by count desc
limit 10;           

執行計劃是

在goods_id、order_status、count建立組合索引再次測試

在goods_id、count重新建立索引這裡保證兩個索引之間沒有其他的索引列 使key_len效率最高

關聯查詢優化

内連接配接時，mysql會自動把小結果集的選為驅動表，是以大表的字段最好加上索引。左外連接配接時，左表會全表掃描，是以右邊大表字段最好加上索引，右外連接配接同理。我們最好保證被驅動表上的字段建立了索引。

explain
select A.user_id,A.name,B.order_id
from tb_user A left join tb_order B on A.user_id=B.user_id
where name ='a766ed' and age>20 and age<22
limit 100;           

執行計劃

排序優化

• 盡量避免使用Using FileSort方式排序。
• order by語句使用索引最左前列或使用where子句與order by子句條件組合滿足索引最左前列。
• where子句中如果出現索引範圍查詢會導緻order by索引失效。

explain
select *
from tb_user
where name='a766ed'
order by age           

執行計劃

分組優化

explain
select sex,count(1)
from tb_user
where name='a766ed' and age>20
group by sex;           

執行計劃

添加索引name、age、sex

慢查詢日志

介紹：MySQL的慢查詢日志是MySQL提供的一種日志記錄，他用來記錄在MySQL中響應時間超過閥值的語句，具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。可以由它來檢視哪些SQL超出了我們最大忍耐時間值。

預設情況下，MySQL資料庫沒有開啟慢查詢日志，需要手動設定參數：

• 檢視是否開啟：show variables like '%slow_query_log%';
• 開啟日志：set global slow_query_log = 1;
• 設定時間: set global long_query_time = 1;
• 檢視時間: SHOW VARIABLES LIKE 'long_query_time%';
• 檢視逾時的sql記錄日志：Mysql的資料檔案夾下/data/...-slow.log;在Navicat中輸入show variables like '%slow_query_log%指令'，就可以得到檔案目錄;

注意：非調優場景下，一般不建議啟動改參數，慢查詢日志支援将日志記錄寫入檔案，開啟慢查詢日志會或多或少帶來一定的性能影響。

前提

預設已經具備sql基本知識，能夠編寫簡單sql語句

運作流程

在z級資料中如何找到我們想要找的資料呢？

計算機是如何加載的資料的

io、如何加快io的通路（減少io次數，減少IO的量）、資料的存儲原理、資料量大了計算機是如何讀取資料

mysql以page的方式加載資料

索引介紹

什麼是Mysql索引

• MySQL官方對于索引的定義：索引是幫助MySQL高效擷取資料的資料結構。例如我們書籍的目錄、字典
• MySQL在存儲資料之外，資料庫系統中還維護着滿足特定查找算法的資料結構，這些資料結構以某種引用(指向)表中的資料，這樣我們就可以通過資料結構上實作的進階查找算法來快速找到我們想要的資料。而這種資料結構就是索引。
• 簡單了解為“排好序的可以快速查找資料的資料結構”。

索引分類

1. 主鍵索引

表中的列設定為主鍵後，資料庫會自動建立主鍵索引。

-- 建立主鍵索引文法： 
alter table 表名 add primary key (字段);
-- 删除主鍵索引文法： 
alter table 表名 drop primary key;           

1. 唯一索引

表中的列建立了唯一限制時，資料庫會自動建立唯一索引。

-- 建立唯一索引文法：
alter table 表名 add unique 索引名(字段)；
-- 删除唯一索引文法：
alter table 表名 drop index 索引名 on 表名;           

1. 單值索引

即一個索引隻包含單個列，一個表可以有多個單值索引，建表時可随表一起建立單值索引

-- 建立單值索引：
alter table 表名 add index 索引名(字段);
-- 删除單值索引：
alter table 表名 drop index 索引名 on 表名;           

1. 複合索引

即一個索引包含多個列，建表時可随表一起建立複合索引

-- 建立複合索引：
alter table 表名 add index 索引名(字段,字段2);
-- 删除複合索引：
alter table 表名 drop index 索引名 on 表名;           

索引優勢和劣勢

優勢

• 提高資料檢索的效率，降低資料庫的IO成本。
• 通過索引對資料進行排序，降低資料排序的成本，降低了CPU的消耗。

劣勢

• 索引實際上也是一張表，儲存了主鍵和索引的字段，并且指向實體表的記錄，是以索引也是需要占用空間的。
• 在索引大大提高查詢速度的同時，卻會降低表的更新速度，在對表進行資料增删改的同時，MySQL不僅要更新資料，還需要儲存一下索引檔案。
• 每次更新添加了的索引列的字段，都會去調整因為更新帶來的減值變化後的索引的資訊。

索引使用場景

• 主鍵自動建立唯一索引
• 頻繁作為查詢條件的字段應該建立索引(where 後面的語句)
• 查詢中與其它表關聯的字段，外鍵關系建立索引
• 多字段查詢下傾向建立複合索引
• 查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度
• 查詢中統計或者分組字段

常見的索引資料結構

hash、二叉樹、BST(二叉搜尋樹)、AVL(自平衡二叉查找樹)、紅黑樹、B樹、B+樹

hash資料結構

memory存儲引擎是用的該資料結構

缺點：無法支援範圍查詢，需要解決哈希碰撞(擾動函數)

二叉樹、BST、AVL、紅黑樹

二叉樹

當極端情況下，資料遞增插入時，會一直向右插入，形成連結清單，查詢效率會降低

BST

BST相較一般的二叉樹，特點概括其來隻有一條：處處滿足順序性。

AVL

他的左右子樹都是AVL樹，左右子樹高度差(又稱平衡因子)的絕對值不操作1

如果一顆二叉搜尋樹是高度平衡的，他就是AVL樹，如果他有n個節點，其高度可保持在O(log2n),搜尋時間複雜度O(log2n)。

紅黑樹

是一種二叉搜尋樹，但在每個結點上增加一個存儲位表示結點的顔色，可以是Red或Black。 通過任何一條從根到葉子的路徑上各個結點着色方式的限制，紅黑樹確定沒有一條路徑會比其他路徑長出倆倍，因而是接近平衡的

上面樹的缺點：隻有兩個分支，資料量大後樹的深度較大，io操作頻繁

B樹、B+樹

B樹

• 三層結構B樹能存多少資料

• 如何加大資料的存儲
• 加大資料存儲後的缺點是什麼

B+樹

• 與B樹相比有什麼差別
• 三層B+樹能存多少資料
• 生産環境我們一般是幾層結構
• 主鍵我們應該怎麼設定，使用自增ID還是uuid,或者是其他的，他們在插入操作是有什麼差別

問題

1. 一個表中可以有幾個索引？（多個）
2. 一個索引對應一顆樹，還是多個索引對應一顆樹？
3. 在B+樹葉子結點存儲資料，那麼實際的資料有幾份？（那為什麼不是多份呢？按照我們的目前的了解是不是多個索引下，每個索引的葉子結點要存儲資料，那innodb是怎麼做的呢）
4. 索引的葉子結點存儲什麼？

在innodb存儲引擎中，資料在進行插入的時候必須要跟某一個索引的列綁定，如果有主鍵就使用主鍵，如果沒有使用唯一索引，如果還是沒有innodb會生成一個6位元組的rowId,其他索引的葉子結點存儲的是跟資料綁定存儲的索引列的值

常見概念

聚簇索引、非聚簇索引

聚簇索引：跟資料綁定的索引就是聚簇索引

非聚簇索引：跟非聚簇索引綁定的索引就是非聚簇索引

回表、索引覆寫、最左比對、索引下推

回表

先根據索引查詢到聚簇索引列值，然後根據聚簇索引列去查詢對應的資料的過程叫做回表

索引覆寫

發現索引的葉子結點包含了所有的查詢列，不需要回表，這個過程就是索引覆寫，要盡量使用

最左比對

組合索引就像一個漏鬥會減少我們過濾資料，就像我們的級聯選擇一樣

索引下推

select *
from tb_user
where name='a766ed' and age>20           

5.6之前版本的執行流程是通過組合索引查找name='a766ed',然後到記憶體中過濾age大于20的資料

5.6版本之後預設開啟索引下推(Using index condition),通過組合索引查找name='a766ed',然後直接在組合索引上過濾age>20的資料

SQL性能分析

MySQL常見瓶頸

• SQL中對大量資料進行比較、關聯、排序、分組時CPU的瓶頸。
• 執行個體記憶體滿足不了緩存資料或排序等需要，導緻産生大量的實體IO。查詢資料時掃描過多資料行，導緻查詢效率低。

Explain

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

• 表的讀取順序
• 哪些索引可以使用
• 資料讀取操作的操作類型
• 那些索引被實際使用
• 表之間的引用
• 每張表有多少行被優化器查詢

EXPLAIN關鍵字使用起來比較簡單： explain + SQL語句：

Explain重要字段名

id字段：

• select查詢的序列号，表示查詢中執行select子句或操作表的順序。
• id相同時，執行順序由上至下。
• id不同，如果是子查詢，id的序号會遞增，id值越大優先級越高，則先被執行。
• id相同和不同都存在時，id相同的可以了解為一組，從上往下順序執行，所有組中，id值越大，優先級越高越先執行。

select_type字段：

查詢的類型，常見值有：

• SIMPLE :簡單的 select 查詢,查詢中不包含子查詢或者UNION。
• PRIMARY:查詢中若包含任何複雜的子部分，最外層查詢則被标記為Primary。
• DERIVED:在FROM清單中包含的子查詢被标記為DERIVED(衍生),MySQL會遞歸執行這些子查詢, 把結果放在臨時表裡。(mysql5.7+過後)
• SUBQUERY: 在SELECT或WHERE清單中包含了子查詢。
• MATERIALIZED 物化子查詢,子查詢來自視圖
• UNION：在 UNION 查詢語句中的第二個和緊随其後的 SELECT。
• UNION RESULT：組中union結果的臨時表
• DEPENDENT SUBQUERY 子查詢依賴外部查詢(慎用)
• DEPENDENT UNION 子查詢中包含union

table字段：

• 顯示這一行的資料是關于哪張表的。

type字段：

• 通路類型排序(從左往右索引效率越高)：

• 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和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的。
• all：Full Table Scan，将周遊全表以找到比對的行。

從最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般來說，最好保證查詢能達到range級别，最好能達到ref。

possible_keys字段：

顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上如果存在索引，則該索引将會被列出來，但不一定會被查詢實際使用上。

key字段：

查詢中實際使用的索引，如果為NULL，則沒有使用索引。

key_len字段：

查詢中實際使用索引的性能，越大越好。

ref字段：

顯示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段：

rows列顯示MySQL認為它執行查詢時必須檢查的行數。一般越少越好。

extra字段：

一些常見的重要的額外資訊：

• Using filesort：MySQL無法利用索引完成的排序操作稱為“檔案排序”。
• Using temporary：Mysql在對查詢結果排序時使用臨時表，常見于排序order by和分組查詢group by。
• Using index：表示索引被用來執行索引鍵值的查找，避免通路了表的資料行，效率不錯。
• Using where：表示使用了where過濾。
• Using index condition：索引下推

盡量避免Using filesort!

查詢優化

索引失效

• 最佳左字首法則：如果索引了多列，要遵循最左字首法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。
• 不在索引列上做任何計算、函數操作，會導緻索引失效而轉向全表掃描。
• 存儲引擎不能使用索引中範圍條件右邊的列。

因為 >,<,>=,<=不一定走索引，因為優化器會計算，符合條件的值的總和，與總資料量的比值，比值<=0.30，才會走索引

• Mysql在使用不等于時無法使用索引會導緻全表掃描。
• is null可以使用索引，但是is not null無法使用索引。
• like以通配符開頭會使索引失效導緻全表掃描。
• 字元串不加單引号索引會失效。
• 使用or連接配接時索引失效。

示範：

複合索引練習

需求查詢年齡為20歲姓名叫“883dea”的男生

在tb_user表建立複合索引

alter table tb_user
    add index ind_name_age_sex (name,age, sex);           

單表查詢優化

explain
select *
from tb_order
where goods_id='20230613103906GO00000001' and order_status>1
order by count desc
limit 10;           

執行計劃是

在goods_id、order_status、count建立組合索引再次測試

在goods_id、count重新建立索引這裡保證兩個索引之間沒有其他的索引列 使key_len效率最高

關聯查詢優化

内連接配接時，mysql會自動把小結果集的選為驅動表，是以大表的字段最好加上索引。左外連接配接時，左表會全表掃描，是以右邊大表字段最好加上索引，右外連接配接同理。我們最好保證被驅動表上的字段建立了索引。

explain
select A.user_id,A.name,B.order_id
from tb_user A left join tb_order B on A.user_id=B.user_id
where name ='a766ed' and age>20 and age<22
limit 100;           

執行計劃

排序優化

• 盡量避免使用Using FileSort方式排序。
• order by語句使用索引最左前列或使用where子句與order by子句條件組合滿足索引最左前列。
• where子句中如果出現索引範圍查詢會導緻order by索引失效。

explain
select *
from tb_user
where name='a766ed'
order by age           

執行計劃

分組優化

explain
select sex,count(1)
from tb_user
where name='a766ed' and age>20
group by sex;           

執行計劃

添加索引name、age、sex

慢查詢日志

介紹：MySQL的慢查詢日志是MySQL提供的一種日志記錄，他用來記錄在MySQL中響應時間超過閥值的語句，具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。可以由它來檢視哪些SQL超出了我們最大忍耐時間值。

預設情況下，MySQL資料庫沒有開啟慢查詢日志，需要手動設定參數：

• 檢視是否開啟：show variables like '%slow_query_log%';
• 開啟日志：set global slow_query_log = 1;
• 設定時間: set global long_query_time = 1;
• 檢視時間: SHOW VARIABLES LIKE 'long_query_time%';
• 檢視逾時的sql記錄日志：Mysql的資料檔案夾下/data/...-slow.log;在Navicat中輸入show variables like '%slow_query_log%指令'，就可以得到檔案目錄;

注意：非調優場景下，一般不建議啟動改參數，慢查詢日志支援将日志記錄寫入檔案，開啟慢查詢日志會或多或少帶來一定的性能影響。