避免使用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