SQL基础总结(十)：索引

 

 

本系列blog源自前年写的SQL学习笔记，汇总一下发上来。(1月份发了前三篇笔记，原以为后面的笔记误操作删了，今天在硬盘里又找到了，一起发上来)

--------------------------------

对于以下列创建索引是恰当的，这些列经常被：查询、排序、分组、联结、用来计算顺序统计（MIN、MAX或中值）。

对于以下列创建索引是不恰当的：仅接受很少不同值（如性别）、很少被用于查询、只有几行的小表的一部分。

 

索引不会改变数据，仅仅是快速访问数据的途径。

表可以没有或有多个索引。

 

不要超出需求创建索引。DBMS在插入、更新或删除行之后必须更新索引。随着表索引的增长，DBMS要花费越来越多的时间维护索引，行的修改速度会越来越慢。因此，不要在一张表上创建超过10个索引。

 

在索引被创建后，DBMS自动维护和使用索引。

 

索引可以引用表的一列或多列。引用一列的索引称为简单索引，引用多列的索引称为组合索引。组合索引中的列在表中不一定是相邻的，简单索引无法垮多个表。

 

列的顺序在组合索引中是重要的。组合索引只作用于定义它的那组列，并非分别作用于每个列或相同列地其他顺序。

 

为了高效的排序和检索，索引要确保唯一性。唯一索引强制表索引中列（或多列）的值唯一。如果要在有重复值的列上创建唯一索引，DBMS将产生错误并拒绝创建。当创建主键约束和唯一约束时，DBMS自动创建唯一索引。

 

索引不是SQL标准的一部分，所以与索引有联系的SQL语句因DBMS不同而有差异。

CREATE [UNIQUE] INDEX index ON table(index_columns);

index_columns是要创建索引的一个或多个用逗号分隔的列名。

一个数据库中索引名称必须唯一。

试图在唯一索引中插入或更新重复的值，DBMS会产生错误并取消操作。

组合索引：

CREATE TABLE state_city_idx ON authors(state,city);

当按照state加city对行排序时，DBMS使用这个索引。当按照state、city或city加state排序和检索时，这个索引是没用的。

 

索引和键的区别：索引是DBMS改善执行效率的物理机制（硬件相关），键是DBMS用来强制引用完整性和通过视图更新的逻辑。

 

索引是存储在硬盘上的文件，因此占用空间。如果使用得当，索引可以成为避开连续读取大表、减少硬盘存取的主要手段。当创建索引时，它使用的空间是相关表占用空间的1.5倍。

当索引完成时，大多数空间将被释放。

 

聚集索引是键值逻辑顺序决定表中相应行的物理顺序的索引。非聚集索引中，索引的逻辑顺序和行存储在硬盘上的物理顺序是不同的。表只可以有一个聚集索引，聚集索引通常可以改进运行效率。它们会让搜索变得很快，插入、删除和更新变得很慢。

 

大多数索引采用平衡树，或B树实现。

 

使用DROP INDEX删除索引

在任何时候删除索引都不会影响表（或其它索引），如果删除了索引，所有SQL和应用程序会继续正常运行。

删除索引通常因为：

相关表很小或用户不再访问索引列。

在插入、更新和删除操作后DBMS维护索引所需的时间超过了索引加速检索而节约的时间。

 

无法删除DBMS为主键约束和唯一约束自动创建的索引。