程序员的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(覆盖索引):如果所需要的数据直接在索引里就可以找到,而不需要再去数据所在的聚簇索引查询一次,那么这次查询 就叫做实现了索引覆盖。

系列目录:


参考资料:


更多文章
  • 工作一年的总结
  • MongoDB 的一些坑
  • Python 的继承
  • Python的yield关键字有什么作用?
  • 借助coroutine用同步的语法写异步
  • Python3函数参数中的星号
  • 使用Git Hooks
  • Token Bucket 算法
  • nginx配置笔记
  • 阅读Flask源码
  • 尤克里里
  • 学习使用Bootstrap4的栅格系统
  • 利用Github的WebHook完成自动部署
  • 使用Tornado和rst来写博客
  • Haskell do notation