学习MySQL优化之聚簇索引与索引覆盖


CREATE TABLE smth (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  ver int(11) default null,
  content varchar(3000) not null,
  intro varchar(3000) not null,
  primary key(id),
  key idver(id,ver)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

上面这张表,注意存储方式是InnoDB,当这张表达到3万条+记录后,会发现,如下问题:

select id from smth order by id; 慢的很

而:

select id from smth order by id,ver; 就快很多了

这是为什么呢?我们一步步来测试,看问题到底出在哪里.

开始测试,插入3万条测试数据,创建以下存储过程:

create procedure smthtest1()
BEGIN
    declare num int default 0;
    while num < 40000 do
    set num := num +1;
    insert into smth values (num ,FLOOR(1 + (RAND() * 1000)),RPAD('中' ,3000 ,'中'),RPAD('华' ,3000 ,'华'));
    end while ;
END

blob.png

数据插进去了,打开profiling设为1;

set profiling=1;
select id from smth order by id;
select id from smth order by id,ver;
show profiles;

blob.png

为了保证数据的严谨,可以多跑几次,看上图,第一句最高31.21s,而第二句只花了0.05s,差距比较大.这是因为MyISAM与InnoDB的索引结构有很大的不同,MyISAM的索引,指向的是数据在磁盘上的位置;而InnoDB的索引指向的是对主键值的引用,所有的真实数据是挂在主键上的.

InnoDB以主键建立一颗树,树上挂着所有的真实数据,其他的索引都是指向主键的,InnoDB里面既有索引,又有数据,索引和数据聚集在一块,因此称为聚簇,有个不足之处就是既包含数据又包含索引,会变的比较大,那么沿着主键走的时候,时间耗费的也长,而且由于InnoDB里有"页"的概念,产生数据的过程中会产生页的分裂,本例中的"content","intro"字段数据量又比较大,页的分裂很快,查询的时候要在不同的页面跳来跳去,极大拖慢速度.

那为什么order by id,ver就快了呢?看两条查询语句的explain,都用到了索引啊,如下图:

blob.png


原因其实是因为复合索引"idver"指向的是主键id而不是大块的数据,内部来说只是存了具体的id值,所以查询的时候只沿着"idver"的索引走了,达到了索引覆盖的效果,没有回行.


回过头来,如果我们把引擎换成MyISAM,同样的主键,索引,和数据,看下对比.

CREATE TABLE mysmth (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  ver int(11) default null,
  content varchar(3000) not null,
  intro varchar(3000) not null,
  primary key(id),
  key idver(id,ver)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

blob.png

再分别查询,得到以下结果:

blob.png

MyISAM的索引都是各归个的自己指向数据,查询的时候沿着索引,不需要像InnoDB那样沿着分裂的页去查找,所以这两种查询并不会产生数量级上较大的差异.大家从上图也能看出,以主键id速度快于idver索引,那是因为主键索引存储量小于idver索引的存储量,自然会快些.

上一篇

评论



分享

我的公众号

恩波的公众号

最新加入

最新评论

dreamer: 求邀请码1079623171@qq.com 查看原文 06月20日 09:03
恩波: 时隔2年多了,目前微信卡券估计已经变了好多了,不好意思啊 查看原文 06月01日 15:33
lwj: 你好,我刚看了你发的这个帖子,不知道现在评论是否能看到。我现在在做这个功能,可以用。但我这还有个需求就是,可以推送多张,我在cardList里,把需要推送的卡券,都添加上了,微信端页面也显示正确,有个领取按钮,但可以领取多次,每次卡包里多一张,而且这张是列表上的第一张 。。请问,你有没有遇到过 推送多张的情况 查看原文 05月15日 14:29
roly: 另外添加卡券接口的参数cardId: "xxxxxxxxxxxxxxxxxxxxxx", cardExt: '{"timestamp":"1426222398","signature":"fdd892770eb681e925f92acb9015c75107b2227a"}' 是通过自己服务获取以上参数 还是用js在html5页面直接生产签名参数? 查看原文 05月12日 16:41
roly: 您好,请问怎么查询当前用户卡券是否领取状态? 查看原文 05月12日 16:20

赞助商