共計 3158 個字符，預計需要花費 8 分鐘才能閱讀完成。
這篇文章主要介紹“MySQL 索引及優化的知識點有哪些”的相關知識，丸趣 TV 小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL 索引及優化的知識點有哪些”文章能幫助大家解決問題。
索引是什么？
索引是幫助 MySQL 進行高效查詢的一種數據結構。好比一本書的目錄，能加快查詢的速度
索引的結構？
索引可以有 B -Tree 索引，Hash 索引。索引是在存儲引擎中實現的
InnoDB / MyISAM 僅支持 B-Tree 索引
Memory/Heap 支持 B -Tree 索引和 Hash 索引
B-Tree
B-Tree 是一種非常適合用于磁盤操作的數據結構。它是一棵多路平衡查找樹。其高度一般在 2 -4，其非葉子節點，葉子節點，都會存儲數據。其所有的葉子節點，都在同一層。下圖是一顆 B -Tree
B+ Tree：B+ 樹是在 B -Tree 基礎上的一種優化。它和 B 樹的主要區別在于：B+ 樹的數據全部存儲在葉子節點中，且葉子節點被一個鏈表串了起來。下圖是一顆 B + 樹
InnoDB 中一個頁的大小為 16KB（一個頁即 B + 樹上的一個節點），若表的主鍵為 INT，大小為 4 字節，那一個節點也能夠存儲 4K 個鍵值，假設指針和鍵值都占相同大小，那么高度為 3 的 B + 樹，第二層有 2048 個節點，第三層的葉子節點數為 2048*2048 = 4194304，一個節點為 16KB，則一共可容納 67108864KB，即 65536MB，即 64G 的數據。
由于葉子節點是被一個鏈表串起來的，所以若 order by 索引列，則默認已經是排好序的，所以效率會很高。
MyISAM 索引
MyISAM 的索引和數據是分開存放的。在 MyISAM 的主鍵索引中，B+ 樹葉子節點里，存的是記錄的地址，故 MyISAM 通過索引查詢，需要經過 2 次 IO
MyISAM 的輔助索引和主鍵索引一樣，唯一的區別是，輔助索引中的 key 可以重復，而主鍵索引的 key 不能重復
InnoDB 索引
InnoDB 的數據和索引是存放在一起的，又稱聚集索引。數據通過主鍵索引，存放在主鍵索引 B + 樹的葉子節點上。
InnoDB 主鍵索引，數據已經包含在了葉子節點中，即索引和數據存放在一起，是為聚集索引。
InnoDB 的輔助索引，葉子節點中存的是主鍵值，而不是地址。走輔助索引，需要檢索 2 次。
InnoDB 和 MyISAM 索引的區別：
InnoDB 使用聚集索引，其主鍵索引葉子節點中直接存儲了數據，而其輔助索引中葉子節點存的是主鍵的值
MyISAM 使用非聚集索引，數據和索引不在同一個文件中，其主鍵索引中葉子節點上存的是該行記錄所在的地址，其輔助索引中葉子節點上存的也是記錄所在的地址，只是輔助索引的 key 可以重復，而主鍵索引的 key 不能重復
問題：
InnoDB 為什么不要使用過長的字段做主鍵？
過長的主鍵，會使得輔助索引所占空間變得很大
為什么推薦 InnoDB 使用自增主鍵？
若使用自增主鍵，則每次插入新的記錄，就會順序的將新記錄添加到當前索引節點的后續位置，一頁寫滿了，才會進行開辟新的一頁，這樣使得索引結構很緊湊，且每次插入時不需要移動已有數據，非常高效。而如果不使用自增主鍵，則每次插入新記錄時，都要選擇一個插入位置，并且可能需要移動數據，使得效率不高，且索引結構不緊湊
為什么要用 B + 樹，不用 B 樹
索引存在哪兒？
索引本身也比較大，一般會存儲在磁盤中，索引和數據可能是分開存放的（MyISAM 的非聚集索引），也可能是一起存放的（InnoDB 的聚集索引）
索引的優缺點?
優點
降低 IO 成本，提高數據查詢效率
降低排序成本（被索引的列會自動排序，使用 order by 效率會提高很多）
缺點
索引會額外占據存儲空間
索引會降低更新表數據的效率。進行增刪改操作時，不僅要保存數據，還要更新對應的索引
索引的分類
單列索引
主鍵索引
唯一索引
普通索引
組合索引
索引使用
建立索引
CREATE INDEX index_name ON table_name(col_name);
-- 或者
ALTER TABLE table_name ADD INDEX index_name(col_name)刪除索引
DROP INDEX index_name ON table_name;需要建立索引的場景
頻繁作為查詢條件的列，需建索引
多表關聯中，關聯字段需建索引
查詢中排序的字段，需建索引
不適用索引的場景
寫多讀少的表，不適合建索引
頻繁更新的字段，不適合建索引
explain 執行計劃
現有一張 user 表，其索引如下所示
其中 name,age,address 三個字段作為一個組合索引
可以使用 explain 對某個 SQL 語句進行性能分析
explain select * from user where name = ampossible_keys
可能用到的索引
key
實際用到的索引
key_len
用于查詢的索引的長度
ref
如果是等值查詢，這里會會是 const
rows
預計需要掃描的行數（不是精確值）
extra
額外信息，如
using where
表示存儲引擎返回的結果，還需要在 SQL Layer 層過濾
using index
表示不需要回表查詢，一般在使用了覆蓋索引時會是這個值。覆蓋索引指的是，select 中的列，全是索引列。不需要回表查詢指的是，直接走輔助索引，就能拿到索引列的值，不需要再去主鍵索引上取記錄了
using index condition
MySQL 5.6.x 之后支持 ICP 特性（Index Condition Pushdown），可以把檢查條件下推到存儲引擎層，不符合條件的記錄，直接不讀取，而不是像原來一樣，先讀取出來，再在 SQL Layer 層過濾，這樣減少了存儲引擎層掃描的行數
using filesort
排序時無法用到索引
type
system : 表中只有 1 行數據，或空表
const : 使用唯一索引或主鍵索引，且用 where 等值查詢，返回記錄是 1 行，又叫唯一索引掃描
ref : 針對非唯一索引，使用等值 where 條件，或者最左前綴規則的查詢。
下面是滿足了最左前綴規則，即對 idx_name_age_add 來說，滿足了最左前綴，第一個索引為 name
range：索引范圍掃描，常見于，，between，in，like 等查詢
注意 like 時，通配符 % 不能放在開頭，否則會導致全表掃描
index：沒有完全匹配上索引，但不用回表查詢的
all: 全表掃描，然后再在 SQL Layer 層過濾符合要求的記錄
索引使用規范（索引失效分析）
全值匹配
在索引列上使用等值查詢
explain select * from user where name = y and age = 15;
2. 最左前綴
組合索引中，查詢條件要從組合索引的最左列開始，如上述 example 中組合索引 idx_name_age_add，是建立在三個列 name，age，address 的，若跳過 name，直接用 age 查詢，則會變為全表掃描
explain select * from user where age = 15;
3. 不要在索引列上做計算
4. 范圍條件右側的索引列會失效
看到第一個 SQL 語句，沒有用上 addresss 索引
5. 盡量使用覆蓋索引
explain select name,age from user where name = y and age = 1;可以避免回表查詢
6. 索引字段不要使用不等（!= 或）, 不要判斷 null（is null/ is not null）
會導致索引失效，轉為全表掃描
7. 索引字段上使用 like 時，不要以 % 開頭
8. 索引字段如果是字符串，記得加單引號
9. 索引字段不要用 or
例子總結：
關于“MySQL 索引及優化的知識點有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注丸趣 TV 行業資訊頻道，丸趣 TV 小編每天都會為大家更新不同的知識點。
