REGEXP_LIKE 索引 like索引失效怎么办

一、like导致的索引失效

1、新建一张用户表并创建address索引

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT '',
  `age` tinyint(4) DEFAULT '0',
  `address` varchar(32) CHARACTER SET latin1 DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `IDX_address` (`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `user_info` VALUES ('1', 'a', '1', 'beijing');
INSERT INTO `user_info` VALUES ('2', 'b', '2', 'shanghai');
INSERT INTO `user_info` VALUES ('3', 'c', '3', 'tianjin');
INSERT INTO `user_info` VALUES ('4', 'd', '4', 'chongqi');
INSERT INTO `user_info` VALUES ('5', 'e', '5', 'guangzhou');
INSERT INTO `user_info` VALUES ('6', 'f', '6', 'shenzhen');
INSERT INTO `user_info` VALUES ('7', 'g', '9', 'hangzhou');
INSERT INTO `user_info` VALUES ('8', 'h', '9', 'wuhan');
INSERT INTO `user_info` VALUES ('9', 'i', '9', 'xiamen');
INSERT INTO `user_info` VALUES ('10', 'j', '8', 'suzhou');

2、通过explain对SELECT语句进行分析

a、普通条件查询

explain SELECT * FROM user_info where address = 'hangzhou';

b、全表扫描，索引失效

explain SELECT * FROM user_info where address like '%hangzhou%';

c、全表扫描，索引失效

explain SELECT * FROM user_info where address like '%hangzhou';

d、通过索引范围查询

explain SELECT * FROM user_info where address like 'hangzhou%';

e、全索引扫描，查询列与搜索条件列是索引列

explain SELECT address FROM user_info where address like '%hangzhou%';

f、全索引扫描，查询列与搜索条件列是索引列

explain SELECT address FROM user_info where address like '%hangzhou';

g、通过索引范围查询

explain SELECT address FROM user_info where address like 'hangzhou%';

所以like ‘%XXX’ 会导致索引失效，解决方法是使用覆盖索引，
除了like，还有一些可能会导致索引失效的原因
在索引列上做任何操作(如函数)
使用范围查询
使用select *
使用不等于(!= 或 <>)
使用is null 或 is not null

二、Explain简单了解

Explain可以对SELECT语句进行分析, 并输出SELECT执行的详细信息，关于详细信息的简单解释

1、id
SQL执行顺序的标识，SQL从大到小的执行

2、select_type
查询中每个select子句的类型，可能出现的值
SIMPLE(简单SELECT，此查询不包含 UNION 查询或子查询)
PRIMARY(此查询是最外层的查询)
UNION(此查询是 UNION 的第二或随后的查询)
…

3、table
查询的表名、别名、derivedx、null

4、type
访问类型(分析数据查询的重要依据)
常用类型：ALL < index < range < ref < eq_ref < const < system（从左到右，性能从差到好）
ALL：表示全表扫描, 这个类型的查询是性能最差的查询之一.
index：表示全索引扫描，仅扫描所有的索引, 而不扫描数据.
range：表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()操作中
ref：表示表的连接匹配条件
eq_ref：同ref，区别是使用的索引是唯一索引
system：表中只有一条数据，这个类型是特殊的const类型
const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据，const查询速度非常快，因为它仅仅读取一次即可
NUll：执行时不用访问表或索引

5、possible_keys
此次查询中可能选用的索引

6、key
此次查询中确切使用到的索引

7、key_len
表示使用了索引的字节数，可通过该列计算查询中使用的索引的长度

8、ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

9、rows
估算SQL要查找到结果集需要扫描读取的数据行数

10、Extra

性能从好到坏：Using index > Using where > Using temporary > Using filesort

Using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，说明性能不错

Using where：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，通常存在该值都建议优化

Using filesort：表示MySQL需额外的排序操作，不能通过索引顺序达到排序效果，通常存在该值都建议优化

文章仅作为个人学习整理