SQL之索引

一、索引的概念

    索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录，能加快数据库的查询速度。一旦建立了索引后，数据库中查询优化器使用索引来快速定位数据，然后就无需扫描表中给定查询的每一行了。

二、索引的特点

优点：

• 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
• 可以加快数据的检索速度
• 可以加速表与表之间的连接
• 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占用物理空间，数据量越大，占用空间越大
• 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

什么时候需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中排序的字段创建索引将大大提高排序的速度
• 查询中经常统计或者分组的字段可以建立索引

什么时候不需要创建索引

• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
• where条件里用不到的字段，不创建索引
• 表记录太少，不需要创建索引
• 经常增删改的表
• 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引

三、索引的类型

    索引可分为四种类型：单列索引、唯一索引、主键索引和聚集索引

（1）单列索引

    最基本的索引类型，没有唯一性之类的限制。

创建索引：

CREATE INDEX index_name ON table_name (column_list)

创建索引的一个例子：

CREATE INDEX jobTitle ON employees(jobTitle);

在表employees的列名为jobTitle的列上建立索引jobTitle。

这时测试索引的效率可以将SQL语句的解释内容打出来：

EXPLAIN SELECT employeeNumber, lastName, firstName FROM employees WHERE jobTitle = 'Sales Rep';

创建表的时候指定索引:

Create table t1(
    id int,
    name char(10),
    Index index_name(id))

增加/修改索引：

ALTER TABLE tablename ADD INDEX index_name (column_list)

（2）唯一索引(UNIQUE)

    此索引的每一个索引值只对应唯一的数据记录，对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

创建唯一索引：

CREATE UNIQUE INDEX index_name ON tablename (column_name)；

创建表时，指定联合唯一索引：

Create table t1(
    Id int,
    name char(10),
    Unique key uni_name (id，name)
  )

修改/增加索引：

ALTER TABLE tablename ADD UNIQUE index_name (column_list)

（3）主键索引(primary key)

    数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

创建主键索引：

Alter table tablename add primary key(id)

创建表时指定主键（两种方式）：

Create table t1(
      Id int primary key
    )

Create table t1(
      Id int,
      Primary key(id)
    )

修改/删除主键索引：

Alter table tablename drop primary key;

（4）聚集索引(cluster)

    在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

CREATE CLUSTERED INDEX index_name ON tablename(column_list)

四、注意事项

    并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。在表较大时再建立索引，表中的数据越多，索引的优越性越明显。考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。

 

原文链接：https://www.jianshu.com/p/f588c41f1cb5