数据库|数据库的索引简介 MySQL|InnoDB

文章图片

文章图片

索引按照存储结构类型可主要分为Btree（也称B树）和Hash两大类型。
MyISAM和InnoDB存储引擎的默认创建的是B+tree索引， Memory存储引擎也可以为B+tree索引（或者Hash索引）。 InnoDB 的 B+Tree 索引分为主索引和辅助索引。
B+Tree索引 : B+Tree是BTree的一个变种，设d为树的度数， h为树的高度， B+Tree和BTree的不同主要在于;

B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

哈希索引：哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引” ，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
按物理存储角度分：

聚集索引：表记录的排列顺序和索引的排列顺序一致所以查询效率快只要找到第一个索引值记录其余连续性的记录在物理上一样连续存放.聚集索引的缺点就是修改慢因为为了使表记录和索引的排列顺序一致在插入记录的时候会对数据页重新排序
非聚集索引：表记录和索引的排列顺序不一定一致两种索引都采用B+树的结构非聚集索引的叶子层并不和实际数据页相重叠而采用叶子层包含一个指向表记录的指针.非聚集索引层次多不会造成数据重排。

什么时候要使用索引：
主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
什么时候不要使用索引：
经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。
建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。
更新数据的时候，系统必须要有额外的时间来同时对索引进行更新。
Mysql索引注意事项：

索引无法存储null值
不适合键值较少的列（不适合重复数据较多的列）
前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
如果条件中有or ，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or ，又想让索引生效，只能将or条件中的每个列都加上索引
对于多列索引，不是使用的第一部分，则不会使用索引
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来否则不使用索引
如果mysql估计使用全表扫描要比使用索引快则不使用索引
MySQL主要提供2种方式的索引：B-Tree索引， Hash索引， B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN) 。相当于二分查找。哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1) 。