平凡

理解MySQL的索引

2018/09/01 Share

在Web应用中,如果出现性能问题,原因极有可能是数据库索引没有设置或者没有设置好。这篇文章希望能帮助大家更好的理解数据库索引。

什么是索引

每当你运行一句where查询,数据库都会遍历一遍该表,找到符合条件的行。
例如查询班里名叫张三的性别:

select sex from class where name="张三"

数据库会把该班级表中所有学生都遍历一遍。
当表的规模扩大后,每一次查询都要遍历更多的数据。

索引就是为了解决这个问题,它把某列的数据取出来,按顺序(如字母顺序)存储到别的位置。如果该列数据是浮点数,就会按值的大小存储,如果是日期类型,那就按日期大小存储。

建立索引以后,再查询该列的数据便不需要遍历整个表,而是以低时间复杂度(如logn)在索引中查到某条数据,然后直接定位到该列原始数据(时间复杂度O(1))。

索引的数据结构

我们知道,数据库查询是数据库的最主要功能之一,我们都希望查询数据的速度能尽可能的快。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的。

好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序排列,而二叉树查找只能应用于二叉查找树

但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织)。所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
看一个例子:

图中展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在的复杂度内获取到相应数据。

虽然这是一个货真价实的索引,但是实际的数据库系统一般采用B树(我的心里没有B树)及其变种实现。

建立索引

许多Web框架已经集成了建立索引的操作,例如laravel中,可以使用migration语句直接建立索引(https://laravel.com/docs/5.6/migrations)。但了解MySQL原生语句怎样建立索引也是很有必要的。

例如,假如有个学生非常多(超过100,000人)的学校,我们建立了一张表来存储学生的数据,这个表的前面几条数据如下:

建立这个表的SQL语句是这样写的:

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

现在我们要对该表进行查询,主要有三个需求:

  • 根据id查找学生
  • 根据last_name查找学生
  • 查询某班上的所有学生

我们发现一个规律,这些查询都是在根据列在查询(第一个是学生id,第二个是last_name, 第三个是class)。所以,我们要确保每列都有自己的索引

第一种查询SELECT * FROM students WHERE id = 1,这种查询很特别,它根据primary key——主键来查询。我们都知道了要查哪一行(id=1,所以是第一行),直接跳到该行就可以了,不再需要进行建立索引操作来优化。理想的情况是每种查询都能像这样(但实际情况肯定并非如此)。每张表都应该像这样建立一个主键。


第二个查询SELECT * FROM students WHERE last_name = 'Smith'。由于last_name这一列没有建立索引,所以会遍历整个表来找到目标。现在我们要在该表创建索引:

CREATE INDEX by_last_name ON students(`last_name`);

这样,该表就有了一个名叫by_last_name的索引,让我们以后在查询last_name这一列时,速度能大大提升。

和它一样,也可以在class这一列上建立索引,加速我们查询某个班级的学生的操作。建立索引的语句可以这样写:

CREATE INDEX by_class ON students(`class`);

同时查询多个列

在数据库查询中,经常要同时查询多个列,例如我们知道某学生叫Smith和它的班级6A,想要取出它的所有信息,查询语句应该这样写:

SELECT * FROM students WHERE class = `6A` AND last_name = 'Smith'

MYSQL通常只会使用一种索引来查询,所以我们建立的两个索引就不会同时生效。但我们也不必再建立其他索引了。

数据库引擎会发现我们在class上建立了索引,而且每个class大约只有20个学生,它会使用by_class这个索引来搜索班里的20个学生,再查找这20个学生的last_name。查找20条数据对于MySQL不是什么难事(和100000+数据比起来),而且我们避免了再建立其他索引对空间的浪费。


同时对多个表建立索引

上个例子中,我们假设每个班级只有20名学生,但如果有些班级人数非常多,超过了100人呢?
这种情况下,我们应该改进一下by_class_index索引,让它同时支持by_last_name, 虽然使用了额外的空间,但使得同时查询这两列的速度大大加快。

DROP INDEX by_class ON students;
CREATE INDEX by_class_and_last_name ON students(class, last_name);

为什么删除by_class这种索引呢?因为我们新建立的索引既可以同时查询classlast_name,又可以只根据class查询,使得by_class这个索引显得多余。


Join查询

实际应用中,极有可能出现Join查询,例如,学生可能有张成绩表grades,它可能长这样:

想查询一个某个学生id对应的所有成绩,查询语句如下:

SELECT * FROM grades WHERE student_id = 1

如果想查询某个班里学生的所有成绩,就要使用Join查询:

SELECT * from students WHERE class = '6A' JOIN grades on grades.student_id = students.id

上面这两种查询都使用了student_id这个外键。通常要为外键设立一个索引:

CREATE INDEX by_student_id ON grades(student_id);

如何选择你要建立索引的列

除了分析你的查询语句,你可以过一遍数据库中的表,分析哪些字段可能会被查询,然后建立它们的索引。下面是一些经验,可以直接在这些列上建立索引:

  • 与其他表进行Join操作的列(这些列通常以xxx_id命名)
  • usernameemail等字段,当用户登录的时候每次都会查询这些列。

小结

如果你发现你的select操作非常的慢,极有可能是你没有为你的表建立索引。看一下哪一列被查询了,然后在该列建立索引, 使用这种语法:

CREATE INDEX by_student_id ON grades(student_id);

把注意力放到数据比较多的表上进行优化。

索引的缺点

上面一直在扯索引的好处,但索引很容易被滥用。它是一把双刃剑。

索引有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATEDELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。


参考及翻译自:
https://atech.blog/viaduct/mysql-indexes-primer
http://blog.codinglabs.org/articles/theory-of-mysql-index.html

发表日期: September 1st 2018

版权声明: 本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. 什么是索引
  2. 2. 索引的数据结构
  3. 3. 建立索引
  4. 4. 同时查询多个列
  5. 5. 同时对多个表建立索引
  6. 6. Join查询
  7. 7. 如何选择你要建立索引的列
  8. 8. 小结
  9. 9. 索引的缺点