索引列上做操作导致索引失效
通常我们认为只要建立索引就可以万事大吉,以为只要建立就一定会使用到,可其实在索引列上的计算、函数、类型转换都可能导致索引失效,所以我们不仅要会创建索引,更重要的是如何正确的使用索引,且不让索引失效
查看表索引
mysql>show index from user;
复合索引,索引顺序name、age、pos
我们看到user表中创建的是一个符合索引,索引顺序name、age、pos
案例分析
举例:两条sql语句查询出来的结果是一样的,但实际在mysql底层执行过程中效率确实完全不一致的
使用explain查看执行结果
mysql>explain select * from user where name="tim";
ref解释:表里面哪些字段走了索引(如果索引是主键或者唯一索引的话,会显示 const),最后选出了数据(选择行,一行一行的数据),连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。
结论:从执行结果看,我们明显的使用到了索引(type=ref),且检索的行为1行,用到的ref为常量const
使用explain查看在所有列上计算的执行结果
mysql>explain select * from user where left(name,3)="tim";
结论:我们看到执行结果是没有使用到索引且是全表扫描(type=All),我们看到ref为NULL
建议:
1、作为一个资深的程序员,为了能和DBA配合默契,在实际生产环境中,我们写的每一条sql语句都需要我们自己explain查看执行结果,写出高效且执行效率最高的sql语句。
2、另外如果我们知道如何优化mysql且说出自己在实际生产环境中优化的案例,在面试过程中也会是加分项,这样有助于我们拿到高薪。