sql优化工具_sql优化什么意思

数据库基础详解：存储过程、视图、游标、SQL语句优化以及索引

本文所做实验均基于PostgreSQL 9.3.5平台

写在文章前：本系列文章用于博主自己归纳复习一些基础知识，同时也分享给可能需要的人，因为水平有限，肯定存在诸多不足以及技术性错误，请大佬们及时指正。

sql优化工具_sql优化什么意思

sql优化工具_sql优化什么意思

如索引是建立在多个列上 只有在他的个列(leading column)被where子句引用时 优化器才会选择使用该索引 这也是一条简单而重要的规则 当仅引用索引的第二个列时 优化器使用了全表扫描而忽略了索引

存储过程 是事先经过编译并存储在数据库中的一段SQL语句的。想要实现相应的功能时，只需要调用这个存储过程就行了（类似于函数，输入具有输出参数）。

缺点 ：

Truncate删除表中的所有数据，这个作不能回滚，也不会（1）sql没有什么模式化的优化方法，如果仅仅要求sql优化，那么只能去查看执行，至于通用的，只能说少用模糊查询，比如like等，少用排序，分组，不过说实话这些不能增加多少速度。触发这个表上的触发器。作比DELETE快很多（直接把表drop掉，再创建一个新表，删除的数据不能找回）。如果表中有自增（AUTO_INCREMENT）列，则重置为1。

Drop命令从数据库中删除表，所有的数据行，索引和约束都会被删除。不能回滚，不会触发触发器。

可以跟基本表一样，进行增删改查作( 增删改作有条件限制，一般视图只允许查询作 )，对视图的增删改也会影响原表的数据。 它就像一个窗口，透过它可以看到数据库中自己感兴趣的数据并且作它们。 好处：

用于定位在查询返回的结果集的特定行，以对特定行进行作。使用游标可以方便地对结果集进行移动遍历，根据需要滚动或对浏览/修改任意行中的数据。主要用于交互式应用。它是一段私有的SQL工作区，也就是一段内存区域，用于暂时存放受SQL语句影响的数据，简单来说，就是将受影响的数据暂时放到了一个内存区域的虚表当中，这个虚表就是游标。

游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。即游标用来逐行读取结果集。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

简单来说：游标就是在查询出的结果集中进行选择性作的工具。

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余的查询。减少锁竞争。

索引是对数据库表中一列或多列的值进行排序的一种结构（说明是在列上建立的），使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

例如这样一个查询：select from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止。有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

从应用上分， 主键索引（聚集） ， 索引（聚集/非聚集） ， 普通索引 ， 组合索引 ， 单列索引和全文索引

一条sql执行过长的时间，你如何优化，从哪些方面？

（ ）SQL语句用大写的 因为Oracle总是先解析SQL语句 把小写的字母转换成大写的再执行

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中使用 != 或 <> 从数据库的基本表中通过查询选取出来的数据组成的虚拟表（数据库中只存放视图的定义，而不存放视图的数据）。可以对其进行增/删/改/查等作。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）。作符，否则时间对比：将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

5.in 和 not in 也要慎用，否则会导致全表扫描。

6.若要提高效率，可以考虑全文检索，尽量避免全表扫描。 7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问，变量的值还是未知的，因而无法作为索引选择的输入项。

8.应尽量避免在 where 子句中对字段进行表达式作，这将导致引擎放弃使用索引而进行全表扫描。

9.应尽量避免在where子句中对字段进行函数作，这将导致引擎放弃使用索引而进行全表扫描。

数据分析课程笔记 - 19 - HiveSQL 常用优化技巧

（ ）用UNION ALL 替换UNION ( 如有可能的话)

大家好呀，这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据，运行过程由于通常要经过 MapReduce 的过程，因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的，因此为了减少等待的时间，提高的运行效率，我们需要在 HiveSQL 的语句上进行一些优化。

索引是表的一个概念部分 用来提高检索数据的效率 Oracle使用了一个复杂的自平衡B tree结构 通常 通过索引查询数据比全表扫描要快 当 Oracle找出执行查询和Update语句的路径时 Oracle优化器将使用索引 同样在联结多个表时使用索引也能够提高效率 另一个使用索引的好处是 他提供了主键(primary key)的性验证 那些LONG或LONG RAW数据类型 您能够索引几乎任何的列 通常 在大型表中使用索引特别有效 当然 您也会发现 在扫描小表时 使用索引同样能提高效率 虽然使用索引能得到查询效率的提高 但是我们也必须注意到他的代价 索引需要空间来存储 也需要定期维护 每当有记录在表中增减或索引列被修改时 索引本身也会被修改 这意味着每条记录的INSERT DELETE UPDATE将为此多付出 次的磁盘I/O 因为索引需要额外的存储空间和处理 那些不必要的索引反而会使查询反应时间变慢 定期的重构索引是有必要的

本节课的主要内容 ：

引言

（1）列裁剪

（2）分区裁剪

3、技巧三：去重技巧——用group by来替换distinct

4、技巧四：聚合技巧——grouping sets、cube、rollup

（3）rollup

5、技巧五：换个思路解题

6、技巧六：union all时可以开启并发执行

8、技巧八：遵循严格模式

Hive 作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job 或 I/O 过多、MapReduce 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化，也包含 Hive 配置项和 MR 方面的调整。

列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hive.optimize.cp ，默认是 true 。

HiveSQL中的 order by 与其他 SQL 语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个 reduce 中，在数据量大时可能会长时间计算不完。

如果使用 sort by ，那么就会视情况启动多个 reducer 进行排序，并且保证每个 reducer 内局部有序。为了控制 map 端数据分配到 reduce 的 key，往往还要配合 distribute by 一同使用。如果不加 distribute by 的话，map 端数据就会随机分配给 reducer。

这里需要解释一下， distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。

这样做的原因是，通常用户的年龄分布是不均匀的，比如20岁以下和50岁以上的人非常少，中间几个年龄段的人又非常多，在 Map 阶段就会造成有些任务很大，有些任务很小。那通过 distribute by 一个均匀字段，就可以让系统均匀地进行“分桶”，对每个桶进行排序，再组合，这样就能从整体上提升 MapReduce 的效率。

取出 user_trade 表中全部支付用户：

原有写法的执行时长：

优化写法的执行时长：

考虑对之前的案例进行优化：

如果我们想知道用户的性别分布、城市分布、等级分布，你会怎么写？

通常写法：

缺点 ：要分别写三次SQL，需要执行三次，重复工作，且费时。

那该怎么优化呢？

GROUPING SETS() ：在 group by 查询中，根10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无确使用索引。据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。

如果我们想知道用户的性别分布以及每个性别的城市分布，你会怎么写？

那该怎么优化呢？

注意： 第二列为NULL的，就是性别的用户分布，其余有城市的均为每个性别的城市分布。

cube：根据 group by 维度的所有组合进行聚合

注意 ：跑完数据后，整理很关键！！！

rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。

如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎么办？

那应该如何优化呢？

有没有别的写法呢？

Hive 中互相没有依赖关系的 job 间是可以并行执行的，最典型的就是

多个子查询union all。在集群资源相对充足的情况下，可以开启并

行执行。参数设置： set hive.exec.parallel=true;

要开启严格模式，需要将参数 hive.mapred.mode 设为 strict 。

好啦，这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句，不断改进，就能掌握的写法。

优化SQL 查询：如何写出高性能SQL语句

3、理解索引，知道怎样的字段建立怎样的索引，索引能做什么，不能做什么，合理的建立字段。

4、合理的拆分和合并表，数据1、技巧一：列裁剪和分区裁剪放在一张表里面查询肯定比放在多张表里面级联查询要快。

5、会查看执行任务，任何数据库都有查看执行任务的方法，包括nosql数据库和newsql数据库已经一些大数据数据库；同时还要会分析执行任务，分析主要是所以的使用效率和字段数据的检索方式。

6、sql语句只是性能优化的简单方面，性能优化是从整体应用架构开始体现的，优化sql并不能够解决根本问题，当数据量达到一定级别以后，数据就不能使用关系型数据库，而要使用大数据数据库，这样sql就无用了。

7、不要刻初始化SqlCommand、SqlConnection，获取数据表至DataTable，查询多次，本地缓冲，效率高点。意专注sql本身，sql只是一种查询语言，它本身与性能无关，性能优化的本质在于对存储方式和查询检索过程的深入理解。

8、任何系统功能业务的准确性至上，首先保证功能的正确性再考虑性能优化，如果功能就是数据量大，业务复杂，必须要用到低性能sql的检索方式，那么你只能妥协，当表中有大量记录时，若要对表进行查询，种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O作。第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。否则就要弃用sql和关系型数据库另寻它路。

在SQL数据库中有1000w条数据，要对某条数据进行作，怎样能快速的找到这条数据，要求优化sQL语句

如我们要对一张很大的用户信息表按照年龄进行分组，优化前的写法是直接 order by age 。使用 distribute by 和 sort by 结合进行优化的时候， sort by 后面还是 age 这个排序字段， distribute by 后面选择一个没有重复值的均匀注意： 在极大的数据量（且很多重复值）时，可以先 group by 去重，再 count() 计数，效率高于直接 count(distinct ) 。字段，比如 user_id 。（2）个人建议，可以根据批量字段建立分区，这样能提高一些速度。

SQL优化（一） Merge Join VS. Hash Join VS. Nested Loop

（1）grouping sets

| 类别 | Nested Loop | Hash Join | Merge Join |

|---------------------------------------------|

| 使用条件 | 任何条件 | 等值连接（=） | 等值或非等值连注意 ：这个聚合结果相当于纵向地堆在一起了（Union all），分类字段用不同列来进行区分，也就是每一行数据都包含 4 列，前三列是分类字段，一列是聚合计算的结果。接(>，<，=，>=，<=)，‘<>’除外 |

| 相关资源 | CPU、磁盘I/O | 内存、临时空间 | 内存、临时空间 |

| 缺点 | 当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。| 为建立哈希表，需要大量内存。次的结果返回较慢。 | 所有的表都需要排序。它为化的吞吐量而设计，并且在结果没有全部找到前不返回数据。|

一张记录数1万以下的小表nbar.mse_test_test，一张大表165万条记录的大表nbar.nbar_test，大表上建有索引

如下图所示，执行器将小表mse_test_test作为外表（驱动表），对于其中的每条记录，通过大表（nbar_test）上的索引匹配相应记录。

如下图所示，执行器选择一张表将其映射成散列表，再遍历另外一张表并从散列表中匹配相应记录。

如下图所示，执行器先分别对mse_test_test和nbar_test按client_key排序。其中mse_test_test使用快速排序，而nbar_test使用external merge排序，之后对二者进行Merge Join。

通过对比 Query 1 Test 1 ， Query 1 Test 2 ， Query 1 Test 3 可以看出Nested Loop适用于结果集很小（一般要求小于一万条），并且内表在Join字段上建有索引（这点非常非常非常重要）。

如下图所示，执行器通过聚簇索引对大表（nbar_test）排序，直接通过对无索引的小表（mse_test_test）排序，之后对二才进行Merge Join。

如下图所示，执行器通过聚簇索引对大表（nbar_test）和小表（mse_2、理解sql语句检索数据的方式。test_test）排序，之后对二才进行Merge Join。

对比 Query 1 Test 4 和 Query 1 Test 5 ，可以看出二者的不同在于对小表（mse_test_test）的访问方式不同，前者使用，后者因为聚簇索引的存在而使用Index Only Scan，在表数据量比较小的情况下前者比后者效率更高。由此可看出如果通过索引排序再查找相应的记录比直接在原记录上排序效率还低，则直接在原记录上排序后Merge Join效率更高。

通过对比 Query 1 Test 2 ， Query 1 Test 6 可以看出Hash Join不要求表在Join字段上建立索引。

本次实验通过设置 enable_hashjoin=true ， enable_nestloop=false ， enable_mergejoin=false 来试图强制使用Hash Join，但是失败了。

阅读下一篇 SQL优化（二） 快速计算Distinct Count

sql查询太慢了如何进行优化？

1、深入理解数据库的工作原理和数据存储的方式，不同的数据库的工作原理是不同的，mysql oracle db2等等都是不同的，更不要说一些nosql数据库和newsql数据库了。

SQL查询的优化可能涉及多个方面。以下是一些常见的优化方法： 1.索引：这是提高查询性能的最常用策略。索引的使用可以大大减少数据库搜索记录的数量。

2.避免全表扫描：尽量避免在具有大量数据的表中进行全表扫描，特别是在关联查询时。 3.适当的字段类型：选择适当的字段类型可以让SQL作更高效。比如，如果一个字段不可能为负，则将其设置为无符号。

4.LIMIT语句：使用LIMIT语句来限制返回的行数，可以减小网络传输的数据量。 5.SQL语句优化：对SQL语句进行优化，例如避免在查询中使用NOTIN和作符，尽可能使用EXISTS而非IN，避免在WHERE子句中使用函数或计算。

6.数据规范化：数Delete用来删除表的全部或者部分数据，执行delete之后，用户需要提交之后才会执行，会触发表上的DELETE触发器（包含一个OLD的虚拟表，可以只读访问被删除的数据），DELETE之后表结构还在，删除很慢，一行一行地删，因为会记录日志，可以利用日志还原数据；据库的规范化可以消除数据冗余，进而优化存储空间和查询速度。 7.分割表：对于大型表，可以考虑进行垂直或水平分割，减小单个表的大小，改善查询性能。

8.使用缓存：对于频繁查询的结果，可以使用缓存技术，以减少数据库的压力。 9.使用Explain：大部分数据库都支持Explain命令，这个命令能够让你看到查询的执行，从而找出可能存在问题的部分。

10.保持统计信息更新：确保数据库的统计信息是的，这首先需要开启数据库管理工具，打开SQL语言编写窗体。如果我们知道某字段的开头部分，我们可以用like‘xx%selectTOP10fromCK_ATEwhereModellikePSM24W%。样可以帮助SQL优化器生成更有效的执行。 以上就是一些基本的SQL查询优化方法，具体应用时需要根据自己的实际情况和数据库类型进行选择和调整。

ORACLE优化SQL语句，提高效率(2)

（2）cube

ALTER INDEX REBUILD 2、技巧二：排序技巧——sort by代替order by

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时 避免在SELECT子句中使用DISTINCT 一般能够考虑用EXIST替换 EXISTS 使查询更为迅速 因为RDBMS核心模块将在子查询的条件一旦满足后 立即返回结果 例子

(低效): SELECT DISTINCT DEPT_NO DEPT_NAME FROM DEPT D EMP E WHERE D DEPT_NO = E DEPT_NO (高效): SELECT DEPT_NO DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E DEPT_NO = D DEPT_NO);

（ ）在Ja代码中尽量少用连接符 ＋ 连接字符串

（ ）避免在索引列上使用NOT通常 我们要避免在索引列上使用NOT NOT会产生在和在索引列上使用函数相同的影响 当Oracle 碰到 NOT 他就会停止使用索引转而执行全表扫描

（ ）避免在索引列上使用计算 WHERE子句中 如索引列是函数的一部分 优化器将不使用索引而使用全表扫描

举例:

低效 SELECT … FROM DEPT WHERE SAL > ; 高效: SELECT … FROM DEPT WHERE SAL > / ;

（ ）用>=替代>

高效 SELECT FROM EMP WHERE DEPTNO >= 低效: SELECT FROM EMP WHERE DEPTNO >

两者的区别在于 前者DBMS将直接跳到个DEPT等于 的记录而后者将首先定位到DEPTNO= 的记录并且向前扫描到个DEPT大于 的记录

（ ）用UNION替换OR (适用于索引列)

通常情况下 用UNION替换WHERE子句中的OR将会起到较好的效果 对索引列使用OR将造成全表扫描 注意 以上规则只针对多个索引列有效 如有column没有被索引 查询效率可能会因为您没有选择OR而降低 在下面的例子中 LOC_ID 和REGION上都建有索引

高效 SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID = UNION SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE REGION = MELBOURNE

低效: SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID = OR REGION = MELBOURNE

（ ）用IN来替换OR

这是一条简单易记的规则 但是实际的执行效果还须检验 在Oracle i下 两者的执行路径似乎是相同的:

SELECT… FROM LOCATION WHERE LOC_ID = OR LOC_ID = OR LOC_ID =

高效

（ ）避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何能够为空的列 Oracle将无法使用该索引 对于单列索引 如列包含空值 索引中将不存在此记录 对于复合索引 如每个列都为空 索引中同样不存在此记录 如至少有一个列不为空 则记录存在于索引中 举例 如性索引建立在表的A列和B列上 并且表中存在一条记录的 A B值为( null) Oracle将不接受下一条具备相同A B值（ null）的记录(插入) 然而如任何的索引列都为空 Oracle将认为整个键值为空而空不等于空 因此您能够插入 条具备相同键值的记录 当然他们都是空! 因为空值不存在于索引列中 所以WHERE子句中对索引列进行空值比较将使ORA约束（Constraint）类型：CLE停用该索引

低效: (索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效 (索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >= ;

（ ）总是使用索引的个列

当SQL语句需要UNION两个查询结果时 这两个结果会以UNION ALL的方式被合并 然后在输出最终结果前进行排序 如用 UNION ALL替代UNION 这样排序就不是必要了 效率就会因此得到提高 需要注意的是 UNION ALL 将重复输出两个结果中相同记录 因此各位还是要从业务需求分析使用UNION ALL的可行性 UNION 将对结果排序 这个作会使用到SORT_AREA_SIZE这块内存 对于这块内存的优化也是相当重要的 下面的SQL能够用来查询排序的消耗量

请帮忙Mysql 一条SQL语句优化

触发器（TRIGGER）是由（比如INSERT/UPDATE/DELETE）来触发运行的作（不能被直接调用，不能接收参数）。在数据库里以的对象存储，用于保证数据完整性（比如可以检验或转换数据）。

SELECT from gk_ where SORT in(0,1) AND state=1 AND ((unitid = '000400010001' and idml like '00010002%') or (tounitid = '000400010001' tounitid = '000400010001' and tomlid like '00010002%')) order by pdate desc limit 5

create index [indename] on [tablename] ([column1],[column2],[column3],……)

pdate 索引 sort这个有几个值,要的只取0,1是不是可以也加个索引改成so优点 ：rt < 2

2020-10-11：一条sql语句执行时间过长，应该如何优化？从哪些方面进行优化？

游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个改进数据库sql语句进行优化的理由 应用程序之优化通常可分为两个方面：源代码之优化和sql语句之优化。源代码之优化在时间成本和风险上代价很高；另一方面，源代码之优化对数据库系统性能之提升收效有限。 优化之理由 1)sql语句是对数据库(数据)进行作之惟一途径； 2)sql语句消耗了70%~90%之数据库资源； 3)sql语句于程序设计逻辑，相对于对程序源代码之优化，对sql语句之优化在时间成本和风险上之代价都很低； 4)sql语句可以有不同之写法； 5)sql语句易学，难精通。 优化技术之发展 代之sql优化工具是执行分析工具。这类之工具对输入之sql语句从数据库提取执行，并解释执行中关键字之含义；第二代之sql优化工具只能提供增加索引之建议，它通过对输入之sql语句之执行之分析来产生是否要增加索引之建议。该类工具存在着致命之缺点——只分析了一条sql语句就得出增加某个索引之结论，根本不理会(实际上也无法评估到)增加之索引对整体数据库系统性能之影响。其破坏性在于： 1、不理会增加之索引对其他增、删、改sql语句之负面影响； 2、没有考虑增加之索引可能导致数据库判断失误； 3、对由于增加索引引起之数据库系统负担忽略不计。 同时，这些工具由于技术水平之限制存在着以下缺点： 1、无法保证建议或改写之正确性； 2、无法进行重写，仅仅提供了建议或有限程度之改写，重写工作还是需要人工完成，优化工作所需之时间和工作量同人工进行优化不多； 3、改写之规则和hints有限，难以处理复杂之sql语句； 4、必须人手逐条进行测试。 这类工具曾经盛极一时，直到人工智能自动sql优化之出现。记录集，那么lishixinzhi/Article/program/Oracle/201311/16789创建一次游标而重复使用若干次，比重复查询数据库要快的多。通俗来说，游标就是能在sql的查询结果中，显示某一行（或某多行）数据，其查询的结果不是数据表，而是已经查询出来的结果集。