【mysql】sql优化原则

IT行业相对于一般传统行业，发展更新速度更快，一旦停止了学习，很快就会被行业所淘汰。所以我们需要踏踏实实的不断学习，精进自己的技术，尤其是初学者。今天golang学习网给大家整理了《【mysql】sql优化原则》，聊聊MySQL，我们一起来看看吧！

文章目录

• 法则一：只返回需要的结果
• 法则二：确保查询使用了正确的索引
• 法则三：尽量避免使用子查询
• 法则四：不要使用 OFFSET 实现分页
• 法则五：了解 SQL 子句的逻辑执行顺序
• 总结

SQL 作为关系型数据库的标准语言，是 IT 从业人员必不可少的技能之一。SQL 本身并不难学，编写查询语句也很容易，但是想要编写出能够高效运行的查询语句却有一定的难度。

查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行顺序、索引以及统计信息的采集等，甚至应用程序和系统的整体架构。本文介绍几个关键法则，可以帮助我们编写高效的 SQL 查询；尤其是对于初学者而言，这些法则至少可以避免我们写出性能很差的查询语句。

法则一：只返回需要的结果

一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行。通常来说，OLTP 系统每次只需要从大量数据中返回很少的几条记录；指定查询条件可以帮助我们通过索引返回结果，而不是全表扫描。绝大多数情况下使用索引时的性能更好，因为索引（B-树、B+树、B\*树）执行的是二进制搜索，具有对数时间复杂度，而不是线性时间复杂度。以下是 MySQL 聚簇索引的示意图：

举例来说，假设每个索引分支节点可以存储 100 个记录，100 万（1003）条记录只需要 3 层 B-树即可完成索引。通过索引查找数据时需要读取 3 次索引数据（每次磁盘 IO 读取整个分支节点），加上 1 次磁盘 IO 读取数据即可得到查询结果。

相反，如果采用全表扫描，需要执行的磁盘 IO 次数可能高出几个数量级。当数据量增加到 1 亿（1004）时，B-树索引只需要再增加 1 次索引 IO 即可；而全表扫描则需要再增加几个数量级的 IO。

同理，我们应该避免使用 SELECT \* FROM， 因为它表示查询表中的所有字段。这种写法通常导致数据库需要读取更多的数据，同时网络也需要传输更多的数据，从而导致性能的下降。

?关于B-树索引的原理以及利用索引优化各种查询条件、连接查询、排序和分组以及 DML 语句的介绍，可以参考：https://tonydong.blog.csdn.ne...

法则二：确保查询使用了正确的索引

如果缺少合适的索引，即使指定了查询条件也不会通过索引查找数据。因此，我们首先需要确保创建了相应的索引。一般来说，以下字段需要创建索引：

• 经常出现在 WHERE 条件中的字段建立索引可以避免全表扫描；
• 将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；
• 多表连接查询的关联字段建立索引，可以提高连接查询的性能；
• 将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。

即使创建了合适的索引，如果 SQL 语句写的有问题，数据库也不会使用索引。导致索引失效的常见问题包括：

• 在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效，这种情况还包括字段的数据类型不匹配，例如字符串和整数进行比较；
• 使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引。对于大型文本数据的模糊匹配，应该考虑数据库提供的全文检索功能，甚至专门的全文搜索引擎（Elasticsearch 等）；
• 如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL；不是所有数据库使用 IS \[NOT\] NULL 判断时都可以利用索引。

执行计划（execution plan，也叫查询计划或者解释计划）是数据库执行 SQL 语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想，我们首先应该查看它的执行计划，通过执行计划（EXPLAIN）确保查询使用了正确的索引。

?关于各种主流数据库中执行计划的查看和解释，可以参考：https://tonydong.blog.csdn.ne... https://blog.csdn.net/horses/article/details/106905110

法则三：尽量避免使用子查询

以 MySQL 为例，以下查询返回月薪大于部门平均月薪的员工信息：

 SELECT emp_id, emp_name  
   FROM employee e  
   WHERE salary > (  
     SELECT AVG(salary)  
       FROM employee  
       WHERE dept_id = e.dept_id);  
-> Filter: (e.salary > (select #2))  (cost=2.75 rows=25) (actual time=0.232..4.401 rows=6 loops=1)  
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.099..0.190 rows=25 loops=1)  
    -> Select #2 (subquery in condition; dependent)  
        -> Aggregate: avg(employee.salary)  (actual time=0.147..0.149 rows=1 loops=25)  
            -> Index lookup on employee using idx_emp_dept (dept_id=e.dept_id)  (cost=1.12 rows=5) (actual time=0.068..0.104 rows=7 loops=25)  

从执行计划可以看出，MySQL 中采用的是类似 Nested Loop Join 实现方式；子查询循环了 25 次，而实际上可以通过一次扫描计算并缓存每个部门的平均月薪。以下语句将该子查询替换为等价的 JOIN 语句，实现了子查询的展开（Subquery Unnest）：

 SELECT e.emp_id, e.emp_name  
   FROM employee e  
   JOIN (SELECT dept_id, AVG(salary) AS dept_average  
           FROM employee  
          GROUP BY dept_id) t  
     ON e.dept_id = t.dept_id  
  WHERE e.salary > t.dept_average;  
-> Nested loop inner join  (actual time=0.722..2.354 rows=6 loops=1)  
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.096..0.205 rows=25 loops=1)  
    -> Filter: (e.salary > t.dept_average)  (actual time=0.068..0.076 rows=0 loops=25)  
        -> Index lookup on t using https://tonydong.blog.csdn.ne...
法则五：了解 SQL 子句的逻辑执行顺序
以下是 SQL 中各个子句的语法顺序，前面括号内的数字代表了它们的逻辑执行顺序：
(6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias  
(1)  FROM t1 JOIN t2  
(2)    ON (join_conditions)  
(3) WHERE where_conditions  
(4) GROUP BY col1, col2  
(5)HAVING having_condition  
(7) UNION [ALL]  
   ...  
(8) ORDER BY col1 ASC,col2 DESC  
(9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;  
也就是说，SQL 并不是按照编写顺序先执行 SELECT，然后再执行 FROM 子句。从逻辑上讲，SQL 语句的执行顺序如下：
首先，FROM 和 JOIN 是 SQL 语句执行的第一步。它们的逻辑结果是一个笛卡尔积，决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序，实际上数据库在获取表中的数据之前会使用 ON 和 WHERE 过滤条件进行优化访问；
其次，应用 ON 条件对上一步的结果进行过滤并生成新的数据集；
然后，执行 WHERE 子句对上一步的数据集再次进行过滤。WHERE 和 ON 大多数情况下的效果相同，但是外连接查询有所区别，我们将会在下文给出示例；
接着，基于 GROUP BY 子句指定的表达式进行分组；同时，对于每个分组计算聚合函数 agg\_func 的结果。经过 GROUP BY 处理之后，数据集的结构就发生了变化，只保留了分组字段和聚合函数的结果；
如果存在 GROUP BY 子句，可以利用 HAVING 针对分组后的结果进一步进行过滤，通常是针对聚合函数的结果进行过滤；
接下来，SELECT 可以指定要返回的列；如果指定了 DISTINCT 关键字，需要对结果集进行去重操作。另外还会为指定了 AS 的字段生成别名；
如果还有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 语句，执行该查询并且合并两个结果集。对于集合操作中的多个 SELECT 语句，数据库通常可以支持并发执行；
然后，应用 ORDER BY 子句对结果进行排序。如果存在 GROUP BY 子句或者 DISTINCT 关键字，只能使用分组字段和聚合函数进行排序；否则，可以使用 FROM 和 JOIN 表中的任何字段排序；
最后，OFFSET 和 FETCH（LIMIT、TOP）限定了最终返回的行数。
了解 SQL 逻辑执行顺序可以帮助我们进行 SQL 优化。例如 WHERE 子句在 HAVING 子句之前执行，因此我们应该尽量使用 WHERE 进行数据过滤，避免无谓的操作；除非业务需要针对聚合函数的结果进行过滤。
除此之外，理解 SQL 的逻辑执行顺序还可以帮助我们避免一些常见的错误，例如以下语句：
-- 错误示例  
SELECT emp_name AS empname  
  FROM employee  
 WHERE empname ='张飞';  
该语句的错误在于 WHERE 条件中引用了列别名；从上面的逻辑顺序可以看出，执行 WHERE 条件时还没有执行 SELECT 子句，也就没有生成字段的别名。
另外一个需要注意的操作就是 GROUP BY，例如：
-- GROUP BY 错误示例  
SELECT dept_id, emp_name, AVG(salary)  
  FROM employee  
 GROUP BY dept_id;  
由于经过 GROUP BY 处理之后结果集只保留了分组字段和聚合函数的结果，示例中的 emp_name 字段已经不存在；从业务逻辑上来说，按照部门分组统计之后再显示某个员工的姓名没有意义。如果需要同时显示员工信息和所在部门的汇总，可以使用窗口函数。扩展：SQL 语法速成手册
?如果使用了 GROUP BY 分组，之后的 SELECT、ORDER BY 等只能引用分组字段或者聚合函数；否则，可以引用 FROM 和 JOIN 表中的任何字段。
还有一些逻辑问题可能不会直接导致查询出错，但是会返回不正确的结果；例如外连接查询中的 ON 和 WHERE 条件。以下是一个左外连接查询的示例：
SELECT e.emp_name, d.dept_name  
  FROM employee e  
  LEFT JOIN department d ON (e.dept_id = d.dept_id)  
 WHERE e.emp_name ='张飞';  
emp_name|dept_name|  
--------|---------|  
张飞     |行政管理部|  
  
SELECT e.emp_name, d.dept_name  
  FROM employee e  
  LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='张飞');  
emp_name|dept_name|  
--------|---------|  
刘备     |   [NULL]|  
关羽     |   [NULL]|  
张飞     |行政管理部|  
诸葛亮   |   [NULL]|  
...  
第一个查询在 ON 子句中指定了连接的条件，同时通过 WHERE 子句找出了“张飞”的信息。
第二个查询将所有的过滤条件都放在 ON 子句中，结果返回了所有的员工信息。这是因为左外连接会返回左表中的全部数据，即使 ON 子句中指定了员工姓名也不会生效；而 WHERE 条件在逻辑上是对连接操作之后的结果进行过滤。
总结
SQL 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。
好了，本文到此结束，带大家了解了《【mysql】sql优化原则》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！