数据库索引结构
Ⅰ 什么是索引索引类型有几种，各有什么特点
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引有三种类型：
唯一索引：唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。
主键索引：数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。
聚焦索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。
(1)数据库索引结构扩展阅读：
通过建立索引可以极大地提高在数据库中获取所需信息的速度，同时还能提高服务器处理相关搜索请求的效率，从这个方面来看它具有以下优点：
在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。
能提高数据的搜索及检索速度，符合数据库建立的初衷。
能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。
Ⅱ 数据库基础：讲解MySQL索引的概念及数据库索引的应用[1]
数据库引入了索引
用户对数据库最频繁的操作是进行数据查询 一般情况下 数据库在进行查询操作时需要对整个表进行数据搜索 当表中的数据很多时 搜索数据就需要很长的时间 这就造成了服务器的资源浪费 为了提高检索数据的能力 数据库引入了索引机制
有关 索引 的比喻
从某种程度上 可以把数据库看作一本书 把索引看作书的目录 通过目录查找书中的信息 显然较没有目录的书方便 快捷
数据库索引实际是什么告渗?(两部分组成)
索引是一个单独的 物理的数据库结构 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
索引在表中的角色
一个表的存储是由两部分组成的 一部分用来存放表的数据页面 另一部分存放索引页面 索引就存放在索引页面上
索引高效原理
通常 索引页面相对于数据页面来说小得多 当进行数据检索时 系统先搜索索引页面 从中找到所需数据的指针 再直接通过指针从数据页面中读取数据
索引的分类
在SQL Server 的数据库中按存储结构的不同将索引分为两类 簇索引(Clustered Index)和非簇索引(Nonclustered Index)
( )簇索引对表的物理数据页中的数据按列进行排序 然后再重新存储到磁盘上 即簇索引与数据是混为一体 的它的叶节点中存储的是实际的数据 由于簇索引对表中的数据一一进行了排序 因此用簇索引查找数据很快 但由于簇索引将表的所有数据完全重新排列了 它所需要的空间也就特别大 大概相当于表中数据所占空间的 % 表的数据行只能以一种排序方式存储在磁盘上 所以一个表只能有一个簇索引
( )非簇索引具有与表的数据完全分离的结构 使用非簇索引不用将物理数据页中的数据按列袜友键排序 非簇索引的叶节点中存储了组成非簇索引的关键字的值和行定位器 行定位器的结构和存储内容取决于数据的存储方式 如果数据是以簇索引方式存储的 则行定位器中存储的是簇索引的索引键;如果数据不是以簇索引方式存储的 这种方式又称为堆存储方式(Heap Structure) 则行定位器存储的是指向数据行的指针 非簇索引将行定位器按关键字的值用一定的方式排序 这个顺序与表的行在数据页中的排序是不匹配的 由于非簇索引使用索引页存储因此它比簇索引需要更多的存储空间且检索效率较低但一个表只能建一个簇索引 当用户需要建立多个索引时就需要使用非簇索引了
小结 Clustered Index 是与物理数据混在一起并对物理数据进重排 就像使用拼音查字典;Unclustered Index 是与物理数据完全分离的 利用额外空间对关键字进行重排 就像使用部首查字典
数据库索引应用
一 索引的概念
索引就是加快检索表中数据的方法 数据库的索引类似于书籍的索引 在书籍中 索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息 在数据库中 索引也允许数据库程序迅速地找到表中的数据 而不必扫描整个数据库
二 索引的特点
索引可以加快数据库的检索速度
索引降低了数据库插入 修改 删除等维护任务的速度
索引创建在表上 不能创建在视图上
索引既可以直接创建 也可以间接创建
可以在优化隐藏中 使用索引
使用查询处理器执行SQL语句 在一个表上 一次只能使用一个索引
其他
三 索引的优点
创建唯一性索引 保证数据库表中每一行数据的唯一性
大大加快数据的检索速度 这也是创建索引的最主要的原因
加速表和表之间的连接 特别是在实现数据的参考完整性方面特别有意义
在使用分组和排序子句进行数据检索时 同样可以显着减少查询中分组和排序的时间
通过使用索引 可以在查询告巧的过程中使用优化隐藏器 提高系统的性能
四 索引的缺点
创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加
索引需要占物理空间 除了数据表占数据空间之外 每一个索引还要占一定的物理空间 如果要建立聚簇索引 那么需要的空间就会更大
当对表中的数据进行增加 删除和修改的时候 索引也要动态的维护 降低了数据的维护速度
lishixin/Article/program/MySQL/201311/29604
Ⅲ 数据库索引有哪几种,怎样建立索引
数据库索引的种类：
1、按照索引列值的唯一性，索引可分为唯一索引和非唯一索引
非唯一索引：B树索引
create index 索引名 on 表名（列名） tablespace 表空间名;
唯一索引：建立主键或者唯一约束时会自动在对应的列上建立唯一索引
2、索引列的个数：单列索引和复合索引
3、按照索引列的物理组织方式
B树索引
create index 索引名 on 表名（列名） tablespace 表空间名;
位图索引
create bitmap index 索引名 on 表名（列名） tablespace 表空间名;
反向键索引
create index 索引名 on 表名（列名） reverse tablespace 表空间名;
函数索引
create index 索引名 on 表名（函数名(列名)） tablespace 表空间名;
删除索引
drop index 索引名
重建索引
alter index 索引名 rebuild
索引的创建格式：
CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>
UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
STORAGE：可进一步设置表空间的存储参数
LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
COMPUTE STATISTICS：创建新索引时收集统计信息
NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区
使用USER_IND_COLUMNS查询某个TABLE中的相应字段索引建立情况
使用DBA_INDEXES/USER_INDEXES查询所有索引的具体设置情况。
在Oracle中的索引可以分为：B树索引、位图索引、反向键索引、基于函数的索引、簇索引、全局索引、局部索引等，下面逐一讲解：
一、B树索引：
最常用的索引，各叶子节点中包括的数据有索引列的值和数据表中对应行的ROWID，简单的说，在B树索引中，是通过在索引中保存排过续的索引列值与相对应记录的ROWID来实现快速查询的目的。其逻辑结构如图：
反向键索引是一种特殊的B树索引，在存储构造中与B树索引完全相同，但是针对数值时，反向键索引会先反向每个键值的字节，然后对反向后的新数据进行索引。例如输入2008则转换为8002，这样当数值一次增加时，其反向键在大小中的分布仍然是比较平均的。
反向键索引的创建示例：
createindex ind_t on t1(id) reverse;
注：键的反转由系统自行完成。对于用户是透明的。
四、基于函数的索引：
有的时候，需要进行如下查询：select * from t1 where to_char(date,'yyyy')>'2007';
但是即便在date字段上建立了索引，还是不得不进行全表扫描。在这种情况下，可以使用基于函数的索引。其创建语法如下：
create index ind_t on t1(to_char(date,'yyyy'));
注：简单来说，基于函数的索引，就是将查询要用到的表达式作为索引项。
五、全局索引和局部索引：
这个索引貌似很复杂，其实很简单。总得来说一句话，就是无论怎么分区，都是为了方便管理。
具体索引和表的关系有三种：
1、局部分区索引：分区索引和分区表1对1
2、全局分区索引：分区索引和分区表N对N
3、全局非分区索引：非分区索引和分区表1对N
创建示例：
首先创建一个分区表
createtable student
(
stuno number(5),
sname vrvhar2(10),
deptno number(5)
)
partition by hash (deptno)
(
partition part_01 tablespace A1,
partition part_02 tablespace A2
);
创建局部分区索引(1v1)：
create index ind_t on student(stuno)
local(
partition part_01 tablespace A2,
partition part_02 tablespace A1
);--local后面可以不加
创建全局分区索引(NvN)：
create index ind_t on student(stuno)
globalpartition by range(stuno)
(
partition p1 values less than(1000) tablespace A1,
partition p2 values less than(maxvalue) tablespace A2
);--只可以进行range分区
创建全局非分区索引(1vN)
createindex ind_t on student(stuno) GLOBAL;
Ⅳ 数据库建立索引怎么利用索引查询
1.合理使用索引x0dx0a索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。x0dx0a索唤漏引的使用要恰到好处，其使用原则如下：x0dx0a在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。x0dx0a在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。x0dx0a在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。x0dx0a如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。x0dx0a使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而 使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量 数据后，删除并重建索引可以提高查询速度。x0dx0a(1)在下面两条select语句中:x0dx0aSELECT * FROM table1 WHERE field1<=10000 AND field1>=0; x0dx0aSELECT * FROM table1 WHERE field1>=0 AND field1<=10000;x0dx0a如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。x0dx0a第一个原则：在where子句中应把最具限制性的条件放在最前面。x0dx0a(2)在下面的select语句中:x0dx0aSELECT * FROM tab WHERE a=? AND b=? AND c=?;x0dx0a若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。x0dx0a第二个原则：where子句中字段的顺序应和索引中字段顺序一致。x0dx0a—————————————————————————— x0dx0a以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。 x0dx0a—————————————喊链段————————————— x0dx0a(3) SELECT field3,field4 FROM tb WHERE field1='sdf' 快 x0dx0aSELECT * FROM tb WHERE field1='sdf' 慢[/cci]x0dx0a因为后者在索引扫描后要多一步ROWID表访问。x0dx0a(4) SELECT field3,field4 FROM tb WHERE field1>='sdf' 快 x0dx0aSELECT field3,field4 FROM tb WHERE field1>'sdf' 慢x0dx0a因为前者可以迅速定位索引。x0dx0a(5) SELECT field3,field4 FROM tb WHERE field2 LIKE 'R%' 快 x0dx0aSELECT field3,field4 FROM tb WHERE field2 LIKE '%R' 慢，x0dx0a因为后者不使用索引。x0dx0a(6) 使用函郑誉数如： x0dx0aSELECT field3,field4 FROM tb WHERE upper(field2)='RMN'不使用索引。x0dx0a如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。x0dx0a(7) 空值不在索引中存储，所以 x0dx0aSELECT field3,field4 FROM tb WHERE field2 IS[NOT] NULL不使用索引。x0dx0a(8) 不等式如 x0dx0aSELECT field3,field4 FROM tb WHERE field2!='TOM'不使用索引。 x0dx0a相似地， x0dx0aSELECT field3,field4 FROM tb WHERE field2 NOT IN('M','P')不使用索引。x0dx0a(9) 多列索引，只有当查询中索引首列被用于条件时，索引才能被使用。x0dx0a(10) MAX，MIN等函数，使用索引。 x0dx0aSELECT max(field2) FROM tb 所以，如果需要对字段取max，min，sum等，应该加索引。x0dx0a一次只使用一个聚集函数，如： x0dx0aSELECT “min”=min(field1), “max”=max(field1) FROM tb x0dx0a不如：SELECT “min”=(SELECT min(field1) FROM tb) , “max”=(SELECT max(field1) FROM tb)x0dx0a(11) 重复值过多的索引不会被查询优化器使用。而且因为建了索引，修改该字段值时还要修改索引，所以更新该字段的操作比没有索引更慢。x0dx0a(12) 索引值过大（如在一个char(40)的字段上建索引），会造成大量的I/O开销（甚至会超过表扫描的I/O开销）。因此，尽量使用整数索引。 Sp_estspace可以计算表和索引的开销。x0dx0a(13) 对于多列索引，ORDER BY的顺序必须和索引的字段顺序一致。x0dx0a(14) 在sybase中，如果ORDER BY的字段组成一个簇索引，那么无须做ORDER BY。记录的排列顺序是与簇索引一致的。x0dx0a(15) 多表联结（具体查询方案需要通过测试得到） x0dx0awhere子句中限定条件尽量使用相关联的字段，且尽量把相关联的字段放在前面。 x0dx0aSELECT a.field1,b.field2 FROM a,b WHERE a.field3=b.field3x0dx0afield3上没有索引的情况下: x0dx0a对a作全表扫描，结果排序 x0dx0a对b作全表扫描，结果排序 x0dx0a结果合并。 x0dx0a对于很小的表或巨大的表比较合适。x0dx0afield3上有索引 x0dx0a按照表联结的次序，b为驱动表，a为被驱动表 x0dx0a对b作全表扫描 x0dx0a对a作索引范围扫描 x0dx0a如果匹配，通过a的rowid访问x0dx0a(16) 避免一对多的join。如： x0dx0aSELECT tb1.field3,tb1.field4,tb2.field2 FROM tb1,tb2 WHERE tb1.field2=tb2.field2 AND tb1.field2=‘BU1032’ AND tb2.field2= ‘aaa’ x0dx0a不如： x0dx0adeclare @a varchar(80) x0dx0aSELECT @a=field2 FROM tb2 WHERE field2=‘aaa’ x0dx0aSELECT tb1.field3,tb1.field4,@a FROM tb1 WHERE field2= ‘aaa’x0dx0a(16) 子查询 x0dx0a用exists/not exists代替in/not in操作 x0dx0a比较： x0dx0aSELECT a.field1 FROM a WHERE a.field2 IN(SELECT b.field1 FROM b WHERE b.field2=100) x0dx0aSELECT a.field1 FROM a WHERE EXISTS( SELECT 1 FROM b WHERE a.field2=b.field1 AND b.field2=100) x0dx0aSELECT field1 FROM a WHERE field1 NOT IN( SELECT field2 FROM b) x0dx0aSELECT field1 FROM a WHERE NOT EXISTS( SELECT 1 FROM b WHERE b.field2=a.field1)x0dx0a(17) 主、外键主要用于数据约束，sybase中创建主键时会自动创建索引，外键与索引无关，提高性能必须再建索引。x0dx0a(18) char类型的字段不建索引比int类型的字段不建索引更糟糕。建索引后性能只稍差一点。x0dx0a(19) 使用count(*)而不要使用count(column_name)，避免使用count(DISTINCT column_name)。x0dx0a(20) 等号右边尽量不要使用字段名，如： x0dx0aSELECT * FROM tb WHERE field1 = field3x0dx0a(21) 避免使用or条件，因为or不使用索引。x0dx0a2.避免使用order by和group by字句。x0dx0a因为使用这两个子句会占用大量的临时空间(tempspace),如果一定要使用，可用视图、人工生成临时表的方法来代替。 x0dx0a如果必须使用，先检查memory、tempdb的大小。 x0dx0a测试证明，特别要避免一个查询里既使用join又使用group by，速度会非常慢！x0dx0a3.尽量少用子查询，特别是相关子查询。因为这样会导致效率下降。x0dx0a一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。x0dx0a4．消除对大型表行数据的顺序存取x0dx0a在 嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。 x0dx0a比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询 10亿行数据。 x0dx0a避免这种情况的主要方法就是对连接的列进行索引。 x0dx0a例如，两个表：学生表（学号、姓名、年龄??）和选课表（学号、课程号、成绩）。如果两个 表要做连接，就要在“学号”这个连接字段上建立索引。 x0dx0a还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。 x0dx0a下面的查询将强迫对orders表执行顺序操作： x0dx0aSELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 x0dx0a虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： x0dx0aSELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 x0dx0aUNION x0dx0aSELECT ＊ FROM orders WHERE order_num=1008 x0dx0a这样就能利用索引路径处理查询。x0dx0a5．避免困难的正规表达式x0dx0aMATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _” x0dx0a即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。 x0dx0a另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。x0dx0a6．使用临时表加速查询x0dx0a把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如： x0dx0aSELECT cust.name，rcvbles.balance，??other COLUMNS x0dx0aFROM cust，rcvbles x0dx0aWHERE cust.customer_id = rcvlbes.customer_id x0dx0aAND rcvblls.balance>0 x0dx0aAND cust.postcode>“98000” x0dx0aORDER BY cust.name x0dx0a如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序： x0dx0aSELECT cust.name，rcvbles.balance，??other COLUMNS x0dx0aFROM cust，rcvbles x0dx0aWHERE cust.customer_id = rcvlbes.customer_id x0dx0aAND rcvblls.balance>;0 x0dx0aORDER BY cust.name x0dx0aINTO TEMP cust_with_balance x0dx0a然后以下面的方式在临时表中查询： x0dx0aSELECT ＊ FROM cust_with_balance x0dx0aWHERE postcode>“98000” x0dx0a临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。 x0dx0a注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。x0dx0a7．用排序来取代非顺序存取x0dx0a非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。