MySQL 调优：MySQL 使用 USE INDEX 作为索引提示

MySQL USE INDEX简介

索引为你提供了优化查询性能的好方法，它就像一本书的目录，让你能快速找到所需内容，mysql在选择最佳查询方式时，需要考虑很多因素，其中基数是重要的因素之一。基数意味着可以插入列中数值的唯一性。

但是，由于多次插入、更新和删除操作，基数可能会发生变化。您可以定期执行 ANALYZE TABLE 语句来更新基数。除此之外，MySQL 还为您提供了建议查询优化器使用特定索引提示（称为 USE INDEX 提示）的方法。

使用 USE INDEX 语句，您可以指定一个或多个索引，这将提示查询优化器使用它们来查找表中的记录。

MySQL USE INDEX使用场景

在MySQL中，使用"USE INDEX"语句的主要使用场景如下：

强制使用特定的索引：当查询优化器选择了不太高效的索引，或者根本没有选择索引时，可以使用"USE INDEX"语句来指定要使用的索引。特别适用于分析和调试查询性能问题。

测试不同索引的性能：在有多个可选的索引时，可以使用"USE INDEX"语句来临时替换不同的索引，以评估它们对查询性能的影响，从而选择最佳的索引策略。

绕过索引选择器：当MySQL选择了不适合的索引时，可能会引起性能问题。使用"USE INDEX"语句可以绕过索引选择器，强制使用指定的索引。

USE INDEX的限制和注意事项：

• 语法限制：USE INDEX语句的语法是使用在SELECT语句中，用于指定要使用的索引。例如：SELECT * FROM table_name USE INDEX (index_name) WHERE condition;。需要确保语法正确并且正确指定了要使用的索引。
• 仅适用于单表查询：USE INDEX语句只适用于单表查询，无法用于涉及多个表的联接查询。如果查询涉及多个表，可以考虑使用FORCE INDEX语句。
• 仅适用于当前查询：USE INDEX语句只适用于当前查询，不会对表的全局索引使用产生影响。它仅在当前查询中强制使用指定的索引。
• 索引必须存在：使用USE INDEX语句时，需要确保指定的索引存在于表中。如果指定了不存在的索引，将会抛出错误。
• 超过索引的列不能使用：USE INDEX语句只能指定索引的使用，不能指定查询中其他列的使用。如果查询需要使用的列不在指定的索引中，优化器可能会选择其他索引或执行全表扫描。
• 潜在性能问题：尽管USE INDEX可以用于指定索引，但过度使用USE INDEX可能会导致查询性能下降。优化器通常能够更好地评估和选择索引，使用USE INDEX可能会限制其优化能力，导致不必要的索引使用和性能损失。
• 谨慎使用：使用USE INDEX语句时需要谨慎评估和测试。建议在实际场景中进行性能测试和比较，确保使用USE INDEX确实能够提供更好的性能，而不是盲目使用。

MySQL USE INDEX 语句

以下语法显示如何在查询中使用 USE INDEX 提示语句。

SELECT cols_list
FROM table_name USE INDEX(index_list)
WHERE condition;

这里，index_list可能只是一个索引，也可能是一组多个索引。

请注意，即使您建议使用索引，它也完全取决于查询优化器根据该索引的使用情况决定是否使用给定索引。

MySQL 使用索引示例

为了演示 USE INDEX 的示例，我们将使用下面的表结构。

现在我们将在表 emps 上创建四个索引。

CREATE INDEX ind_name ON emps(name);
CREATE INDEX ind_city ON emps(city);
CREATE INDEX ind_name_city ON emps(name,city);
CREATE INDEX ind_city_name ON emps(city,name);

现在，我们将编写一个查询来获取名称或城市以字母“a”开头的记录。我们将使用解释子句来检查哪些索引用于查找记录。

EXPLAIN SELECT * FROM emps WHERE name LIKE 'a%' OR city LIKE 'a%' \G;

从这里我们可以看到，优化器使用了索引ind_name,ind_city，从表中检索数据。

现在，我们将尝试指定不同的索引名称，看看查询优化器是否使用它们。

正如您所看到的，我们指定的索引对于从表中查找记录没有帮助，这就是查询优化器不使用它的原因。　

总结

过度使用USE INDEX可能会导致查询性能下降。优化器通常能够更好地评估和选择索引，使用USE INDEX可能会限制其优化能力，导致不必要的索引使用和性能损失。

在使用USE INDEX语句时需要谨慎评估和测试。建议在实际场景中进行性能测试和比较，确保使用USE INDEX确实能够提供更好的性能，而不是盲目使用。

虽然我们在语句中使用了use index，但是指定的索引并不总能被查询优化器引用，优化器会根据实际情况评估性能。

原文地址：https://www.toutiao.com/article/7270769835280384548/