记录Mysql索引类型，索引生效和失效场景

记录Mysql索引类型，索引生效和失效场景

创建数据表

DROP TABLE IF EXISTS staff;
CREATE TABLE staff (
	id BIGINT(22) not null auto_increment,
	name VARCHAR(22) COMMENT '姓名',
	age TINYINT(3) COMMENT '年龄',
	pos VARCHAR(22) COMMENT '职位',
	salary DOUBLE COMMENT '薪水',
	PRIMARY KEY(`id`)
)ENGINE=INNODB CHARSET=utf8;

创建索引

CREATE INDEX `multi_index` on staff(name,age,pos);

插入测试数据

对比执行sql语句

-- Case#1：两边都是%
EXPLAIN SELECT * FROM staff WHERE name LIKE '%Alice%';
-- 索引失效 type=all

-- Case#2：左边是%
EXPLAIN SELECT * FROM staff WHERE name LIKE '%Alice';
-- 索引失效 type=all

-- Case#3：右边是%
EXPLAIN SELECT * FROM staff WHERE name LIKE 'Alice%';
-- 索引生效 type=range

从结果可以看出：当使用like模糊匹配时，只有%写在右边索引才会生效，索引类型为type=range，即范围搜索，这是因为Mysql的最左匹配原则。

-- Case#：右边是%
EXPLAIN SELECT * FROM staff WHERE name LIKE 'Alice%';
-- type=range extra=Using where

EXPLAIN SELECT name,age,pos FROM staff WHERE name LIKE 'Alice%';
-- type=index extra=Using where; Using index

where条件相同，但是一个查询字段了是*，一个是name,age,pos，虽然索引都生效了，但是Explain的执行结果完全不同

SELECT * FROM staff WHERE name LIKE 'Alice%';做了回表查询

SELECT name,age,pos FROM staff WHERE name LIKE 'Alice%';使用了索引覆盖，再从索引文件中过滤数据(extra=Using where; Using index)

EXPLAIN SELECT name,age,pos FROM staff WHERE name LIKE 'Alice%';
-- type=index extra=Using where; Using index

EXPLAIN SELECT name,age,pos FROM staff WHERE name LIKE '%Alice%';
-- type=index extra=Using where; Using index

从上面结果可看出：当SELECT后面的字段属于索引字段范畴，后面的LIKE语句中%在哪个位置都不重要了，因为都是使用了索引覆盖，再从索引文件中过滤数据。

再来几个测试样例，可见只要查询的字段中只要包含无索引的字段，那么最终都是全表扫描，索引失效。

-- IndexCase#：查询Id
EXPLAIN SELECT id FROM staff WHERE name LIKE '%Alice%';
-- 结果：使用上了索引（因为 name 有索引，同时查询的 Id 是主键肯定也有索引）
-- type=index extra=Using where; Using index

EXPLAIN SELECT salary FROM staff WHERE name LIKE '%Alice%';
-- type=all extra=Using where; Using index

-- IndexCase#5：查询name
EXPLAIN SELECT name FROM staff WHERE name LIKE '%Alice%';
-- 结果：使用上了索引（因为查询条件和查询字段都是有索引的 name）
-- type=index extra=Using where; Using index

-- IndexCase#6：查询age
EXPLAIN SELECT age FROM staff WHERE name LIKE '%Alice%';
-- 结果：使用上了索引（因为查询条件的 name 以及查询字段的 age 都有索引）
-- type=index extra=Using where; Using index

EXPLAIN SELECT pos FROM staff WHERE name LIKE '%Alice%';
-- type=index extra=Using where; Using index

-- IndexCase#7：查询 id & name
EXPLAIN SELECT id, name FROM staff WHERE name LIKE '%Alice%';
-- 结果：使用上了索引（因为查询条件的 name 以及查询字段的 id & name 都有索引）
-- type=index extra=Using where; Using index

EXPLAIN SELECT pos,name FROM staff WHERE name LIKE '%Alice%';
-- 使用上了索引，索引顺序很重要，最左匹配原则
-- type=index extra=Using where; Using index

EXPLAIN SELECT name,age,pos FROM staff WHERE name LIKE '%Alice%';
-- type=index extra=Using where; Using index

EXPLAIN SELECT name,age,pos,salary FROM staff WHERE name LIKE '%Alice%';
-- type=all extra=Using where