索引优化总结口诀:
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不写*;
不等控制还有or,索引失效要少用;
var引号不能丢,sql高级也不难;
新建一张表:
创建索引
create index idx_name_age on student(name,age);
查看索引
show index from student;
接下来我们使用explain来测试
全值匹配我最爱
explain select * from student where name = '张三' and age = 1;
最左前缀要遵守,带头大哥不能死,中间兄弟不能断
explain select * from student where age = 1;
索引列上少计算
explain select * from student where left(name,1) = '张' and age = 1;
范围之后全失效
explain select * from student where age > 1 and name = '王五';
like百分写最右
explain select * from student where name like '%张';
可以加上覆盖索引解决
覆盖索引不写*
explain select * from student where name = 'zhangsan';
不等控制还有or,索引失效要少用
explain select * from student where name = '张三' or age = 2;
image.png
explain select * from student where name != '张三';