mysql 的count(*)统计结果很慢？为什么

最新推荐文章于 2024-02-05 09:39:42 发布

Coding-lover

最新推荐文章于 2024-02-05 09:39:42 发布

阅读量1.2w

点赞数

分类专栏：数据库文章标签： count

数据库专栏收录该内容

100 篇文章 0 订阅

订阅专栏

MySql的count统计结果

起因：最近在学习mysql的数据库，发现在innodb表中大数据量下count(*)的统计结果实在是太慢，所以想找个办法替代这种查询，下面分享一下我查找的过程。

实践：在给出具体的结论之前，我们先看看下面的现象。

一. 创建数据库

创建数据库的表语句如下：

create database IF NOT EXISTS MY_TEST default charset utf8  COLLATE utf8_general_ci;

二. 创建User表

创建User表的语句如下，UserId为主键id，在Id和k上分别建立索引，索引的名字分为了“index_id”和“index_k”。

create table USER (
                  UserId bigint(20) unsigned not null auto_increment,
                  Id     int(10) unsigned not null default 0,
                  k       int(10) unsigned not null default 0,
                  UserName varchar(120) not null default '',
                  PRIMARY KEY  (UserId),
                  KEY index_Id (Id),
                  KEY index_k (k)
)Engine=InnoDB  DEFAULT CHARSET=UTF8;

查看User上的索引，查询结果如下：

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| user  |          0 | PRIMARY  |            1 | UserId      | A         |     4
041613 |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | index_Id |            1 | Id          | A         |     4
041613 |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | index_k  |            1 | k           | A         |     4
041613 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
3 rows in set (1.30 sec)

从上表中我们可以看到user表上有3个索引，分别为主键Primary索引、、二级索引index_Id和index_k。

三. 在User表上比较查询统计

1.直接count(*)统计

mysql> explain select count(*) from user;
+----+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | index_Id | 4       | NULL | 4041613 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
1 row in set (0.04 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  4058181 |
+----------+
1 row in set (2.50 sec)

在这里使用select count(*) 的时候，默认走的索引是index_Id。虽然user表上有主键索引，但是分析引擎计算的结果需要走“index_Id”索引（有的时候走主键索引），“4041613”这个数字说明分析引擎认为能够提取到正确的结果之前需要扫描“4041613”行索引。Rows参数表明该查询所使用的索引的索引长度，index_Id的索引长度为“4”；

2. 主键字段做条件查询count(*)

这里我们查询所有Userid大于0的记录，我们来看看查询情况。

mysql> explain select count(*) from user where UserId>0;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 8       | NULL | 2020806 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.13 sec)

mysql> select count(*) from user where UserId>0;
+----------+
| count(*) |
+----------+
|  4058181 |
+----------+
1 row in set (15.39 sec)

当我们加上主键条件的时候，我们可以看到本次查询走的索引是“Primary”主键索引，我们可以看到此次请求需要15.39秒，比第一个查询2.50秒慢了很多。主键索引的长度为“8”。

3. 二级索引做条件查询count(*)

这里我们查询所有Id大于0的记录，我们来看下查询结果：

mysql> explain select count(*) from user where id>0;
+----+-------------+-------+-------+---------------+----------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | user  | range | index_Id      | index_Id | 4       | NULL | 1734104 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+--------------------------+
1 row in set (0.16 sec)

mysql> select count(*) from user where id>0;
+----------+
| count(*) |
+----------+
|  4058181 |
+----------+
1 row in set (2.94 sec)

（1）和（3）的查询时间都差不多，基本在2.5秒左右，因为这两个查询走的都是“index_Id”索引。但是（2）虽然走的主键索引，但是很慢，竟然用掉了15秒，index_Id的索引长度为4，主键索引的长度为8，是不是因为索引的长度影响了索引的查询效率？？先别下结论，我们再看下下面的例子。

四. 创建AnotherUser表

建立另外一张表，这张表与上一张表的区别是主键字段UserId和Id字段类型的调换过来了，主键UserId为int(10)，Id类型为bigint(20)。建表语句如下：

create table ANOTHERUSER (
          UserId  int(10) unsigned not null auto_increment,
          Id     bigint(20) unsigned not null default 0,
          k       int(10) unsigned not null default 0,
          UserName varchar(120) not null default '',
          PRIMARY KEY  (UserId),
          KEY index_Id (Id),
          KEY index_k (k)
 )Engine=InnoDB  DEFAULT CHARSET=UTF8;

五. 在AnotherUser表上比较查询统计

anotherUser表与User表的字段个数完全相同，唯一不同点在于两个表的主键id的类型不同，另外名称为“Id”的字段的类型也不同，两个表的数据记录基本相同。

1. 直接count(*)统计

mysql> explain select count(*) from anotherUser;
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | anotherUser | index | NULL          | PRIMARY | 4       | NULL | 4056379 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.80 sec)

mysql> select count(*) from anotheruser;
+----------+
| count(*) |
+----------+
|  4056400 |
+----------+
1 row in set (13.75 sec)

从上面的查询我们可以看到，count(*)在没有加任何条件的时候此次查询走的是主键索引，这个表的主键索引长度是4。优化器认为在提取到正确结果集之前大概需要扫描“4056379”行索引。

2. 主键字段做条件查询count(*)

我们来看下用主键UserId作为查询条件的情况，下面是查询的代码：

mysql> explain select count(*) from anotherUser where UserId>0;
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | anotherUser | range | PRIMARY       | PRIMARY | 4       | NULL | 2028189 | Using where; Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.04 sec)

mysql> select count(*) from anotherUser where UserId>0;
+----------+
| count(*) |
+----------+
|  4056400 |
+----------+
1 row in set (13.82 sec)

我们可以看到，虽然这里的主键索引的长度为4，但是查询时间基本还是在15秒左右。由此可以看出，index_Id索引查询时间比主键查询时间短并不是索引长度造成的。

3. 二级索引做条件查询count(*)

我们来测试下走Id索引，anotherUser的表Id字段是bigInt(20)。查询结果如下：

mysql> explain select count(*) from  anotherUser where Id>0;
+----+-------------+-------------+-------+---------------+----------+---------+------+---------+--------------------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows    | Extra                    |
+----+-------------+-------------+-------+---------------+----------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | anotherUser | range | index_Id      | index_Id | 8       | NULL | 1862640 | Using where; Using index |
+----+-------------+-------------+-------+---------------+----------+---------+------+---------+--------------------------+
1 row in set (0.09 sec)

mysql> select count(*) from  anotherUser where Id>0;
+----------+
| count(*) |
+----------+
|  4056400 |
+----------+
1 row in set (2.87 sec)

走二级索引index_Id只需要2.5秒左右，该索引的长度是8(比主键索引长度4大)，但是此次统计仍然要比使用主键来统计要快的多。这更加说明了两者的查询结果不同不是由两者的索引长度造成的。

六. 结论

1.没有任何条件的查询不一定走的是主键索引，mysql优化器会使用认为是最小代价的索引
2.在count(*)的时候，采用主键索引比二级索引要慢，而且慢的原因不是因为两者的索引的长度不同
3.Count(*)在没有查询条件的情况下，对innodb引擎的mysql会进行全表扫描，而myasm引擎的mysql无需进行全表扫描，因为myasm的引擎记录了每个表的多少记录。但是当有查询条件的时候，两者的查询效率一致。
4.经过后来查询大量的资料，主键索引count(*)的时候之所以慢
- InnoDB引擎
[1] 数据文件和索引文件存储在一个文件中，主键索引默认直接指向数据存储位置。
[2] 二级索引存储指定字段的索引，实际的指向位置是主键索引。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，所以主键索引统计没有二级索引效率高。
[3] 由于主键索引直接指向实际数据，所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。
- MyAsm引擎
[1] 该引擎把每个表都分为几部分存储，比如用户表，包含user.frm，user.MYD和user.MYI。
[2] User.frm负责存储表结构
[3] User.MYD负责存储实际的数据记录，所有的用户记录都存储在这个文件中
[4] User.MYI负责存储用户表的所有索引，这里也包括主键索引。

5.MyAsm引擎不支持事务处理，没有仔细深入研究。两种引擎各有自己的使用场景，每个引擎的特点也不尽相同，感兴趣的你可以再仔细深入研究。

转载自：http://blog.csdn.net/wgw335363240/article/details/6295906

Coding-lover

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql 的count(*)统计结果很慢？为什么

MySql的count统计结果起因：最近在学习mysql的数据库，发现在innodb表中大数据量下count(*)的统计结果实在是太慢，所以想找个办法替代这种查询，下面分享一下我查找的过程。实践：在给出具体的结论之前，我们先看看下面的现象。一. 创建数据库创建数据库的表语句如下：create database IF NOT EXISTS MY_TEST default charset utf8 C
复制链接

扫一扫