普通查询比count(*)更快

Question

我希望能够进行如下计数（这只是一个示例，实际上不会计算狗的数量）：

SELECT COUNT(*)
FROM dogs AS d INNER JOIN races AS r ON d.race_id = r.race_id
    LEFT INNER colors AS c ON c.color_id = r.color_id
WHERE d.deceased = 'N'

我有一张MyISAM表格，里面有130,000只狗。Races表格有1,500条记录，是一张InnoDB表格，有9列；Colors表格有83条记录，也是InnoDB表格，有两列（id和name）。 *_id列都是主键，我在dogs.race_id和races.color_id的“外键”上建立了索引，并在dogs.deceased上建立了一个索引。所有提到的列都不能为NULL。

# mysql --version
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

现在的情况是：在我的PhpMyAdmin中，这个查询需要1.8秒（使用SQL_NO_CACHE），结果为64,315。将COUNT(*)更改为COUNT(d.dog_id)或COUNT(d.deceased)也需要1.8秒来运行相同的结果。

但是，当我删除COUNT()，只执行SELECT *或SELECT dog_id时，它只需要约0.004秒来运行（然后使用类似mysql_num_rows()的东西计算结果）。

这是怎么回事？我该如何使COUNT()更快地工作？

编辑：下面添加了一个EXPLAIN。

EXPLAIN SELECT COUNT(*)
FROM dogs AS d INNER JOIN races AS r ON d.race_id = r.race_id
    INNER JOIN colors AS c ON c.color_id = r.color_id
WHERE d.deceased = 'N'

给我：

+----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref                  | rows | Extra       |
+----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | c     | index | color_id         | color_id | 4       | NULL                 |   83 | Using index | 
|  1 | SIMPLE      | r     | ref   | PRIMARY,color_id | color_id | 4       | database.c.color_id  |   14 | Using index | 
|  1 | SIMPLE      | d     | ref   | race_id,deceased | race_id  | 4       | database.r.race_id   |  123 | Using where | 
+----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+

- 7ochem

---

可能是重复的问题：COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? - John Woo

是的，我已经阅读了那篇文章。这个问题更像是“SELECT COUNT(*) vs. SELECT *”。 - 7ochem

Answer 1

MySQL优化器仅在必要时执行完整表扫描，因为某些列可以为空，这意味着如果该列未定义为NOT NULL，则其中可能存在一些NULL值，因此MySQL必须执行表扫描以查找。如果您的列可为空，则尝试在另一个不可为空的列上运行计数，这应该比count(*)提供更好的性能。

- CloudyMarble

Answer 2

创建索引以加快计数速度：

CREATE INDEX ix_temp ON dogs (d.race_id)
INCLUDE (columns needed for the query)

- Nik Drosakis

---

你确定能在多张表上创建索引吗？... ON dogs (d.race_id, r.race_id) ... d.race_id 属于 dogs 表，但是 r.race_id 属于 races 表。 - 7ochem

Answer 3

尝试在dogs.deceased上设置索引，并使用SELECT COUNT(*) ... USE INDEX my_index_name。

- mrks

---

我已经在dogs.deceased上建立了一个索引。我将查询更改为SELECT COUNT(*) FROM dogs AS d USE INDEX (idx_deceased) LEFT JOIN ...，但是仍然显示“查询花费2.9秒”。无论如何，感谢您的建议！ - 7ochem