2023年MySQL索引完全指南：提高查询性能与优化数据库

2 年 ago

文, 翔

9 minutes

作者选择将 Diversity in Tech Fund 作为 Write for Donations 计划的一部分收到捐款的受益方。

介绍

关系型数据库可以用于处理任意大小的数据，包括拥有数百万行的大型数据库。结构化查询语言（SQL）提供了一种简洁而直接的方式，基于特定条件在数据库表中找到特定的行。随着数据库变得越来越大，在其中定位特定行变得越来越困难，就像是在大海捞针。

数据库具备接受广泛查询条件的能力，这让数据库引擎难以预测哪些查询最常见。无论数据库表的大小如何，引擎必须能够高效地定位行。然而，随着数据量增加，搜索性能可能会受到影响。数据集越大，数据库引擎越难快速找到与查询匹配的文档。

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

在这个教程中，您将学习什么是索引，如何创建索引以及索引是否用于查询数据库。

先决条件

要按照此指南操作，你需要一台运行基于SQL的关系数据库管理系统（RDBMS）的计算机。本指南中的指令和示例是在以下环境中验证的：

一台运行Ubuntu 20.04的服务器，配置了具有管理权限的非root用户和通过UFW配置的防火墙，如我们的Ubuntu 20.04初始服务器设置指南中所述。
在服务器上安装并保护MySQL，如如何在Ubuntu 20.04上安装MySQL中所述。本指南使用通过步骤3中描述的流程创建的非root MySQL用户进行了验证。
基本熟悉执行SELECT查询以从数据库中检索数据，如我们的如何在SQL中从表中选择行指南中所述。

注意：

请注意许多RDBMS使用了它们自己独特的SQL实现。虽然本教程中列出的命令适用于大多数RDBMS，但索引不是标准SQL语法的一部分，所以如果您在除MySQL之外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个含有一些装载有样本数据的数据库，以便你能够练习使用索引。我们鼓励你参阅下面的连接到MySQL和设置一个示例数据库部分，了解连接到MySQL服务器和创建本指南中所有示例中使用的测试数据库的详细信息。

连接到MySQL并设置一个示例数据库

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

如果您的SQL数据库系统运行在远程服务器上，从您的本地机器通过SSH登录到您的服务器。

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将sammy替换为您的MySQL用户账户的名称。

mysql -u sammy -p

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

CREATE DATABASE indexes;

如果数据库成功创建，您将收到如下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择索引数据库，请运行以下USE语句：

USE indexes;

您将收到以下输出结果：

OutputDatabase changed

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

员工表将包含有关数据库中员工的简化数据。它将包括以下列：

employee_id: 此列保存员工标识符，由int数据类型表示。此列将成为表的主键，每个值将成为其相应行的唯一标识符。
first_name: 此列保存每个员工的名字，使用varchar数据类型表示，最多50个字符。
last_name: 此列保存每个员工的姓氏，使用varchar数据类型表示，最多50个字符。
device_serial: 此列保存分配给员工的计算机序列号，使用varchar数据类型表示，最多15个字符。
salary: 此列保存每个员工的薪水，使用存储数值数据的int数据类型表示。

用以下命令创建示例表：

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

如果以下输出打印出来，那么表示表被创建了：

OutputQuery OK, 0 rows affected (0.00 sec)

接下来，通过执行以下的INSERT INTO操作，向员工表中加载一些示例数据。

这是文章《如何在MySQL中使用索引》的第2部分（共9部分）。

以下是一个向employees表中插入多条记录的MySQL语句示例：

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

执行上述语句后，数据库将返回操作成功的消息。

输出查询正常，20行受影响（0.010秒）
记录：20  重复：0  警告：0

注意：

数据集的规模还不足以直接说明索引对性能的影响。然而，这个数据集将展示MySQL如何利用索引来限制遍历的行数以执行查询并获取结果。

有了这些，你就可以继续阅读本指南的其余部分，并开始在MySQL中使用索引了。

索引简介

通常情况下，在对MySQL数据库执行查询时，数据库必须逐行检查整个表。例如，您可能希望搜索与”Smith”匹配的员工姓氏，或者希望查询所有薪资高于$100000的员工。数据库将逐行检查表中的每一行，以验证其是否符合条件。如果符合条件，该行将被添加到返回结果的列表中。如果不符合条件，MySQL将继续扫描后续行，直到浏览整个表为止。

尽管这种找到匹配行的方法很有效，但随着表的大小增长，它可能会变得缓慢且消耗资源。因此，对于大型表或需要频繁或快速数据访问的查询，这种方法可能不适用。

为了解决大表和查询的性能问题，可以使用索引。索引是独特的数据结构，只存储数据的排序子集，与表行分开。它们允许数据库引擎在查找特定字段或字段集的值或排序时更快速、高效地工作。

以员工表为例，您可以执行一种典型的查询，即通过姓氏找到员工。如果没有任何索引，MySQL将从表中检索每位员工并验证姓氏是否与查询匹配。但是，使用索引时，MySQL将保留姓氏的独立列表，其中仅包含指向主表中给定员工行的指针。然后，它将使用该索引检索结果，而无需扫描整个表。

在中文中，您可以将索引类比为电话簿。要在电话簿中查找名为约翰·史密斯的人，您首先翻到以S开头的姓名列表所在的正确页面，然后浏览以Sm开头的页面。按照这个逻辑，您可以迅速排除许多条目，因为它们与您寻找的人不匹配。该过程之所以有效，是因为电话簿中的数据按字母顺序排序，而数据库中直接存储的数据很少按这种方式排列。数据库引擎中的索引发挥类似于电话簿的作用，它保持着按字母顺序排列的数据引用，从而帮助数据库快速找到所需的行。

在MySQL中使用索引有多个好处。其中最常见的是加快WHERE条件查询（使用精确匹配条件和比较），更快地排序使用ORDER BY子句的数据，并强制值的唯一性。

然而，在某些情况下，使用索引可能会降低数据库的峰值性能。索引旨在加快数据检索速度，并使用附加的数据结构来实现，这些结构与表数据一同存储。这些结构必须随着数据库中每次的修改保持更新，这可能会降低INSERT、UPDATE和DELETE查询的性能。对于经常更改的大型数据集来说，通过改善SELECT查询速度获得的好处，有时可能会被写入数据库的查询性能明显变慢所抵消。

只有在有明确需求的情况下才建议创建索引，比如当应用程序的性能开始下降时。在选择要创建的索引时，考虑最经常执行并花费最长时间的查询，并根据这些查询条件来建立索引，以获得最大的收益。

注意：

本指南旨在介绍MySQL中的数据库索引主题，以说明常见的应用和索引类型。数据库引擎支持更多复杂的索引使用场景，以提高数据库性能，但这超出了本指南的范围。我们鼓励您查阅官方的MySQL索引文档，以获取数据库功能的更完整描述。

在接下来的步骤中，你将为不同场景创建不同类型的索引。你将学习如何验证索引在查询中是否被使用。最后，你将学会在必要时删除索引。

使用单列索引

单列索引是一种最常见且直接的索引类型，您可以使用它来优化查询性能。这种类型的索引帮助数据库加快根据单列值进行数据集过滤的查询速度。在单列上创建的索引可以加速许多条件查询，包括使用=运算符进行精确匹配和使用>或<运算符进行比较。

在你之前创建的示例数据库中，没有索引。在创建索引之前，你首先会测试数据库在使用WHERE子句仅请求表中的一部分数据时如何处理SELECT查询。

假设您想要找到薪水恰好为100000美元的员工。执行以下查询：

SELECT * FROM employees WHERE salary = 100000;

在这个例子中，WHERE子句要求员工的工资与所请求的值完全匹配。数据库将如下回应：

输出+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

注意：

如上所示的输出结果显示，数据库几乎即时地对发出的查询做出了响应。在数据库中只有少量的样本行时，使用索引对查询性能几乎没有明显影响。然而，在大型数据集的情况下，执行查询后数据库报告的查询执行时间将会有明显的变化。

从查询输出来看，你无法知道数据库引擎在表中查找匹配行的处理方式。然而，MySQL提供了一种获取查询计划（即引擎执行查询的方式）的方法：EXPLAIN语句。

要访问SELECT查询的查询计划，请执行以下操作：

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

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

执行计划将类似于以下内容（您的表可能略有不同）：

输出+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在这个表格输出中，列描述了查询执行的许多方面。根据你的MySQL版本，你的输出可能会包含额外的列，但是在这个教程中，以下是最重要的信息：

possible_keys 列出了MySQL考虑使用的索引。在这种情况下，没有任何索引（NULL）。
key 描述了MySQL在执行查询时决定使用的索引。在这种情况下，没有使用任何索引（NULL）。
rows 显示MySQL在返回结果前必须单独分析的行数。这里是20，对应于表中所有可能的行数。这意味着MySQL必须扫描employees表中的每一行来找到返回的那一行。
Extra 显示查询计划的额外描述信息。在本例中，Using where注释表示数据库使用WHERE语句直接从表中过滤结果。

由于没有设置索引，数据库需要扫描20行才能检索到一行数据。如果表中包含数百万行，则MySQL将会逐行扫描它们，导致查询性能差。

注意

较新的MySQL版本在使用EXPLAIN时，在输出中显示1行结果，1个警告，而较旧的MySQL版本和兼容的MySQL数据库通常只会显示1行结果。警告不是问题的标志。MySQL使用其警告机制来提供有关查询计划的进一步扩展信息。这些附加信息的使用超出了本教程的范围。您可以在MySQL文档中的扩展EXPLAIN输出格式页面了解更多相关内容。

你刚刚运行的SELECT查询使用了确切的查询条件，即WHERE salary = 100000。接下来，我们来测试一下如果使用比较条件，数据库是否会有类似的表现。尝试检索薪水低于70000的员工。

SELECT * FROM employees WHERE salary < 70000;

这次数据库返回了两行数据，分别是John Smith和Jane Doe。

Output+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

然而，当您使用EXPLAIN来理解查询执行的方式如下：

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

你会注意到这个表与之前的查询几乎完全相同。

Output+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

与之前的查询一样，MySQL扫描了表中的所有20行，通过查询中的WHERE子句找到了您请求的行。尽管返回的行数相对于表中所有行数来说很少，但数据库引擎仍然需要执行大量的工作来找到它们。

要解决这个问题，你可以为薪资列创建一个索引，它将告诉MySQL维护一个额外的、经过高度优化的数据结构，特别适用于员工表中的薪资数据。要执行此操作，请执行以下查询：

CREATE INDEX salary ON employees(salary);

创建索引语句的语法需要：

索引名称，在本例中是salary。名称在单个表中必须是唯一的，但可以在同一数据库的不同表中重复。
为其创建索引的表名。在本例中是employees。
为其创建索引的列列表。这里，您使用名为salary的单个列来构建索引。

注意

根据您的MySQL用户权限，执行CREATE INDEX命令时可能会收到错误提示：ERROR 1142 (42000): INDEX命令被拒绝对用户’user’@’host’的’table ’employees’。为了为您的用户授予INDEX权限，请作为root用户登录MySQL并执行以下命令，根据需要替换MySQL用户名和主机名：
GRANT INDEX on *.* TO ‘sammy’@’localhost’;
FLUSH PRIVILEGES;

更新用户权限后，以root用户身份退出登录，并将身份切换回用户，并重新运行CREATE INDEX语句。

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

OutputQuery OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

有了索引之后，尝试重复之前的查询以检查是否有任何变化。首先检索工资正好为100,000的单个员工。

SELECT * FROM employees WHERE salary = 100000;

结果将和之前一样，只返回James Brown。

Output

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 行结果集 (0.000 秒)

不过，要求MySQL解释它如何处理查询将会显示与之前有些不同。按照以下方式执行EXPLAIN查询：

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

这一次，输出将会像这样打印出来：

输出+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | salary        | salary | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 行结果集, 1 个警告 (0.00 秒)

MySQL宣布从可能的键中选择了一个名为salary的键进行使用，这也就是你创建的索引。现在，行的列显示为1而不是20。由于使用了索引，数据库避免了扫描所有行的操作并立即返回了所需的单行数据。现在，额外的列中不再提及使用WHERE，因为不需要迭代主表并检查每一行是否符合查询条件来执行查询。

在一个小样本数据集中，使用索引的影响并不是很明显。但是数据库检索结果所需的工作量要少得多，这种改变对于一个更大的数据集来说将会产生明显的影响。

尝试重新运行第二个查询，检索工资低于70000的员工，以检查索引是否也会在那里使用。

执行下面的查询语句。

SELECT * FROM employees WHERE salary < 70000;

约翰·史密斯和简·多将返回相同的两行数据。

输出+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 行结果集 (0.000 秒)

然而，当您使用以下方法进行解释时：

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

这次执行同一查询的结果将与之前的结果不同。

输出+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | salary        | salary | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 行结果集, 1 个警告 (0.00 秒)

主键列显示MySQL使用索引进行查询。在行的方面，仅分析两行以返回结果。这次，附加列显示使用索引条件，这意味着在这种特殊情况下，MySQL通过使用索引进行过滤，然后仅使用主表检索已匹配的行。

注意

注意：有时候，即使存在索引可以使用，MySQL也会决定不使用它。例如，如果执行以下查询：

EXPLAIN SELECT * FROM employees WHERE salary < 140000;

执行计划如下所示：

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | salary        | NULL | NULL    | NULL |   20 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

尽管possible_keys列表中包含了salary，但空的key列（值为NULL）表示MySQL决定不使用索引，这可以通过扫描了20行来确认。数据库查询规划器会分析每个查询以确定最快的执行路径。如果访问索引的成本超过了使用索引的好处（例如，如果查询返回了原始表数据的很大一部分），数据库可能决定进行完整表扫描更快。

类似地，Extra列中的注释（如Using index condition或Using where）更详细地描述了数据库引擎如何执行查询。根据上下文，数据库可能选择另一种执行查询的方式，可能会缺少Using index condition注释，或者出现其他注释。这并不意味着索引没有被正确使用，而只是数据库决定以更高效的方式访问行。

在这一部分中，您创建并使用单列索引来提高对依赖于对单个列进行筛选的SELECT查询的性能。在下一节中，您将探讨索引如何用于确保给定列中的值的唯一性。

使用唯一索引来防止数据重复

正如您在上一节中所探讨的，索引的一个常见用途是通过帮助数据库引擎减少工作量来更有效地检索数据。另一个目的是确保在定义索引的表部分中的数据不会重复。这就是唯一索引的作用。

避免重复数值通常是为了保证数据完整性而必要的，无论是从逻辑还是技术的角度来看。例如，不应该有两个不同的人使用相同的社会安全号码，在线系统也不应该允许多个用户使用相同的用户名或电子邮件地址进行注册。

在这个指南中的员工表示例中，分配设备的序列号是一个不应该存在重复的字段。如果存在重复，这将意味着两个员工被分配了相同的电脑。然而，在这一点上，你可以很容易地插入具有重复序列号的新员工。

请尝试插入另一名员工，其设备序列号已经在使用中。

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

数据库将依要求插入行，并通知您插入成功。

OutputQuery OK, 1 row affected (0.009 sec)

然而，如果你现在使用ABC123电脑来查询员工数据库：

mysql> SELECT * FROM employees WHERE device_serial = 'ABC123';

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

Output+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|          21 | Sammy      | Smith     | ABC123        |  65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)

这是一个使员工数据库保持有效的预期行为。让我们通过删除刚创建的行来撤销这个更改。

mysql> DELETE FROM employees WHERE employee_id = 21;

您可以通过重新运行上一个SELECT查询来确认。

mysql> SELECT * FROM employees WHERE device_serial = 'ABC123';

再次，只有约翰·史密斯使用带有序列号ABC123的设备。

Output+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

为了防止这种错误发生，可以在device_serial列上创建一个唯一索引来保护数据库。

要这样做，请执行以下操作：

mysql> CREATE UNIQUE INDEX device_serial ON employees(device_serial);

在创建索引时添加UNIQUE关键字会指示数据库确保device_serial列中的值不重复。使用唯一索引，所有添加到表中的新行都会与索引进行比对，以确定列值是否满足约束条件。

数据库将确认索引的创建。

OutputQuery OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在，检查是否仍然可以将重复的条目添加到表中。尝试再次运行先前成功的插入查询。

这是文章《如何在MySQL中使用索引》的第7部分（共9部分）。

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

这一次会显示错误信息。

输出错误 1062 (23000): 重复条目 'ABC123' 对于键 'device_serial'

你可以再次使用SELECT查询来验证新行是否未添加到表中。

SELECT * FROM employees WHERE device_serial = 'ABC123';

现在返回了一行数据。

输出+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 行记录 (0.000 秒)

唯一索引不仅防止重复条目，还是加速查询的功能索引。数据库引擎会像前一步骤一样使用唯一索引。您可以通过执行以下操作来验证这一点。

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

执行计划将类似于以下内容（您的表格可能会稍有不同）：

输出+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 行记录, 1 个警告 (0.00 秒)

设备序列索引在可能的键和键列中都显示出来，证实在执行查询时使用了该索引。

在数据库中，您使用了唯一索引来防止重复数据。在下一部分中，您将使用跨多列的索引。

使用多列索引

直到现在，你在之前的部分创建的所有索引都是使用单个列名来定义的，涉及选择的列的值。大多数数据库系统支持跨多个列的索引。这种称为多列索引的索引提供了一种将多个列的值存储在单个索引中的方式，从而使数据库引擎能够更快速高效地执行使用这组列的查询。

经常需要进行性能优化的常用查询通常会在WHERE过滤子句中使用多个条件。这种查询的一个例子是通过数据库查询同时根据名字和姓氏来找到一个人。

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

对于优化这个查询的首要想法可能是创建两个单独的索引，一个在last_name列上，另一个在first_name列上。然而，对于这种情况来说，这并不是最好的选择。

如果您以此方式创建了两个不同的索引，MySQL 将知道如何找到所有名为 Smith 的员工。它也将知道如何找到所有名为约翰的员工。然而，它将不知道如何找到名为约翰·史密斯的人。

为了阐明存在两个独立索引的问题，想象一下有两本不同的电话簿，一本按姓氏排序，另一本按名字排序。这两本电话簿类似于分别基于姓氏和名字列创建的索引。作为一个电话簿用户，你可以用三种方式来解决查找约翰·史密斯的问题。

使用按姓氏排序的电话簿找到所有名为史密斯的人，忽略第二本电话簿，然后手动逐一检查所有史密斯的人，直到找到约翰·史密斯。
相反的做法：使用按名字排序的电话簿找到所有名为约翰的人，忽略第二本电话簿，然后手动逐一检查所有约翰的人，直到找到约翰·史密斯。
尝试同时使用两本电话簿：分别找到所有名为约翰的人和所有名为史密斯的人，记下中间结果，然后尝试手动交叉这两个数据子集，寻找同时在两个单独列表中的人。

所有这些方法都不理想，并且在处理多个不相关索引和包含多个过滤条件的查询时，MySQL也有类似的选择。

另一种方法是使用索引，这些索引不仅考虑单列，还考虑多列。你可以把它想象成一个放置在另一个电话簿中的电话簿：首先你查找姓氏为史密斯的人，在第二个目录中按照名字的字母顺序组织，你可以用它快速找到约翰。

注意

常言道MySQL每个使用在查询中的表只能使用一个索引。虽然这并不总是正确的，因为MySQL支持索引合并优化来同时使用多个索引运行查询。然而，这个限制在构建索引时是一个很好的经验法则。MySQL可能决定不使用多个索引；即使它使用了多个索引，在许多情况下，它们也不会像专用索引一样有效。

在MySQL中，为员工表的姓和名创建一个多列索引，执行以下操作：

CREATE INDEX names ON employees(last_name, first_name);

在这种情况下，CREATE INDEX语句稍有不同。现在在表名（employees）后的括号中，列出了两列：last_name和first_name。这将在这两列上创建一个多列索引。索引定义中列出的列的顺序很重要，你马上就会发现。

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

OutputQuery OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在，请尝试发出SELECT查询，查找名字为John且姓氏为Smith的行。

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

结果是一个包含员工名为约翰·史密斯的单行数据。

Output+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

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

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

执行计划将类似于以下内容（你的表可能略有不同）：

Output+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

数据库使用了名称索引。扫描了一行数据，因此表格没有多余地被遍历。额外列显示了“使用索引条件”，这意味着MySQL可以仅使用索引完成过滤。

使用跨越这两列的多列索引对姓和名进行过滤，为数据库提供了一个直接且快速的方式来查找所需的结果。

如果在两列上定义了索引，如果你尝试查找所有名字为Smith的员工，但不根据名字进行筛选，会发生什么情况？运行修改后的查询：

SELECT * FROM employees WHERE last_name = 'Smith';

输出将返回以下结果：

Output+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|          20 | Abigail    | Smith     | FGH890        | 155000 |
|          17 | Daniel     | Smith     | WXY901        | 140000 |
|           1 | John       | Smith     | ABC123        |  60000 |
|           5 | Michael    | Smith     | MNO345        |  80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)

有四个员工姓史密斯。

再次访问查询执行计划。

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

执行计划将类似于下面的（您的表可能会略有不同）：

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

这次返回了四行结果，因为有多个员工具有相同的姓氏。然而，执行计划表显示数据库使用了多列索引名来执行此查询，仅扫描了4行 – 即返回结果的准确数量。

在之前的查询中，用于过滤结果的列（last_name）在CREATE INDEX语句中首先传递。现在，您将通过first_name来过滤employees表，而这是多列索引中列列表中的第二个列。执行以下查询：

SELECT * FROM employees WHERE first_name = 'John';

输出结果将会返回如下所示：

Output+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

访问查询执行计划

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

输出将如下返回：

Output+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

再次，返回的结果中只包含一个员工，但这次没有使用索引。数据库扫描了整个表，这可以通过Extra列中的Using where注释以及扫描了20行来说明。

在这种情况下，由于在首次创建索引时传递给CREATE INDEX语句的列的顺序：last_name，first_name，导致数据库未使用索引。只有在查询使用第一列或同时使用第一列和第二列时，数据库才能使用此索引；如果索引定义中的第一列未被查询使用，它无法支持对索引的查询。

通过在多列上创建索引，数据库可以利用索引来加速涉及到所有索引列或左侧前缀逐渐增长的查询。例如，包括列（a，b，c）的多列索引可以用来加速涉及到所有三列的查询，涉及到前两列的查询，甚至仅涉及第一列的查询。另一方面，索引无法帮助只涉及最后一列c或最后两列b和c的查询。

通过仔细选择包括在索引中的列及其顺序，可以使用单个多列索引来加快对同一张表的各种查询。在这个例子中，假设我们按照名字和姓氏来查找员工，或者仅按照姓氏来查找员工，在名字索引中提供的列的顺序保证了该索引将加速所有相关的查询。

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

列出和删除已存在的索引

在之前的部分中，您创建了新的索引。由于索引具有名称并且是在特定的表上定义的，当需要时您还可以列出它们并进行操作。

在这个教程中，列出你创建的所有用于员工表的索引，请执行以下语句。

SHOW INDEXES FROM employees;

输出将类似于以下内容：

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

根据您使用的MySQL版本，输出结果可能会稍有不同，但它将包括所有索引，包括索引的名称、定义索引的列、关于其唯一性的信息以及索引定义的其他详细信息。

要删除现有的索引，可以使用DROP INDEX SQL语句。假设您不再想对device_serial列强制执行唯一性约束。因此，设备序列号索引将不再需要。执行以下命令：

DROP INDEX device_serial ON employees;

设备序列是索引名称，雇员是定义了该索引的表。数据库将确认索引删除。

OutputQuery OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

通过使用CREATE INDEX和DROP INDEX命令，您可以在现有数据库上管理索引，并遵循最佳实践在需要和有益时创建索引。

结论是：

通過遵循這個指南，你學到了索引是什麼，以及如何使用MySQL中最常見的類型來加速通過條件SELECT查詢檢索數據。你使用索引來維護列數據的唯一性，並學習了當在過濾條件中使用多個列時，索引如何影響查詢。

根据经常执行的查询类型，您可以使用索引来调整数据库的性能，为常见用例在读写性能方面找到适当的平衡。本教程仅介绍了使用索引的基础知识。通过了解MySQL如何选择何时使用哪些索引，您可以通过索引来支持更复杂的查询。要了解更多信息，请参阅MySQL索引的文档。

如果你想更多了解关于SQL语言和与之相关的不同概念，我们鼓励你查看”How To Use SQL”系列中的其他指南。

#MySQL教程 #MySQL索引 #数据库优化 #数据库管理 #查询性能