索引的操作

• 索引：数据库对象索引其实与书的目录非常类似，主要是为了提高从表中检索数据的速度和保证字段的唯一性。由于数据存储在数据库表中，所以索引是创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构（B-树或哈希表）中，通过mysql可以快速有效的查找与键值相关的字段。根据索引的存储类型，可以将索引分为B型树索引和哈希索引。
• 创建索引的利弊：索引的创建有利有弊，创建索引可以提高查询的速度，但过多的创建索引或占用更多的磁盘空间。
• 则在以下情况适合创建索引：经常被查询的字段，即在WHERE字句中出现的字段；在分组的字段，即在GROUP BY字句中出现的字段；存在关系的子表和父表之间的联合查询，即主键或外键字段；设置唯一完整性约束字段。
• 在以下情况不适合创建索引：在查询中很少使用的字段；拥有许多重复值得字段。

创建索引：就是在表的一个字段或多个字段上建立索引。

• 普通索引：
  • 创建表时创建普通索引：CREATE TABLE table_name（属性名1 数据类型1, 属性名2 数据类型2,属性名3 数据类型3, …, INDEX index_name(属性名1)); 在创建table_name表时在属性名1字段上创建名为index_name的普通索引。
  • 在已存在的表上创建普通索引：CREATE INDEX index_name ON table_name(属性2); 在名为table_name的表的属性2字段创建一个名为index_name的普通索引。
  • 通过SQL语句ALTER TABLE创建普通索引：ALTER TABLE table_name ADD INDEX index_name(属性3);在表名为table_name的表中的属性3字段添加一个名为index_name的普通索引。
• 唯一索引：在创建索引时，限制索引的值必须是唯一的。通过该类的索引可以更快速地查询某条记录。根据创建检索引的方式可以分为自动索引（在数据库表中设置完整性约束时，该表会被系统自动创建索引。）和手动索引（手动在表上创建索引）。
  • 创建表时创建唯一索引：CREATE TABLE table_name（属性名1 数据类型1, 属性名2 数据类型2,属性名3 数据类型3, … ,UNIQUE INDEX index_name(属性名1)); 在创建table_name表时在属性名1字段上创建名为index_name的唯一索引。
  • 在已存在的表上创建唯一索引：CREATE UNIQUE INDEX index_name ON table_name(属性2); 在名为table_name的表的属性2字段创建一个名为index_name的唯一索引。
  • 通过SQL语句ALTER TABLE创建唯一索引：ALTER TABLE table_name ADD UNIQUE INDEX index_name(属性3);在表名为table_name的表中的属性3字段添加一个名为index_name的唯一索引。
• 全文索引：主要关联在数据类型为CHAR、VARCHAR、TEXT的字段上，以便能够跟快速的查询数据量较大的字符串类型字段。在创建时和唯一索引的创建方式基本一样，只是修改关键字UNIQUE为FULLTEXT关键字即为创建全文索引，并且创建方式也都相同。例如：创建表时创建全文索引：CREATE TABLE table_name（属性名1 数据类型1, 属性名2 数据类型2,属性名3 数据类型3, … ,FULLTEXT INDEX index_name(属性名1)); 在创建table_name表时在属性名1字段上创建名为index_name的全文索引。
• 多列索引：创建多列索引的语句和创建普通索引的语句不同的地方在于指定索引列的数量不同，普通索引只指定一列，而多列索引需要指定两列及以上，并且各列之间用逗号分隔，其余全部相同。例：CREATE TABLE table_name（属性名1 数据类型1, 属性名2 数据类型2,属性名3 数据类型3, …, INDEX index_name(属性名1，属性名2)); 在创建table_name表时在属性名1和属性名2字段上创建名为index_name的多列索引索引。

删除索引：DROP INDEX index_name ON table_name;删除名为table_name表中名为index_name的索引。

查看索引：

• 执行SQL语句SHOW CREATE TABLE table_name;检验数据库表中的索引是否创建成功。
• 执行SQL语句EXPLAIN SELECT * FROM table_name WHERE 属性名=1;检验数据库table_name中”属性名“字段的索引是否被使用。

补充一下索引知识：

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

• B+ Tree索引

  • 是大多数MySQL存储引擎的默认索引类型。
  • B+ Tree： 是基于B Tree和叶子结点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能；
  • B Tree： 也就是平衡树，是一颗查找树，并且所有叶子结点位于同一层；
  • 不需要进行全表扫描，只需要对树进行搜索，所以查找速度快很多。因为B+ Tree的有序性，不仅可以用于查找，还可以用于排序和分组。InnoDB的B+ Tree索引分为主索引和辅助索引。主索引的叶子结点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据存放在两个不同的地方，所以一个表只能有一个聚簇索引。辅助索引的叶子节点的data域记录着主键值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

• 哈希索引

  • 能以O(1)的时间进行查找，但是失去了有序性，无法用于排序与分组，只支持精确查找，无法用于部分查找和范围查找。
  • InnoDB存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+ Tree索引之上在创建一个哈希索引，这样就让B+ Tree索引具有哈希索引的一些优点，比如快速的哈希查找。

• 全文索引

  • MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

• 空间数据索引

  • MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。