总结下常用的sql优化,从避免无效查询、正确使用索引、避免索引失效、其他优化四个方面来讨论
法则一:避免查询无效数据
避免使用select * 操作,因为大多时间我们并不是要使用全部的字段,读取全部字段与网络传输更多数据都会导致性能下降。
法则二:添加正确的索引
最有效的调优是添加索引,添加索引可以避免全表扫描;且我们应避免添加索引后索引失效的操作;
应该添加索引的4地方:
- Where后面经常出现的字段
- Order by后排序的关键字
- Group by后分组的的关键字
- Join on 后的关键字
法则三:尽量避免索引失效
应避免的操作,这些操作会导致即使索引添加了也会失效:
- Like操作通配符出现在左侧的时候,如like %文龙,因为索引会对数据进行排序,通配符出现在左侧使得排序失效,从而导致索引失效。
优化方式:应避免使用,如果有此种需求,可以使用INSTR(str, substr)>0取代,str是数据库目标字段,substr是模糊输入字段,数据大时也可以考虑全文搜索引擎,如ES(Elasticsearch)
- 尽量避免使用in与not in,会导致索引失效;
优化方式:如果数值连续可用between and 代替,如果不连续可以使用EXISTS与NOT EXISTS代替;因为EXISTS走索引的
- Where 后面的索引字段进行了表达式运算或使用了函数会导致索引失效
优化方式:将表达式、函数操作移动到等号右侧
- Where 语句中,如果用is null或is not null 来判定索引关键字时,索引失效
优化方式:给默认值
- Or 用union代替,or数据库引擎会放弃索引进行全表扫描
- 避免使用where 1=1 ,用标签,where 1=1数据库引擎会放弃索引进行全表扫描
法则四:其他优化
能分页分页,能批量批量,禁止使用$