MySQL-索引原理


1. 索引本质

索引是存储引擎快速找到记录的一种数据结构。

2. 索引的分类

1)主键索引(PRIMARY KEY):列的值必须唯一且不允许有NULL值。一张表只允许有一个主键。

2)唯一索引(UNIQUE) :唯一索引列的值必须唯一,允许有NULL值。

3)普通索引:

4)组合索引:索引包含多个列。

5)全文索引(FULL TEXT): 全文检索

3. 索引数据结构

使用国外的数据结构模拟网站[https://www.cs.usfca.edu/~galles/visualization/Algorithms.html] 模拟各种数据结构的创建过程。

MySQL的索引使用B+Tree数据结构。


31. MyISAM索引

MyISAM的索引与行记录是分开存储的,叫做非聚集索引(UnClustered Index)。每张myisam表对应下面3个文件,如mysql.user[user.frm->存储表结构;user.MYD->存储数据;user.MYI->存储索引]。


1)主键索引,也是一颗B+Tree树,叶子节点存储索引列的值和叶子节点的data域存储的是数据记录的物理地址,每个叶子节点页保存下一个叶子页的指针。

MySQL-索引原理


2)二级索引,也是一颗B+Tree树,跟主键索引结构是一样,区别在于二级索引的列值可以重复


MySQL-索引原理

3.2 InnoDB索引

1)主键索引,叶子节点存储所有的主键索引及行记录数据,这种索引也叫聚集索引。内节点存储key和节点指针。

MySQL-索引原理

2)二级索引,叶子节点存储索引列和主键。使用二级索引检索数据需要检索两遍索引,首先获得主键,然后通过主键找到具体的记录。


MySQL-索引原理


3. 建议

(1)不建议使用较长的列做主键,例如uuid char(64),因为所有的普通索引都会存储主键,会导致普通索引过于庞大,索引裂变平衡,这个非常消耗性能资源;
(2)建议使用趋势递增整型列的key做主键,由于数据行与索引一体,这样不至于插入记录时,有大量索引分裂,行记录移动;

4. InnoDB索引和MyISAM索引区别

1)MyISAM的索引与数据分开存储;

2)MsISAM索引叶子节点存储数据行的物理地址

3)InnoDB索引,主键索引和数据行统一存储(*.idb文件)

4)二级索引的叶子节点存储key和主键


5. 创建索引的原则

1)适合索引的列是出现在where子句中的列,或者连接子句中指定的列;
2)基数较小的类,索引效果较差,没有必要在此列建立索引;(基数=列值唯一的数量)
3)使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;
4)不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。
5)dan表索引建议控制在5个以内
6)禁止在更新频繁、区分度不高的字段上创建索引
7)组合索引,必须把区分度字段放在前面


6. 索引优化策略

最左前缀
索引选择性与前缀索引
InnoDB的主键选择与插入优化


备注

文章中图片均为引用互联网上的图片

MySQL-索引原理

上一篇:mysql 的语句的执行顺序


下一篇:SQL Server GROUP BY 后 拼接 字符串