您好,匿名用户

MySQL COUNT(*) 用主键索引为何效率很低?

0 投票

文件表五百多万行数据,起初没有索引,执行 SELECT COUNT(*) FROM file; 速度极慢,大概二十几秒。
后来为 filenamesize 添加了普通索引,SELECT COUNT(*) FROM file; 查询只需要不到1秒,使用 idx_size 索引。
又为 id 添加了唯一索引,查询时间依旧不到1s,索引改用 unq_id

SELECT COUNT(*) FROM file; 使用 PRIMARY 索引效率为啥这么低?那么以后需要 COUNT(*) 的表是否都为主键添加唯一索引会比较好?


文件表结构
Field Type Null Key Extra
id int(11) NO PRI auto_increment
filename varchar(255) NO
size bigint(20) unsigned NO MUL

  • 没有建立任何索引时,查询时间是23秒,使用PRIMARY索引:
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+
1 row in set (23.39 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | pan_file | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4806466 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.07 sec)

  • filenamesize 添加了普通索引后,查询时间降为0.95s,使用 idx_size 索引
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+
1 row in set (0.95 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | pan_file | NULL       | index | NULL          | idx_size | 8       | NULL | 4806466 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
1 row in set (0.06 sec)

  • 又为 id 添加了唯一索引,查询速度为 0.90s,索引改用 unq_id
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+
1 row in set (0.90 sec)

mysql> explain SELECT SQL_NO_CACHE COUNT(*) FROM pan_file;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | pan_file | NULL       | index | NULL          | unq_id | 4       | NULL | 4806466 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+
1 row in set (0.06 sec)
用户头像 提问 4 天 @ Jax 中士 (1,214 威望)
分享到:

1个回答

0 投票
 
最佳答案

因为主键索引树的叶子节点是数据,二级索引树的叶子节点是主键值,所以二级索引树比主键索引树小很多。对于 count(*) 操作,优化器会找到最小的那棵树来遍历,所以当你有建立二级索引的时候,速度自然就快了

用户头像 回复 4 天 @ Hecarim 上士 (1,561 威望)
选中 5月12日 @Jax
提一个问题:

相关问题

0 投票
1 回复 114 阅读
用户头像 提问 2017年 6月3日 @ Wukong 上士 (1,730 威望)
0 投票
1 回复 1 阅读
用户头像 提问 5月6日 @ Caitlyn 上士 (1,762 威望)
0 投票
0 回复 105 阅读
用户头像 提问 2014年 3月19日 @ Morgana 中士 (1,062 威望)
0 投票
1 回复 2 阅读

欢迎来到随意问技术百科, 这是一个面向专业开发者的IT问答网站,提供途径助开发者查找IT技术方案,解决程序bug和网站运维难题等。
温馨提示:本网站禁止用户发布与IT技术无关的、粗浅的、毫无意义的或者违法国家法规的等不合理内容,谢谢支持。

欢迎访问随意问技术百科,为了给您提供更好的服务,请及时反馈您的意见。
...