关于mysql:mysqlsql优化原则

文章目录

• 法令一：只返回须要的后果
• 法令二：确保查问应用了正确的索引
• 法令三：尽量避免应用子查问
• 法令四：不要应用 OFFSET 实现分页
• 法令五：理解 SQL 子句的逻辑执行程序
• 总结

SQL 作为关系型数据库的规范语言，是 IT 从业人员必不可少的技能之一。SQL 自身并不难学，编写查问语句也很容易，然而想要编写出可能高效运行的查问语句却有肯定的难度。

查问优化是一个简单的工程，波及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行程序、索引以及统计信息的采集等，甚至应用程序和零碎的整体架构。本文介绍几个要害法令，能够帮忙咱们编写高效的 SQL 查问；尤其是对于初学者而言，这些法令至多能够防止咱们写出性能很差的查问语句。

法令一：只返回须要的后果

肯定要为查问语句指定 WHERE 条件，过滤掉不须要的数据行。通常来说，OLTP 零碎每次只须要从大量数据中返回很少的几条记录；指定查问条件能够帮忙咱们通过索引返回后果，而不是全表扫描。绝大多数状况下应用索引时的性能更好，因为索引（B- 树、B+ 树、B\* 树）执行的是二进制搜寻，具备对数工夫复杂度，而不是线性工夫复杂度。以下是 MySQL 聚簇索引的示意图：

Clustered index

举例来说，假如每个索引分支节点能够存储 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 <auto_key0> (dept_id=e.dept_id)  (actual time=0.011..0.015 rows=1 loops=25)  
            -> Materialize  (actual time=0.048..0.057 rows=1 loops=25)  
                -> Group aggregate: avg(employee.salary)  (actual time=0.228..0.510 rows=5 loops=1)  
                    -> Index scan on employee using idx_emp_dept  (cost=2.75 rows=25) (actual time=0.181..0.348 rows=25 loops=1)  

改写之后的查问利用了物化（Materialization）技术，将子查问的后果生成一个内存长期表；而后与 employee 表进行连贯。通过理论执行工夫能够看出这种形式更快。

以上示例在 Oracle 和 SQL Server 中会主动执行子查问开展，两种写法成果雷同；在 PostgreSQL 中与 MySQL 相似，第一个语句应用 Nested Loop Join，改写为 JOIN 之后应用 Hash Join 实现，性能更好。

另外，对于 IN 和 EXISTS 子查问也能够得出相似的论断。因为不同数据库的优化器能力有所差别，咱们应该尽量避免应用子查问，思考应用 JOIN 进行重写。

搜寻公众号 Java 笔记虾，回复“后端面试”，送你一份面试题大全.pdf

法令四：不要应用 OFFSET 实现分页

分页查问的原理就是先跳过指定的行数，再返回 Top-N 记录。分页查问的示意图如下：

分页查问

数据库个别反对 FETCH/LIMIT 以及 OFFSET 实现 Top-N 排行榜和分页查问。当表中的数据量很大时，这种形式的分页查问可能会导致性能问题。以 MySQL 为例：
`

SELECT *  
  FROM large_table  
 ORDER BY id  
 LIMIT 10 OFFSET N;  

`
以上查问随着 OFFSET 的减少，速度会越来越慢；因为即便咱们只须要返回 10 条记录，数据库依然须要拜访并且过滤掉 N（比方 1000000）行记录，即便通过索引也会波及不必要的扫描操作。

对于以上分页查问，更好的办法是记住上一次获取到的最大 id，而后在下一次查问中作为条件传入：

SELECT *  
    FROM large_table  
     WHERE id > last_id  
     ORDER BY id  
     LIMIT 10;  

如果 id 字段上存在索引，这种分页查问的形式能够根本不受数据量的影响。

???? 对于 Top-N 排行榜和分页查问的具体介绍，能够参考：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 语句的执行程序如下：

1. 首先，FROM 和 JOIN 是 SQL 语句执行的第一步。它们的逻辑后果是一个笛卡尔积，决定了接下来要操作的数据集。留神逻辑执行程序并不代表物理执行程序，实际上数据库在获取表中的数据之前会应用 ON 和 WHERE 过滤条件进行优化拜访；
2. 其次，利用 ON 条件对上一步的后果进行过滤并生成新的数据集；
3. 而后，执行 WHERE 子句对上一步的数据集再次进行过滤。WHERE 和 ON 大多数状况下的成果雷同，然而外连贯查问有所区别，咱们将会在下文给出示例；
4. 接着，基于 GROUP BY 子句指定的表达式进行分组；同时，对于每个分组计算聚合函数 agg\_func 的后果。通过 GROUP BY 解决之后，数据集的构造就产生了变动，只保留了分组字段和聚合函数的后果；
5. 如果存在 GROUP BY 子句，能够利用 HAVING 针对分组后的后果进一步进行过滤，通常是针对聚合函数的后果进行过滤；
6. 接下来，SELECT 能够指定要返回的列；如果指定了 DISTINCT 关键字，须要对后果集进行去重操作。另外还会为指定了 AS 的字段生成别名；
7. 如果还有汇合操作符（UNION、INTERSECT、EXCEPT）和其余的 SELECT 语句，执行该查问并且合并两个后果集。对于汇合操作中的多个 SELECT 语句，数据库通常能够反对并发执行；
8. 而后，利用 ORDER BY 子句对后果进行排序。如果存在 GROUP BY 子句或者 DISTINCT 关键字，只能应用分组字段和聚合函数进行排序；否则，能够应用 FROM 和 JOIN 表中的任何字段排序；
9. 最初，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 优化实质上是理解优化器的的工作原理，并且为此创立适合的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。