Mysql分页&关联查询优化

RiverLi 发布于2019-08-01 13:37 / 2375人阅读

摘要：当升级的时候需要注意关联语法运算符优先级等其他可能会发生变化的地方。优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。这个技术也可以用于优化关联查询中的子句。

以下内容参考《高性能Mysql》

优化关联查询

这个话题基本上整本书都在讨论，这里需要特别提到的是：

确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。
当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。

确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL
才有可能使用索引来优化这个过程。

当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化
的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果

优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同
时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需
要做大量的文件排序操作。

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候注”，例如可能是LIMIT
1000,20这样的查询，这时MySQL需要查询1 0 020条记录然后只返回最后20条，前面
10 000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，
那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分
页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所
有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样
做的效率会提升非常大。考虑下面的查询：

mysql> SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

如果这个表非常大，那么这个查询最好改写成下面的样子：

mysql> SELECT film.film_id,Film.description
    ->  FROM  sakila.film
    ->INNER JOIN(
    ->  SELECT film.film_id FROM sakila.film
    ->  ORDER BY title LIMIT 50,5
    ->) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需
要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联
查询中的LIMIT子句。

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对
应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询
就可以改写为：

mysql> SELECT film_id, description FROM sakila.Film
       -> WHERE position BETWEEN so AND 54 0RDER BY position;

对数据进行排名的问题也与此类似，但往往还会同时和GROUP BY混合使用。在这种情况
下通常都需要预先计算并存储排名信息。

LIMIT和OFFST的问题，其实是OFFSET的问题．它会导致MySQL扫描大量不需要的
行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该
书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租借记录做
翻页，那么可以根据最新一条租借记录向后追溯，这种做法可行是因为租借记录的主键
是单调增长的。首先使用下面的查询获得第一组结果：

mysql> SELECT * FROM sakila.rental
      -> ORDER BY rental id DESC LIMIT 20;

假设上面的查询返回的是主键为1 6 049到1 6 03 0的租借记录，那么下一页查询就可以
从1 6 030这个点开始：

mysql> SELECT * FROM sakila*rental
    -> WHERE rental id < 16030,
    -> ORDER BY rental id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。
其他优化办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主
键列和需要做排序的数据列。还可以使用Sphinx优化一些搜索操作，参考附录F可以获
得更多相关信息。

云数据库MySQL 分页关联分页优化分页性能优化 limit分页优化

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48796.html

MySQL分页查询优化

摘要：如果素所的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据，要优化这种查询，要么在页面中限制分页的数量，要么是优化大偏移量的性能。优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖查询，而不是查询所有的列。之前搬砖的时候遇到对行数大的表进行分页的操作，性能好差。最近在读《高性能MySQL》，正好讲到这个方面的，记录一下（基本上都是原文）。优化LIMIT分页在...

wh469012917 2019-07-31 16:18 评论0 收藏0
MySQL分页优化实验与总结

摘要：前言分页的优化是日常开发中经常遇到的问题，笔者在此做一个经验总结，并附上相应的实验过程。应用程序层面的分页优化设计除了对语句进行优化，我们还可以在应用程序层面对分页进行一些优化设计。前言分页的sql优化是日常开发中经常遇到的问题，笔者在此做一个经验总结，并附上相应的实验过程。实验准备若不想亲自实验的，可以直接跳过这一节。但还是建议大家做一下实验，眼见为实。 1.安装测试数据库 ...

Tikitoo 2019-08-01 14:00 评论0 收藏0
MySql查询性能优化

摘要：如果缓存的是关联查询的结果，那么其中的一个表发生变化，整个缓存就失效了。例如上面用代替关联查询比随机的关联更加高效。优化关联查询，要确保或者子句中的列上有索引，并且在建立索引时需要考虑到关联的顺序。避免向数据库请求不需要的数据在访问数据库时，应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源，并增加网络开销。例如在处理分页时，应该使用LIMIT限制My...

魏宪会 2019-07-31 17:21 评论0 收藏0
对一个26万数据MYSQL表的Yii2程序优化实战之二【开刀数据表】

摘要：在这里我并不打算对其进行所谓的优化，具体原因可以看北哥工兵连引的一篇文章应用的缓存设计模式，如果有一天它真的影响了，我们可以在关联层加一个缓存来解决。上一节我们对订餐系统后台欢迎页面的统计图表进行了处理，在本节我们将对订单列表和详细订单列表进行优化。中间穿插个小广告哈，北哥在segmengfault上的讲座下周二开始，主要说yii2和微信支付的那点事，感兴趣的可以去看看 https...

whataa 2019-08-01 13:31 评论0 收藏0
Elasticsearch如何做到亿级数据查询毫秒级返回？

摘要：类似于里的推荐商品不断下拉出来一页一页的类似于微博中，下拉刷微博，刷出来一页一页的，你可以用，关于如何使用，自行上网搜索。但是，这种只适合于类似微博下拉翻页的，不能随意跳到任何一页的场景。 showImg(https://segmentfault.com/img/bVbtkDJ?w=400&h=188);本文来源 | https://zhuanlan.zhihu.com/p/... 导...

jcc 2019-07-16 15:38 评论0 收藏0