跪拜 Guibai
← All articles
Backend · Interview · Java

Stop Tuning COUNT(*): Ask What the Number Is For, Then Move It Out of the Table

By Rain的Java大神之路 ·
Read original on juejin.cn ↗ Google Translate ↗ Alt translation

A single slow COUNT(*) can time out a dashboard or an order list, and most optimization attempts only move the bottleneck. Asking for precision and real-time first, then moving counting into a bucket table or Redis, keeps the query from ever scanning 80 million rows.

Summary

Counting rows in an 80-million-row InnoDB table is a classic interview question, and the first correct step is not to write SQL but to ask what the total is for. Precision, real-time, and table scale form an impossible triangle: you get at most two. If an approximate magnitude works, the optimizer's statistics table returns in milliseconds; if you need exact and frequently read, the count moves out of the table into a bucket counter or Redis projection.

Six levels of solutions run from a cheap EXPLAIN and a purpose-built narrow secondary index to parallel ID-shard counting, a 16-64 bucket counter table updated in the same transaction, a Redis counter with after-commit increments and a compensation table, and a Canal binlog listener that needs idempotency for at-least-once messaging. Each level carries code and pitfalls, including the classic soft-delete trap where DELETE becomes UPDATE and the counter drifts upward, and the requirement that Java and MySQL agree on the same bucket hash function.

The final advice: estimate when you don't need exact, async when you don't need real-time, and incremental instead of full scans. For pagination, the cheapest fix is to drop the total and use cursor-based load-more. A daily calibration against the real table is the safety net every counting system needs, because counts don't fail loudly; they drift silently.

Takeaways
— InnoDB does not store a row count; every COUNT(*) scans an index tree and filters rows by MVCC visibility, so it is O(N).
— MyISAM keeps a count in metadata and returns it in O(1), but it lacks transaction and row-level lock support.
— COUNT(*) and COUNT(1) execute identically in MySQL 5.7/8.0; COUNT(id) adds a value fetch, and COUNT(field) counts only non-NULL rows and may cause table lookups.
— A narrow secondary index can speed up COUNT(*) by 5-10x because the optimizer picks the smallest index to scan, reducing pages read.
— For approximate counts, information_schema.TABLE_ROWS samples about 20 pages and can be off by 40-50%; it suits dashboards, not money reconciliation.
— MySQL 8.0.14+ parallel scan requires no WHERE/GROUP BY/ORDER BY/LIMIT, a non-partitioned table, a cluster index scan without secondary index, and enough data; verify with EXPLAIN FORMAT=TREE.
— A bucket counter table with 16-64 buckets spreads row-lock contention; the SUM of buckets returns an exact count in milliseconds.
— Java and MySQL must use the same bucket hash (CRC32) or calibration will scatter counts and never match.
— When using Redis for count, increment after transaction commit, use INCR/DECR, log failures to a compensation table, and run a daily full calibration against the DB.
— Soft delete that changes DELETE to UPDATE without a DECR causes the counter to drift upward silently.
— Canal-based binlog counting needs an idempotent table keyed by GTID+offset to handle at-least-once MQ delivery.
— For pagination, replacing total count with cursor-based load-more eliminates the need for expensive COUNT queries entirely.
Conclusions

The first question in any COUNT optimization should be about tolerance for error, not index size. That requirement clarity decides whether you spend an afternoon on a bucket table or five minutes on an estimate.

The soft-delete drift trap is the most commonly missed failure mode because it doesn't error; it silently inflates. Any counting system must cover every mutation path, including UPDATE-based deletes.

Moving COUNT into a bucket counter shifts cost from reads to writes, but the daily calibration is what keeps it honest. Without that, any missed write or edge case accumulates forever.

The advice to replace total count with cursor pagination is the cheapest and most effective fix, but it requires product agreement. Most developers won't propose it because they assume the page-number metric is non-negotiable.

The claim that COUNT(1) and COUNT(*) are identical is correct and backed by MySQL 8.0 official docs, but the interview question still ignores it because the real bottleneck is scanning 80 million rows, not the function.

Concepts & terms
MVCC
Multi-version Concurrency Control, which lets InnoDB readers see a consistent snapshot without locking writes. It prevents a stored global row count because different transactions see different versions.
ReadView
A per-transaction snapshot of committed transactions that determines which row versions are visible during an index scan in InnoDB.
Covering index
An index that contains all columns needed by a query, so the query reads only the index pages without going back to the clustered table. For COUNT(*), a narrow secondary index reduces pages scanned.
Bucket counter
A counter table split into N buckets to spread row-lock contention under high-concurrent writes; SUM of buckets gives an exact total in milliseconds.
binlog
MySQL's binary log that records all data changes. Canal subscribes to it to capture inserts and deletes without changing business code.
ON DUPLICATE KEY UPDATE
An INSERT statement that atomically increments an existing row's column on duplicate key, used here to update bucket counters without lost updates.
Source: juejin.cn ↗ Google Translate ↗ Backup ↗