关于SQL的学习记录（四、索引）

索引

索引基础：
对于大部分数据库用户来说索引是一个非常陌生的概念。因为普通用户很少特意的去使用索引。只有那些管理着海量数据的DBA才会去特意的创建索引和使用索引。
①使用索引的原因：

• 提供唯一的码值
• 提高查询性能
  数据表中的记录越多，使用索引可能就会得到越大的效益。反之，使用索引就没有什么价值了。

②索引的种类
在许多大型数据库系统中，按存储结构的不同将索引分为两类：聚簇索引、非聚簇索引

• 聚簇索引：
  一个聚簇索引就是一个在物理上与表融合在一起的视图。表和视图共享相同的存储区域。聚簇索引在物理上以索引顺序重新整理了数据的行。这种体系结构中一个表只允许一个聚簇索引。
  ❀MySQL中不同的数据存储引擎对聚簇索引有不同的支持。MyISAM存储引擎使用的是非聚簇索引，InnoDB存储引擎使用的是聚簇索引。
  ❀在SQL Server中，删除和重建一个聚簇索引对于改造一个表来说是一个常用的技术。这是一种保证数据页在磁盘上邻近的方便途径。同时，也是重建表中一些空闲空间的很好的方法。
  ❀Oracle的聚簇索引和SQL Server的聚簇索引完全不同。Oracle聚簇索引在一个Oracle块中同时存储两个或多个表中的数据。在建立聚簇索引时，先创建一个聚簇，然后在该聚簇上创建一个索引，最后在CREATE TABLE语句中指定该表存储在这个聚簇上。
  ❀聚簇码通常是用来连接这两个或多个表的连接字段。也就是说，如果用户需要使用两个表中的数据，那么只需要存取这一个Oracle块就可以，而并不需要先访问一个表，然后再访问另一个表。在SQL Server中没有与Oracle相似的结构。
• 非聚簇索引
  ❀在非聚簇索引中，索引数据和表数据在物理上是分离的，表中的记录并不按照索引中的顺序存储。非聚簇索引的查询效率相对于聚簇索引来说比较低，但由于一个数据表只能创建一个聚簇索引，所以当用户需要使用多个索引时就只能创建非聚簇索引了。

索引的创建和使用：
①创建索引时应该注意的内容
实际上，使用索引会提高查询性能这句话是有前提的，就是说并不是所有情况下使用索引都能提高查询性能。所以在创建并使用索引前应当注意下面的几点内容。

• 对于只有少量数据记录的表或在Oracle中占有小于10个Oracle块的表来说，使用索引查询数据没有任何好处。应当省掉存取和使用索引块的开销，直接执行全表扫描的到表中的所有数据，这样会更快一些。
• 如果索引字段中有很多不同的数据值和空值时，使用索引会极大地提高性能。
• 如果执行查询后，返回的数据记录很少，则索引可以优化该查询。比较好的情况时返回记录数少于全部数据的25%（根据DBMS的不同配置，该数字有所不同）。如果返回的数据记录很多，则使用索引不会得到太多的好处。
• 索引可以提高查询数据的速度，但它也降低了数据的更新速度。因此，如果要进行大量的更新操作，再执行更新操作前应该删除一些不必要的索引，在更新完毕后再重新创建索引，这样会提高效率。
• 索引也会占用数据库空间，所以在设计数据库的可用空间时应当考虑索引所占用的空间。
• 在某字段上创建索引时，应当考虑是否经常使用该字段筛选记录。如果不是，则不应该创建索引，因为该索引不会起什么作用，反而在修改数据是会影响性能。
• 尽量不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低期望获得的性能。
• 尽量不要将索引与表存储在同一个驱动器上，分开存储会避免访问冲突，从而能够提高性能。

②创建索引的SQL语句（其中[ ]中的为可选项）

• 语法格式：

  CREATE INDEX 索引名 ON 表名(字段名1[,字段名...]);
  

  （在MySQL和SQL Server中，索引的名称在表内必须唯一，但在数据库中不必唯一。而在Oracle中，索引的名称在用户内必须唯一。）
• MySQL、SQL Server、Oracle对上面创建索引的语句有不同的扩展
  ❀MySQL

  CREATE [UNIQUE|FULLTEXT|SPATIAL] 
  INDEX 索引名
  [USING 索引类型]
  ON 表名(字段名1[,字段名...]);
  

  ❀SQL Server

  CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
  INDEX 索引名
  ON {table|view} (字段名1 [ASC|DESC][,字段名2...])
  [WITH {PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB}[,字段名n...]] 
  [ON filegroup];
  

  ❀Oracle

  CREATE [UNIQUE]
  INDEX 索引名
  ON 表名(字段名1 [ASC|DESC] [,字段名...])
  [INITRANS integer]
  [MAXTRANS integer]
  [TABLESPASE tablespase_name]
  [STORAGE storage_clause]
  [PCTFREE integer]
  [NOSORT]
  [RECOVERABLE|UNRECOVERABLE]
  [PARALLEL parallel_clause];
  

③创建和使用唯一索引
使用索引的主要原因之一就是提供唯一的字段值。唯一索引强制表中任意两条记录的索引值互不相同。创建唯一索引需要使用UNIQUE关键字。

索引的删除：
索引是一把双刃剑，虽然它提高了查询速度，但也降低了更新数据的速度，因为每当更新数据时，都要维护一次索引。因此，当不再使用索引或者要向表插入大量数据时，应当删除索引。
删除索引的语法格式：

DROP INDEX 索引名
ON 表名;