文章来自于
(1条消息) Sql优化总结!详细!(2021最新面试必问)_笨笨杨的博客-CSDN博客_sql优化面试题及答案
避免使用or
使用in取替代or
在效率上,在in的列和or的列有索引时,in和or的速度基本没有区别,但是如果没有索引时,随着数据量的增大,in的执行速度没有什么区别,但是or的执行效率会变慢
在操作上,in把子表和父表做hash查询,而or是对父表进行hoop循环
在使用范围上,or适合子表数据比父表少的情况,in适合父表比子表少的情况
使用exists替代in
使用in进行子查询时会产生临时表,消耗资源,但是当exists查询时,如果列上有索引,那么就会查询索引,避查询父表,当查询到第一行满足数据时,会就会返回,不会产生临时表。
使用union all代替or
使用varchar代替char
char是固定长度,如果存入的数据没有到达char的长度时,会补空格补齐。
varchar的按照实际长度存取,可以比char节省资源。
避免返回大量数据
返回大量数据有两点坏处:其一是数据传输量过大,造成查询时间过长,网络传输时间会过长。
其二是用户可能也看不了那么多数据,纯纯浪费资源。
采用分页查询
使用explain分析sql执行情况,在其type那一行查看情况。
一共有八种数据:
all 全局扫描,速度最慢,效率最低。
index 索引全扫描
range 索引范围查询,常与<>=between一起使用
ref 使用非唯一索引或者唯一索引前缀扫描,返回一条数据,常出现在关联查询中。
eq_ref 类似于ref,区别是使用的是唯一索引,使用主键关联查询
const 对主键或者唯一键精确查询,对行中其他列当作常数处理
nullMySQL 不使用表和索引查询
system表里只有一行数据
执行效率: System > const > eq_ref > ref > range > index > ALL
优化like查询
like查询作为模糊查询功能强大,但是通常会伴随着索引失效,那么如何优化like使其效率增大呢
方法一,对like的字段进行操作:比如原本要查询的是:
select id,sex,age from tablea where department like '%技术%'
可以改成
select id,sex,age from tablea where department like '技术%'
方法二:使用函数来达到模糊查询的函数:
比如locate: select id,sex,age from tablea where locate(department,'技术')>0
locate的用法:找出要匹配字段在被匹配中的位置,在第几个就会显示数字几
如果使用position,就把后面的改成position(‘a’ in department) ,也会返回第几个位置
find_in_set 和instr
字符串怪现象:
where str=123 和where str='123'不一样
索引问题:
给要经常查询的字段使用索引,但是索引不应该过多,因为索引是另外见了一张索引表,对于查询来讲速度可能是会,但是当插入和更新语句的速度就会变慢
避免在sql中使用!=和<>,因为会导致索引失效
1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时(数据少)