欢迎来到魔豆IT网-IT综合知识分析平台

不不不SQL优化极简主义

2028-01-02 20:38:29栏目 : 网络编程围观 : 57次

作为关系数据库的标准语言,SQL教程是IT从业者必备的技能之一。SQL本身不难学,写查询语句很容易,但是很难写出能高效运行的查询语句。

推荐(免费):SQL教程

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

以下规则适用于各种关系数据库,包括但不限于MySQL、Oracle、SQL Server、PostgreSQL和SQLite。如果你觉得文章有用,欢迎评论,赞,转发给朋友支持。

规则一:只返回想要的结果

请务必为查询语句指定WHERE条件,并过滤掉不必要的数据行。一般来说,OLTP系统一次只需要从大量数据中返回几条记录;指定查询条件可以帮助我们通过索引返回结果,而不是扫描整个表。在大多数情况下,使用索引时性能更好,因为索引(B树、B+树、B*树)执行二分搜索法,并且具有对数时间复杂度而不是线性时间复杂度。以下是MySQL聚簇索引示意图:例如,假设每个索引分支节点可以存储100条记录,100万(1003)条记录只能用3个B-树进行索引。按索引查找数据时,需要读取三次索引数据(每次磁盘IO读取整个分支节点),然后读取一次数据就可以得到查询结果。纯干货!15000字的语法手册与大家分享

相反,如果使用全表扫描,则需要执行的磁盘IO数量可能会高出几个数量级。当数据量增加到1亿(1004)时,B树索引只需要再增加一次索引IO。但是全表扫描需要增加几个数量级的IO。

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

规则2:确保查询使用正确的索引

如果没有合适的索引,即使指定了查询条件,索引也不会搜索数据。因此,我们首先需要确保创建相应的索引。一般来说,需要对以下字段进行索引:

在WHERE条件下经常出现的索引字段可以避免全表扫描;将按顺序排序的字段添加到索引中可以避免额外的排序操作;对多表连接查询的相关字段进行索引可以提高连接查询的性能;“分组依据”分组操作字段被添加到索引中,分组可以通过使用索引来完成。即使创建了适当的索引,如果SQL语句写得不正确,数据库也不会使用该索引。导致索引失效的常见问题包括:

在WHERE子句中,对索引字段执行表达式操作或使用函数将导致索引无效。这种情况还包括字段的数据类型不匹配,比如字符串和整数比较;使用LIKE匹配时,如果通配符出现在左侧,则不能使用索引。对于大文本数据的模糊匹配,要考虑数据库提供的全文检索功能,甚至是专门的全文搜索引擎(Elasticsearch等)。);如果在WHERE条件下对字段创建了索引,请尝试将其设置为非空;;当使用“是[否]空”判断时,并非所有数据库都可以使用索引。执行计划(也称为查询计划或解释计划)是数据库执行SQL语句的具体步骤,如通过索引或全表扫描访问表中的数据,实现连接查询和连接的顺序等。如果SQL语句的性能不尽如人意,首先要检查它的执行计划,通过解释来保证查询使用正确的索引。

规则3:尽量避免使用子查询

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

从员工工资>中解释分析选择员工标识、员工姓名;(从员工中选择AVG(工资),其中部门标识=部门标识);->;过滤:(e .薪水>等;(选择#2))(成本=2.75行=25)(实际时间=0.232..4.401行=6个循环= 1)-& gt;e上的表格扫描(成本=2.75行=25)(实际时间=0.099..0.190行=25个循环= 1)-& gt;选择#2(条件中的子查询;从属)->;合计:平均值(员工工资)(实际时间=0.147..0.149行=1个循环= 25)-& gt;使用idx _ EMP _ dept对员工进行索引查找(dept _ id = e. dept _ id)(成本= 1.12行= 5)(实际时间= 0.068..0.104行= 7个循环= 25)从执行计划可以看出,MySQL采用了类似的嵌套循环连接实现;子查询循环了25次,但实际上每个部门的月平均工资都可以通过一次扫描计算缓存。以下语句用等效的JOIN语句替换子查询,从而实现子查询Unnest:

解释分析从员工中选择部门标识,从员工中选择部门名称加入(选择部门标识,AVG(工资)作为部门平均从员工中按部门分组)t开e .部门标识= t .部门标识其中e .工资>;t.dept _ average->;嵌套循环内部连接(实际时间=0.722..2.354行=6个循环= 1)-& gt;e上的表格扫描(成本=2.75行=25)(实际时间=0.096..0.205行=25个循环= 1)-& gt;过滤:(e .薪水>等;t.dept_average)(实际时间=0.068..0.076行=0个循环= 25)-& gt;使用& ltauto_key0>。(dept_id=e.dept_id)(实际时间=0.011..0.015行=1个循环= 25)-& gt;物化(实际时间=0.048..0.057行=1个循环= 25)-& gt;组合计:平均值(员工工资)(实际时间=0.228..0.510行=5个循环= 1)-& gt;使用idx _ EMP _ dept对员工进行索引扫描(成本= 2.75行= 25)(实际时间= 0.181..0.348行= 25个循环= 1)重写的查询使用物化技术从子查询结果中生成临时内存表。然后连接员工表。从实际执行时间可以看出,这种方法比较快。

上面的例子会在Oracle和SQL Server中自动执行子查询扩展,两种编写方法效果相同;在PostgreSQL中,和MySQL类似,第一条语句使用嵌套循环连接(Nested Loop Join),重写为Join,然后用Hash Join实现,性能更好。

此外,对于IN和EXISTS子查询也可以得出类似的结论。因为不同数据库的优化器功能不同,所以我们应该尽可能避免使用子查询,并考虑用JOIN重写。搜索微信官方账号农民工的技术之路,回复“1024”,送你一大包技术资源。

规则4:不要用OFFSET来实现分页

分页查询的原理是在返回Top-N记录之前跳过指定的行数。分页查询的示意图如下:数据库一般支持FETCH/LIMIT和OFFSET,实现Top-N排名列表和分页查询。当表中的数据量非常大时,这种方式的分页查询可能会导致性能问题。以MySQL为例:

-MySQL select * FROM _ large _ table ORDER BY id LIMIT 10 OFFSET N;随着OFFSET的增加,查询速度会越来越慢;因为即使我们只需要返回10条记录,数据库仍然需要访问并过滤掉N(例如1,000,000)行记录,即使通过了索引,也会涉及到不必要的扫描操作。

对于上面的分页查询,一个更好的方法是记住上次获得的最大id,并在下一个查询中将它作为条件传递:

-MySQL select * FROM _ large _ table WHERE id >;last _ id ORDER BY id LIMIT 10如果id字段上有索引,这种分页查询方法基本上不受数据量的影响。

规则5:理解SQL子句的逻辑执行顺序

以下是SQL中每个子句的语法顺序,前面括号中的数字代表它们的逻辑执行顺序:

(6)选择[DISTINCT | ALL]列1,列2,agg_func(列3)作为别名(1)FROM t1 JOIN T2(2)ON(JOIN _ conditions)(3)WHERE _ conditions(4)GROUP BY col 1,列2(5)HAVING _ condition(7)UNION[ALL]...(8)按第1列ASC、第2列DESC排序(9)偏移m行仅获取下一个行数;也就是说,SQL并不是先执行SELECT,再按写顺序执行FROM子句。从逻辑上讲,SQL语句的执行顺序如下:

首先,FROM和JOIN是执行SQL语句的第一步。它们的逻辑结果是笛卡尔乘积,决定了接下来要操作的数据集。请注意,逻辑执行顺序不代表物理执行顺序。事实上,数据库在获取表中的数据之前,会使用ON和WHERE过滤条件来优化访问;其次,应用on条件过滤前一步的结果并生成新的数据集;然后,再次执行WHERE子句来过滤上一步中的数据集。WHERE和ON在大多数情况下有相同的效果,但是外部连接查询不同,下面我们举个例子;然后,根据GROUP BY子句中指定的表达式分组;同时,为每个组计算聚集函数agg_func的结果。GROUP BY处理后,数据集的结构发生了变化,只保留了分组字段和聚合函数的结果;如果有GROUP BY子句,HAVING可用于进一步过滤分组结果,通常是聚合函数的结果;接下来,SELECT可以指定要返回的列;如果指定了DISTINCT关键字,则需要对结果集进行重复数据消除。此外,将为指定为的字段生成别名;如果有集合运算符(UNION、INTERSECT、EXCEPT)和其他SELECT语句,则执行查询并合并两个结果集。对于集合操作中的多个SELECT语句,数据库通常可以支持并发执行;然后,应用ORDER BY子句对结果进行排序。如果有GROUP BY子句或DISTINCT关键字,则只能使用分组字段和聚合函数进行排序;否则,您可以使用FROM和JOIN表中的任何字段进行排序;最后,OFFSET和FETCH(LIMIT,TOP)限制最终返回的行数。了解SQL逻辑执行的顺序,可以帮助我们优化SQL。比如WHERE子句在HAVING子句之前执行,所以我们要尽量使用WHERE进行数据过滤,避免不必要的操作。除非业务需要过滤聚合函数的结果。

另外,了解SQL的逻辑执行顺序也可以帮助我们避免一些常见的错误,比如下面的语句:

-错误示例从empname = & # 39的员工中选择EMP _ name作为empname。张飞& # 39;;此语句的错误是在WHERE条件中引用了列别名;从上面的逻辑顺序可以看出,在执行WHERE条件的时候,没有执行SELECT子句,所以没有生成字段的别名。

另一个需要注意的操作是分组,例如:

-分组依据错误示例从员工分组依据部门标识中选择部门标识、员工姓名、平均工资;示例中的emp_name字段不再存在,因为结果集仅保留分组字段和聚合函数在GROUP BY处理后的结果。从逻辑上讲,按部门分组统计后,显示员工姓名是没有意义的。如果需要同时显示员工信息和部门汇总,可以使用窗口功能。扩展:SQL语法快速手册

有一些逻辑问题可能不会直接导致查询错误,但会返回不正确的结果;例如外部连接查询中的开和关条件。以下是左外部联接查询的示例:

SELECT e.emp_name,d . dept _ name FROM employee LEft JOIN department d ON(e . dept _ id = d . dept _ id)WHERE e . EMP _ name = & # 39;张飞& # 39;;Emp _ name | dept _ name | | |张飞|行政部|从员工e中选择e. EMP _ name,d. dept _ name左加入d部门on (e. dept _ id = d. dept _ id和张飞& # 39;);Emp _ name | dept _ name | | | | |刘备| [NULL]|关羽| [NULL]|张飞|行政部|诸葛亮| [NULL]|...第一个查询在on子句中指定连接条件,同时通过WHERE子句进行查找,在第二个查询中,所有的过滤条件都放在on子句中,并返回所有的员工信息。这是因为左外联接会返回左表中的所有数据,即使在ON子句中指定了员工姓名,也不会生效;WHERE条件在逻辑上是为了过滤连接操作后的结果。摘要

SQL优化本质上是了解优化器的工作原理,并为此目的创建合适的索引和正确的语句;同时,当优化器不够聪明时,手动使其聪明。

以上是SQL优化最小规则的详细内容,还有更多

展开剩余内容

分享到:

猜你喜欢

  • kgma是什么格式

    Kgma格式是酷狗不保护音乐版权的特殊格式。是特殊保护格式,只能在酷狗音乐播放器上播放,是MP3格式的加密包。因为音乐版权问题,音乐文件是加密的,暂时不支持格式转换,只能在酷狗...

    2028-01-02
  • 如果windows7启用了网络,但无法保存,该怎么办

    Windows7启用网络无法保存的解决方案:首先,计算机右键点击,然后选择管理、服务、应用和服务;然后分别启动相关服务,启动类型设置为自动;最后,打开高级共享设置,并设置为打开...

    2028-01-02
  • 蓝牙4.1和5.0有什么区别

    区别:蓝牙5.0的速度是蓝牙4.1的两倍;蓝牙5.0的有效距离可以达到300米,是蓝牙4.1的几倍。蓝牙5.0也比蓝牙4.1耗电少。蓝牙是一种支持设备间短距离通信(一般在10m...

    2028-01-02
  • csv是什么格式的文件

    Csv是一种通用且相对简单的逗号分隔值文件格式,是一种用于存储数据的纯文本文件。纯文本意味着CSV文件是一个字符序列,不包含必须像二进制数字一样解释的数据。逗号分隔值文件格式逗...

    2028-01-02
热门标签