11、索引

一、概念及优缺点

　　索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于快速获取信息。你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中， 索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

　　索引的本质是数据结构。索引是在存储引擎中实现的，因此每种存储引擎的索引不一定相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每一个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256个字节。有些存储引擎支持更多的索引数和更大的索引长度。

优点

• 提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因
• 降低了插入、删除、修改等维护任务的速度
• 唯一索引可以确保每一行数据的唯一性
• 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
• 在实现数据的参考完整性方面，可以加速表和表之间的连接在使用分组和排序子句进行数据查询是，可以显著减少查询中分组和排序的时间，降低CPU的消耗
  • 对于有依赖关系的子表和父表联合查询时，可以提高查询速度
• 在使用分组和排序子句进行数据查询是，可以显著减少查询中分组和排序的时间，降低CPU的消耗

缺点

• 索引需要占物理和数据空间。
• 创建索引和维护索引要消耗时间，并且随着数据量的增加，所消耗的时间也会增加
• 索引需要占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占用一定的物理空间，存储在磁盘上
  • 如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
• 虽然索引大大提高了查询数据，同时却会降低更新表的速度
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度

提示：索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，建议先删除表中的索引，然后插入数据，插入完成后在创建索引。

索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时 要花费较多的时间维护索引。

二、索引类型

1、主键索引(Primary Key)

　　主键索引就是一种特殊的唯一索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。在MySQL的InnoDB 的表中当没有显示的指定表的主键时InnoDB会自动先检查表中是否有唯一索引且不允许存在null值的字段如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

2、二级索引(辅助索引)

　　二级索引的叶子节点存储的数据是主键，通过二级索引，可以定位主键的位置。 

• 唯一索引(Unique Key) ：唯一索引也是一种约束。在创建唯一性索引时，限制该索引的值必须是唯一的，唯一索引的属性列不能出现重复的数据，但允许由空值。在一张数据表里可以有多个唯一索引。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。这类索引可以创建在任何数据类型中，建立索引以后，可以通过索引进行查询。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(FullText) ：全文索引主要是为了检索大文本数据中的关键字的信息。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型得字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

3、Mysql常见索引：FULLTEXT，HASH，BTREE，RTREE。

三、索引的使用

1、创建索引

1.1、创建表的时候创建索引

• 隐式的方式创建索引，在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引
• 显示的创建索引

  CREATE TABLE table_name(
      col_name data_type,
      [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
  );
  -- UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
  -- INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
  -- index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
  -- col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
  -- length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
  -- ASC或DESC指定升序或者降序的索引值存储。

1.2、在已存在的表上创建索引

--  使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
-- 使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

1.3、删除索引

-- 使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name;
-- 使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;