or导致索引失效的解决方法_导致MySQL索引失效的几种常见写法

数据准备

先准备一些数据，方便测试

创建表结构CREATE TABLE USER(

idINT(5) UNSIGNED NOT NULLAUTO_INCREMENT,

create_timeDATETIME NOT NULL,

NAMEVARCHAR(5) NOT NULL,

ageTINYINT(2) UNSIGNED ZEROFILL NOT NULL,

sexCHAR(1) NOT NULL,

mobileCHAR(12) NOT NULL DEFAULT '',

addressCHAR(120) DEFAULT NULL,

heightVARCHAR(10) DEFAULT NULL,PRIMARY KEY(id),KEYidx_createtime (create_time) USING BTREE,KEYidx_name_age_sex (NAME,sex,age) USING BTREE,KEYidx_height (height) USING BTREE,KEYidx_address (address) USING BTREE,KEYidx_age (age) USING BTREE

) ENGINE=INNODB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;

添加数据INSERT INTO test.user(id, create_time, name, age, sex, mobile, address, height`) VALUES (1, '2019-09-02 10:17:47', '冰峰', 22, '男', '1', '陕西省咸阳市彬县', '175');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (2, '2020-09-02 10:17:47', '松子', 13, '女', '1', NULL, '180');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (3, '2020-09-02 10:17:48', '蚕豆', 20, '女', '1', NULL, '180');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (4, '2020-09-02 10:17:47', '冰峰', 20, '男', '17765010977', '陕西省西安市', '155');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (255, '2020-09-02 10:17:47', '竹笋', 22, '男', '我测试下可以储存几个中文', NULL, '180');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (256, '2020-09-03 10:17:47', '冰峰', 21, '女', '', NULL, '167');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (257, '2020-09-02 10:17:47', '小红', 20, '', '', NULL, '180');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (258, '2020-09-02 10:17:47', '小鹏', 20, '', '', NULL, '188');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (259, '2020-09-02 10:17:47', '张三', 20, '', '', NULL, '180');INSERT INTO test.user(id, create_time, NAME, age, sex, mobile, address, height) VALUES (260, '2020-09-02 10:17:47', '李四', 22, '', '', NULL, '165');

导致索引失效

单个索引

1、使用!= 或者 <> 导致索引失效

SELECT * FROM user WHERE name != '冰峰';

可以通过分析SQL看到，type类型是ALL，扫描了10行数据，进行了全表扫描。<>也是同样的结果。

2、类型不一致导致的索引失效

注意：设计表字段的时候，千万、一定、必须要保持字段类型的一致性，啥意思？比如user表的id是int自增，到了用户的账户表user_id这个字段，一定、必须也是int类型，千万不要写成varchar、char什么的骚操作。

SELECT * FROM user WHERE height= 175;

可以通过分析SQL看到

上面这个索引失效是因为：这个SQL诸位一定要看清楚，height表字段类型是varchar，但是我查询的时候使用了数字类型，因为这个中间存在一个隐式的类型转换，所以就会导致索引失效，进行全表扫描。

对比一下

SELECT * FROM USER WHERE NAME = '张三' AND height = '175';

可以通过分析SQL看到

3、函数导致的索引失效

如果你对列进行了(+，-，*，/，!), 那么都将不会走索引。

SELECT * FROM USER WHERE age - 1 = 20;

结果如下：

5、OR引起的索引失效(特殊情况下：即不是同一个索引字段)

SELECT * FROM user WHERE name = '张三' OR height = '175';

注意：OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效

结果如下：

6、模糊搜索导致的索引失效

SELECT * FROM USER WHERE NAME LIKE '%冰%';

结果如下

比较一下：

SELECT * FROM USER WHERE NAME LIKE '冰%';

7、NOT IN、NOT EXISTS导致索引失效

SELECT s.* FROM user s WHERE NOT EXISTS (SELECT * FROM user u WHERE u.name = s.name AND u.name = '冰峰')

结果如下:

SELECT * FROM user WHERE name NOT IN ('冰峰');

结果如下：

复合索引

注意在测试复合索引时 要测试之前，删除其他的单列索引。

1、最左匹配原则

EXPLAIN SELECT * FROM USER WHERE NAME = '冰峰' AND sex = '男';

啥叫最左匹配原则，就是对于符合索引来说，它的一个索引的顺序是从左往右依次进行比较的