如何在 MySQL 中使用索引

作者选择了多样性在技术基金作为写给捐款计划的一部分接受捐款。

介绍

关系数据库可以用来处理任何大小的数据,包括包含数百万行的大型数据库。结构化查询语言(SQL)提供了一个简洁、简单的方法来根据特定标准在数据库表中找到特定行。

数据库接受各种查询条件的能力使数据库引擎难以预测哪个查询最常见。该引擎必须准备在数据库表中有效地定位行,无论它们的大小如何。

数据库管理员可以使用索引来支持数据库引擎并提高其性能。

在本教程中,您将了解索引是什么,如何创建它们,以及它们是否用于查询数据库。

前提条件

要遵循本指南,您需要运行基于 SQL 的关系数据库管理系统(RDBMS)的计算机。

运行 Ubuntu 20.04 的服务器,具有非 root 用户的管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 所描述的。
MySQL 安装并保护在服务器上,如在 [Ubuntu 20.04 上如何安装 MySQL] 所描述的。

<$>[注] 注: 请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,但索引不是标准SQL语法的一部分,因此如果您在MySQL以外的系统上测试它们,确切的语法或输出可能会有所不同。

您还需要一个数据库,其中有一些表载有样本数据,以便您可以练习使用索引。我们鼓励您通过以下 连接到MySQL和设置样本数据库部分,以了解有关连接到MySQL服务器和创建本指南中的示例中使用的测试数据库的详细信息。

连接到MySQL并设置样本数据库

在本节中,您将连接到MySQL服务器并创建一个样本数据库,以便您可以按照本指南中的示例。

如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:

1[environment local]
2ssh sammy@your_server_ip

然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:

1mysql -u sammy -p

创建一个名为索引的数据库:

1CREATE DATABASE indexes;

如果数据库创建成功,您将收到这样的输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

要选择索引数据库,运行以下使用语句:

1USE indexes;

您将获得以下输出:

1[secondary_label Output]
2Database changed

选择数据库后,您可以创建示例表,此指南中,您将使用想象中的员工数据库存储有关当前员工及其工作设备的详细信息。

表员工将包含数据库中有关员工的简化数据,包含以下列:

此列将成为表的主要密钥,每个值将成为其相应行的唯一标识符。

first_name:此列将包含每个员工的名称,用 varchar 数据类型表示,最多为 50 字符。
last_name:此列将包含每个员工的最后一个名称,使用 varchar 数据类型表示,最多为 50 字符。

使用以下命令创建示例表:

1CREATE TABLE employees (
2    employee_id int,
3    first_name varchar(50),
4    last_name varchar(50),
5    device_serial varchar(15),
6    salary int
7);

如果下列输出打印,则已创建表:

1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)

接下来,通过运行以下INSERT INTO操作来加载员工表中的一些样本数据:

 1INSERT INTO employees VALUES
 2    (1, 'John', 'Smith', 'ABC123', 60000),
 3    (2, 'Jane', 'Doe', 'DEF456', 65000),
 4    (3, 'Bob', 'Johnson', 'GHI789', 70000),
 5    (4, 'Sally', 'Fields', 'JKL012', 75000),
 6    (5, 'Michael', 'Smith', 'MNO345', 80000),
 7    (6, 'Emily', 'Jones', 'PQR678', 85000),
 8    (7, 'David', 'Williams', 'STU901', 90000),
 9    (8, 'Sarah', 'Johnson', 'VWX234', 95000),
10    (9, 'James', 'Brown', 'YZA567', 100000),
11    (10, 'Emma', 'Miller', 'BCD890', 105000),
12    (11, 'William', 'Davis', 'EFG123', 110000),
13    (12, 'Olivia', 'Garcia', 'HIJ456', 115000),
14    (13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
15    (14, 'Isabella', 'Wilson', 'NOP012', 125000),
16    (15, 'Matthew', 'Martinez', 'QRS345', 130000),
17    (16, 'Sophia', 'Anderson', 'TUV678', 135000),
18    (17, 'Daniel', 'Smith', 'WXY901', 140000),
19    (18, 'Mia', 'Thomas', 'ZAB234', 145000),
20    (19, 'Joseph', 'Hernandez', 'CDE567', 150000),
21    (20, 'Abigail', 'Smith', 'FGH890', 155000);

数据库将响应成功消息:

1[secondary_label Output]
2Query OK, 20 rows affected (0.010 sec)
3Records: 20 Duplicates: 0 Warnings: 0

<$>[注] 注: 数据集不够大,可以直接说明索引的性能影响,但是,这个数据集将展示MySQL如何使用索引来限制经过的行数量来执行查询并获得结果。

有了它,你已经准备好跟随其余的指南,并开始在MySQL中使用索引。

引入指数

通常情况下,当您对MySQL数据库执行查询时,数据库必须通过表中的所有行一个接一个,例如,您可能希望查找与Smith匹配的员工姓名或所有工资高于$100000的员工。表中的每一个行都会被检查一个接一个,以验证它是否符合条件。

虽然这种找到匹配行的方法有效,但随着表的大小增加,它可能会变得缓慢和资源密集,因此,这种方法可能不适合需要频繁或快速访问数据的大型表或查询。

要解决大型表和查询的性能问题,可以使用索引。索引是唯一的数据结构,仅存储数据的分类子集,而不是表行。

使用员工表作为一个例子,您可以执行的典型查询之一是以姓名查找员工. 没有任何索引,MySQL将从表中检索每个员工,并验证最后姓名是否匹配查询。

要在书中找到一个名叫约翰·史密斯的人,你首先要转到右边的页面,在那里列出有名称以S开头的人,然后浏览那些名称以Sm开头的人的页面。按照这个逻辑,你可以快速删除许多条目,知道它们不匹配你正在寻找的人。这个过程只工作,因为电话簿中的数据按字母排序,这很少发生在数据库中直接存储的数据中。数据库引擎中的索引为电话簿提供类似的目的,保持数据的字母顺序的参考,从而帮助数据库快速找到所需的行。

使用 MySQL 中的索引具有多种好处. 最常见的是加快 WHERE的条件查询(具有准确的匹配条件和比较),更快地使用 ORDER BY的条款来排序数据,并强制执行值独特性。

然而,使用索引可能会在某些情况下降低数据库峰值性能。索引旨在加速数据检索,并使用与表数据一起存储的额外数据结构实现。这些结构必须在数据库中的每一次更改时保持最新,这可能会减缓 INSERT, UPDATE和 DELETE查询的性能。随着大数据集的频繁变化,对 SELECT查询的更快速度所带来的好处有时可以被写入数据到数据库的查询的显著较慢性能所抵消。

当选择要创建哪个索引时,请考虑执行最频繁且需要最长时间的查询,并根据最能从中受益的查询条件构建索引。

<$>[注] **注:**本指南旨在介绍MySQL中的数据库索引主题,说明常见的应用程序和索引类型。数据库引擎支持许多更复杂的场景,用于使用索引来提高数据库性能,这不在本指南的范围内。

在以下步骤中,您将为各种场景创建不同类型的索引. 您将学习如何验证索引是否在查询中使用。

使用单列索引

单列索引是最常见和最简单的索引类型,可用于优化查询性能. 此类型索引有助于数据库加速基于单列值过滤数据集的查询。

在上一个步骤中创建的示例数据库中,没有索引。在创建索引之前,您将首先测试数据库如何处理员工表中的SELECT查询,当使用WHERE条款仅请求表中的数据子集。

假设您想要找到有10万美元的员工,请执行以下查询:

1SELECT * FROM employees WHERE salary = 100000;

WHERE条款要求准确匹配员工的工资,匹配所请求的值. 在此示例中,数据库将响应如下:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           9 | James      | Brown     | YZA567        | 100000 |
6+-------------+------------+-----------+---------------+--------+
71 row in set (0.000 sec)

<$>[注] **注:**如上面的输出所示,数据库几乎立即响应发送的查询。在数据库中只有少数样本行,使用索引不会明显影响查询性能。

根据查询输出,你无法知道数据库引擎是如何处理在表中找到匹配行的问题的,但是,MySQL提供了了解查询计划的方法,这就是引擎如何执行查询: EXPLAIN语句。

若要访问SELECT查询的查询计划,请执行以下操作:

1EXPLAIN SELECT * FROM employees WHERE salary = 100000;

EXPLAIN命令告诉MySQL运行SELECT查询,但不会返回结果,它会显示有关数据库引擎如何内部执行查询的信息。

执行计划将类似于以下(您的表可能略有不同):

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
3| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
5|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
6+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
71 row in set, 1 warning (0.00 sec)

在此表输出中,列描述了查询执行的许多方面. 根据您的MySQL版本,您的输出可能会包含额外的列,但对于本教程,这里是最重要的信息:

possible_keys列出了MySQL考虑使用的索引。在这种情况下,没有索引(NULL)。
key描述了MySQL在执行查询时决定使用的索引。在这种情况下,没有索引被使用(NULL)。
rows显示了MySQL在返回结果之前必须单独分析的行数。在这里,它是 20,这相当于表中所有可能的行数。这意味着MySQL必须扫描员工表中的每个行以找到返回的单一行数。
Extra显示了查询计划的额外描述性信息。在这个例子中,‘使用在哪里’注释意味着数据库的过滤器直接从表中使用‘W

没有索引,数据库必须扫描20行以获取一个单一的行,如果表中包含数百万行,MySQL将不得不通过它们一个接一个,导致查询性能差。

<$>[注] **注:**新版本的MySQL,在使用EXPLAIN时,在输出中显示1行在集合,1警告,而较旧的MySQL版本和MySQL兼容的数据库通常只会显示1行在集合而不是。警告不是一个问题的迹象。MySQL使用其警告机制来提供进一步扩展的查询计划信息。此类额外信息的使用不在本教程的范围内。您可以在MySQL文档中的 [扩展的EXPLAIN输出格式]页面(https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html)中了解更多有关这种行为的信息。 <$>

您刚刚运行的SELECT查询使用了准确的查询条件,即WHERE salary = 100000。接下来,让我们检查数据库是否会在比较条件下表现类似。

1SELECT * FROM employees WHERE salary < 70000;

这一次,数据库返回了约翰·史密斯和Jane Doe的两行:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6|           2 | Jane       | Doe       | DEF456        |  65000 |
7+-------------+------------+-----------+---------------+--------+
88 rows in set (0.000 sec)

但是,当您使用EXPLAIN来理解查询执行时,如下所示:

1EXPLAIN SELECT * FROM employees WHERE salary < 70000;

您会注意到表几乎与上一个查询相同:

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
3| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
5|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    33.33 | Using where |
6+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
71 row in set, 1 warning (0.00 sec)

与之前的查询一样,MySQL扫描了表中的所有20行,以便通过查询上的WHERE条款找到您请求的行。

要解决这一问题,您可以为工资列创建索引,该索引会告诉MySQL保持额外的高度优化的数据结构,特别是从员工表中获取的工资数据。

1CREATE INDEX salary ON employees(salary);

CREATE INDEX语法要求:

索引名称,在这种情况下是工资。该名称必须在单个表中是唯一的,但可以在相同数据库中的不同表中重复。
索引创建的表名称在这种情况下,它是员工。索引创建的列表。

注意:根据您的MySQL用户权限,您在执行CREATE INDEX命令时可能会收到错误:ERROR 1142 (42000):INDEX命令被拒绝给用户user@host表员工`。

1GRANT INDEX on *.* TO 'sammy'@'localhost';
2FLUSH PRIVILEGES;

更新用户权限后,以 root 登录并以用户身份重新登录,然后重启CREATE INDEX语句。

数据库将确认该索引已成功创建:

1[secondary_label Output]
2Query OK, 0 rows affected (0.024 sec)
3Records: 0 Duplicates: 0 Warnings: 0

有了索引,尝试重复之前的查询,检查是否有任何变化。

1SELECT * FROM employees WHERE salary = 100000;

结果将与以前相同,只有詹姆斯·布朗返回:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           9 | James      | Brown     | YZA567        | 100000 |
6+-------------+------------+-----------+---------------+--------+
71 row in set (0.000 sec)

但是,要求MySQL解释它是如何接近查询的,会显示一些与以前的差异。

1EXPLAIN SELECT * FROM employees WHERE salary = 100000;

这一次,输出将打印如下:

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
3| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
4+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
5|  1 | SIMPLE      | employees | NULL       | ref  | salary        | salary | 5       | const |    1 |   100.00 | NULL  |
6+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
71 row in set, 1 warning (0.00 sec)

MySQL声明,从possible_keys中显示的一个可能的密钥中,它决定使用名为salary的密钥,这是你创建的索引。行列现在显示1而不是20。由于它使用了索引,数据库避免扫描数据库中的所有行,并可以立即返回单个请求的行。

使用一个小样本数据集,使用索引的影响并不明显,但数据库需要更少的工作来获取结果,而这种变化对更大的数据集的影响将是显著的。

尝试重新启动第二个查询,查找工资低于70000的员工,以检查该索引是否也在那里使用。

执行以下查询:

1SELECT * FROM employees WHERE salary < 70000;

John Smith和Jane Doe的相同两行将返回:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6|           2 | Jane       | Doe       | DEF456        |  65000 |
7+-------------+------------+-----------+---------------+--------+
88 rows in set (0.000 sec)

但是,当您使用解释时,如下:

1EXPLAIN SELECT * FROM employees WHERE salary < 70000;

该表将与之前执行的相同查询不同:

1[secondary_label Output]
2+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
3| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
4+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
5|  1 | SIMPLE      | employees | NULL       | range | salary        | salary | 5       | NULL |    2 |   100.00 | Using index condition |
6+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
71 row in set, 1 warning (0.00 sec)

关键列告诉你MySQL使用索引来执行查询.在行中,只分析了两行以返回结果.这次,额外列说使用索引条件,这意味着在这种情况下,MySQL通过使用索引过滤,然后只使用主表来检索已经匹配的行。

<$>[注] 注: 有时,即使有一个索引存在并可能被使用,MySQL会反对它。

1EXPLAIN SELECT * FROM employees WHERE salary < 140000;

执行计划将读成如下:

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
3| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
5|  1 | SIMPLE      | employees | NULL       | ALL  | salary        | NULL | NULL    | NULL |   20 |    80.00 | Using where |
6+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
71 row in set, 1 warning (0.00 sec)

即使在possible_keys中列出工资,空的关键列(读NULL)意味着MySQL决定不使用索引,这可以通过20扫描的行来确认。数据库查询计划器对每个查询进行分析,以确定最快的执行路径。

类似地,在额外列中的注释,例如使用索引条件或使用在哪里描述了数据库引擎如何更详细地执行查询。根据背景,数据库可能会选择另一种方法来执行查询,并且您可能缺少使用索引条件注释的输出,或者另一个注释存在。

在本节中,您创建并使用单列索引,以提高基于对单列进行过滤的SELECT查询的性能。

使用单一索引防止数据重复

正如您在上一节中所探讨的那样,指数的一个常见用途是通过帮助数据库引擎减少工作来更有效地获取数据,以实现相同的结果。

避免重复值往往是必要的,以保证数据的完整性,从逻辑或技术的角度来看,例如,不应该有两个不同的人使用相同的社保号码,或在线系统不应该允许多个用户与相同的用户名或电子邮件地址注册。

在本指南中的员工表示例中,分配设备的序列号是一个不应该包含重复的字段。

尝试插入已使用的设备序列号的其他员工:

1INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

数据库将强制并插入行,通知您成功:

1[secondary_label Output]
2Query OK, 1 row affected (0.009 sec)

但是,如果您现在使用ABCD123计算机来查询员工的数据库,则如下:

1SELECT * FROM employees WHERE device_serial = 'ABC123';

你会得到两个不同的人作为结果:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6|          21 | Sammy      | Smith     | ABC123        |  65000 |
7+-------------+------------+-----------+---------------+--------+
82 rows in set (0.000 sec)

这不是一个预期的行为,以保持员工数据库的有效性. 让我们通过删除新创建的行来扭转这个变化:

1DELETE FROM employees WHERE employee_id = 21;

您可以通过重新点击之前的选择查询来确认这一点:

1SELECT * FROM employees WHERE device_serial = 'ABC123';

再一次,只有约翰·史密斯使用有序号ABC123的设备:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6+-------------+------------+-----------+---------------+--------+
71 row in set (0.000 sec)

为了保护数据库免受此类错误,您可以在device_serial列上创建一个独特的索引。

要做到这一点,执行:

1CREATE UNIQUE INDEX device_serial ON employees(device_serial);

在创建索引时添加UNIQUE关键字,指示数据库确保device_serial列中的值不能重复。

数据库将确认索引创建:

1[secondary_label Output]
2Query OK, 0 rows affected (0.021 sec)
3Records: 0 Duplicates: 0 Warnings: 0

现在,请检查是否仍然可以将重复的条目添加到表中. 尝试再次运行以前成功的输入查询:

1INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

此时,将显示错误消息:

1[secondary_label Output]
2ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'

您可以通过再次使用选择查询来验证新行没有被添加到表中:

1SELECT * FROM employees WHERE device_serial = 'ABC123';

现在只返回一行:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6+-------------+------------+-----------+---------------+--------+
71 row in set (0.000 sec)

单一索引除了防止重复输入之外,也是一种功能齐全的索引,可加速查询,数据库引擎会像上一步一样使用单一索引。

1EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';

执行计划将类似于以下(您的表可能略有不同):

1[secondary_label Output]
2+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
3| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
4+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
5|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
6+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
71 row in set, 1 warning (0.00 sec)

device_serial索引显示在possible_keys和key列中,确认该索引在执行查询时使用。

您已经使用了独特的索引来防止数据库中的数据重复使用,在下一节中,您将使用超过一个列的索引。

在多个列上使用索引

到目前为止,您在以前的部分中创建的所有索引都是使用单一列名来定义的,这些索引与所选列的值有关。大多数数据库系统都支持超过一个列的索引。

通常使用的需要优化性能的查询通常在WHERE过滤条款中使用多个条件。

1SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

首先用索引优化此查询的想法可能是创建两个单独的索引,一个在上一个_名称列上,另一个在上一个_名称列上。

如果你以这种方式创建两个单独的索引,MySQL将知道如何找到所有名为史密斯的员工,它也将知道如何找到所有名为约翰的员工,但是,它不会知道如何找到名为约翰史密斯的人。

为了说明两个单独的索引的问题,想象两个单独的电话簿,一个按姓名排列,另一个按姓名排列。

使用以姓名排序的电话簿查找所有名为史密斯的人,忽略第二个电话簿,并手动穿过所有史密斯的人一个接一个,直到你找到约翰·史密斯。
相反:使用以姓名排序的电话簿查找所有名为约翰的人,忽略第二个电话簿,并手动穿过所有约翰的人一个接一个,直到你找到约翰·史密斯。

这些方法都不是理想的,而MySQL在处理多个分离索引和请求多个过滤条件的查询时有类似的选择。

另一种方法是使用索引,而不是一个单一的列,但许多列,你可以想象这是一个电话簿放置在另一个电话簿:首先你寻找姓氏史密斯,引导你到第二个目录,所有的人名为史密斯,按字母组织的姓氏,你可以用它快速找到约翰。

<$>[注] 注: 通常会说MySQL可以在查询中使用的每个表中只使用一个索引。这并不总是如此,因为MySQL支持(https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html)在运行查询时共同使用多个索引。然而,在构建索引时,这种限制是一个很好的拇指规则。MySQL可能会决定不使用多个索引;即使在许多场景中,它们也不会为目的和专用索引服务。

在MySQL中,要在员工表中创建姓氏和姓氏的多列索引,执行:

1CREATE INDEX names ON employees(last_name, first_name);

在这种情况下,CREATE INDEX 语句略有不同,现在在表名称(员工)后面的窗格中列出两个列:last_name 然后是first_name。这在两个列上创建了一个多列索引。索引定义中列出的列顺序很重要,正如你会在一瞬间看到的。

数据库将显示以下消息,确认已成功创建索引:

1[secondary_label Output]
2Query OK, 0 rows affected (0.024 sec)
3Records: 0 Duplicates: 0 Warnings: 0

现在,尝试发出SELECT查询以找到与John匹配的第一名和Smith匹配的姓氏的行:

1SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

结果是一个单行与一个名为约翰·史密斯的员工:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6+-------------+------------+-----------+---------------+--------+
71 row in set (0.000 sec)

现在使用EXPLAIN查询来检查索引是否被使用:

1EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

执行计划将类似于以下(您的表可能略有不同):

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
3| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
4+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
5|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
6+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
71 row in set, 1 warning (0.00 sec)

数据库使用了名称索引。一个单行被扫描,所以表没有超过必要的时间。额外列说使用索引条件,这意味着MySQL只能使用索引完成过滤。

使用跨这两个列的多列索引对第一名和最后名称进行过滤,可为数据库提供直接、快速的方法来找到所需的结果。

若在两个列上定义了索引,如果尝试查找所有名为Smith的员工,但不对第一名进行过滤,会发生什么?运行修改的查询:

1SELECT * FROM employees WHERE last_name = 'Smith';

输出将返回如下:

 1[secondary_label Output]
 2+-------------+------------+-----------+---------------+--------+
 3| employee_id | first_name | last_name | device_serial | salary |
 4+-------------+------------+-----------+---------------+--------+
 5|          20 | Abigail    | Smith     | FGH890        | 155000 |
 6|          17 | Daniel     | Smith     | WXY901        | 140000 |
 7|           1 | John       | Smith     | ABC123        |  60000 |
 8|           5 | Michael    | Smith     | MNO345        |  80000 |
 9+-------------+------------+-----------+---------------+--------+
104 rows in set (0.000 sec)

四名员工姓名史密斯。

再次访问查询执行计划:

1EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

执行计划将类似于以下(您的表可能略有不同):

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
3| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
4+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
5|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 203     | const |    4 |   100.00 | NULL  |
6+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
71 row in set, 1 warning (0.01 sec)

然而,执行计划表显示数据库使用多列索引名称来执行此查询,仅扫描了4行 - 返回的确切数字。

在以前的查询中,用于过滤结果的列(‘last_name’)首先在‘CREATE INDEX’声明中传递。现在,您将通过‘first_name’过滤‘employees’表,这是该多列索引列表中的第二列。

1SELECT * FROM employees WHERE first_name = 'John';

输出将如下返回:

1[secondary_label Output]
2+-------------+------------+-----------+---------------+--------+
3| employee_id | first_name | last_name | device_serial | salary |
4+-------------+------------+-----------+---------------+--------+
5|           1 | John       | Smith     | ABC123        |  60000 |
6+-------------+------------+-----------+---------------+--------+
71 row in set (0.000 sec)

访问查询执行计划:

1EXPLAIN SELECT * FROM employees WHERE first_name = 'John';

输出将如下返回:

1[secondary_label Output]
2+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
3| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
5|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
6+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
71 row in set, 1 warning (0.00 sec)

再次,返回的结果包含一个员工,但这次没有使用索引. 数据库扫描了整个表,说明在额外列中的使用在哪里注释,以及20扫描的行。

在这种情况下,数据库没有使用索引,因为在首次创建索引时将CREATE INDEX陈述传递到列的顺序:last_name, first_name。

通过在多个列上创建的索引,数据库可以使用索引来加速涉及所有索引列的查询,或增加所有索引列的左前缀。例如,包含列 (a,b,c)的多列索引可以用于加速涉及所有三个列的查询,以及仅涉及前两个列的查询,甚至仅涉及第一个列的查询。

通过仔细选择索引中包含的列和它们的顺序,可以使用单个多列索引来加速在同一表中的各种查询。

在本节中,您使用了多列索引,并在指定此类索引时了解了列顺序,在下一节中,您将学习如何管理现有索引。

列出和删除现有索引

在之前的部分中,您创建了新的索引. 由于索引有名称,并在特定表中定义,您也可以列出并在需要时操纵它们。

若要列出您在本教程中为员工表创建的所有索引,请执行以下语句:

1SHOW INDEXES FROM employees;

结果将类似于以下:

 1[secondary_label Output]
 2+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
 3| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
 4+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
 5| employees |          0 | device_serial |            1 | device_serial | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
 6| employees |          1 | salary        |            1 | salary        | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
 7| employees |          1 | names         |            1 | last_name     | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
 8| employees |          1 | names         |            2 | first_name    | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
 9+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
104 rows in set (0.01 sec)

根据您的MySQL版本,您的输出可能会略有不同,但它将包括所有索引,包括它们的名称,用于定义索引的列,其独特性信息和索引定义的其他广泛细节。

若要删除现有索引,您可以使用DROP INDEX SQL 语句. 假设您不想再强制执行device_serial列的独特性。

1DROP INDEX device_serial ON employees;

「device_serial」是索引名称,而「employees」是索引被定义的表格。

1[secondary_label Output]
2Query OK, 0 rows affected (0.018 sec)
3Records: 0 Duplicates: 0 Warnings: 0

有时,典型查询的模式会随着时间的推移而改变,或者新查询类型会变得突出,然后,您可能需要重新评估已使用的索引,创建新的索引,或删除未使用的索引,以避免通过保持它们的更新来降低数据库性能。

使用 CREATE INDEX 和 DROP INDEX 命令,您可以在现有数据库中管理索引,并按照最佳实践创建索引,当它们变得必要和有益时。

结论

通过遵循本指南,您了解了索引是什么,以及如何在MySQL中使用最常见的类型来通过有条件的SELECT查询来加速数据检索。

您可以使用索引来根据最常执行的查询类型来塑造数据库的性能,为常见用例找到正确的阅读和写作性能平衡。本教程仅涵盖了用于此目的使用索引的基本知识。

如果您想了解更多有关 SQL 语言的不同概念并使用它的工作,我们鼓励您查看如何使用 SQL 系列中的其他指南。