书写高质量SQL的建议

一家团聚 發表於 2023-11-30 00:00:00

<img title="书写高质量SQL的建议" alt="书写高质量SQL的建议" border="0" height="auto" src="https://zhuji.jb51.net/uploads/img/202305/27a6211a8225b51da65b6d1e9213b700.jpg" width="auto">
<h3>
1、避免混乱的逻辑</h3>

反例：(统计用户数量)
<ol class="dp-sql">
<li class="alt">
List<User> users = userMapper.selectAll(); 
</li>
<li>
return users.size(); 
</li>
</ol>

正例：
<ol class="dp-sql">
<li class="alt">
int count = userMapper.countUser("select count(*) from user"); 
</li>
<li>
return count; 
</li>
</ol>
<h3>
2、select one 如果已知结果只有一条, 使用limit 1</h3>

反例：(查找nickname = 报之琼瑶的用户)
<ol class="dp-sql">
<li class="alt">
select id, nickname from t where nickname = '报之琼瑶' 
</li>
</ol>

正例：
<ol class="dp-sql">
<li class="alt">
select id, nickname from t where nickname = '报之琼瑶' limit 1 
</li>
</ol>

理由：
<ol>
<li>
加上limit1,只要找到了对应的一条记录, 就不会继续向下扫描了,效率会大大提高。limit1适用于查询结果为1条(也可能为0)会导致全表扫描的的SQL语句。</li>
<li>
如果条件列上有索引就不用limit 1，如主键查询 id = 1</li>
</ol>
<h3>
3、尽量避免在where子句中使用or来连接条件</h3>

反例：(查找name = 张三或者法外狂徒的用户)
<ol class="dp-sql">
<li class="alt">
select id,name from t where name = '张三' or name = '法外狂徒' 
</li>
</ol>

正例:
<ol class="dp-sql">
<li class="alt">
select id,name from t where name = '张三' 
</li>
<li>
union all 
</li>
<li class="alt">
select id,name from t where name = '法外狂徒' 
</li>
</ol>

理由：

使用or将导致引擎放弃使用索引而进行全表扫描
<h3>
4、优化like关键字</h3>

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

反例：
<ol class="dp-sql">
<li class="alt">
select userId,name from user where userId like '%123' 
</li>
</ol>

正例：
<ol class="dp-sql">
<li class="alt">
select userId,name from user where userId like '123%' 
</li>
</ol>
<center>
<img title="书写高质量SQL的建议" alt="书写高质量SQL的建议" src="https://zhuji.jb51.net/uploads/img/202305/d975a74437bc1d399e1c7635869ee571.jpg">
</center>

%123, 百分号在前不走索引
<center>
<img title="书写高质量SQL的建议" alt="书写高质量SQL的建议" src="https://zhuji.jb51.net/uploads/img/202305/acd1562cb47e6e06966ddb0c575a5795.jpg">
</center>

123%，百分号在后走索引

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

可以通过

INFORMATION_SCHEMA.OPTIMIZER_TRACE来分析查询过程
<center>
<img title="书写高质量SQL的建议" alt="书写高质量SQL的建议" src="https://zhuji.jb51.net/uploads/img/202305/8487b89390b4a345320d784cd8f18db2.jpg">
</center>

trace字段json复制出来即可分析
<h3>
5、查询SQL尽量不要使用select *,而是select具体字段, 不要返回用不到的任何字段。</h3>

反例：(统计用户数量)
<ol class="dp-sql">
<li class="alt">
select * from t 
</li>
</ol>

正例:
<ol class="dp-sql">
<li class="alt">
select id, name, tel from t 
</li>
</ol>

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

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

反例：
<ol class="dp-sql">
<li class="alt">
select * from user where date_add(create_time,Interval 5 day) >=now() 
</li>
</ol>

正例:
<ol class="dp-sql">
<li class="alt">
select * from user where create_time >= date_add(now(), interval - 5 day) 
</li>
</ol>
<center>
<img title="书写高质量SQL的建议" alt="书写高质量SQL的建议" src="https://zhuji.jb51.net/uploads/img/202305/84301c689b1556bbc1d1190b4dc9fe6b.jpg">
</center>

不走索引
<center>
<img title="书写高质量SQL的建议" alt="书写高质量SQL的建议" src="https://zhuji.jb51.net/uploads/img/202305/555839d485185b5581d84b600f93dd24.jpg">
</center>

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

反例： (对字段user_age进行运算操作, 不走索引)
<ol class="dp-sql">
<li class="alt">
select * from user where user_age - 1 = 2 
</li>
</ol>

正例： (走索引)
<ol class="dp-sql">
<li class="alt">
select * from user where user_age = 3 
</li>
</ol>

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

頁: [1]

琼殿技术论坛's Archiver

书写高质量SQL的建议