MySQL索引优化

## 索引优化

## 建表：

```mysql
CREATE TABLE staffs(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(24) 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'入职时间'
)CHARSET utf8 COMMENT '员工记录表';

SELECT * FROM staffs;
INSERT INTO staffs(NAME,age,pos,add_time) VALUES
('z3',22,'manager',NOW()),
('July',23,'dev',NOW()),
('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('c4',24,'lisi',NOW());

索引失效：

1. 全值匹配：即索引的索引列全都有

2. 最佳左前缀法则：如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

   （口决1：也就是你建的索引的第一个字段不能丢失 （带头大哥不能死））

   （口决2：中间兄弟不能断）

   ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME,age,pos);
   
   
   SHOW INDEX FROM staffs;
   EXPLAIN SELECT * FROM staffs WHERE NAME='july';#type为ref  ref为一个const
   EXPLAIN SELECT * FROM staffs WHERE NAME='july' AND age=25;#type为ref  ref为两个const
   EXPLAIN SELECT * FROM staffs WHERE NAME='july' AND age=25 AND pos='dev';#type为ref  ref为三个const
   
   EXPLAIN SELECT * FROM staffs WHERE age=23 AND pos='dev';#type为ALL 索引建了但是没用到 ref为null
   EXPLAIN SELECT * FROM staffs WHERE  pos='dev';#type为ALL（第一个索引字段丢失）
   
   EXPLAIN SELECT * FROM staffs WHERE NAME='july' AND pos ='dev';#type为ref ref只有一个const理论上应该有两个（中间兄弟断了）
   

3. 不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描

   EXPLAIN SELECT * FROM staffs WHERE NAME = 'july';#type为ref
   EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4)= 'july';#type为ALL
   

4. 存储引擎不能使用索引中范围条件右边的列

   EXPLAIN SELECT * FROM staffs WHERE NAME='july' AND age=25 AND pos='manager';#type为ref   ref为三个const
   EXPLAIN SELECT * FROM staffs WHERE NAME='july' AND age>25 AND pos='manager';#type变为range  ref变为null
   

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

   EXPLAIN SELECT * FROM staffs WHERE NAME='july' AND age=25 AND pos='manager';#type为ref  Extra为null
   EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME='july' AND age=25 AND pos='manager';#type为ref Extra多了Using index
   EXPLAIN SELECT NAME FROM staffs WHERE NAME='july' AND age=25;#type为ref Extra有Using index
   EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME='july' AND age>25 AND pos='dev';#Extra有Using index
   

6. mysql在使用不等于（！=或<>）的时候无法使用索引会导致全表扫描

   EXPLAIN SELECT * FROM staffs WHERE NAME='july';#type为ref  ref为一个const
   EXPLAIN SELECT * FROM staffs WHERE NAME!='july';#type为ALL key为null
   

7. is null，is not null 也无法使用索引

   EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL;#全null
   EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;#type为ALL key为null
   

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

   (百分号like加右边)

   EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%july%';#type为ALL
   EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%july';#type为ALL
   EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'july%';#type为range 有key Extra为Using index condition
   #问题：怎样解决%...%索引失效？
   CREATE TABLE tbl_user(
    id INT(11) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20) DEFAULT NULL,
    age INT(11) DEFAULT NULL,
    email VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY(id)
   )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
   
   INSERT INTO tbl_user(NAME,age,email) VALUES
   ('1aa1',21,'b@163.com'),
   ('2aa2',222,'a@163.com'),
   ('3aa3',265,'c@163.com'),
   ('4aa4',21,'d@163.com');
   
   SELECT * FROM tbl_user;
   #通过覆盖索引来解决
   #没创建索引
   EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%'; #type为ALL
   EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'; #type为ALL
   EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'; #type为ALL
   EXPLAIN	SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   #创建索引
   CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
   EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';#type为index Extra有Using index
   EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';#type为index Extra有Using index
   EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';#type为index Extra有Using index
   EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';#type为index Extra有Using index
   EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';#type为index Extra有Using index
   EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'; #type为index Extra有Using index
   EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';#type为index Extra有Using index
   EXPLAIN	SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';#type为ALL
   
   

9. 字符串不加单引号索引失效

   #name是varchar类型
   SELECT * FROM staffs WHERE NAME='2000';#可以查出来
   SELECT * FROM staffs WHERE NAME=2000;#可以查出来  mysql底层实现隐式转换
   
   EXPLAIN SELECT * FROM staffs WHERE NAME='2000';#type为ref key不为空
   EXPLAIN SELECT * FROM staffs WHERE NAME=2000;#type为ALL key为null
   
   

10. 少用or，用它来连接时会索引失效

    EXPLAIN SELECT * FROM staffs WHERE NAME='july' OR NAME='z3';#type为ALL key为null