sql优化操作

文章来自于

(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觉得全表扫描更快时（数据少）

distinct的字段尽量少

使用默认值代替null

批量添加数据

批量删除数据

先过滤后分组

排序字段建立索引

优先使用innerjoin

使用union all代替union