MySQL InnoDB 存储引擎 count加速
SELECT COUNT(1) from table USE INDEX (UNIQUE KEY);
比 使用主键索引要快,我测试的是1000W生产环境数据,快了至少3倍.....
- EXPLAIN SELECT COUNT(1) from `order` USE INDEX (PRIMARY)\G;
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: order
- type: index
- possible_keys: NULL
- key: PRIMARY
- key_len: 8
- ref: NULL
- rows: 1
- Extra: Using index
- 1 row in set (0.01 sec)
|
- EXPLAIN SELECT COUNT(1) from `order` USE INDEX (UNQ_p)\G;
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: order
- type: index
- possible_keys: NULL
- key: UNQ_p
- key_len: 99
- ref: NULL
- rows: 1
- Extra: Using index
- 1 row in set (0.00 sec)
|
这个非聚集索引比主键索引还列宽还长些....
- SELECT COUNT(1) from `order` USE INDEX (UNQ_p);
- +
- | COUNT(1) |
- +
- | 10984918 |
- +
- 1 row in set (36.60 sec)
|
- SELECT COUNT(1) from `order` USE INDEX (PRIMARY);
- +
- | COUNT(1) |
- +
- | 10984918 |
- +
- 1 row in set (1 min 31.57 sec)
|
评论暂时关闭