MySQL InnoDB是索引组织表,如果根据主键大范围查询,效率非常低下。
因为主键索引中还包含数据,需要扫描大量的块。
这种场景,如果有一个二级索引,则可以极大的提升查询速度。
初始化实验数据(100W记录)
使用主键索引的count(*)查询
使用二级索引的count(*)查询
但是奇怪的是,按照Oracle对于索引的描述,不全为null的内容可以被索引。
很明显,t表的num字段可以为空。
插入一个空值之后,居然还可以使用索引??非常奇怪
因为主键索引中还包含数据,需要扫描大量的块。
这种场景,如果有一个二级索引,则可以极大的提升查询速度。
初始化实验数据(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 ;
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)
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| 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倍的提升。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)
但是奇怪的是,按照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(*)Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
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)
对空值的查询居然还能使用索引?+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| 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)
不走索引着急,不明白为什么走索引也着急。| 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/