美团面试:使用MySQL,你遇到过哪些坑?

数据库 MySQL
本文技术面试题,是本人亲身经历的。所以,十年老兵告诉你,作为一枚后端开发人员,不管你们公司有没有DBA,数据库相关技术都是必备的。

[[420165]]

本文技术面试题,是本人亲身经历的。所以,十年老兵告诉你,作为一枚后端开发人员,不管你们公司有没有DBA,数据库相关技术都是必备的。

面试官:对于锁这一块,你还是掌握的蛮好的。

菜鸟我:(稍稍微笑,表示回应)

面试官:用了这么多年的MySQL,有哪些坑,让你难以忘怀。

菜鸟我:巴拉巴拉 开始扯(这种面试题,我在面试前已经准备过了,所以,洒洒水啦)

下面,就根据自己的实战经历整理了一些数据库开发的规范用法,用6个“避免”来概括。

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

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

2、避免对索引列做运算

有次,有位同事让我看一条SQL,说是在前台查询很快,但是把SQL取出来,在数据库中执行的时候,跑10分钟都不出结果。看了一下SQL,最后定位到一个视图中的一个子查询上面。该子查询的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 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

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

## 以下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 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.

再执行,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
    `namevarchar(20) NOT NULL 

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

 
insert into table1 values (4,"tianweichang"),(2,"zhangsan"),(3,"lisi"
insert into table2 values (1,"tianweichang"),(2, null
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

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

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

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

select * from table2 where name != 'tianweichang' 
  • 1.

select * from table2 where name != 'zhaoyun1' 
  • 1.

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

select concat("1"nullfrom dual; 
  • 1.

(4) 当计算count时候,name为null 的不会计入统计

select count(namefrom table2; 
  • 1.

5、避免select

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

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

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

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

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

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

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

 

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

 

责任编辑:武晓燕 来源: Java后端技术全栈
相关推荐

2017-07-14 09:29:45

AndroidWebview

2021-05-27 09:27:35

开发技能缓存

2020-10-12 09:49:14

C++ 开发代码

2019-10-28 14:07:29

研发管理技术

2020-11-08 14:38:35

JavaScript代码开发

2009-07-23 15:07:32

2011-04-26 09:22:05

SQLite

2023-03-13 07:41:34

分页查询数据排序

2019-12-05 08:44:20

MybatisSQL场景

2018-04-25 10:57:00

AIX报错vios

2022-10-20 18:00:59

OCP模型参数

2021-04-04 22:31:26

白帽子厂商漏洞

2018-03-26 09:39:06

大数据IT互联网

2021-02-19 11:01:46

异步竞态接口异步

2020-09-24 10:49:09

iOSiPadOSBug

2020-04-26 14:40:19

戴尔

2021-12-30 09:32:04

缓存数据库数据

2021-12-26 14:32:11

缓存数据库数据

2017-09-07 15:23:21

神经网络数据网络

2024-03-18 08:14:07

SpringDAOAppConfig
点赞
收藏

51CTO技术栈公众号