18

MySQL 数据库开发中的 6 个“避免”

 3 years ago
source link: https://xie.infoq.cn/article/717f2f953a689102f85c54c6b
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

关注 “ Java后端技术全栈 ”**

回复“面试”获取全套大厂面试资料

由于近期工作涉及数据库相关的操作较多，就根据自己的实战经历整理了一些数据库开发的规范用法，利用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)


YriaEzm.jpg!mobile

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

select * from table2 where name != 'zhaoyun'


VJbeaqn.jpg!mobile

select * from table2 where name != 'zhaoyun1'


Unyi2eI.jpg!mobile

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

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


vey2EzJ.jpg!mobile

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

select count(name) from table2;


7jYBZrF.jpg!mobile

5、避免select *

  • 使用select *可能会返回不使用的列的数据。它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。

  • 如果明确指定列，则结果集更可预测并且更易于管理。想象一下，当您使用select *并且有人通过添加更多列来更改表格数据时，将会得到一个与预期不同的结果集。

  • 使用select *可能会将敏感信息暴露给未经授权的用户。

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

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

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

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

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

推荐阅读

【原创】SpringBoot快速整合Thymeleaf模板引擎

【原创】Spring Boot 集成Spring Data JPA的玩法

【原创】Spring Boot集成Mybatis的玩法

RNZvQn6.png!mobile


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK