mysql索引失效_Mysql 优化——索引失效（实战，截图集锦）

索引失效：

1. 组合索引的最左前缀原则
2. 隐式转换
3. 有or必全有索引
4. like以%开头
5. where中索引列有运算
6. where中索引列使用了函数
7. 如果mysql觉得全表扫描更快时(数据少)

本文主要记录：最左前缀 和 隐式转化；

最左前缀：

为name、age、phone 创建索引：

where name = 'W' and age = ' 12 ' and phone = ' 18888886666 '

可以 (最左)

where name = 'W' and age = ' 12 '

可以(最左)

where name = 'W'

可以(最左)

where name = 'W' and phone = ' 18888886666 '

可以(最左)

where phone = ' 18888886666 ' and name = 'W' and age = ' 12 '

可以(优化器，调整where 顺序)

where age = ' 12 ' and phone = ' 18888886666 ' and name = 'W'

可以(优化器，调整where 顺序)

详情见：

隐式转化：

CREATE TABLE `test_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, `sex` tinyint(1) NOT NULL COMMENT '1 男 2 女', `phone` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_phone` (`phone`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

字段名称： phone

字段类型：varchar(20)

索引：idx_phone

---

看图说话：

SQL 一：

explainselect * from `test_user` where phone = '18866668888'

执行：

​

结果：命中

SQL 二：

explainselect * from `test_user` where phone = 18866668888

执行：

​

结果：未命中，全表扫描

WHY：

对比两条SQL，区别在于 where 条件中，是否使用引号包含 value 值。

因为phone 字段的类型为 varchar，即字符型，如果SQL 中使用 整型，那么将触发类型转换。同样的类型才能比较。

SQL 2 中，phone 字段中的所有值 将改为 整型比较

例：

​

ID1 数字开头的，截取到非数字的位置

验证：SQL：

​

结果：居然查出来了，不要吃精。验证了上边的说法。

ID2 字符开头的，则直接为 0

​

结果：居然又查出来了，不要吃精。验证了上边的说法。

这就是 隐式转换。

但是，要是

CREATE TABLE `test_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, `sex` tinyint(1) NOT NULL COMMENT '1 男 2 女', `phone` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_phone` (`phone`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

字段类型为 int，

那么

​

都可以命中索引。

最终结论：

如果字段类型，是非整型(即字符型)，那么注意where 条件的匹配条件

• 整型，不命中索引(隐式转化)
• 字符型，命中索引(类型一致)

如果字段类型，是整型，那么两种场景都能命中索引。

左手代码，右手诗，趣味学习新知识