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)

不走索引著急，不明白為什麼走索引也著急。

MySQL Count(*)提速30倍

相關文章