影响结果集的解读
- 查询条件与索引的关系决定 影响结果集
影响结果集不是输出结果数,不是查询返回的记录数,而是索引所扫描的结果数
范例:
select * from user where area=’厦门’and sex=’女’
假设索引为 area假设User表中area=’厦门’的有125000条,而搜索返回结果为60233条。影响结果集是125000条,索引先命中125000条厦门用户,再遍历以sex=’女’进行筛选操作,得到60233条结果。
如果该SQL 增加limit 0,30的后缀。查询时,先命中area=’厦门’,然后依顺序执行sex=’女’筛选操作,直到满足可以返回30条为止,所涉及记录数未知。除非满足条件的结果不足30条,否则不会遍历125000条记录。
但是如果SQL中涉及了排序操作,比如order by lastlogin desc 再有limit 0,30时,排序需要遍历所有area=’厦门’的记录,而不是满足即止。
- 影响结果集 越趋近 于实际输出或操作的目标结果集,索引效率越高
- 影响结果集与查询开销的关系可以理解为线性相关。减少一半影响结果集,即可提升一倍查询效率!当一条搜索query可以符合多个索引时,选择影响结果集最少的索引。
- SQL的优化,核心就是对结果集的优化,认识索引是增强对结果集的判断,基于索引的认识,可以在编写SQL的时候,对该SQL可能的影响结果集有预判,并做出适当的优化和调整。
- Limit 的影响
如果索引与查询条件和排序条件完全命中,影响结果集就是limit后面的数字($start + $end),
比如:limit 200,30 影响结果集是230. 而不是30.
如果索引只命中部分查询条件,甚至无命中条件,在无排序条件情况下,会在索引命中的结果集中遍历到满足所有其他条件为止。
比如select * from user limit 10
; 虽然没用到索引,但是因为不涉及二次筛选和排序,系统直接返回前10条结果,影响结果集依然只有10条,就不存在效率影响。
如果搜索所包含的排序条件没有被索引命中,则系统会遍历是所有索引所命中的结果,并且排序。
例如Select * from user order by timeline desc limit 10
; 如果timeline不是索引,影响结果集是全表,就存在需要全表数据排序,这个效率影响就巨大。再比如Select * from user where area=’厦门’order by timeline desc limit 10
; 如果area是索引,而 area + timeline 未建立索引,则影响结果集是所有命中area=’厦门’的用户,然后在影响结果集内排序。
栗子
论坛翻页优化
背景,常见论坛帖子页SQL:
select * from post where tagid=$tagid order by lastpost limit $start, $end
翻页。索引为tagid+lastpost 复合索引
挑战,超级热帖,几万回帖,用户频频翻到末页,limit 25770,30 一个操作下来,影响结果集巨大(25770+30),查询缓慢。
解决方法:
只涉及上下翻页情况每次查询的时候将该页查询结果中最大的 l a s t p o s t 和 最 小 的 分 别 记 录 为 lastpost和最小的分别记录为 lastpost和最小的分别记录为minlastpost 和$maxlastpost ,使用这种方式,影响结果集只有30条,效率极大提升。
上翻页查询为
select * from post where tagid=$tagid and lastpost<$minlastpost order by lastpost desc limit 30;
下翻页为
select * from post where tagid=$tagid and lastpost>$maxlastpost order by lastpost limit 30;
涉及跳转到任意页:
互联网上常见的一个优化方案可以这样表述,
select * from post where tagid=$tagid and lastpost>=(select lastpost from post where tagid=$tagid order by lastpost limit $start,1) order by lastpost limit 30; 或者select * from post where pid in (select pid from post where tagid=$tagid order by lastpost limit $start,30);(第2条S语法在新的mysql版本已经不支持,新版本mysql in的子语句不再支持limit条件,但可以分解为两条SQL实现,原理不变,不做赘述)
以上思路在于,子查询的影响结果集仍然是$start +30,但是数据获取的过程(Sending data状态)发生在索引文件中,而不是数据表文件,这样所需要的系统开销就比前一种普通的查询低一个数量级,而主查询的影响结果集只有30条,几乎无开销。但是切记,这里仍然涉及了太多的影响结果集操作。
延伸问题:
来自于uchome典型查询SELECT * FROM uchome_thread WHERE tagid='73820' ORDER BY displayorder DESC, lastpost DESCLIMIT $start,30
;
如果换用如上方法,
上翻页代码
SELECT * FROM uchome_thread WHERE tagid='73820' and lastpost<$minlastpost ORDER BY displayorder DESC,lastpost DESCLIMIT 0,30
;
下 翻 页 代 码
SELECT * FROM uchome_thread WHERE tagid='73820' and lastpost>$maxlastpost ORDER BY displayorder DESC, lastpost ASCLIMIT 0,30
;
==这里涉及一个order by 索引可用性问题,当order by中复合索引的字段,一个是ASC,一个是DESC 时,其排序无法在索引中完成。所以只有上翻页可以正确使用索引,影响结果集为30。下翻页无法在排序中正确使用索引,会命中所有索引内容然后排序,效率低下。
本文取自百度文库-Mysql性能优化教程一文
*2021年2月24日09:11:46