mysql索引系列二：分类介绍各种索引

目录

简述

各数据结构索引简介

各B+树索引详细介绍

其他关于索引的小知识：

---

简述

mysql索引的分类大致如下图所示：

 

 

各数据结构索引简介

B+树索引

InnoDB中使用，B+树通常意味着所有值都是按照顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。B+树可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。

 

Hash索引

只有Memory存储引擎显示支持。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据的指针。hash索引只有精确索引所有列的查询才有效。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，由Mysql自动管理，不需要DBA人为干预。默认情况下为开启，我们可以通过参数innodb_adaptive_hash_index来禁用此特性。

当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于缓冲池中的B+ Tree索引上再创建一个哈希索引，这样就上B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找

 

全文索引

全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词，主要用于全文检索。

 

空间索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

 

各B+树索引详细介绍

聚簇索引

1. InnoDB对主键建立聚簇索引。

2. 如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。

3. 如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。

4.一个表只能有一个聚簇索引，MYSQL的存储引擎不支持用户指定聚簇索引；

5.InnoDB使用聚簇索引来组织数据，索引应该尽量避免随机的聚簇主键，比如字符串主键；

6.聚簇索引中节点页包含了索引列，叶子页包含了行的全部数据。

 

二级索引

1、叶子节点保存的不是行的物流位置的指针，而是行的主键值；

2、二级索引查找行，存储引擎通常需要两次B+树查找，一次找到对应的主键值，一次根据主键值在聚簇索引中找到对应的行；

3、若是覆盖索引，则二级索引中已经包含了查询所需的数据，就不再需要在聚簇索引中查找了。

 

普通索引

1、最基本的索引，用key和index关键字创建；

2、使用update，for update上锁时，通常会产生gap 锁，加锁的不止当前行，还有索引间的间隙，间隙一般左开右闭；

 

唯一索引

1、列的值必须唯一，但是允许有控制。

2、如果是组合索引，则列值的组合必须唯一。

3、对于使用唯一索引 来搜索唯一行的语句，只加记录锁不加间隙锁(这并不包括组合唯一索引）。

 

主键索引：

1、特殊的唯一索引，不允许有空值

2、一个表只能有一个主键

 

组合索引

1、组合索引一般由于多个单列索引，因为多个单列索引，mysql只能用到它认为似乎是最有效率的单列索引；

2、组合索引的使用符合“最左前缀”的结果，简单说，就是从索引的最左边列开始匹配，不能跳过某一列，也不能使用范围查找列后面的索引。

 

其他关于索引的小知识：

1、同一个表索引总数上限是16个

2、可以使用前缀索引减少索引的大小，加快索引查询的速度；

3、show status like 'Handler_read%'可以得到两个值Handler_read_key和Handler_read_rnd_next。Handler_read_key代表一个行被索引值读的次数，很低代表增加索引得到的性能改善不高；Handler_read_rnd_next代表数据文件中读下一行的请求书，如果很高，意味着正在进行大量的表扫描，索引效率不高。

4、尽量避免在 where 子句中使用!=或<>操作符，否则存储引擎会放弃索引使用全表扫描；

5、mysql无法使用前缀索引做order by和group by

6、where条件中若进行了函数转化，则存储引擎会放弃索引使用全表扫描。同样的道理，如果列类型是字符串，那where条件中一定要把字符串常量值用引号引起来，否则mysql默认把输入的常量值转换以后才进行检索，造成索引无效。

 

 

参考：https://mp.weixin.qq.com/s/h4B84UmzAUJ81iBY_FXNOg