MySql 干货分享之索引

[qbhy]

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL 是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

今天我们就来聊聊 MySQL 的索引。尽管 MySQL 有许多优点，但是在海量数据的情况下，性能方面的表现还是会让人捉急，这时候就轮到 MySQL 的索引出场了。我会以抛出问题然后解决问题的方式来进行本次分享。比如：什么是索引？索引可以做什么？为什么使用索引可以提高效率？ MySQL 支持哪些索引类型？什么情况下应不建或少建索引？什么是联合索引？为什么说 B+比 B 树更适合实际应用中操作系统的文件索引和数据库索引？

一、什么是索引？

官方解释：索引(Index)是帮助 MySQL 高效获取数据的数据结构。

通俗理解：索引是一种特殊的文件（ InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

二、索引可以做什么？

首先，索引不是万能的，索引可以加快数据检索操作，但会使数据修改操作变慢。每次修改数据记录，索引就必须刷新一次。为了在某种程度上弥补这一缺陷，许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。这个选项的作用是暂时制止 MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入 /修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。

三、为什么使用数据索引能提高效率？

• 数据索引的存储是有序的
• 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
• 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

四、MySQL 支持哪些索引类型？

我们这里说的索引类型并不是指“主键索引”、“外键索引”这些，而是索引底层的数据结构。MySQL 的索引数据结构支持以下两种：

• B-Tree 索引。B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过 1，而且同层级的节点间有指针相互链接，是有序的，如下图：
• Hash 索引。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的，如下图所示：
  • 哈希索引的优势：等值查询，哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）
  • 哈希索引不适用的情况：
    • 不支持范围查询
    • 不支持索引完成排序
    • 不支持联合索引的最左前缀匹配规则

五、什么情况下应不建或少建索引？

我们都知道什么时候应该使用索引，那么，什么时候不应该使用索引呢？我们上面说到，索引并不是万能的，所以，索引肯定也有不适用的场景。以下几个场景的时候，我们应该尽量不建或者说少建索引：

• 表记录太少。
• 经常插入、删除、修改的表。
• 数据重复且分布平均的表字段，假如一个表有 10 万行记录，有一个字段 A 只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表 A 字段建索引一般不会提高数据库的查询速度。
• 经常和主字段一块查询但主字段索引值比较多的表字段。

六、什么是联合索引？

• 联合索引是两个或更多个列上的索引。 对于联合索引，MySQL 支持从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是 key index (a,b,c)，可以支持 a 、a,b 、a,b,c 这 3 种组合进行查找，但不支持 b,c 进行查找，当最左侧字段是常量引用时，索引就十分有效。
• 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。
• 复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。

七、为什么说 B+比 B 树更适合实际应用中操作系统的文件索引和数据库索引？

• B+的磁盘读写代价更低。B+的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。
• B+-tree 的查询效率更加稳定。由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

八、结语

看到这里，你应该已经把 MySQL 的索引知识都过了个大概，希望本文能帮到你，happy hacking 。​接下来听首听首歌放松一下吧。 真的爱你 BEYOND - BEYOND