"SELECT COUNT(*)"；即使使用where子句也很慢

本文介绍了"SELECT COUNT(*)"；即使使用where子句也很慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

I'm trying to figure out how to optimize a very slow query in MySQL (I didn't design this):

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)

解释性声明对我没有帮助:

The explain statement doesn't help me here:

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)

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

OK, it still thinks it needs roughly 4 million entries to count, but I could count lines in a file faster than that! I don't understand why MySQL is taking this long.

这是表的定义:

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)"，但是没有性能差异).

Is there something obvious I'm missing? (Yes, I've already tried "SELECT COUNT(change_event_id)", but there's no performance difference).

推荐答案

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

InnoDB uses clustered primary keys, so the primary key is stored along with the row in the data pages, not in separate index pages. In order to do a range scan you still have to scan through all of the potentially wide rows in data pages; note that this table contains a TEXT column.

我会尝试两件事:

运行optimize table.这将确保数据页按排序顺序进行物理存储.可以想象，这可以加快对集群主键的范围扫描.

仅在change_event_id列上创建一个附加的非主索引.这会将该列的副本存储在索引页面中，从而使扫描速度更快.创建它之后，检查说明计划以确保它正在使用新索引.

run optimize table. This will ensure that the data pages are physically stored in sorted order. This could conceivably speed up a range scan on a clustered primary key.

create an additional non-primary index on just the change_event_id column. This will store a copy of that column in index pages which be much faster to scan. After creating it, check the explain plan to make sure it's using the new index.

(如果您将change_event_id列从零开始递增，您可能还希望使其为bigint )

(you also probably want to make the change_event_id column bigint unsigned if it's incrementing from zero)