一文带你完全掌握MySQL索引

1、索引概述

• 索引是一种用于快速查询和检索数据的数据结构，相当于字典的目录，常见的索引结构有B树、B+树和hash等

• 索引的优点：

  • 使用索引可以大大加快数据检索的速度、减少检索的数据量
  • 通过创建唯一性索引，可保证数据库表中每一行数据的唯一性

• 索引的缺点：

  • 创建索引和维护索引需要耗费许多时间，当对表中的数据进行增删改的时候，如果数据有索引，则索引也需要动态地修改，会降低SQL的执行效率
  • 索引需要使用物理文件存储，会耗费一定的空间

注意：大多数情况下，索引查询是比全表扫描要快的，但是如果数据量不大，则使用不一定能够带来很大的提升

2、 索引的底层数据结构

• 哈希表之所以能通过key快速取出对应的value，原因在于哈希算法（散列算法），通过哈希算法，可以快速找到key对应的index和value

hash = hashfunc(key)
index = hash % array_size

• 哈希算法为了解决Hash 冲突 问题，即多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法，链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了解决链表过长的问题而引入了红黑树。
  

• 既然哈希表这么快，为什么MySQL 没有使用其作为索引的数据结构呢？

  • Hash 冲突问题 ：如上已经提及。
  • Hash 索引不支持顺序和范围查询(是最大的缺点， 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。
  • hash结构没有顺序，IO复杂度高。

• 不使用二叉树和红黑树的原因？

  • 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
  • 红黑树：树的高度随着数据量增加而增加，IO代价高。

B 树& B+树

• B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

• 目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

• B 树& B+树两者的异同：

  • B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定，**任何查找都是从根节点到叶子节点的过程，**叶子节点的顺序检索很明显。
    
    

3、主键类型

1）主键索引(Primary Key)

• 数据表的主键列使用的就是主键索引。
• 一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
• 如果采用 MySQL 的 InnoDB引擎，当没有显示地指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
  

2）二级索引（辅助索引）

• 二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

二级索引包括唯一索引、普通索引、前缀索引、全文索引。

• 唯一索引(Unique Key) ：唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
  

4、聚集索引和非聚集索引

聚集索引

• 聚集索引是索引结构和数据存放在一起的索引，主键索引属于聚集索引。
• 在Mysql中，如果采用InnoDB引擎，则表的.idb文件包含了该表的索引和数据，其表的索引（B+树）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据
• 聚集索引的优点
  查询速度非常快（B+树是一颗多叉平衡树，叶子节点有序，定位到索引节点，就定位到了数据）
• 聚集索引的缺点
  • 需要依赖于有序的数据，如果索引的数据不是有序的，就需要在插入数据时进行排序，对于类似于字符串或UUID这类又长又难比较的数据，插入和查找的速度都会比较慢
  • 更新代价大，如果要修改索引列的数据，则对应的索引也要修改，所以，对于主键索引来说，主键一般都是不可被修改的

非聚集索引

• 非聚集索引是索引结构和数据分开存储的索引，故二级索引属于非聚集索引
• 二级索引的叶子节点存放的是主键，需要根据主键再回表查询数据
• 优点：
  • 非聚集索引的更新代价比聚集索引要小，其叶子节点是不存放数据的
• 缺点：

  • 依赖于数据的有序性

  • 可能会二次查询（回表），当查到索引对应的指针或主键后，可能会需要根据指针或主键再到数据文件或表中再次查询
    
    

5、覆盖索引

• 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

6、联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

7、最左前缀匹配原则

• 在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。
• 所以，我们在使用联合索引时，可以将区分度高的字段放在最左边(区分度公式：count(distinct colName)/count(*)，即看某字段不重复的数据量在总数据量中的比重)，这也可以过滤更多数据。

8、创建索引的注意事项

• 1）选择合适的字段创建索引：

  • 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

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

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

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

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

• 4）注意避免冗余索引 。

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

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

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

9、MySQL 如何为表字段添加索引？

• 添加主键索引（PRIMARY KEY）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

• 添加唯一索引（UNIQUE ）

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

• 添加普通索引（INDEX ）

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

• 添加全文索引（FULLTEXT ）

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

• 添加前缀索引(以前m位字符来创建)

ALTER TABLE `table_name` ADD index ( column(m) )

• 添加组合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

参考资料：
图解 MySQL 索引：B-树、B+树
Mysql面试总结