为 MySQL 查询优化选择最佳索引 mysqlmysql查询优化索引

我们的许多用户、开发者和数据库管理员不断向我们的团队咨询有关 EverSQL 的索引推荐算法。

所以，我们决定写一些这方面的内容。

本教程不会详细介绍该算法的所有内部特性，而是要简单地说明索引最重要的方面。另外，也是最重要的，我们将通过基于一套规则而不是基于猜测来提供实用的示例，以便正确地索引表和查询。

本教程关注的重点是 MySQL、MariaDB 和 PerconaDB 数据库。这些信息也可能与其他数据库供应商有关，但在某些情况下可能不会。

我应该为我的 SQL 查询创建哪些索引？
根据通常的经验规则，当尝试优化你的 SQL 查询时，你可以依照以下步骤构建复合索引：

首先列出你的查询中所有使用的表，并为查询中的每个子查询创建一个独立的列表。如果你有一个包含 2 个 SELECT 子查询的 SELECT 查询，那你应该建立 3 个列表，每个列表包含引用在其中的表。
在此过程结束时，你可能会在每个查询列表中为每个这些表添加一个列的列表。

在你的任何索引中最左边的列应与查询相等比较（如 age = 25）中的列匹配。
你可以添加多个列，只要所有列与常量进行比较相等即可。

那么，你应该选择一个列，这将是“范围列(range column)”。MySQL 在每个索引中只支持一个范围列。
因此，你应该使用范围运算符(<>, >, <, IN(), BETWEEN, LIKE)来查看所有的比较，并选择可以过滤最多行数的那个。
将该列给该表添加为你的索引中的下一列。
你可以在这里获得一些信息 —— 关于在范围列之前添加相等列的理由（幻灯片由一位 MySQL 优化团队的成员编写）。

如果查询中不存在范围列，你可以添加 GROUP BY 子句中的列。

如果查询中不存在范围列，并且没有 GROUP BY 子句，则可以添加 ORDER BY 子句中的列。

在某些情况下，创建一个独立的索引来保存 ORDER BY 子句的列是有意义的，因为 MySQL 有时会选择使用它。请注意，尽管如此，索引应该包含 ORDER BY 子句中的所有列，它们应该全部在 ORDER BY 子句中用相同的顺序(ASC / DESC)指定。这并不能保证数据库的优化器会选择这个索引而不是 WHERE 复合索引，但是值得一试。

最后，从 SELECT 子句中添加相关的列，这可能允许 MySQL 使用索引作为覆盖索引。覆盖索引是包含过滤和查询子句中的所有列的索引。这样的索引允许数据库仅通过使用索引运行查询，而不需要访问表。在许多情况下，这种方法显然更快。        

我们通过一个示例来说明：

SELECT id, first_name, last_name, age from employees where first_name = ‘John’ AND last_name = ‘Brack’ and age > 25 ORDER BY age ASC;
对于这个查询，我们将以添加 first_name 和 last_name 列开始，它们与等号运算符进行比较。然后，我们将添加与范围条件进行比较的 age 列。这里不需要在 ORDER BY 子句索引，因为 age 列已经在索引中了。最后同样重要的是，我们将从 SELECT 子句中添加 id 到索引以生成 covering 索引。         

所以为了正确的索引这个查询，你应该添加以下索引：
employees (first_name, last_name, age, id)

以上是一个非常简化的伪代码算法，可以让你为相当简单的 SQL 查询构建简单的索引。

如果你正在寻找一种方法来实现这个过程的自动化，并希望增强专有索引算法和查询优化的好处，你可以试用 EverSQL Query Optimizer，它为你做了所有繁重的工作。   

索引（或编写 SQL 查询）时不应该做什么？
我们收集了一些程序员和数据库管理员在编写查询和索引表时遇到的最常见的错误。

将表中的每一列分别索引
在大多数情况下，MySQL 将不能在查询中为每个表使用多个索引。

因此，当为表中的每一列创建一个单独的索引时，数据库只能使用索引执行其中一个搜索操作，而其余部分将显着较慢，因为数据库不能使用索引执行它们。

我们建议使用复合索引（本文稍后解释）而不是单列索引。

filtering 条件中的 OR 运算符
考虑以下查询语句：

SELECT a, b FROM tbl WHERE a = 3 OR b = 8
在许多情况下，MySQL 将无法使用索引来应用 OR 条件，所以，此查询是不可索引的。

因此，我们建议避免这种 OR 条件，并考虑将查询拆分为两部分，并结合 UNION DISTINCT 使用（或者最好使用 UNION ALL，以防你不知道其中不会有任何重复的结果）      

在索引中列的顺序十分重要
比方说，我把我的联系人电话簿交给你，电话簿是按照联系人的名字排序的，要求你找出电话簿中有多少人名为“John”。你会接过电话簿，说“没问题”。你将找到包含以 John 开头的所有名字的页面，并从此处开始计数。

现在，假设我改变了任务，并给你一个按联系人的姓氏排序的电话簿，但要求你仍然统计以“John”作为名字的所有联系人。你会怎么做？ 同样的，数据库在这种情况下也会很为难的。

现在让我们看看一个 SQL 查询来演示使用 MySQL 优化器时相同的行为：

SELECT first_name, last_name FROM contacts WHERE first_name = ‘John’;
拥有索引的联系人(first_name, last_name)在这里是理想的，因为索引从我们的筛选条件开始，然后在 SELECT 子句中以另一个列结束。

但是，具有反向索引的联系人(last_name，first_name)是相当没有意义的，因为数据库不能使用索引过滤，作为列，我们需要的是索引中的第二个，而不是第一个。

这个例子的结论是，索引中的列顺序非常重要。      

增加的冗余索引
当你试图优化你的 SQL 查询时，索引是非常有意义的，它可以显著地提高性能。

但是，这也有不利的一面。你创建的每个索引都应该保持更新，并在数据库中发生更改时保持同步。因此，对于数据库中的每个 INSERT / UPDATE / DELETE，都应更新所有相关索引。此更新可能需要较长的时间，特别是对于大型的表/索引。

所以，除非你知道你需要它们，否则不要创建索引。

另外，我们强烈推荐在某一段时间内分析一下数据库，搜索任何可以删除的冗余索引。