MySQL 使用了错误的索引

发布于09月29日

有一个表，其中包含以下各列:

Name	Type
Id	int
DateTime	datetime(6)
公司ID	int (FK)
IsExcluded	tinyint(1)

有2个BTREE索引:

公司ID
ComapnyID、DateTime、IsExcluded

SELECT之后使用的是只有一列的第一个索引，此SELECT操作耗时2.3秒.当我强制第二次索引 Select 时，花费0.015秒.此外，当我将DATETIME范围减少一天时，MySQL将使用第二个索引，而不强制.

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

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

有没有办法设置或"了解"MySQL这个查询的第二个索引是最合适的(不插入任何其他要查询的内容)？

推荐答案

有没有办法设置或"了解"MySQL这个查询的第二个索引是最合适的(不插入任何其他要查询的内容)？

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

但坦率地说，据我所知，没有开发人员使用这一功能.要弄清楚如何使用它实在是太难了，而且您 Select 的任何自定义索引统计信息都可能很快就会过时.

更常见的是使用索引提示来告诉优化器只考虑特定的索引，或者忽略其他索引.见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部分，因为它跟在范围条件中使用的列之后.因此，优化器 Select 一个更紧凑的索引，这样它就可以在较少的I/O读操作中将索引加载到RAM中.

了解索引中的列顺序很重要的一点是，相等条件中涉及的列应该位于左侧.然后，您可以有one列用于不等条件或范围条件，并且索引中的任何后续列都不用于搜索或排序.

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

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

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

MySQL 使用了错误的索引

推荐答案

Mysql相关问答推荐

过程/别名是有点长的MySQL命令的一部分吗？

在MySQL中从非连续数据创建时间线

在停靠容器中备份和恢复MySQL数据库时出现Unicode字符问题

我是否需要在 N+1 列上建立索引，才能有效地在 N 列上执行 SELECT，并按其他列排序？

MySQL - 密码哈希没有预期的格式

MySQL MDL(元数据锁)为什么会导致死锁？

Mysql - 按周分组但从 1 开始计数

排序子查询结果并返回每个 ID 的第一行

即使在某些表中不匹配，如何从 MySQL 中的多个表中 Select 所有记录

根据 Power Query 中的条件替换值

高效的 SQL 查询，用于计算半小时时间序列中已发生的行的一部分

在 MySQL 中跨日期和位置计算和排序实体的共现

为什么以及如何将 Django filter-by BooleanField 查询转换为 SQL WHERE 或 WHERE NOT 而不是 1/0？

MySQL中的会话客户端是什么

为什么 fetch 方法不能获取任何东西？(feat node.js，restAPI)

Mysql 相等性反对 false 或 true

估计行数 SQL

使用 MySQL 在树中查找 Leaf

在网页的 HTML 表中显示 MySQL 数据库表中的值

将数据库从 Postgres 迁移到 MySQL