[一点笔记]数据库索引-MySQL

  索引用于快速找出在某个列中有一特定值得行。不使用索引，MySQL必须从第1条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花时间越多。当表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。
  索引是对数据表中一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查找速度。所有MySQL列类型都可以被索引。

索引的主要优点是可以大大加快数据查询速度，数据库索引的特点：

  a）避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据。

  b）聚集索引可以避免数据插入操作，集中于表的最后一个数据页面。

  c）在某些情况下，索引可以避免排序操作。

创建索引：
  1、在创建表时创建索引：

CREATE TABLE table_name 
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length]) [ASC|DESC]

  方括号内为可选字段，UNIQUE、FULLTEXT、SPATIAL分别表示唯一索引、全文索引和空间索引；INDEX、KEY两者作用相同，用来指定创建索引；index_name为索引的名称；col_name为需要创建索引的字段列；length可选，表示索引长度；ASC、DESC指定升序或降序。
示例：
在建表时为年份字段year创建了一个普通索引

  2、在已经存在的表上创建索引
  可以使用alter table或者create index在已知的表上添加索引

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length,...]) [ASC|DESC]

示例：
为bookname设置名为bkname，长度20的索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC|DESC]

示例：
为comment列设置名为com的普通索引

  3、查看当前表的索引
  使用show create table 查看表结构：

  可以看到之前建立的三个索引（year、bkname、com）。使用explain语句查看索引是否正在被使用：

可以看到possible_key（可选用的索引）和key（实际选用的索引）的值都为year，查询使用了我们之前建立的索引。

索引的分类：
  1、普通索引和唯一索引
    普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
    唯一索引的索引列的值必须是惟一的，的允许有空值。如果是组合索引，则列的组合必须唯一，主键索引是一种特殊的唯一索引，不允许有空值。

  2、单列索引和组合索引
    单列索引即一个索引只包含单个列，一个表中可以有多个单列索引。
    组合索引指在表的多个字段组合上创建索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引是遵循最左前缀集合。

  3、全文索引
    全文索引类型为FULLTEXT，在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空值全文索引可以在CHAR、VARCHAR或TEXT类型的列上创建。

  4、空间索引
    空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种：GEOMETRY、POINT、LINSTRING和POLYGON。创建空间索引的列，必须将其声明为NUT NULL

  但是增加索引也有许多不利，主要因为创建和维护索引需要时间，并且随着数据量的增加所耗费的时间也会增加，当表中数据变化时，索引也要动态的维护，这就降低了数据维护速度。此外索引需要磁盘空间，过多的索引会导致文件尺寸过大。

  综上所述，设计索引时应该考虑：

  a）避免对经常更新的表进行过多的索引，并且索引中的列应尽可能的少。而对经常
用于查询的字段应该创建索引

  b）数据量小的表不要使用索引。查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。

  c）在经常用来做搜索、且不同值较多的列上建立索引。一般来说，在WHERE和JOIN中出现的列需要建立索引以提高查询速度。不同值很少的列上不要建立索引，比如学生表的“性别”字段只有“男”、“女”两个不同值，如果建立索引不但不会提高查找效率，反而会严重降低数据更新速度。

  d）当唯一性是某种数据本身的属性时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

  e) 在频繁进行排序或分组（即group by 或 order by 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

索引的删除：
  MySQL中可以使用ALTER TABLE 和DROP INDEX删除索引，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。

ALTER TABLE table_name DROP INDEX index_name;

DROP INDEX index_name ON table_name;