"SELECT COUNT(*)"很慢,即使使用where子句也是如此

我试图弄清楚如何在MySQL中优化一个非常慢的查询(我没有设计这个):

SELECT COUNT(*) FROM change_event me WHERE change_event_id > '1212281603783391';
+----------+
| COUNT(*) |
+----------+
|  3224022 |
+----------+
1 row in set (1 min 0.16 sec)

将其与完整计数进行比较:

select count(*) from change_event;
+----------+
| count(*) |
+----------+
|  6069102 |
+----------+
1 row in set (4.21 sec)

解释声明对我没有帮助:

 explain SELECT COUNT(*) FROM change_event me WHERE change_event_id > '1212281603783391'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: me
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 4120213
        Extra: Using where; Using index
1 row in set (0.00 sec)

好吧,它仍然认为它需要大约400万个条目来计算,但我可以更快地计算文件中的行数!我不明白为什么MySQL需要这么长时间.

这是表定义:

CREATE TABLE `change_event` (
  `change_event_id` bigint(20) NOT NULL default '0',
  `timestamp` datetime NOT NULL,
  `change_type` enum('create','update','delete','noop') default NULL,
  `changed_object_type` enum('Brand','Broadcast','Episode','OnDemand') NOT NULL,
  `changed_object_id` varchar(255) default NULL,
  `changed_object_modified` datetime NOT NULL default '1000-01-01 00:00:00',
  `modified` datetime NOT NULL default '1000-01-01 00:00:00',
  `created` datetime NOT NULL default '1000-01-01 00:00:00',
  `pid` char(15) default NULL,
  `episode_pid` char(15) default NULL,
  `import_id` int(11) NOT NULL,
  `status` enum('success','failure') NOT NULL,
  `xml_diff` text,
  `node_digest` char(32) default NULL,
  PRIMARY KEY  (`change_event_id`),
  KEY `idx_change_events_changed_object_id` (`changed_object_id`),
  KEY `idx_change_events_episode_pid` (`episode_pid`),
  KEY `fk_import_id` (`import_id`),
  KEY `idx_change_event_timestamp_ce_id` (`timestamp`,`change_event_id`),
  KEY `idx_change_event_status` (`status`),
  CONSTRAINT `fk_change_event_import` FOREIGN KEY (`import_id`) REFERENCES `import` (`import_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

版:

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.37, for pc-solaris2.8 (i386) using readline 5.0

有什么明显的东西我不见了吗？(是的,我已经尝试过"SELECT COUNT(change_event_id)",但没有性能差异).

1> ʞɔıu..：

---

InnoDB使用群集主键,因此主键与数据页中的行一起存储,而不是存储在单独的索引页中.为了进行范围扫描,您仍然必须扫描数据页中的所有可能宽的行; 请注意,此表包含TEXT列.

我会尝试两件事:

跑optimize table.这将确保数据页面按排序顺序进行物理存储.这可以想象地加速群集主键上的范围扫描.

仅在change_event_id列上创建其他非主索引.这将在索引页面中存储该列的副本,扫描速度要快得多.创建后,检查解释计划以确保它使用新索引.

(如果它从零开始递增,你也可能想让change_event_id列bigint 无符号)

---

这是我第一次见到有人建议在PRIMARY KEY列上创建冗余索引作为MySQL中的性能黑客.我对它的工作原理以及它有用的查询类型非常感兴趣.你有进一步阅读这个主题的链接吗？

---

"优化表"没有多大帮助,但冗余索引解决了这个问题.谢谢!

2> benjismith..：

---

以下是我建议的一些事项:

将列从"bigint"更改为"int unsigned".您真的期望在此表中拥有超过42亿条记录吗？如果没有,那么你就浪费了超宽领域的空间(和时间).MySQL索引在较小的数据类型上更有效.

运行" OPTIMIZE TABLE "命令,然后查看您的查询是否更快.

您还可以考虑根据ID字段对表进行分区,尤其是当较旧的记录(ID值较低)随时间变得不那么相关时.分区表通常可以比一个巨大的未分区表更快地执行聚合查询.

---

编辑:

仔细查看此表,它看起来像一个日志式样式表,其中插入行但从未修改过.

如果这是真的,那么您可能不需要InnoDB存储引擎提供的所有事务安全性,并且您可能无法切换到MyISAM,这在聚合查询上要高效得多.

3> chaos..：

---

我之前使用IP地理定位数据库遇到了这样的行为.经过一些记录,MySQL从基于范围的查询的索引中获得任何优势的能力显然已经消失.使用地理定位DB,我们通过将数据分段为足够合理的块来处理它,以允许使用索引.