mysql中innodb表中count()优化

起因：在innodb表上做count(*)统计实在是太慢了，因此想办法看能不能再快点。

现象：先来看几个测试案例，如下

一、 sbtest 表上的测试

show create table sbtest＼G
*************************** 1. row ***************************
Table: sbtest
Create Table: CREATE TABLE `sbtest` (
`aid` bigint(20) unsigned NOT NULL auto_increment,
`id` int(10) unsigned NOT NULL default '0',
`k` int(10) unsigned NOT NULL default '0',
`c` char(120) NOT NULL default '',
`pad` char(60) NOT NULL default '',
PRIMARY KEY  (`aid`),
KEY `k` (`k`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1

show index from sbtest;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sbtest |          0 | PRIMARY  |            1 | aid         | A         |     1000099 |     NULL | NULL   |      | BTREE      |         |
| sbtest |          1 | k        |            1 | k           | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| sbtest |          1 | id       |            1 | id          | A         |     1000099 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
填充了 100万条 记录。

1、 直接 count(*)

explain SELECT COUNT(*) FROM sbtest;
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | sbtest | index | NULL          | PRIMARY | 8       | NULL | 1000099 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
SELECT COUNT(*) FROM sbtest;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.42 sec)
可以看到，如果不加任何条件，那么优化器优先采用 primary key 来进行扫描。

2、count(*) 使用 primary key 字段做条件

explain SELECT COUNT(*) FROM sbtest WHERE aid>=0;
+----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest | range | PRIMARY       | PRIMARY | 8       | NULL | 485600 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+
SELECT COUNT(*) FROM sbtest WHERE aid>=0;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.39 sec)
可以看到，尽管优化器认为只需要扫描 485600 条记录(其实是索引)，比刚才少多了，但其实仍然要做全表(索引)扫描。因此耗时和第一种相当。

3、 count(*) 使用 secondary index 字段做条件

explain SELECT COUNT(*) FROM sbtest WHERE id>=0;
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest | range | id            | id   | 4       | NULL | 500049 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
SELECT COUNT(*) FROM sbtest WHERE id>=0;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.43 sec)
可以看到，采用这种方式查询会非常快。有人也许会问了，会不会是因为 id 字段的长度比 aid 字段的长度来的小，导致它扫描起来比较快呢？先不着急下结论，咱们来看看下面的测试例子。

二、 sbtest1 表上的测试

show create table sbtest1＼G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`aid`),
KEY `k` (`k`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
show index from sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sbtest1 |          0 | PRIMARY  |            1 | aid         | A         |     1000099 |     NULL | NULL   |      | BTREE      |         |
| sbtest1 |          1 | k        |            1 | k           | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| sbtest1 |          1 | id       |            1 | id          | A         |     1000099 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

这个表里，把 aid 和 id 的字段长度调换了一下，也填充了 1000万条 记录。

1、 直接 count(*)

explain SELECT COUNT(*) FROM sbtest1;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | sbtest1 | index | NULL          | PRIMARY | 4       | NULL | 1000099 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+
SELECT COUNT(*) FROM sbtest1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.42 sec)

可以看到，如果不加任何条件，那么优化器优先采用 primary key 来进行扫描。

2、count(*) 使用 primary key 字段做条件

explain SELECT COUNT(*) FROM sbtest1 WHERE aid>=0;
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest1 | range | PRIMARY       | PRIMARY | 4       | NULL | 316200 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
SELECT COUNT(*) FROM sbtest1 WHERE aid>=0;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.42 sec)

可以看到，尽管优化器认为只需要扫描 485600 条记录(其实是索引)，比刚才少多了，但其实仍然要做全表(索引)扫描。因此耗时和第一种相当。

3、 count(*) 使用 secondary index 字段做条件

explain SELECT COUNT(*) FROM sbtest1 WHERE id>=0;
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest1 | range | id            | id   | 8       | NULL | 500049 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
SELECT COUNT(*) FROM sbtest1 WHERE id>=0;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.45 sec)

可以看到，采用这种方式查询会非常快。

上面的所有测试，均在 mysql 5.1.24 环境下通过，并且每次查询前都重启了 mysqld。

可以看到，把 aid 和 id 的长度调换之后，采用 secondary index 查询仍然是要比用 primary key 查询来的快很多。看来主要不是字段长度引起的索引扫描快慢，而是采用 primary key 以及 secondary index 引起的区别。那么，为什么用 secondary index 扫描反而比 primary key 扫描来的要快呢？我们就需要了解innodb的 clustered index 和secondary index 之间的区别了。

innodb 的 clustered index 是把 primary key 以及 row data 保存在一起的，而 secondary index 则是单独存放，然后有个指针指向 primary key。因此，需要进行 count(*) 统计表记录总数时，利用 secondary index 扫描起来，显然更快。而primary key则主要在扫描索引，同时要返回结果记录时的作用较大，例如：

SELECT * FROM sbtest WHERE aid = xxx;

那既然是使用 secondary index 会比 primary key 更快，为何优化器却优先选择 primary key 来扫描呢，Heikki Tuuri 的回答是：

in the example table, the secondary index is inserted into in a perfect order! That is
very unusual. Normally the secondary index would be fragmented, causing random disk I/O,
and the scan would be slower than in the primary index.
I am changing this to a feature request: keep 'clustering ratio' statistics on a secondary
index and do the scan there if the order is almost the same as in the primary index. I
doubt this feature will ever be implemented, though.

时间： 2024-09-20 00:03:04

mysql中innodb表中count()优化的相关文章

提高MySQL中InnoDB表BLOB列的存储效率的教程_Mysql

首先,介绍下关于InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效: 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size

jsp mysql-jsp页面如何实现一个用mysql创建的表中的信息

问题描述 jsp页面如何实现一个用mysql创建的表中的信息 这是我的jsp界面 <%@ page language="java" import="java.util.*" pageEncoding="gb2312"%> My JSP 用户名: 密 码 </body> </html> 而且我的数据库也连接上了,连接代码放在src/com.ming.java/DB类中,我在数据库中还建了一张表格userinfo

jsp-java 中JSP 实现把excel表中数据导入到mysql数据库中的表中的具体实现方法

问题描述 java 中JSP 实现把excel表中数据导入到mysql数据库中的表中的具体实现方法 java 中JSP 实现把excel表中数据导入到mysql数据库中的表中的具体实现方法 解决方案 参考:http://blog.csdn.net/casilin/article/details/5750773 解决方案二: 楼主幸苦了!!谢谢分享!!! 解决方案三: jsp导excel到mysql数据库 http://wenku.it168.com/d_000468232.shtml

mysql保存一条有id的数据到表中，表中id可以不变吗

问题描述 mysql保存一条有id的数据到表中,表中id可以不变吗 mysql保存一条有id的数据到表中,表中新增的数据id可以不变吗?所有字段不都变,可以不 解决方案 如果列上面被设置成了主键或加上了唯一性约束,那么这一列上的数据在每一行中都要不一样. 如果你说的id列有被设置成主键或者唯一性约束的话,就不能不变. 没有主键和唯一性约束的话,可以有完全相同的两条或多条的一样的数据. 但为了区分每一行数据,在数据库设计上面会将设置一列做为主键. 解决方案二: 如果id不是唯一主键,就可以一样 解

mysql update 根据表中字段查询另一张表更新更新

问题描述 mysql update 根据表中字段查询另一张表更新更新 mysql有两张表, 班级表class,包含 | id | name | | 1 | 一班 | | 2 | 二班 | 学生表student,其中classId为空,className有值并对应class表中的name | id | name | classId | className | | 1 | 一班 | | 一班 | | 2 | 二班 | | 二班 | | 3 | 一班 | | 一班 | | 4 | 二班 | | 二班

asp.net 如何读取客户端电脑中SQLITE表中的数据

问题描述 如题:如何读取客户端电脑中SQLITE表中的数据 解决方案 解决方案二:做不到吧,你数据库的账号密码都不知道首先解决方案三:我本地机器SQLITE是统一开发的单机程序解决方案四:首先要知道你要取得数据的ip地址,数据库名称,用户名,密码,然后建立数据连接,就可以得到数据了.解决方案五:这个用VB程序写在excel里比较好解决方案六:sqlite,一般都是作为单机版的数据库使用网络环境使用,建议改用其他数据,比如mssql.oracle.mysql等等

MySQL中从表中取出随机数据性能优化

最简的办法 rand() 函数实例  代码如下 复制代码 SELECT * FROM table_name ORDER BY rand() LIMIT 5; 花时间为 0.7888 如果这样在数据量大时就挂了 后来找到一个办法  代码如下 复制代码 SELECT * FROM table_name AS r1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM table_name)) AS id) AS r2 WHERE r1.id >= r2.i

MySQL随机从表中取出数据sql语句

rand在手册里是这么说的: RAND() RAND(N) 例  代码如下 复制代码 SELECT * FROM table_name ORDER BY rand() LIMIT 5; 返回在范围0到1.0内的随机浮点值.如果一个整数参数N被指定,它被用作种子值.  代码如下 复制代码 mysql> select RAND(); -> 0.5925 mysql> select RAND(20); -> 0.1811 mysql> select RAND(20); ->

详解MySQL下InnoDB引擎中的Memcached插件_Mysql

前些年,HandlerSocket的横空出世让人们眼前一亮,当时我还写了一篇文章介绍了其用法梗概,时至今日,由于种种原因,HandlerSocket并没有真正流行起来,不过庆幸的是MySQL官方受其启发,研发了基于InnoDB的Memcached插件,总算是在MySQL中延续了NoSQL的香火,以前单独架设Memcached服务器不仅浪费了内存,而且还必须自己维护数据的不一致问题,有了Memcached插件,这些问题都不存在了,而且借助MySQL本身的复制功能,我们可以说是变相的实现了Memca