MySQL高级——索引优化案例7

一、建表语句

1、员工表的建表语句

CREATE TABLE emps(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';

INSERT INTO emps(NAME,age,pos,add_time) VALUES('张三',22,'manager',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('李四',23,'dev',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('王五',23,'dev',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('1aa1',21,'dev',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('2aa2',21,'dev',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('3aa3',25,'dev',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('4aa4',22,'dev',NOW());
INSERT INTO emps(NAME,age,pos,add_time) VALUES('aa',23,'dev',NOW());

2、创建复合索引语句

ALTER TABLE emps ADD INDEX idx_emps_nameAgePos(NAME,age,pos);

3、查看创建的索引语句

SHOW INDEX FROM emps;

二、案例

2.1、根据索引列的name ，以%开头并且%结尾模糊查询所有数据，并执行explain

EXPLAIN SELECT * FROM emps WHERE NAME LIKE ‘%张三%’

• 结论：以%开头并且%结尾模糊查询，type为all，说明是全表扫描；key为null说明实际没用到索引

2.2、根据索引列的name ，以%开头模糊查询所有数据，并执行explain

EXPLAIN SELECT * FROM emps WHERE NAME LIKE ‘%张三’

• 结论：以%开头模糊查询，type为all，说明是全表扫描；key为null说明实际没用到索引

2.3、根据索引列的name ，以%结尾模糊查询所有数据，并执行explain

EXPLAIN SELECT * FROM emps WHERE NAME LIKE ‘张三%’

• 结论：以%结尾模糊查询，type为range，说明是一个范围，性能比较好；key为idx_emps_nameAgePos说明实际用到了索引

三、案例结论

• like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作。

四、like ‘%字符串%’ 时索引不被使用的解决方式

4.1、根据索引列的name ，以%开始并且以%结尾模糊查询id列数据，并执行explain

EXPLAIN SELECT id FROM emps WHERE NAME LIKE ‘%aa%’

• 结论：以%开头并且%结尾模糊查询id列数据，type为index，说明是性能好；key不为null说明id用到了索引，因为id是主键索引。

4.2、根据索引列的name ，以%开始并且以%结尾模糊查询id列和name列数据，并执行explain

EXPLAIN SELECT id,NAME FROM emps WHERE NAME LIKE ‘%aa%’

• 结论：以%开头并且%结尾模糊查询id列和name列数据，type为index，说明是性能好；key不为null说明用到了索引。

4.3、根据索引列的name ，以%开始并且以%结尾模糊查询id列、name列和age列数据，并执行explain

EXPLAIN SELECT id,NAME,age FROM emps WHERE NAME LIKE ‘%aa%’

• 结论：以%开头并且%结尾模糊查询id列、name列和age列数据，type为index，说明是性能好；key不为null说明用到了索引。

4.4、根据索引列的name ，以%开始并且以%结尾模糊查询id列、name列、age列和pos列数据，并执行explain

EXPLAIN SELECT id,NAME,age,pos FROM emps WHERE NAME LIKE ‘%aa%’

• 结论：以%开头并且%结尾模糊查询id列、name列、age列和pos列数据，type为index，说明是性能好；key不为null说明用到了索引。

4.5、根据索引列的name ，以%开始并且以%结尾模糊查询id列、name列、age列、pos列和add_time列数据，并执行explain

EXPLAIN SELECT id,NAME,age,pos,add_time FROM emps WHERE NAME LIKE ‘%aa%’

• 结论：以%开头并且%结尾模糊查询id列、name列、age列、pos列和add_time列数据，type为all，说明是全表扫描；key为null说明没有使用到索引。

五、like ‘%字符串%’ 时索引不被使用的结论

• 使用覆盖索引解决‘%字符串%’ 模糊查询索引不被使用情况
• 覆盖索引:即查询列要被所建的索引覆盖。通俗讲就是创建的索引列和查询的列在字段上和顺序上最好完全一致
• 4.5示例中，创建的复合索引有name、age、pos三列，而查询的字段有name，age，pos和add_time四列，超出了所建立的索引列数，没有被创建的索引覆盖，因此索引失效，从而导致全表扫描。