- 浏览: 287836 次
- 性别:
- 来自: 无锡
文章分类
- 全部博客 (100)
- swing (6)
- web (13)
- Eclipse (5)
- plug-in (0)
- mysql (3)
- java综合 (13)
- 反编译 (2)
- oracle (16)
- uml (1)
- 编码相关 (2)
- tomcat (2)
- gis (2)
- windows (8)
- ssh (1)
- android (9)
- LBS (1)
- 笔记 (1)
- struts2 (1)
- http (1)
- 安全 (5)
- vps (1)
- linux (3)
- dwr (1)
- jni (1)
- js (2)
- 支付宝 (1)
- 基础与原理 (4)
- maven (3)
- sso (1)
- 数字证书 (2)
- keytool (1)
最新评论
-
wgyyouge:
有个命令行下的高效迁移工具ora2mysqlhttp://ww ...
强大简单的mysql迁移到oracle的工具 -
qqwe8554677:
...
java汉字转拼音,取汉字首字母,支持繁体 -
相约的旋律:
最后一个结论有疑问。我们在生产服务器上面一开始是使用 in 查 ...
SQL in 和 exists区别(转)(数据量大,效率区别特别明显) -
Seavision:
怎么输出大写?
java汉字转拼音,取汉字首字母,支持繁体 -
诗飘秋舞的活着:
输入 长沙的时候 输出的是 zhangsha 和zs
java汉字转拼音,取汉字首字母,支持繁体
一、关于索引的知识
要写出运行效率高的sql,需要对索引的机制有一定了解,下面对索引的基本知识做一介绍。
1、索引的优点和局限
索引可以提高查询的效率,但会降低dml操作的效率。
所以建立索引时需要权衡。对于dml操作比较频繁的表,索引的个数不宜太多。
2、什么样的列需要建索引?
经常用于查询、排序和分组的列(即经常在where、order或group by子句中出现的列)。
3、主键索引和复合索引
对于一张表的主键,系统会自动为其建立索引。
如果一张表的几列经常同时作为查询条件,可为其建立复合索引。
4、建立索引的语句
create index i_staff on staff (empno);
create index i_agent on agent (empno, start_date);
5、删除索引的语句
drop index I_staff;
drop index I_agent;
6、查询索引的语句
法一:利用数据字典
表一:all_indexes 查看一张表有哪些索引以及索引状态是否有效
主要字段: index_name, table_name, status
例如:select index_name, status
from all_indexes
where table_name=’STAFF_INFO’;
INDEX_NAME STATUS
--------------------- -----------
I_STAFF VALID
表二:all_ind_columns 查看一张表在哪些字段上建了索引
主要字段: table_name, index_name, column_name, column_position
例如: select index_name, column_name, column_position
from all_ind_columns
where table_name=’AGENT’
INDEX_NAME COLUMN_NAME COLUMN_POSITON
--------------------- ----------------------- --------------------------
I_AGENT EMPNO 1
I_AGENT START_DATE 2
由此可见,agent表中有一个复合索引(empno, start_date )
法二:利用toad工具
toad用户界面比sql*plus友好,并且功能强大。你可以在toad编辑器中键入表名,按F4,便可见到这张表的表结构以及所有索引列等基本信息。
7、索引的一些特点
1): 不同值较多的列上可建立检索,不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就没必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
2): 如果在索引列上加表达式,则索引不能正常使用
例如:b1,c1分别是表b,c的索引列
select * from b where b1/30< 1000 ;
select * from c where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
以上都是不正确的写法
3): where子句中如果使用in、or、like、!=,均会导致索引不能正常使用
例如:select * from b where b1=30 or b1=40;
4):使用复合索引进行查询时必须使用前置列
例如表a上有一个复合索引(c1,c2,c3),则c1为其前置列
如果用c1或c1+c2或c1+c2+c3为条件进行查询,则该复合索引可以发挥作用,反之,用c2或c3或c2+c3进行查询,则该索引不能起作用。
二. 书写sql注意事项:
1、避免给sql语句中引用的索引列添加表达式:
典型实例:
b1,c1分别是表b,c的索引列:
1)select * from b where b1/30< 1000 ;
2)select * from c where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
替代方案:
1) select * from b where b1 < 30000;
2) select * from c where c1 = to_date(‘20020301 14:01:01’, ‘YYYYMMDD HH24:MI:SS’);
注:在lbs中有两个重要字段,pol_info中的undwrt_date和prem_info中的payment_date,这两个日期是带时分秒的,所以经常有同事用to_char 来查询某一时间段的数据。
例如:select count(*) from pol_info where to_char(undwrt_date,’YYYYMMDD’)=’20020416’;
select count(*) from prem_info where to_char(undwrt_date,’YYYYMM’)=’200203’;
替代方案:
select count(*) from pol_info
where undwrt_date>=to_date(’20020416’,’YYYYMMDD’) and
undwrt_date<to_date(’20020417’,’YYYYMMDD’);
select count(*) from prem_info
where payment_date>=to_date(’20020301’,’YYYYMMDD’) and
payment_date<to_date(’20020401’,’YYYYMMDD’);
2、避免在where子句中使用in、or、like、!=
典型实例:
a1是a表上的索引列:
1) select * from a
where ( a1 = ‘0’ and ...) or (a1 = ‘1’ and ...);
2) select count(*) from a where a1 in (‘0’,’1’) ;
替代方案:
1)select * from a where a1 = ‘0’ and ...
union
select * from a where a1 = ‘1’ and ...
2) select count(*) from a where a1 = ‘0’;
select count(*) from a where a1 = ‘1’;
然后做一次加法运算;或者直接用存储过程来实现;
小结:
对字段使用了 ‘in,or,like’ 做条件、对字段使用了不等号 ‘!=’,均会使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引,或者使用union连结符代替。另一种方式是使用存储过程,它使SQL变得更加灵活和高效。
3、建立适当的索引
曾经接过开发的一个统计sql, select … from tablea where cola=… and …
运行效率非常慢,经查tablea数据量巨大,再查all_ind_columns,发现cola是tablea的一个复合索引中的一列,但不是前置列。象这种情况,就需要与开发商量,是否针对cola建一个索引。
4、like和substr
对于‘like’和‘substr’,其效率并没有多大分别。但是,当所搜索的值不存在时,使用‘like’的速度明显大于‘substr’。
所以:select * from a where substr(a1,1,4) = '5378' 可以用like替代
select * from a where a1 like ‘5378%’;
5、写where条件时,有索引字段的判断在前,其它字段的判断在后;如果where条件中用到复合索引,按照索引列在复合索引中出现的顺序来依次写where条件;
6、使用多表连接时,在from子句中,将记录数少的表放在后面,可提高执行效率;
7、避免使用not in
not in 是效率极低的写法,尽量使用minus或外连接加以替代
典型实例:
1) select col1 from tab1 where col1 not in (select col1 from tab2);
2) select sum(col2) from tab1 where col1 not in (select col1 from tab2);
替代方案
select col1 from tab1 minus select col1 from tab2;
select sum(a.col2) from tab1 a, tab2 b
where a.col1=b.col2(+) and b.col1 is null;
8、表查询时,如果其中一个表的记录数量明显大于其他表,则可以先对此表进行查询后,再与其他小表进行表连接。
典型实例:
select a.plan_code, b.dno, c,tno, sum(a.tot_modal_prem),
from prem_info a, dept_ref b, plan_type c
where substr(a.deptno,1,7) = substr(b.deptno,1,7)
and a.plan_code = c.plan_code
group by b.dno, c.tno, a.plan_code;
替代方案:
select b.dno, c.tno, a.plan_code, a.tot_amount
from (select plan_code, deptno, sum(tot_modal_prem) tot_amount
from prem_info
group by deptno, plan_code) a
dept_ref b,
plan_type c
where substr(a.deptno,1,7) = substr(b.deptno,1,7)
and a.plan_code = c.plan_code
group by b.dno, c.tno, a.plan_code;
小结:
由于prem_info表的记录数远远大于dept_ref表和plan_type表中的记录数,所以首先从prem_info表中查询需要的记录,此时记录数已经被大量缩小,然后再和其他两个表连接,速度会得到很大改善!
9、查询数量较大时,使用表连接代替IN,EXISTS,NOT IN,NOT EXISTS等。
典型实例:
a、使用IN:
select sum(col2) from tab1 where col1 in (select col1 from tab2);
使用EXISTS::
select sum(col2) from tab1 a
where exists ( select * from tab2 where col1=a.col1);
b、使用NOT IN:
select sum(col2) from tab1 where col1 not in (select col1 from tab2);
使用NOT EXISTS:
select sum(col2) from tab1 a
where not exists ( select * from tab2 where col1=a.col1);
替代方案:
a、使用连接:
select sum(a.col2) from tab1 a,tab2 b where a.col1=b.col2;
b、使用外连接:
select sum(a.col2) from tab1 a,tab2 b
where a.col1=b.col2(+) and b.col1 is null;
补充:
UNION和UNION ALL的区别
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录;而UNION ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。但是UNION ALL比UNION效率高,执行快。
发表评论
-
大数据量提高性能变通方法
2012-05-11 10:43 1082大数据量需要变通 方法之一如下: 可以使用 row_n ... -
SQL in 和 exists区别(转)(数据量大,效率区别特别明显)
2012-02-03 15:35 8184来源: 薛波的日志 in 和 exist ... -
sqlplus登录
2011-09-30 16:42 13661.直接敲sqlplus并回车就是启动SQL*PLUS,输入u ... -
字符转换CLOB插入数据库
2011-09-19 09:26 1171public static CLOB oracleStr2Cl ... -
oracle instr ,case when
2011-04-13 16:31 1347今天新学了一个函数instr(str1,str2,m,n); ... -
Oracle的转义字符需要用ESCAPE函数来定义
2011-03-28 11:21 4378SQL> create table t_char(a ... -
java调用存储过程返回的结果集(游标)
2011-03-25 11:46 2271新建存储过程 create or replace pr ... -
Merge Sql
2011-03-20 16:47 3630转自:http://dikar.iteye.com/blo ... -
头一回写这么麻烦的oracle查询语句,请高手轻拍
2011-03-19 19:32 1193今天同事遇到一问题,写查询不知如何下手,因为查询某条详细信息时 ... -
临时表空间不足ora-01652
2011-03-01 15:08 1451非临时表空间: select file_id fro ... -
oracle实用语句及函数记录
2011-02-18 20:43 987查询插入:insert into table_A(col1,c ... -
Oracle因主机名或IP变动,导致EM无法启动的问题。
2011-02-14 16:17 2059错误信息: WIN的事件查看器: An erro ... -
Oracle建表步骤
2011-02-12 15:06 1095create tablespace x datafile 'x ... -
ORACLE分页查询
2011-01-24 17:08 1103select * from (select id,rown ... -
强大简单的mysql迁移到oracle的工具
2011-01-13 10:25 20927http://www.5stardatabasesoftwar ...
相关推荐
在SQL Server数据库中,索引是提升数据处理效率的关键手段,尤其对于联机事务处理(OLTP)系统,快速的数据查询速度是系统性能的核心指标。本文主要探讨如何通过合理使用索引来优化SQL Server的性能。 首先,我们来...
- **提高查询效率**:索引可以显著加快数据检索的速度,特别是对于大型数据库。 - **支持唯一性约束**:唯一索引可以确保某一列或多列的组合不包含重复的值。 #### 局限性 - **增加写入操作开销**:当执行插入、...
数据库索引是数据库性能优化的重要手段之一。创建索引可以提高查询速度,降低数据库的负载,提高数据的安全性。本文将详细介绍数据库创建索引的原则、分类、创建方法、管理和优化等方面的知识点。 索引的概念和优点...
1. 唯一性:索引的唯一性有助于提高查询效率,避免冲突。 2. 组合索引:为多个列创建复合索引,适用于多条件查询。 3. 考虑索引宽度:索引列的长度会影响索引大小,过宽的索引可能会占用过多空间且降低性能。 4. ...
【SQL Server 2000 索引详解】 在SQL Server 2000中,索引是一种关键的数据...通过选择合适的索引类型、索引列和配置参数,可以大幅提高查询效率,同时需要注意避免过度索引,以免影响数据的插入、更新和删除速度。
在SQL Server 2000中,全文索引是一项强大的功能,它允许用户进行复杂的文本搜索,大大提高了从大量文本数据中检索信息的效率。全文索引与传统的基于关键字的索引不同,它能理解词汇的语法和语义,从而提供更精确、...
SQLServer中的索引碎片是影响数据库性能的重要因素,它分为外部碎片和内部碎片两种类型。外部碎片发生在索引页不在其逻辑顺序上时,导致查询过程中需要额外的页切换,尤其是对于返回有序结果集的查询,这将显著降低...
在设计数据库时,应权衡索引效率和存储成本,以找到最适合应用程序需求的平衡点。同时,了解并熟练掌握`CONTAINS`和`FREETEXT`的使用,可以更好地优化查询性能,提升用户体验。对于深入学习SQL Server全文索引,推荐...
当面对复杂的查询或批量更新操作时,合理利用索引提示(Index Hint)成为提升SQL执行效率的关键策略之一。本文通过一个具体的案例,深入探讨了如何通过索引提示优化SQL执行计划,从而解决数据库性能瓶颈。 #### ...
SqlServer索引工作原理 在了解SqlServer索引工作原理之前,我们需要了解什么是索引。索引是一种特殊的数据库结构,它可以快速...通过了解索引的工作原理,我们可以更好地设计和优化数据库,提高数据查询的速度和效率。
### 提高SQL Server性能:通过DBCC DBREINDEX重建索引 #### 一、引言 在数据库管理系统中,SQL Server作为一款广泛使用的数据库产品,其性能优化是确保应用程序高效运行的关键因素之一。其中,索引是提升查询速度的...
根据提供的文件信息,本文将详细解析SQL Server 2000中重建索引与收缩数据库的相关知识点。 ### 一、重建索引 #### 1. 什么是索引 在SQL Server 2000中,索引是提高数据检索速度的重要工具。它可以看作是一种特殊...
总之,Sphinx作为SQL全文索引工具,通过高效的全文索引和搜索机制,提升了数据库的查询效率,为企业级应用提供了强大的搜索能力。同时,结合CoreSeek的中文支持,使其在处理中文数据时同样表现出色。
在SQL Server中,全文索引是一种特殊类型的索引,主要用于提高文本数据的检索效率,尤其是在执行复杂模糊查询时。全文索引支持复杂的自然语言查询,能够帮助用户快速定位到含有特定词语或短语的数据记录。 #### 二...
全文索引与传统的基于B-tree结构的索引不同,它采用了一种特殊的功能性索引,由SQL Server全文引擎服务创建和维护,以提高对长文本字段的搜索效率。 全文索引的关键特性在于它的语言敏感性。与LIKE语句不同,全文...
### SQL Server 索引基础知识知识点汇总 #### 一、记录数据的基本格式 - **数据页作为基础单位**:在 SQL Server 中,数据页是最基本的存储单位,无论是缓存中的数据还是磁盘上的物理存储,都以数据页的形式存在。...
2. 对于返回某范围内数据的操作,聚集索引可以快速定位起点和终点,提高效率。 3. 如果列值大部分相同,聚集索引的效果不佳,非聚集索引可能更适合。 4. 频繁更新的列不适合设置聚集索引,因为每次更新都会影响索引...
Oracle和SQL Server作为两种广泛应用的关系型数据库管理系统,都支持创建和管理索引以优化查询速度。本文将深入探讨这两个数据库系统中如何批量创建和删除索引,以及它们对系统运行效率的影响。 首先,让我们了解...