精华内容
下载资源
问答
  • My SQL建立索引

    千次阅读 2014-03-03 16:36:52
    什么是索引 拿汉语字典的目录页(索引)打比方:正如汉语字典的汉字按页存放一样,SQL Server的数据记录也是按页存放的...同理,SQL Server允许用户创建索引,指定按某列预先排序,从而大大提高查询速度。 •

    什么是索引

    拿汉语字典的目录页(索引)打比方:正如汉语字典中的汉字按页存放一样,SQL Server中的数据记录也是按页存放的,每页容量一般为4K 。为了加快查找的速度,汉语字(词)典一般都有按拼音、笔画、偏旁部首等排序的目录(索引),我们可以选择按拼音或笔画查找方式,快速查找到需要的字(词)。

    同理,SQL Server允许用户在表中创建索引,指定按某列预先排序,从而大大提高查询速度。

    •          SQL Server中的数据也是按页( 4KB )存放

    •          索引:是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。

    •          索引页:数据库中存储索引的数据页;索引页类似于汉语字(词)典中按拼音或笔画排序的目录页。

    •          索引的作用:通过使用索引,可以大大提高数据库的检索速度,改善数据库性能。

     

    索引类型

    •          唯一索引:唯一索引不允许两行具有相同的索引值

    •          主键索引:为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的,并且不能为空

    •          聚集索引(Clustered):表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个

    •          非聚集索引(Non-clustered):非聚集索引指定表的逻辑顺序。数据存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针。可以有多个,小于249个

     

    索引类型:再次用汉语字典打比方,希望大家能够明白聚集索引和非聚集索引这两个概念。

     

    唯一索引:

    唯一索引不允许两行具有相同的索引值。

    如果现有数据中存在重复的键值,则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时,数据库也拒绝接受此数据。例如,如果在stuInfo表中的学员员身份证号(stuID) 列上创建了唯一索引,则所有学员的身份证号不能重复。

    提示:创建了唯一约束,将自动创建唯一索引。尽管唯一索引有助于找到信息,但为了获得最佳性能,建议使用主键约束或唯一约束。

     

    主键索引:

    在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时,它还允许快速访问数据。

     

    聚集索引(clustered index)

    在聚集索引中,表中各行的物理顺序与键值的逻辑(索引)顺序相同。表只能包含一个聚集索引。例如:汉语字(词)典默认按拼音排序编排字典中的每页页码。拼音字母a,b,c,d……x,y,z就是索引的逻辑顺序,而页码1,2,3……就是物理顺序。默认按拼音排序的字典,其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字(词)对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

     

    非聚集索引(Non-clustered)

    如果不是聚集索引,表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引(nonclustered index)有更快的数据访问速度。例如,按笔画排序的索引就是非聚集索引,“1”画的字(词)对应的页码可能比“3”画的字(词)对应的页码大(靠后)。

    提示:SQL Server中,一个表只能创建1个聚集索引,多个非聚集索引。设置某列为主键,该列就默认为聚集索引

     

    如何创建索引

    使用T-SQL语句创建索引的语法:

    CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 

        INDEX   index_name

         ON table_name (column_name…)

          [WITH FILLFACTOR=x]

    q       UNIQUE表示唯一索引,可选

    q       CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引,可选

    q       FILLFACTOR表示填充因子,指定一个0到100之间的值,该值指示索引页填满的空间所占的百分比

     

    在stuMarks表的writtenExam列创建索引:

    USE stuDB

    GO

    IF EXISTS (SELECT name FROM sysindexes

              WHERE name = 'IX_writtenExam')

       DROP INDEX stuMarks.IX_writtenExam 

    /*--笔试列创建非聚集索引:填充因子为30--*/

    CREATE NONCLUSTERED INDEX IX_writtenExam

         ON stuMarks(writtenExam)

              WITH FILLFACTOR= 30

    GO

    /*-----指定按索引 IX_writtenExam 查询----*/

    SELECT * FROM stuMarks  (INDEX=IX_writtenExam)

        WHERE writtenExam BETWEEN 60 AND 90

    虽然我们可以指定SQL Server按哪个索引进行数据查询,但一般不需要我们人工指定。SQL Server将会根据我们创建的索引,自动优化查询 。

     

    索引的优缺点

    •          优点

    –         加快访问速度

    –         加强行的唯一性

    •          缺点

    –         带索引的表在数据库中需要更多的存储空间

    –         操纵数据的命令需要更长的处理时间,因为它们需要对索引进行更新

     

    创建索引的指导原则

    •          请按照下列标准选择建立索引的列。

    –         该列用于频繁搜索

    –         该列用于对数据进行排序

    •          请不要使用下面的列创建索引:

    –         列中仅包含几个不同的值。

    –         表中仅包含几行。为小型表创建索引可能不太划算,因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长

    展开全文
  • sybase用SQL建立索引

    千次阅读 2008-05-27 23:17:04
    SQL建立索引 为了给一个表建立索引,启动任务栏SQL Sever程序组的ISQL/w程序。进入查询窗口后,输入下面的语句: J*W CREATE INDEX mycolumn_index ON mytable (myclumn) 这个语句建立了一个名...
    用SQL建立索引 
    
    为了给一个表建立索引,启动任务栏SQL Sever程序组中的ISQL/w程序。进入查询窗口后,输入下面的语句:
    J*W
    CREATE INDEX mycolumn_index ON mytable (myclumn)
    这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字,但你应该在索引名中包含所索引的字段名,这对你将来弄清楚建立该索引的意图是有帮助的。

    注意:
    在本书中你执行任何SQL语句,都会收到如下的信息:
    This command did not return data,and it did not return any rows n!2juVr
    这说明该语句执行成功了。
    索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引,也是个非唯一索引。(这是一个索引的缺省属性)
    如果你需要改变一个索引的类型,你必须删除原来的索引并重建 一个。建立了一个索引后,你可以用下面的SQL语句删除它:

    DROP INDEX mytable.mycolumn_index i5XCO@\I
    1x, SGs\
    注意在DROP INDEX 语句中你要包含表的名字。在这个例子中,你删除的索引是mycolumn_index,它是表mytable的索引。 ^ Qm8U]P
    要建立一个聚簇索引,可以使用关键字CLUSTERED。)记住一个表只能有一个聚簇索引。(这里有一个如何对一个表建立聚簇索引的例子: ~aq[~Jf$
    CP% #zI
    CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn) [Blcpf#ya@
    R'tmR25
    如果表中有重复的记录,当你试图用这个语句建立索引时,会出现错误。但是有重复记录的表也可以建立索引;你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可: I +,_5
    WVF2'T
    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) vj1fd}u\"
    WITH ALLOW_DUP_ROW ,pd:`{vC,
    H DV!6Zyv
    这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免在一个表中出现重复记录,但是,如果已经出现了,你可以使用这种方法。 6m +zy*6#y
    要对一个表建立唯一索引,可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子: 0 sB9vv(k
    ~nN;r[IP
    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn) _)]O:SZ;
    [$DR .2
    这是你将经常使用的索引建立语句。无论何时,只要可以,你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。 H ]=Vm $
    最后,要建立一个对多个字段的索引──复合索引──在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引: p `i_&
    ^C[>T
    CREATE INDEX name_index ON username(firstname,lastname) c"S k{ :
    @Zv 4TJz|
    这个例子对两个字段建立了单个索引。在一个复合索引中,你最多可以对16个字段进行索引。 M/W|d(S
    xL0=
    用事务管理器建立索引 !OEvNw-f\
    用事务管理器建立索引比用SQL语句容易的多。使用事务管理器,你可以看到已经建立的索引的列表,并可以通过图形界面选择索引选项。 =d..Y9pw#)
    使用事务管理器你可以用两种方式建立索引:使用Manage Tables窗口或使用Manage Indexes窗口。 R`o;
    要用Manage Tables 窗口建立一个新索引,单击按钮Advanced Options(它看起来象一个前面有一加号的表)。这样就打开了Advanced Options对话框。这个对话框有一部分标名为Primary Key &,j sAt#
    +2##|WR*p
    要建立一个新索引,从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引,你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后,索引会自动被建立。在Manage Tables窗口中的字段名旁边,会出现一把钥匙。 " I\)Ty
    你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外,主索引强制一个字段成为唯一值字段。 *]!>Vizr.
    要建立没有这些限制的索引,你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes,打开Manage Indexes 窗口。在Manage Indexes 窗口中,你可以通过下拉框选择表和特定的索引。要建立一个新索引,从Index下拉框中选择New Index.,然后就可以选择要对之建立索引的字段。单击按钮Add,把字段加人到索引中。 A )wkcW@
    cjP<p~9|3
    ~j!d}%
    你可以为你的索引选择许多不同的选项。例如,你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后,单击按钮Build,建立该索引。 } :&Z&Y%WV
    展开全文
  • Oracle 建立索引SQL优化

    千次阅读 2018-02-11 09:53:22
    一、建立数据库索引: ... 2、对于两表连接的字段,应该建立索引。如果经常某表的一个字段进行Order By 则也经过进行索引。  3、不应该小表上建设索引。 优缺点:  1、索引主要进行提高数据的查...

     

    一、建立数据库索引:

    索引有单列索引和复合索引之说。

    建设原则:

     1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。

     2、对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

     3、不应该在小表上建设索引。

    优缺点:
     1、索引主要进行提高数据的查询速度。 当进行DML时,会更新索引。因此索引越多,则DML越慢,其需要维护索引。 因此在创建索引及DML需要权衡。

       2、当一个表的索引达到4个以上时,ORACLE的性能可能还是改善不了,因为OLTP系统每表超过5个索引即会降低性能,而且在一个sql 中, Oracle 从不能使用超过 5个索引

       3、索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片. (建立索引影响了删除和更新操作)

    创建索引:
     单一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);

     复合索引:Create Index <Index-Name> On emp(deptno,job); —>在emp表的deptno、job列建立索引。

      select * from emp where deptno=66 and job='sals' ->走索引。

      select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引

      select * from emp where deptno=66 ->走索引。

      select * from emp where job='sals' ->进行全表扫描、不走索引。

      如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。

          同时在Oracle里用PL/SQL的F5可以对整个SQL查询来判断没加索引前和加完索引后的用时。

     

    索引失效的情况:
     ① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
     ② 索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC'  或 SELECT Col FROM tbl WHERE name LIKE '%ABC%'  而 SELECT Col FROM tbl WHERE name LIKE 'ABC%' 会使用索引。

     ③ 索引列上不能进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效,应该改成SELECT Col FROM tbl WHERE col > 10 * 10

     ④ 索引列上不要使用NOT ( != 、 <> )如:SELECT Col FROM tbl WHERE col ! = 10 应该改成:SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

     

    二、关于SQL 性能优化

     

    (一)ORACLE 性能优化主要方法
    ⑴硬件升级(CPU、内存、硬盘):

         CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志,并且ORACLE是一个提供并行能力的数据库系统,如果运行队列数目超过了CPU处理的数目,性能就会下降;
         内存:衡量机器性能的另外一个指标就是内存的多少了,在ORACLE中内存和我们在建数据库中的交换区进行数据的交换,读数据时,磁盘I/O必须等待物理I/O操作完成,在出现ORACLE的内存瓶颈时,我们第一个要考虑的是增加内存,由于I/O的响应时间是影响ORACLE性能的主要参数;
         网络条件:NET*SQL负责数据在网络上的来往,大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响,还有交换机、集线器等等网络设备的性能对网络的影响很明显,建议在任何网络中不要试图用3个集线器来将网段互联。
     

    ⑵版本及参数设置


    ⑶应用程序设计(框架、调用方式---源代码)

         程序设计中的一个著名定律是20%的代码用去了80%的时间;
         两种方式优化:源代码的优化和SQL语句的优化。源代码的优化在时间成本和风险上代价很高;另一方面,源代码的优化对数据库系统性能的提升收效有限。
         DBMS处理查询计划的过程是这样的:在做完查询语句的词法、语法检查之后,将语句提交给DBMS的查询优化器,优化器做完代数优化和存取路径的优化之后,由预编译模块对语句进行处理并生成查询规划,然后在合适的时间提交给系统处理执行,最后将执行结果返回给用户。 


    ⑷SQL 语句优化:

     

    当Oracle数据库拿到SQL语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。
    也就是说,数据库是执行的查询计划,而不是Sql语句。
    查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。
    其中基于规则的查询优化器在10g版本中消失。

    对于规则查询,其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。


    先执行From ->Where ->Group By->Order By,所以尽量避免全表扫。

    执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。  

    对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾,而对于多表之间的连接,则写在之前。因为这样进行连接时,可以去掉大多不重复的项。  

    ④SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.但是在count(*)和count(1)的执行中不需要遵守上述内容,速度经过我测试是相同的。

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

      union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,并且相应列的数据类型也应该相当的。union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL.

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

      高效:
      SELECT ID , NAME
      FROM LOCATION
      WHERE ID = 1
      UNION
      SELECT ID , NAME
      FROM LOCATION
      WHERE NAME = “SUQI356”

      低效:
      SELECT ID , NAME 
      FROM LOCATION
      WHERE ID = 1 OR NAME = “SUQI356”
      如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

    ⑥用EXISTS替代IN、用NOT EXISTS替代NOT IN和用(+)比用NOT IN更有效率
    在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 
    在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 
    为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

    例子:

    高效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

    低效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

    ⑦ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表 。

    ⑧避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、总计等操作; 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

      ⑨尽可能使用varchar代替char,因为变长字段存储空间小,在一个相对较小的字段内搜索效率要高。

      ⑩使用临时表来存储

    展开全文
  • SQL索引建立规则与优化

    千次阅读 2013-12-18 12:02:13
    SQL索引建立规则与优化 一.了解索引 实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非...

    SQL索引建立规则与优化

    一.了解索引

    实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

      其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查字,就会很自然地翻开字典的前几页,因为的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查字,那您也会将您的字典翻到最后部分,因为的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。

     我们把这种正文内容本身就是一种按照一定规则排列的目录称为聚集索引

      如果您认识某个字,您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据偏旁部首查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合部首目录检字表而查到的字的排序并不是真正的正文的排序方法,比如您查字,我们可以看到在查部首之后的检字表中的页码是672页,检字表中的上面是字,但页码却是63页,的下面是字,页面是390页。很显然,这些字并不是真正的分别位于字的上下方,现在您看到的连续的驰、张、弩三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。

     我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为非聚集索引

      通过以上例子,我们可以理解到什么是聚集索引非聚集索引另外,每个表只能有一个聚集索引,因为正文只能按照一种方法进行排序。

    二.建立索引的规则

    1、表的主键、外键必须有索引;

    2、数据量超过300的表应该有索引;

    3、经常与其他表进行连接的表,在连接字段上应该建立索引;

    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;

    5、索引应该建在选择性高的字段上;

    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:

       A、正确选择复合索引中的主列字段,一般是选择性较好的字段;

       B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

       C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;

       D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;

       E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

    8、频繁进行数据操作的表,不要建立太多的索引;

    9、删除无用的索引,避免对执行计划造成负面影响;

      以上是一些普遍的建立索引时的判断依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。

     

    类别

    分组排序

    某范围内

    的数据

    一个或极少不同值

    小数目的

    不同值

    大数目的

    不同值

    频繁更新

    外键列

    主键列

    频繁修

    索引列

    聚集


    ×

    ×

    ×

    ×

    非聚集

    ×(聚集索引被占,则)

    ×

    ×(聚集索引被占则√)

    √(text等类 ×)

     

    三.索引优化规则

       具体优化规则 以通俗的十八掌来阐述。

    第一掌 避免对列的操作


    任何对列的操作都可能导致全表扫描,这里所谓的操作包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等式的右边,甚至去掉函数(否则不能使用索引)。


    1:下列SQL条件语句中的列都建有恰当的索引,但30万行数据情况下执行速度却非常慢:


    select * from record where substring(CardNo,1,4)='5378'(13)


    select * from record where amount/30< 100011秒)


    select * from record where to_char(ActionTime,'yyyymmdd')='19991201'10秒)


    由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的,因此它不得不进行表扫描,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表扫描,因此将SQL重写如下:


    select * from record where CardNo like '5378%'< 1秒)


    select * from record where amount < 1000*30< 1秒)


    select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')< 1秒)


    差别是很明显的!


    第二掌 避免不必要的类型转换


    需要注意的是,尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致全表扫描。


    2:表tab1中的列col1是字符型(char),则以下语句存在类型转换:


    select col1,col2 from tab1 where col1>10


    应该写为: select col1,col2 from tab1 where col1>'10'


    第三掌 增加查询的范围限制


    增加查询的范围限制,避免全范围的搜索。



    第四掌 尽量去掉"IN""OR"


    含有"IN""OR"Where子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。


    4select count(*) from stuff where id_no in('0','1')23秒)


    可以考虑将or子句分开:


    select count(*) from stuff where id_no='0'


    select count(*) from stuff where id_no='1'


    然后再做一个简单的加法,与原来的SQL语句相比,查询速度更快。

    第五掌 尽量去掉 "<>"

    尽量去掉 "<>",避免全表扫描,如果数据是枚举值,且取值范围固定,则修改为"OR"方式。

    5

    UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;

    以 上语句由于其中包含了"<>",执行计划中用了全表扫描(TABLE ACCESSFULL),没有用到state字段上的索引。实际应用中,由于业务逻辑的限制,字段state为枚举值,只能等于012,而且,值等于=12的很少,因此可以去掉"<>",利用索引来提高效率。

    修改为:UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第4种方法。

    第六掌 去掉Where子句中的IS NULLIS NOT NULL

    Where字句中的IS NULLIS NOT NULL将不会使用索引而是进行全表搜索,因此需要通过改变查询方式,分情况讨论等方法,去掉Where子句中的IS NULLIS NOT NULL

    第七掌 索引提高数据分布不均匀时查询效率

    索引的选择性低,但数据的值分布差异很大时,仍然可以利用索引提高效率。A、数据分布不均匀的特殊情况下,选择性不高的索引也要创建。

    表 ServiceInfo中数据量很大,假设有一百万行,其中有一个字段DisposalCourseFlag,取值范围为枚举值:[01234567]。按照前面说的索引建立的规则,选择性不高的字段不应该建立索引,该字段只有8种取值,索引值的重复率很高,索引选择性明显很低,因此不建索引。然而,由于该字段上数据值的分布情况非常特殊,具体如下表:


    取值范围                  1~5        6        7

    占总数据量的百分比        1%        98%        1%


    而且,常用的查询中,查询DisposalCourseFlag<6 的情况既多又频繁,毫无疑问,如果能够建立索引,并且被应用,那么将大大提高这种情况的查询效率。因此,我们需要在该字段上建立索引。

    第八掌 利用HINT强制指定索引(ORACLE 使用)

    ORACLE优化器无法用上合理索引的情况下,利用HINT强制指定索引。

    继续上面7的例子,ORACLE缺省认定,表中列的值是在所有数据行中均匀分布的,也就是说,在一百万数据量下,每种DisposalCourseFlag值各有12.5万数据行与之对应。假设SQL搜索条件DisposalCourseFlag=2,利用DisposalCourseFlag列上的索引进行数据搜索效率,往往不比全表扫描的高,ORACLE因此对索引视而不见,从而在查询路径的选择中,用其他字段上的索引甚至全表扫描。根据我们上面的分析,数据值的分布很特殊,严重的不均匀。为了利用索引提高效率,此时,一方面可以单独对该字段或该表用analyze语句进行分析,对该列搜集足够的统计数据,使ORACLE在查询选择性较高的值时能用上索引;另一方面,可以利用HINT提示,在SELECT关键字后面,加上“/*+INDEX(表名称,索引名称)*/”的方式,强制ORACLE优化器用上该索引。

    比如: select * from serviceinfo where DisposalCourseFlag=1 ;

    上面的语句,实际执行中ORACLE用了全表扫描,加上蓝色提示部分后,用到索引查询。如下:

    select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *

    from serviceinfo where DisposalCourseFlag=1;

    请注意,这种方法会加大代码维护的难度,而且该字段上索引的名称被改变之后,必须要同步所有指定索引的HINT代码,否则HINT提示将被ORACLE忽略掉。

    第九掌 屏蔽无用索引

    继续上面8的例子,由于实际查询中,还有涉及到DisposalCourseFlag=6的查询,而此时如果用上该字段上的索引,将是非常不明智的,效率也极低。因此这种情况下,我们需要用特殊的方法屏蔽该索引,以便ORACLE选择其他字段上的索引。比如,如果字段为数值型的就在表达式的字段名后,添加“+ 0”,为字符型的就并上空串:“||""”

    如: select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 

    不过,不要把该用的索引屏蔽掉了,否则同样会产生低效率的全表扫描。
    第十掌 分解复杂查询,用常量代替变量

    对于复杂的Where条件组合,Where中含有多个带索引的字段,考虑用IF语句分情况进行讨论;同时,去掉不必要的外来参数条件,减低复杂度,以便在不同情况下用不同字段上的索引。

    继续上面9的例子,对于包含

    Where (DisposalCourseFlag < v_DisPosalCourseFlag) or(v_DisPosalCourseFlag is null) and....的查询,(这里v_DisPosalCourseFlag为一个输入变量,取值范围可能为[NULL01234567]),可以考虑分情况用IF语句进行讨论,类似:

    IF v_DisPosalCourseFlag =1 THEN

    Where DisposalCourseFlag = 1 and ....

    ELSIF v_DisPosalCourseFlag =2 THEN

    Where DisposalCourseFlag = 2 and ....

    。。。。。。

    第十一掌 like子句尽量前端匹配

    因为like参数使用的非常频繁,因此如果能够对like子句使用索引,将很高的提高查询的效率。

    6select * from city where name like ‘%S%’

    以上查询的执行计划用了全表扫描(TABLE ACCESS FULL),如果能够修改为:

    select * from city where name like ‘S%’

    那 么查询的执行计划将会变成(INDEX RANGE SCAN),成功的利用了name字段的索引。这意味着OracleSQL优化器会识别出用于索引的like子句,只要该查询的匹配端是具体值。因此我们在做like查询时,应该尽量使查询的匹配端是具体值,即使用like ‘S%’

    第十二掌 用Case语句合并多重扫描

    我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询:

    81select count(*) from emp where sal<1000;

         2select count(*) from emp where sal between 1000 and 5000;

         3select count(*) from emp where sal>5000;

    这样我们需要进行三次全表查询,但是如果我们使用case语句:

    select

    count (case when sal <1000 then 1 else null end) count_poor,

    count (case when sal between 1000 and 5000 then 1 else null end) count_blue,

    count (case when sal >5000 then 1 else null end) count_poor

    from emp;

     

    这样查询的结果一样,但是执行计划只进行了一次全表查询。

    第十三掌 使用nls_date_format

    9

    select * from record where to_char(ActionTime,'mm')='12'

    这个查询的执行计划将是全表查询,如果我们改变nls_date_format

    SQL>alert session set nls_date_formate=’MM’;

    现在重新修改上面的查询:

    select * from record where ActionTime='12'

    这样就能使用actiontime上的索引了,它的执行计划将是(INDEX RANGE SCAN)。
    第十四掌 使用基于函数的索引

    前面谈到任何对列的操作都可能导致全表扫描,例如:

    select * from emp where substr(ename,1,2)=’SM’;

    但是这种查询在客服系统又经常使用,我们可以创建一个带有substr函数的基于函数的索引,

    create index emp_ename_substr on eemp ( substr(ename,1,2) );

    这样在执行上面的查询语句时,这个基于函数的索引将排上用场,执行计划将是(INDEX RANGE SCAN)。

    第十五掌 基于函数的索引要求等式匹配

    上面的例子中,我们创建了基于函数的索引,但是如果执行下面的查询:

    select * from emp where substr(ename,1,1)=’S’

    得 到的执行计划将还是(TABLE ACCESSFULL),因为只有当数据列能够等式匹配时,基于函数的索引才能生效,这样对于这种索引的计划和维护的要求都很高。请注意,向表中添加索引是非常危险的操作,因为这将导致许多查询执行计划的变更。然而,如果我们使用基于函数的索引就不会产生这样的问题,因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

    第十六掌 使用分区索引

    在用分析命令对分区索引进行分析时,每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。

    例如,假设你已经定义了一个分区索引,并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区,而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引,所以提高了查询的速度。

    第十七掌 使用位图索引

    位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度,因为在位图索引中进行的检索是在RAM中完成的,而且也总是比传统的B树索引的速度要快。对于那些少于1000个唯一数据值的数据列建立位图索引,可以使执行效率更快。

    第十八掌 决定使用全表扫描还是使用索引

    和所有的秘笈一样,最后一招都会又回到起点,最后我们来讨论一下是否需要建立索引,也许进行全表扫描更快。在大多数情况下,全表扫描可能会导致更多的物理磁盘输入输出,但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序,那么一个要返回记录数小于10%的查询可能会读取表中大部分的数据块,这样使用索引会使查询效率提高很多。但是如果表非常有顺序,那么如果查询的记录数大于40%时,可能使用全表扫描更快。因此,有一个索引范围扫描的总体原则是:

    1)对于原始排序的表 仅读取少于表记录数40%的查询应该使用索引范围扫描。反之,读取记录数目多于表记录数的40%的查询应该使用全表扫描。

    2)对于未排序的表    仅读取少于表记录数7%的查询应该使用索引范围扫描。反之,读取记录数目多于表记录数的7%的查询应该使用全表扫描。

    总结

    以上的招式,是完全可以相互结合同时运用的。而且各种方法之间相互影响,紧密联系。这种联系既存在一致性,也可能带来冲突,当冲突发生时,需要根据实际情况进行选择,没有固定的模式。最后决定SQL优化功力的因素就是对ORACLE内功的掌握程度了。

    另外,值得注意的是:随着时间的推移和数据的累计与变化,ORACLESQL语句的执行计划也会改变,比如:基于代价的优化方法,随着数据量的增大,优化器可能错误的不选择索引而采用全表扫描。这种情况可能是因为统计信息已经过时,在数据量变化很大后没有及时分析表;但如果对表进行分析之后,仍然没有用上合理的索引,那么就有必要对SQL语句用HINT提示,强制用合理的索引。但这种HINT提示也不能滥用,因为这种方法过于复杂,缺乏通用性和应变能力,同时也增加了维护上的代价;相对来说,基于函数右移、去掉“IN OR <> IS NOT NULL”、分解复杂的SQL语句等等方法,却是放之四海皆准的,可以放心大胆的使用。

    同时,优化也不是一劳永逸的,必须随着情况的改变进行相应的调整。当数据库设计发生变化,包括更改表结构:字段和索引的增加、删除或改名等;业务逻辑发生变化:如查询方式、取值范围发生改变等等。在这种情况下,也必须对原有的优化进行调整,以适应效率上的需求。

     

     

     

     

     

     

    展开全文
  • SQL Server-索引管理

    千次阅读 2013-05-01 12:14:56
    (1)在SQL Server Management Studio的“对象资源管理器”面板,使用与创建索引同样的方法,打开索引-属性命令,即可看到该索引对应的信息。   (2)使用系统存储过程sp_helpindex查看指定表的索引信息 use test...
  • SQL索引

    千次阅读 2016-04-06 11:47:53
    SQL索引在数据库优化占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引索引...
  • SQL索引建立原则和使用

    万次阅读 2019-08-02 13:31:54
    之前网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,发现遗漏了些东西,这里自己整理一下这方面的内容。 1 前言 SQL索引有两种,聚集索引和非聚集索引 聚集索引存储记录是物理上...
  • SQL Server-索引介绍

    千次阅读 2013-05-01 10:54:31
    什么是索引 拿汉语字典的目录页(索引)打比方:正如汉语字典的汉字按页存放一样,SQL Server的数据记录也是按页存放...同理,SQL Server允许用户创建索引,指定按某列预先排序,从而大大提高查询速度。 •
  • 在sqlserver如何重建索引

    万次阅读 2010-07-06 20:54:00
    大多数SQL Server表需要索引来提高数据的访问速度,如果没有索引SQL Server 要进行表格扫描读取表的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引,簇索引通过重排表的数据来提高数据的...
  • 数据库存优化设计往往会提到索引,这编文章就来详细的说明一下 SQL SERVER 下面的建立索引的技巧和需要注意的一些地方,让您可以更直观的了解数据库的结构。 往往数据量比较小,查询量也不是很大的时候我们...
  • SQLServer 重建索引

    千次阅读 2019-01-03 14:22:04
    做维护项目的时,特别是数据库转移时,经常会遇到索引维护的问题,通过语句,我们就可以判断某个表的索引是否需要重建。 分析表的索引: 分析表的索引建立情况:DBCC showcontig('Table') DBCC SHOWCONTIG 正在...
  • Sql Server的修复命令

    千次阅读 2012-03-07 10:22:29
     重启服务器后,没有进行任何操作的情况下,在SQL查询分析器执行以下SQL进行数据库的修复,修复数据库存在的一致性错误与分配错误。   use master declare @databasename varchar
  • SQL索引详解

    万次阅读 多人点赞 2018-03-08 00:25:13
    SQL索引详解转自:http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.htmlSQL索引在数据库优化占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,这里将带你一步步揭开...
  • SQL 优化之索引

    千次阅读 2016-07-13 16:04:14
     SQL索引在数据库优化占有一个非常大的比例,一个好的索引的设计,可以让你的效率提高几十甚至几百倍,这里将带你一步步揭开他的神秘面纱。  1.1什么是索引?  SQL索引有两种,聚集索引和非聚集索引索引...
  • php linux sphinx 命令建立索引

    千次阅读 2010-06-05 17:52:00
    建立server searchd --install --config d:/csft/bin/csft.conf searchd --delete searchd -h 必须手动启动服务   建立索引 ...用在建立索引的时候后面加上: indexer
  • SQL Server 2008索引使用技巧

    千次阅读 2016-07-06 17:35:06
    微软MVP及畅销书《Hitchhiker's Guide SQL Server》的作者Bill Vaughn简要给出了SQL Server 2008的索引使用技巧。该主题基于Kimberly Tripp和Paul Randall(这两位是SQL Server高可用性和性能方面的专家)的建议。 ...
  • SQL数据库常用命令

    千次阅读 2018-12-04 23:56:09
    索引建立、修改、删除 create unique index <索引名> on <表名>(<列名> asc,<列名> desc,...) alter index <索引名> rename to <新的索引名> drop index <索引名> 数据查询 select [all|distinct] <目标...
  • SQL优化一键命中索引

    千次阅读 2018-09-21 15:47:14
    项目开发中sql大家经常用到,表的索引也是,这些sql的运行性能是怎样的你知道么?中索引啦没?命中哪个索引?索引中有哪些是无效索引? 哪些会走索引,哪些必定不会走索引. Mysql是通过explain神奇命令来分析低效sql的...
  • sql之强制索引

    千次阅读 2018-05-19 16:43:00
    2、第一时间想到是建立联合索引,但是数据库存在多条索引的情况下,索引的执行是全部执行。 3、所以这里要按照特定的索引执行,就必须使用强制索引。 4、强制索引 FORCE INDEXSELECT * FROM TABLE1 FORCE INDEX ...
  • lol_dba是一小包rake任务,可扫描您的应用程序模型并显示可能应建立索引的列的列表。 此外,它可以生成.sql迁移脚本。 快速示例 要以最简单的方式使用lol_dba,您必须做两件事: gem install lol_dba 然后运行以下...
  • SQLServer单列索引和多列索引的不同

    千次阅读 2018-01-02 16:21:39
    索引是什么:数据库索引类似于一本书的目录,一本书使用目录可以快速找到你想要的信息,而不需要读完全书。数据库,数据库程序使用索引可以重啊到表的数据,而不必扫描整个表。书的目录是一个字词...
  • 使用SQL创建唯一索引

    千次阅读 2019-04-27 08:10:00
    使用sql语句创建唯一索引,格式如下: create unique index 索引名 on 表名(列名1,列名2……) 示例;表GoodsMade_Labour的SID列上创建唯一索引IX_GoodsMade_Labour,代码如下: create unique index IX_...
  • 总结SQL索引

    千次阅读 2009-03-07 23:29:00
    索引 数据库的索引与书籍的索引类似。一本书,利用索引可以快速查找所需信息,无须阅读整本书。数据库,索引使...可以为表的单个列建立索引,也可以为一组列建立索引;索引采用 B 树结构。索引包含一
  • SQL常用命令

    万次阅读 2018-07-22 17:35:15
    SQL常用命令使用方法: (1) 数据记录筛选: sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 ...
  • SQL Server建库建表命令

    千次阅读 多人点赞 2020-03-16 17:12:00
    student表定义如上图,代码片段如下: use SCHOOL --数据库SCHOOL创建一个表 CREATE TABLE Students ( Stu_id Varchar(10) constraint pk_stu primary key NOT NULL, Stu_name Varchar(10) constraint uk_name ...
  • Sql server索引优化

    千次阅读 2017-04-01 15:15:27
    Sql server索引优化
  • SQL索引优化

    千次阅读 2018-08-04 14:58:23
    前言 规模比较大的局点,往往因为一个小的SQL语句不够优化,导致数据库性能急剧下降,小型机idle所剩无几,应用服务器断连、超时,严重影响业务的正常运行...建立索引常用的规则如下: 1、表的主键、外键必须有索...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 109,398
精华内容 43,759
关键字:

在sql中建立索引的命令是