MySQL Count(*)提速30倍

壹頁書發表於2014-04-22
MySql
MySQL InnoDB是索引組織表，如果根據主鍵大範圍查詢，效率非常低下。
因為主鍵索引中還包含資料,需要掃描大量的塊。

這種場景，如果有一個二級索引，則可以極大的提升查詢速度。

初始化實驗資料(100W記錄)


  1. --建立實驗表
  2. create table t(
  3.     id int primary key,
  4.     num int,
  5.     content varchar(1000)
  6. )engine=innodb;

  8. --建立可以產生隨機字串的函式
  9. DROP FUNCTION IF EXISTS rand_string;
  10. delimiter //
  11. CREATE FUNCTION rand_string(l_num int UNSIGNED,l_type tinyint UNSIGNED)
  12. RETURNS varchar(2000)
  13. BEGIN
  14.  -- Function : rand_string
  15.  -- Author : dbachina#dbachina.com
  16.  -- Date : 2010/5/30
  17.  -- l_num : The length of random string
  18.  -- l_type: The string type
  19.  -- 1.0-9
  20.  -- 2.a-z
  21.  -- 3.A-Z
  22.  -- 4.a-zA-Z
  23.  -- 5.0-9a-zA-Z
  24.  -- :
  25.   -- mysql> select rand_string(12,5) random_string;
  26.   -- +---------------+
  27.   -- | random_string |
  28.   -- +---------------+
  29.   -- | 3KzGJCUJUplw  |
  30.   -- +---------------+
  31.   -- 1 row in set (0.00 sec)
  32.  DECLARE i int UNSIGNED DEFAULT 0;
  33.  DECLARE v_chars varchar(64) DEFAULT '0123456789';
  34.   DECLARE result varchar (2000) DEFAULT '';
  35.  
  36.   IF l_type = 1 THEN
  37.     SET v_chars = '0123456789';
  38.   ELSEIF l_type = 2 THEN
  39.     SET v_chars = 'abcdefghijklmnopqrstuvwxyz';
  40.   ELSEIF l_type = 3 THEN
  41.     SET v_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  42.   ELSEIF l_type = 4 THEN
  43.     SET v_chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  44.   ELSEIF l_type = 5 THEN
  45.     SET v_chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  46.   ELSE
  47.     SET v_chars = '0123456789';
  48.   END IF;
  49.  
  50.   WHILE i < l_num DO
  51.       SET result = concat( result,substr(v_chars,ceil(rand()*(length(v_chars)-1)),1) );
  52.     SET i = i + 1;
  53.   END WHILE;
  54.   RETURN result;
  55. END;
  56. //
  57. delimiter ;

  59. --插入100W條實驗資料
  60. DROP PROCEDURE IF EXISTS insertTableT;
  61. delimiter //
  62. create procedure insertTableT()
  63. begin
  64.     declare i int;
  65.     set i=0;
  66.     while i<1000000 do
  67.         insert into t values(i,i,rand_string(1000,5));
  68.         set i=i+1;
  69.     end while;
  70. end;
  71. //
  72. delimiter ;

使用主鍵索引的count(*)查詢

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

  9. mysql> select count(*) from t;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. | 1000000  |
  14. +----------+
  15. 1 row in set (6.85 sec)

使用二級索引的count(*)查詢
  1. mysql> create index t_num on t(num);
  2. Query OK, 0 rows affected (9.55 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

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

  13. mysql> select count(*) from t;
  14. +----------+
  15. | count(*) |
  16. +----------+
  17. | 1000000  |
  18. +----------+
  19. 1 row in set (0.18 sec)
使用二級索引相比主鍵索引有近30倍的提升。

但是奇怪的是，按照Oracle對於索引的描述，不全為null的內容可以被索引。
很明顯，t表的num欄位可以為空。
插入一個空值之後，居然還可以使用索引？？非常奇怪
  1. mysql> insert into t(id,num,content) values(1000001,null,'1');
  2. Query OK, 1 row affected (0.02 sec)

  4. mysql> commit;
  5. Query OK, 0 rows affected (0.04 sec)
包含null值，依然使用索引的count(*)

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

  9. mysql> select count(*) from t;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. | 1000001  |
  14. +----------+
  15. 1 row in set (0.18 sec)
  對空值的查詢居然還能使用索引？

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

  9. mysql> select * from t where num is null;
  10. +---------+------+---------+
  11. | id      | num  | content |
  12. +---------+------+---------+
  13. | 1000001 | NULL | 1       |
  14. +---------+------+---------+
  15. 1 row in set (0.00 sec)
不走索引著急，不明白為什麼走索引也著急。
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1147227/，如需轉載，請註明出處，否則將追究法律責任。

相關文章