Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2856694
  • 博文数量: 471
  • 博客积分: 7081
  • 博客等级: 少将
  • 技术积分: 5369
  • 用 户 组: 普通用户
  • 注册时间: 2012-01-04 21:55
文章分类

全部博文(471)

文章存档

2014年(90)

2013年(69)

2012年(312)

分类: Mysql/postgreSQL

2014-02-19 17:40:10

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 秒
阅读(1716) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~