![]() ![]() 2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。 系统存储过程sp_depends 这个存储过程专门用来查看一个数据库对象引用了哪些其它的数据库对象 比如要看一个存储过程用到了哪些表及字段,就可以写: exec sp_depends sp_procedure 如果是表名要加引号 exec sp_depends 'MyTable' 同样可以检查触发器 这个存储过程对于检查一个存储过程是很方便的,我们使用它就可以看到存储过程使用了哪些表及字段,而不用写存储过程的脚本,去一行一行的找 3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点: a) SQL的使用规范: i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。 ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。 iii. 尽量避免使用游标 iv. 注意where字句写法, 必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小 v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。 vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。 vii.尽量使用“>=”,不要使用“>”。 viii.注意一些or子句和union子句之间的替换 ix.注意表之间连接的数据类型,避免不同类型数据之间的连接。 x. 注意存储过程中参数和数据类型的关系。 xi.注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。 b) 索引的使用规范: i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。 ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引 iii.避免对大表查询时进行table scan,必要时考虑新建索引。 iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。 v. 要注意索引的维护,周期性重建索引,重新编译存储过程。 c)tempdb的使用规范: i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。 ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。 iii.在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。 iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。 v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。 vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。 d)合理的算法使用: 根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等。 -------------------------------------------------------------------------- -------------------------------------------------------------------------- 全文索引——CONTAINS 语法 我们通常在 WHERE 子句中使用 CONTAINS ,就象这样:SELECT * FROM table_name WHERE CONTAINS(fullText_column,'search contents')。 我们通过例子来学习,假设有表 students,其中的 address 是全文本检索的列。 1. 查询住址在北京的学生 SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'beijing' ) remark: beijing是一个单词,要用单引号括起来。 2. 查询住址在河北省的学生 SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province"' ) remark: HEBEI province是一个词组,在单引号里还要用双引号括起来。 3. 查询住址在河北省或北京的学生 SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province" OR beijing' ) remark: 可以指定逻辑操作符(包括 AND ,AND NOT,OR )。 4. 查询有 '南京路' 字样的地址 SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'nanjing NEAR road' ) remark: 上面的查询将返回包含 'nanjing road','nanjing east road','nanjing west road' 等字样的地址。 A NEAR B,就表示条件: A 靠近 B。 5. 查询以 '湖' 开头的地址 SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"hu*"' ) remark: 上面的查询将返回包含 'hubei','hunan' 等字样的地址。 记住是 *,不是 %。 6. 类似加权的查询 SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' ) remark: ISABOUT 是这种查询的关键字,weight 指定了一个介于 0~1之间的数,类似系数(我的理解)。表示不同条件有不同的侧重。 7. 单词的多态查询 SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' ) remark: 查询将返回包含 'street','streets'等字样的地址。 对于动词将返回它的不同的时态,如:dry,将返回 dry,dried,drying 等等。 --------------------------------------------------------- --------------------------------------------------------- 1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.数据库加密: select encrypt('原始密码') select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码') select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 3.取回表中字段: declare @list varchar(1000),@sql nvarchar(1000) select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A' set @sql='select '+right(@list,len(@list)-1)+' from 表A' exec (@sql) 4.查看硬盘分区: EXEC master..xp_fixeddrives 5.比较A,B表是否相等: if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B) print '相等' else print '不相等' 6.杀掉所有的事件探察器进程: DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses WHERE program_name IN('SQL profiler',N'SQL 事件探查器') EXEC sp_msforeach_worker '?' 7.记录搜索: 开头到N条记录 Select Top N * From 表 ------------------------------- N到M条记录(要有主索引ID) Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc ---------------------------------- N到结尾记录 Select Top N * From 表 Order by ID Desc 8.如何修改数据库的名称: sp_renamedb 'old_name', 'new_name' 9:获取当前数据库中的所有用户表 select Name from sysobjects where xtype='u' and status>=0 10:获取某一个表的所有字段 select name from syscolumns where id=object_id('表名') 11:查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%' 12:查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype='P' 13:查询用户创建的所有数据库 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 或者 select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 14:查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = '表名' [n].[标题]: Select * From TableName Order By CustomerName [n].[标题]: Select * From TableName Order By CustomerName -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- Sql优化是一项复杂的工作,以下的一些基本原则是本人看书时所记录下来的,很明确且没什么废话: 1. 索引的使用: (1).当插入的数据为数据表中的记录数量的10%以上,首先需要删除该表的索引来提高数据的插入效率,当数据插入后,再建立索引。 (2).避免在索引列上使用函数或计算,在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描。如: 低效:select * from dept where sal*12 >2500; 高效:select * from dept where sal>2500/12; (3).避免在索引列上使用not和 “!=”,索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到not 和 “!=”时,就会停止使用索引而去执行全表扫描。 (4).索引列上>=代替> 低效:select * from emp where deptno > 3 高效:select * from emp where deptno >=4 两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。 (5).非要对一个使用函数的列启用索引,基于函数的索引是一个较好的方案。 2. 游标的使用: 当在海量的数据表中进行数据的删除、更新、插入操作时,用游标处理的效率是最慢的,但是游标又是必不可少的,所以正确使用游标十分重要: (1). 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。 (2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录,例如: insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code) ods_customer为数据源表。dim_customer为维表。 (3). 使用显式的游标,因为隐式的游标将会执行两次操作,第一次检索记录,第二次检查too many rows这个exception,而显式游标不执行第二次操作。 3. 据抽取和上载时的sql优化: (1). Where 子句中的连接顺序: oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前,那些可以过滤掉大量记录的条件必须写在where子句的末尾。如: 低效:select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno); 高效:select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’; (2). 删除全表时,用truncate 替代 delete,同时注意truncate只能在删除全表时适用,因为truncate是ddl而不是dml。 (3). 尽量多使用commit 只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。 (4). 用exists替代in ,可以提高查询的效率。 (5). 用not exists 替代 not in (6). 优化group by 提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如: 低效:select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’; 高效: select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job; (7). 有条件的使用union-all 替代 union:这样做排序就不必要了,效率会提高3到5倍。 (8). 分离表和索引 ![]() |
数据库学习经典文章
最新推荐文章于 2024-09-05 10:00:22 发布