mysql 性能优化

影响结果集的解读

• 查询条件与索引的关系决定 影响结果集

影响结果集不是输出结果数，不是查询返回的记录数，而是索引所扫描的结果数

范例：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)，查询缓慢。
解决方法：
只涉及上下翻页情况每次查询的时候将该页查询结果中最大的$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