书写高质量SQL的建议

1、避免混乱的逻辑

反例：(统计用户数量)

List< User > users = userMapper.selectAll();  return  users. size (); 

正例：

int   count  = userMapper.countUser( "select count(*) from user" );  return   count ; 

2、select one 如果已知结果只有一条, 使用limit 1

反例：(查找nickname = 报之琼瑶 的用户)

select   id, nickname  from  t   where  nickname =  '报之琼瑶'  

正例：

select   id, nickname  from  t   where  nickname =  '报之琼瑶'  limit 1 

理由：

加上limit1,只要找到了对应的一条记录, 就不会继续向下扫描了,效率会大大提高。limit1适用于查询结果为1条(也可能为0)会导致全表扫描的的SQL语句。 如果条件列上有索引就不用limit 1，如主键查询 id = 1

3、尽量避免在where子句中使用or来连接条件

反例：(查找name = 张三 或者 法外狂徒 的用户)

select  id, name   from  t  where   name  =  '张三'   or   name  =  '法外狂徒'  

正例:

select  id, name   from  t  where   name  =  '张三'   union   all   select  id, name   from  t  where   name  =  '法外狂徒'  

理由：

使用or将导致引擎放弃使用索引而进行全表扫描

4、优化like关键字

like常用于模糊查询, 不恰当的编码会导致索引失效

反例：

select  userId, name   from   user   where  userId  like   '%123'  

正例：

select  userId, name   from   user   where  userId  like   '123%'  

%123, 百分号在前不走索引

123%，百分号在后走索引

但是也会存在百分号在后不走索引的情况，mysql的innodb存储引擎最终执行哪种方法都是基于成本计算的, 通过比较全表扫描和二级索引比较再回表查询

可以通过

INFORMATION_SCHEMA.OPTIMIZER_TRACE来分析查询过程

trace字段json复制出来即可分析

5、查询SQL尽量不要使用select *,而是select具体字段, 不要返回用不到的任何字段。

反例：(统计用户数量)

select   *   from   t   

正例:

select  id,  name , tel  from  t 

理由：

妨碍优化器选择更优的执行计划，比如说索引扫描 增删字段可能导致代码崩溃

6、尽量避免在索引列上使用mysql的内置函数

反例：

select  *  from   user   where  date_add(create_time,Interval 5  day ) >=now() 

正例:

select  *  from   user   where  create_time >= date_add(now(), interval - 5  day ) 

不走索引

走索引

7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

反例： (对字段user_age进行运算操作, 不走索引)

select  *  from   user   where  user_age - 1 = 2 

正例： (走索引)

select  *  from   user   where  user_age = 3 

原文链接：https://www.toutiao.com/a6992763976006271496/

查看更多关于书写高质量SQL的建议的详细内容...