# MySQL COUNT(*) 优化COUNT(*) 是最常用的聚合函数但数据量大时COUNT(*) 可能跑得很慢。这篇说说怎么优化。## COUNT(*) 的原理sql-- MySQL 优化器会根据情况选择不同的计数方式SELECT COUNT(*) FROM order;-- 可能走索引扫描也可能全表扫描EXPLAIN SELECT COUNT(*) FROM order;优化器选择策略1. 如果有主键或 NOT NULL 字段扫描主键索引2. 如果有 COUNT(*) 专用优化用最小索引3. 如果什么索引都没有全表扫描## 为什么 COUNT(*) 慢sql-- 全表扫描InnoDB 要一行一行读出来计数SELECT COUNT(*) FROM order; -- 1000万行要读1000万次-- 走索引只读索引树不读数据SELECT COUNT(*) FROM order; -- 如果有主键 id只读主键索引全表扫描要读数据页索引扫描只要读索引树。索引树比数据小很多所以快很多。## 优化方案### 1. 加主键或索引sql-- 最简单的优化确保有主键ALTER TABLE order MODIFY COLUMN id BIGINT PRIMARY KEY AUTO_INCREMENT;-- 或者建一个专用索引CREATE INDEX idx_id ON order(id); -- 主键本来就有索引不用单独建-- COUNT(*) 会自动用主键索引最小索引### 2. 用主键 COUNT 而不是全表 COUNTsql-- 优化只 COUNT 主键列如果有索引SELECT COUNT(id) FROM order; -- 走主键索引### 3. 用条件 COUNT 减少扫描范围sql-- ❌ 慢COUNT 全表SELECT COUNT(*) FROM order;-- ✅ 快加条件只扫部分SELECT COUNT(*) FROM order WHERE status completed;-- 如果 status 有索引效果更好CREATE INDEX idx_status ON order(status);### 4. 缓存计数对于不要求精确的数据可以用 Redis 缓存计数。java// 写入时同步更新 Redispublic void createOrder(Order order) {orderMapper.insert(order);redisTemplate.opsForValue().increment(order:count); // 同步更新计数}// 读取时从缓存拿public long getOrderCount() {Long count redisTemplate.opsForValue().get(order:count);return count ! null ? count : orderMapper.countAll();}### 5. 统计表对于精确但不实时允许延迟几分钟的需求用统计表。sql-- 订单统计表CREATE TABLE order_stats (stat_date DATE PRIMARY KEY,total_count BIGINT DEFAULT 0,total_amount DECIMAL(15, 2) DEFAULT 0);-- 每天跑一次统计INSERT INTO order_stats (stat_date, total_count, total_amount)SELECT CURRENT_DATE, COUNT(*), SUM(amount) FROM orderON DUPLICATE KEY UPDATEtotal_count VALUES(total_count),total_amount VALUES(total_amount);### 6. INFORMATION_SCHEMA快速查看表行数不精确。sql-- 快速查看近似行数SELECT TABLE_ROWS FROM information_schema.TABLESWHERE TABLE_NAME order; -- 近似值可能有偏差## 不同 COUNT 用法对比| 用法 | 含义 | 性能 ||------|------|------|| COUNT(*) | 统计所有行包括 NULL | 最慢 || COUNT(1) | 统计所有行忽略 1 | 和 COUNT(*) 差不多 || COUNT(id) | 统计非 NULL 的 id | 快走索引 || COUNT(col) | 统计非 NULL 的列 | 取决于有没有索引 |sql-- 性能对比SELECT COUNT(*) FROM order; -- 全表扫描SELECT COUNT(1) FROM order; -- 全表扫描和上面差不多SELECT COUNT(id) FROM order; -- 走主键索引快SELECT COUNT(status) FROM order; -- 走 status 索引如果有否则全表## 实战建议sql-- 查看当前 COUNT 性能EXPLAIN SELECT COUNT(*) FROM order;-- type index扫索引树OK-- type ALL全表扫描要优化### 常见场景sql-- 场景1统计总订单数精确SELECT COUNT(*) FROM order; -- 定期COUNT不要实时查-- 场景2统计已完成订单数精确CREATE INDEX idx_status ON order(status);SELECT COUNT(*) FROM order WHERE status completed;-- 场景3统计用户总数精确SELECT COUNT(DISTINCT user_id) FROM order;## 小结| 优化方法 | 适用场景 ||----------|----------|| 加主键/索引 | 所有 COUNT(*) || 加 WHERE 条件 | 有过滤条件的统计 || Redis 缓存 | 不要求精确的实时计数 || 统计表 | 允许几分钟延迟的统计 || INFORMATION_SCHEMA | 快速查看近似值 |COUNT(*) 优化的核心就两点**让查询走索引****减少扫描行数**。---**相关阅读**- [MySQL 索引底层 B 树原理]- [MySQL 慢查询优化实战]- [MySQL 执行计划 EXPLAIN 详解]
MySQL COUNT优化
# MySQL COUNT(*) 优化COUNT(*) 是最常用的聚合函数但数据量大时COUNT(*) 可能跑得很慢。这篇说说怎么优化。## COUNT(*) 的原理sql-- MySQL 优化器会根据情况选择不同的计数方式SELECT COUNT(*) FROM order;-- 可能走索引扫描也可能全表扫描EXPLAIN SELECT COUNT(*) FROM order;优化器选择策略1. 如果有主键或 NOT NULL 字段扫描主键索引2. 如果有 COUNT(*) 专用优化用最小索引3. 如果什么索引都没有全表扫描## 为什么 COUNT(*) 慢sql-- 全表扫描InnoDB 要一行一行读出来计数SELECT COUNT(*) FROM order; -- 1000万行要读1000万次-- 走索引只读索引树不读数据SELECT COUNT(*) FROM order; -- 如果有主键 id只读主键索引全表扫描要读数据页索引扫描只要读索引树。索引树比数据小很多所以快很多。## 优化方案### 1. 加主键或索引sql-- 最简单的优化确保有主键ALTER TABLE order MODIFY COLUMN id BIGINT PRIMARY KEY AUTO_INCREMENT;-- 或者建一个专用索引CREATE INDEX idx_id ON order(id); -- 主键本来就有索引不用单独建-- COUNT(*) 会自动用主键索引最小索引### 2. 用主键 COUNT 而不是全表 COUNTsql-- 优化只 COUNT 主键列如果有索引SELECT COUNT(id) FROM order; -- 走主键索引### 3. 用条件 COUNT 减少扫描范围sql-- ❌ 慢COUNT 全表SELECT COUNT(*) FROM order;-- ✅ 快加条件只扫部分SELECT COUNT(*) FROM order WHERE status completed;-- 如果 status 有索引效果更好CREATE INDEX idx_status ON order(status);### 4. 缓存计数对于不要求精确的数据可以用 Redis 缓存计数。java// 写入时同步更新 Redispublic void createOrder(Order order) {orderMapper.insert(order);redisTemplate.opsForValue().increment(order:count); // 同步更新计数}// 读取时从缓存拿public long getOrderCount() {Long count redisTemplate.opsForValue().get(order:count);return count ! null ? count : orderMapper.countAll();}### 5. 统计表对于精确但不实时允许延迟几分钟的需求用统计表。sql-- 订单统计表CREATE TABLE order_stats (stat_date DATE PRIMARY KEY,total_count BIGINT DEFAULT 0,total_amount DECIMAL(15, 2) DEFAULT 0);-- 每天跑一次统计INSERT INTO order_stats (stat_date, total_count, total_amount)SELECT CURRENT_DATE, COUNT(*), SUM(amount) FROM orderON DUPLICATE KEY UPDATEtotal_count VALUES(total_count),total_amount VALUES(total_amount);### 6. INFORMATION_SCHEMA快速查看表行数不精确。sql-- 快速查看近似行数SELECT TABLE_ROWS FROM information_schema.TABLESWHERE TABLE_NAME order; -- 近似值可能有偏差## 不同 COUNT 用法对比| 用法 | 含义 | 性能 ||------|------|------|| COUNT(*) | 统计所有行包括 NULL | 最慢 || COUNT(1) | 统计所有行忽略 1 | 和 COUNT(*) 差不多 || COUNT(id) | 统计非 NULL 的 id | 快走索引 || COUNT(col) | 统计非 NULL 的列 | 取决于有没有索引 |sql-- 性能对比SELECT COUNT(*) FROM order; -- 全表扫描SELECT COUNT(1) FROM order; -- 全表扫描和上面差不多SELECT COUNT(id) FROM order; -- 走主键索引快SELECT COUNT(status) FROM order; -- 走 status 索引如果有否则全表## 实战建议sql-- 查看当前 COUNT 性能EXPLAIN SELECT COUNT(*) FROM order;-- type index扫索引树OK-- type ALL全表扫描要优化### 常见场景sql-- 场景1统计总订单数精确SELECT COUNT(*) FROM order; -- 定期COUNT不要实时查-- 场景2统计已完成订单数精确CREATE INDEX idx_status ON order(status);SELECT COUNT(*) FROM order WHERE status completed;-- 场景3统计用户总数精确SELECT COUNT(DISTINCT user_id) FROM order;## 小结| 优化方法 | 适用场景 ||----------|----------|| 加主键/索引 | 所有 COUNT(*) || 加 WHERE 条件 | 有过滤条件的统计 || Redis 缓存 | 不要求精确的实时计数 || 统计表 | 允许几分钟延迟的统计 || INFORMATION_SCHEMA | 快速查看近似值 |COUNT(*) 优化的核心就两点**让查询走索引****减少扫描行数**。---**相关阅读**- [MySQL 索引底层 B 树原理]- [MySQL 慢查询优化实战]- [MySQL 执行计划 EXPLAIN 详解]
