MySQL索引——分类、何时使用、何时不使用、何时失效

1、分类

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。

（1）普通索引：create index index_name on table(column)；

或者创建表时指定，create table(..., index index_name column);

（2）唯一索引：类似普通索引，索引列的值必须唯一（可以为空，这点和主键索引不同）

create unique index index_name on table(column)；或者创建表时指定unique index_name column

（3）主键索引：特殊的唯一索引，不允许为空，只能有一个，一般是在建表时指定primary key(column)

（4）组合索引：在多个字段上创建索引，遵循最左前缀原则。alter table t add index index_name(a,b,c);

   https://blog.csdn.net/xing_____/article/details/78826508

（5）全文索引：主要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合match against使用，现在只有char，varchar，text上可以创建全文索引。在数据量较大时，先将数据放在一张没有全文索引的表里，然后再利用create index创建全文索引，比先生成全文索引再插入数据快很多。

2、何时使用索引

MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。

（1）主键，unique字段；

（2）和其他表做连接的字段需要加索引；

（3）在where里使用＞，≥，＝，＜，≤，is null和between等字段；

（4）使用不以通配符开始的like，where A like 'China%'；

（5）聚集函数MIN()，MAX()中的字段；

（6）order by和group by字段；

3、何时不使用索引

（1）表记录太少；

（2）数据重复且分布平均的字段（只有很少数据值的列）；

（3）经常插入、删除、修改的表要减少索引；

（4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；

（5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

4、索引何时失效

（1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；

（2）like未使用最左前缀，where A like '%China'；

（3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；

（4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）

（5）如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换）；

（6）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

5、explain语句

  type字段为All，未使用索引；为ref，使用索引

  ALL                  全表扫描

  index                索引全扫描

  range               索引范围扫描，常用语<,<=,>=,between等操作

  ref                    使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

  eq_ref               类似ref，区别在于使用的是唯一索引，使用主键的关联查询

  const/system    单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

  null                   MySQL不访问任何表或索引，直接返回结果



  还有key字段表示用到的索引，没有用到为null

6、EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。这个列表是在优化过程的早期创建的，因此有些列出来的索引可能对于后续优化过程是没有用的。

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

如果该索引没有出现在possible_keys中，那么mysql选用的是出于另外的原因——例如，他可能选择了一个覆盖索引，哪怕没有where字句，换句话说，possible_keys揭示了哪一个索引能有助于提高查询效率，而key显示的是优化采用哪一个索引可以最小化查询成本。

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

Type列的解释

Type：告诉我们对表使用的访问方式，主要包含如下集中类型。

all：全表扫描。

const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。

eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。

fulltext：进行全文索引检索。

index：全索引扫描。

index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。

index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。

rang：索引范围扫描。

ref：Join语句中被驱动表索引引用的查询。

ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。

system：系统表，表中只有一行数据；

unique_subquery：子查询中的返回结果字段组合是主键或唯一约束。

Extra字段解释

Extra：查询中每一步实现的额外细节信息，主要会是以下内容。

Distinct：查找distinct 值，当mysql找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。

Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。

Range checked for each record (index map: N)：通过 MySQL 官方手册的描述，当 MySQL Query Optimizer 没有发现好的可以使用的索引时，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或 index_merge访问方法来索取行。

SELECT tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。

Using filesort：当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。

Using index：所需数据只需在 Index 即可全部获得，不须要再到表中取数据。

Using index for group-by：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。

Using temporary：当 MySQL 在某些操作中必须使用临时表时，在 Extra 信息中就会出现Using temporary 。主要常见于 GROUP BY 和 ORDER BY 等操作中。

Using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息。

Using where with pushed condition：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还须要通过打开 Condition Pushdown 优化功能才可能被使用。控制参数为 engine_condition_pushdown 。

Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。

No tables：Query 语句中使用 FROM DUAL或不包含任何 FROM子句。

Not exists：在某些左连接中，MySQL Query Optimizer通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数。