发布新日志

  • Oracle学习笔记

    2010-06-07 10:47:48

    连接Oracle工具
    运行:sqlplusw、sqlplus、Oracle
    sqlplus worksheet 开发用的
    Enterprise Manager Console  图形操作

    ###########################################################远程连接
    pl/sql developer连接远程数据库
    开始-》所有程序-》Oracle-OraHome92-》Configuration and Migration Tools-》Net Manager
    打开一个界面点  本地-》服务命名
    再点左面的绿色的“+”,Net服务名填写你想要显示的名字例如(orcl_ip),然后下一步
    选 TCP/IP(Internet协议)  下一步
    主机名: 远程数据库的IP地址,端口默认(远程oracle的端口)  下一步
    服务名:windows看服务名,unix好像echo $ORACLE_HOME,我的机器orcl 下一步
    测试,更改用户密码测试,如果成功显示如下
    scott  tiger

    ###########################################################登录数据库
    conn 用户名/密码@网络服务名 as sysdba|sysoper
    connect scott/tiger@oracle
    conn  scott/tiger@oracle   远程访问,服务名oracle
    conn scott/tiger   本地
    conn system as sysdba
    conn system/123456 as sysdba

    show user
    disc 断开连接
    passw  修改密码

    ###########################################################文件操作命令
    文件操作命令:
    @ d:\a.sql  或 start d:\a.sql  执行.sql里德SQL命令
    edit d:\a.sql  编写a.sql
    spool d:\b.sql  …… spool off  将sql*plus屏幕上的内容……输出到指定文件中

    ###########################################################显示和设置环境变量
    显示和设置环境变量
    show linesize
    set linesize 90  每行显示90个字符
    set pagesize 5   每5行分页
    ###########################################################权限
    创建用户(需要system权限)
    create user 用户名 identified by my123;   创建用户时不能以数字开头
    password 用户名;   修改用户密码,修改别人密码需dba或拥有alter user权限
    alter user 用户名 identified by 密码
    drop user  用户名     自己不能删除自己
    drop user 用户名 cascade     在删除用户时,如果用户已经创建了表,连表一起删除
    刚创建的用户,没有任何权限,连登录权限都没有。
    权限分为:系统权限140多个、对象权限25个(select、insert、update、delete、all)
    系统权限:create session有此权限才可登录到数据库
    角色:具有一系列权限 connect、dba、resource(在任何一个表空间建表)
    grant connect to 用户名    具备登录数据库权限
    grant resource to 用户名   具备建表权限
    grant select on emp to 用户名  把对emp表的select权限付给用户。
    在scott用户执行grant select on emp to 用户名(把对scott的emp表的select权限付给用户)。登录用户名执行select * from scott.emp;
    grant all on emp to 用户名;   把emp表操作的所有权限付给用户。

    revoke select on emp from 用户名;   收回权限

    grant select on emp to 用户名 with grant option   对象权限+with grant option可将获得的权限再次分给其他用户
    grant   connect     to 用户名 with admin option   系统权限+with admin option可将获得的权限再次分给其他用户

    注:如果用户A将权限分给B,B将权限跟给C,将B的权限收回后,C权限也将自动被收回。株连模式

    ########################################################### profile文件实现管理用户口令

    create profile 文件名加锁 limit failed_login_attempts 3 password_lock_time 2;  密码输入3次,锁定2天
    alter user 用户名 profile 文件名加锁;  给用户加锁
    alter user 用户名 account unlock;   解锁

    create profile 文件名 limit password_life_time 10 password_grace_time 2;   每10天修改密码,宽限期2天
    create profile 文件名 limit password_life_time 10 password_grace_time 2 password_reuse_time 5; 指定口令可重用时间5天后可重用

    drop profile 文件名      删除profile文件,被profile约束的用户可释放
    ###########################################################表管理,数据类型
    以字母开头
    长度30字符
    不能用oracle保留字
    A-Z,a-z,0-9,$,#等
    字符型:
    char 最大2000字符
    例: char(10) '小韩'前4个字符放'小韩',后添6个空格不全。查询速度快,例如身份证,全字符比较。
    varchar2(20) 变长 最大4000字符
    例:varchar2(10) '小韩' 只分配4个字符。节省空间,查询速度慢,一个一个字符比较。
    clob(character large objient) 字符型大对象 最大4G
    数字型:
    number 10-38次方-10+38次方
    number(5,2)  表示一个小数有5位有效数,2位小数,范围-999.99-999.99
    number(5) 表示一个5位整数,范围-99999-99999
    日期:
    date 年月秒
    datestamp 毫秒级,银行项目
    图片:
    blob 二进制数据,可存放图片、声音 4G , 一般保密性高的图片、声音可以放入数据库,普通只存放图片路径
    ###########################################################表管理,表操作
    学生表
    create table student(
    xh number(4),--学号
    xm varchar2(20),--姓名
    sex char(2),--性别
    birthday date,--出生日期
    sal number(7,2) --奖学金
    );
    desc student  --查看表结构
    alter table student add (classid number(2)); --添加字段
    alter table student modify (xm varchar2(30)); --修改字段
    alter table student drop column sal; --删除字段 (一般不用)
    rename student to stu; --修改表名
    drop table student; 删除表
    ###########################################################表管理,数据操作
    insert into student values ('A001','张三','男','01-5月-05',10);   oracle默认日期格式'DD-MON-YY'
    alter session set nls_date_format='yyyy-mm-dd'; --修改默认日期格式
    insert into student(xh,xm,sex) values('A003','John','女'); --插入字段
    insert into student(xh,xm,sex) select * from student;  --复制自身插入数据,速度2的N次方。
    insert into student(xh,xm,sex,birthday) values(''A003','John','女',null); --插入空数据
    select * from student where birthday is null/not null;  --查询空数据
    update student set sex='女',birthday='1997-12-11' where xh='A001'; --修改数据
    delete from student;  删除所有记录,表结构还在,写日志,可以恢复,删除速度慢
    savepoint aa; 保存点
    roolback to aa; --回滚
    drop table student; --删除表的结构和数据
    delete from student where xh='A001'; --删除一条记录
    truncate table student; --删除表中所有记录,表结构还在,不写日志,无法找回删除记录,删除速度快
    ###########################################################表管理,select操作
    set timing on;  显示sql语句执行完成时间
    如何处理null值
    select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp; --nvl(comm,0) 如果nvl为空,就用0计算,如果不为空就用本身值。
    日期大于1982-1-1要写成  date>'1-1月-1982'  不能写成 date>'1982-1-1'  此为Oracle默认的日期格式
    like % _  0到多个字符,1个字符
    is null / is not null
    select * from emp by deptno,sal desc;   --desc降序
    select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪"
    ###########################################################Oracle复杂查询、子查询
    max,min,avg,sum,count分组函数
    select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;
    1、分组函数只能出现在选择列,或having、order by子句中;
    2、如果在select语句中同时包含group by,having,order by顺序为group by,having,order by;
    3、选择列中如果有列、表达式、分组函数,则这些列和表达式必须有一个出现在group by子句中。
    多表查询条件,至少为表个数-1。笛卡尔集。
    between  1 and 2;
    自连接:
    select worker.ename,boss.ename from emp worker,emp boss where worker.ename=FROD;
    子查询:
    把能排除最多查询数据的条件放在子查询的左边,SQL语句扫描 左->右。
    in
    select * from emp where sal> all(select sal from emp where deptno=30); 
    select * from emp where sal> (select max(sal) from emp where deptno=30); --效率高
    select * from emp where sal> any(select sal from emp where deptno=30);
    select * from emp where sal> (select min(sal) from emp where deptno=30); --效率高
    多列子查询:
    select * from emp where (deptno,job)=(select deptno,job from emp where ename="SMITH";
    不能给表取别名。
    ###########################################################Oracle分页 三种方式
    1、根据rowid分来
    select * from emp where rowid in (select rid from (select rownum rn,rid
    from(select rowid rid,cid from emp drder by cid desc) where rownum<=10) where rn>=6 order by cid desc;
    执行时间0.03秒
    2、按分析函数来分
    select * from (select a1.*,row_number() over (order by cid desc) rk from emp a1) where rk<10000 and rk>9980;
    执行时间1.01秒
    3、按rownum来分
    select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;
    select * from (select a1.*,rownum rn from (select * from emp order by cid desc) a1 where rownum<=10) where rn>=6; 二分法,查询速度快
    修改列显示,只需修改select * from emp。
    执行时间0.1秒
    emp表明,cid关键字段,取第6-第10记录,7万条记录。

    ###########################################################Oracle合并查询
    M union N where  合并排重   --M、N为表
    M union all N where  合并
    M minus N   M减N
    M intersect N 取M、N交集
    ###########################################################Oracle 创建数据库
    Configuration and Migration Tools——Database Configuration Assistant
    Data Warehouse  数据仓库
    General Purpose 普通用途
    New Database 
    Transaction Processing 事物处理
    ###########################################################Oracle 事务和锁
    create table myemp (id,ename,sal) as select empno,ename,sql from emp; --用查询结果创建一个新表
    insert into myemp (Myid,myname,mydept) select empno,ename,deptno from emp where deptno=10; --批量插入数据
    update emp set (job,sal,comm) = (select job,sal,comm from emp where ename='SMITH' where ename='SCOTT';

    commit; --提交事务,执行后,会确认事务的变化、结束事务、删除保存点、释放锁
    事务几个重要操作:
    1、设置保存点  savepoint a
    2、取消部分事务 rollback to a
    3、取消全部事务 rollback

  • SQL基础学习笔记

    2010-06-07 10:39:26

    数据定义
    create
    alter
    drop

    table、view、index、procedure、trigger、schema、domain

    数据操作
    select
    insert
    delete
    update

    数据控制
    grant
    deny
    revoke

    事物控制
    commit
    rollback
    set transaction

    注明:以下SQL语句中M,N为表 A,B,C为栏目
    select distinct A from M where A>100 or (A<50 and A>30)  对栏目A排重
    select * from M where A in ('namea','nameb')   取出A=namea,A=nameb的数据
    select * from M where A between '1982' and '1992' 取出1982-1992的数据
    select * from M where A like 'a_z' 'a%' '%a' '%a%'  _:一个字符 %:多个字符
    select * from M order by A asc, B desc  asc升序 desc降序

    select sum(A),B from M group by A having sum(A)>100  如果被select的只有函数栏,不需要group by子句//avg,count,max,min,sum
    select count(distinct A),B from M group by B

    内部连接
    select * from M,N where M.A=N.A
    外部连接
    where A1.A=A2.A(+)  新表数据都要
    select concat(A,B) from M 把A,B数据字符串串连起来
    select substr(A,3,4) from M 抓出A中第3个字符开始向后抓4个字符
    select trim(' sample ')   'sample' 移除sample左右字符
    select ltrim(' sample ')  'sample '
    select rtrim(' sample ')  ' sample'

    表格处理
    create table M (SID integer Unique,First_name char(50) not null,Last_name char(50),Birth_date date)
    create table M (SID integer Unique)
    create table M (SID integer Check (SID>0))
    create table M (SID integer,Primary Key(SID))  //MySQL
    create table M (SID integer Primary Key)        //Oracle,SQLServer
    alter table M Add Primary Key (SID)   //必须先确认主键栏位not null

    限制
    not null
    unique 不允许输入重复值
    check  栏位中数据符合某些条件 (未用在MySQL)

    create view 视图名 as select * from M   创建视图
    create index 索引名 on M(A,B)  为栏目A,B创建索引

    alter table M add C char(50)  添加栏目C
    alter table M change A C char(50) 修改栏目A为C
    alter table M modify C char(30)  修改栏目C
    alter table M drop C  删除栏目C

    drop table M       删除表
    truncate table M   清空表

    insert into M (A,B) values ('a','b')  插入数据
    insert into M (A,B) select A,B from N where ……  将N表A,B数据插入M表
    update M set A=a,B=2 where …… 修改数据
    delete from M where ……  删除数据

    select A from M
    union/union all/intersect/minus   M,N的A栏目合并且剔除重复/不剔除重复/A字段交集/M中有N中没有
    select A from N

    子查询
    select * from M exists (select * from N where ……)  如果内查询(select * from N where ……)有数据,则执行外查询select * from M
    select A ,case A
      when 条件 then B*2   //条件可以是一个值或公式
      when 条件 then B*2
      ……
      else B 
      end
      New_B,C from M  New_B为CASE栏位的栏位名

     

  • Oracle init.ora常用配置详解

    2010-05-25 15:56:14

    以下内容摘自: www.51testing.com

    db_name = "51test"

      一个数据库标识符,应与CREATE DATABASE 语句中指定的名称相对应。

    instance_name = 51test

      在多个例程使用相同服务名的情况下,用来唯一地标识一个数据库例程。

    INSTANCE_NAME

      不应与SID混淆,它实际上是对在一台主机上共享内存的各个例程的唯一标识。

    service_names = 51test

      为 Net8 监听程序可用于识别一个服务 (如:复制环境中的一个特定数据库) 的例程指定服务名。如果该服务没有域,将附加 DB_DOMAIN 参数。

    control_files = ("/opt/apps/oracle/oradata/51test/control01.ctl", "/opt/apps/oracle/oradata/51test/control02.ctl", "/opt/apps/oracle/oradata/51test/control03.ctl")
    open_cursors = 320

      库高速缓存 指定一个会话一次可以打开的游标(环境区域)的最大数量,并且限制 PL/SQL 使用的 PL/SQL 游标高速缓存的大小,以避免用户再次执行语句时重新进行语法分析。请将该值设置得足够高,这样才能防止应用程序耗尽打开的游标。

    max_enabled_roles = 32
    db_block_buffers = 5120

      高速缓存与I/O 缓冲区高速缓存中 Oracle 块的数量。该参数会显着影响一个例程的 SGA 总大小。

    shard_pool_size = 75497472
    large_pool_size = 15728640

      池--指定大存储池的分配堆,它可被多线程服务器 (MTS) 用作会话内存、用作并行执行的消息缓冲区以及用作 RMAN备份和恢复的磁盘 I/O 缓冲区。

    java_pool_size = 65536

      以字节为单位,指定 Java 存储池的大小,它用于存储 Java 的方法和类定义在共享内存中的表示法,以及在调用结束时移植到 Java 会话空间的 Java 对象。

    log_checkpoint_interval = 10000

      指定在出现检查点之前,必须写入重做日志文件中的 OS 块(而不是数据库块)的数量。无论该值如何,在切换日志时都会出现检查点。较低的值可以缩短例程恢复所需的时间,但可能导致磁盘操作过量。

    log_checkpoint_timeout = 1800

      指定距下一个检查点出现的最大时间间隔(秒数)。将该时间值指定为 0,将禁用以时间为基础的检查点。较低的值可以缩短例程恢复的时间,但可能导致磁盘操作过量

    processes = 220
    log_buffer = 8388608

      以字节为单位,指定在 LGWR 将重做日志条目写入重做日志文件之前,用于缓存这些条目的内存量。重做条目保留对数据库块所作更改的一份记录。如果该值大于65536,就能减少重做日志 文件 I/O,特别是在有长时间事务处理或大量事务处理的系统上  **最大值为 500K 或 128K * CPU_COUNT,两者之中取较大者

    oracle_trace_enable = true

      启动一个默认的 Oracle Trace 集合,直到该值再次设置为 NULL。

    sql_trace=false

      这些信息对改善性能很有用。由于使用 SQL 跟踪设备将引发系统开销,只应在需要优化信息的情况下使用 TRUE。

    timed_statistics=true

      收集操作系统的计时信息,这些信息可被用来优化数据库和 SQL

      语句。要防止因从操作系统请求时间而引起的开销,请将该值设置为零。 将该值设置为 TRUE 对于查看长时间操作的进度也很有用。

    background_dump_dest = /opt/apps/oracle/admin/51test/bdump

      指定在 Oracle 操作过程中为后台进程(LGWR,DBW n 等等)写入跟踪文件的路径名(目录或磁盘)。它还定义记录着重要事件和消息的数据库预警文件的位置。

    core_dump_dest = /opt/apps/oracle/admin/51test/cdump

      指定核心转储位置的目录名(用于 UNIX)。

    resource_manager_plan = system_plan

      如果指定该值,资源管理器将激活计划和例程的所有子项 (子计划、指令和使用者组)。如果不指定,资源管理器将被禁用,但使用 ALTER SYSTEM 命令还可以启用。

    user_dump_dest = /opt/apps/oracle/admin/51test/udump

      为服务器将以一个用户进程身份在其中写入调试跟踪文件的目录 指定路径名。例如,该目录可这样设置: NT 操作系统上的 C:/

      ORACLE/UTRC;UNIX 操作系统上的 /oracle/utrc;或 VMS 操作系统上的DISK$UR3:[ORACLE.UTRC]。

    db_block_size = 8192

      一个 Oracle 数据库块的大小(以字节计)。该值在创建数据库时设置,而且此后无法更改。 1024 - 65536 (根据操作系统而定)。

    remote_login_passwordfile = exclusive

      指定操作系统或一个 文件是否检查具有权限的用户的口令。如果设置为 NONE,Oracle 将忽略口令文件。如果设置为    EXCLUSIVE,将使用数据库的口令文件对每个具有权限的用户进行验证。如果设置为 SHARED,多个数据库将共享 SYS 和     INTERNAL 口令文件用户

    os_authent_prefix = ""

      使用用户的操作系统帐户名和口令来验证连 接到服务器的用户。该参数的值与各用户的操作系统帐户连接在一起。要去除 OS

      帐户前缀,请指定空值。

    job_queue_processes = 4

      只用于复制环境。它指定每个例程的 SNP 作业队列进程的数量 (SNP0, ... SNP9, SNPA, ... SNPZ)。要自动更新表快照或执行由 DBMS_JOB 创建的请求,请将该参数设置为 1 或更大的值。   0 到 36

    job_queue_interval = 60

      作业队列 只用于复制环境。它以秒为单位指定该例程的每个 SNPn 后台进程的唤醒频率。  1 到 3600

    distributed_transactions = 10

      一个数据库一次可参与的分布式事务处理的最大数量。如果由于网络故障异常频繁而减少该值,将造成大量未决事务处理。

    open_links = 4

      指定在一次会话中同时打开的与远程数据库的连接的最大数量。该值应等于或超过一个引用多个数据库的单个 SQL 语句中引用的数据库的数量,这样才能打开所有数据库以便执行该语句。

    mts_dispatchers = "(protocol=TCP)(mul=ON)(tick=15)(pool=(in=2)(out=2))"

      为设置使用多线程服务器的 共享环境而设置调度程序的数量和类型。可以为该参数指定几种选项。这是字符串值的一个示例:"(PROTOCOL=TCP) (DISPATCHERS=3)"。

    compatible = "8.1.0"

      允许使用一个新的发行版,同时保证与先前版 本的向后兼容性。

    sort_area_size = 524288

      以字节为单位,指定排序所使用的最大内存量。排序完成 后,各行将返回,并且内存将释放。增大该值可以提高大型排序的效率。如果超过了该内存量,将使用临时磁盘段。相当于 6 个数据库块的值 (最小值) 到操作系统确定的值(最大值)。

    sort_area_retained_size = 131072

      以字节为单位,指定 在一个排序运行完毕后保留的用户全局区 (UGA) 内存量的最大值。最后一行从排序空间中被提取后,该内存将被释放回 UGA,而不是释放给操作系统。

  • 使用聚集索引和非聚集索引的区别

    2009-12-02 15:07:45

    使用聚集索引

        聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。

    聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。避免每次查询该列时都进行排序,从而节省成本。

    注意事项
    定义聚集索引键时使用的列越少越好。
    • 包含大量非重复值的列。
    • 使用下列运算符返回一个范围值的查询:BETWEEN、>、>=、< 和 <=。
    • 被连续访问的列。
    • 返回大型结果集的查询。
    • 经常被使用联接或 GROUP BY 子句的查询访问的列;一般来说,这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引,可以使 SQL Server 不必对数据进行排序,因为这些行已经排序。这样可以提高查询性能。
    • OLTP 类型的应用程序,这些程序要求进行非常快速的单行查找(一般通过主键)。应在主键上创建聚集索引。


    聚集索引不适用于:

    • 频繁更改的列 。这将导致整行移动(因为 SQL Server 必须按物理顺序保留行中的数据值)。这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。

    • 宽键 。来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。

    使用非聚集索引

    非聚集索引与课本中的目录类似。数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储,而表中的信息按另一种顺序存储(这可以由聚集索引规定)。如果在表中未创建聚集索引,则无法保证这些行具有任何特定的顺序。

    多个非聚集索引
    有些书籍包含多个索引。例如,一本介绍园艺的书可能会包含一个植物通俗名称索引,和一个植物学名索引,因为这是读者查找信息的两种最常用的方法。对于非聚集索引也是如此。可以为在表中查找数据时常用的每个列创建一个非聚集索引。


    注意事项

    在创建非聚集索引之前,应先了解您的数据是如何被访问的。可考虑将非聚集索引用于:
    • 包含大量非重复值的列,如姓氏和名字的组合(如果聚集索引用于其它列)。如果只有很少的非重复值,如只有 1 和 0,则大多数查询将不使用索引,因为此时表扫描通常更有效。
    • 不返回大型结果集的查询。
    • 返回精确匹配的查询的搜索条件(WHERE 子句)中经常使用的列。
    • 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引,在任何外键列上创建一个聚集索引。
    • 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。

    索引都是一种排序,只是聚集索引的排序和物理表中的数据排序相同,一致的;而非聚集索引的排序和物理表数据的排序不同。

    当我们在更新统计信息的时候,对于那些聚集索引列不但要更新索引页还要同时对数据物理表数据重新排序;而对非聚集索引列则只需要更新索引页。

    转载:http://hi.baidu.com/yuleboxcn/blog/item/1a2464faf153ad6d034f5697.html

  • 数据库索引的详细说明

    2009-12-02 15:03:44


    一、简介

      自从你和你的团队成功的开发和部署了一个INTERNET网站,已经过去数月了,这个网站在很短的时间内吸引了数千用户前来注册和使用,因此你有了一个非常满意的客户。包括你和你的团队、管理层、客户,每个人都非常高兴。

      生活并不总是一帆风顺的。当站点的用户开始日均高速增长的时候,问题随即出现了,客户发来邮件开始抱怨网站性能太慢,同时称网站正在丢失客户。

      你开始调查这个系统,很快你发现当系统访问或更新数据的时候,速度非常慢。打开数据库一看,数据库的记录增加的很快,有些表的记录达到了成千上万行,测试团队在产品数据库上做了一个测试,结果发现在测试服务器上仅2/3秒就能完成的一个处理过程,现在需要5分钟。”

      这个古老的故事发生在全球范围内的数以千计的系统身上。包括我在内,几乎每个开发人员在他或她的开发过程中会碰到同样的事情。我知道为什么这样的情形会发生,同时我也知道如何去克服它。

      二、阅读范围

      请注意本一系列文章讨论的主要的焦点是“事务性的SQLServer数据库数据访问性能优化”,但大部分优化技术同样适用于其他的数据库。

      我将要讨论的优化技术仅仅适用于软件开发人员。作为一个开发者,你需要跟随我关注的问题,确认你已经作了所有能做的事情,去优化你已经写的或将要写的数据访问代码。数据库管理人员(DBA)同样在优化和提高性能上扮演了很重要的角色,但是DBA领的优化将不属于这篇文章讨论的范围。

      三、开始优化一个数据库

      当基于数据库的应用系统放慢的时候,99%的可能是系统的数据访问过程没有优化,或者没有使用最好的方式。所以你需要回顾和优化你的数据访问/操作过程,提高系统的全局性能。接下来我们通过一步一步的方式开始我们的优化任务。

      第一步:在列上采用正确的索引

      有些人可能争论实施正确的索引是否是数据库优化过程的第一步。但是我认为在数据库应用正确的索引是第一位的。原因有2点:

      1.在一个产品系统里,它将使你在很快的时间内提高尽可能大的性能。

      2.创建数据库索引不需要你做任何的系统修改,因此不需要任何重新编译和部署

      如果你发现有当前的数据库没有很好的处理索引,你建了索引,结果就是性能的快速提升。然而,如果索引已经处理了,我们进入下面的步骤。

      什么是索引

      我相信你已经明白了什么是索引,但是,我仍旧看到很多人对索引不太清楚。让我们再一次弄明白什么是索引,请看下面的小故事。

      很久以前,在一个古城市里有一个很大的图书馆,里面有数以千计的图书,图书凌乱的存放在书架上。因此,一旦有读者向图书员索要一本图书,图书员除了一本一本的检查图书,看是否匹配读者索要的图书,其它没有更好的办法。发现一本渴望的图书往往需要花费图书员数个小时。同时读者也不得不等很长的时间。

    [这看起来象一个没有主键的表,当在表里进行搜索数据的时候,数据库引擎需要遍历全部的数据来查找相关的记录,所以运行起来非常慢。]

      当读者和图书每天都在大量增加的时候,图书员的工作越来越繁重。有一天,有一个智者来到图书馆,看到图书员的繁重的工作,建议他给每一本书编号,同时按顺序码放在书架上。“我可以从中得得什么好处?”图书员问,那个智者回答到:“如果有读者通过给你一个书号来索要图书,你很快就能发现在哪个书架上存放了包含该书号的图书,然后在这个书架上,你同样能很快的找到需要的图书”

      [给书编号就象在数据表里创建一个主键,当你在一个表里创建了一个主健后,系统就创建了一个聚集索引树,所有的包含记录的数据页按照主键的值在文件系统中进行排序.每一个数据页内部也同样按照主键的值进行排序.所以,当你向数据库请求任何一个数据行的时候,首先数据库服务器使用聚焦索引找到合适的页(象首先发现书架一样),接着在页里查找包含主键值的记录(象在书架发现一本书)]

      “这正是我所需要的”,兴奋的图书员开始给书编号,接着把它们排列在不同的书架上,他花费了一天的时间来排序.在那天快结束的时候,他做了测试,结果发现几乎不用花费时间就能找到一本书.图书员高兴极了.

      [这正是你创建了主键后所发生的事情.首先,创建了聚焦索引,接着数据页在物理文件里按照主键的值被排序.有一点我想你应该很容易理解,因为数据仅仅只能使用一列的值作为凭证来排序,所以一个表只能创建一个聚焦索引.就象图书只能使用一个标准即书号来排序一样.]

      等一等,问题还没有被完全解决,在接下来的时间里,有个读者没有图书的编号,只有图书的名字,他想通过书名索要图书,如何办呢?可怜的图书员只能按照从1到N来查遍所有已经编号的图书.如果图书存放在67号书架上,他可能需要20分钟,相比早间图书没有被排序的时候,他所花费的2-3个小时.这确实有一个进步.但是和花费30秒通过书号查找一本书比较起来,,20分钟仍旧是一个不短的时间.还有没有更好的办法呢?他问那个智者。

      [假设你有一个产品表,如果你只有一个ProductID主键而没有其它的索引,上述的情况同样会发生,所以,当使用产品名字来搜索的时候,数据引擎只能遍历文件里所有物理排序的数据页,没有其它的办法.]

      那个智者告诉图书员:因为你已经按照书号对图书做了排序,你不能使用其它的凭证重新排序,所以,较好的方法是创建一个包含书名和与之对应的编号的目录或索引,在这个目录上,按照图书的字母顺序排序,并使用阿拉伯字母进行分组,例如,当有人想查找DatabaseManagementSystem这本书的时候,你使用下列的规则就能发现这本书

      1.在书名目录里跳到D章,找到包含你的书名的图书.

      2.得到这本书的书号,然后用书号去查找这本书

      “你真是一个天才”,图书员喊到,他立即花费了一些时间创建了书名的目录,通过一个快速的测试,他发现使用书名来查询仅仅需要1分钟,其中30秒查找书的编号,30秒用编号来找书.

      图书员想到,读者还可能使用其它的凭证来查找图书,例如作者的名字,所以他为作者创建了同样的目录.在创建了这些目录后,图书员可以使用这些凭证在1分钟内找到图书.图书员的繁重的工作终于结束了,许多读者也因为很快的查找到图书而聚集在图书馆,图书馆变的非常热闹起来.

      图书员随后开始过着他的快乐的生活,故事结束了.

      到这里,现在我确信你已经明白了什么是索引,为什么它们如此重要以及它们的内部工作原理,,例如,我们有一个已创建聚焦索引的产品表Products,因为当创建了主键的时候,随即就创建了聚焦索引。我门应当在Productname列创建一个非聚焦索引,一旦我们这样作了,数据库引擎就为非聚焦索引创建一个索引树,象故事里的书名目录,按照产品的名字在索引页里排序。每个索引页包含一定范围的产品名字和与之对应的ProductID,所以当使用产品名字作为凭证搜索的时候,数据库引擎首先查询产品名字的非聚焦索引树来发现这本书的主键productID,一旦发现,数据库引擎就使用主键ProductID来搜索聚焦索引树,从而并得到正确的结果。

    索引树的工作原理

    被称做为B+树,中间的节点包含一定数量的值,指示数据库引擎当从跟节点搜索一个索引值的时候如何遍历.如果这是一个聚焦索引树,页节点是物理数据页.如果是非聚焦索引树,页节点包含包含索引值和与之对应的聚焦索引值.

      通常,在索引树里发现需要的值并且转到目标数据记录,对于数据库引擎来说花费的时间是很短的,所以,在数据库应用索引极大的提高了数据的检索操作.

      请跟随下列的步骤确保正确的索引包含在你的数据库里。

      确保数据库的每个表有一个主健

      这么做会确保每个表有一个聚焦索引,通过主健的值,表的数据页通按物理顺序排列在磁盘上。所以,任何使用主健的数据检索操作,任何在主健字段的排序操作都能非常迅速的检索数据。

      在这些列上创建非聚焦索引

      经常被作为搜索凭证的列

      用来联合其它表的列

      用来作为外健的列

      用来排序的列

      高选择性列

      Xml类型

      下面是一个创建索引的命令的例子

      CREATEINDEX

      NCLIX_OrderDetails_ProductIDON

      dbo.OrderDetails(ProductID)

    第二步:创建正确的复合索引

      现在,你是否已经在数据库创建了所有的适合的索引?假设,在一个Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty),你已经在外键(ProductID)创建了索引,如果ProductID是一个高选择性列,任何在where语句里使用索引列(ProductID)的检索数据的SELECT查询都会运行的非常快吗?

      对,相对没有在外键创建索引的情况(这需要全部数据页的遍历)来说,这是非常快的,但是,还有进一步提升的空间.

      让我们假设:Sales表包含10,000行数据,下面的SQL语句选择400行。


      SELECTSalesDate,SalesPersonIDFROMSalesWHEREProductID=112

      首先让我们弄明白在数据库引擎怎么执行SQL语句的:

      1.Sales表有在ProductID列一个非聚焦索引,所以,首先查询非聚焦索引树,发现包含ProductID=112的入口。

      2.包含ProductID=112入口的索引页同样同样也包含了聚焦索引的值(所有的主健的值,即SalesID)

      3.对于每一个主健(共400个),数据库引擎进入聚焦索引树来发现正确的行的位置

      4.对于每一个主健,一旦发现正确的行的位置,数据库引擎会从匹配的行得到SalesDate和SalesPersonID的列的值。

      请注意,在上述的步骤中,对于每一个ProductID=112的主键入口(共400个),数据库引擎必须搜索聚焦索引树400次,来检索附加的列(SalesDate,SalesPersonID)。

    让我们猜想一下,如果非聚焦索引不但包含了聚焦索引的值(主健),同时还包含查询里标注的其他的2个列(SalesDate,SalesPersonID)的值,数据库引擎就不用执行上述的第3步和第4步,只须进入ProductID的列的非聚焦索引树,从索引页上读取3个列的值,这样运行的速度不是更快吗?

      幸运的是,有一种办法来实施这种特点,这就是复合索引。你可以在表的列上创建复合索引,标明哪些列是和聚焦索引一起的应该附加存储的列。下面是一个在表Sales表的列ProductID创建复合索引的例子。


      CREATEINDEXNCLIX_Sales_ProductID--Indexname
      ONdbo.Sales(ProductID)--Columnonwhichindexistobecreated
      INCLUDE(SalesDate,SalesPersonID)--Additionalcolumnvaluestoinclude

      请注意,创建复合索引应当包含少数几个列,并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给你带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能降低。

      当创建复合索引的时候,尽量使用DatabaseTuningAdvisor(数据库优化顾问)的帮助。

      我们知道,一旦一个SQL开始运行,SQLSERVER引擎优化器基于以下几点动态的产生不同的检索计划。

      数据量

      统计

      索引变化

      TSQL的参数值

      服务器的负载

      这意味着:对于一个特殊的SQL语句,在产品服务器上的执行计划可能和在测试服务器上的执行计划不近相同,甚至表和索引结构一样。这同样也表明,一个在测试服务器上创建的索引可能会加速测试服务器上的性能,但是在产品服务器上的同样的索引可能不会带给你任何益处。为什么?因为在测试环境下的SQLSEVVER执行计划可能使用创建的索引,因此给你很好的性能,但是,在产品服务器上的执行计划可能出于下列的原因而根本不使用新创建的索引。例如:一个非聚焦索引列在产品服务器上不是高选择性列,而在测试服务器上是高选择性列.

      所以,当创建索引的时候,我们需要弄明白这一点:索引是执行引擎用来提高速度的。但是我们该如何去做呢?

      答案是我们必须在测试服务器上模拟产品服务器的负载,接着创建索引,以及测试他们。只有这样,在测试服务器上能提高性能的索引,才能更有可能在产品服务器上提高性能。

      这么做应该很困难,但幸运的是,我们有一些好用的工具去实现它,请跟随下面的指导:

      1:使用SQLprofiler捕获产品服务器上的痕迹。使用Tuningtemplate(我知道,有人建议不要在产品服务器上使用SQLprofiler,但有些时候,你不得不在产品服务器上诊断性能问题的时候使用它),如果你不熟悉这个工具,或者你想了解更多的关于SQLprofiler的知识,请阅读http://msdn.microsoft.com/en-us/library/ms181091.aspx

      2.利用上一步产生的跟踪文件,用数据库优化顾问在测试数据库创建相似的负载,从优化顾问得到一些建议,特别是创建索引的建议,你很可能从优化顾问那里获得比较实际的建议。因为优化顾问使用产品服务器产生的跟踪文件来装载测试服务器,所以能产生最可能好的索引建议。如果你不熟悉优化顾问工具,或者你想了解更多的关于使用优化顾问的的资料,请阅读:http://msdn.microsoft.com/en-us/library/ms166575.aspx.

    第三步:如果有碎片发生,重新整理它

      到了这里,如果你已经在表里创建了所有正确的索引,但是,你可能还没有获得所希望的良好的性能。什么原因呢?有一种可能是出现了索引碎片。

      1、什么是索引碎片

      索引碎片是这样一种情形:由于在表里大量的插入、修改、删除操作而使索引页分裂。如果索引有了高的碎片,有两种情况,一种情况是扫描索引需要花费很多的时间,另一种情况是在查询的时候索引根本不使用索引,都会导致性能降低。

      有2种类型的碎片:

      内部破碎:由于索引页里的数据插入或修改操作而发生,以数据作为稀疏矩阵的形式的分布而结束,这将导致数据页的增加,从而增加查询时间。

      外部破碎:由于索引/数据页的数据插入或修改而发生,以页码分离和在文件系统里不连贯的新的索引页的分配而结束,数据库服务器不能利用预读操作的优点,因为:下一个相关联的数据页不临近,而且这些相关连的下面的页码可能在数据文件的任何地方。

      2、如何知道索引破碎是否已经发生?

      在数据库执行下面的SQL语句(下面的语句在SQLserver2005及以后的版本运行正常,以你的目标数据库的名字取代AdventureWorks’)


      SELECTobject_name(dt.object_id)Tablename,si.name
      IndexName,dt.avg_fragmentation_in_percentAS
      ExternalFragmentation,dt.avg_page_space_used_in_percentAS
      InternalFragmentation
      FROM
      (
      SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
      FROMsys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED'
      )
      WHEREindex_id<>0)ASdtINNERJOINsys.indexessiONsi.object_id=dt.object_id
      ANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10
      ANDdt.avg_page_space_used_in_percent<75ORDERBYavg_fragmentation_in_percentDESC

    分析上面的结果,你就能发现在哪里出现了索引碎片,应用下面的规则:

      ExternalFragmentation的值>10,预示对应的索引出现外部碎片。InternalFragmentation的值<75,预示对应的索引出现内部碎片

      3、怎样重新整理索引碎片

      有2种方式:

      索引重组:执行下面的命令:

      ALTERINDEXALLONTableNameRECOGNIZE

      索引重建:

      ALTERINDEXALLONTableNameREBUILDWITH(FILLFACTOR=90,ONLINE=ON)

      通过使用具体索引的名字代替ALL,你能重组或重建单个的索引。你也可以使用数据库控制台来重建/重组索引

      SQL Server使用索引实现数据访问优化

      4、什么时候重组和重建索引?

      当外部碎片的值在10-15,内部碎片的值在60-75,对于这样的索引,你应该重组索引。否则,你应该重建索引。

      关于索引重建的一个重要的事情是:一旦在一个特定的表上重建索引,表就会被锁定(重组的时候不会发生)。所以,对于一个产品数据库的一个大的表,因为在一个大表上的索引重建往往需要花费数个小时,我们不希望这种锁定。幸运的是,在SQL2005有一个解决方法,你可以在重建一个表的索引的时候,把ONLINE选项的值设为ON,这样会使重建索引和表上的数据事务同样进行。

      四、实现数据访问结束语

      在数据表里的所有适合创建索引的字段上创建索引,这是非常诱惑人的。但是如果你正在从事一个事务数据库工作,在每个字段上创建索引并不是每次都是需要的。事实上,在一个OLTP系统上创建大量的索引可能会降低数据库的性能。(因为当很多操作是更新操作的时候,更新数据意味着更新索引)

      一个首要的规则建议如下:

      如果你在从事一个事务性数据库,平均不要在一个表上创建超过5个索引,另外,如果你在从事数据仓库,平均最高可在一个表上创建10个索引。

    转载:http://hi.baidu.com/yuleboxcn/blog/item/dd0c4dd1bbf96f87a1ec9c54.html
Open Toolbar