优化MySQL计数查询

Question

有没有办法进一步优化这个操作，或者我应该对需要 9 秒来计算 1100 万行感到满意？

devuser@xcmst > mysql --user=user --password=pass -D marctoxctransformation -e "desc record_updates"                                                                    
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| record_id    | int(11)  | YES  | MUL | NULL    |       | 
| date_updated | datetime | YES  | MUL | NULL    |       | 
+--------------+----------+------+-----+---------+-------+
devuser@xcmst > date; mysql --user=user --password=pass -D marctoxctransformation -e "select count(*) from record_updates where date_updated > '2009-10-11 15:33:22' "; date                         
Thu Dec  9 11:13:17 EST 2010
+----------+
| count(*) |
+----------+
| 11772117 | 
+----------+
Thu Dec  9 11:13:26 EST 2010
devuser@xcmst > mysql --user=user --password=pass -D marctoxctransformation -e "explain select count(*) from record_updates where date_updated > '2009-10-11 15:33:22' "      
+----+-------------+----------------+-------+--------------------------------------------------------+--------------------------------------------------------+---------+------+----------+--------------------------+
| id | select_type | table          | type  | possible_keys                                          | key                                                    | key_len | ref  | rows     | Extra                    |
+----+-------------+----------------+-------+--------------------------------------------------------+--------------------------------------------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | record_updates | index | idx_marctoxctransformation_record_updates_date_updated | idx_marctoxctransformation_record_updates_date_updated | 9       | NULL | 11772117 | Using where; Using index | 
+----+-------------+----------------+-------+--------------------------------------------------------+--------------------------------------------------------+---------+------+----------+--------------------------+
devuser@xcmst > mysql --user=user --password=pass -D marctoxctransformation -e "show keys from record_updates"
+----------------+------------+--------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name                                               | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+--------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| record_updates |          1 | idx_marctoxctransformation_record_updates_date_updated |            1 | date_updated | A         |        2416 |     NULL | NULL   | YES  | BTREE      |         | 
| record_updates |          1 | idx_marctoxctransformation_record_updates_record_id    |            1 | record_id    | A         |    11772117 |     NULL | NULL   | YES  | BTREE      |         | 
+----------------+------------+--------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

- andersonbd1

---

引擎是什么 - MyISAM 还是 InnoDB？ - Unreason

假设您的表中有1100万行，但平均每天实际上更新了多少条记录？或者说这1100万行是正在更新的数据。如果每天有1100万次更新操作，建议使用汇总表。 - DRapp

引擎是MyISAM。总结表对此无效 - 数据在不断变化，查询可能是任何东西（每秒钟）。 - andersonbd1

每秒计算1,222,222.2行。也许我只是一个贫穷、不幸的网页脚本编写者，但我会毫不犹豫地接受这些结果。 - bob-the-destroyer

你能和我们分享一些关于这个查询的背景吗？看起来你想通过简单的“调整”技术来解决过大的改进问题。你需要利用表的使用模式才能在不到一秒的时间内完成。 - Ronnis

Answer 1

如果 MySQL 必须计数 1100 万行数据，没有太多方法可以加快简单计数的速度，至少不能让它在 1 秒内完成。您应该重新考虑如何进行计数，以下是一些想法：

1. 在表中添加自动递增字段。看起来您不会从表中删除数据，因此可以使用简单的数学运算来查找记录数。选择最早日期的最小自动递增编号和最后日期的最大自动递增编号，然后将后者减去前者即可得到记录数。例如：

   SELECT min(incr_id) min_id FROM record_updates WHERE date_updated BETWEEN '2009-10-11 15:33:22' AND '2009-10-12 23:59:59';
   SELECT max(incr_id) max_id FROM record_updates WHERE date_updated > DATE_SUB(NOW(), INTERVAL 2 DAY);`
   

2. 创建另一个表，总结每天的记录计数。然后您可以查询该表以获取总记录数。每年只会有365条记录。如果需要更精细的时间，请查询摘要表以获取完整的一天数据，并从当前表中仅获取开始和结束日期的记录计数。然后将它们全部加起来。

如果数据不会改变，看起来确实是这样的话，那么摘要表将很容易维护和更新。它们将显着加快速度。

- Brent Baisley

---

+1 对于摘要表建议。在这种情况下，您可以选择非规范化一些较小的信息，以生成所需的数字。但是，在正确维护冗余数据方面一定要非常小心。 - Randy

摘要可以，自动递增不行——它不能保证连续。 - Unreason

抱歉让您久等了，我出城了。那些是好的建议，但对我来说行不通，因为数据是实时更新的，查询可能是任何数据到任何其他数据（每秒钟都在变化）。我目前正在研究将索引加载到内存中。 - andersonbd1

仅当数据集本身和所需计算字段集永远不会更改时，摘要表才能正常工作。而众所周知，需求总是在变化。 - bob-the-destroyer

@anderson，当您更新基础数据时，能否同时更新摘要？如果能保持摘要同步，那么“按秒”查询就不是问题了（请参见我下面的回答）。 - user533832

对于那些不需要删除任何行的表格，为其进行简单的数学计算加1。 - Randell

Answer 2

由于>'2009-10-11 15:33:22'包含大部分记录，建议进行反向匹配，如<'2009-10-11 15:33:22'（MySQL工作量较少，涉及的行数也较少）。

select 
  TABLE_ROWS -
  (select count(*) from record_updates where add_date<"2009-10-11 15:33:22") 
from information_schema.tables 
where table_schema = "marctoxctransformation" and table_name="record_updates"

你可以与编程语言（如bash shell）结合使用，
使这个计算变得更加智能...
例如，首先执行执行计划以计算哪个比较将使用更少的行。

从我的测试结果来看（约1000万条记录），
普通比较需要大约3秒钟，
现在缩短到了约0.25秒钟。

- ajreal

Answer 3

MySQL不会在InnoDB中“优化”count(*)查询，因为版本控制。必须遍历索引中的每个项目并检查其版本是否正确以进行显示（例如，不是打开提交）。由于数据库中的任何数据都可以修改，所以范围选择和缓存无法工作。但是，您可能可以使用触发器。有两种方法可以实现这种疯狂的方法。

第一种方法冒着减慢事务速度的风险，因为它们无法真正并行运行：使用插入后和删除后触发器来增加/减少计数器表。第二种技巧：使用这些插入/删除触发器调用一个存储过程，该存储过程馈入一个外部程序，类似地调整值上下或对非事务性表进行操作。请注意，在回滚的情况下，这将导致不准确的数字。

如果您不需要精确的数字，请查看此查询：

select table_rows from information_schema.tables
where table_name = 'foo';

示例的区别：count(*)：1876668， table_rows：1899004。table_rows值是一个估计值，即使您的数据库没有更改，每次都会得到不同的数字。

出于我的好奇心：您需要每秒更新的精确数字吗？如果是这样，为什么？

- Jeff Ferland

Answer 4

如果历史数据不易变，则创建摘要表。有多种方法可选，选择哪种方法将取决于您的表格更新方式以及更新频率。

例如，假设旧数据很少/从未更改，但最近的数据是，创建一个月度摘要表，在每个月末填充上个月的数据（例如在二月底插入一月份的计数器）。一旦您拥有摘要表，您可以在范围的开始和结束处加总全月和部分月份：

select count(*) 
from record_updates 
where date_updated >= '2009-10-11 15:33:22' and date_updated < '2009-11-01';

select count(*) 
from record_updates 
where date_updated >= '2010-12-00';

select sum(row_count) 
from record_updates_summary 
where date_updated >= '2009-11-01' and date_updated < '2010-12-00';

为了清晰起见，我将其拆分成了上面的形式，但你可以在一次查询中完成此操作：

select ( select count(*)
         from record_updates 
         where date_updated >= '2010-12-00'
               or ( date_updated>='2009-10-11 15:33:22' 
                    and date_updated < '2009-11-01' ) ) +
       ( select count(*) 
         from record_updates 
         where date_updated >= '2010-12-00' );

您可以根据整个星期或整天的情况来调整此方法，制作汇总表。

- user533832

Answer 5

您应该在“date_updated”字段上添加索引。

如果您不介意更改表的结构，另一件可以做的事情是使用日期的时间戳作为“int”而不是“datetime”格式，这可能会更快。 如果您决定这样做，查询将会是：

select count(date_updated) from record_updates where date_updated > 1291911807

- Doron

---

从我所看到的，date_updated 字段上已经有一个索引。 - a1ex07

是的，该列已经有一个索引了......您是建议我更改索引的某些内容吗？ - andersonbd1

关于将日期时间更改为整数的第二个问题...我在另一个表/列上遇到了类似的问题，它并没有表现得更快。 - andersonbd1

即使存储为其他类型的字段可以更快，但建议将日期时间存储为datetime字段。因为代价巨大，而好处微乎其微（如果有的话）。 - user533832

所以我尝试了一下（8字节到4字节）。时间从9秒降至7秒。但这个提升不足以让我增加复杂度。 - andersonbd1

Answer 6

这取决于几件事情，但这样可能适合您

我假设这个计数永远不会改变，因为它是过去的，所以结果可以以某种方式被缓存

count1 = "select count(*) from record_updates where date_updated <= '2009-10-11 15:33:22'"

给出表中记录的总数，这是InnoDB表中的近似值，所以请注意，它取决于引擎。

count2 = "select table_rows from information_schema.`TABLES` where table_schema = 'marctoxctransformation' and TABLE_NAME = 'record_updates'"

你的答案

结果 = count2 - count1

- niallo

Answer 7

你的表中没有主键。在这种情况下，它可能总是扫描整个表。拥有主键从来不是一个坏主意。

- a1ex07

Answer 8

如果您需要返回整个表的行数，那么有一个替代方案可以使用，而不是使用SELECT COUNT(*)语句。SELECT COUNT(*)会对整个表进行扫描以返回总行数，所以可能会花费很长时间。在这种情况下，您可以使用sysindexes系统表作为替代方案。在sysindexes表中有一列ROWS，它包含了数据库中每个表的总行数。因此，您可以使用以下select语句来代替SELECT COUNT(*)： SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 这样可以提高查询速度。 编辑：我发现我的答案只适用于使用SQL Server数据库，MySQL数据库没有sysindexes表。

- THE DOCTOR

---

@zedo 即使在 SQL Server 上，如何处理“where date_updated > '2009-10-11 15:33:22'”？ - user533832

我只会在WHERE子句中再添加一个AND。但是，我不确定sysindexes中的时间戳是否与您要查看的实际表相符。 - THE DOCTOR

@zedo 怎么可能呢？每个索引在 sysindexes 中只有 1 行。而且 rows 字段的值是一个估计值。 - user533832

行数字段的值不仅仅是根据数据库上次更新统计信息的时间估算出来的。如果 DBCC SHOW_STATISTICS (table_name, index_name) 命令指示其已过时，则可以运行以下命令来更新统计信息：USE <database_name> EXEC sp_updatestats - THE DOCTOR

@zedo，除非您是该数据库上唯一的用户，否则仍应将其视为估计-但这只是一个旁白，因为sysindexes只能为您提供表中总行数的估计值，而这不是anderson想要的。 - user533832

如果您是DBA，并确保此信息是最新的，或者如果您在sysindexes表上启用了自动更新，则不应将其视为估计值。我从前几天的编辑已经表明，我意识到andersonbd1的MySQL数据库没有sysindexes表。 - THE DOCTOR

Answer 9

我想请您澄清一些细节（可以在问题的评论中提出，但当您更新问题时，从这里删除实际上更容易）。

1. 数据的预期用途是什么？插入一次，多次获取计数，还是您的插入和选择大致相等？
2. 您是否关心插入/更新性能？
3. 表格使用的引擎是什么？（你可以使用 SHOW CREATE TABLE ...）
4. 您需要准确的计数还是近似准确的计数（例如0.1％正确）
5. 您可以使用触发器、汇总表、更改模式、更改RDBMS等，还是只能添加/删除索引？
6. 也许您应该解释一下这个表格的作用是什么？您有一个与行数匹配的cardinality的record_id，那么它是PK还是FK或者其他什么？另外，date_updated的基数表明（虽然不一定正确）平均每个记录有相同的值约为5,000个），那是什么？-问一个SQL调优问题没有上下文是可以的，但如果重新设计是一个选项，那么有一些上下文也很好。

与此同时，我建议您获取this调整脚本并检查它将给出的建议（它只是一个通用的调整脚本 - 但它将检查您的数据和统计信息）。

- Unreason

Answer 10

不要使用 count(*)，尝试使用 count(1)，像这样：

select count(1) from record_updates where date_updated > '2009-10-11 15:33:22'

我之前上过一门DB2课程，记得讲师提到当我们只想计算表中行数而不考虑数据时，使用count(1)比count(*)更快。如果这样做有所区别，请告诉我。

注意：这里有一个链接可能会对你有用：http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

- limc

---

在Oracle中已经证明这种情况并不成立，使用count(*)和count(1)的性能是相同的。 - Randy

@Randy：在这种情况下是不是像OP中所述使用MySQL？ - Danosaure

@Danosaure - 我不确定，只是想添加一些我知道的关于Oracle的信息。我也没有一个MySQL实例来自己进行测试... :( - Randy

实际上，MySQL（ISAM而不是InnoDB）应该针对某些Count(*)查询进行优化 - 比如那些没有条件或在主键上的查询。因此，limc的这个语句应该不适用。但请随意测试。 - zanlok