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

一、建表语句

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、根据索引列的name、age和pos查询所有，并执行explain

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

• 结论： key为idx_emps_nameAgePos说明实际使用到了索引；ref为const,const,const，说明是3个常量索引；并且type为ref，说明性能不错。

2、根据索引列的name、age和pos只查询索引列，并执行explain

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

• 结论： key为idx_emps_nameAgePos说明实际使用到了索引；ref为const,const,const，说明是3个常量索引；并且type为ref，说明性能不错。extra为Using index，说明额外使用了索引，比第一个示例优化效果明显。

3、根据索引列的name、age和pos只查询索引列并且age是一个范围查询，并执行explain

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

• 结论： key为idx_emps_nameAgePos说明实际使用到了索引；key_len长度为66，只使用到了name列索引,不包含age索引；extra为Using index，说明额外使用了索引

三、结论

• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 查询。