【MySQL查缺补漏学习】三、MySQL索引

说明：【MySQL查缺补漏学习】系列是在工作之余，梳理的一些关于MySQL的一些容易忽略的知识点，通过回顾和补充也可以更加系统的学习MySQL，以便在工作中更加游刃有余。

MySQL索引

索引（MySQL）里叫“键“）是存储引擎用于快速找到记录的一种数据结构。

为了更好的理解，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 300 页的书，如果你想快速找到其中的某一个知识点，如果不看目录，估计可得找一会儿。同样，对于数据库的表而言，索引就像是它的“目录”。

索引的类型

索引有很多类型，可以为不同场景提供更好的性能。在MySQL中，索引存在于存储引擎而不是服务层。

B-Tree 索引

大多说MySQL引擎都支持这种索引。B-Tree 通常意味着所有的值是按照顺序存储的。并且每一个叶子页到根的距离相同。

实际上很多存储引擎使用的是 B+tree，即每一个叶子结点都包含指向下一个叶子结点的指针。从而方便叶子结点的范围遍历。

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。

当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10ms左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20个10ms的时间。

B-Tree 索引适用于全键值、键值范围、建前缀查找，其中键前缀查找只适用于根据最左原则的查找。因为索引的树是有序的，可以用于查询中的 order by 操作（按顺序查找）。

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有的的列都无法使用索引优化查询。 比如 where a=''aa' and b like ‘b%’ and c =‘12345'（ 数据表 key (a,b,c) ），这个查询只用到了前两列，因为like 是范围查询。

哈希索引

哈希索引是基于哈希表实现的，是一种以键 - 值（key-value）存储数据的结构，只有精准匹配索引所有列的查询才有效。对每一行数据，存储引擎对所有的索引列计算一个哈希码（hash code），哈希索引将所有的哈希码存在索引中，同时在哈希表中保存指向每个数据的指针。

简单来说，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

你可以设想下，如果你现在要找身份证号在 [ID_card_X, ID_card_Y] 这个区间的所有用户，就必须全部扫描一遍了。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

对于哈希索引，总结一下:

• 哈希索引只包含哈希值和指针，不存储字段值。所以不能使用索引中的值来避免读取行。不过，访问内存的速度快，大部分情况下 这一点对性能的影响并不明显。

• 哈希索引的数据不是按照顺序存储的，无法使用排序。

• 哈希索引也不支持部分索引列匹配查找。如，在数据列（a,b）上建立哈希索引，如果查询只有列a，则无法使用该索引。

• 哈希索引支持等值比较查询，包括 =、in()、<=> ，不支持范围查询。

创建自定义哈希索引

如果存储引擎不支持哈希索引，则可以创建一个伪哈希索引。
思路：在 B-Tree 上创建一个伪哈希索引。使用哈希值而不是键本身进行查找。我们要做的是在查询 WHERE 子句中指定使用哈希函数（如crc32、sha1，MD5）。这也是一种给字符串加索引的方式。

如：

mysql> select field_list from url where url="https://www.mysql.com/";

删除原来在url 列上的索引，新增一个被索引的url_crc的列，使用 crc32做哈希，便可以使用下面的方式查询：

mysql> select field_list from url where url_crc=crc32("https://www.mysql.com/") and url="https://www.mysql.com/";

这样做的性能会非常高。因为MySQL优化器会使用这个选择性高、体积小的 url_crc 列索引来完成查找。不过缺陷就是需要维护哈希值。

注：为什么不直接只使用 url_crc 来做where 条件呢？
如果数据表非常大，crc32 会出现打了重复的哈希冲突。因所谓的生日悖论（自行搜素查询释义），出现哈希冲突的概率的增长速度可能比想象的还要快得多。要避免冲突，必须在where 条件中带入哈希值和对应的值。

空间数据索引 （R -Tree）

MYISAM 表支持空间索引，可以用来存储地理位置数据。MySQL的GIS支持不完善，大部分人不会使用这个特性。

全文索引

全文索引是一特殊的索引，它查找的是文本中的关键词，而不是比较索引中的值。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引类似搜索引擎做的事，而不是简单的 where 条件匹配。

在 相同的列上创建 全文索引和B-Tree 索引并不冲突，全文索引适合于 MATCH AGAINST 操作，而不是普通的 WHERE 操作。

MySQL的全文索引有很多限制，可能无法满足自己的应用场景，我们可以使用 Sphinx作为一个MySQL内部搜索引擎来使用。（http://sphinxsearch.com/）

END

---

如有问题请在下方留言。

或关注我的公众号“孙三苗”，输入“联系方式”。获得进一步帮助。