当前位置：首页 > Mysql教程 > 列表

mysql中innodb表中count()优化

发布:smiling　来源: PHP粉丝网 　添加日期：2015-04-17 16:29:07　浏览: 评论：0　

count()是用来统计数据表中所有记录的一个函数了,但在此函数在innodb中性能不怎么样了,下面我们来看看mysql中innodb表中count()优化,希望例子对各位有帮助.

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

现象:先来看几个测试案例,如下.

一、sbtest 表上的测试.

  1. show create table sbtest＼G 
  2. *************************** 1. row *************************** 
  3. Table: sbtest 
  4. Create Table: CREATE TABLE `sbtest` ( 
  5. `aid` bigint(20) unsigned NOT NULL auto_increment, 
  6. `id` int(10) unsigned NOT NULL default '0', 
  7. `k` int(10) unsigned NOT NULL default '0', 
  8. `c` char(120) NOT NULL default '', 
  9. `pad` char(60) NOT NULL default '', 
  10. PRIMARY KEY  (`aid`), 
  11. KEY `k` (`k`), 
  12. KEY `id` (`id`) 
  13. ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1 
  14. show index from sbtest; 
  15. +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  16. | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  --phpfensi.com 
  17. +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  18. | sbtest |          0 | PRIMARY  |            1 | aid         | A         |     1000099 |     NULL | NULL   |      | BTREE      |         | 
  19. | sbtest |          1 | k        |            1 | k           | A         |          18 |     NULL | NULL   |      | BTREE      |         | 
  20. | sbtest |          1 | id       |            1 | id          | A         |     1000099 |     NULL | NULL   |      | BTREE      |         | 
  21. +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 

填充了 100万条 记录.

1、直接 count(*)

  1. explain SELECT COUNT(*) FROM sbtest; 
  2. +----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+ 
  3. | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows    | Extra       | 
  4. +----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+ 
  5. |  1 | SIMPLE      | sbtest | index | NULL          | PRIMARY | 8       | NULL | 1000099 | Using index | 
  6. +----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+ 
  7. SELECT COUNT(*) FROM sbtest; 
  8. +----------+ 
  9. | COUNT(*) | 
  10. +----------+ 
  11. |  1000000 | 
  12. +----------+ 
  13. 1 row in set (1.42 sec) 

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

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

  1. explain SELECT COUNT(*) FROM sbtest WHERE aid>=0; 
  2. +----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+ 
  3. | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    | 
  4. +----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+ 
  5. |  1 | SIMPLE      | sbtest | range | PRIMARY       | PRIMARY | 8       | NULL | 485600 | Using where; Using index | 
  6. +----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+ 
  7. SELECT COUNT(*) FROM sbtest WHERE aid>=0; 
  8. +----------+ 
  9. | COUNT(*) | 
  10. +----------+ 
  11. |  1000000 | 
  12. +----------+ 
  13. 1 row in set (1.39 sec) 

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

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

  1. explain SELECT COUNT(*) FROM sbtest WHERE id>=0; 
  2. +----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  3. | id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    | 
  4. +----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  5. |  1 | SIMPLE      | sbtest | range | id            | id   | 4       | NULL | 500049 | Using where; Using index | 
  6. +----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  7. SELECT COUNT(*) FROM sbtest WHERE id>=0; 
  8. +----------+ 
  9. | COUNT(*) | 
  10. +----------+ 
  11. |  1000000 | 
  12. +----------+ 
  13. 1 row in set (0.43 sec) 

可以看到,采用这种方式查询会非常快,有人也许会问了,会不会是因为 id 字段的长度比 aid 字段的长度来的小,导致它扫描起来比较快呢？先不着急下结论,咱们来看看下面的测试例子.

二、sbtest1 表上的测试

  1. show create table sbtest1＼G 
  2. *************************** 1. row *************************** 
  3. Table: sbtest1 
  4. Create Table: CREATE TABLE `sbtest1` ( 
  5. `aid` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  6. `id` bigint(20) unsigned NOT NULL DEFAULT '0', 
  7. `k` int(10) unsigned NOT NULL DEFAULT '0', 
  8. `c` char(120) NOT NULL DEFAULT '', 
  9. `pad` char(60) NOT NULL DEFAULT '', 
  10. PRIMARY KEY (`aid`), 
  11. KEY `k` (`k`), 
  12. KEY `id` (`id`) 
  13. ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1 
  14. show index from sbtest1; 
  15. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  16. | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
  17. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  18. | sbtest1 |          0 | PRIMARY  |            1 | aid         | A         |     1000099 |     NULL | NULL   |      | BTREE      |         | 
  19. | sbtest1 |          1 | k        |            1 | k           | A         |          18 |     NULL | NULL   |      | BTREE      |         | 
  20. | sbtest1 |          1 | id       |            1 | id          | A         |     1000099 |     NULL | NULL   |      | BTREE      |         | 
  21. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 

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

1、直接 count(*).

  1. explain SELECT COUNT(*) FROM sbtest1; 
  2. +----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+ 
  3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra       | 
  4. +----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+ 
  5. |  1 | SIMPLE      | sbtest1 | index | NULL          | PRIMARY | 4       | NULL | 1000099 | Using index | 
  6. +----+-------------+---------+-------+---------------+---------+---------+------+---------+-------------+ 
  7. SELECT COUNT(*) FROM sbtest1; 
  8. +----------+ 
  9. | COUNT(*) | 
  10. +----------+ 
  11. |  1000000 | 
  12. +----------+ 
  13. 1 row in set (1.42 sec) 

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

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

  1. explain SELECT COUNT(*) FROM sbtest1 WHERE aid>=0; 
  2. +----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+ 
  3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    | 
  4. +----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+ 
  5. |  1 | SIMPLE      | sbtest1 | range | PRIMARY       | PRIMARY | 4       | NULL | 316200 | Using where; Using index | 
  6. +----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+ 
  7. 1 row in set (0.00 sec) 
  8. SELECT COUNT(*) FROM sbtest1 WHERE aid>=0; 
  9. +----------+ 
  10. | COUNT(*) | 
  11. +----------+ 
  12. |  1000000 | 
  13. +----------+ 
  14. 1 row in set (1.42 sec) 

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

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

  1. explain SELECT COUNT(*) FROM sbtest1 WHERE id>=0; 
  2. +----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+ 
  3. | id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    | 
  4. +----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+ 
  5. |  1 | SIMPLE      | sbtest1 | range | id            | id   | 8       | NULL | 500049 | Using where; Using index | 
  6. +----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+ 
  7. 1 row in set (0.00 sec) 
  8. SELECT COUNT(*) FROM sbtest1 WHERE id>=0; 
  9. +----------+ 
  10. | COUNT(*) | 
  11. +----------+ 
  12. |  1000000 | 
  13. +----------+ 
  14. 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 的回答是:

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

Tags: innodb表 count()优化

分享到：

推荐文章

热门文章

最新评论文章