专栏首页Java后端技术栈cwnait【原创】MySQL数据库开发中的6个“避免”

【原创】MySQL数据库开发中的6个“避免”

由于近期工作涉及数据库相关的操作较多,就根据自己的实战经历整理了一些数据库开发的规范用法,利用6个“避免”来概括。

1、避免在数据库中做运算

有句话叫做“别让脚趾头想事情,那是脑瓜子的职责”,用在数据库开发中,说的就是避免让数据库做她不擅长的事情。MySQL并不擅长数学运算和逻辑判断,所以尽量不在数据库做运算,复杂运算可以移到程序端CPU。

2、避免对索引列做运算

有次,有位同事让我看一条SQL,说是在前台查询很快,但是把SQL取出来,在数据库中执行的时候,跑10分钟都不出结果。 看了一下SQL,最后定位到一个视图中的一个子查询上面。该子查询的SQL文本如下:

SELECT  acinv_07.id_item ,
        SUM(acinv_07.dec_endqty) dec_endqty
FROM    acinv_07
WHERE   acinv_07.fiscal_year * 100 + acinv_07.fiscal_period 
        = ( SELECT DISTINCT
                   ctlm1101.fiscal_year * 100 + ctlm1101.fiscal_period
                   FROM ctlm1101 WHERE flag_curr = 'Y'
                   AND id_oprcode = 'acinv'
                   AND acinv_07.id_wh = ctlm1101.id_table)
GROUP BY acinv_07.id_item

在acinv_07表上的列fiscal_year和列fiscal_period是有索引的。但是,如果对索引列进行运算,就会导致原本可以走索引的走不了索引。于是,动手改写成如下SQL:

SELECT    id_item ,
                    SUM(dec_qty) dec_qty
          FROM      dpurreq_03
          GROUP BY  id_item
        ) a ,
        ( SELECT    a.id_item ,
                    SUM(a.dec_endqty) dec_endqty
          FROM      acinv_07 a ,
                    ( SELECT DISTINCT
                                ctlm1101.fiscal_year ,
                                ctlm1101.fiscal_period ,
                                id_table
                      FROM      ctlm1101
                      WHERE     flag_curr = 'Y'
                                AND id_oprcode = 'acinv'
                    ) b
          WHERE     a.fiscal_year = b.fiscal_year
                    AND a.fiscal_period = b.fiscal_period
                    AND a.id_wh = b.id_table
          GROUP BY  a.id_item

再执行,4s钟左右就可以跑出结果了。 总的来说,写SQL时,不到万不得已,不要对索引列进行计算。

3、避免count(*)

在分页查询的时候,有的人总是习惯用select count(*)获得总的记录条数,实际上这不是一个高效的做法,因为,之前获得数据的时候已经查询过一次了,select count(*)相当于同一个语句查询了两次,对数据库的开销自然就大了,我们应当使用数据库自带的API,或者系统变量来完成这个工作。

4、避免使用NULL字段

大家在数据库表字段设计的时候,应该尽量都加上NOT NULL DEFAULT ''。 使用NULL字段会产生很多不好的影响,例如:很难进行查询优化、NULL列加索引,需要额外空间、含NULL复合索引无效…… 看下面的案例:

数据初始化:
create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)


create table table2 (
    `id` INT (11) NOT NULL,
    `name`  varchar(20)
)

insert into table1 values (4,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values (1,"zhaoyun"),(2, null)

(1) NOT IN子查询在有NULL值的情况下返回永远为空结果,查询容易出错

select name from table1 where name not in (select name from table2 where id!=1)

(2) 列值允许为空,索引不存储null值,结果集中不会包含这些记录。

select * from table2 where name != 'zhaoyun'
select * from table2 where name != 'zhaoyun1'

(3) 使用concat拼接时,首先要对各个字段进行非null判断,否则只要任何一个字段为空都会造成拼接的结果为null

select concat("1", null) from dual;

(4) 当计算count时候null column不会计入统计

select count(name) from table2;

5、避免select *

  • 使用select *可能会返回不使用的列的数据。它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。
  • 如果明确指定列,则结果集更可预测并且更易于管理。想象一下,当您使用select *并且有人通过添加更多列来更改表格数据时,将会得到一个与预期不同的结果集。
  • 使用select *可能会将敏感信息暴露给未经授权的用户。

6、避免在数据库里存图片

图片确实是可以存储到数据库里的,例如通过二进制流将图片存到数据库中。

但是,强烈不建议把图片存储到数据库中!!!!首先对数据库的读/写的速度永远都赶不上文件系统处理的速度,其次数据库备份变的巨大,越来越耗时间,最后对文件的访问需要穿越你的应用层和数据库层。

图片是数据库最大的杀手。一般来说数据库都是存储一个URL,然后再通过URL来调用图片。

图片,文件,二进制数这三样东西慎重存储到数据库中。

本文分享自微信公众号 - Java后端技术全栈(jjs-2018),作者:田老师

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 分库分表的几种常见玩法及如何解决跨库查询等问题

    在谈论数据库架构和数据库优化的时候,我们经常会听到“分库分表”、“分片”、“Sharding”…这样的关键词。让人感到高兴的是,这些朋友所服务的公司业务量正在(...

    用户4143945
  • 这10道 Spring 常见面试题,你能搞定吗?

    今天先用10道题来热热身,千万别小看这10道题,能把这10道题回答上来的也算是很不错的。请看题:

    用户4143945
  • 教你写一个迷你版的Tomcat~

    Tomcat,这只3脚猫,大学的时候就认识了,直到现在工作中,也常会和它打交道。这是一只神奇的猫,今天让我来抽象你,实现你!

    用户4143945
  • 自学sql注入(一)

    这是笔者自行整理出来的有关sql注入的一些知识点,自己还有些迷迷糊糊,可能有些不对的地方。等学完之后,再来详写一系列的关于sql注入的文章

    宸寰客
  • R in action读书笔记(11)-第八章:回归-- 选择“最佳”的回归模型

    用基础安装中的anova()函数可以比较两个嵌套模型的拟合优度。所谓嵌套模型,即它的一

    统计学家
  • Python练手例子(11)

    py3study
  • kubernetes 上手指南:概念篇

    今天的主题:kubernetes 概念篇,通过一些示例,学习 kubernetes(k8s) 的一些核心概念。

    谢伟
  • insert语句的加锁情况分析

    今天分享的内容是MySQL里面insert语句的加锁情况,废话就不多说了,直接从线上的例子开始吧。

    AsiaYe
  • 《大话脑成像》系列之六——脑电信号频域变换

    傅里叶大神推荐关注思影科技,并表示以下文章全是关于他! 自从做了那么多核磁基础讲解之后,有些脑电的朋友或者学员感到不忿,感到委屈,感到不公平,说哎呀,主编你好偏...

    用户1279583
  • JAVA 第二天 基本数据类型

    拾点阳光

扫码关注云+社区

领取腾讯云代金券