http://blog.csdn.net/wgw335363240/article/details/6295906
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(*)的时候之所以慢
l InnoDB引擎
[1] 数据文件和索引文件存储在一个文件中,主键索引默认直接指向数据存储位置。
[2] 二级索引存储指定字段的索引,实际的指向位置是主键索引。当我们通过二级索引统计数据的时候,无需扫描数据文件;而通过主键索引统计数据时,由于主键索引与数据文件存放在一起,所以每次都会扫描数据文件,所以主键索引统计没有二级索引效率高。
[3] 由于主键索引直接指向实际数据,所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。
l MyAsm引擎
[1] 该引擎把每个表都分为几部分存储,比如用户表,包含user.frm,user.MYD和user.MYI。
[2] User.frm负责存储表结构
[3] User.MYD负责存储实际的数据记录,所有的用户记录都存储在这个文件中
[4] User.MYI负责存储用户表的所有索引,这里也包括主键索引。
5. MyAsm引擎不支持事务处理,没有仔细深入研究。两种引擎各有自己的使用场景,每个引擎的特点也不尽相同,感兴趣的你可以再仔细深入研究。
分享到:
相关推荐
MySQL中的InnoDB存储引擎在处理`COUNT(*)`查询时与MyISAM存在显著差异,这主要是因为两者在数据统计机制上的不同。MyISAM引擎在每个表中维护了一个计数器,可以快速返回表中的行数,而InnoDB则没有这样的内置计数器...
3. **缓存计数**:对于频繁的`COUNT(*)`查询,可以考虑在应用层或数据库层面缓存统计结果,定期更新。 4. **分批计算**:对于非常大的表,可以将表分成多个部分,分别计算每个部分的`COUNT(*)`,然后累加。 5. **...
在MySQL数据库中,计算表行数的SQL语句`SELECT COUNT(*) FROM table`在处理大量数据时可能会变得非常慢。这是因为不同的存储引擎,如MyISAM和InnoDB,对`COUNT(*)`操作的实现方式有所不同。MyISAM引擎在磁盘上直接...
同时,学习使用聚合函数(COUNT、SUM、AVG、MIN、MAX)对数据进行统计分析。 7. **事务处理**:理解ACID(原子性、一致性、隔离性、持久性)原则,学习如何在MySQL中使用BEGIN、COMMIT、ROLLBACK语句进行事务管理。...
### MySQL面试知识点详解 #### 一、基础知识题 1. **MySQL与其他数据库管理系统(如PostgreSQL, SQL Server)有何区别?** - **MySQL** 是一个开源的关系型数据库管理系统(RDBMS),由Oracle公司支持。它以轻量...
- **SELECT语句**:用于从数据库中检索数据,可以与WHERE子句结合进行条件查询,也可以使用GROUP BY进行分组和聚合函数(如COUNT, SUM, AVG, MAX, MIN)进行统计计算。 - **JOIN操作**:连接两个或多个表,如INNER...
为什么这个查询执行得很慢? - **A. Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming.** - **B. Collecting information requires various disk-level operations ...
4. **聚合函数**:如`COUNT()`、`SUM()`、`AVG()`、`MIN()`和`MAX()`,用于统计和计算。 5. **窗口函数**:如`RANK()`、`DENSE_RANK()`、`ROW_NUMBER()`等,用于计算行号、分组排名等,适用于数据分析。 6. **事务...
文件“201-mysql-master”很可能是一个关于MySQL的项目或教程,MySQL是世界上最流行的开源关系数据库管理系统。 1. **SQL基础概念** - **数据定义语言 (DDL)**:用于创建和修改数据库结构,如CREATE TABLE用于创建...
"MySQL 面试题知识点总结" MySQL 是一种关系型数据库管理系统,以下是 MySQL 面试题中涵盖的一些知识点: ...* 进行 count () 统计某列的记录数的时候,如果采用的 NULL 值,会被系统自动忽略掉,但是空值是统计到其中
- **分组数据**:使用GROUP BY对数据进行分组,然后应用SUM、COUNT、AVG等聚合函数进行统计分析。 - **HAVING子句**:在GROUP BY之后过滤结果,比在WHERE中过滤更合适。 3. **子查询和联接**: - **嵌套子查询**...
在MySQL数据库中,当执行`GROUP BY`语句时,通常是为了对数据进行分组并进行聚合计算,如计算每个组的总数、平均值等。然而,标准的`GROUP BY`查询并不直接提供每个组的行数,而是返回每个组的一行数据。如果需要...
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数. Count, sql的执行次数及占总的slow log数量的百分比. Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢...
MySQL是世界上最受欢迎的关系型数据库管理系统之一,其性能和效率在很大程度上取决于索引的使用。在面试中,了解和掌握MySQL的索引使用注意事项、优化SQL的方法以及分库分表等高级概念是至关重要的。 首先,让我们...
- 对于`MyISAM`存储引擎,当使用`COUNT(*)`时,如果没有索引或者条件限制,速度会很慢。如果条件字段有索引,应添加条件以提高性能。 - 在处理大量数据时,避免无条件地使用`COUNT(*)`,这可能会导致全表扫描,降低...
- **优化建议**:避免在性能敏感的应用中使用游标,因为它们通常比简单的查询慢很多。 - **示例**:尽量使用集合操作代替游标逐行处理。 #### 总结 通过以上优化措施,可以显著提高SQL语句的执行效率,从而提升...
对于XML和JSON,MySQL提供`XML_CONCAT`和`JSON_MERGE_PATCH`等函数,而PostgreSQL有`xmlparse`和`jsonb`类型来处理这类数据。 通过深入理解和实践这些高级SQL概念,你将能够更有效地处理复杂的数据查询和分析任务,...
在MySQL数据库中,`DISTINCT` 是一个非常重要的关键字,用于从查询结果中去除重复的行,从而确保返回的每一行都是唯一的。本教程将深入探讨`DISTINCT`的使用方法,以及它在实际数据库操作中的应用。 首先,让我们...
例如,通过子查询找到第866,613个ID,然后查询ID大于等于该值的20行,或者使用`JOIN`操作将子查询结果与原表进行连接,这两种方法的查询时间都很短。 除此之外,还有其他优化措施可以考虑: 1. **索引优化**:根据...
2. **性能**:当数据量很大时,使用LIMIT语句进行分页查询可能仍会比较慢,可以考虑使用索引优化查询性能。 3. **用户体验**:除了技术层面的优化,还需要考虑页面设计上的用户体验,如提供清晰的页码导航、快速...