MySQL-索引

索引原理

索引是对数据库表中一列或多列的值进行排序的一种结构

索引被用来快速找出在一个列上用一特定值的行。

没有索引，MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。

对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。

MYISAM 和 INNODB 都是用 B+Tree 作为索引结构

简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。

索引的分类

实际工作使用中，索引可以建立在单一列上，称为单列索引，也可以建立在多个列上，称为组合索引。

1、普通索引

这是最基本的索引，它没有任何限制。

2、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3、主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

4、聚簇索引

聚簇索引的索引顺序就是数据存储的物理存储顺序，这样能保证索引值相近的元组所存储的物理位置也相近。

5、全文索引（FULLTEXT）

全文索引只能创建在数据类型为VARCHAR或TEXT的列上，建立全文索引后，能够在建立了全文索引的列上进行全文查找。全文索引只能在MyISAM存储引擎的表中创建。

索引的应用

创建索引

应该创建索引的列

• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
• 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不该创建索引的列

• 对于那些在查询中很少使用或者参考的列不应该创建索引。
  若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 对于那些只有很少数据值或者重复值多的列也不应该增加索引。
  这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 对于那些定义为text, image和bit数据类型的列不应该增加索引。
  这些列的数据量要么相当大，要么取值很少。
• 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

使用alter table 创建索引

alter table 表名 add unique index 索引名 (列名);

使用create index 创建索引

-- 创建普通索引 
CREATE INDEX 索引名 ON 表名(列名);

-- 创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

-- 创建普通组合索引
CREATE INDEX 索引名 ON 表名(列名1,列名2);

-- 创建唯一组合索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名1,列名2);

查看索引

SHOW {INDEX | INDEXES | KEYS} {FROM | IN } 表名;

Table：指明索引所在表的名称。

Non_unique：该索引是否不是唯一性索引。如果不是值为1，如果是值为0。

Key_name：索引的名称。

Column_name：建立索引的列名称。

Collation：说明以何种顺序（升序或降序）索引。升序是A，如果值是NULL，则表示无分类。

删除索引

#-- 直接删除索引
DROP INDEX 索引名 ON 表名;

#-- 修改表结构删除索引
ALTER TABLE 表名 DROP INDEX 索引名;