Mysql的sql语句的优化

Mysql的sql语句优化

1. 尽量避免在where子语句中使用or,否则引擎将放弃索引而选择扫描全表,应该选择用union all代替
   select id,name,sex,age from user where name=‘xxx’ or name=‘xxxx’
   替换成:
   select id,name,sex,age from user where name=‘xxx’
   union all
   select id,name,sex,age from user where name=‘xxxx’

2. 模糊查询是避免使用’%xxx’和’%xxx%’,否则索引将失效,应该选择’xxx%‘或者reverse(’%xxx’),reverse是mysql中的反转函数,相当于’xxx%’
   select id from user where phone like ‘%xxx%’
   或者
   select id from user where phone like ‘%xxx’
   替换成:
   select id from user where phone like ‘xxx%’
   或者
   select id from user where phone like reverse(’%xxx’)

3. 对查询进行优化时,避免进行全表扫描,在where和order by涉及的列上建立索引,可以大大提升查询的速度

4. 避免在where子语句对字段进行null判断,否则将导致引擎放弃使用索引而进行全表扫描
   select id from user where num is null
   或者
   select id from user where num is not null
   替换成
   select id from user where LENGTH(trim(num))=0
   或者
   select id from user where LENGTH(trim(num))>0

5. 应尽量避免在where子句中对字段进行函数操作

6. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
   select id from user where num/2=100
   替换成:
   select id from user where num=100*2

7. 索引并不是越多越好,在查询的时候索引可以提高速度,但是降低了insert和update的速度,因为 insert 或 update 时有可能会重建索引

8. 避免在where子语句中使用!=或者<>,否则系统将可能无法正确使用索引

9. in 和 not in 也要慎用，否则会导致全表扫描，如：
   select id from user where num in(1,2,3)
   替换成:
   select id from user where num between 1 and 3

以上是跟索引失效的补救方式，提升查询的速度

1. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
2. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
3. 任何地方都不要使用 select * from user,用具体的字段列表代替“*”,不要返回用不到的任何字段,select * 会查询出不需要的、额外的数据，那么这些额外的数据在网络上进行传输，必定会造成性能延迟
4. 尽量使用union all代替 union
   Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
   Union All：对两个结果集进行并集操作，包括重复行，不进行排序
5. 将经常用到的字段加上索引.例如:搜索,排序的相关字段,最好将这些字段设置索引
6. 绝对不要轻易用order by rand(),很可能会导致mysql的灾难
7. 避免不必要的类型转换,这里的类型转换是潜在的类型转换,比如将字符串与数字类型比较,这样会将字符串进行转换,导致全表扫描
8. 合理使用in与exists,exists是循环的方式,外表记录数代表循环的次数,外表的记录少,适合用它.
   in先执行子查询,子查询去重之后,然后在执行主查询,子查询返回结果越少,越适合这种方式.
   如果两张表数据一样大,那么用in和exists差别不大,但是如果不一样大,子查询小的用in,主查询小的用exists
9. 规范所有关键字的书写,select ,update,delete,要么全大写要不全小写