MySQL - 优化策略

2021/07/14 database 共 1106 字,约 4 分钟
Bob.Zhu

优化 SQL 的策略

优化 INSERT

需要对一张表插入很多行数据时,应该尽量使用一次性插入多个值的 INSERT 语句,这种方式将缩减客 户端与数据库之间的连接、关闭等消耗,效率比多条插入单个值的 INSERT 语句高。也可以关闭事务的 自动提交,在插入完数据后提交。当插入的数据是按主键的顺序插入时,效率更高。

COUNT 查询

COUNT 是一个特殊的函数,它可以统计某个列值的数量,在统计列值时要求列值是非空的,不会统计 NULL 值。如果在 COUNT 中指定了列或列的表达式,则统计的就是这个表达式有值的结果数,而不是 NULL。

COUNT 的另一个作用是统计结果集的行数,当 MySQL 确定括号内的表达式不可能为 NULL 时,实际上就 是在统计行数。当使用 COUNT() 时, 不会扩展成所有列,它会忽略所有的列而直接统计所有的行数。

LIMIT 分⻚

在偏移量非常大的时候,需要查询很多条数据再舍弃,这样的代价非常高。要优化这种查询,要么是在 ⻚面中限制分⻚的数量,要么是优化大偏移量的性能。最简单的办法是尽可能地使用覆盖索引扫描,而 不是查询所有的列,然后根据需要做一次关联操作再返回所需的列。

还有一种方法是从上一次取数据的位置开始扫描,这样就可以避免使用 OFFSET。其他优化方法还包括 使用预先计算的汇总表,或者关联到一个冗余表,冗余表只包含主键列和需要做排序的数据列。

GROUP BY

如果没有通过 ORDER BY 子句显式指定要排序的列,当查询使用 GROUP BY 时,结果自动按照分组 的字段进行排序,如果不关心结果集的顺序,可以使用 ORDER BY NULL 禁止排序。

关联查询

确保 ON 或 USING 子句中的列上有索引,在创建索引时就要考虑到关联的顺序。

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

在 MySQL 5.5 及以下版本尽量避免子查询,可以用关联查询代替,因为执行器会先执行外部的 SQL 再 执行内部的 SQL。(笛卡尔积)

UNION 查询

MySQL 通过创建并填充临时表的方式来执行 UNION 查询,除非确实需要服务器消除重复的行,否则一 定要使用 UNION ALL,如果没有 ALL 关键字,MySQL 会给临时表加上 DISTINCT 选项,这会导致对 整个临时表的数据做唯一性检查,这样做的代价非常高。

使用用户自定义变量

在查询中混合使用过程化和关系化逻辑的时候,自定义变量可能会非常有用。用户自定义变量是一个用 来存储内容的临时容器,在连接 MySQL 的整个过程中都存在,可以在任何可以使用表达式的地方使用 自定义变量。例如可以使用变量来避免重复查询刚刚更新过的数据、统计更新和插入的数量等。

参考资料

文档信息

Search

    Table of Contents