索引
|
INNODB引擎
|
#MYISAM引擎
|
MEMORY引擎
|
BTREE索引
|
支持
|
#支持
| 支援
|
HASH 索引
|
不支援
| ##不支援 | 支援 |
#R-tree 索引 | 不支援 | 支援 | 不支援 |
Full-text | 5.6版本之後支援 | 支援 | 不支援
|
#我們通常指的索引,如果沒有明確指出,都是使用B 樹(一種多路搜尋樹,不一定是二元的)結構來組織的。稱為索引的聚集索引、複合索引、前綴索引、唯一索引預設都使用 B 樹索引。
BTREE
多路平衡搜尋樹,一棵m階(m叉)BTREE滿足:
ceil表示向上取整,ceil (2.3)=3
插入關鍵字案例
#保證不破壞m階B樹的性質
#由於3階,最多只能2個節點,所以一開始26和30在一起,之後再來個85就要開始分裂了,30作為中間上位,26保持,85去到右邊
即:中間位置上位,然後左邊留在舊節點,右邊去到新結點
如圖中的70再插入的時候,70剛好是中間位置上位,然後62保持,85又去分一個新節點出來
上位後又需要分裂
##繼續向上分裂即可,同理的
比相比優勢比起二元搜尋樹,高度/深度更低,自然查詢效率更高。 B TREE
比起優勢
MySQL中的B TreeMySql索引資料結構對經典的B Tree進行了最佳化。在原B Tree的基礎上,增加一個指向相鄰葉子節點的鍊錶指標(整體類似一個雙向鍊錶的結構),就形成了帶有順序指標的B Tree,提高區間存取的效能。
細心的同學可以看出,這張圖跟我們的二元查找樹簡圖的一個最大差別是什麼?
MySQL中的B Tree 索引結構示意圖:
二元找出樹簡圖:
索引原理BTree索引:初始化介紹淺藍色的稱為一個磁碟區塊,可以看到每個磁碟區塊包含幾個資料項(深藍色所示)和指標(黃色所示) 如磁碟區塊1包含資料項17和35,包含指標P1、P2、P3,
P1表示小於17的磁碟區塊,P2表示在17和35之間的磁碟區塊,P3表示大於35的磁碟區塊。
查找過程
如果要查找資料項29,那麼首先會把磁碟區塊1由磁碟載入到內存,此時發生一次IO。在記憶體中用二分查找確定29在17和35之間,鎖定磁碟區塊1的P2指針,記憶體時間因為非常短(相比磁碟的IO)可以忽略不計,透過磁碟區塊1的P2指針的磁碟位址把磁碟區塊3由磁碟載入到內存,發生第二次IO,29在26和30之間,鎖定磁碟區塊3的P2指針,透過指標載入磁碟區塊8到內存,發生第三次IO,同時記憶體中通過二分查找搜尋到29,結束查詢,總計三次IO。
真實的情況是,3層的B 樹可以表示上百萬的數據,如果上百萬的數據查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個數據項都要發生一次IO,那麼總共需要百萬次的IO,顯然成本非常非常高。
索引分類
索引組織表是一種以主鍵順序作為索引儲存的表,該方式適用於InnoDB引擎。由於InnoDB採用了B 樹索引模型,因此資料儲存在B 樹中。
每一個索引在InnoDB裡面對應一棵B 樹。
假設,我們有一個主鍵列為ID的表,表中有欄位k,並且在k上有索引。
這個表格的建表語句是:
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
复制代码
表中R1~R5的(ID,k)值分別為(100,1)、(200,2)、(300,3)、 (500,5)和(600,6),兩棵樹的範例示意圖如下:
# 從圖中不難看出,根據葉子節點的內容,索引類型分為主鍵索引和非主鍵索引。
主鍵索引
資料表的主鍵列使用的就是主鍵索引,且會預設創建,這也是為什麼,我們還沒學索引的時候,老師常跟我們說根據主鍵查會快一點,原來主鍵本身就建好了索引。
主鍵索引的葉子節點存的是整行資料。在InnoDB裡,主鍵索引也被稱為叢集索引(clustered index)。
輔助索引
輔助索引的葉子節點內容是主鍵的值。在InnoDB裡,輔助索引也被稱為二級索引(secondary index)。
如下圖:
#根據上面的索引結構,我們來討論一個問題:基於主鍵索引和輔助索引的查詢有什麼不同?
也就是說,基於輔助索引的查詢需要多掃描一棵索引樹。因此,我們在應用程式上應盡量使用主鍵查詢。
除非說,我們所要查詢的數據,剛好就是我們索引樹上存在的,此時我們稱之為覆蓋索引--即索引列中包含了我們要查詢的所有資料。
同時,二級索引又分為如下幾種(先簡單略過即可,後續我們再慢慢了解):
# #唯一索引(Unique Key) :唯一索引也是一種限制。 唯一索引的屬性列不能出現重複的數據,但是允許資料為 NULL,一張表允許建立多個唯一索引。 建立唯一索引的目的大部分時候都是為了該屬性列的資料的唯一性,而不是為了查詢效率。
普通索引(Index) :普通索引的唯一作用就是為了快速查詢數據,一張表允許創建多個普通索引,並允許資料重複和NULL。
前綴索引(Prefix) :前綴索引只適用於字串類型的資料。前綴索引是對文字的前幾個字元建立索引,相比普通索引建立的資料更小, 因為只取前幾個字元。
全文索引(Full Text) :全文索引主要是為了檢索大文本資料中的關鍵字的信息,是目前搜尋引擎資料庫使用的一種技術。 Mysql5.6 之前只有 MYISAM 引擎支援全文索引,5.6 之後 InnoDB 也支援了全文索引
擴充--索引下推
所謂下推,顧名思義,其實是延後我們的回錶操作,MySQL不會輕易讓我們去回表,因為很浪費。什麼意思呢?來看下邊這個例子。
我們建立了一個複合索引(name,status,address),索引中也是按這個欄位來儲存的,類似圖中這樣:
複合索引樹(只儲存索引列和主鍵用於回表)
name |
#status |
address |
id(主鍵) |
#小米1 |
##0
|
1
|
1
|
小米2
|
1
|
1
|
#2
|
我们执行这样一条语句:
SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ;
复制代码
首先我们在复合索引树上,找到了第一个以小米开头的name -- 小米1
此时我们不着急回表(回到主键索引树搜索的过程,我们称为回表),而是先在复合索引树判断status是否=1,此时status=0,我们直接就不回表了,直接继续找下一个以小米开头的name
找到第二个-- 小米2,判断status=1,则根据id=2去主键索引树上找,得到所有的数据
这种先在自身索引树上判断是否满足其他的where条件,不满足则直接pass掉,不进行回表的操作,就叫做索引下推。
最左前缀原则
所谓最左前缀,可以想象成一个爬楼梯的过程,假设我们有一个复合索引:name,status,address,那这个楼梯由低到高依次顺序是:name,status,address,最左前缀,要求我们不能出现跳跃楼梯的情况,否则会导致我们的索引失效:
-
按楼梯从低到高,无出现跳跃的情况--此时符合最左前缀原则,索引不会失效
出现跳跃的情况
这两个尽管where中字段的顺序不一样,第二个看起来越级了,但实际上效果是一样的
其实是因为我们MySQL有一个Optimizer(查询优化器),查询优化器会将SQL进行优化,选择最优的查询计划来执行。
索引设计原则
针对表
查询频次高,且数据量多的表
针对字段
最好从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。
其他原则
最好用唯一索引,区分度越高,使用索引的效率越高
不是越多越好,维护也需要时间和空间代价,建议单张表索引不超过 5 个
因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能。
比如:
我们创建了三个单列索引,name,status,address
当我们where中根据status和address两个字段来查询时,数据库只会选择最优的一个索引,不会所有单列索引都使用。
最优的索引:具体是指所查询表中,辨识度最高(所占比例最少)的索引列,比如此处address中有一个辨识度很高的 '西安市'数据;
使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀,比如有N个字段,我们不一定需要创建N个索引,可以用复合索引
也就是说,我们尽量创建复合索引,而不是单列索引
创建复合索引:
CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);
就相当于
对name 创建索引 ;
对name , email 创建了索引 ;
对name , email, status 创建了索引 ;
复制代码
举个栗子
假设我们有这么一个表,id为主键,没有创建索引:
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB
复制代码
如果要在此处建立复合索引,我们要遵循什么原则呢?
通过调整顺序,可以少维护一个索引
如果我们建立索引(age,name),由于最左前缀原则,我们这个索引能实现的是根据age,根据age和name查询,并不能单纯根据name查询(因为跳跃了),为了实现我们的需求,我们还得再建立一个name索引;
而如果我们通过调整顺序,改成(name,age),就能实现我们的需求了,无需再维护一个name索引,这就是通过调整顺序,可以少维护一个索引。
考虑空间->短索引
我们有两种方案:
建立联合索引(name,age),建立单列索引:age索引。
建立联合索引(age,name),建立单列索引:name索引。
这两种方案都能实现我们的需求,这个时候我们就要考虑空间了,name字段是比age字段大的,显然方案1所耗费的空间是更小的,所以我们更倾向于方案1。
何时建立索引
where中的查询字段
查询中与其他表关联的字段,比如外键
排序的字段
统计或分组的字段
何时达咩索引
表中数据量很少
经常改动的表
频繁更新的字段
数据重复且分布均匀的表字段(比如包含了很多重复数据,那此时多叉树的二分查找,其实用处不大,可以理解为O(logn)退化了)
索引相关语法
创建索引
默认会为主键创建索引--primary
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]
复制代码
查找索引
结尾加上\G,可以变成竖屏显示
select index from tbl_name\G;
复制代码
删除索引
drop INDEX index_name on tbl_name ;
复制代码
变更索引
1). alter table tb_name add primary key(column_list);
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
2). alter table tb_name add unique index_name(column_list);
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)
3). alter table tb_name add index index_name(column_list);
添加普通索引, 索引值可以出现多次。
4). alter table tb_name add fulltext index_name(column_list);
该语句指定了索引为FULLTEXT, 用于全文索引
复制代码
查看索引使用情况
show status like 'Handler_read%'; -- 查看当前会话索引使用情况
show global status like 'Handler_read%'; -- 查看全局索引使用情况
复制代码
Handler_read_first:索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)。
Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用(这个值越高越好)。
Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。
Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。
Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。