程序员的MySQL手册(四)：索引设计

在了解了第三节的情况下，我们设计两个表，关系如下：

CREATE TABLE `user` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `passwd` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

CREATE TABLE `user_exam` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `score` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

接着我们分别插入10万行数据:

import random

from faker import Faker
import pymysql.cursors

fake = Faker()

# Connect to the database
connection = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='new_password',
    db='foo',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor,
)
connection.ping()

try:
    for i in range(100000):
        print(i, "...")
        fake_date = fake.date_this_month()
        with connection.cursor() as cursor:
            sql = "INSERT INTO `user` (`created_at`, `updated_at`, `name`, `passwd`) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (fake_date, fake_date, "{}:{}".format(i, fake.name()), fake.name()))
            connection.commit()

        with connection.cursor() as cursor:
            sql = "INSERT INTO `user_exam` (`created_at`, `updated_at`, `name`, `score`) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (fake_date, fake_date, fake.name(), random.randint(0, 100)))

        connection.commit()
finally:
    connection.close()

接着我们可以去看看 user 表中的查询效率区别，可以看到 user 表中，name 有索引，而 passwd 没有索引，我们看看查询效率：

MariaDB [foo]> select name, passwd from user limit 10,20;
+------------------+------------------+
| name             | passwd           |
+------------------+------------------+
| Regina Dawson    | Corey Smith Jr.  |
| Julie Jordan     | Jacob Reyes      |
| Amber Anderson   | Joe Floyd        |
| Tonya Jackson    | Joe Sosa         |
| Amy Armstrong    | Sandra Valentine |
| Jane Miller      | Daniel Booker    |
| Corey Mccarthy   | Roy Price        |
| Phillip Reynolds | Julie Wagner     |
| Jeffrey Schwartz | Shelia Clark     |
| Jessica Roberts  | Sandra Townsend  |
| Robert Rogers II | Sarah Church     |
| James Bennett    | David Rodriguez  |
| Billy Zimmerman  | Krista Lee       |
| Sheri Harris     | Billy Edwards    |
| Vicki Gomez      | Jose Dunn        |
| Craig Freeman    | Kevin Martin     |
| Anthony Brown    | Anthony Lynch    |
| Faith Carroll    | Michele Young    |
| Desiree Kelly    | Chelsea Smith    |
| Todd King        | Susan Rogers     |
+------------------+------------------+
20 rows in set (0.001 sec)

MariaDB [foo]> select COUNT(*) from user where name='Corey Mccarthy';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.001 sec)

MariaDB [foo]> select COUNT(*) from user where passwd='Sandra Townsend';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.019 sec)

通过有索引的 name 来查询，只需要0.001秒，而没有索引的 passwd 需要0.019秒。有同学可能要问了，这不是也挺快的吗？看起来是挺快的，但是要注意两个基本事实：

这个表的数据量并不大，才10万
我用的这个虚拟机所在的宿主机，磁盘是SSD

如果脱离了这两个条件之一，速度还会更慢。由此我们可以看到索引的好处了，它可以极大的提升查询速度。但是，什么样的索引才是好的索引呢？这是这次我们要探索的问题。

索引的类型

要想了解如何让索引更高效，我们必须先了解索引是如何工作的。常见的索引类型有两种，一种是B树索引，一种是哈希索引，接下来我们分别介绍。

B树索引(以及B+树索引)

通常我们讲索引，就是讲的B树索引，因为数据库一般都会使用B树或者B+树来构建索引（MySQL大部分的引擎都支持B树，InnoDB使用B+树）。 MySQL的InnoDB实现中，B+树的叶子节点存储数据，且叶子节点之间有横向指针，可以直接顺序访问，叶子节点中包含数据所在的位置（一般是存储主键ID）。

使用B树(或B+树)索引，可以优化以下查询：

全匹配查询，例如 user 的名字为 “Jhon Joes”, 查询为 SELECT * FROM user WHERE name = "Jhon Joes"
匹配左半部分，例如 SELECT * FROM user WHERE name = "Jhon Joes" AND passwd = "blabal"
匹配左边的一部分，例如 SELECT * FROM user WHERE name LIKE 'Jhon %'
匹配一个范围的数据，例如 SELECT * FROM user WHERE name > 'A' AND name < 'Z'
左边的列全匹配，加上一部分的模糊查询，例如 SELECT * FROM user WHERE name="Jhon Joes" AND passwd LIKE 'abcd%'
仅查询索引的值，例如：SELECT name FROM user WHERE name="Jhon"

其实这些总结起来，就是传说中的最左匹配原则。具体可以见参考资料中给出的链接。

同时由于B树是有顺序的，MySQL还可以使用索引来进行排序。

哈希索引

对于哈希索引，我们可以回忆一下编程语言中常用的dict或者map。对于直接求值是否相等的查询，哈希索引可以很快的给出答案，例如查询： SELECT * FROM user WHERE name="Jhon Joes"，但是哈希索引没有顺序所以无法用于排序，并且不能进行模糊查询，也无法做范围查找。并且如果hash函数没有选好的话，冲突会很严重，因此性能会下降。

如何构建一个好的索引？

综上，我们可以知道索引可以有以下几个好处：

通过查找索引，然后通过索引上保存的数据行的指针，我们可以减少所需要查询的数据量
通过索引我们可以避免一部分查询的排序
通过索引我么可以把随机I/O变成顺序I/O

那么，我们要怎么样才能构建一个足够高效的索引呢？

区分度

这里我们要引入一个新的概念，叫做区分度。什么叫区分度呢？顾名思义，在人群中一眼望去，越容易认出你来，那么你的区分度就越高。

对于数据的区分度，我们要怎么来确定呢？那就是对于一个给定的数据，在全部数据中，所占百分比越高，是不是就能查询出更多的行数呢？是的，因此区分度就越低。相反，如果给定一个数据，占的比例越低，那么区分度就越高。

举个例子，如果有一列，存储的数据是性别，只有三种可能：男，女，未知，那么这一列如果作为索引的话，区分度是很低的，因为它无法把大量的不符合条件的行都过滤掉。但是如果使用的是姓名的话，区分度就要高很多，因为重名的人不算多。但是如果是根据主键来查询的话，那么区分度就是最高了，因为主键不会重复。

对于单列索引是这样的规则，对于多列的复合索引，也是一样的规则，在满足查询条件的情况下，把区分度的列放在左边，把需要模糊查询的放在右边，这样可以获得最佳效能的索引。

索引的大小

前面我们说过，数据的类型，使用能够表示数据的越小的数据类型越好，对于索引也是一样，越小的索引，所需要的处理指令越少，占用的内存硬盘也越少。

因此，尽可能选择区分度高，而数据大小比较小的列来做索引，对于复合索引，如无必要，不使用，如使用，尽量简洁明要，选择区分度高的必要的列。

而对于过长的列，我们可以想办法选取其中的一部分来做索引，比如取一定范围的前缀，或者计算一个哈希值等。

聚簇索引(clustered index)

聚簇索引并不是一种索引类型，而是一种结合了索引的数据组织方式。MySQL的InnoDB的聚簇索引是把数据与一个B树索引存储在一起。

对于InnoDB来说，选择作为聚簇索引的索引，优先级如下：

如果有主键，选择主键
如果没有主键，选择第一个非空的UNIQUE键
如果都没有，MySQL自己偷偷的生成一个隐藏的列来做

聚簇索引实现了如下的好处：

通过聚簇索引，数据几乎是顺序存放的，比如1号user的数据和2号user的数据，在磁盘上不会离得太远
数据访问更快，比如以主键为聚簇索引，当通过其它查询查到行号为2的主键所在行时，只需要再通过聚簇索引进行一次查找就可以把数据找出来
如果只使用主键作为查询条件，那么一次就可以把所有数据查出来

其它几个索引相关的名词

secondary index(次级索引)：一个表里，只有一个索引可以做成聚簇索引，剩下的其它索引，都叫次级索引。
covering index(覆盖索引)：如果所需要的数据直接在索引里就可以找到，而不需要再去数据所在的聚簇索引查询一次，那么这次查询就叫做实现了索引覆盖。

系列目录：

参考资料：