IT技术互动交流平台

Mysql聚簇索排序慢案例分析

来源:IT165收集  发布日期:2016-12-12 20:35:38

为什么当 执行select较多时应当使用mysiam引擎呢?尤其是在有索引的情况下 本篇章依托一个实际应用,分析一下。

一.前言: 网上看到有一个有趣的现象,一个有1W数据量的表,执行不同的orderby条件,查询时间非常大,这个是实际应用中确实出现的问题??为什么呢? 二.分析 a).情况描述:
1.有主键id,联合索引(id,ver);用前者当orderby查询慢,用后者orderby查询会很快; 2.每一行的数据量挺大 3.id为主索引,而select查询的字段也仅仅有id,那么不就是索引覆盖了呗,不用到物理磁盘回行数据,在索引上就能拿到要的数据了,但本应该查询更快的却慢了。Mysql-索引覆盖
b).分析:
肯定用的不是mysiam引擎,若是的话用这两个索引查询,其实速度是差不多的,因为索引上存的都是一个物理行的地址嘛,实际占有的数据量又不大。但如果是innodb就不一样了,它的主索引下边可是拖家带口存放着该行的所有数据的。
c).结论:
1.主因:用的innodb引擎
是聚簇索引,主键ID索引还下拖家带口的挂着该行的其他数据,导致沿着ID排序时,要跨过好多小块才能查询遍历每个ID;(而mysiam下头没那么多数据,跨过相同的数据块会更快,遍历更多的行)
2.从因:有几个字段下的数据量比较大,即拖家带口带的人还比较多,数据量比较大。每行数据量大,在磁盘存储时占用的块儿也多 3. 当时mysiam引擎时不存在这个问题
d).映射结论:
当 执行select较多时,应当使用mysiam引擎, 当执行 insert,update多时使用innodb引擎
更多结论请看:Mysql-索引总结
三.模拟测试 还原上面所说的条件,建立连个表,控制变量,除了引擎不同外,其余条件相同,主键ID主索引,联合索引(id,ver)。 1.新建表t7,mysiam引擎
2.随机插入一万条数据
3.执行查询语句,查看时间
显然,时间相差不太大,都是一个量级的。
4.新建表t8,innodb引擎

5.随机插入一万条数据
小插曲,按照上边脚本执行语句,等待时间非常长,为什么呢?因为其为聚簇索引,有主键索引ID,在创建主键索引的时候,行的数据块大量移动,有分裂移动的时间在里边。 操作是先删除主键索引ID,插入数据后在add primary key(id),再创建主键索引结构 6.执行查询语句,查看时间
显然,时间相差差别很大。
原因:两个语句都用到了聚簇索引,只是主键的跨块儿太多,而联合索引为次级索引,下边无数据,块儿少,遍历快。
7.总分析,只有t8表(innodb)的按照主键索引排序耗时多,其余还好
时间排序结论:innodb.主索引 > innodb.次索引 > mysiam
效率将近差了27倍,问题处在了哪里?
1.主因,沿着主键做order by排序,查询时会跨页很多块,时间增加 2.如果没有几个长的char字段,数据块也不大,也就不会造成这么大的差别,
比如,删除表中str1,str2,str3字段,查询时间也会大大减少,差异不明显

更多关于聚簇索引详细介绍:Mysql-聚簇索引
Tag标签: 案例分析  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规