MySQL索引

MySQL索引

简介

索引（index）是帮助MySQL高效获取数据的有序的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

使用索引的优点主要有：

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

主要缺点有：

• 索引列需要占用一定空间。
• 索引会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。因为需要维护数据结构。

总之，索引可以理解为用空间换时间的数据结构，对于需要大量查询的表，通常都需要创建索引来提高查询效率。

索引数据结构

B+Tree索引是我们最常用的索引，在没有特殊说明的情况下，索引通常都指B+Tree索引

B-Tree

B-Tree，又叫多路平衡查找树，其中多路指一个节点可以有两个以上的子节点。平衡指的是任意节点的子树的高度差都小于等于1。查找指的是一个节点下的子节点是有序的，通常指左小右大，方便查找。这是一种查找效率比较高的数据结构，具体如下图

可以访问https://www.cs.usfca.edu/~galles/visualization/Algorithms.html来查看B树的操作过程，具体不再过多解释

B+Tree

B+Tree是在B-Tree的基础上做了一定的调整，具体结构图如下

和B-Tree区别如下：

• 所有的节点会出现在叶子节点
• 只在叶子节点保存数据，其他节点只作为索引
• 叶子节点将作为一个单向链表

在MySQL的InnoDB中，对B+Tree进行了一定优化，把叶子作为一个双向的链表，以此提高区间访问的性能，具体如下图

索引分类

根据索引的作用，可以分为如下几种

根据索引的存储结构，还可以分为聚簇索引和二级索引，其中聚集索引的叶子节点存放的是数据，而二级索引的叶子节点存放的是聚集索引的值，一般是主键。

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
  

从上图可以得知，当使用主键进行查询时，会走聚集索引，直接可以从叶子节点中取到数据。

如果我们通过二级索引去查找数据，则只能拿到数据的id，需要再去使用聚集索引，才能找到真正的数据，这就是回表查询，效率会比使用主键查找慢。

索引语法

• 创建索引：若创建索引时指定了多列，称为联合索引

  CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名 (列名,...);
  

  对于文本字段，可以在列名后加(n)来表示为前n个字符创建索引，这样可以节省空间

  CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名 (列名(n),...);
  

• 查看索引：

  SHOW INDEX FROM 表名;
  

• 删除索引：

  DROP INDEX 索引名 ON 表名;