性能调优攻略｜SQL语句优化

茶码道 2017-02-26 15:39

1/ 前言

关于 MySQL 优化的方式在《千万级的大表！MySQL这样优化更好》文章已经有提到过。今天我们是谈谈关于 SQL 语句的优化部分。

日常 SQL 性能优化过程中，会借助一些查询分析工具来帮忙，例如：

1. Solarwinds的SQL Query Analyzer；

2. MySQL 的SQL Query Analyzer；

3. Oracle 的SQL Performance Analyzer；

4. 微软的SQL Query Analyzer；

其中 MySQL 我们还可以使用 explain 工具来查看 SQL 语句的执行计划（Execution Plan）。

2/ 性能优化

对于 SQL 的性能优化，主要通过以下几个维度来讨论：

2.1/ 全表检索

例1：SELECT * FROM user WHERE name = "xxx";

这样的 SQL 语句基本上是全表查找，线性复杂度为 O(n)，记录数越多，性能也就越差。对于这种情况，我们可以有以下两种方法来提示性能：

• 分表，将大记录数分割成几块小的记录数；

• 索引，针对name 字段建立索引，对于B-Tree索引的复杂度基本上是 O(logn)；

2.2/ 索引

针对已建立索引字段，在 WHERE 和 ORDER BY 子句中 ，尽量不要在字段上做计算、类型转换、函数、空值判断、字段连接等操作，因为这些操作将会破坏索引原本的性能。

2.3/ 多表查询

关系型数据库最多的操作就是多表查询，多表查询主要有三个关键字，EXISTS、IN 和 JOIN。基本来说，现在的数据引擎对 SQL 语句优化得都很好了，EXISTS、IN 和 JOIN 在结果上有些不同，但性能基本上都差不多。

对于 JOIN 有三种实现算法，

• 嵌套循环（MySQL 只支持这种）

• 排序归并，将两个表按照查询字段排序，再合并。

• Hash 的 JOIN，主要解决嵌套循环的 O(logn)的复杂度，使用一个临时的 Hash 表来标识；

2.4/ 部分结果集

MySQL 中的 LIMIT、Oracle 的 ROWNUM、SQL Server 里的 TOP 都是在限制前几条的返回结果。这样给数据库引擎很多可以调优的空间。

2.5/ 字符串

由于字符串在 SQL 中操作性能比较差，因此在创建数据结构时，我们的基本上原则是：能用数字表示的尽量使用数字来表示，如：时间、工号等。

2.6/ 全文检索

对于Text 类的文本字段查询，尽量不使用 LIKE 来做全文检索，如果需要全文检索功能，可以尝试使用第三方组件，如 Sphinx、ELK等。

2.7/ 其它

• 不用 SELECT *，明确指出各个字段，多表查询一定要在字段名前加上表名。

• 不用 HAVING，因为会遍历所有的记录，性能极差。

• 尽可能地使用 UNION ALL取代 UNION。

• 索引过多，INSERT 和 DELETE 会越慢，UPDATE 如果是多数索引，也会慢，但如果只是一个索引，则只会影响一个索引表。

还有一点重要的，数据库的各种操作都需要大量的内存，所以要保证服务器有足够的内存。特别是针对 多表查询的 SQL 语句，更是相当的耗内存。

3/ 总结

对 SQL 语句的性能优化，这里只是提供一个优化方向或约定。在日常使用过程还是根据具体使用场景和方式来决定，没有绝对的方式。