数据库索引

在这里我来总结一下数据库索引相关的知识，和大家来分享一下，主要从一下几个方面来了解数据库的索引：

一、什么是数据库索引？

首先顾名思义，线索、指引；生活中也有很多索引，书的索引、商场里的索引等等。书的索引就是书的目录，xx-yy页是什么内容；商场的索引是1楼化妆品，2楼女装，3楼男装......它们都是为了方便我们快速的定位到我们的目标而存在的。那么什么是数据库的索引呢？总结维基百科给出的定义是这样的：用以协助快速查询（排序）、更新数据库表中数据的一种存储结构（数据结构）；度娘百科上是这样写的：它是一种对数据库表中一列或多列的值进行排序的一种存储结构。

二、作用

1、快速取数据；

2、保证数据记录的唯一性；

3、实现表与表之间的参照完整性；

4、在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间

三、优缺点

1、优点：

①大大提高检索数据的速度

②唯一索引，能保证数据的唯一性

③外键的列加上索引能加速表连接

④显著的减少排序和分组的时间

2、缺点：

①、索引会占物理内存

②、新增、修改、删除的列有索引的话需要动态维护，从而影响操作表的效率

四、索引类型

四种索引：普通索引、唯一索引、主键索引和聚集索引。

1、普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
①创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,...);
②修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);
③创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

2、UNIQUE索引

表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique：
①创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
②修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
③创建表时指定索引：CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );

3、主键：PRIMARY KEY索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。
①主键一般在创建表的时候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。
②我们也可以通过修改表的方式加入主键：“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。
每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）
注：不能用CREATE INDEX语句创建PRIMARY KEY索引

4、聚集索引

索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。

五、索引的操作

1、ALTER TABLE - 用来创建普通索引、UNIQUE索引或PRIMARY KEYS索引

①ALTER TABLE table_name ADD INDEX index_name (column_list);

②ALTER TABLE table_name ADD UNIQUE index_name (column_list);

③ALTER TABLE table_name ADD PRIMARY KEYS (column_name);

2、CREATE TABLE - 可对表增加普通索引或UNIQUE索引

①CREATE INDEX index_name ON table_name (column_list);

②CREATE_UNIQUE index_name ON table_name (column_list);

3、删除索引

①DROP INDEX index_name ON table_name;

②ALTER TABLE table_name DROP IDNEX index_name;

③ALTER TABLE  table_name DROP PRIMARY KEY;

注：其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

六、索引选择原则和注意事项

1、经常出现在where语句或join语句中的字段适合加索引

2、数据较多的表适合加索引

3、 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

4、更新操作不是很频繁的字段适合加索引

5、使用短索引，如果对字符串字段设置索引应指定前缀长度，可节省大量索引空间，提升查询速度

6、where语句中是OR的关系的，索引不起作用（可以考虑用union代替）

7、联合索引（又叫复合索引，两个或更多个列上的索引）。对于复合索引Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是INDEX  IDX_NAME (a,b,c). 可以支持a |a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 ，所以常用来当查询条件的字段应尽量靠左，使用频率相对低的靠右；比如一个查询有两个条件：where a=1 and b=2，如果我们为a、b两个字段分别创建索引，由于mysql一次只能使用一个索引，所以我们加两个还有一个没有用，这种情况就应该为这两个字段创建联合索引，那这个查询的效率才算是得到了最大程度的提高

8、索引不会包含有NULL值的列，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

8、查询中排序的字段适合加索引，没有条件只有排序的情况，只给排序的字段加索引即可；若是有where条件加排序的情况，应该为条件和排序的字段创建联合索引

9、分组或统计的字段适合加索引，即经常用来分组或求和的字段

10、like语句中，column_name like '%xxx%'不会使用索引，但like 'xxx%'会使用，所以尽量少用like '%xxx%'查询