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;

查询结果：

5、覆盖索引（索引覆盖）

覆盖索引，也有人称之为索引覆盖，如果一个索引包含了（或覆盖了）查询语句中的查询字段与条件，此时就可以叫做覆盖索引。

就是select的数据列只用从索引中就能够取得，不必从数据表中读取。也就是查询的列被所使用的索引覆盖。索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用B-Tree索引做覆盖索引。

当发起一个被索引覆盖的查询（也叫作索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。

注意：如果要使用覆盖索引，一定要注意 select 列表中只取出所需要的列，不能使用 select * 。

6、索引的创建时机

单表：单表时，可针对where和order的字段建立索引，可建复合索引。如果查询语句对某个字段有进行范围的查询时，如果某个索引包含该字段，那么该索引在该字段后面的其他字段的索引将不会生效。所以如果对某个字段有范围的查询的话，可以考虑不为该字段建立索引。

多表：左连接时，右表一定要建立索引。右连接时，左表一定要建立索引。

“永远用小的结果集驱动大的结果集”，也就是在小的结果集的表中建立索引。

7、索引使用原则

要想充分利用到索引，可以遵守以下规则：

• 全值匹配我最爱，最左前缀要遵守
• 带头大哥不能死，中间兄弟不能断
• 索引列上少计算，范围之后全失效
• Like百分写最右，覆盖索引不写星
• 不等空值还有or，索引失效要少用
• VAR\VARCHAR引号不可丢，SQL高级也不难

7.1、全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断

全值匹配的意思查询的条件或者排序和复合索引的字段和顺序都保持一致。最左前缀原则指的是查询从索引的最左的那个字段开始并且不跳过索引中的某个字段，但是右边的字段不一定需要全部保留。比如复合索引为：name,age,pos，则查询条件应该为where name = xxx 或者 name = xxx and age =xxx 或者 name = xxx and age =xxx and pos= xxx，如果是where age =xxx and pos =xxx，则索引无法生效。

如果查询字段与索引字段顺序的不同或者跳过了中间的某个字段，则可能会导致索引无法充分使用，甚至索引失效！

在创建组合索引的时候，尽量包含查询条件中更多的字段，并且将最常用到的作为查询条件的字段放在最左边。可以通过不断地调整 SQL 查询语句来匹配到合适的索引。

满足最左前缀和不能跳过索引的某个字段只是针对该字段是属于索引的，如果该字段不属于该索引的字段则不影响。比如复合索引为：name,age,pos，查询条件可以为：where name = xxx and sex = xxx and age= xxx and pos=xxx ，中间有个 sex 字段不影响，因为该字段不属于该索引内的字段。

7.2、索引列上少计算，范围之后全失效

不要在索引列上做任何操作（包括计算、函数、自动或者手动的类型转换），这些都会导致索引失效而转向全表扫描。

范围之后全失效：指的是如果对某个索引字段使用了范围的查询条件（比如：in、like、<>），那么该索引不会对在该字段之后的字段生效。比如复合索引为：name,age,sex，查询条件为where name = xxx and age > 20 and sex =xxx，则索引只对name和age字段的查询生效，sex字段的过滤不会生效，该索引就不会被充分利用。存储引擎不能使用索引中范围条件右边的列。所以建议将可能做范围查询的字段放在索引顺序的最后面。

7.3、Like百分写最右，覆盖索引不写星

Like百分写最右：当使用 like 模糊查询时，如果百分比符号不止是存在于字符串右边的话，那么 mysql 的索引就会失效，从而变成全表扫描。

上面可以看到，只有当百分比符号只存在于字符串右边时，索引才生效。或者说，只有当 like 的百分比符号不存在于字符串最左边时，索引才生效。

如果想要通过 like '%xxx%' 查询仍能使用索引，此时可以使用覆盖索引。

比如基于 name, age 字段建立索引 idx_user_nameAge ON tbl_user(NAME,age)，如下查询：

此时查询 name, age 字段并且查询条件 name 包含在索引字段内，所以能用到覆盖索引。

覆盖索引不写星：尽量使用覆盖索引，也就是访问的字段包含在索引的字段内，不要使用或者减少使用 select *。

7.4、不等空值还有or，索引失效要少用

mysql 在使用 is not null 和 is null 时，有可能会无法使用索引从而导致全表扫描。

mysql 在使用不等于符号（!= 或者<>）时，有可能会无法使用索引从而导致全表扫描。

少用 OR 来连接查询条件，OR 关键字连接查询条件会导致索引失效。如下：

7.5、VAR\VARCHAR引号不可丢，SQL高级也不难

当我们根据字符串条件来查询时，即使不加引号，mysql 也能把正确结果给查询出来，这是因为 mysql 底层会帮你自动进行类型转换，但这样实际上就相当于对索引列进行了计算，这会导致索引直接失效，变成全表查询。

如下：

但是给数字类型额外加上引号并不会导致索引失效。

8、索引失效的其他常见情况

8.1、当查询的数据量大于全表的20%时，索引失效

当查询的数据量超过一定百分比（可能是20%，或者30%）时，索引就会失效。也就是说，当我们通过查询 SQL 最终查询出来的数据量占全表数据量的百分比超过 20% 时，mysql 就可能不会使用索引，而是使用全表扫描，因为此时使用索引的开销反而更大。

当然我们也可以通过 SQL 强制使用索引来解决这个问题。