之前，我记录了一下pgsql select count 的性能。今天我想来对比一下 PostgreSQL、MySQL（特别是 InnoDB 和 MyISAM 引擎）以及 IBM Db2 在 SELECT COUNT(*) 性能上的差异及其原因。

核心差异主要源于它们各自的存储引擎设计、并发控制机制（尤其是 MVCC 的实现）以及是否维护实时行计数。

1. PostgreSQL:

• 机制: 如前文详述，PostgreSQL 使用 MVCC (多版本并发控制) 来处理并发事务。这意味着表中的每一行都可能有多个版本，每个版本对不同的事务具有不同的可见性。

COUNT(*) 处理: 为了得到精确的行数，PostgreSQL 必须遍历表或相关索引，并检查每一行（或索引条目）对当前事务是否可见。它不存储一个随时可用的、全局精确的行计数元数据。

• 性能:

无 WHERE 条件的 COUNT(*) 在大表上可能很慢，因为它需要扫描大量数据并进行可见性检查。

性能高度依赖于能否进行 Index-Only Scan（需要合适的索引和良好的 VACUUM 维护）。如果能进行 Index-Only Scan，速度会快很多。否则，即使是 Index Scan 也可能需要访问表检查可见性。

1. 优点: MVCC 提供了非常好的读写并发性能和快照隔离。
2. 缺点: 无条件的 COUNT(*) 可能成为性能瓶颈。

2. MySQL:

MySQL 的行为强烈依赖于所使用的存储引擎。最常见的是 InnoDB 和（较旧的）MyISAM。

2.1 MySQL (MyISAM 引擎):

• 机制: MyISAM 是一个非事务性存储引擎，通常使用表级锁进行写操作。它不使用 MVCC。

COUNT(*) 处理: MyISAM 在表的元数据中存储了一个精确的行计数值。当执行无 WHERE 条件的 SELECT COUNT(*) FROM table_name; 时，它直接读取这个存储的值。

• 性能: 对于无 WHERE 条件的全表 COUNT(*), MyISAM 极其快速，几乎是 O(1) 复杂度，因为它不需要扫描任何数据。

1. 优点: 简单的全表 COUNT(*) 速度极快。
2. 缺点:

• 缺乏事务支持 (ACID)。
• 表级锁导致写并发性能差。
• 不是崩溃安全的。
• 带 WHERE 条件的 COUNT(*) 仍然需要扫描。
• 现代 MySQL 版本中已不再是默认引擎，InnoDB 是主流。

2.2 MySQL (InnoDB 引擎):

• 机制: InnoDB 是 MySQL 的默认事务性存储引擎，支持 ACID，使用行级锁，并且也实现了 MVCC（类似于 PostgreSQL）。

COUNT(*) 处理: 由于 MVCC 的存在，InnoDB 也不能像 MyISAM 那样简单地存储一个全局精确的行数。它也需要考虑行的可见性。因此，执行 COUNT(*) 时，InnoDB 通常需要扫描索引（首选主键索引，如果没有合适的二级索引）来统计对当前事务可见的行数。

• 性能:

无 WHERE 条件的 COUNT(*) 性能与 PostgreSQL 类似，需要进行扫描，其速度取决于表大小、索引和缓存。它通常比 MyISAM 慢得多。

InnoDB 在某些版本中对扫描主键索引进行 COUNT(*) 做了一些优化，但基本原理（需要扫描）不变。

1. 优点: 支持事务、行级锁、MVCC，并发性能和数据一致性好。
2. 缺点: 无条件的 COUNT(*) 同样可能较慢（相比 MyISAM）。

3. IBM Db2:

• 机制: Db2 是一个功能强大的企业级 RDBMS，支持事务、多种隔离级别和复杂的并发控制机制（通常基于锁，但具体实现可能因版本和配置而异）。它也有类似于 MVCC 的概念来处理数据一致性。

COUNT(*) 处理: Db2 通常也不维护一个实时、精确的全表行计数供 COUNT(*) 直接使用。为了保证事务隔离和数据一致性，它需要通过扫描来确定可见的行数。

Db2 的优化器会尝试使用最有效的索引来执行 COUNT(*)，例如选择一个列数最少、体积最小的索引进行扫描。

Db2 非常依赖统计信息（通过 RUNSTATS 命令收集）。优化器根据统计信息估算成本并选择执行计划。

• 性能:

COUNT(*) 的性能通常也依赖于扫描（表或索引）。其速度取决于表大小、索引效率、统计信息的新鲜度和准确性以及系统负载。

性能特征与 PostgreSQL 和 InnoDB 概念上相似，即需要扫描，速度不是 O(1)。

可以通过查询系统目录表（例如 SYSCAT.TABLES 中的 CARD 列）获取基于统计信息的估算行数，这通常非常快。

1. 优点: 功能强大，优化器成熟，支持复杂事务和高并发。
2. 缺点: COUNT(*) 同样需要扫描，性能受多种因素影响。准确的统计信息对性能至关重要。

总结对比:

核心 takeaway:

MyISAM 是个特例：因为它牺牲了事务和并发特性，所以能做到无条件 COUNT(*) 的 O(1) 速度。

PostgreSQL, MySQL (InnoDB), Db2 行为相似：由于它们都支持事务和某种形式的并发控制（如 MVCC 或复杂的锁机制），它们通常都需要通过扫描表或索引来获得精确的 COUNT(*) 结果，因为需要判断哪些行对当前事务是可见的。性能因此依赖于扫描效率、索引使用、缓存和系统维护（如 VACUUM 或 RUNSTATS）。

因此，当比较 COUNT(*) 性能时，理解数据库的底层存储和并发模型至关重要。不能简单地说哪个数据库“更快”，而要看具体的存储引擎、配置以及查询场景。对于需要精确、高并发事务的现代应用，PostgreSQL、InnoDB 和 Db2 的行为是更常见的模式。