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

上一篇

评论



分享

我的公众号

恩波的公众号

最新加入

最新评论

所谓爱人: 可不可以加个好友QQ1217994113 查看原文 04月01日 18:47
17688905252: 看了好多说是要加上这个样式的,但是加上之后为什么还是一点反应都没有呢... 查看原文 04月01日 14:55
17688905252: 你好,加上了cursor:point;为什么还是没有反应呢? 查看原文 04月01日 14:54
新城旧梦: 跪求邀请码!望大大可以看见!一定会珍惜、已熟记版规!351309009@qq.co m 查看原文 03月31日 23:30
...噢NO: 您好,大神,还玩草榴吗?能不能给我发一个邀请码呢。如果可以,万分感谢!820012464@qq.com 祝您17年身体健康 查看原文 03月23日 16:49

赞助商