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 |  --Vevb.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.