1、为什么用 secondary index 扫描反而比 primary key 扫描来的要快呢？我们就需要了解innodb的clustered index 和 secondary index 之间的区别了。
innodb 的 clustered index 是把 primary key 以及 row data 保存在一起的，而 secondary index 则是单独存放，然后有个指针指向 primary key。因此，需要进行 count(*) 统计表记录总数时，利用 secondary index 扫描起来，显然更快。而primary key则主要在扫描索引，同时要返回结果记录时的作用较大。
2、可以看到以上where条件有主键和普通索引用and连接的话，实际用到的索引是主键PRIMARY。
3、可以看到以上where条件有主键和普通索引用 or 连接的话，实际用到的索引是普通索引 sex。
弄了一天的mysql索引，通过自己的实践来记录一些事实吧：
一、首先，建表：
CREATE TABLE `sbtest` (
`aid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`id` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
`passwd` varchar(100) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`sex` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`aid`),
) ENGINE=InnoDB AUTO_INCREMENT=6000001 DEFAULT CHARSET=utf8，
这里只有一个主键索引aid，
二、存储过程插入6百万条数据：
delimiter $$
create procedure insertt()
begin
set @a=0;
set @e=1;
while @a<6000000 do
set @b='name2 for procedure';
set @c='pass2 for procecdure';
set @d='test2 for procecdure';
insert into sbtest values('',@e,@b,@c,@d);
set @a=@a+1;
set @e=@e+1;
end while;
end$$
delimiter ;
三、测试count的执行效率
测试之前需要把mysql缓存关闭掉，修改mysql的配置my.cnf
query_cache_size = 0
query_cache_type = 0
重启mysql
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
查看这两项：
| query_cache_size | 0 |
| query_cache_type | OFF |
证明mysql缓存已经关闭。接下来继续.....
mysql> explain select count(*) from sbtest;
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | index | NULL | PRIMARY | 8 | NULL | 5995867 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
可以看到所查询的数据记录数为 5995867，默认用的是主键索引。
四、此时用php脚本验证一下执行时间：
set_time_limit(0);
function getmicrotime(){
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
$con = mysql_connect('localhost','root','') or die('can not connect mysql!');
mysql_select_db('ljyun_512_merchant',$con) or die('select error!');
echo 'start time = '.$startTimt = getmicrotime().'
';
$sql = "select count(*) from sbtest ";
$query = mysql_query($sql);
$res = mysql_fetch_assoc($query);
echo 'end time = '.$endTime = getmicrotime().'
';
$queryTime = $endTime - $startTimt;
echo 'query time is:'.$queryTime.' 秒
';
unset($queryTime,$startTimt,$endTime);
var_dump($res);
以上php程序输出结果为：
start time = 1358934178.7986
end time = 1358934181.8649
query time is:3.0663001537323 秒
array(1) { ["count(*)"]=> string(7) "6000000" }
可以看到程序执行时间为3.0663001537323 秒。
五、添加普通索引key：
mysql> alter table sbtest add key sex(`sex`);
mysql> explain select count(*) from sbtest;
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | index | NULL | sex | 4 | NULL | 5995867 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
可以看到所查询的数据记录数为 5995867，默认用的是普通索引key sex。
此时执行以上php脚本，结果是：
start time = 1358934526.6436
end time = 1358934527.2778
query time is:0.63420009613037 秒
array(1) { ["count(*)"]=> string(7) "6000000" }
可以看到程序执行时间为0.63420009613037 秒。
以上主键索引和 （主键索引+普遍索引）6百万条数据，两种情况执行的时间相差大概5倍。当然这两种情况都是在sql语句不加where条件的前提下。
如果sql语句加上where条件会是如何呢？请看下面测试结果：
六、sql中有where条件的情况：
mysql> explain select count(*) from sbtest where aid>=0;
+----+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------+
| 1 | SIMPLE | sbtest | range | PRIMARY | PRIMARY | 8 | NULL | 2997933 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------+
可以看到所查询的数据记录数为 2997933，用的是主键索引 PRIMARY。
执行php脚本结果为：（执行前要把php中的sql语句加上where：select count(*) from sbtest where aid>=0）
start time = 1358934968.3334
end time = 1358934971.4169
query time is:3.0834999084473 秒
array(1) { ["count(*)"]=> string(7) "6000000" }
可以看到程序执行时间为3.0834999084473 秒。
改变where条件：
mysql> explain select count(*) from sbtest where sex>=0;
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | sbtest | range | sex | sex | 4 | NULL | 2997933 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
可以看到所查询的数据记录数为 2997933，查询条数和以上情况一致，用的是普通索引 sex。
php执行结果：（select count(*) from sbtest where sex>=0）：
start time = 1358935193.4689
end time = 1358935194.6322
query time is:1.163300037384 秒
array(1) { ["count(*)"]=> string(7) "6000000"
可以看到程序执行时间为1.163300037384 秒。这里的where条件sex>=0要比主键where条件aid>=0快上将近2秒多。
显然，在Innodb中使用count()函数普通索引要比主键索引快，原因我在百度中找到的结果：
为什么用 secondary index 扫描反而比 primary key 扫描来的要快呢？我们就需要了解innodb的clustered index 和 secondary index 之间的区别了。
innodb 的 clustered index 是把 primary key 以及 row data 保存在一起的，而 secondary index 则是单独存放，然后有个指针指向 primary key。因此，需要进行 count(*) 统计表记录总数时，利用 secondary index 扫描起来，显然更快。而primary key则主要在扫描索引，同时要返回结果记录时的作用较大。
七、此时我把where条件在更改一下
mysql> explain select count(*) from sbtest where sex>=0 and id>=0;
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | ALL | sex | NULL | NULL | NULL | 5982147 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
可以看到以上并没有用到索引，
程序执行时间为：
query time is:2.9929001331329 秒
mysql> explain select count(*) from sbtest where sex>=0 and aid>=0;
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | range | PRIMARY,sex | PRIMARY | 8 | NULL | 2991073 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
可以看到以上where条件有主键和普通索引用and连接的话，实际用到的索引是主键PRIMARY。
程序执行时间为：
query time is:2.9929001331329 秒
mysql> explain select count(*) from sbtest where sex>=0 or aid>=0;
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | sbtest | index | PRIMARY,sex | sex | 4 | NULL | 5982147 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.01 sec)
可以看到以上where条件有主键和普通索引用 or 连接的话，实际用到的索引是普通索引 sex。
程序执行时间为
query time is:1.1176002025604 秒
此时给sbtest表在添加一个普通索引id
mysql>alter table sbtest add key id(`id`)
mysql> explain select count(*) from sbtest where sex>=0 and id>=0;
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | ALL | sex,id | NULL | NULL | NULL | 5982147 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
可以看到以上，实际用到的索引是普通索引 NULl,没有用到任何索引。
程序执行时间为
query time is:3.1594998836517 秒
mysql> explain select count(*) from sbtest where sex>=0 and id>=0 and aid>=0;
+----+-------------+--------+-------+----------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | range | PRIMARY,sex,id | PRIMARY | 8 | NULL | 2991073 | Using where |
+----+-------------+--------+-------+----------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
可以看到以上，实际用到的索引是主键索引 PRIMARY。
程序执行时间为
query time is:3.8204998970032 秒。
mysql> explain select count(*) from sbtest where sex>=0 or aid>=0 or id>=0;
+----+-------------+--------+------+----------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | ALL | PRIMARY,sex,id | NULL | NULL | NULL | 5982147 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
可以看到以上，实际用到的索引 NULL,没有用到任何索引。
程序执行时间为 query time is:3.034900188446 秒
阅读(1719) | 评论(0) | 转发(0) |
