美团面试：使用MySQL，你遇到过哪些坑？

╰+攻爆jí腚メ 2022-09-09 12:41 223阅读 0赞

关注公众号“Java后端技术全栈”

回复“000”获取程序员必备电子书

大家好，我是田维常，十年码农给你分享后端开发技术，记得关注我。

前面分享7篇，关于2017年，我去上海美团面试遇到的技术问题。

[**美团面试：熟悉哪些JVM调优参数，幸好我准备过！**][JVM]

[美团面试：讲清楚MySQL结构体系，立马发offer][MySQL_offer]

[美团面试：慢SQL有遇到过吗？是怎么解决的？][SQL]

[美团面试：String s = new String("111")会创建几个对象？][String s _ new String_111]

[美团面试：为什么就能直接调用userMapper接口的方法？][userMapper]

[**美团面试：接口被恶意狂刷，怎么办？**][Link 1]

[**美团面试：MySQL中有哪些锁？当时我就懵逼了！**][MySQL]

本文技术面试题，是本人亲身经历的。所以，十年老兵告诉你，作为一枚后端开发人员，不管你们公司有没有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

在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

再执行，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,"tianweichang"),(2,"zhangsan"),(3,"lisi")
    insert into table2 values (1,"tianweichang"),(2, null)

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

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

![e25535a93b527d7ee13e49fc215b8a48.png][]

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

select * from table2 where name != 'tianweichang'

![dd5106fbdd3cffb7b3510a2c4da52141.png][]

select * from table2 where name != 'zhaoyun1'

![600faf2e788d7f078916a03cba2c4fa2.png][]

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

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

![27466d86730e74f3867f69419b967e61.png][]

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

select count(name) from table2;

![1f3c74c3e5a6cb8171e6ff5e1a158f23.png][]

## 5、避免select ##

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

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

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

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

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

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

好了，今天就分享到这里了。期待你的关注、点赞、在看、转发！

加我微信，拉你进群，和大家一起聊技术、谈人生！

![128253766c6690485cfb65f23ecca7b8.png][]

推荐阅读

*  [离职后，10天面试 4 家公司的总结][10_4]  
    
 *  [一套Spring Cloud Alibaba视频教程][Spring Cloud Alibaba]  
    
 *  [后端开发人员，学习之路][Link 2]
 *  [后端面试总结 23万，500多页（含答案）！][23_500]
 *  [112期面试汇总，建议收藏][112]

[JVM]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd557df9ca22f4efc57b8abeea251aa4e965c9b08af4428057d8062f8d60032c33f335679d39&idx=1&mid=2247505170&scene=21&sn=36ffb753df777d1b892163e07c22e312#wechat_redirect
[MySQL_offer]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd557ab0ca22f3a6754bdd71d976fd22bb3bc65e87f059c9a74f1db3e01f8fdff8d9844ac3e8&idx=1&mid=2247504475&scene=21&sn=11130bc937218b5e052c10cd8fa85a0c#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd557949ca22f05fdf97feeb9f339951bfb62a592823578ae02b557d8f79022e6c02ab85aaab&idx=1&mid=2247504290&scene=21&sn=8431458c6d46bdccc8a91afd95f906de#wechat_redirect
[String s _ new String_111]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd554e7cca22c76a2c897360afa9e0c6f4ed7da42a571673158c2524cf46b2fea901163a474b&idx=1&mid=2247493271&scene=21&sn=f3c5bf9f67971ee6c9dacc0eb757b0e3#wechat_redirect
[userMapper]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd555a1dca22d30b64ae0760054523998b79277c5b6886813011504b7e717a8c29159223596f&idx=1&mid=2247496438&scene=21&sn=02020dac3415ac5d1887b88053d30ffb#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd557a94ca22f382326479915ee56139a95ac500a84f057544d8a279be42cfe468c29fd64638&idx=1&mid=2247504511&scene=21&sn=307daa44f37c3d55464e696f0423325d#wechat_redirect
[MySQL]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd557b5bca22f24d05c7add694d82f3e3598ed55c4ae4ba8fe133c2dd588da5c841c6f89d313&idx=1&mid=2247504816&scene=21&sn=527ed15852f11904728cb0da164bfa85#wechat_redirect
[e25535a93b527d7ee13e49fc215b8a48.png]: /images/20220829/1dc426d2bb584f1c9c020e0d842d7b2b.png
[dd5106fbdd3cffb7b3510a2c4da52141.png]: /images/20220829/c3902e1eaffb4b6c841f8d11fe3890bf.png
[600faf2e788d7f078916a03cba2c4fa2.png]: /images/20220829/6f5c3553730041678e0728dfecc1dfe9.png
[27466d86730e74f3867f69419b967e61.png]: /images/20220829/3c8ae3c03c8a455793783a5797e0f576.png
[1f3c74c3e5a6cb8171e6ff5e1a158f23.png]: /images/20220829/801d0e0128044415a8f652836327b3ec.png
[128253766c6690485cfb65f23ecca7b8.png]: /images/20220829/545bc945aeeb4a6696c9543fd911e773.png
[10_4]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd556459ca22ed4faf44bc01dbab47030bae7995cb0bcaa91b55a9e130b2a9e0be119dea29ef&idx=1&mid=2247503026&scene=21&sn=183d38a1172e3eb4d31f60783c477c38#wechat_redirect
[Spring Cloud Alibaba]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd556225ca22eb33a3859702053e4f6817f646264cf58fabe5c9f18144da347305a2a6d8b0e6&idx=2&mid=2247502542&scene=21&sn=efbdc5883e72041b423df9dd229723f1#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd555753ca22de4522b62563737854b0359847f046b416df20cdfa099ea81d8e03c8bb22f4d1&idx=1&mid=2247499704&scene=21&sn=e3a867f360e8a8cccf8d79e0652c36c0#wechat_redirect
[23_500]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd556535ca22ec2350b399f33fd1125ce75dd34f1351b47cb70c2a60ffad803853209fd083b4&idx=1&mid=2247503326&scene=21&sn=a35bd6b6dd62d742f34c7c80e8f8f421#wechat_redirect
[112]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd556527ca22ec310441fb12453e0bd624fb0e4bfa18919d1141d3641b9c462a9a28113dc95a&idx=2&mid=2247503308&scene=21&sn=0464ed003b29d85ae47f5b330cade32e#wechat_redirect