mysql优化特定类型的查询

Mysql  /  houtizong 发布于 3年前   167

本节所介绍的查询优化的技巧都是和特定版本相关的，所以对于未来mysql的版本未必适用。1 优化count查询  对于count这个函数的网上的大部分资料都是错误的或者是理解的都是一知半解的。在做优化之前我们先来看看  真正的count()函数的作用到底是什么。  count()是一个特殊的函数，有两种非常不同的作用，他可以统计某个列值的数量，也可以统计行数。  在统计列的时候，要求列值是非空的，也就是说只统计有值得列的数量。由于大多数人对NULL的理解有限，  所以并不能很深入的理解count()函数。  count的另一个作用是统计结果集的行数，最简单的方法是count(*),当我们执行这一的查询的时候，优化器会  忽略掉所有的列值而直接统计所有的行数，这样的定义更清晰性能也会更好，常见的错误是在括号内定义了一个列  却希望统计结果集的行数。    简单的优化  现有查询语句 select count(*) from world.city where id >5;  通过show status查看该查询需要扫描几千行数据，如果将条件翻转一下，先查找id<=5的数量，然后使用  总数一减就能得到同样的结果，却可以将扫描的行数减少到5行以内。    select (select count(*) from world.city) - count(*) from world.city where id <=5;  这样可以大大减少需要扫描的行数，是因为在查询优化阶段会将其中的子查询直接当做一个常数来处理，  可以查看其执行计划来验证。    在大多数场景中并不要求一定要使用count统计出精确数据的行数，这时候我们要充分利用近似值，避免让数据库  做更多的工作。即使迫不得已使用count计数，也要力求约束条件尽可能的简单，要删除掉distinct这样的约束来  避免文件排序。    快速、精确、实现简单 三者永远只能满足其二，必须舍弃其一。  2 优化关联查询  1 确保on或者using字句中的列上有索引。在创建索引的时候就要考虑关联的顺序。    当表A和表B用列C关联的时候，如果优化器的关联顺序是B、A的时候，那么就不需要在B表的对应列上建立索引。没有用到的索引只会带来负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。  2 确保任何的group by 和order by中的表达式只涉及到一个表中的一列，          3  优化limit分页      在进行分页操作的时候 我们通常会使用limit加上偏移量的办法实现，同时加上合适的order by子句，  如果索引使用的得当，通常效率都会不错。否则mysql将会做大量的文件排序操作。  一个非常常见又非常令人头疼的问题是，在偏移量非常大的时候，例如limit 10000,20  这时候mysql 需要查询 10020条记录然后只返回最后20条记录，前面10000条记录都将被抛弃，这样的代价非常高  如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这样的查询，  要么是在页面中限制分页的数量，要么是优化大便宜量的性能。    优化此类分页查询的一个最简单的办法就是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后再  根据需要做一个关联操作再返回所需要的列。对于偏移量很大的时候，这样做的效率会提升非常大。    考虑下面的查询   select film_id,desc from film order by title limit 500,5;      如果这张表非常大，那么最好修改一下   select film_id ,desc from film         inner join(select film_id from film order by title limit 500,5)as lim using(film_id);      这里的延迟关联将大大提升查询效率，他让mysql扫描尽可能少的页面，获取需要访问的记录后再根据关联   列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的limit子句。      也可以将limit查询转换为已知位置的查询，让mysql通过范围扫描获得到对应的结果。例如在一个位置上有索引，并且预先计算出了边界值，   上面的查询就可以改写为：     select film_id,desc from film where position between 500 and 504 order by position;    当然我们还可以使用租借书签的方式来提高性能。   如果我们已经知道 需要返回的记录是主键在16029到16040之间的数据，那么下一页查询   就可以如下进行   select film_id ,desc from film where film_id < 16040 order by film_id desc limit 20;   因为我们已经使用书签记录下了最新一条数据的film_id 并且film_id主键是单调增长的，所以无论翻页到多后面其性能都是很好的。      4 优化union查询       除非确实需要服务器消除重复的行，否则就一定要使用union all ，这一点非常重要。如果缺少all关键字   mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高   即使有all关键字 mysql仍然会使用临时表存储结果。