MySQL索引

一、什么是索引和为什么要使用索引

先来个例子，如果我们需要从一本新华字典中的查找某个字的解释。首先通过目录查询该字的页数，再通过页数查找到对应的字解释。那如果我们没有目录的情况下，我们怎么查询字的解释呢？只能从书的头页往尾页进行查询相符的字解释。从这个例子中我们可以知道通过目录可以提高我们的查询速度，而索引就相当于表中的目录。通过索引可以快速查找到对应的数据。索引是一种特许的文件，而这些文件是存放数据表里所有的数据的引用指针。当表中的数据量很大时，没有索引的情况下，就遍历表中的所有数据进行筛选符合的数据。而有了索引我们就可以像上面的例子那样能快速查找到对应的数据。

二、MySQL索引的类型

1.普通索引

普通索引:没有任何限制的基本索引，通常由关键字KEY或INDEX定义的索引，普通索引主要是加快对数据的访问速度。而这种索引经常出现在查询条件或排序条件的数据列中创建索引。

2.唯一索引

唯一索引与普通索引类似，但是唯一索引是有限制，就是索引值必须唯一，而唯一可以保证数据记录的唯一性。在实际的开发中我们创建唯一索引的目的往往不只是为了提高访问速度，而只是为了避免数据出现重复。

3.全文索引（FULLTEXT）

对于文本字段上的普通索引,只是对字段中最前面的字符串进行快速访问。而对于较大段文字的情况下。如果需要处理的数据量很大的情况下，会造成响应时间长。而对于这种较大段文字的字段的索引，Mysql提供了全文索引。而全文索引仅可用于 MyISAM 表。而对于InnoDB数据表不支持全文索引。全文索引在生成这种类型的索引时，MySQL会把在文本中出现的所有词组创建一份目录，查询时根据目录去检索有关的数据记录。不过切记对于大容量的数据表，在生成全文索引时非常消耗时间和消耗硬盘空间。

4.单列索引、多列索引

• 单列索引也就是说只有一个字段的索引

单列索引
   ALTER TABLE tab ADD INDEX index_a (index_a) ;

• 而所列索引就是由多个字段组成的索引。

多列索引
   ALTER TABLE tab ADD INDEX la_b_c (index_a,index_b,index_c) ;

在多列索引中，索引是有序的，因为mysql中 myisam，innodb默认使用的是 Btree索引，至于btree的数据结构是怎样的都不重要,只需要知道结果，既然是索引那这个数据结构最后是排好序,这种有序的组合索引可以这样理解，比如 (index_a,index_b,index_c) ，abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的。因为有序那么匹配规则也会从左开始。
多列索引匹配规则 :
1.全字段匹配，这种情况下符合所有的索引因此匹配上，
select * from tab where index_a = "a" and index_b ="b" and index_c = "c"
index_a、index_b、index_c都作为查询条件的时候，匹配就与顺序无关。
select * from tab where index_b ="b" and index_a = "a" and index_c = "c"
2.匹配部分最左前缀,这种情况下index_b没有匹配上，因此在index_b断点，只匹配了index_a，而index_c没有匹配上。
select * from tab where index_a = "a" and index_c = "c"

在mysql中执行查询时，只能使用一个索引，如果我们在index_a,index_b,index_c上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

• 单列索引和多列索引有很大的区别。如果使用多列索引，where条件中字段的顺序非常重要，需要满足最左前缀列。

5.组合索引

组合索引意指存在两组或以上的索引，例如建立一个组合索引：ALTER TABLE tab ADD INDEX index_titme_time (index_a,index_b,index_c)。建立这样的组合索引，其实是相当于分别建立了(index_a,index_b,index_c )、(index_a,index_b)、(index_a)组合索引。我们就会有疑问为什么没有建立（index_b,index_c ）或者（index_b）等的组合呢？是因为MySQL组合索引“最左前缀”的结果。

三、MySQL索引优化

• 使用短索引
  对于字符的字段，如果字段过长的情况下，查询是非常耗时的。而对于这种过长的字符查询我们应该使用 索引字符串值的前缀（prefixe）进行处理。例如有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间。

• 尽量全值匹配
  在使用使用索引的时候，如果我们创建了索引A,B,C，那么我们尽可能的选择所有索引匹配为查询条件。

• 最佳左前缀原则
  使用索引的时候尽可能使用我们定义的索引的列从左匹配。

例如我们创建了索引A,B,C
select * from table where A = 1,B=2,C=3。这时候索引是会生效的
select * fron table where A =1 ,C=3 ,这个时候我们C索引是不会生效的。因为我们创建的索引是从(A,B,C)，如果B索引不存在，C索引是不会生效的
select * from table where  A=1 B>2,C=4,这时候我们的B索引是不会生效的，因为范围查询是会令索引失效的。

• 使用索引列排序
  在使用索引查询的时候，如果where条件中只有索引字段的情况下，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• like语句操作
  在使用like进行查询的时候一般情况下,如果使用like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

• 索引不能包含有NULL值的列
  在查询的时候索引值包含NULL或者在组合索引中包含NULL值的情况下，索引是无效的。

• 符合多列索引规则
  在使用多列索引的情况下，要符合多列组合索引的生效规则。

*创建多列索引规则
在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

*char或者varchar的索引查询
char或者varchar的索引查询， 要使用单引号或者双引号，如果没有时间单引号或者双引号会导致索引失效

• OR使用union替代
  在使用OR查询的时候尽可能的使用Union进行替代，因为OR属于范围查询，范围查询是会令索引失效。但是我们可以通过索引覆盖来保证索引的生效。

例如：索引A
 select * from table  where a ==2 or a==0 这个属于范围查询，因此索引不生效。
select a from table where a==2 or a ==0 因为使用了覆盖索引，因此索引是会生效的。
select * from table where a ==2 
union 
select * from table where a==0; 这种方式索引是会生效的。

Reference：
https://www.cnblogs.com/codeAB/p/6387148.html
http://feiyan.info/16.html
http://blog.csdn.net/zmx729618/article/details/52701532