MySQL索引

MySQL索引

本文主要记载了MySQL索引的原理与MySQL的索引使用，如有错误，欢迎纠正，共同进步。

1. 引言

索引是数据库表中一列或多列的值进行排序的一种结构，使用索引能够快速地访问数据库表的特定信息。索引的一个主要目标就是加快检索表格的数据。如果在没有添加索引，搜索特定的记录需要进行全表扫描，需要O(n)时间；而如果添加了索引，那么只需要lg(n)的时间。

2. 原理

索引的底层是B树、B+树或Hash。使用这些数据结构是为了尽可能地减少磁盘IO，因为一次磁盘IO需要的代价是非常高昂的。

1. B树

B树(Balance Tree)是一种自平衡的多路搜索树，对于一个m阶的B树(m阶表示所有节点中子节点数量的最大值)，它必须满足以下特点：

• 每个结点最多只有m个子节点；
• 每个非叶子结点(除了根结点)具有至少${\lceil \frac{m}{2} \rceil}；
• 具有k个子节点的节点含有k-1个关键信息。

B树的搜索从根节点开始进行二分查找，如果命中则输出当前结点的数据，否则切换到搜索目标所属范围内的子节点，进行下一轮搜索。

2. B+树

B+树是B树的一个变体，其主要特征包含：

• 有n个元素的非叶节点包含n个子树，但每个元素不保存数据，只用来索引；
• 所有的叶节点的元素包含了全部的关键字信息，及指向含有这些关键字的指针；
• 所有的非叶节点可以看作是索引的一部分，其内仅含有子树叶节点中的最大或最小的关键字。

MySQL中InnoDB使用的就是B+树，因为B+树比B树更加适合数据库索引，主要有：

• B+树的磁盘读写代价更低。因为B+树的内部节点并不保存指向关键字的指针，所以其内部节点需要的内存更小，在相同大小的内存中可以存储更多的节点信息；
• B+树查询效率更加稳定。因为B+树也属于平衡树，而平衡树的每个子树的深度差值不超过1，另外B+树内部只是索引，所以每次搜索都需要通过相近的路径长度；
• B+树便于范围查找。因为B+树的非叶节点包含的是子树叶节点中的最大或最小的关键字。

3. Hash

Hash索引就是通过哈希函数把关键字转换成一个哈希值，并通过哈希值直接查找到相应的位置。这种方法只适用于单点查询，不能用于范围查询。MySQL中MEMORY使用的就是Hash索引。

3. MySQL操作索引

MySQL中索引分为单列索引与组合索引，其中单列索引中只包含一个字段，而组合索引中则包含多个字段。

1. 单列索引
   单列索引又分为普通索引，唯一索引与主键索引，其创建方式如下：

   1. 普通索引

      create index <index_name> on <table_name>(<column>);
      // alter table <table_name> add index <index_name>(<column>);
      

      • <index_name>：索引名称；

      普通索引是最基本的索引，不同记录的索引值可以相等。

   2. 唯一索引

      create unique index <index_name> on <table_name>(<column>);
      // alter table <table_name> add unique index <index_name>(<column>);
      

      与普通索引相比，唯一索引要求所有类型的值都是唯一的，并且允许有NULL值。

   3. 主键索引
      主键索引一般会在表格建立的时候设置，并且主键一定是索引。

2. 组合索引

   create index <index_name> on <table_name>(<column1, column2, ...>);
   

查看现有的索引可以使用show：

show index from <table_name>;

删除索引时，可以使用drop：

drop index <index_name> on <table_name>;

4. 索引的优缺点

优点：

• 减少检索时间，索引就是为了减少检索时间建立的机制；
• 减少检索的数据，使用索引只会检索到特定的记录；
• 通过唯一索引确保每个记录的唯一性。

缺点：

• 索引文件会占大量的内存；
• 在更新数据时需要维护索引文件。

5. 总结

本文主要记载了MySQL索引实现的数据结构与其操作。

版权声明

转载请注明出处。