数据库索引详解

什么是索引?

索引是一种用于快速查询和检索数据的数据结构。
常见的索引结构有: B 树， B+树和 Hash。

索引的优缺点

优点

• 大大加快数据的检索速度（大大减少的检索的数据量），大部分系统都是读请求大于写请求的
• 通过建立唯一索引，可以保证数据库中数据的唯一性

缺点

• 创建索引和维护索引需要耗费许多时间，如果对表中的数据进行增删改操作时，索引也需要进行修改，降低了效率
• 占用物理存储空间，索引需要使用物理文件进行存储，会占用一定的空间

B树和B+树

B树

• 所有结点既存放键（Key），也存放数据（data）
• 叶子结点是相互独立的
• 因为每个结点都存放着data，所以检索时相当于对范围内的每个结点使用二分查找，直到查询到相应的值

B+树

• 叶子结点存放键（Key）和数据（data），其他结点只存放键（Key）
• 叶子结点会有一条引用链指向和它相邻的叶子结点
• 查询效率稳定，因为只有叶子结点存放data，所以每次查询都是从根节点到叶子结点的一条路径
  

Hash索引和B+树索引的优劣

• Hash索引定位快

Hash索引使用Hash表，可以在很短的时间内通过索引定位到数据的位置

• Hash冲突问题

Hash索引可能会存在Hash冲突问题

• Hash索引不支持顺序和范围查询

B+树是有序的，顺序或范围查询时只需要遍历即可，Hash索引是根据Hash算法计算Hash值来查询，每次都进行一次Hash算法，效率低下

索引类型

主键索引（Primary Key）

• 数据表中的主键用的就是主键索引
• 一张数据表只能有一个主键索引，并且不能为null，不能重复
• 在mysql的InnoDB中，如果没有指定表的主键，InnoDB会自动检查表中是否唯一索引的字段，如果有，会把这个字段设置为主键，如果没有，会自动创建一个6Byte的自增主键

二级索引（辅助索引）

二级索引又叫做辅助索引，叶子结点存储的数据是主键，所以通过二级索引可以定位到主键的位置
唯一索引，普通索引，前缀索引等索引属于二级索引

• 唯一索引（Unique Key）

唯一索引的字段不能出现重复值，但是允许数据为Null，一张表允许创建多个唯一索引，唯一索引的目的是实现字段的唯一性

• 普通索引（Index Key）

普通索引的目的是为了快速查询到数据，一张表允许创建多个普通索引，允许数据为null和重复

• 前缀索引（Prefix Key）

前缀索引只适用于字符串数据，对文本的前几个字符创建索引，相比普通索引占用的数据更小

• 全文索引（Full Text) Key）

全文索引主要是为了检索大文本数据中关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚集索引和非聚集索引

聚集索引

聚集索引即索引结构和数据一起存放的索引，主键索引属于聚集索引
在mysql中，InnoDB引擎的表的.ibd文件包含了表的索引和数据，B+树的每个非叶子结点存储索引，叶子结点存储索引和索引对应的数据

优点

查询速度非常快，B+树本身是一棵多叉平衡树，叶子结点是有序的，只要定位到索引的位置，就相当于定位到了数据

缺点

• 依赖有序的数据，因为B+树是平衡的，所以如果数据不是有序的，就需要在插入时排序，速度较慢
• 更新代价大，如果索引处的数据被更改，那么对应的索引也会被更改，修改代价大，所以对于主键来说，一般是不可被修改的

非聚集索引

非聚集索引即索引结构和数据分开存放的索引，二级索引属于非聚集索引
MYISAM 引擎的表的.MYI 文件包含了表的索引，B+树的每个非叶子节点存储索引，叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据
二级索引的叶子结点存放的是索引和主键，需要根据主键回表查询数据

优点

更新代价小，因为叶子结点不存放数据

缺点

• 依赖有序的数据
• 可能会二次查询（回表），如果查询到叶子结点的数据指针或主键时，可能还需要根据指针或主键再查询表中的数据

非聚集索引一定会回表吗

不一定
如果用户使用sql语句查询用户名，而且用户名字段正好建立了索引

SELECT name FROM table WHERE name='zzy';

直接返回索引对应的数据指针指向的name数据就好了，无需回表查询其他数据。
同样的，如果查询的时主键

SELECT id FROM table WHERE id = 1;

本身索引对应的数据指针指向的就是主键，直接返回就可以
以上两种情况就叫做覆盖索引

覆盖索引

如果一个索引包含了所需要查询的字段的值，这就叫做覆盖索引，覆盖索引就是查询的列和索引是对应的，无需通过主键再查询一次
覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。
对于主键索引，如果查询的字段正好是主键，那么直接根据主键索引就可以查询出主键了，无需回表
对于普通索引，如果要查询name字段，而name字段正好有索引，那么直接根据索引查到name数据，也无需回表

索引创建原则

单列索引

单列索引即由一列属性组成的索引。

联合索引(多列索引)

联合索引即由多列属性组成索引。

最左前缀原则

假设创建的联合索引由三个字段组成

ALTER TABLE table ADD INDEX index_name (num,name,age)

那么当查询的条件有为：num / (num AND name) / (num AND name AND age)时，索引才生效
所以使用联合索引时，尽量把查询最频繁的字段放在最左边，查询的时候也尽量以这个字段为第一条件

索引创建注意点

最左前缀原则

有些mysql版本不遵守最左前缀原则，但是我们仍应该遵守最左前缀原则

选择合适的字段

不为 NULL 的字段

索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

被频繁查询的字段

我们创建索引的字段应该是查询操作非常频繁的字段。

被作为条件查询的字段

被作为 WHERE 条件查询的字段，应该被考虑建立索引。

被经常频繁用于连接的字段

经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

不合适创建索引的字段

被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。
如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

不被经常查询的字段没有必要建立索引

尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中就肯定能命中，那么就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

考虑在字符串类型的字段上使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

使用索引一定能提高查询性能吗

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。