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博客

MySQL Count(*)提速30倍

最新文章