当前位置：文档之家 › 编写高效的SQL语句

编写高效的SQL语句

  • 格式：doc
  • 大小：354.00 KB
  • 文档页数：27

1.1 编写高效的SQL语句1.1.1 SQL语句的执行过程1.1.2 选用适合的ORACLE优化器[使用提示：与数据库管理员密切相关。

在缺省情况下，ORACLE采用CHOOSE优化器，由数据库管理员定期运行analyze命令进行表分析，保证数据库中的对象统计信息的准确性。

]ORACLE的优化器共有3种：a. RULE (基于规则)b. COST (基于成本)c. CHOOSE (选择性)可以通过对init.ora文件中OPTIMIZER_MODE参数的设置来设置缺省的优化器，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。

也可以在SQL句级或是会话(session)级对其进行覆盖。

为了使用基于成本的优化器(CBO, Cost-Based Optimizer) ，需要经常运行analyze 命令，以保证数据库中的对象统计信息(object statistics)的准确性。

如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。

如果table已经被analyze过, 优化器模式将自动成为CBO，反之，数据库将采用RULE形式的优化器。

1.1.3 访问Table的方式[使用提示：与开发人员密切相关，作为OLTP系统，应尽量减少表扫描。

]ORACLE 对于表的访问采用如下两种方式：a. 全表扫描全表扫描就是顺序地访问表中每条记录。

ORACLE采用一次读入多个数据块(database block)的方式进行全表扫描。

b. 通过ROWID访问表采用基于ROWID的访问方式会提高访问表的效率，ROWID包含了表中记录的物理位置信息。

ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。

通常索引提供了快速访问ROWID的方法，因此基于索引列的查询可以得到性能上的提高。

1.1.4 共享SQL语句[使用提示：与开发人员密切相关]为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。

这块内存位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中，可以被所有的数据库用户共享。

因此，当执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。

ORACLE的这个功能提高了SQL的执行性能并节省了内存的使用。

数据库管理员必须在init.ora中为这个区域设置合适的参数，这个内存区域越大，就可以保留更多的语句，被共享的可能性也越大。

也并不是越大越好，应遵守“够用就好”的原则。

当向ORACLE 提交一个SQL语句，ORACLE会首先在共享池中查找相同的语句。

需要说明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同（包括空格、换行等）。

共享的语句必须满足三个条件：A. 字符级的比较：当前被执行的语句和共享池中的语句必须完全相同.例如:SELECT * FROM DM_SWJG;和下列每一个都不同SELECT * from DM_SWJG;Select * From Dm_swjg;B. 两个语句所指的对象必须完全相同：例如：下列SQL语句能否在这两个用户之间共享C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)例如：如下a组的两个SQL语句是相同的(可以共享),而b组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)a.变量名相同select pin , name from people where pin = :blk1.pin;select pin , name from people where pin = :blk1.pin;b. 变量名不同select pin , name from people where pin = :blk1.ot_ind;select pin , name from people where pin = :blk1.ov_ind;1.1.5 选择最有效率的表名顺序(只在基于规则的优化器中有效)[使用提示：在进行多表连接查询时必须遵守本例，如果对于数据量情况不清楚，请向设计或者需求人员确认。

]ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表driving table)将被最先处理。

在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。

当ORACLE处理多个表时，会运用排序及合并的方式连接它们。

首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如：表TAB1 16,384 条记录表TAB2 1 条记录选择TAB2作为基础表(最好的方法)select count(*) from tab1,tab2 执行时间0.96秒选择TAB2作为基础表(不佳的方法)select count(*) from tab2,tab1 执行时间26.09秒如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。

例如:EMP表的EMP_NO和CA T_NO列分别在LOCATION表和CATEGORY表中出现，EMP表是LOCATION表和CATEGORY表的交叉表。

高效语句：SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.LOCN = L.LOCNAND E.CAT_NO = C.CAT_NOAND E.EMP_NO BETWEEN 1000 AND 2000低效语句：SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 20001.1.6 WHERE子句中的连接顺序[使用提示：在进行多表连接查询时必须遵守本例]ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

例如:1.1.7 SELECT子句中避免使用‘*’[使用提示：不允许使用SELECT *]在SELECT子句中列出所有的COLUMN时，使用*是一个方便的方法。

但这是一个非常低效的方法。

因为ORACLE在解析的过程中，会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，将耗费更多的时间。

1.1.8 减少访问数据库的次数[使用提示：目前项目中基本上采用方法3，对于方法1、2可视具体情况使用]当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等等. 由此可见, 减少访问数据库的次数, 就能实际上减少ORACLE的工作量.例如:以下有三种方法可以检索出雇员号等于0342或0291的职员.方法1 (最低效)SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO …,..,.. ;OPEN C1(291);FETCH C1 INTO …,..,.. ;CLOSE C1;END;方法3 (高效)SELECT A.EMP_NAME , A.SALARY , A.GRADE,B.EMP_NAME , B.SALARY , B.GRADEFROM EMP A,EMP BWHERE A.EMP_NO = 342OR B.EMP_NO = 291;1.1.9 删除重复记录[使用提示：程序中基本上用不到这个例子，在进行推广维护时可推荐使用本例。

] DELETE FROM EMP EWHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP XWHERE X.EMP_NO = E.EMP_NO);1.1.10 用TRUNCATE替代DELETE[使用提示：TRUNCATE仅适用于全表删除]当删除表中的记录时，在通常情况下,，回滚段(rollback segments ) 用来存放可以被恢复的信息。

如果执行了ROLLBACK操作，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 。

而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。

当命令运行后，数据不能被恢复。

因此很少的资源被调用，执行时间也会很短。

1.1.11 尽量可能的多使用COMMIT[使用提示：尽量少使用事务，能不用尽量不用]只要有可能，在程序中应尽量多使用COMMIT，这样程序的性能会得到提高，对于资源的需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费在COMMIT时必须要考虑事务的完整性，现实中效率和事务完整性往往不可兼得的。

禁止无任何意义的开启事务。

1.1.12 用Where子句替换HA VING子句[使用提示：HA VING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等。

除此而外，限制条件必须写在WHERE子句中。

]避免使用HA VING子句, HA VING 只会在检索出所有记录之后才对结果集进行过滤。

这个处理需要排序,总计等操作。

尽量通过WHERE子句来限制记录的数目,达到减少这方面开销的目的。

例如:1.1.13 减少对表的查询[使用提示：必须严格遵守本例]在含有子查询的SQL语句中,要特别注意减少对表的查询.1.1.14 通过内部（单行）函数提高SQL效率[使用提示：当程序出现效率低下的问题时可酌情使用，一般情况下不建议用，并且对于出现多表连接，超过3个表以上时，需要经过评审。

合集下载

数据库管理规范

数据库管理规范

数据库管理规范一、概述数据库管理规范是为了保证数据库系统的高效、安全、可靠运行而制定的一系列规范和标准。

本文将从数据库设计、权限管理、备份与恢复、性能优化等方面详细介绍数据库管理规范的要求和操作流程。

二、数据库设计规范1. 数据库命名规范- 数据库名应简洁明了，能够准确描述其所存储的数据内容。

- 数据库名使用小写字母，可以使用下划线作为分隔符。

- 避免使用数据库系统保留的关键字作为数据库名。

2. 表设计规范- 表名应具有描述性，能够清晰表达其所存储数据的含义。

- 表名使用小写字母，可以使用下划线作为分隔符。

- 表名避免使用数据库系统保留的关键字作为表名。

- 表中的字段名应具有描述性，能够清晰表达字段所存储数据的含义。

- 字段名使用小写字母，可以使用下划线作为分隔符。

- 字段名避免使用数据库系统保留的关键字作为字段名。

3. 数据类型选择- 根据数据的实际需求，选择合适的数据类型，避免浪费存储空间和性能。

- 避免使用过长的数据类型，例如使用INT代替BIGINT等。

4. 索引规范- 根据查询需求和数据量，合理选择索引类型和字段。

- 避免过多的索引，以免影响插入和更新操作的性能。

- 定期检查和优化索引，确保其有效性和性能。

三、权限管理规范1. 用户权限管理- 为每个用户分配最小权限原则，避免用户拥有不必要的权限。

- 定期审查和更新用户权限，确保权限的合理性和安全性。

- 禁止使用默认或弱密码，要求用户使用强密码并定期更换。

2. 角色权限管理- 根据用户的职责和权限需求，设定不同的角色。

- 角色应具有清晰的权限范围和权限等级，避免权限冲突和滥用。

- 定期审查和更新角色权限，确保权限的合理性和安全性。

3. 数据库审计- 启用数据库审计功能，记录用户的操作和访问日志。

- 定期审查和分析审计日志，发现异常操作和安全风险。

四、备份与恢复规范1. 定期备份- 根据业务需求和数据变更频率，制定合理的备份策略。

- 定期对数据库进行全量备份和增量备份，确保数据的完整性和可恢复性。

sql语句的编写思路

sql语句的编写思路

sql语句的编写思路编写SQL 语句时，可以遵循以下一般的思路和步骤：1. 确定目标：明确你要从数据库中获取或修改的数据是什么，或者你要执行的操作是什么（如查询、插入、更新、删除等）。

2. 选择适当的表：确定涉及到的数据库表，这些表包含了你需要操作的数据。

了解表结构和关系模式对于编写有效的SQL 语句非常重要。

3. 使用关键字选择操作类型：根据你的目标操作类型，选择适当的SQL 关键字（如SELECT、INSERT、UPDATE、DELETE）。

4. 编写基本的语法结构：根据选定的操作类型，编写基本的语法结构。

例如，SELECT 语句通常包括SELECT、FROM、WHERE、GROUP BY、HAVING 和ORDER BY 子句。

5. 使用条件筛选数据：如果你需要根据特定的条件来检索数据，使用WHERE 子句来添加筛选条件。

条件可以是简单的相等或不等关系，也可以是复杂的逻辑运算。

6. 定义需要返回的列：在SELECT 语句中，使用SELECT 子句来指定需要返回的列。

你可以选择具体的列名，也可以使用通配符* 来返回所有列。

7. 考虑使用聚合函数和分组：如果你需要对数据进行汇总或统计，可以使用聚合函数（如SUM、COUNT、AVG、MIN、MAX）和GROUP BY 子句来分组数据。

8. 考虑排序：如果你希望以特定的顺序返回数据，可以使用ORDER BY 子句来指定排序的列和排序顺序（升序或降序）。

9. 考虑连接多个表：如果你需要从多个表中检索数据，需要使用JOIN 来连接这些表，并在ON 子句中指定连接条件。

10. 考虑数据的插入、更新和删除：如果你的目标是插入、更新或删除数据，根据操作类型编写相应的INSERT、UPDATE 或DELETE 语句，并使用适当的条件进行筛选。

11. 格式化和优化：在编写SQL 语句时，注意格式化代码以提高可读性，并优化语句以提高性能。

这包括使用缩进、换行和注释，并避免不必要的重复或复杂的查询。

sqlsqerver语句优化方法

sqlsqerver语句优化方法

sqlsqerver语句优化方法SQL Server是一种关系型数据库管理系统，可以使用SQL语句对数据进行操作和管理。

优化SQL Server语句可以提高查询和操作数据的效率，使得系统更加高效稳定。

下面列举了10个优化SQL Server语句的方法：1. 使用索引：在查询频繁的列上创建索引，可以加快查询速度。

但是要注意不要过度索引，否则会影响插入和更新操作的性能。

2. 避免使用SELECT *：只选择需要的列，避免不必要的数据传输和处理，提高查询效率。

3. 使用JOIN替代子查询：在进行关联查询时，使用JOIN操作比子查询更高效。

尽量避免在WHERE子句中使用子查询。

4. 使用EXISTS替代IN：在查询中使用EXISTS操作比IN操作更高效。

因为EXISTS只需要找到一个匹配的行就停止了，而IN需要对所有的值进行匹配。

5. 使用UNION替代UNION ALL：如果对多个表进行合并查询时，如果不需要去重，则使用UNION ALL操作比UNION操作更高效。

6. 使用TRUNCATE TABLE替代DELETE：如果要删除表中的所有数据，使用TRUNCATE TABLE操作比DELETE操作更高效。

因为TRUNCATE TABLE不会像DELETE一样逐行删除，而是直接删除整个表的数据。

7. 使用分页查询：在需要分页显示查询结果时，使用OFFSET和FETCH NEXT操作代替传统的使用ROW_NUMBER进行分页查询。

这样可以减少查询的数据量，提高效率。

8. 避免使用CURSOR：使用游标(CURSOR)会增加数据库的负载，降低查询效率。

如果可能的话，应该尽量避免使用游标。

9. 使用参数化查询：使用参数化查询可以减少SQL注入的风险，同时也可以提高查询的效率。

因为参数化查询会对SQL语句进行预编译，可以复用执行计划。

10. 定期维护数据库：定期清理过期数据、重建索引、更新统计信息等维护操作可以提高数据库的性能。

数据库优化与性能调优的实用技巧

数据库优化与性能调优的实用技巧

数据库优化与性能调优的实用技巧数据库优化与性能调优是数据库管理中非常重要的一个环节，它可以提高数据库的性能并降低系统的负载。

本文将从索引优化、查询优化、并发控制、硬件优化以及其他实用技巧等方面进行详细阐述。

一、索引优化1. 选择合适的索引类型：根据数据的特点和查询需求，选择适合的索引类型。

常见的索引类型包括主键索引、唯一索引、普通索引等。

2. 添加合适的索引：根据数据库查询需求和数据访问模式，添加适当的索引以提高查询效率。

避免过多或重复索引对数据库性能造成负面影响。

3. 压缩索引空间：对于大表的索引，可以考虑使用索引压缩技术，降低索引空间占用，提高查询速度。

二、查询优化1. 编写高效的SQL语句：合理编写SQL语句，避免不必要的数据访问和计算，提高查询效率。

2. 优化查询计划：通过分析查询执行计划，进行优化调整，例如选择合适的连接方式、重新排列WHERE条件等。

3. 使用适当的数据库连接池：数据库连接池可以提高数据库的并发处理能力，减少连接的创建和关闭开销。

三、并发控制1. 事务管理：合理划分事务边界，控制事务的粒度，并避免长时间持有锁对数据库性能造成的影响。

2. 锁优化：选择合适的锁机制，如行级锁、表级锁，避免锁竞争过多，提高并发处理能力。

3. 利用乐观并发控制：在适当的场景下，可以使用乐观并发控制技术，减少锁冲突，提高并发性能。

四、硬件优化1. 硬盘性能优化：使用高性能硬盘、RAID技术等，提高磁盘I/O性能，加快数据读写速度。

2. 内存优化：增加数据库服务器的内存大小，提高缓存效果，减少磁盘I/O次数，提高数据库读取性能。

3. CPU优化：选择高性能的CPU，并进行合理的负载均衡，提高数据库的处理能力。

五、其他实用技巧1. 定期进行数据库统计分析：通过收集数据库的统计信息，分析查询频率和查询行为，优化数据库设计和索引。

2. 合理使用数据库分区：对于大型数据库，可以使用分区技术将数据划分为多个区域，提高查询效率和管理灵活性。