其它MySQL 面试系列：
MySQL 面试系列：一条select语句在MySQL是这样执行的?
MySQL 面试系列：MySQL 常见的开放性问题
MySQL 面试系列：MySQL 性能优化 & 分布式
MySQL 面试系列：MySQL 命令和内置函数
MySQL 面试系列：MySQL 中日志的面试题总结
MySQL 面试系列：MySQL 中锁的面试题总结
MySQL 面试系列：MySQL 事务的面试题总结
MySQL 面试系列：MySQL 索引的面试题总结
MySQL 面试系列：MySQL 基础模块的面试题总结
在面试中经常会被问到：如何对 MySQL 的查询语句进行优化。查询优化本就不是一蹴而就的，需要学会使用对应的工具、借鉴别人的经验来对SQL进行优化，并且提升自己。
下面是根据个人经验及网络相关知识整理的查询语句优化的各个方面，希望对你有所帮助。
前言
先来巩固一下索引的优点，检索数据快、查询稳定、存储具有顺序性避免服务器建立临时表、将随机的 I/O 变为有序的 I/O。
但索引一旦创建的不规范就会造成以下问题，占用额外空间，浪费内存，降低数据的增、删、改性能。
所以只有在理解索引数据结构的基础上才能创建出高效的索引。
本文所有操作均在 MySQL8.0.12
一、创建索引规范
在学习索引优化之前，需要对创建索引的规范有一定的了解，此规范来自于阿里巴巴开发手册。
主键索引：pk_column_column
唯一索引：uk_column_column
普通索引：idx_column_column
二、索引失效原因
创建索引需知道在什么情况下索引会失效，只有了解索引失效的原因，在创建索引时才不会出现一些已知错误。
1.带头大哥不能死
这局经典的语句就是涵盖创建索引时一定要符合最左侧原则。
例如表结构为u_id，u_name，u_age，u_sex，u_phone，u_time
创建索引为idx_user_name_age_sex。
查询条件必须带上 u_name 这一列。
2.不在索引列上做任何操作
不在索引列上做任何计算、函数、自动或者手动的类型转换，否则会进行全表扫描。简而言之不要在索引列上做任何操作。
3.俩边类型不等
例如建立了索引 idx_user_name，name 字段类型为 varchar
在查询时使用 where name = kaka，这样的查询方式会直接造成索引失效。
正确的用法为 where name = "kaka"
4.不适当的like查询会导致索引失效
创建索引为 idx_user_name
执行语句为 select * from user where name like "kaka%"; 可以命中索引。
执行语句为 select name from user where name like "%kaka"; 可以使用到索引（仅在8.0以上版本）。
执行语句为select * from user where name like ''%kaka"; 会直接导致索引失效
5.范围条件之后的索引会失效
创建索引为 idx_user_name_age_sex
执行语句 select * from user where name = 'kaka' and age > 11 and sex = 1;
上面这条 sql 语句只会命中 name 和 age 索引，sex 索引会失效。
复合索引失效需要查看 key_len 的长度即可。
总结：%在后边会命令索引，当使用了覆盖索引时任何查询方式都可命中索引。
以上就是咔咔关于索引失效会出现的原因总结，在很多文章中没有标注 MySQL 版本，所以你有可能会看到is null 、or索引会失效的结论。
三、SQL优化杀手锏之 Explain
在写完 SQL 语句之后必须要做的一件事情就是使用 Explain 进行 SQL 语句检测，看是否命中索引。
下图就是使用 explain 输出格式，接下来将会对输出格式进行简单的解释。
image
1.id
这列就是查询的编号，如果查询语句中没有子查询或者联合查询这个标识就一直是1。
如存在子查询或者联合查询这个编号会自增。
2.select_type
最常见的类型就是 SIMPLE 和 PRIMARY ，此列知道就行了。
3. **table
理解为表名即可
4. type
此列是在优化 SQL 语句时最需要关注的列之一，此列显示了查询使用了何种类型。
以下排序从最优到最差。
-
system：表内只有一行数据
-
const：最多只会有一条记录匹配，常用于主键或者唯一索引为条件查询
-
eq_ref：当连接使用的索引为主键和唯一时会出现
-
ref：使用普通索引=或<=> 运算符进行比较将会出现
-
fulltext：使用全文索引
-
ref_or_null：跟ref类型类似，只是增加了 null 值的判断，实际用的不多。语句为 where name = 'kaka' and name is null，name 为普通索引
-
index_merge：查询语句使用了俩个以上的索引，常见在使用 and、or 会出现，官方文档将此类型放在 ref_or_null 之后，但是在很多的情况下由于读取索引过多性能有可能还不如 range
-
unique_subquery：用于where中的in查询，完全替换子查询，效率更高。语句为
value IN (SELECT primary_key FROM single_table WHERE some_expr) -
index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引
-
range：索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者 like 等运算符的查询中。
-
index：索引全表扫描，把索引从头到尾扫一遍
-
all：全表扫描,性能最差。
5.possible_keys
此列显示的可能会使用到的索引
6.**key
优化器从possible_keys中命中的索引
7.key_len
查询用到的索引长度（字节数），key_len 只计算 where 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len 中。
网友评论