较大的数据集时，在MySQL中未使用索引

Question

我的查询看起来像是这样的

EXPLAIN SELECT 
    r.owner_id, 
    r.owner_address, 
    r.owner_platform,
    r.updated_at 
FROM some_owner_table as r 
WHERE 
    r.updated_at > '2022-09-16 22:16:38.832' 
ORDER BY 
    r.updated_at DESC LIMIT 200;

结果是

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'r', NULL, 'range', 'INDEX_by_updated_at', 'INDEX_by_updated_at', '6', NULL, '1', '100.00', 'Using index condition'

，如果我们使用不同的日期，我认为增加了我们获得的结果数：

'1', 'SIMPLE', 'r', NULL, 'ALL', 'INDEX_by_updated_at', NULL, NULL, NULL, '263', '37.64', 'Using where; Using filesort'

使用filesort在性能方面似乎有问题。不再使用使用索引条件。

这是索引的真正起作用，还是我们可以做一些事情来进一步优化该表的查询？

编辑：表有263行。

编辑：创建查询：

CREATE TABLE `some_owner_table` (
  `owner_id` bigint(20) NOT NULL,
  `owner_address` bigint(20) NOT NULL,
  `owner_platform` int(11) NOT NULL,
  `updated_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`owner_id`,`owner_platform`),
  KEY `INDEX_by_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

MySQL的优化器通常选择使用索引跳过，如果可以推断您的病情与表格足够大的部分相匹配。根据我的经验，阈值约为20％，但这不是官方功能，如果MySQL Server代码更改，则可能会有所不同。

原因是进行索引查找实际上需要更多的工作，然后从该索引条目获取整个行。优化器可以假定，只要按主键顺序行行行走，并保持与条件匹配的行是更经济的。

但是，如果要搜索的特定值发生在一小部分行中，那么选择几个索引条目，然后获取相应的行更经济。

如果您认为优化器做出了错误的选择，则可以使用 Force Index提示，它使优化器将桌子视为无限成本，因此，如果该索引完全相关，则将使用它。

例如，避免filesort可能值得。也就是说，如果您强迫查询通过在上读取索引来扫描行 ，则分类将变为no-op，它将避免使用文件。

另一个想法：如果您正在测试一个很小的数据集，则优化器可以认为使用索引并不重要，因为无论如何，排在RAM中的少数页面中，并且成本是搜索或排序将是微不足道的。

这就是为什么您应该使用较大样本量测试优化的原因，因为优化器可能对琐碎的数据集做出不同的选择。

Answer 2

MySQL / Mariadb中的查询计划者对是否使用索引做出了基于成本的选择。过度简化，它猜测CPU和IO时间需要在不使用索引的情况下满足查询需要多少时间，并选择最小的猜测。就像@billkarwin所说的那样，对于小（数百行）和大（数百万）桌子来说，这些选择可能会大不相同。因此，不要浪费大量时间在小桌子上解释查询。

您有col＆gt;查询中的COL DESC限制200模式的恒定顺序。当您的查询不使用索引时，服务器大致完成了这些事情。

• 扫描逐行行，寻找与Where子句相匹配的行。
• 将这些行放入内部数据结构中，这是一种轻巧的表。当说明输出中提到时，该表称为“文件”。这不是文件系统文件。
• 完成表扫描后，它按顺序指定的“文件”为“文件”。
• 最后，它向您发送了从排序的“文件”中向您发送前200行，并丢弃其余的。

但是您的列上有一个索引。当它使用索引时，服务器可以更有效地工作。它

• 随机访问第一行匹配的索引。
• 通过索引顺序扫描，直到最后一个匹配的行
• 检索并将每个匹配行发送给您时
• ，并在发送200行时停止。

无需分类。 （HOORAY。）

如果您提到的查询必须比生产更快，则可以尝试按照这样的降序重新创建索引：

ALTER TABLE some_owner_table 
     DROP KEY INDEX_by_updated_at,
      ADD KEY INDEX_by_updated_at 
          (updated_at DESC);

这将您的索引降低顺序，通过子句完全匹配您的订单。向前扫描索引而不是向后扫描索引的价格略低，而修订后的索引可以实现这种情况。

如果仍然不够快，您可以尝试创建覆盖索引。覆盖索引允许服务器从索引完全满足查询，而不是从索引到表^*查找每个行的数据。看起来像这样。

ALTER TABLE some_owner_table 
     DROP KEY INDEX_by_updated_at,
      ADD KEY INDEX_by_updated_at 
           (updated_at DESC, owner_id, owner_address, owner_platform);

这样的索引需要表空间（SSD或磁盘空间），并稍微放慢了插入和更新，因为服务器必须更新索引和表。但是表空间很便宜。而且大多数桌子的插入或更新几乎没有选择。

^*在Innodb中，该表实际上存储在主键内。它称为a clustered index 。