学习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索引的存储量,自然会快些.

上一篇

评论



分享

我的公众号

恩波的公众号

最新加入

最新评论

热门网站主②:婷婷: 热门广告联盟诚邀各类优质网站长合作。承接CPC,CPV,CPM,CPA,CPS等类型广告,帮你做各类产品推广,拥有十万网站主加盟,千万级流量,CTR防作弊检测,让客户更加放心。热门广告联盟 http://www.remenad.com 扒搬窝资源网为您提供各类精品网站源码,棋牌源码,游戏源码,软件源码,商业教程 扒搬窝资源网 http://www.pabanwo.com/ 查看原文 10月14日 10:05
叫我金夫人有糖吃: 现在还有邀请码吗????1104190614@qq.com 求求求 查看原文 07月30日 10:45
阿飞: 大神,有没demo文件 查看原文 07月05日 10:25
dreamer: 求邀请码1079623171@qq.com 查看原文 06月20日 09:03
恩波: 时隔2年多了,目前微信卡券估计已经变了好多了,不好意思啊 查看原文 06月01日 15:33

赞助商