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

在使用mysql进行查询的时候，很多人都知道要使用explain来查看执行计划，使SQL用上索引，那是不是使用上索引就结束了呢？不是的，用上索引只是最基本的要求，还需要使用最优的索引，SQL的执行效率才会最高，才能达到想要的效果。

我应该创建哪些索引来优化我的 SQL 查询？

在我们开始之前，请注意，建议努力创建与SQL中尽可能多的查询“匹配”的索引，因为这将减少索引的总量，以及它们的存在可以在数据库上创建的负载插入或更新数据（这也需要更新索引）。另外，请记住索引中列的顺序很重要。

作为一般的经验法则，MySQL 只能为查询中的每个表使用一个索引（不包括我们不会在本文中介绍的索引合并），因此您应该首先列出查询中的所有表，看看是否和您应该为其创建哪个索引。

创建索引时，通常应该从 WHERE 和 JOIN 条件中的相等条件开始。在大多数情况下，诸如name = ‘John’ 之类的条件将允许数据库过滤表中的许多行并通过少量行返回所需的结果。因此，我们应该通过将这些列添加到索引来开始索引。请记住，您可能应该只添加属于选择性条件的列，否则它们对优化查询的贡献不大。

然后，您应该查看范围条件，但您应该只为每个表添加其中一个 – 这是最具选择性的条件，因为 MySQL 只能处理每个索引中的一个“范围列”。在某些没有范围条件的情况下，添加 GROUP BY / ORDER BY 列是有意义的，假设排序仅在一个方向 (ASC / DESC) 上完成。

在某些情况下，创建一个包含 ORDER BY 子句列的单独索引也是有意义的，因为 MySQL 有时会选择使用它。请注意，要发生这种情况，索引应包含 ORDER BY 子句中的所有列，并且应以相同的顺序 (ASC / DESC) 指定它们。这并不能保证数据库的优化器会选择这个索引而不是其他复合索引。与使用其他索引（或扫描全表）相比，只有当 MySQL 知道通过排序索引快速跟踪结果时，才会发生这种情况。

此外，在某些情况下，也可以将 SELECT 子句中的列添加到索引中，以创建覆盖索引。这仅在索引尚未“太大”时才相关。什么太大了？好吧，这里没有官方的经验法则，但我通常在索引中最多使用 5 列。创建覆盖索引可以让数据库不仅可以使用索引进行过滤，还可以直接从索引中获取SELECT子句所需的信息，从而节省了从表数据中获取过滤行数据的宝贵I/O操作.

让我们看一个例子来说明：

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，它们与相等运算符进行比较。然后，我们将添加与范围条件进行比较的年龄列。无需在此处对 ORDER BY 子句进行索引，因为age列已在索引中。最后但并非最不重要的一点是，我们将SELECT 子句中的id添加到索引中以获得覆盖索引。

因此，要正确索引此查询，您应该创建索引：
employees (first_name, last_name, age, id)

上面是一个非常简化的伪算法，它将允许您为相当简单的 SQL 查询构建简单的索引。

在确定 JOIN ON 子句中的哪些列要索引时，您应该记住，MySQL 有一个算法来检测您应该在 INNER JOIN 中从哪个表开始，它还确定首先要索引的列。

索引（或编写 SQL 查询）时不应该做什么？

我们收集了一些我们看到程序员和数据库管理员在编写查询和索引他们的表时最常见的错误。

分别索引表中的每一列

在大多数情况下，MySQL 不能为查询中的每个表使用多个索引（不包括非常特殊的索引合并情况）。

因此，当为表中的每一列创建单独的索引时，数据库必然只执行一项使用索引的搜索操作，其余的搜索操作会明显变慢，因为数据库不能使用索引来进行搜索。执行它们。

我们建议在适当的地方使用复合（多列）索引，而不是单独为每一列使用单列索引。

过滤条件中的 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';

拥有索引contacts (first_name, last_name)在这里是理想的，因为索引以我们的过滤条件开始并以 SELECT 子句中的另一列结束。

但是，拥有反向索引contacts（last_name，first_name）是没有用的，因为数据库不能使用索引进行过滤，因为我们需要的列是索引中的第二个而不是第一个。

这个例子的结论是索引中列的顺序相当重要。

冗余索引

尝试优化 SQL 查询时，索引非常有用，它们可以显着提高性能。

但是，它们也有缺点。当您的数据库发生更改时，您创建的每个索引都应保持更新和同步。因此，对于数据库中的每个 INSERT / UPDATE / DELETE，都应该更新所有相关索引。此更新可能需要一些时间，尤其是大型表/索引。

因此，除非您知道需要它们，否则不要创建索引。