MySQL - 基本索引减慢简单聚合查询

Question

我有一个存储流量数据的简单表:

CREATE TABLE `domain_traffic` (
  `dtraff_id` int(10) UNSIGNED NOT NULL,
  `domain_id` int(10) UNSIGNED NOT NULL,
  `dtraff_time` bigint(20) UNSIGNED NOT NULL,
  `dtraff_web` bigint(20) UNSIGNED DEFAULT '0',
  `dtraff_ftp` bigint(20) UNSIGNED DEFAULT '0',
  `dtraff_mail` bigint(20) UNSIGNED DEFAULT '0',
  `dtraff_pop` bigint(20) UNSIGNED DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `domain_traffic`
  ADD PRIMARY KEY (`dtraff_id`),
  ADD KEY `domain_id` (`domain_id`);

ALTER TABLE `domain_traffic`
  MODIFY `dtraff_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

通过强制使用 domain_id 索引(MySQL 默认执行的操作)运行以下查询大约需要 12 秒:

SELECT SQL_NO_CACHE
    SUM(dtraff_web) as web,
    SUM(dtraff_ftp) as ftp,
    SUM(dtraff_mail) as mail,
    SUM(dtraff_pop) as pop
FROM domain_traffic FORCE INDEX (domain_id)
WHERE domain_id = 150

但是

上面相同的查询要求 MySQL 忽略 domain_id 索引只用了大约 2 秒(无论如何仍然很糟糕):

SELECT SQL_NO_CACHE
    SUM(dtraff_web) as web,
    SUM(dtraff_ftp) as ftp,
    SUM(dtraff_mail) as mail,
    SUM(dtraff_pop) as pop
FROM domain_traffic IGNORE INDEX (domain_id)
WHERE domain_id = 150

我真的很惊讶这样的结果，我真的很想知道为什么会这样......

EXPLAIN 两个查询:

使用 domain_id 索引:

+------+-------------+----------------+------+---------------+-----------+---------+-------+---------+-------+
| id   | select_type | table          | type | possible_keys | key       | key_len | ref   | rows    | Extra |
+------+-------------+----------------+------+---------------+-----------+---------+-------+---------+-------+
|    1 | SIMPLE      | domain_traffic | ref  | domain_id     | domain_id | 4       | const | 2069312 |       |
+------+-------------+----------------+------+---------------+-----------+---------+-------+---------+-------+

忽略 domain_id 索引

+------+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | domain_traffic | ALL  | NULL          | NULL | NULL    | NULL | 4138625 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+---------+-------------

备案:

1. MySQL 供应商:MariaDB 10.1
2. ID 为 150 的域的行数:4156659

有什么解释和建议吗？

谢谢。

没有优化器提示的结果(遵循@Bill Karwin 的回答):

MariaDB [imscp]> EXPLAIN
  SELECT SQL_NO_CACHE
          SUM(dtraff_web) as web, SUM(dtraff_ftp) as ftp,
          SUM(dtraff_mail) as mail, SUM(dtraff_pop) as pop
      FROM domain_traffic WHERE domain_id = 150;
+------+-------------+----------------+------+---------------+-----------+---------+-------+---------+-------+
| id   | select_type | table          | type | possible_keys | key       | key_len | ref   | rows    | Extra |
+------+-------------+----------------+------+---------------+-----------+---------+-------+---------+-------+
|    1 | SIMPLE      | domain_traffic | ref  | domain_id     | domain_id | 4       | const | 2069312 |       |
+------+-------------+----------------+------+---------------+-----------+---------+-------+---------+-------+
1 row in set (0.00 sec)

MariaDB [imscp]> SELECT SQL_NO_CACHE
      SUM(dtraff_web) as web, SUM(dtraff_ftp) as ftp,
      SUM(dtraff_mail) as mail, SUM(dtraff_pop) as pop
  FROM domain_traffic WHERE domain_id = 150;
+--------------+--------------+--------------+--------------+
| web          | ftp          | mail         | pop          |
+--------------+--------------+--------------+--------------+
| 105989792928 | 106045788277 | 105954990092 | 105942540350 |
+--------------+--------------+--------------+--------------+
1 row in set (8.53 sec)

Answer 1

当您有忽略索引的 EXPLAIN 时，它表明它运行表扫描(类型:ALL)。检查的行数约为 4.1m(这只是粗略估计，不是准确计数)。

您阐明了 domain_id = 150 的约 4.1m 行。因此，实际上表中的每一行都符合您的 WHERE 条件。

想想一本书后面的索引。为什么它不包含“the”或“and”等词的条目？因为这些词几乎出现在每一页上，将它们编入索引并使用索引来查找这些常见词的出现，然后翻到相应的页面，然后翻回索引以查找下一页是浪费时间发生在第 2 页，依此类推。

与MySQL中二级索引的方式相同。如果优化器检测到您搜索的给定值太常见，它会跳过索引并只进行表扫描。当索引不能有效地缩小搜索范围以使其不值得时，在阅读索引时更容易做到这一点。

在实践中，我观察到当值出现在表中 21-25% 的行上时，优化器会跳过使用索引。通常这是一个很好的决定。很少有必要使用 FORCE INDEX 告诉优化器您不希望不惜一切代价进行表扫描。但这种情况很少见。

我的建议是:让优化器完成它的工作。它通常会根据查询逻辑和频率做出是否使用索引的正确决定您正在搜索的特定值。

---

回复你的评论:

如果您的生产数据允许 WHERE 条件选择表的少数子集，那么优化器应该决定是否值得使用索引。优化器的目标之一是减少 InnoDB 需要读取的已检查行的数量。

这是一个很好的例子，说明了为什么您需要使用模拟生产数据的数据进行测试。拥有不同数据值的正确比率有助于您进行实际的查询优化器测试。

还要确保使用 ANALYZE TABLE不时地确保 InnoDB 具有有关索引中数据分布的当前统计信息。我见过通过运行 ANALYZE TABLE 非常简单地修复奇怪的索引行为的情况。这是一个快速操作，即使您的表非常大也是如此。

这不必非常频繁，但如果索引中值的分布发生显着变化(例如，如果您执行主要的批量插入或批量删除)，则值得在之后执行 ANALYZE TABLE。