SQL索引优化

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
#创建员工表
CREATE TABLE IF NOT EXISTS staffs
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL DEFAULT '' COMMENT '姓名',
    age INT NOT NULL DEFAULT  0  COMMENT '年龄',
    pos VARCHAR(50) NOT NULL DEFAULT ''  COMMENT '职位',
    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'

 )ENGINE=INNODB CHARSET utf8 COMMENT '员工表';
 #查询
 SELECT * FROM staffs;
 #修改表结构创建索引
 ALTER TABLE staffs ADD INDEX  idex_nap(NAME,age,pos);
 #优化一：全职匹配
 SELECT *FROM staffs WHERE NAME ='July' AND age ='23' AND pos = 'dev';
 #优化二:匹配最左列
 SELECT * FROM staffs WHERE NAME ='July' AND age = '23';
 #优化三：模糊查询，匹配前缀
 SELECT * FROM staffs WHERE NAME LIKE 'J%';
 #优化四：匹配范围
 SELECT * FROM staffs WHERE NAME > 'marry';
 #优化五:
 SELECT * FROM staffs WHERE NAME ='marry' AND age >18;
 #优化六：只使用索引字段
 SELECT NAME,age,pos FROM staffs WHERE  NAME ='July' AND age = 25 AND pos ='dev';
 SELECT NAME,age FROM staffs WHERE  NAME = 'July'  AND age > 25;