美文网首页
项目中写sql时注意些什么

项目中写sql时注意些什么

作者: sunpy | 来源:发表于2023-04-21 22:43 被阅读0次

使用索引


无索引时,mysql只能一页一页去搜索匹配条件。
有索引时,通过构建的B+树路径去搜索了。
索引可以大大提高查询效率。在设计数据库表时,可以根据查询频率和查询条件来选择要创建索引的列。
需要注意的是,过多的索引会影响数据的插入和更新性能,因此需要根据实际情况进行权衡。
索引:https://www.jianshu.com/p/0f593dad0257

避免全表扫描


尽可能避免全表扫描,可以使用WHERE语句来限制查询范围。
需要注意的是,使用范围查询(例如BETWEEN、>、<等)可能会导致索引失效,因此需要谨慎使用。

尽量减少JOIN操作


JOIN操作会增加查询的复杂度和开销。可以通过合理的表设计和查询优化来避免或者减少JOIN操作,譬如增加冗余字段,还有异步冗余,利用binlog+mq实现。

利用内存操作


可以将sql的一次查询分为多次查询,可以避免mysql中的大事务,还可以将多次查询的结果,在java中进行交并集操作。

减少查询返回的列数


只查询需要的列,可以减少网络IO传输和内存开销,提高查询效率。

使用批量操作


对于批量操作(例如插入、更新、删除等),可以使用批量语句来减少网络传输和数据库连接开销,提高操作效率。

避免使用子查询


子查询会增加查询的复杂度和开销,可以使用JOIN或者临时表来代替子查询。

避免使用OR条件


OR条件可能会导致索引失效,可以使用UNION或者IN语句来代替OR条件。

能用主键多用主键


主键本身就是一种聚簇索引,用非主键的索引字段还需要回表操作。

mysql多行数据操作


可以先定位,再查询和操作,譬如order by rand() 随机排序记录,可以先将主键取出来随机排序,再将获取的主键定位记录位置,获取记录。

字段最好设置为非空


Mysql中,含有空值的列很难进行查询优化,对于null可以使用0或者特殊字符。

update锁表问题


  • 现象:
    当Mysql预估扫描行数超过全表总数约为20% - 30%时,即便有二级索引,也会升级为全表扫描。
  • 原因:
    二级索引的顺序和主键顺序一般来说是不一样的,根据二级索引的顺序回表读数据时,实际上对于主键则很可能是随机扫描,因此当需要随机扫描的数量超过一定比例时(一般是20% - 30%),则优化器会决定直接改成全表扫描。
  • 解决:
    可以利用其他表,让他们通过主键关联,可以连表更新,通过主键更新可以锁行,这样不会锁表了。

慢sql查看执行计划


可以使用explain查看查询是否使用了索引以及扫描的行数。

主键设计


主键设计尽量有序且唯一

避免使用using filesort


  • 现象:
    最近使用order by对查询结果排序,发现使用和不使用的速度差别很大,使用explain查看执行计划,Extra信息发现使用using filesort。
  • 原因:
    where后面的查询条件和order by后面的排序字段不同。
  • 解决:
    可以使用联合索引。

参考


一般在写SQL时需要注意哪些问题,可以提高查询的效率? - 黎杜的回答 - 知乎
https://www.zhihu.com/question/29619558/answer/2995456130
https://blog.51cto.com/imysql/3235169?abTest=51cto
https://blog.csdn.net/qq_38263083/article/details/124706631
https://blog.csdn.net/li1325169021/article/details/121141604

相关文章

网友评论

      本文标题:项目中写sql时注意些什么

      本文链接:https://www.haomeiwen.com/subject/uhijjdtx.html