MySQL之查询性能优化(十二)

查询性能优化

优化COUNT()查询

• 4.使用近似值
  有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。很多时候，计算精确值的成本非常高，而计算近似值则非常简单。曾经有一个人希望统计他的网站的当前活跃用户数是多少，这个活跃用户数保存在缓存中，过期时间为30分钟，所以每隔30分钟需要重新计算并放入缓存。因此这个活跃用户数本身就不是精确值，所以使用近似值代替是可以接受的。另外，如果要精确统计在线认数，通常WHERE条件会很复杂，一方面需要剔除当前非活跃用户，另一方面还要剔除系统中某些特定ID的"默认"用户，去掉这些约束条件对总数的影响很小，但却可能很好地提升该查询的性能。更进一步地优化则可以尝试删除DISTINCT这样的约束来避免文件排序。这样重写过的查询要比原来的精确统计的查询快很多，而返回的结果则几乎相同
• 5.更复杂的优化。
  通常来说，COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果，因此是很难优化的。除了前面的方法，在MySQL层面还能做的就只有索引覆盖扫描了，如果这还不够，就需要考虑修改应用的架构，可以增加汇总表,或者增加类似Memcached这样的外部缓存系统。可能很快你就会发现陷入到一个熟悉的困境,“快速，精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个

优化关联查询

这个话题基本上一直在讨论，这里需要特别提到的是:

• 1.确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用到c关联的时候，如果优化器的关联顺序是B、A,那么久不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
• 2.确保任何的GROUP BY 和ORDER BY中的表达式只涉及到一个表中的列，这昂MySQL才有可能使用索引来优化这个过程
• 3.当升级MySQL的时候需要注意:关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等

优化子查询

关于子查询优化给出的最重要的优化建议就是尽可能使用关联查询，至少当前的MySQL版本需要这样，"尽可能使用关联"并不是绝对的,如果使用的是MySQL5.6或更新的版本或者MariaDB，那么久可以直接忽略关于子查询的这些建议了

优化GROUP BY和DISTINCT

在很多场景下，MySQL都使用同样的方法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成:使用临时表或者文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。如果需要对关联查询做分组(GROUP BY)，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。例如下面的查询效率不会很好:

mysql> SELECT actor.first_name,actor.last_name, COUNT(*)-> FROM sakila.film_actor-> INNER JOIN sakila.actor USING(actor_id)-> GROUP BY actor.first_name,actor.last_name;

如果查询按照下面的写法效率则会更高:

mysql> SELECT actor.first_name,actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;

使用actor.actor_id列分组的效率甚至会比使用film_actor.actor_id更好。这点通过简单的测试即可验证。这个查询利用了演员的姓名和ID直接相关的特点，因此改写后的结果不受影响，但显然不是所有的关联语句的分组查询都可以改写成在SELECT中直接使用非分组列的形式的。甚至可能会在服务器上设置SQL_MODE来禁止这样的写法。如果是这样，也可以通过MIN()或者MAX()函数来绕过这种限制，但一定要清楚，SELECT后面出现的非分组列一定是直接依赖分组列，并且在每个组内的值是唯一的，或者是业务上根本不在乎这个值具体是什么:

mysql>SELECT MIN(actor.first_name), MAX(actor.last_name), .....;

较真的人可能会说这样写的分组查询是有问题的，确实如此。从MIN()或者MAX()函数的用法就可以看出这个查询是有问题的。但若更在乎的是MySQL运行查询的效率时这样做也无可厚非。如果实在较真的话也可以改写成下面的形式

mysql> SELECT actor.first_name,actor.last_name, cnt FROM sakila.actor INNER JOIN (  SELECT actor_id, COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id ) AS c USING(actor_id);

这样写更满足关系理论，但成本有点高，因为子查询需要创建和填充临时表，而子查询中创建的临时表是没有任何索引的(值得一提的是，MariaDB修复了这个限制)。在分组查询的SELECT中直接使用非分组列通常不是什么好主意，因为这样的结果通常是补丁的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。碰到的大多数这种查询最后都导致了故障(因为MySQL不会对这类查询返回错误)，而且这种写法大部分是由于偷懒而不是为优化而故意这么设计的。建议始终使用含义明确的语法。事实上，建议对MySQL的SQL_MODE设置为包含ONLY_FULL_GROUP BY，这时MySQL会对这类查询直接返回一个错误，提醒你需要重写这个查询。如果没有通过ORDER BY子句显示地指定排序列，当查询使用GROUP BY子句地时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字

优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。可以通过EXPLAIN来观察其执行计划，特别要注意是否通过文件排序或者临时表实现的，然后再去掉WITH ROLLUP子句看执行计划是否相同。也可以通过前面介绍的优化器提示来固定执行计划。很多时候，如果可以，在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果。最好的办法是尽可能地将WITH ROLLUP功能转移到应用程序中处理