数据库创建索引后怎么使用

应该建索引的字段：1经常作为查询条件的字段2外键3经常需要排序的字段4分组排序的字段

应该少建或者不建索引的字段有：1表记录太少，2经常需要插入，删除，修改的表，3表中数据重复且分布平均的字段

一些SQL的写法会限制索引的使用：1where子句中如果使用in、or、like、!=，均会导致索引不能正常使用，将""换成">and=chr(0)";2使用函数时，该列就不能使用索引。3比较不匹配数据类型时，该索引将会被忽略。

一些SQL语句优化的写法：1如果from是双表的查询时，大表放在前面，小表放在后面(基础表)。最后面的表是基础表。(只在基于规则的优化器中有效)2如果三表查询时，选择交叉表(table)作为基础表(只在基于规则的优化器中有效)3写where条件时，有索引字段的判断在前，其它字段的判断在后；如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件；4查询数量较大时，使用表连接代替IN，EXISTS，NOTIN，NOTEXISTS等。5ORACLE采用自下而上的顺序解析WHERE子句,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

数据库引入了索引

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

有关 索引 的比喻

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

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

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

索引在表中的角色

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

索引高效原理

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

索引的分类

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

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

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

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

数据库索引应用

一 索引的概念

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

二 索引的特点

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

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

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

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

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

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

其他

三 索引的优点

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

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

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

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

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

四 索引的缺点

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

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

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

lishixinzhi/Article/program/MySQL/201311/29604

为给定表或视图创建索引。

只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引，无论表中是否有数据。可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。语法

CREATE[UNIQUE][CLUSTERED|]INDEXindex_name

ON{table|view}(column[ASC|DESC][,n])

[WITH[,n]]

[ONfilegroup]::=

{PAD_INDEX|

FILLFACTOR=fillfactor|

IGNORE_DUP_KEY|

DROP_EXISTING|

STATISTICS_NORECOMPUTE|

SORT_IN_TEMPDB

}参数

UNIQUE为表或视图创建唯一索引（不允许存在索引值相同的两行）。视图上的聚集索引必须是UNIQUE索引。在创建索引时，如果数据已存在，MicrosoftSQLServer会检查是否有重复值，并在每次使用INSERT或UPDATE语句添加数据时进行这种检查。如果存在重复的键值，将取消CREATEINDEX语句，并返回错误信息，给出第一个重复值。当创建UNIQUE索引时，有多个NULL值被看作副本。如果存在唯一索引，那么会产生重复键值的UPDATE或INSERT语句将回滚，SQLServer将显示错误信息。即使UPDATE或INSERT语句更改了许多行但只产生了一个重复值，也会出现这种情况。如果在有唯一索引并且指定了IGNORE_DUP_KEY子句情况下输入数据，则只有违反UNIQUE索引的行才会失败。在处理UPDATE语句时，IGNORE_DUP_KEY不起作用。SQLServer不允许为已经包含重复值的列创建唯一索引，无论是否设置了IGNORE_DUP_KEY。如果尝试这样做，SQLServer会显示错误信息；重复值必须先删除，才能为这些列创建唯一索引。CLUSTERED创建一个对象，其中行的物理排序与索引排序相同，并且聚集索引的最低一级（叶级）包含实际的数据行。一个表或视图只允许同时有一个聚集索引。具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其它索引。在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。说明因为按照定义，聚集索引的叶级与其数据页相同，所以创建聚集索引时使用ONfilegroup子句实际上会将表从创建该表时所用的文件移到新的文件组中。在特定的文件组上创建表或索引之前，应确认哪些文件组可用并且有足够的空间供索引使用。文件组的大小必须至少是整个表所需空间的12倍，这一点很重要。

创建索引可以直接在对象资源管理器中选择表---->关系---->自己创建索引，也可以通过语句创建，方法：

--创建聚集索引，可以提供查询速度

create clustered index 索引名 on 表名（列名）

with fillfactor = 50 --这是指填充因子

go

创建索引后查询方法如下：

select 列名 from 表名(index=你创建的索引名)+条件

一 ROWID的概念

存储了row在数据文件中的具 置 位编码的数据 A Z a z + 和 /

row在数据块中的存储方式

SELECT ROWID last_name FROM hr employees WHERE department_id = ;

比如 OOOOOOFFFBBBBBBRRR

OOOOOO data object number 对应dba_objects data_object_id

FFF file# 对应v$datafile file#

BBBBBB block#

RRR row#

Dbms_rowid包

SELECT dbms_rowid rowid_block_number( AAAGFqAABAAAIWEAAA ) from dual;

具体到特定的物理文件

二 索引的概念

类似书的目录结构

Oracle 的 索引 对象 与表关联的可选对象 提高SQL查询语句的速度

索引直接指向包含所查询值的行的位置 减少磁盘I/O

与所索引的表是相互独立的物理结构

Oracle 自动使用并维护索引 插入 删除 更新表后 自动更新索引

语法 CREATE INDEX index ON table (column[ column] );

B tree结构(非bitmap)

[一]了解索引的工作原理

表 emp

目标 查询Frank的工资salary

建立索引 create index emp_name_idx on emp(name);

[试验]测试索引的作用

运行/rdbms/admin/utlxplan 脚本

建立测试表

create table t as select from dba_objects;

insert into t select from t;

create table indextable

as select rownum id owner object_name subobject_name

object_id data_object_id object_type created

from t;

set autotrace trace explain

set timing on

分析表 可以得到cost

查询 object_name= DBA_INDEXES

在object_name列上建立索引

再查询

[思考]索引的代价

插入 更新

三 唯一索引

何时创建 当某列任意两行的值都不相同

当建立Primary Key(主键)或者Unique constraint(唯一约束)时 唯一索引将被自动建立

语法 CREATE UNIQUE INDEX index ON table (column);

演示

四 组合索引

何时创建 当两个或多个列经常一起出现在where条件中时 则在这些列上同时创建组合索引

组合索引中列的顺序是任意的 也无需相邻 但是建议将最频繁访问的列放在列表的最前面

演示(组合列 单独列)

五 位图索引

何时创建

列中有非常多的重复的值时候 例如某列保存了 性别 信息

Where 条件中包含了很多OR *** 作符

较少的update *** 作 因为要相应的跟新所有的bitmap

结构 位图索引使用位图作为键值 对于表中的每一数据行位图包含了TRUE( ) FALSE( ) 或NULL值

优点 位图以一种压缩格式存放 因此占用的磁盘空间比标准索引要小得多

语法 CREATE BITMAP INDEX index ON table (column[ column] );

掩饰

create table bitmaptable as select from indextable where owner in( SYS PUBLIC );

分析 查找 建立索引 查找

六 基于函数的索引

何时创建 在WHERE条件语句中包含函数或者表达式时

函数包括 算数表达式 PL/SQL函数 程序包函数 SQL函数 用户自定义函数

语法 CREATE INDEX index ON table (FUNCTION(column));

演示

必须要分析表 并且query_rewrite_enabled=TRUE

或者使用提示/+ INDEX(ic_index)/

七 反向键索引

目的 比如索引值是一个自动增长的列

多个用户对集中在少数块上的索引行进行修改 容易引起资源的争用 比如对数据块的等待 此时建立反向索引

性能问题

语法

重建为标准索引 反之不行

八 键压缩索引

比如表landscp的数据如下

site feature job

Britten Park Rose Bed Prune

Britten Park Rose Bed Mulch

Britten Park Rose Bed Spray

Britten Park Shrub Bed Mulch

Britten Park Shrub Bed Weed

Britten Park Shrub Bed Hoe

……

查询时 以上 列均在where条件中同时出现 所以建立基于以上 列的组合索引 但是发现重复值很多 所以考虑压缩特性

Create index zip_idx

on landscp(site feature job)

press ;

将索引项分成前缀(prefix)和后缀(postfix)两部分 前两项被放置到前缀部分

Prefix : Britten Park Rose Bed

Prefix : Britten Park Shrub Bed

实际所以的结构为

Prune

Mulch

Spray

Mulch

Weed

Hoe

特点 组合索引的前缀部分具有非选择性时 考虑使用压缩 减少I/O 增加性能

九 索引组织表(IOT)

将表中的数据按照索引的结构存储在索引中 提高查询速度

牺牲插入更新的性能 换取查询性能 通常用于数据仓库 提供大量的查询 极少的插入修改工作

必须指定主键 插入数据时 会根据主键列进行B树索引排序 写入磁盘

十 分区索引

簇:

A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together

lishixinzhi/Article/program/Oracle/201311/17769

以上就是关于数据库创建索引后怎么使用全部的内容，包括:数据库创建索引后怎么使用、数据库基础：讲解MySQL索引的概念及数据库索引的应用[1]、数据库中创建索引等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！