SQL查询优化

最新推荐文章于 2021-06-30 15:21:26 发布

空空dream

最新推荐文章于 2021-06-30 15:21:26 发布

阅读量195

点赞数

分类专栏：数据库

版权源自：dahouzi.cn

本文链接：https://blog.csdn.net/lglglgl/article/details/105359724

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这是大学时候一门课程的内容，悔恨当年没有好好学，为了工资现在重拾…

SQL查询优化

查询优化的本质（T查询耗时）

假设如下

T：查询花费时间
S：查询SQL需要访问的资源
V：硬件性能提供的单位时间内所能访问到的资源量

那么，T=S/V，我们能做的：

降低S，即减少SQL语句所需要访问的资源，说人话就是尽量查询更少的行
提高V，即增加硬件系统的吞吐量

分析工具

关系型数据库执行计划本质一样的，都是对SQL耗费的硬件资源和访问的数据量进行展示。

Oracle：sql*plus autotrace、dbms_xplan，explain plan
SQL Server：查询–显示估计的查询计划
MySQL：explain、desc

各执行计划参数详解

Oracle

六种执行计划，选择时一般遵循以下规则：

如果sql执行很长时间才出结果或返回不了结果，用方法1：explain plan for
跟踪某条sql最简单的方法是方法1：explain plan for，其次是方法2：set autotrace on
如果相关察某个sql多个执行计划的情况，只能用方法4：dbms_xplan.display_cursor或方法6：awrsqrpt.sql
如果sql中含有函数，函数中有含有sql，即存在多层调用，想准确分析只能用方法5：10046追踪
想法看到真实的执行计划，不能用方法1：explain plan for和方法2：set autotrace on
想要获取表被访问的次数，只能用方法3：statistics_level = all

MySQL

在这里插入图片描述

执行计划输出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra
id：关联表查询中，id越大优先级越高，越先被执行
select_type：查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
table：查询涉及的表或衍生表
partitions：查询涉及到的分区（MySQL的高级特性，参考https://www.cnblogs.com/w2154/p/4707562.html）原理未明
type：提供了判断查询是否高效的重要依据依据
possible_keys：指示MySQL可以从中选择查找此表中的行的索引
key：MySQL查询实际使用到的索引
key_len：表示索引中使用的字节数
ref：显示该表的索引字段关联了哪张表的哪个字段
rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
filtered：返回结果的行数占读取行数的百分比，值越大越好
extra：包含不适合在其他列中显示但十分重要的额外信息。

SQL Server

logical operation：基于微软查询处理概念模型的逻辑操作。例如，联接运算符的physical operation属性表示联接算法（nested loops，merge ，hash）物理运算符
logical operation属性表示逻辑联接类型（Inner join，outer join，semi join 等等）逻辑运算符
如果没有与该运算符关联的逻辑操作，则这项度量的值与physical operation相同
actual number of rows：从该运算符实际返回的行数（只显示在实际的计划中）
estimated I/O cost和estimated cpu cost：运算符在特定资源上的估计成本（I/O或CPU）这两个度量将帮助你确定运算符是否是I/O密集或CPU密集的
estimated operator cost：执行该操作的成本
estimated subtree cost：如前所述，他表示到当前节点为止整个子树的累积成本
estimated number of rows：该运算符预计的返回行数。在有些情况下，通过观察实际行数和估计行数之间的差异，你可以找出因统计信息不足或其他原因而导致的成本问题
estimated row size：你可能会奇怪为什么在实际的查询计划中没有显示该属性的实际值。因为你的表可能包含可变长度类型，表中行的大小各异
actual rebinds和actual rewinds：这两个度量仅与作为nested loops联接内侧的运算符有关，在其他运算符中，rebinds将显示为1，rewinds将显示为0
它们表示内部init方法被调用的次数。重新绑定次数和重绕次数之和等于联接外侧所处理的行数。重新绑定意味着联接的一个或多个参数发生更改后，必须重新计划

索引类型

Oracle索引

逻辑	物理	B-tree
Single column 单行索引	Partitioned 分区索引	Bitmap 位图索引
Concatenated 多行索引	NonPartitioned 非分区索引	Normal 正常型B树
Unique 唯一索引		Rever Key 反转型B树
NonUnique 非唯一索引
Function-based函数索引
Domain 域索引

SQL Server索引

聚集索引：直接决定了记录的存放位置，或者说：根据聚集索引可以直接获取到记录。
非聚集索引：保存了二个信息：1.相应索引字段的值，2.记录对应聚集索引的位置（如果表没有聚集索引则保存记录指针）。
seek：寻找；scan：扫描

Table Scan：遍历整个表，查找所有匹配的记录行。这个操作将会一行一行的检查，当然，效率也是最差的。
Index Scan：根据索引，从表中过滤出来一部分记录，再查找所有匹配的记录行，显然比第一种方式的查找范围要小，因此比Table Scan要快。
Index Seek：根据索引，定位（获取）记录的存放位置，然后取得记录，因此，比起前二种方式会更快。
Clustered Index Scan：和Table Scan一样。注意：不要以为这里有个Index，就认为不一样了。其实它的意思是说：按聚集索引来逐行扫描每一行记录，因为记录就是按聚集索引来顺序存放的。而Table Scan只是说：要扫描的表没有聚集索引而已，因此这二个操作本质上也是一样的。
Clustered Index Seek：直接根据聚集索引获取记录，最快！

MySQL

MySQL索引是在存储引擎中实现的，不同的储存引擎不同的索引，一般分为四类

单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引

索引类型
单列索引	主键索引	PRIMARY索引列唯一且不能为空；一张表只能有一个主键索引
单列索引	普通索引	NORMAL索引列没有任何限制
单列索引	唯一索引	UNIQUE索引列的值必须是唯一的，但允许有空
组合索引		组合索引就是把多个列加入到统一个索引中
全文索引		FULLTEXT 用于搜索很长一篇文章的时候，效果最好。

索引方式
HASH：
BTREE（Mysql默认）：
参考：
https://www.cnblogs.com/sunjingwu/p/10755823.html
https://www.cnblogs.com/hllnj2008/p/4788605.html
https://blog.csdn.net/weixin_38750084/article/details/85169195