索引失效的几种情况

常见的有如下几种情况

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '名字',
  `sex` tinyint(5) DEFAULT '0' COMMENT '性别',
  `address` varchar(255) DEFAULT NULL COMMENT '住址',
  `card_id` varchar(18) DEFAULT NULL COMMENT '身份证',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `card_id` (`card_id`) USING BTREE,
  KEY `age` (`age`) USING BTREE,
  KEY `name_and_address` (`name`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

1 对于使用 like 查询， 查询如果是 ‘%aaa’ 不会使用索引，而 ‘aaa%’ 会使用到索引。

explain
select * from user where name like "%meiyu"

explain
select * from user where name like "meiyu%"

2 对于索引字段如果发生类型自动转化也会失效

--card_id 为varchar类型
explain
select * from user where card_id ='421127199604124781'  --索引有效
select * from user where card_id =421127199604124781 --索引失效

注意：字符串类型要加引号

3. 如果条件中有 or， 有条件没有使用索引，即使其中有条件带索引也不会使用。

--card_id 有索引 address没有索引
explain
select * from user where card_id ='421127199604124781'  or address = '深圳龙岗'

4对列进行计算或者是使用函数，则该列的索引会失效

explain
select * from user where age+10 = 18

5联合索引遵循最左匹配原则

KEY `name_and_address` (`name`,`address`) USING BTREE

--有效
explain
select * from user where name = 'huangyaoshi' and address = '桃花岛'
--有效
explain
select * from user where name = 'huangyaoshi' 
--无效
explain
select * from user where address = '桃花岛'

注意 就是最左侧name字段必须要有