MySQL创建索引与索引失效场景

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，一般以表的id字段为主键

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 );

普通索引

可以加速查询，但不能约束数据唯一性，可以在查询和插入操作的时候使用普通索引来提升性能

create index 索引名 on 表名(列名);
create index 索引名 on 表名(列名(长度));
如果是CHAR,VARCHAR类型，length可以小于字段实际长度，此时可省略不指定
如果是BLOB 和 TEXT 类型，必须指定length，

唯一索引

会强制保证数据的唯一性，允许有空值。如果是组合索引，则列值的组合必须唯一，再次插入该列的相同数据时会报错

create unique index 索引名 on 表名(列名);
create unique index 索引名 on 表名(列名(长度));
如果是CHAR,VARCHAR类型，length可以小于字段实际长度，此时可省略不指定
如果是BLOB 和 TEXT 类型，必须指定length，

普通组合索引

多个字段组合在一起组成一个索引，类似普通索引，加速查询，但不能约束数据唯一性

create index 索引名 on 表名(列名1,列名2);

唯一组合索引

多个字段组合在一起组成一个索引，但这几个字段组合在一起可以约束数据唯一性，再次插入这几个列组合相同数据时会报错

create unique index 索引名 on 表名(列名1,列名2);

explain

id

这一行说明了sql执行的顺序，在 join 查询或子查询时，通过这个参数可以很清晰的看到 mysql 是通过怎样的顺序执行我们给定 sql 的。
注：id值越大，说明执行的顺序越靠前

select_type

说明了执行这条sql时的查询类型

    SIMPLE ：简单查询，不包含子查询或Union查询
    PRIMARY：最外面的SELECT
    SUBQUERY ：包含子查询
    DERIVED ：在FROM列表中包含的子查询被标记为 DERIVED
    UNION ：包含 union 查询

type

最重要的分析字段之一，下面是性能由最差到最好
在阿里巴巴要求，sql 性能优化的目标至少要达到 range 级别

ALL：遍历全表以找到匹配行
INDEX：和 ALL 一样，都是全表扫描，区别是 index 扫描表时是按索引次序进行而不是行
range：只搜索给定范围的行，通常出现在 in、between、<>
index_merge：表示使用了索引合并(对多个索引分别进行条件扫描，然后将它们各自的结果进行合并)
ref_or_null：类似ref，但是可以搜索值为NULL的行
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
eq_ref ：唯一性索引扫描，(在使用主键或唯一性索引查找时看到，最多只返回一条记录)
const：只通过索引，就找到结果了(不用再去数据表中扫描了)
null：在优化阶段分解查询语句，在执行阶段用不着再访问表或索引(常见于只进行min或max 查询)

table

说明数据来自哪张表

partitions

匹配的分区

possible_keys

对于建索引有参考价值,可能在这个sql查询中使用的索引

key

说明这条sql查询实际使用的索引

key_len

索引字段最大的可能长度

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

filtered

查询的行数占数据表总行数的百分比

Extra

不适合在其它列中显示，但十分重要的额外信息

Using filesort：MySQL 对结果使用一个外部索引排序，而不是按照数据表本身的索引排序
Using index：使用了覆盖索引(只通过索引就查到结果集了),避免访问了表的数据行，效率不错
Using temporary：使用了临时表保存中间结果，常见于 order by 和 group by
Using where：使用了where条件
Using join buffer：使用了连接缓存

索引失效场景

1、字段类型不一致，发生了隐式转化

示例：id主键索引，s_name, age 各有一个索引
-- 未命中索引
explain select id, s_name, age from student where s_name = 100;
-- 命中索引
explain select id, s_name, age from student where s_name = '100';

表中s_name字段类型为varchar，但查询时用的是int，会发生类型转化，因此查询不走索引

2、查询中包含 or

示例：id主键索引，s_name, age 各有一个索引，create_by没有索引
-- 不走索引
explain select id, s_name, age from student where s_name = '100' or create_by = 'admin';
create_by未创建索引，当查询语句where后过滤条件包含该字段不走索引；

-- 走索引
explain select id, s_name, age from student where s_name = '100' or age = 18;
s_name, age有各自的索引，查询语句会将索引合并，参考explain执行结果type字段的值：index_merge

3、like通配符 % 的错误使用

示例：id主键索引，s_name, age 各有一个索引
-- 不走索引
explain select id, s_name, age from student where s_name like '%20';
-- 不走索引
explain select id, s_name, age from student where s_name like '%20%';
以上两种情况均为通配符 % 在前面

-- 走索引，取消在前面的通配符 % 
explain select id, s_name, age from student where s_name like '20%';

-- 走索引，注意这里只查询了一个字段，且是where后过滤的字段
explain select s_name from student where s_name like '%20%';

4、联合索引最左匹配原则

最左原则：

假设组合索引为：a,b,c
当SQL中对应有：a、或者a，b、或者a，b，c的时候，可称为完全满足最左原则；
当SQL中查询条件对应只有a，c的时候，可称为部分满足最左原则；
当SQL中没有a的时候，可称为不满足最左原则。

注：MySQL5.7开始，会自动优化，如：会把c，b，a优化为a，b，c使之完全遵循最左原则；会把c，a优化为a，c，使之部分遵循最左原则。即：SQL语句中的对应条件的先后顺序无关。

示例：s_name, age两个字段创建普通组合索引
-- 走索引 遵循最左原则
explain select id, s_name, age, create_by, create_time from student where s_name = 'zs';
-- 走索引 遵循最左原则
explain select id, s_name, age, create_by, create_time from student where s_name = 'zs' and age = 20;
-- 不走索引 没有遵循最左原则
explain select id, s_name, age, create_by, create_time from student where age = 20;
-- 走索引 因为查询列为覆盖索引，但若查询列中加入一个没有索引的字段，则不走索引
explain select id, s_name, age from student where s_name = 'zs' or age = 20;

5、索引列使用mysql函数

示例：id主键索引，s_name, age 各有一个索引，create_by, create_time没有索引
-- 不走索引
-- substr(s_name,1,3) = 'zss'：将s_name列字符串从第一位截取到第三位，然后结果是 zss
explain select id, s_name, age, create_by, create_time from student where substr(s_name,1,3) = 'zss';
查询时使用了mysql内置的函数，导致了索引命中失败

6、索引列存在计算 （+ 、-、*、/）

示例：id主键索引，s_name, age 各有一个索引，create_by, create_time没有索引
-- 不走索引
explain select id, s_name, age, create_by, create_time from student where age - 1 = 19;
查询条件中包含索引列计算，导致索引未命中

7、使用!= 、<>、not in 可能会导致索引失效

注意：是可能会使索引失效，不是绝对，当前尚未遇到该情况

8、使用is null 、is not null 导致索引失效

(1)若绝大多数行都是非null，则查询is null 走二级索引，查询is not null走全表扫描；
(2)若绝大多数行都是null，则查询is not null走索引，is null 也走索引；

9、左连接或右连接字段编码不一致

例如：表一有s_name字段，表二有s_name字段，且在各自的表里该字段都建立了索引，当两个表根据s_name字段做左连接或右连接时，如果这个字段在各自的标中字符编码不一致时，索引不会生效，若想使索引生效，将字符编码改为一致即可

10、group by 未遵循最左匹配原则

示例：s_name, age两个字段创建普通组合索引
-- 不走索引
explain select id, s_name, age, create_by, create_time from student group by age;
-- 不走索引
explain select ANY_VALUE(s_name),age from student group by age;

使用group by 进行分组的字段未遵循最左匹配原则，索引将失效

此处延伸出个问题：
如果你的MySQL版本大于等于 5.7，你会发现上面第一条语句可能执行失败
从 MySQL 5.7.5 开始，默认 SQL 模式包括 ONLY_FULL_GROUP_BY。 （在 5.7.5 之前，MySQL 不检测函数依赖，并且默认不启用 ONLY_FULL_GROUP_BY）这可能会导致一些sql语句失效。
解决办法：
1、要么像上述第二个语句，将查询的另一个字段放入ANY_VALUE()中，分组字段可不放入，但是只能放进一个字段，若有其余需要查询的字段就不能用了；
2、编辑MySQL配置文件：
windows：
编辑 mysql 配置文件 my.ini，在尾部添加以下内容，重新启动 mysql 即可：

[mysql] 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

linux：
辑 /etc/my.cnf 文件，在尾部添加以下内容，重新启动 mysql 即可：

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

11、两个字段对比导致索引未命中

示例：id为主键索引，age为普通索引
-- 不走索引
explain select id, s_name, age, create_by, create_time from student where age > id;

12、范围查找索引失败

如果查找的数据通过索引查找超出全表的10%-30%，DBMS发现全表扫描比走索引效率更高，
因此就放弃了走索引，而使用全表扫描