数据库建立索引怎么利用索引查询？

1.合理使用索引\x0d\x0a索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。\x0d\x0a索引的使用要恰到好处，其使用原则如下：\x0d\x0a在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。\x0d\x0a在频繁进行排序或分组（即进行group by或order by *** 作）的列上建立索引。\x0d\x0a在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。\x0d\x0a如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。\x0d\x0a使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁 *** 作而 使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量 数据后，删除并重建索引可以提高查询速度。\x0d\x0a(1)在下面两条select语句中:\x0d\x0aSELECT * FROM table1 WHERE field1=0\x0d\x0aSELECT * FROM table1 WHERE field1>=0 AND field1=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。\x0d\x0a第一个原则：在where子句中应把最具限制性的条件放在最前面。\x0d\x0a(2)在下面的select语句中:\x0d\x0aSELECT * FROM tab WHERE a=? AND b=? AND c=?\x0d\x0a若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。\x0d\x0a第二个原则：where子句中字段的顺序应和索引中字段顺序一致。\x0d\x0a—————————————————————————— \x0d\x0a以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。 \x0d\x0a—————————————————————————— \x0d\x0a(3) SELECT field3,field4 FROM tb WHERE field1='sdf' 快 \x0d\x0aSELECT * FROM tb WHERE field1='sdf' 慢[/cci]\x0d\x0a因为后者在索引扫描后要多一步ROWID表访问。\x0d\x0a(4) SELECT field3,field4 FROM tb WHERE field1>='sdf' 快 \x0d\x0aSELECT field3,field4 FROM tb WHERE field1>'sdf' 慢\x0d\x0a因为前者可以迅速定位索引。\x0d\x0a(5) SELECT field3,field4 FROM tb WHERE field2 LIKE 'R%' 快 \x0d\x0aSELECT field3,field4 FROM tb WHERE field2 LIKE '%R' 慢，\x0d\x0a因为后者不使用索引。\x0d\x0a(6) 使用函数如： \x0d\x0aSELECT field3,field4 FROM tb WHERE upper(field2)='RMN'不使用索引。\x0d\x0a如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。\x0d\x0a(7) 空值不在索引中存储，所以 \x0d\x0aSELECT field3,field4 FROM tb WHERE field2 IS[NOT] NULL不使用索引。\x0d\x0a(8) 不等式如 \x0d\x0aSELECT field3,field4 FROM tb WHERE field2!='TOM'不使用索引。 \x0d\x0a相似地， \x0d\x0aSELECT field3,field4 FROM tb WHERE field2 NOT IN('M','P')不使用索引。\x0d\x0a(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。\x0d\x0a(10) MAX，MIN等函数，使用索引。 \x0d\x0aSELECT max(field2) FROM tb 所以，如果需要对字段取max，min，sum等，应该加索引。\x0d\x0a一次只使用一个聚集函数，如： \x0d\x0aSELECT “min”=min(field1), “max”=max(field1) FROM tb \x0d\x0a不如：SELECT “min”=(SELECT min(field1) FROM tb) , “max”=(SELECT max(field1) FROM tb)\x0d\x0a(11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的 *** 作比没有索引更慢。\x0d\x0a(12) 索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。\x0d\x0a(13) 对于多列索引，ORDER BY的顺序必须和索引的字段顺序一致。\x0d\x0a(14) 在sybase中，如果ORDER BY的字段组成一个簇索引，那么无须做ORDER BY。记录的排列顺序是与簇索引一致的。\x0d\x0a(15) 多表联结（具体查询方案需要通过测试得到） \x0d\x0awhere子句中限定条件尽量使用相关联的字段，且尽量把相关联的字段放在前面。 \x0d\x0aSELECT a.field1,b.field2 FROM a,b WHERE a.field3=b.field3\x0d\x0afield3上没有索引的情况下: \x0d\x0a对a作全表扫描，结果排序 \x0d\x0a对b作全表扫描，结果排序 \x0d\x0a结果合并。 \x0d\x0a对于很小的表或巨大的表比较合适。\x0d\x0afield3上有索引 \x0d\x0a按照表联结的次序，b为驱动表，a为被驱动表 \x0d\x0a对b作全表扫描 \x0d\x0a对a作索引范围扫描 \x0d\x0a如果匹配，通过a的rowid访问\x0d\x0a(16) 避免一对多的join。如： \x0d\x0aSELECT tb1.field3,tb1.field4,tb2.field2 FROM tb1,tb2 WHERE tb1.field2=tb2.field2 AND tb1.field2=‘BU1032’ AND tb2.field2= ‘aaa’ \x0d\x0a不如： \x0d\x0adeclare @a varchar(80) \x0d\x0aSELECT @a=field2 FROM tb2 WHERE field2=‘aaa’ \x0d\x0aSELECT tb1.field3,tb1.field4,@a FROM tb1 WHERE field2= ‘aaa’\x0d\x0a(16) 子查询 \x0d\x0a用exists/not exists代替in/not in *** 作 \x0d\x0a比较： \x0d\x0aSELECT a.field1 FROM a WHERE a.field2 IN(SELECT b.field1 FROM b WHERE b.field2=100) \x0d\x0aSELECT a.field1 FROM a WHERE EXISTS( SELECT 1 FROM b WHERE a.field2=b.field1 AND b.field2=100) \x0d\x0aSELECT field1 FROM a WHERE field1 NOT IN( SELECT field2 FROM b) \x0d\x0aSELECT field1 FROM a WHERE NOT EXISTS( SELECT 1 FROM b WHERE b.field2=a.field1)\x0d\x0a(17) 主、外键主要用于数据约束，sybase中创建主键时会自动创建索引，外键与索引无关，提高性能必须再建索引。\x0d\x0a(18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。\x0d\x0a(19) 使用count(*)而不要使用count(column_name)，避免使用count(DISTINCT column_name)。\x0d\x0a(20) 等号右边尽量不要使用字段名，如： \x0d\x0aSELECT * FROM tb WHERE field1 = field3\x0d\x0a(21) 避免使用or条件，因为or不使用索引。\x0d\x0a2.避免使用order by和group by字句。\x0d\x0a因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用，可用视图、人工生成临时表的方法来代替。 \x0d\x0a如果必须使用，先检查memory、tempdb的大小。 \x0d\x0a测试证明，特别要避免一个查询里既使用join又使用group by，速度会非常慢！\x0d\x0a3.尽量少用子查询，特别是相关子查询。因为这样会导致效率下降。\x0d\x0a一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。\x0d\x0a4．消除对大型表行数据的顺序存取\x0d\x0a在 嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。 \x0d\x0a比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询 10亿行数据。 \x0d\x0a避免这种情况的主要方法就是对连接的列进行索引。 \x0d\x0a例如，两个表：学生表（学号、姓名、年龄??）和选课表（学号、课程号、成绩）。如果两个 表要做连接，就要在“学号”这个连接字段上建立索引。 \x0d\x0a还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。 \x0d\x0a下面的查询将强迫对orders表执行顺序 *** 作： \x0d\x0aSELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 \x0d\x0a虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： \x0d\x0aSELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 \x0d\x0aUNION \x0d\x0aSELECT ＊ FROM orders WHERE order_num=1008 \x0d\x0a这样就能利用索引路径处理查询。\x0d\x0a5．避免困难的正规表达式\x0d\x0aMATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _” \x0d\x0a即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。 \x0d\x0a另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。\x0d\x0a6．使用临时表加速查询\x0d\x0a把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序 *** 作，而且在其他方面还能简化优化器的工作。例如： \x0d\x0aSELECT cust.name，rcvbles.balance，??other COLUMNS \x0d\x0aFROM cust，rcvbles \x0d\x0aWHERE cust.customer_id = rcvlbes.customer_id \x0d\x0aAND rcvblls.balance>0 \x0d\x0aAND cust.postcode>“98000” \x0d\x0aORDER BY cust.name \x0d\x0a如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序： \x0d\x0aSELECT cust.name，rcvbles.balance，??other COLUMNS \x0d\x0aFROM cust，rcvbles \x0d\x0aWHERE cust.customer_id = rcvlbes.customer_id \x0d\x0aAND rcvblls.balance>0 \x0d\x0aORDER BY cust.name \x0d\x0aINTO TEMP cust_with_balance \x0d\x0a然后以下面的方式在临时表中查询： \x0d\x0aSELECT ＊ FROM cust_with_balance \x0d\x0aWHERE postcode>“98000” \x0d\x0a临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。 \x0d\x0a注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。\x0d\x0a7．用排序来取代非顺序存取\x0d\x0a非顺序磁盘存取是最慢的 *** 作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。

oracle对于数据库中的表信息，存储在系统表中。查询已创建好的表索引，可通过相应的sql语句到相应的表中进行快捷的查询：

1.

根据表名，查询一张表的索引

select

*

from

user_indexes

where

table_name=upper('表名')

2.

根据索引号，查询表索引字段

select

*

from

user_ind_columns

where

index_name=('索引名')

3.根据索引名，查询创建索引的语句

select

dbms_metadata.get_ddl('index','索引名',

['用户名'])

from

dual

--['用户名']可省，默认为登录用户

ps：dbms_metadata.get_ddl还可以得到建表语句，如：

select

dbms_metadata.get_ddl('table','表名',

['用户名'])

from

dual

//取单个表的建表语句，['用户名']可不输入，默认为登录用户

select

dbms_metadata.get_ddl('table',u.table_name)

from

user_tables

u

//取用户下所有表的建表语句

当然，也可以用pl/sql

developer工具来查看相关的表的各种信息。

数据库引入了索引

用户对数据库最频繁的 *** 作是进行数据查询 一般情况下 数据库在进行查询 *** 作时需要对整个表进行数据搜索 当表中的数据很多时 搜索数据就需要很长的时间 这就造成了服务器的资源浪费 为了提高检索数据的能力 数据库引入了索引机制

有关 索引 的比喻

从某种程度上 可以把数据库看作一本书 把索引看作书的目录 通过目录查找书中的信息 显然较没有目录的书方便 快捷

数据库索引实际是什么?(两部分组成)

索引是一个单独的 物理的数据库结构 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

索引在表中的角色

一个表的存储是由两部分组成的 一部分用来存放表的数据页面 另一部分存放索引页面 索引就存放在索引页面上

索引高效原理

通常 索引页面相对于数据页面来说小得多 当进行数据检索时 系统先搜索索引页面 从中找到所需数据的指针 再直接通过指针从数据页面中读取数据

索引的分类

在SQL Server 的数据库中按存储结构的不同将索引分为两类 簇索引(Clustered Index)和非簇索引(Nonclustered Index)

( )簇索引对表的物理数据页中的数据按列进行排序 然后再重新存储到磁盘上 即簇索引与数据是混为一体 的它的叶节点中存储的是实际的数据 由于簇索引对表中的数据一一进行了排序 因此用簇索引查找数据很快 但由于簇索引将表的所有数据完全重新排列了 它所需要的空间也就特别大 大概相当于表中数据所占空间的 % 表的数据行只能以一种排序方式存储在磁盘上 所以一个表只能有一个簇索引

( )非簇索引具有与表的数据完全分离的结构 使用非簇索引不用将物理数据页中的数据按列排序 非簇索引的叶节点中存储了组成非簇索引的关键字的值和行定位器 行定位器的结构和存储内容取决于数据的存储方式 如果数据是以簇索引方式存储的 则行定位器中存储的是簇索引的索引键如果数据不是以簇索引方式存储的 这种方式又称为堆存储方式(Heap Structure) 则行定位器存储的是指向数据行的指针 非簇索引将行定位器按关键字的值用一定的方式排序 这个顺序与表的行在数据页中的排序是不匹配的 由于非簇索引使用索引页存储因此它比簇索引需要更多的存储空间且检索效率较低但一个表只能建一个簇索引 当用户需要建立多个索引时就需要使用非簇索引了

小结 Clustered Index 是与物理数据混在一起并对物理数据进重排 就像使用拼音查字典Unclustered Index 是与物理数据完全分离的 利用额外空间对关键字进行重排 就像使用部首查字典

数据库索引应用

一 索引的概念

索引就是加快检索表中数据的方法 数据库的索引类似于书籍的索引 在书籍中 索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息 在数据库中 索引也允许数据库程序迅速地找到表中的数据 而不必扫描整个数据库

二 索引的特点

索引可以加快数据库的检索速度

索引降低了数据库插入 修改 删除等维护任务的速度

索引创建在表上 不能创建在视图上

索引既可以直接创建 也可以间接创建

可以在优化隐藏中 使用索引

使用查询处理器执行SQL语句 在一个表上 一次只能使用一个索引

其他

三 索引的优点

创建唯一性索引 保证数据库表中每一行数据的唯一性

大大加快数据的检索速度 这也是创建索引的最主要的原因

加速表和表之间的连接 特别是在实现数据的参考完整性方面特别有意义

在使用分组和排序子句进行数据检索时 同样可以显著减少查询中分组和排序的时间

通过使用索引 可以在查询的过程中使用优化隐藏器 提高系统的性能

四 索引的缺点

创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加

索引需要占物理空间 除了数据表占数据空间之外 每一个索引还要占一定的物理空间 如果要建立聚簇索引 那么需要的空间就会更大

当对表中的数据进行增加 删除和修改的时候 索引也要动态的维护 降低了数据的维护速度

lishixinzhi/Article/program/MySQL/201311/29604

---