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

一、建表语句

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());

2、创建复合索引语句

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

3、查看创建的索引语句

SHOW INDEX FROM emps;

二、案例

1、按照创建复合索引字段的顺序条件查询，然后执行EXPLAIN

EXPLAIN SELECT * FROM emps WHERE NAME=‘张三’ EXPLAIN SELECT * FROM emps WHERE NAME=‘张三’ AND age=22 EXPLAIN SELECT * FROM emps WHERE NAME=‘张三’ AND age=22 AND pos=‘manager’

• 结论： 以上3种情况type 都为ref,说明性能不错；key为idx_emps_nameAgePos说明实际中用到了索引；ref为三个const说明用到了三个常量索引。

2、依次从左至右去掉创建的复合索引字段，然后条件查询并执行EXPLAIN

//条件查询时去掉了复合索引的第一个属性name，然后执行EXPLAIN。 EXPLAIN SELECT * FROM emps WHERE age=22 AND pos=‘manager’ //条件查询时去掉了复合索引的第一个属性name和第二个属性age，然后执行EXPLAIN。 EXPLAIN SELECT * FROM emps WHERE pos=‘manager’

• 结论： 以上2种情况type 都为all,因此需要进行优化；key为null说明实际中没有用到索引。导致索引失效。

3、去掉创建的复合索引的中间属性，然后条件查询并执行EXPLAIN

//条件查询时去掉了复合索引的中间属性age，然后执行EXPLAIN。 EXPLAIN SELECT * FROM emps WHERE NAME=‘张三’ AND pos=‘manager’

• 结论： 以上1种情况type 都虽然是ref,但是key_len的长度明显是一个索引的长度，并且ref中只有一个const常量索引，因此只用到了一个符合索引中的第一个属性name。违背了最佳左前缀法则，导致索引失效。

三、案例结论

• 如果索引了多列，要准守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。否则会造成索引失效。