MySQL的索引

1、索引的基本介绍

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。可以简单理解为排好序的快速查找数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个 右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针，这样就可以运用 二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

我们平常所说的索引，如果没有特别指明，都是指 B 树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用 B+ 树索引，统称索引。当然，除了 B+ 树这种类型的索引之外，还有哈希索引（hash index）等。

索引分单列索引（主键索引、唯一索引、普通索引）和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个组合索引包含两个或两个以上的列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件中(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

但过多的使用索引将会造成滥用，因为索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

1.1、索引的好处

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

使用索引的好处：

1. 建立索引可以大大提高检索的数据，以及减少表的检索行数
2. 在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）
3. 可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性.
4. 在表连接的连接条件可以加速表与表直接的相连

1.2、索引的缺点

索引的缺点如下：

1. 索引文件会占用物理空间，除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。
2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3. 当对表的数据进行 INSERT、UPDATE、DELETE 的时候，索引也要动态的维护，这样就会降低数据的维护速度。

1.3、索引的创建时机

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面，哪一些索引需要建立，哪一些所以是多余的。

适合创建索引的情况：

1. 主键会自动建立唯一索引，无需我们再手动建。
2. 频繁作为查询条件的字段应该创建索引。一般来说，在经常需要搜索的列上，可以加快索引的速度。
3. 查询中与其它表关联的字段，外键关系建立索引。在表与表的而连接条件上加上索引，可以加快连接查询的速度。
4. 单键/组合索引的选择问题， 组合索引性价比更高。
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。在经常需要排序(order by)、分组(group by)和distinct 的列上加索引，可以加快排序查询的时间,  (单独order by 用不了索引，索引考虑加where 或加limit)
6. 查询中统计或者分组字段。
7. 在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
8. 使用短索引。如果你的一个字段是Char(32)或者int(32)，在创建索引的时候可以指定前缀长度，比如前10个字符 (前提是多数值是唯一的..)，那么短索引可以提高查询速度，并且可以减少磁盘的空间，也可以减少I/0操作。
9. 选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

不适合创建索引的情况：

1. 表记录太少
2. 经常增删改的表或者字段
3. Where 条件里用不到的字段不创建索引
4. 数据重复且分布平均的表字段不适合建索引。如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

其他的一些情况说明：

• 不要在列上进行运算，这样会使得mysql索引失效，也会进行全表扫描。
• like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick '%ABC%' 那么这个索引讲不会起到作用.而nickname lick 'ABC%' 那么将可以用到索引。
• 索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串

2、创建索引

2.1、单列索引

2.1.1、普通索引

这个是最基本的索引，它没有任何限制。创建命令如下：

-- 直接创建索引
CREATE INDEX index_name ON table_name (column_name);
-- 修改表结构的方式来添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);   

-- 示例
CREATE INDEX idx_user_name ON user (name);

ALTER TABLE user ADD INDEX idx_user_name (name);

对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。创建索引时，使用 column_name(length) 语法，对列的前 length 个字符编制索引。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。

-- 只用一列的一部分创建索引
CREATE INDEX index_name ON table_name (column_name(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

如果某列的前 n 个字符基本都不同，那么使用该列的前 n 个字符来创建索引也不会比使用列的全名创建的索引速度慢很多。另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

2.1.2、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，这一点和主键索引是一样的，但唯一索引允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

-- 直接创建索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);
-- 修改表结构的方式来添加索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);   

当给表创建或设置主键的时候，mysql 会自动添加一个与主键对应的唯一索引，所不需要对主键再做额外的添加索引的操作。

数据库主键和索引的区别与联系：

1. 主键是一定是唯一性索引，但唯一性索引不一定是主键。主键就是能够唯一标识表中某一行的属性或者是属性组，一个表只能有一个主键。数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。
2. 唯一索引标识索引值唯一，一个表可以有多个唯一索引，但主键只能有一个。
3. 主键列不能为空，但唯一索引列可以为空。
4. 一张表只能有一个主键，但可以有多个索引。
5. 通俗举例来说：主键相当于一本书的页码，索引相当于书的目录。

2.1.3、主键索引

主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会自动创建主键索引。CREATE INDEX不能用来创建主键索引，而是应该使用 ALTER TABLE。

-- 实际上创建主键就是创建了主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

2.2、组合索引

组合索引也就是一个索引包含多个字段。

创建命令如下：

CREATE INDEX index_name ON table_name (column1, column2, column3...);

-- 示例
CREATE INDEX idx_user_name_idcard_email ON user (name, idcard, email);

3、删除索引

DROP INDEX [indexName] ON table_name; 

4、查看索引

SHOW INDEX [indexName] FROM table_name; 

示例：

show index from tbl_emp;

查询结果：