Mysql 索引优化以及口诀

索引口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能丢，中间兄弟不能断；
索引列上不计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写 *；
不等空值还有or，索引失效要少用；
字符单引不可丢，SQL高级也不难。

表结构如下：

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 '员工记录表';

insert into staffs(name,age,pos,add_time) values ('z3','22','manager',now());
insert into staffs(name,age,pos,add_time) values ('z2','23','dev',now());
insert into staffs(name,age,pos,add_time) values ('2000','21','dev',now());
Query OK, 1 row affected (0.15 sec)

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

1.全值匹配我最爱

建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

2.最佳左前缀原则

最前缀法则，必须有车头，中间车厢不能断

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

4.存储引擎不能使用索引中范围条件右边的列（范围之后全失效，不包括本身）

若中间索引列用到了范围（>、<、like等），则后面的所以全失效

5.尽量使用覆盖索引（尽量不用*）

6.like左模糊查询会导致索引失效

有时候右模糊查询，并不能查到想得到的内容
如果想需要使用两边都是%号，又不想索引失效，
此时应使用覆盖查询（查询范围在已经建立的索引范围之内，尽量与索引的个数，顺序相同）

使用select *

使用覆盖查询

7.is null 和 is not null将无法使用索引

8.字符串不加引号，索引失效

9.少用or，用它连接会可能会造成索引失效