MySQL 索引详解

 

MySQL 索引

作用:约束+加速查找

一.索引分类:

1.普通索引:加速查找
2.主键索引:加速查找 + 约束(不能为空) + 唯一
3.唯一索引:加速查找 + 唯一
4.联合索引(多列)
    - 联合主键索引
    - 联合唯一索引
    - 联合普通索引
'''
前三个是单列(字段)索引
'''

 

二.索引特点:

无索引:从前到后依次查找,速度慢,浪费时间

有索引:会创建关联索引字段的额外文件(某种格式),当按该索引字段查找数据时,会先到该文件查找数据在表的那个位置,再到表中定位到数据

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

三.索引格式种类

hash索引:会将索引字段的数据转换成hash值,每个hash值都绑定了对应数据的存储地址

# 缺点:因为hash数值是随机生成的,导致了存在hash索引表中的数据的无序,所以如果当你要查找id大于5(范围)的数据时,就会慢了
# 优点:找单值速度快

 

btree索引(******):

 

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘