mysql limit 索引失效_MySQL,针对limit优化，以及讨论using filesort、using temporary和索引失效的问题...

针对limit优化，以及讨论using filesort、using temporary和索引失效的问题

表结构sql

CREATE TABLE `a` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`code` CHAR(36) DEFAULT NULL,

`name` CHAR(20) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `code` (`code`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`code` CHAR(36) DEFAULT NULL,

`col1` CHAR(22) DEFAULT NULL,

`col2` CHAR(22) DEFAULT NULL,

`col3` CHAR(22) DEFAULT NULL,

`col4` CHAR(22) DEFAULT NULL,

`col5` CHAR(22) DEFAULT NULL,

`col6` CHAR(22) DEFAULT NULL,

`col7` CHAR(22) DEFAULT NULL,

`col8` CHAR(22) DEFAULT NULL,

`col9` CHAR(22) DEFAULT NULL,

`col10` CHAR(22) DEFAULT NULL,

`col11` CHAR(22) DEFAULT NULL,

`col12` CHAR(22) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `code` (`code`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入测试数据测试

INSERT INTO a (NAME) VALUES('n');

INSERT INTO a (NAME) SELECT NAME FROM a; -- 执行屡次，达到必定数据量

UPDATE a SET CODE = UUID();

UPDATE a SET NAME = id;

INSERT INTO b (CODE) SELECT CODE FROM a; -- 以a表code做为b表code

-- 设置b表的其它字段值

UPDATE b SET

col1=CONCAT('c',id),col2=CONCAT('c',id),col3=CONCAT('c',id)

,col4=CONCAT('c',id),col5=CONCAT('c',id),col6=CONCAT('c',id)

,col7=CONCAT('c',id),col8=CONCAT('c',id),col9=CONCAT('c',id)

,col10=CONCAT('c',id),col11=CONCAT('c',id),col12=CONCAT('c',id);

-- 再对b表翻倍2次，即4倍，执行2次

INSERT INTO b (CODE,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,`col12`)

SELECT CODE,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,`col12` FROM b;

-- a表有131072行，b表有524288行

-- 查询总结果集优化

SELECT COUNT(1)

FROM a

INNER JOIN b ON a.code=b.code;

-- count: 524288spa

-- 未优化前

code

EXPLAIN

SELECT a.id AS aid,a.name

,b.id AS bid,b.code,b.col1

,b.col2,b.col3,b.col4,b.col5

,b.col6,b.col7,b.col8,b.col9

,b.col10,b.col11

FROM a

INNER JOIN b ON a.code=b.code

ORDER BY b.id

LIMIT 520000,20;

执行计划是这样的： 对象

-- 耗时：14.2sblog

--优化1.先用临时表查询一部分字段,在inner join 查询所有须要的字段

排序

EXPLAIN

SELECT t.aid,t.name

,b.id AS bid,b.code,b.col1

,b.col2,b.col3,b.col4,b.col5

,b.col6,b.col7,b.col8,b.col9

,b.col10,b.col11

FROM (

SELECT a.id AS aid,b.id,a.name

FROM a INNER JOIN b ON a.code=b.code

ORDER BY b.id

LIMIT 520000,20

) t INNER JOIN b ON t.id=b.id;

执行计划是这样的：  索引

解释一下执行计划id这一列：id的数值说明了多表链接的链接顺序，原则是：从大到小，从上到下，文档

因此，链接顺序第3行，第4行，第1行，第2行

-- 耗时：4s 说明起做用了,此方法优化的效率取决于临时表t的字段数量

-- 2.反向查询法，当偏移量大于where筛选后总量的【一半】时,可使用反向查找(原理下面讲)

-- ,排序规则相反,缩小偏移量offsize,

-- 新偏移量=where筛选后总数-旧偏移量-size，因此，offsize=524288 - 520000 - 20 = 4268

-- 不过这样查出来的数据顺序与咱们须要的结果是相反的，因此须要再倒转结果集，或者渲染时反向遍历

EXPLAIN

SELECT t.aid,t.name

,b.id AS bid,b.code,b.col1

,b.col2,b.col3,b.col4,b.col5

,b.col6,b.col7,b.col8,b.col9

,b.col10,b.col11

FROM (

SELECT a.id AS aid,b.id,a.name

FROM a INNER JOIN b ON a.code=b.code

ORDER BY b.id DESC

LIMIT 4268,20

) t INNER JOIN b ON t.id=b.id ORDER BY t.id ASC;

-- 最后这个order要注意一下，不要by b.id，不然会出现Using temporary; Using filesort

-- by t.id只会有Using filesort，下面的sql同理，后面会解释原理的。

执行计划是这样的：

-- 耗时：3.8s 这个变化很微弱？或者说根本没用？我觉的可能被其它因素影响了，这个咱们先放着，下面再验证

-- 优化到这一步,很明显3.8秒不符合正常的要求.

-- 通过分段执行发现,效率出现大损耗的地方是，第10行:【ORDER BY b.id DESC】

-- ，这时我就有疑惑了，虽然这里须要排序的数据量很是大,但b.id不是索引字段吗?

-- 此处应该用到索引排序的!能够经过这个执行计划第3行的Extra发现并无,而是用到了额外的排序(using filesort),为何?

-- 网上博客: "要尽量的保证排序字段在驱动表中"

-- 先解释什么是驱动表:这一切还得从MySQL多表链接的处理方式提及，首先MySQL优化器要肯定以谁为驱动表，

-- 也就是说以哪一个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪一个表的结果集小，

-- 就以哪一个表为驱动表，固然MySQL优化器实际的处理方式会复杂许多，还会根据order by 的字段来优化

-- 也就是说若是你的order by 子句排序字段在大表的话，优化器‘或许’会选择大表为驱动表，固然前提是

-- 你的另外一张表的关联字段创建了索引，不然仍是会选择关联字段有索引的表为‘被’驱动的对象，好比：

-- if a表 < b表，a表的code字段没有索引，而b表的字段code有索引，则选择a为驱动，b为被驱动，a主b从

-- if a < b, a.code有索引，b.code无索引，则b主a从

-- if a < b, a.code和b.code都有索引，先不考虑order by的排序字段，则a主b从

-- if a < b, a.code和b.code都有索引，若有order by b.id,则不肯定，若是a 小于 b不少的话

--     仍是会选择a为驱动，a主b从，若是差异不明显的话，b主a从，

-- 正由于优化器选择驱动表的复杂性，因此有时人是很难判断出哪一个是驱动表，刚开始验证这个时，

-- a的数据量是b的1/2,原本‘反向查询法’那一块sql，执行的时候仍是以a表为驱动，仍是很耗时的，

-- 但过了一下子之后，数据我并无改变，再执行发现很快,瞬间懵逼了，经过explain 查看驱动表变成了b表

-- 我把b表翻倍之后，才让驱动表稳定为a表。

-- 这让我不得不怀疑MySQL有JIT的优化，至于事实有没有我没有找到资料文档。

-- 驱动表的解释就到这里了，下面接着理解“要尽量的保证排序字段在驱动表中”这句话。

-- a = 1/4b,

-- 上面这个sql语句并无where筛选过滤,因此a表结果集 < b表结果集,则a表是驱动

-- 显而易见,排序字段b.id是b表的字段,不在驱动表内

-- ,因而乎不可避免的出现了"额外的排序"[Using filesort],甚至使用了"临时表"[Using temporary]

-- 更准确的说,

-- 1.用非驱动表的字段排序会出现[Using temporary]

--   则无论排序的字段有没有索引都会[Using filesort],

-- 3.而用驱动表字段排序则不会[Using temporary],

--   则要想都没有[Using temporary]和[Using filesort]必须是驱动表的索引字段

-- 即两个条件:1:驱动;2:索引

-- 既然这样,那咱们对语句修改，修改后的语句是:

EXPLAIN

SELECT t.aid,t.name

,b.id AS bid,b.code,b.col1

,b.col2,b.col3,b.col4,b.col5

,b.col6,b.col7,b.col8,b.col9

,b.col10,b.col11

FROM (

SELECT a.id AS aid,b.id,a.name

FROM a INNER JOIN b ON a.code=b.code

ORDER BY a.id DESC

LIMIT 4268,20

) t INNER JOIN b ON t.id=b.id ORDER BY t.aid ASC;

执行计划是这样的：

发现执行计划第3行Extra没有了 Using temporary; Using filesort,而且type变成了index

-- 耗时：0.02s

-- 如今咱们继续验证反向查找法是否有用，在此语句基础去掉反向查找法的优化看看效率怎样

EXPLAIN

SELECT t.aid,t.name

,b.id AS bid,b.code,b.col1

,b.col2,b.col3,b.col4,b.col5

,b.col6,b.col7,b.col8,b.col9

,b.col10,b.col11

FROM (

SELECT a.id AS aid,b.id,a.name

FROM a INNER JOIN b ON a.code=b.code

ORDER BY a.id

LIMIT 520000,20

) t INNER JOIN b ON t.id=b.id;

-- 1.7s，1.7s > 0.02s 证实了反向查找法有效

-- 反向查找法有效的原理：首先limit 的本质是扫描offsize + size 次，舍弃前offsize行数据，只去后size行

-- 好比 limit 520000，20 ，则扫描520000+20行，舍弃前520000行，取后20行

-- 因此得出结论：偏移量offsize越小扫描的的次数越少，效率更高，经过比较explain的rows的信息能够看到扫描次数区别

-- 通过把排序字段改成驱动表的字段后(b.id ->  a.id),效果立竿见影,只需0.02s和1.7s-- 这个提高级别是很大的

-- 可是有个问题,排序的字段变了,结果固然就不同了

-- ,这个问题只能具体状况具体分析了,到底用不用驱动表的字段来排序要根据具体业务逻辑来

-- 若是必定要保证排序字段在驱动表中,须要用STRAIGHT_JOIN 代替 inner join 来强制链接顺序

EXPLAIN

SELECT t.aid,t.name

,b.id AS bid,b.code,b.col1

,b.col2,b.col3,b.col4,b.col5

,b.col6,b.col7,b.col8,b.col9

,b.col10,b.col11

FROM (

SELECT a.id AS aid,b.id,a.name

FROM b STRAIGHT_JOIN a ON a.code=b.code

ORDER BY b.id DESC

LIMIT 4268,20

) t INNER JOIN b ON t.id=b.id ORDER BY t.id ASC;

执行计划是这样的：

-- 耗时：0.06s, 效率又差了点,这是意料之中的，大表驱动小表效率不差才怪,若是这里b表更大的话，效率会更差

-- 这要注意,改变驱动表，好比，b驱动a，若是a.code原来没有索引

-- ,则必需要另外为a.code创建索引,不然

-- ,数据量一大,等待执行结果就得looong time了

-- 总结:优化要根据实际状况来,没有绝对的好的语句,也没有永远美的语句

-- 不一样时期的语句优化也不同

-- 你们可能会遇到相似下面的问题：本来运行良好的查询语句，过了一段时间后，可能会忽然变得很糟糕。

-- 一个很大可能的缘由就是数据分布状况发生了变化，好比：b表变得比a表大

-- ，或者联合查询的where字句的条件筛选出现新变化，从而致使MySQL优化器对驱动表的选择发生了变化

-- ，进而出现on字句使用索引失效的状况，除非强制驱动表STRAIGHT_JOIN。