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

一、建表语句

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查询，并执行explain

EXPLAIN SELECT * FROM emps WHERE NAME=‘张三’

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

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

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

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

3、根据索引列的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，说明性能不错。

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

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

• 结论： key为idx_emps_nameAgePos说明实际使用到了索引；key_len长度为66，说明使用到了name列和age索引，没有使用到age范围查询列右侧的索引； 并且type为range，说明性能不错。

三、结论

• 存储引擎不能使用索引中范围条件右边的列。
• 说明：即索引范围右侧都失效。（案例中第4个示例，age是一个范围查询条件，通过explain查看后，pos查询条件没有使用到索引，即age右侧的索引列失效。）