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

上一篇

评论



分享

我的公众号

恩波的公众号

最新加入

最新评论

emerson: 那个卡券的加密是不是要异步的?如果不需要的话,是不是使用jq的点击触发函数里面将addcard的api放进去?然后是不是会自动跳转到领取卡券的页面上的 查看原文 01月11日 21:30
渎沽沅洱: 有用的 查看原文 01月10日 15:52
京九线: 大神有个问题想咨询 574450603 查看原文 12月29日 23:54
I`m here: thanks for you offer 查看原文 12月14日 14:44
呼死你短信轰炸: 求草榴邀请码 查看原文 12月09日 20:07

赞助商