一、测试准备
MySQL5.7 简单的创建一个表并准备几条测试数据
CREATE TABLE `t_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`tel` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t_user`(`id`, `name`, `tel`, `age`) VALUES (1, 'Alice', '13302546589', 30);
INSERT INTO `t_user`(`id`, `name`, `tel`, `age`) VALUES (2, 'Rock', '15203256984', 20);
INSERT INTO `t_user`(`id`, `name`, `tel`, `age`) VALUES (3, 'Amy', '15863257894', 22);
二、测试一下几种情况,索引是否失效
给tel字段添加索引
ALTER TABLE `t_user` ADD INDEX idx_tel (`tel`);
正常情况
EXPLAIN SELECT * FROM t_user WHERE tel = '13302546589';
显示使用索引
- 隐性类型转换,即where条件tel字段不加单引号
EXPLAIN SELECT * FROM t_user WHERE tel = 13302546589;
key为null,索引失效
- 使用 like 模糊查询
1)%通配符在后面
EXPLAIN SELECT * FROM t_user WHERE tel like '13302546%';
索引没有失效
2)%通配符在前面
EXPLAIN SELECT * FROM t_user WHERE tel like '%02546589';
索引失效
- 使用 in 查询
1)in 里面只有一个
EXPLAIN SELECT * FROM t_user WHERE tel in ('13302546589')
索引没有失效
2) in 里面有多个
EXPLAIN SELECT * FROM t_user WHERE tel in ('13302546589','15203256984');
索引失效
- 使用 or
EXPLAIN SELECT * FROM t_user WHERE tel = '13302546589' OR age = 30;
索引失效
- 使用函数
EXPLAIN SELECT * FROM t_user WHERE MD5(tel) = '3546b734c57ba7f77f4fae80bf3d9069';
索引失效