数据库索引设计与优化

这是读完了 <高性能MySQL> , <数据库索引设计与优化> 和 MySQL Manual 之后的总结。本文的所有知识都是与 MySQL 相关的，其他 数据库也许有不同。

• 索引的本质是用写入时间换取查询时间

• 所有的优化都是有有效上限的，例如内存当然是越大越好，但是当你把所有的数据和缓存等都装下了之后，内存再往上增加就不会再 有明显的所用。CPU当然是越快越好，但是如果你的查询不是CPU bound，那么也不会有明显作用。磁盘随机IO当然是越快越好，也许有一天 磁盘可以达到和内存相当的速度，那时候磁盘也许就不再是瓶颈。

• 通常情况下来说，MySQL只能使用一颗索引树，但是5.0之后有索引合并的优化，这种情况下可以使用多个索引，例如：

mysql jiajun@127.0.0.1:fd_test> EXPLAIN SELECT * FROM user WHERE mobile='12345678910' OR fullname='小白';
+------+---------------+---------+-------------+---------------------+---------------------+-----------+--------+--------+-----------------------------------------------+
| id   | select_type   | table   | type        | possible_keys       | key                 | key_len   | ref    | rows   | Extra                                         |
|------+---------------+---------+-------------+---------------------+---------------------+-----------+--------+--------+-----------------------------------------------|
| 1    | SIMPLE        | user    | index_merge | MOBILE_IDX,IDX_NAME | MOBILE_IDX,IDX_NAME | 767,768   | <null> | 2      | Using union(MOBILE_IDX,IDX_NAME); Using where |
+------+---------------+---------+-------------+---------------------+---------------------+-----------+--------+--------+-----------------------------------------------+
1 row in set
Time: 0.010s

诸如AND, OR等语句，MySQL可能会使用多个索引检索出结果，然后对多个结果进行AND，OR等操作。

• 三星索引

  • 第一颗星在 WHERE 语句的所有条件。如果该条件可以使用一个索引（假设有多个condition，则应当有一个联合索引），这样可以 加快过滤的速度
  • 第二颗星在 ORDER BY 语句。详见文章最下面的引用。使用例如 SELECT * FROM user ORDER BY mobile LIMIT 5 这样的查询 时，MySQL可以直接从 mobile 的索引进行查找，充分的利用了 mobile 索引的有序性。
  • 第三颗星在 SELECT 后面的字段，如果 SELECT 所取出来的字段都是索引里的值，那么就无需从真正的数据里获取到指定的 那一行来获取所需要的数据了。

符合三星的查询是最佳的查询，但是实际过程我们往往难以做到，例如如果使用了ORM，一般都没有可能拿到第三颗星。

• 我们来假装自己是MySQL服务器，模拟一下查询的过程。首先收到请求校验请求等这些过程我们就先忽略，直接看到利用索引的 这个过程。

  • 优化器决定是否使用某个索引
  • 使用某个索引之后，索引通常是B树或者其变种。通常根节点一定在内存中，其下层的节点可能会在内存中也可能会在磁盘上，取决 于操作系统内存是否充足以及是否将其缓存。
    • 假设将其缓存在内存中，则一步一步往下查找，在内存中自然速度是非常快的，一直到找到叶子节点
    • 假设没有缓存在内存中，则找到子节点所在的磁盘中的块，读取数据，然后继续进行，一直找到叶子节点
    • 此前的步骤中，数据往往是连续的。找到叶子节点之后，读取叶子节点中的数据，找到数据真正所在的行，这一步往往是 随机I/O。因为数据所存储的地方可能是不同的。
      • 但是主键通常会是一张表中有且仅有的聚族索引。聚族索引是连续存储的索引。

• 联合索引是个好东西，但是使用联合索引查询时，会遵循最左匹配原则，例如有个联合索引是 (key1, key2, key3) ，那么能用上 的查找是 (key1) ， (key1, key2) ， (key1, key2, key3) 。其实这里我有一点不明白，其实索引可以特殊处理一下，然后可以跳 过其中某个值进行使用，例如 (key1, key3) ，原因是无论是 int还是varchar都可以换算成大小固定的值，例如int是4 byte， varchar换成一个指针，这样就可以进行固定大小的偏移从而跨值利用索引。也许是为了降低复杂度所以目前并没有这样做，也许是有 其他原因。

另外为什么是最左匹配原则呢？因为比较索引的时候是从左往右比较。

• JOIN 是怎么使用索引的？最朴素的JOIN自然是 for...for 嵌套，例如 SELECT A.* FROM A JOIN B ON A.key1 = B.key1 ，可能会 生成类似于

def query():
    result = []

    for row_in_b in B:
        for row_in_a in A:
            if row_in_b.key1 == row_in_a.key1:
                result.append(row_in_a)

    return result

这样的代码，如果在连表的字段上有索引，那么可能会生成：

def query():
    result = []

    for row_in_b in B:
        if row_in_b.key1 in A.key1:
            row_in_a = [i for i in A where A.key1 = row_in_b.key1]
            result.extend(row_in_a)

    return result

从而把时间复杂度从 O(n^2) 降低到 O(n lgn)

• IN 语句是怎么使用索引的？MySQL会把 IN 后面的条件先排序，然后进行范围查找。因为B树是有序的，所以可以减少很多无关数据。

还有很多。。。不过不想写了。。。还是看书吧，哈哈哈

• ORDER BY如何使用索引
• 高性能MySQL
• 数据库索引设计和优化
• MySQL manual