Amazon Aurora MySQL非空字段COUNT查询变慢问题及优化咨询

问题背景 #

我在AWS Aurora MySQL（常规MySQL也存在此问题）中尝试加快InnoDB表的总行数统计速度。有一个带索引的VARCHAR字段zCreator（非主键，主键为独立字段id），实际所有行该字段均非空，此时执行count(z_creator)速度较快：

mysql> show indexes from sync_fmlog;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sync_fmlog |          0 | PRIMARY  |            1 | ID          | A         |      321352 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sync_fmlog |          1 | creator  |            1 | z_Creator   | A         |         257 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.11 sec)

mysql> select count(z_creator) from sync_fmlog;
+------------------+
| count(z_creator) |
+------------------+
|           350849 |
+------------------+
1 row in set (0.79 sec)

mysql> explain select count(z_Creator) from sync_fmlog;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sync_fmlog | NULL       | index | NULL          | creator | 258     | NULL | 254782 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.04 sec)

但执行以下语句将该字段设为非空后：

alter table sync_fmlog modify z_Creator varchar(255) not null;

统计操作变得异常缓慢，尽管返回结果一致：

mysql> show indexes from sync_fmlog;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sync_fmlog |          0 | PRIMARY  |            1 | ID          | A         |      321352 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sync_fmlog |          1 | creator  |            1 | z_Creator   | A         |         257 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.30 sec)

mysql> select count(z_creator) from sync_fmlog;
+------------------+
| count(z_creator) |
+------------------+
|           350849 |
+------------------+
1 row in set (13.14 sec)

mysql> explain select count(z_Creator) from sync_fmlog;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sync_fmlog | NULL       | index | NULL          | creator | 257     | NULL | 306471 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.04 sec)

疑问 #

1. 为什么字段设为NOT NULL后，count(z_creator)的执行速度大幅下降？
2. 有没有高效获取InnoDB表准确总行数的方法？

原因分析 #

当zCreator字段允许为NULL时，count(z_creator)会跳过NULL值，InnoDB优化器会选择遍历creator索引——因为索引中会存储NULL标记，统计时只需计数非NULL条目，执行成本较低。但字段改为NOT NULL后，count(z_creator)等价于count(*)，此时优化器的选择逻辑发生变化：

• InnoDB会优先选择最小的索引执行count操作，因为更小的索引意味着更少的IO开销。但你的creator索引基数极低（仅257），说明该字段重复值极多，InnoDB的B+Tree索引中大量重复值会降低索引节点的存储密度，实际遍历这个索引的开销反而比遍历主键索引更大。
• 另外，执行ALTER TABLE修改字段约束后，可能触发了索引重建或统计信息更新，优化器选择了看似更小但实际遍历成本更高的索引，最终导致执行时间变长。

高效统计总行数的方法 #

1. 强制使用更优索引 #

如果确定主键索引（或其他紧凑索引）遍历更快，可以用FORCE INDEX强制优化器选择：

SELECT count(z_creator) FROM sync_fmlog FORCE INDEX(PRIMARY);

或者直接针对主键索引执行count(*)：

SELECT count(*) FROM sync_fmlog FORCE INDEX(PRIMARY);

2. 使用近似统计信息（极速） #

如果业务可以接受近似行数，可查询INFORMATION_SCHEMA.TABLES获取缓存的统计数据，速度极快：

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '你的数据库名' 
AND TABLE_NAME = 'sync_fmlog';

注意：该值为近似值，受统计信息更新频率影响，可手动执行ANALYZE TABLE sync_fmlog;更新统计数据。

3. 维护独立计数表（精确实时） #

若需要精确且实时的行数，可维护一张专门的计数表：

• 插入数据时，同步给计数表的计数加1；
• 删除数据时，同步给计数表的计数减1；
• 用事务保证操作原子性，或用触发器自动维护（触发器可能影响写入性能，需谨慎评估）。

4. 选择最紧凑的索引执行count #

InnoDB执行count(*)时会自动选择最小的索引，因此如果表中有窄字段的非唯一索引（如TINYINT、SMALLINT类型字段的索引），其大小远小于主键索引或宽字段索引，用它统计会更高效。

内容的提问来源于stack exchange，提问作者Jesse Barnum