一.背景
根据第一节索引的原理分析，常见一些优化建议不走索引的原因。
通过这些案例的分析，我们可以做到自己识别是否走索引。
二. 分析常见不走索引的写法
1>. 查询条件中含有函数或表达式
正常走索引:select * from t_user where id=2+1
条件字段中带有函数或表达式:select * from t_user where id+1=2
分析原因：
字段经过函数或表达式处理后返回的是一个新的值，这个值与索引中的数据已经是两个东西，所以没有办法去索引中寻找。至于数据库对表达式这种未做优化，可能还是希望开发人员尽可能的表达式在值中不要在字段中。
2>.隐式转换(本质还是1)
这个容易被忽略也是最常见的。
正常情况：select * from t_user where age='2' （age是字符类型普通索引）
不走索引情况：select * from t_user where age=2 (这里mysql做了隐式转换)
分析：
隐式转换的本质还是对字段使用了函数，select * from t_user where age=2 这个语句会被转换为：select * from t_user where to_number(age)=2 执行。
这里需要注意的是：对id值是否带引号，都走索引。
原因是：对字段未做隐式转换，这种场景是对后面的值隐式转换为浮点型。
ps：
mysql隐式转换总结如下：
不同类型全都转换为浮点型。
如果字段是字符，条件是整型，那么会把表中字段全都转换为整型。
3>.OR一个未索引的字段
分析：
因为or了一个未索引的字段，即使条件有id，但是还是需要全表扫描。
4>.is null & is not null
not null字段都不走索引讨论无意义，这里只讨论可null字
分析：
is null和is not null 根据数据量和null值的数量不同以及返回的结果是否不需要回查数据，可能会走索引。mysql会估算全表扫描和搜索引的代价来选择是否走。
5>.最左前缀匹配
这个比较好理解，因为组合索引是按照顺序存储的。匹配不到前面的没法找。而且唯一性高的放在前面。
分析：这一点结合索引的存储结构就可以理解，所以理论知识比一些总结的经验更实在。
6>.order by
同4，会根据代价看是否走。
不走的情况：
1>.用来查找结果的索引(key2) 和 排序的索引(key1) 不一样.
2>.排序字段在不同的索引中，无法使用索引排序.
3>.排序字段顺序与索引中列顺序不一致，无法使用索引排序，比如索引是 key idx_kp1_kp2(key_part1,key_part2).
4>.order by中的升降序和索引中的默认升降不一致无法使用索引排序.
5>.ey_part1是范围查询，key_part2无法使用索引排序.
6>.对于还有join的关联查询，排序字段并非全部来自于第一个表.
附 Mysql select执行过程.
1. 客户端发送一条查询给服务器；
2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5. 将结果返回给客户端。
查询处理的顺序如下:
1）FROM：对FROM子句中的左表和右表执行笛卡儿积（Cartesian product），产生虚拟表VT1。
2）ON：对虚拟表VT1应用ON筛选，只有那些符合的行才插入虚拟表VT2中。
3）JOIN：如果指定了OUTER JOIN（如LEFT OUTERJOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止。
4）WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合的记录才**入虚拟表VT4中。此时数据还没有分组，所以不能在where中出现对统计的过滤
5）GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5。在该阶段会将NULL值分到同一个分组中。
6）CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生表VT6。
7）HAVING：对虚拟表VT6应用HAVING过滤器，只有符合的记录才插入虚拟表VT7中。
8）SELECT：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中。
9）DISTINCT：去除重复数据，产生虚拟表VT9。
10）ORDER BY：将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10。如果不指定排序，数据并非总是按照主键顺序进行排序的。NULL被视为最小值
11）LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户。LIMIT n, m的效率是十分低的,一般可以通过在where条件中指定范围来优化 where id> ? limit 10
附：Explain命令解释
explain命令是查看查询优化器（Optimizer）是如何执行查询语句的。
explain输出字段
id select_type table type possible_keys key key_len ref rows Extra
id ：来体现执行顺序。单张表该值为1，多张表会出现多条记录，id值越大越先被执行,id相同时执行顺序由上至下。
select_type ：
simple：查询中不包含子查询或者union
SUBQUERY：包含在SELECT列表中的子查询中的SELECT(不在From字句中)
UNION：中的第二个或后面的SELECT语句,UNOIN中的第一个SELECT显示为PRIMARY。
PRIMARY:查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
DERIVED:包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。
UNION RESULT:用来从UNION的匿名临时表检索结果的SELECT被标记为UNION SELECT。
table ：显示这一行数据正在访问哪张表，若在查询中为select起了别名，则显示别名，如果为(x是个数字,可以理解为第几步执行的结果)
type：访问类型
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：这是const类型的特例，很少出现，可以忽略不计
const：表示通过索引一次就找到了(唯一索引或只有一条数据的索引)。
eq_ref：通过唯一索引扫描整个表。
ref：非唯一性索引扫描
range：where语句中出现了bettween、<、>、in等的查询
index：index与ALL区别为index类型只遍历索引树。
ALL: 全表扫描。
possible_keys
查询可以使用哪些索引。
keys
MySQL实际采用哪个索引。
key_len
使用的索引的长度，越短越好
key_len的长度计算公式：
varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
ref
如果索引的那一列被使用了，如果可能，是一个常量const
rows
大致估算出找到所需的记录所需要读取的行数
Extra
Using index: 从索引中就可以查询到最终需要的信息，不需要再读取表。
Using where ：表示优化器需要通过索引回表查询数据
Using temporary: 对查询结果排序时使用了临时表，常见于order by 和 group by
Using filesort: MySQL中无法利用索引完成的排序操作称为”文件排序”
网友评论