SQL 优化极简法则与优化方法.docx

《SQL 优化极简法则与优化方法.docx》由会员分享，可在线阅读，更多相关《SQL 优化极简法则与优化方法.docx（11页珍藏版）》请在优知文库上搜索。

1、目录法则一：只返回需要的结果法则二：确保查询使用了正确的索引法则三：尽量避免使用子查询法则四：不要使用OFFSET实现分页法则五：了解SQ1.子句的逻辑执行顺序总结方法SQ1.作为关系型数据库的标准语言，是IT从业人员必不可少的技能之一。SQ1.本身并不难学，编写查询语句也很容易，但是想要编写出能够高效运行的查询语句却有一定的难度。查询优化是一个复杂的工程,涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQ1.语句的执行顺序、索引以及统计信息的采集等，甚至应用程序和系统的整体架构。本文介绍几个关键法则，可以帮助我们编写高效的SQ1.查询；尤其是对于初学者而言，这些法则至少可以避免我们写

2、出性能很差的查询语句。以下法则适用于各种关系型数据库，包括但不限于:MySQ1.OracleSQ1.ServerPostgreSQ1.以及SQ1.ite等。法则一：只返回需要的结果一定要为查询语句指定WHERE条件，过滤掉不需要的数据行。通常来说，O1.TP系统每次只需要从大量数据中返回很少的几条记录；指定查询条件可以帮助我们通过索引返回结果，而不是全表扫描。绝大多数情况下使用索引时的性能更好，因为索引(B-树、B+树、B*树)执行的是二进制搜索，具有对数时间复万(1003)条记录只需要3层B-树即可完成索引。通过索引查找数据时需要读取3次索引数据(每次磁盘IO读取整个分支节点)，加上1次磁盘

3、IO读取数据即可得到查询结果。相反，如果采用全表扫描，需要执行的磁盘IO次数可能高出几个数量级。当数据量增加到1亿(1004)时，B-树索引只需要再增加1次索引IO即可；而全表扫描则需要再增加几个数量级的10。同理，我们应该避免使用SE1.ECT*FROM,因为它表示查询表中的所有字段。这种写法通常导致数据库需要读取更多的数据，同时网络也需要传输更多的数据，从而导致性能的下降。法则二：确保查询使用了正确的索引如果缺少合适的索引，即使指定了查询条件也不会通过索引查找数据。因此,我们首先需要确保创建了相应的索引。一般来说，以下字段需要创建索引：经常出现在WHERE条件中的字段建立索引可以避免全表扫

4、描；将ORDERBY排序的字段加入到索引中，可以避免额外的排序操作；多表连接查询的关联字段建立索引，可以提高连接查询的性能；将GRoUPBY分组操作字段加入到索引中，可以利用索引完成分组。即使创建了合适的索引，如果SQ1.语句写的有问题，数据库也不会使用索引。导致索引失效的常见问题包括：在WHERE子句中对索引字段进行表达式运算或者使用函数都会导致索引失效，这种情况还包括字段的数据类型不匹配，例如字符串和整数进行比较；使用1.IKE匹配时，如果通配符出现在左侧无法使用索引。对于大型文本数据的模糊匹配，应该考虑数据库提供的全文检索功能，甚至专门的全文搜索引擎(Elasticsearch等)；如果

5、WHERE条件中的字段上创建了索引，尽量设置为NOTNU1.1.；不是所有数据库使用1SNOTNU1.1.判断时都可以利用索引。执行计划(executionplan,也叫查询计划或者解释计划)是数据库执行SQ1.语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果SQ1.语句性能不够理想，我们首先应该查看它的执行计划，通过执行计划(EXP1.AlN)确保查询使用了正确的索引。法则三：尽量避免使用子查询以MySQ1.为例，以下查询返回月薪大于部门平均月薪的员工信息：ExplainanalyzeSE1.ECTemp_id,emp_nameFROMemplo

6、yeeeWHEREsalary(SE1.ECTAVG(salary)FROMemployeeWHEREdept_id=e.dept_id);Filter:(e.salary(select#2)(cost=2.75rows=25)(actualtime=0.232.4.401rows=6loops=1)Tablescanone(cost=2.75rows=25)(actualtime=0.099.0.190rows=251oops=1)Select#2(subqueryincondition;dependent)Aggregatezavg(employee.salary)(actualtime=

7、0.147.0.149rows=lloops=25)Indexlookuponemployeeusingidx_emp_dept(dept_id=e.dept_id)(cost=1.12rows=5)(actualtime=0.068.0.104rows=7loops=25)从执行计划可以看出，MySQ1.中采用的是类似Nested1.oopJoin实现方式；子查询循环了25次，而实际上可以通过一次扫描计算并缓存每个部门的平均月薪。以下语句将该子查询替换为等价的JOIN语句，实现了子查询的展开(SubqueryUnnest)：ExplainanalyzeSE1.ECTe.emp_id,e.em

8、p_nameFROMemployeeeJoIN(SE1.ECTdept_id,AVG(SaIary)ASdept_aVerageFROMemployeeGROUPBYdeptJd)tONe.dept_id=t.dept_idWHEREe.salaryt.dept-average;Nestedloopinnerjoin(actualtime=0.722.2.354rows=61oops=l)Tablescanone(cost=2.75rows=25)(actualtime=0.096.0.205rows=251oops=l)Filter:(e.salaryt.dept_average)(actu

9、altime=0.068.0.076rows=01oops=25)Indexlookupontusing(dept_id=e.dept_id)(actualtime=0.011.0.015rows=lloops=25)Materialize(actualtime=0.048.0.057rows=lloops=25)Groupaggregate:avg(employee.salary)(actualtime=0.228.0.510rows=51oops=l)Indexscanonemployeeusingidx_emp_dept(cost=2.75rows=25)(actualtime=0.18

10、1.0.348rows=251oops=1)改写之后的查询利用了物化(MaterialiZation)技术，将子查询的结果生成一个内存临时表；然后与employee表进行连接。通过实际执行时间可以看出这种方式更快。以上示例在Oraele和SQ1.SerVer中会自动执行子查询展开，两种写法效果相同；在PostgreSQ1.中与MySQ1.类似，第一个语句使用Nested1.oopJoin,改写为Jc)IN之后使用HaShJOin实现，性能更好。另外，对于IN和EXISTS子查询也可以得出类似的结论。由于不同数据库的优化器能力有所差异，我们应该尽量避免使用子查询，考虑使用JolN进行重写。法则四

11、：不要使用OFFSET实现分页分页查询的原理就是先跳过指定的行数，再返回TOP-N记录。分页查询的示意图如下：分页查询数据库一般支持FETCH/1.IMIT以及OFFSET实现Top-N排行榜和分页查询。当表中的数据量很大时，这种方式的分页查询可能会导致性能问题。以MySQ1.为例：-MySQ1.SE1.ECT*FROMlarge_tableORDERBYid1.imit100ffsetn;以上查询随着OFFSET的增加，速度会越来越慢；因为即使我们只需要返回10条记录，数据库仍然需要访问并且过滤掉N(比如1000000)行记录，即使通过索引也会涉及不必要的扫描操作。对于以上分页查询，更好的方

12、法是记住上一次获取到的最大id,然后在下一次查询中作为条件传入：-MySQ1.SE1.ECT*FROMlarge_tableWHEREidlastJdORDERBYid1.IMIT10;如果id字段上存在索引，这种分页查询的方式可以基本不受数据量的影响。法则五：了解SQ1.子句的逻辑执行顺序以下是SQ1.中各个子句的语法顺序，前面括号内的数字代表了它们的逻辑执行顺序：(6)SE1.ECTDISTlNCTA1.1.col1,co12,agg_func(col3)ASalias(l)FROMtlJOINt2(2)ON(join_conditions)(3)WHEREwhere_conditions

13、(4)GR0UPBYcoll,co!2(5)HAVINGhaving_condition(7)UNIONA1.1.(8)0RDERBYcollASC,col2DESC(9)0FFSETmRoWSFETCHNEXTnUm_rOWSROWSoN1.Y;也就是说，SQ1.并不是按照编写顺序先执行SE1.ECT,然后再执行FROM子句。从逻辑上讲，SQ1.语句的执行顺序如下：首先，FROM和JOIN是SQ1.语句执行的第一步。它们的逻辑结果是一个笛卡尔积，决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序，实际上数据库在获取表中的数据之前会使用ON和WHERE过滤条件进行优化访问；其次，

14、应用ON条件对上一步的结果进行过滤并生成新的数据集；然后，执行WHERE子句对上一步的数据集再次进行过滤。WHERE和ON大多数情况下的效果相同，但是外连接查询有所区别，我们将会在下文给出示例；接着，基于GROUPBY子句指定的表达式进行分组；同时，对于每个分组计算聚合函数agg_func的结果。经过GROUPBY处理之后，数据集的结构就发生了变化，只保留了分组字段和聚合函数的结果；如果存在GROUPBY子句，可以利用HAVING针对分组后的结果进一步进行过滤，通常是针对聚合函数的结果进行过滤；接下来，SE1.ECT可以指定要返回的列；如果指定了DISTINeT关键字，需要对结果集进行去重操作

15、。另外还会为指定了AS的字段生成别名；如果还有集合操作符(UNION、INTERSECTEXCEPT)和其他的SE1.ECT语句，执行该查询并且合并两个结果集。对于集合操作中的多个SE1.ECT语句，数据库通常可以支持并发执行；然后，应用ORDERBY子句对结果进行排序。如果存在GROUPBY子句或者DISTlNCT关键字，只能使用分组字段和聚合函数进行排序；否则，可以使用FROM和JOIN表中的任何字段排序；最后，OFFSET和FETCH(1.IMITTOP)限定了最终返回的行数。了解SQ1.逻辑执行顺序可以帮助我们进行SQ1.优化。例如WHERE子句在HAVING子句之前执行，因此我们应该尽量使用WHERE进行数据过滤，避免无谓的操作；除非业务需要针对聚合函数的结果进行过滤。除此之外，理解SQ1.的逻辑执行顺序还可以帮助我们避免一些常见的错误，例如以下语句：-错误示例SE1.ECTemp_nameASempnameFROMemployeeWHEREemPname=张飞；该语句的错误在于WHERE条件中引用了列别名；从上面的逻辑顺序可以看出，执行WHERE条件时还没有执行SE1.ECT子句，也就没有生成字段的别名。另外一个需要注意的操作就是GRoUPBY,例如：-GROUPBY错误示例SE1.ECTdeptJd