数据库管理员—索引

最新推荐文章于 2021-04-08 10:23:50 发布

ylcy999

最新推荐文章于 2021-04-08 10:23:50 发布

阅读量324

点赞数

一、索引

查询是在表上进行的最频繁的访问，一般情况用户总是查询表中的部分数据，select语句中通常需要where子句指定查询条件，如果能够在很小的范围内查询所需要的数据，而不是在全表范围内查询，那么将减少很多不必要的磁盘I/O，查询的速度无疑会大大加快，这种快速查询的方式就是索引。

索引是建立在表上的数据库对象，它主要用于加快查询速度，另外索引也可以作为唯一性约束，如果在表的一个列上建立了唯一性索引，那么系统将自动在这个列上建立唯一性约束，可以保证插入这个列的数据是唯一的。

索引是建立在表中某个或几个列上的，这样的列称之为索引列。在创建索引时，数据库服务器将对索引列的数据进行排序，并将排序的结果存储在索引所占用的存储空间中。查询数据时，数据库服务器首先在索引中查询，然后再到表中查询，因为索引中的数据事先进行了排序，所以只需要很少的查找次数就可以找到需要的数据。

select empno，ename，sal from emp where empno=7902；

假设在表emp中有1000行数据，

1）如果没有索引，那么系统需要在全表范围查询，查询次数为1000。

2）如果有索引，那么只需要很少的几次查询就可以找到编号为7902的员工数据。

索引中不仅存储了索引列上的数据（例empno），而且还存储了一个rowid的值，rowid是表中的一个伪列，数据库服务器自动添加，表中的每一行数据都有一个rowid，他代表这一行的标识，也就是这一行数据在存储空间的物理位置。在访问表中数据时，根据这个伪列值找到数据的实际存储位置，然后再进行访问。
索引列上数据已经进行排序（例empno），在索引中通过折半查找法查找数据，快速查找到到目标数据（目标empno），根据索引中目标empno对应的rowid就能直接到表中找到这行数据。
表是独立于索引的，无论对表建立了多少索引，无论索引对表中的数据进行怎样排序，表中数据不会发生变化。

索引可以分为唯一性索引和非唯一性索引，其中唯一性索引可以保证索引列的值是唯一的，按照索引列中列的数目，索引可以分为单列索引和复合索引。按照索引列的数据组织方式，索引可以分为B*树索引，位图索引，反向索引和基于函数的索引。

合理使用可以提高数据库查询功能，不合理使用会降低数据库性能。尤其在进行DML（insert、delete、update）操作时，在创建索引时，表中的数据被排序，DML操作表中数据发生变化，索引中数据将会被重新排序，如果在表上建立了多个索引，那么每个索引中的数据都要被重新进行排序，开销很大，特别是表非常大时。

建立索引一般原则为：

如果每次查询仅选择表中的少量行，应该建立索引。
如果在表上需要进行频繁的DML 操作，不要建立索引。
尽量不要在有很多重复值的列上建立索引。
不要在太小的表上建立索引，小表查询速度挺快，建立索引会增加系统开销。

索引的创建：

索引的创建可以自动创建，也可以手动创建。如果在表的一个或几个列上建立了主键约束或者唯一性约束，那么数据库服务器将自动在这些列上建立唯一性索引。这时候索引的名字和约束的名字相同。手动创建索引需要执行SQL命令，创建索引的命令是create index。只要用户具有create index的系统权限，用户可以在自己的模式中创建索引。用户具有create any index这个系统权限，用户可以在其他用户的模式中创建索引。

create index 索引名 on 表名（列1，列2...）；指定1个列称为单列索引，指定多个列称为复合索引。

在默认情况下，创建的索引是非唯一的，也就是说在表中的索引列上允许存在重复值。如果需要创建唯一想索引，那么就需要使用关键字UNIQUE。

create unique index 索引名 on 表名（列1，列2）

这样可以保证表中的列1和列2组合没有重复值，例如 create unique index idx3 on emp（empname，deptno）指没有两个员工名字相同，并且在同一部门。

建立索引指定列的一个重要原则是选择经常用在where子句中使用的列，如果在查询时要在where子句中指定多个查询条件，那么可以在涉及的多个列上分别创建索引。如果能够将两个索引合并为为一个，那么查询的次数也会减少。也就是复合索引，复合索引主要用于多个条件的查询语句中。

指定索引列时，要遵循以下原则：