MySQL索引及索引数据结构
文章目录
索引是帮助MySQL高效获取数据的排好序的数据结构
-
主键和unique约束的字段会自动添加索引(根据主键查询效率较高,尽量根据主键检索)
-
索引操作
- 添加索引
create index 索引名 on 表名(字段名)
; - 删除索引
drop index 索引名 on 表名(字段名)
;
- 添加索引
-
索引分类:
- 单一索引:给单个字段添加索引
- 联合索引:多个字段联合起来添加一个索引
- 主键索引:主键上会自动添加索引
- 唯一索引:unique约束的字段会自动添加索引
-
索引失效的情况:
模糊查询时,第一个查询字符是不确定值(’%‘或’_’)时,索引失效
select name from stu where name like '%e%';
索引失效select name from stu where name like 'h%';
索引有效
数据结构
-
索引数据结构
- 二叉树
- 红黑树
- Hash表
- B-Tree树
-
红黑树结构:
-
Hash表:效率高,但是无法支持范围查找
-
B-Tree结构
- 叶节点具有相同的深度,叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
-
B+Tree (B-Tree变种)
-
非叶子节点不存储data,只存储冗余索引,可以存放更多的索引
-
叶子节点包含所有的索引字段
-
叶子节点用指针连接,提高区间访问的性能
-
MyISAM存储引擎索引实现
MyISAM索引文件和数据文件是分离的(非聚集)
-
.frm文件 存储表结构和数据定义的信息
-
.MYD 存储数据
-
.MYI 存储索引
-
在使用了索引查找的情况下,先在MYI文件中定位到目标节点,再去MYD文件快速查找
InnoDB索引实现
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引 - 叶节点包含完整的数据记录
- 为什么InnoDB必须有主键,并且推荐使用整型的自增主键?
- 需要一个主键索引,用B+Tree来组织维护数据。
- 没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
- 如果没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)
- 使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+Tree叶子节点分裂顺序一致,无须频繁的移动、分页操作。
- 为什么非主键索引结构叶子节点存储的是主键值?
- 保持一致性:当数据库表进行DML操作时,同一行的页地址会发生改变,因非主键索引保存的是主键的值,无需进行更改。
- 节省存储空间:InnoDB数据本身就已经汇聚到主键索引所在的B+Tree上了,如果普通索引还继续保存一份数据,就会导致有多少索引就要存多少份数据。