高性能 MySQL(十一):优化特定类型的查询

高性能 MySQL(十一):优化特定类型的查询_第1张图片

❤️ 个人主页:水滴技术
订阅专栏:高性能 MySQL
支持水滴:点赞 + 收藏⭐ + 留言

系列文章目录

高性能 MySQL(一):逻辑架构

高性能 MySQL(二):并发控制(锁)

高性能 MySQL(三):事务与锁详解

高性能 MySQL(四):多版本并发控制(MVCC)

高性能 MySQL(五):设计表结构时,如何选择数据类型会更高效?

高性能 MySQL(六):索引类型

高性能 MySQL(七):11个高性能的索引策略

高性能 MySQL(八):通过优化数据访问,来解决慢查询

高性能 MySQL(九):通过重构查询语句,来解决慢查询

高性能 MySQL(十):执行一个查询的过程


大家好,我是水滴~~

本篇文章主要讲述,对特点类型的查询进行优化。

一、优化 count() 查询

count()是一个特殊的函数,有两种非常不同的作用:它可以统计某个列值的数量,并且只统计列值是非空的。另一个作用是统计结果集的行数,当 MySQL 确认括号内的表达式值不可能为空时,实际上就是在统计行数。

当我们使用count(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,MySQL 会忽略所有的列而直接统计所有的行数。

一个常见的错误是,统计行数时在括号内指定了一个列。如果希望得到结果集的行数,最好使用count(*),这样写意义清晰,性能也会很好。

二、优化关联查询

优化关联查询时,需要特别注意以下几点:

  • 确保 on 或者 using子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。
  • 确保任何的 group byorder by 中的表达式只涉及到一个表中的列,这样 MySQL 才有可能使用索引来优化这个过程。
  • 升级 MySQL 时需注意:关联语法、运算符优先级等其他可能会发生变化的地方。

三、优化子查询

关于子查询的优化,建议尽可能使用关联查询来代替。当然 MySQL 5.6 或更高版本,可以直接忽略该建议。

四、优化 group bydistinct

这两种查询都可以使用索引来优化,这也是最有效的优化办法。

当无法使用索引的时候,group by 有两种策略来完成:使用临时表或者文件排序来做分组。对于任何查询语句,这两种策略的性能都有可以提升的地方。可以通过使用提示 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来让优化器按照你希望的方式支行。

如果需要对关联查询做 group by 分组,并且是按照查找表中的某个列进行分组,那么通常采用查找表的标识列分组的效率会比其他列更高。

五、优化 limit 分页

在系统中需要进行分页操作的时候,通常会使用 limit 加上偏移量的办法来实现,同时加上合适的 order by 子句。如果有对应的索引,通常效率会不错。

一个非常常见又令人头疼的问题是,在偏移量非常大的时候,例如可能是 limit 1000,20 这样的查询,这时 MySQL 需要查询 10020 条记录,然后只返回最后 20 条,前面的 10000 条记录都将被抛弃,这样的代价非常高。要优化这样的查询,要么是在页面中限制分页的数量,要么是优化大偏移量的性能。

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

六、优化 SQL_CALC_FOUND_ROWS

分页的时候,另一个常用的技巧是在 limit 语句中加上 SQL_CALC_FOUND_ROWS 提示(hint),这样就可以获得去掉 limit 以后满足条件的行数,因此可以作为分页的总数。

实际上,MySQL 也是扫描了所有满足条件的行以后,才会知道行数,所以加上这个提示的代价可能非常高。

一个更好的设计是,将具体的页数换成“下一页”按钮。另一种做法是使用缓存技术,先缓存一部分数据。

高性能 MySQL(十一):优化特定类型的查询_第2张图片

你可能感兴趣的