传统的mysql针对辅助索引查找数据实现的方法。这里主要介绍innodb存储引擎对于辅助索引的查找实现方式。我们知道,mysql在对辅助索引取数据的时候,先是通过索引页的叶子节点找到对应的主键id,再通过主键id找到对应的数据页,在数据页中最后通过二分查找找到对应的数据。这里我们设想一下,假如在一张表中对某个字段建立一个辅助索引,而这个字段有一些重复的数据,那么我们根据这个字段去做where条件的时候,势必每次取到的id不一定是顺序的,既然不会是顺序的,那么必然会产生一定的随机io。在计算机中,随机io的速度比顺序的io的速度慢很多,因为在一个柱面中,随机io必然会造成磁头的随机旋转,从而产生一定量的磁盘io,而顺序io则可降低到最低。面对这种情况,就是我们下面介绍的mrr优化大显身手的地方了。
上面已经说过,对非唯一的辅助索引查询,由于每次在辅佐索引页叶子节点上查找主键的id的时候不一定是顺序的,如果每次都通过查询出来的主键盘id去拿数据的话,就会产生我们上面所说的随机io的情况。mrr优化就在这里做了功夫,在通过辅佐索引页上拿到主键id后,并不是通过id直接去数据页中取数据。而是先通过排序算法,把取到的主键id按照从小到大的方式排序,然后再通过书签查找,取得对应的数据。这样就可以把随机io的情况降到最低。
上面说到的是mrr的一个好的方式,其实mrr优化还有一个重要的作用就是避免了缓冲池中页的频繁更改。再没有启用mrr优化之前,由于主键的id是随机取的,那么可能每次取到的数据都不在同一个页中。比如第一个数据在一个页中,第二个数据又在另一个页中,而第三个数据又在第一个页中,在缓冲池不够大的时候,这样的情况会造成缓冲池中的页不断的离开缓冲池,然后又进入缓冲池,从而造成缓冲池的热点数据频繁更新。而启用了mrr优化以后,由于主键是顺序的,则可以把此开销降为最低。
优化目的:将随机IO转为顺序IO
MRR的使用的优化目的是减少磁盘的随机访问,其前提是使用索引,找到所需的行,并按照主键进行排序,并将随机访问转换为较为顺序的数据访问,其优化使用于range、ref和eq_ref类型的查询,也就是说非这些类型的查询,优化也就失去了作用
set global optimizer_switch ='mrr=on,mrr_cost_based=on';
select * from salaries where salary >=10000 and salary <40000 and emp_no <20000;
explain
select * from salaries
where (from_date between '1986-01-01' and '1995-01-01')
and (salary between 38000 and 40000);
set global optimizer_switch='mrr=on,mrr_cost_based=off';
mrr=on:表示启用
mrr_cost_based:表示通过cost based的方式来选择是否启用mrr
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1762910/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/91975/viewspace-1762910/