一叶知秋丿
程序员应该都知道,为了提高数据库的查询速度,我们可以对表上的一个字段或者多个字段建立索引,但是有些 SQL 错误的写法,可能会导致索引失效。
01. 查看执行计划
如何判断 SQL 的执行是做了全表扫描还是走了索引,不是凭感觉判断 SQL 执行的快慢,而是要看 SQL 的执行计划;很多工具都提供了查看执行计划的功能,不过最原始的方法,还是通过 explain 进行查看;下面的 SQL,是否使用的索引,一目了然。
1. 没有索引
explain select * from user where gender = 'M';
2. 有索引
explain select * from user where name = 'Tom';
02. 索引失效
1. 使用 like 时,% 在前面不走索引(在后面可以走索引);
explain select * from user where name like '%om';2. 数据类型出现隐式转化,比如我们这里手机号 mobile 字段设置的是 varchar 类型,但是查询的时候用的是数字,那么就【可能】不走索引。
explain select * from user where mobile = 13800000000;3. 在索引字段上使用 not,<>,!= ;
explain select * from user where mobile <> '13800000000';explain select * from user where mobile != '13800000000';
4. 对索引字段上使用函数;
explain select * from user where length(mobile) < 105. 联合索引,如果查询条件不满足最左匹配原则,则不会走索引;
6. or 会使索引失效,尽管 or 左右的条件都有索引;
explain select * from user where name = 'Tom' or mobile = '13800000000';
总之,MySQL 的索引优化和索引失效还是挺复杂的,主要体现在 MySQL 随着版本升级,有一些我们熟知的技巧可能会不再正确,我们现在认为一定会索引失效的 SQL 写法,可能会变成走索引,所以这也是为什么我在上文中,多次用到【可能】会造成索引失效的原因。
我将持续分享Java开发、架构设计、程序员职业发展等方面的见解,希望能得到你的关注。
会点代码的大叔
以 Mysql 为例,其中索引 BTree 类型 。以下几种SQL设计会导致虽然使用了索引,但是索引不会生效,即引擎放弃使用索引而进行全表扫描:
- WHERE 子句中使用 != 或 <> 操作符。
- WHERE 子句中对索引列使用 %前缀模糊查询。
- WHERE 子句中对索引列使用 OR 来连接条件。
- WHERE 子句中对索引列使用 NOT IN。
- WHERE 子句中对索引列使用计算、函数、类型转换等操作。
- WHERE 子句中对索引列使用参数。
软件测试开发技术栈
以下情况,MySQL的索引"失效"不可用
1、通过索引扫描的记录超过20%~30%,可能会变成全表扫描
2、联合索引中,查询条件不符合左侧前导要求
3、查询条件列最左以通配符%开始
4、查询条件发生数据类型隐式转换,或者字符集不匹配
5、HEAP表使用HASH索引时,使用范围检索或者ORDER BY
6、多表关联时,排序字段不属于驱动表,无法利用索引完成排序
7、JOIN查询时,关联列数据类型(字符集)不一致也会导致索引不可用
8、不可见索引,即便force index也不可用9、违反索引排序规则