一文搞懂 MySQL 索引
1、MySQL 索引 簡介
1.1、MySQL 索引 是什么？
?索引是一個單獨的、存儲在 磁盤 上的 數(shù)據(jù)庫結(jié)構(gòu) ，包含著對數(shù)據(jù)表里 所有記錄的 引用指針。
1.2、 MySQL 索引 的存儲類型有哪些？
?MySQL中索引的存儲類型有兩種，即 BTree 和 Hash。
1.3、MySQL 索引 在哪里實現(xiàn)的？
?索引是在存儲引擎中實現(xiàn)的。（MySQL 的存儲引擎有：InnoDB、MyISAM、Memory、Heap）
- InnoDB / MyISAM 只支持 BTree 索引
- Memory / Heap 都支持 BTree 和 Hash 索引
1.4、存儲引擎 是什么？
?存儲引擎就是指 表的類型 以及 表在計算機上的存儲方式。
1.5、索引 的優(yōu)缺點有哪些？
優(yōu)點：
- 提高數(shù)據(jù)的查詢的效率（類似于書的目錄）
- 可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性（唯一索引）
- 減少分組和排序的時間（使用分組和排序子句進行數(shù)據(jù)查詢）
- 被索引的列會自動進行分組和排序
缺點：
- 占用磁盤空間
- 降低更新表的效率（不僅要更新表中的數(shù)據(jù)，還要更新相對應(yīng)的索引文件）
2、MYSQL 索引 的分類
1、普通索引 和 唯一索引
-
普通索引：MySQL 中的基本索引類型，允許在定義索引的列中插入 重復(fù)值 和 空值
-
唯一索引：要求索引列的值必須 唯一，但允許 有空值
- 如果是組合索引，則列值的組合必須 唯一
- 主鍵索引是一種特殊的唯一索引，不允許 有空值
2、單列索引 和 組合索引
- 單列索引：一個索引只包含單個列，一個表可以有多個單列索引
- 組合索引：在表的 多個字段 組合上 創(chuàng)建的 索引
- 只有在查詢條件中使用了這些字段的 左邊字段 時，索引才會被使用（最左前綴原則）
3、全文索引
- 全文索引 的類型為 fulltext
- 在定義索引的 列上 支持值的全文查找，允許在這些索引列中插入 重復(fù)值 和 空值
- 全文索引 可以在 char、varchar 和 text 類型的 列 上創(chuàng)建
4、空間索引
-
空間索引 是對 空間數(shù)據(jù)類型 的字段 建立的索引
-
MySQL中的空間數(shù)據(jù)類型有4種，分別是 Geometry、Point、Linestring 和 Polygon
-
MySQL 使用 Spatial 關(guān)鍵字進行擴展，使得能夠用創(chuàng)建正規(guī)索引類似的語法創(chuàng)建空間索引
-
創(chuàng)建空間索引的列，不允許為空值，且只能在 MyISAM 的表中創(chuàng)建。
5、前綴索引
- 在 char、varchar 和 text 類型的 列 上創(chuàng)建索引時，可以指定索引 列的長度
3、MySQL 索引 的數(shù)據(jù)結(jié)構(gòu)
?MySQL 索引 的數(shù)據(jù)結(jié)構(gòu)可以分為 BTree 和 Hash 兩種，BTree 又可分為 BTree 和 B+Tree。
Hash：使用 Hash 表存儲數(shù)據(jù)，Key 存儲索引列，Value 存儲行記錄或行磁盤地址。
?Hash 只支持等值查詢（“=”，“IN”，“<=>”），不支持任何范圍查詢（原因在于 Hash 的每個鍵之間沒有任何的聯(lián)系），Hash 的查詢效率很高，時間復(fù)雜度為 O(1)。
BTree：屬于多叉樹，又名多路平衡查找樹。
性質(zhì)：
- BTree 的節(jié)點存儲多個元素（ 鍵值 - 數(shù)據(jù) / 子節(jié)點 的地址）
- BTree 節(jié)點的鍵值按 非降序 排列
- BTree 所有葉子節(jié)點都位于同一層（具有相同的深度）
查詢過程，例如：Select * from table where id = 6;
BTree 的不足：
- 不支持范圍查詢的快速查找（每次查詢都得從根節(jié)點重新進行遍歷）
- 節(jié)點都存儲數(shù)據(jù)會導(dǎo)致磁盤數(shù)據(jù)存儲比較分散，查詢效率有所降低
B+Tree：在 BTree 的基本上，對 BTree 進行了優(yōu)化：只有葉子節(jié)點才會存儲 鍵值 - 數(shù)據(jù)，非葉子節(jié)點只存儲 鍵值 和 子節(jié)點 的地址；葉子節(jié)點之間使用雙向指針進行連接，形成一個雙向有序鏈表。
等值查詢，例如：Select * from table where id = 8;
范圍查詢，例如：Select * from table where id between 8 and 22;
B+Tree 的優(yōu)點：
- 保證了等值查詢和范圍查詢的快速查找
- 單一節(jié)點存儲更多的元素，減少了查詢的 IO 次數(shù)
4、MySQL 索引 的實現(xiàn)
4.1、MyISAM 索引
- MyISAM 的 數(shù)據(jù)文件（.myd） 和 索引文件（.myi） 是分開存儲的
- MyISAM（B+Tree）葉子節(jié)點中存儲的鍵值為索引列的值，數(shù)據(jù)為索引所在行的磁盤地址
- MyISAM 的 主鍵索引（Primary key）和 輔助索引（Secondary key）在結(jié)構(gòu)上沒有任何區(qū)別，只是 主鍵索引 要求 鍵值唯一，而 輔助索引 鍵值 可以重復(fù)
4.2、InnoDB 索引
-
數(shù)據(jù)和索引都存儲在一個文件中（.ibd）
-
一般情況下，聚簇索引等同于主鍵索引；除 聚簇索引 外的所有索引 均稱為 輔助索引
-
InnoDB（B+Tree）葉子節(jié)點中存儲的鍵值為索引列的值
- 如果是聚簇索引，數(shù)據(jù)為整行記錄（除了主鍵值）
- 如果是輔助索引，數(shù)據(jù)為該行的主鍵值
-
每一張表都有一個聚簇索引
- 如果表中有定義主鍵，主鍵索引用作聚簇索引
- 如果表中沒有定義主鍵，選擇第一個不為 NULL 的唯一索引列用作聚簇索引
- 如果以上都沒有，使用一個 6 字節(jié)長整形的隱式字段 ROWID （自增）用作聚簇索引
-
根據(jù)在 輔助索引樹 中獲取的 主鍵id，再到 主鍵索引樹 查詢數(shù)據(jù)的過程 稱為 回表 查詢
-
組合索引
- 遵循 最左匹配（最左前綴）原則：
- 使用 組合索引 查詢時，MySQL 會一直向右匹配直至遇到范圍查詢（>、<、between、like）就停止匹配。
- 只有第一列是有序的，其它列都是無序的（最左匹配原則的原因）
- 遵循 最左匹配（最左前綴）原則：
主鍵索引（聚簇索引）：
輔助索引：
組合索引：
覆蓋索引：
- 覆蓋索引不是一種索引結(jié)構(gòu)，而是一種優(yōu)化手段
- 我們只需要查詢 組合索引 中的字段，而不需要表中的其它字段，在這過程中不會產(chǎn)生回表現(xiàn)象，這種情況稱為 覆蓋索引
create index idx on user(name, age, gender);
-- 使用覆蓋索引
explain select name, age, gender from user where name ='萬葉' and age = 18 and gender = '0';
-- 未使用覆蓋索引
explain select * from user where name ='萬葉' and age = 18 and gender = '0';
5、MySQL 索引 的使用
5.1、MySQL 索引 的基本語法
- 定義 主鍵約束、外鍵約束、唯一約束 等約束時 相當(dāng)于同時在指定列上創(chuàng)建了一個索引
創(chuàng)建表時：
create table table_name(
[col_name data_type] [unique | fulltext | spatial...],
[unique...] [index | key] [index_name] (col_name [length], ...)
);
create table user (
id INT NOT NULL,
name CHAR(30) NOT NULL,
unique index uniqueIdx(id)
);
表已存在時：
-- 第一種
alter table table_name
add [unique...] [index | key] [index_name] (col_name [length], ...);
alter table user add unique index uniqueIdx(id);
-- 第二種
create [unique...] index index_name
on table_name (col_name [length], ...);
create unique index uniqueIdx on user(id);
-- 刪除索引
drop index index_name on table_name;
5.2、怎么判斷要不要加索引？
加索引：
- 數(shù)據(jù)本身具有某種的性質(zhì)，如：唯一性、非空性…
- 頻繁進行 分組或排序 的列；如果待排序的列有多個，可以建立 組合索引
不加索引：
- 經(jīng)常更新的列
- 列 的值類型 很少，如 性別
- where 條件中用不到的列
- 參與計算的列
- 數(shù)據(jù)量小的表
5.3、只要創(chuàng)建了索引，就一定會生效嗎？
?不一定。當(dāng)使用 組合索引 時，如果沒有遵循 最左匹配 原則，索引不生效。
例如，創(chuàng)建 id、name、age 組合索引
- id、（id、name）、（id、name、age）查詢，索引生效
- age、（age、name）查詢，索引不生效
5.4、怎樣判斷索引是否生效？
?使用 explain 關(guān)鍵字。
- possible_keys：MySQL 在搜索數(shù)據(jù)記錄時可選用的各個索引
- key：MySQL 實際選用的索引
例如：
explain select * from user where id = 1;
文章來源：http://www.zghlxwxcb.cn/news/detail-440557.html
5.5、怎么避免索引失效?文章來源地址http://www.zghlxwxcb.cn/news/detail-440557.html
- 使用組合索引時，遵循 最左匹配 原則
- 不在索引列上進行任何操作，如：計算、函數(shù)、類型轉(zhuǎn)換
- 盡量使用覆蓋索引
- 索引列 盡量不使用 不等于（!= / <>）條件、通配符開頭的模糊查詢（like %abc）、or 作為連接條件
- 字符串加單引號（不加可能會發(fā)生索引列的隱式轉(zhuǎn)換，導(dǎo)致索引失效）
到了這里，關(guān)于一文搞懂 MySQL 索引的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
