索引的应用

什么是索引?

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

作用

在数据库系统中建立索引主要有以下作用：
（1）快速取数据；
（2）保证数据记录的唯一性；
（3）实现表与表之间的参照完整性；
（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

优缺点

优点
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

如何创建索引?

以mysql为例子

1.添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
2.添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( 
`column` 
) 
3.添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
4.添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
5.添加多列索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

索引的类型

• 根据数据库的功能，可以在数据库设计器中创建四种索引：普通索引、唯一索引、主键索引和复合索引。

• 普通索引: 最基本的索引类型，没有唯一性之类的限制。
  普通索引可以通过以下几种方式创建： 创建索引，例如CREATE INDEX
  <索引的名字> ON tablename (列的列表)；
  修改表，例如ALTER TABLE tablename ADD INDEX
  [索引的名字] (列的列表)； [3] 创建表的时候指定索引，例如
  CREATE TABLE tablename ( […],INDEX [索引的名字] (列的列表) )；

• 唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。
  当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。
  例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。
  对某个列建立UNIQUE索引后，插入新记录时，数据库管理系统会自动检查新纪录在该列上是否取了重复值，在CREATE TABLE
  命令中的UNIQE约束将隐式创建UNIQUE索引。 创建唯一索引的几种方式：
  创建索引，例如CREATE UNIQUE INDEX<索引的名字> ON tablename (列的列表)；
  修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
  创建表的时候指定索引，
  例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) )；

• 主键索引 简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键。
  在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
  提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

• 复合索引

• 单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;
  用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);
  复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;
  同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引;
  设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;

如何选择合适的列创建索引？

1. 在where 从句，group by 从句，order by 从句，on 从句中出现的列；

2. 索引字段越小越好；

3. 离散度大的列放到联合索引的前面；比如：

   select * from payment where staff_id = 2 and customer_id = 236;

   针对上面的查询是 index(sftaff_id, customer_id) 好？还是index(customer_id, staff_id)好？

   因为customer_id的离散度更大，因此用后面的更合适！！

   那么问题来了。怎么判断离散度呢，可以使用
   select count(distinct customer_id), count(distinct staff_id) from 表名

   谁的值大，说明这一些列的离散度更高！

创建索引需要注意SQL语句的写法

1、CREATE [UNIQUE|CLUSTERED] INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)
2、其中UNIQUE和CLUSTERED为可选项，分别是建立唯一索引和聚簇索引，具体解释为：UNIQUE:表示此索引的每一个索引值只对应唯一的数据。
3、CLUSTERED:表示要建立的索引时聚簇索引，即索引项的顺序与表中记录的物理顺序一致的索引组织。

拓展：
1、SQL语言，是结构化查询语言(Structured Query Language)的简称。SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

2、SQL语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。SQL语言语句可以嵌套，这使他具有极大的灵活性和强大的功能。

3、结构化查询语言SQL（STRUCTURED QUERY LANGUAGE）是最重要的关系数据库操作语言，并且它的影响已经超出数据库领域，得到其他领域的重视和采用，如人工智能领域的数据检索，第四代软件开发工具中嵌入SQL的语言等。