MySQL之查询性能优化五（优化特定类型的查询）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之查询性能优化五（优化特定类型的查询），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3987字，纯文字阅读大概需要6分钟。

内容图文

1.优化count()查询
count()聚合函数，以及如何优化使用了该函数的查询，很可能是mysql中最容易被误解的前10个话题之一

count() 是一个特殊的函数，有两种非常不同的作用。它可以统计某个列值的数量，也可以统计行数。
统计列值 要求列值是非空的。(不统计null，即null值计数为0)

count()的另一个用处是统计结果集的行数。当mysql确认括号的表达式值不可能为空时，实际上就是统计
行数。最简单的就是当我们使用count(*)的时候，这种情况下统配符*并不会像我们猜想的那样扩展成所有
的列，实际上，它会忽略所有的列而直接统计所有的行数。

假如在同一个查询中统计同一个列不同值得数量
select sum(if(color = ‘blue‘, 1, 0)) as BLUE, sum(if(color = ‘red‘, 1, 0)) as RED from items

select count(color= ‘blue‘ or null) blue, count(color= ‘red‘ or null) red, from items

2.优化关联查询
这个话题讨论的比较频繁，这里需要特别提到的是：
确保on或者using子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。
当表A和表B用列c关联的时候，如果优化器的关联顺序时B，A，那么就不需要在B表的对应
列建立索引。一般来说，没有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。

确保任何的group by 和order by 的表达式只涉及到一个表中的列。这样mysql才有可能使用索引来优化这个过程

当升级mysql的时候需要注意：关联语法，运算符优先级等其他可能会发生变化的地方。

3.优化子查询
尽可能用关联代替。

4.优化group by 和 distinct
在很多场景下，mysql都使用同样的方法优化这两种查询，事实上，mysql优化器会在内部处理的时候
相互转化这两类查询。他们都可以使用索引来优化，这也是最有效的优化方法。

优化 group by with rollup
分组查询的一个变种就是要求mysql对返回的分组结果再做一次超级聚合。你可以使用with rollup
来实现这种优化。最好的办法是尽可能将with rollup 功能转移到应用程序处理。

5.优化limit和offset
在系统需要进行分页操作的时候，我们通常会使用LIMIT加OFFSET的办法来实现，同时加上合适order by子句。
如果有对应的索引，通常效率会不错，否则，mysql需要做大量的文件排序。

在OFFSET大的时候，查询会很糟糕。要优化这种查询，要么是在页面中限制分页的数量，要么优化大偏移量查询的性能。

优化此类分页查询最简单的一个方法是使用索引覆盖扫描，而不是查询所有的列。
然后根据一次关联操作再返回需要的列。在偏移量很大的时候，这样会大大提升效率：

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_id FROM sakila.film
　　-> ORDER BY title LIMIT 50, 5
　　-> ) AS lim USING(film_id);

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

有时候也可以将limit查询转换为已知位置的查询，让mysql通过范围扫描获得到对应的结果。

6.优化 SQL_CALC_FOUND_ROWS
分页的时候，另一个常用的技巧是在limit语句中加上SQL_CALC_FOUND_ROWS提示(hint),
这样就可以获取去掉limit以后满足条件的行数，因此可以作为分页的总数。看来，mysql做了
非常"高深"的优化，像是通过某种方法预测了总行数。但实际上，mysql只有再扫描所有满足条件的行
以后才知道行数，所以加上这个提示以后，不管是否需要，mysql都会扫描所有满足条件的行，然后再
抛弃不需要的行，而不是在满足limit的行数后就终止扫描。所以该提示的代价可能非常高。

7.优化union查询
mysql总是通过创建并填充临时表的方式来执行union查询。因此很多优化策略在union查询中都没法
很好的使用。经常需要手工地将where，limit，order by等子句下推到union的各个子查询中，以便
优化器可以充分利用这些条件进行优化。

除非确实需要服务器消除重复的行，否则一定要使用union all ，这一点很重要。
如果没有ALL关键字，mysql会给临时表加上distinct选项，这会导致对整个临时表的
数据唯一检查。这样的代价非常高。即使有ALL关键字，mysql仍然会使用临时表存储结果。
事实上，mysql总是将结果放入临时表，然后再读出，再返回给客户端，很多时候这样做是没有必要的。

8.静态查询分析

9.使用用户自定义变量
set @one := 1

MySQL之查询性能优化五（优化特定类型的查询）

标签：

本文系统来源：http://www.cnblogs.com/w2154/p/4705453.html

内容总结

以上是互联网集市为您收集整理的MySQL之查询性能优化五（优化特定类型的查询）全部内容，希望文章能够帮你解决MySQL之查询性能优化五（优化特定类型的查询）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端