面试题：MySQL中COUNT()查询优化策略之索引使用

Question

在MySQL中，对于COUNT(*)查询，什么样的索引策略有助于优化查询性能？请举例说明如何创建合适的索引来加速COUNT(*)操作。

Answer 1

1. 有助于优化COUNT(*)查询的索引策略：
   • 当表中数据量较大时，覆盖索引是优化COUNT(*)查询的有效策略。覆盖索引意味着查询所需要的数据都可以从索引中获取，而不需要回表操作。对于COUNT(*)这种不需要具体数据列，只关心行数的查询，如果索引包含了表中的所有列（聚簇索引本质上就是这样，它包含了所有数据列），或者创建的二级索引包含了所有可能用于过滤的列，就可以直接通过索引快速统计行数，避免全表扫描。
   • 另一种情况，如果查询中有WHERE条件，在条件列上创建索引可以快速定位满足条件的行，进而加速COUNT(*)操作。例如，如果经常执行SELECT COUNT(*) FROM table_name WHERE column1 = 'value';，那么在column1上创建索引会提升查询性能。
2. 举例创建合适索引加速COUNT(*)操作：
   • 创建聚簇索引（InnoDB表默认以主键作为聚簇索引）：

     CREATE TABLE example_table (
         id INT PRIMARY KEY AUTO_INCREMENT,
         column1 VARCHAR(255),
         column2 INT
     );
     

     这里的主键id形成聚簇索引，当执行SELECT COUNT(*) FROM example_table;时，InnoDB可以直接通过聚簇索引快速统计行数，因为聚簇索引包含了表中的所有数据行。
   • 创建二级索引用于带条件的COUNT(*)查询： 假设表example_table已经存在，现在经常执行SELECT COUNT(*) FROM example_table WHERE column1 = 'value';，可以在column1上创建索引：

     CREATE INDEX idx_column1 ON example_table(column1);
     

     这样在执行上述COUNT(*)查询时，MySQL可以利用idx_column1索引快速定位满足column1 = 'value'的行并统计数量，而不需要扫描全表。