索引优化：避免索引失效

一、索引失效的十大原因

#优化索引口诀
全值匹配我最爱，最左前缀要遵守
带头大哥不能丢，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最右， 覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不能丢，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);




mysql> select *from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos     | add_time            |
+----+------+-----+---------+---------------------+
|  1 | z3   |  22 | manager | 2019-08-12 23:06:53 |
|  2 | z2   |  23 | dev     | 2019-08-12 23:07:35 |
|  3 | 2000 |  21 | dev     | 2019-08-12 23:07:58 |
+----+------+-----+---------+---------------------+
需要的表结构

1、全值匹配我最爱

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

 

 

 

2、最佳左前缀原则

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

 

 

 

 

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

 

 

 

 

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

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

 

 

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

 

 

 

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

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

 

1、使用select *

 2、使用覆盖查询

 

 

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

 

 

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

 

 

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

 

posted @ 2019-08-12 23:36  pdun  阅读(1510)  评论(0)  编辑  收藏  举报