MySQL Count(*)提速30倍

MySQL InnoDB是索引组织表，如果根据主键大范围查询，效率非常低下。
因为主键索引中还包含数据,需要扫描大量的块。

这种场景，如果有一个二级索引，则可以极大的提升查询速度。

初始化实验数据(100W记录)

--创建实验表
create table t(
    id int primary key,
    num int,
    content varchar(1000)
)engine=innodb;

--创建可以产生随机字符串的函数
DROP FUNCTION IF EXISTS rand_string;
delimiter //
CREATE FUNCTION rand_string(l_num int UNSIGNED,l_type tinyint UNSIGNED)
RETURNS varchar(2000)
BEGIN
 -- Function : rand_string
 -- Author : dbachina#dbachina.com
 -- Date : 2010/5/30
 -- l_num : The length of random string
 -- l_type: The string type
 -- 1.0-9
 -- 2.a-z
 -- 3.A-Z
 -- 4.a-zA-Z
 -- 5.0-9a-zA-Z
 -- :
  -- mysql> select rand_string(12,5) random_string;
  -- +---------------+
  -- | random_string |
  -- +---------------+
  -- | 3KzGJCUJUplw  |
  -- +---------------+
  -- 1 row in set (0.00 sec)
 DECLARE i int UNSIGNED DEFAULT 0;
 DECLARE v_chars varchar(64) DEFAULT '0123456789';
  DECLARE result varchar (2000) DEFAULT '';
 
  IF l_type = 1 THEN
    SET v_chars = '0123456789';
  ELSEIF l_type = 2 THEN
    SET v_chars = 'abcdefghijklmnopqrstuvwxyz';
  ELSEIF l_type = 3 THEN
    SET v_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  ELSEIF l_type = 4 THEN
    SET v_chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  ELSEIF l_type = 5 THEN
    SET v_chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  ELSE
    SET v_chars = '0123456789';
  END IF;
 
  WHILE i < l_num DO
      SET result = concat( result,substr(v_chars,ceil(rand()*(length(v_chars)-1)),1) );
    SET i = i + 1;
  END WHILE;
  RETURN result;
END;
//
delimiter ;

--插入100W条实验数据
DROP PROCEDURE IF EXISTS insertTableT;
delimiter //
create procedure insertTableT()
begin
    declare i int;
    set i=0;
    while i<1000000 do
        insert into t values(i,i,rand_string(1000,5));
        set i=i+1;
    end while;
end;
//
delimiter ;

使用主键索引的count(*)查询

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

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (6.85 sec)

使用二级索引的count(*)查询

mysql> create index t_num on t(num);
Query OK, 0 rows affected (9.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select count(*) from t;
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| 1  | SIMPLE      | t     | index | NULL          | t_num | 5       | NULL | 931113 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.18 sec)

使用二级索引相比主键索引有近30倍的提升。

但是奇怪的是，按照Oracle对于索引的描述，不全为null的内容可以被索引。
很明显，t表的num字段可以为空。
插入一个空值之后，居然还可以使用索引？？非常奇怪

mysql> insert into t(id,num,content) values(1000001,null,'1');
Query OK, 1 row affected (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

包含null值，依然使用索引的count(*)

mysql> explain select count(*) from t;
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| 1  | SIMPLE      | t     | index | NULL          | t_num | 5       | NULL | 931114 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1000001  |
+----------+
1 row in set (0.18 sec)

  对空值的查询居然还能使用索引？

mysql> explain select * from t where num is null; +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| 1  | SIMPLE      | t     | ref  | t_num         | t_num | 5       | const | 1    | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from t where num is null;
+---------+------+---------+
| id      | num  | content |
+---------+------+---------+
| 1000001 | NULL | 1       |
+---------+------+---------+
1 row in set (0.00 sec)

不走索引着急，不明白为什么走索引也着急。
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1147227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1147227/