什么情况下,mysql创建了索引,但不被命中?

2020-05-01

在测试之前,先来了解mysql explain使用方法。

使用 explain 可以检查你的sql语句是否用到了索引。

id选择标识符
select_type表示查询的类型
table输出结果集的表
partitions匹配的分区
type表示表的连接类型
possible_keys表示查询时,可能使用的索引
key表示实际使用的索引
key_len索引字段的长度
ref列与索引的比较
rows扫描出的行数(估算的行数)
filtered按表条件过滤的行百分比
Extra执行情况的描述和说明

创建测试表

CREATE TABLE `user` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL,
	`password` VARCHAR(50) NOT NULL,
	`age` TINYINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `Index 2` (`username`, `password`)
)
ENGINE=InnoDB
;

开始测试了

1,如果条件中有 or ,即使其中有条件带索引也不会使用索引。

EXPLAIN
SELECT *
FROM USER
WHERE username = 'admin' OR username = 'test'

2,like查询是以%开头,索引不会命中。

EXPLAIN
SELECT *
FROM USER
WHERE username like '%admin'

3,如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。

EXPLAIN
SELECT *
FROM USER
WHERE username = 123

4,没有查询条件。

EXPLAIN
SELECT *
FROM USER

5,查询条件没有建立索引。

EXPLAIN
SELECT *
FROM USER
WHERE age = 20

6,查询条件中,在索引列上使用函数(+, - ,*,/)。

EXPLAIN
SELECT *
FROM USER
WHERE username=100+23

7,采用 not in, not exist,不等于等查询语句。

EXPLAIN
SELECT *
FROM USER
WHERE username != 'admin'

8,联合索引最左原则。

使用到了索引

EXPLAIN
SELECT *
FROM USER
WHERE username = 'admin' and password='123456'
EXPLAIN
SELECT *
FROM USER
WHERE PASSWORD='123456' AND username = 'admin'

未使用到索引

EXPLAIN
SELECT *
FROM USER
WHERE PASSWORD='123456'