MySQL 使用了错误的索引

Question

有一个包含这些列的表:

<表类="s-表"><头>姓名类型<正文>身份证整数日期时间日期时间(6)公司编号整数(FK)被排除微积分(1)

BTREE索引有2个:

1. 公司编号
2. CompnyId,DateTime,IsExcluded

---

以下选择使用只有一列的第一个索引，这个选择需要 2.3 秒。当我强制第二个索引选择时需要 0.015 秒。此外，当我将 DateTime 范围减少一天时，MySQL 会在不强制的情况下使用第二个索引。

select IsExcluded,DateTime,CompanyId FROM table where 
IsExcluded = 0 and 
DateTime >= '2022-06-02' and 
DateTime < '2022-09-22' and 
CompanyId = 1;

我知道如果选择了超过 ~20%-30% 的行，MySQL 可能会决定忽略索引，但我不明白为什么 MySQL 选择不同的显然不是最合适的索引。

有什么方法可以设置或“学习”MySql 该查询的第二个索引是最合适的(无需插入任何其他查询)？

Answer 1

Is there any way how to setup or "learn" MySql that second index for this query is the most suitable (without inserting anything else to query) ?

理论上，您可以手动调整索引统计信息以影响优化器的选择。参见 https://dev.mysql.com/blog-archive/histogram-statistics-in-mysql/

但坦率地说，据我所知，没有开发人员使用此功能。弄清楚如何使用它太难了，而且您选择的任何自定义索引统计信息都可能很快过时。

更常见的是使用索引提示告诉优化器只考虑特定的索引，或者忽略其他索引。参见 https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

例子:

SELECT IsExcluded, DateTime, CompanyId 
FROM table USE INDEX (myindex_with_three_columns)
WHERE IsExcluded = 0 
 AND DateTime >= '2022-06-02' 
 AND DateTime < '2022-09-22' 
 AND CompanyId = 1;

(我在猜测您的 3 列索引的名称。)

我猜你的索引没有被使用的原因是 DateTime 部分没有足够的帮助来证明更宽的索引(即你的日期范围涵盖太多行)，而 IsExcluded 部分根本不使用，因为它跟在范围条件中使用的列之后。因此优化器会选择一个更紧凑的索引，这样它就可以在更少的 I/O 读取中将索引加载到 RAM 中。

了解索引中的列顺序很重要的是，相等条件中涉及的列应该在左边。然后您可以在不等式或范围条件中使用一个列，并且索引中的任何后续列都不会用于搜索或排序。

在您的情况下，您在 (CompanyId,DateTime,IsExcluded) 上有一个索引，但第二列用于范围条件，因此第三列不用于搜索。充其量它可以尝试使用 index condition pushdown ，但这不如真正缩小搜索范围。

更好的索引是对列进行排序，使 DateTime 列在最后。用于相等的列中的任何一个都可以在前面，但它们都必须在 DateTime 列之前。在这种情况下，这些顺序中的任何一个都会更好:

(CompanyId,IsExcluded,DateTime)
(IsExcluded,CompanyId,DateTime)