数据库技术:MySql（三）SQL优化策略

1、尽量全职匹配

当建立了索引列后，能在where 条件中使用索引尽量使用。

2、最佳左前缀法则

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

 

EXPLAIN SELECT * FROM Student WHERE  age = 25 AND pos = ‘dev’

EXPLAIN SELECT * FROM Student WHERE pos = ‘dev’

EXPLAIN SELECT * FROM Student WHERE NAME = ‘July’

3、不在索引列上做任何操作

不在索引列上做任何操作指的是：计算、函数、（自动or手动）类型转换，会导致索引失效而转向全表扫描。

 

4、范围条件放最后

 

中间有范围查询会导致后面的索引列全部失效

例：index(name, pos, age)

EXPLAIN SELECT * FROM Student WHERE NAME = ‘July’  and age >22 and pos=’manager’

5、覆盖索引尽量用

 

尽量使用覆盖索引：只访问索引的查询（索引列和查询列一致），减少 select  * （*需要解析成字段）。

6、不等于要慎用

6.1 MySql在使用不等于（!=  或者 <> ）的时候无法使用索引会导致全表扫描。

例：

select id from t where num is null （num有索引也会全表扫描）；

在设计表时可以在 num 上设置默认值0，确保表中没有null值，这样查询 ：select id from t where num = 0

 

6.2 如果一定要使用不等于，需要覆盖索引：select 后写字段，不能用*代替

EXPLAIN SELECT name,age,pos FROM Student WHERE NAME != ‘July’;

 

EXPLAIN SELECT name,age,pos FROM Student WHERE NAME <> ‘July’;

 

7、Null/Not有影响

7.1 自定义为not null

 

EXPLAIN select * from Student where name is null

EXPLAIN select * from Student where name is not null

在字段为not null的情况下，使用is null 或 is not null 会导致索引失效。

解决方式：覆盖索引——select * 替换成字段

EXPLAIN select  name,age,pos from Student where name is not null

7.2 自定义为null或者不定义

 

EXPLAIN select * from Student2 where name is null

 

 

EXPLAIN select * from Student2 where name is not null

 

Is not null 的情况会导致索引失效

解决方式：覆盖索引

EXPLAIN select  name,age,pos from Student where name is not null

8、Like查询要当心

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

 

解决方式：覆盖索引

EXPLAIN select name,age,pos from Student where name like ‘%july%’

9、字符类型加引号

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

10、Or改为Union效率高

10.1 避免在where子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

例： select  id from t where num=10 or num=20

替换成

select id from t where num=10

union all

select id from t where num=20

 

10.2 第二种解决方式：覆盖索引

EXPLAIN

select name,age from Student where name=’July’ or name = ‘z3’

 

例：

 

需要了解更多数据库技术:MySql（三）SQL优化策略，都可以关注数据库技术分享栏目—编程笔记