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

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

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

菜鸟我：(稍稍微笑，表示回应)

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

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

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

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

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

2、避免对索引列做运算

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

1. ## 以下SQL来源于网络 
2. SELECT  acinv_07.id_item , 
3.         SUM(acinv_07.dec_endqty) dec_endqty 
4. FROM    acinv_07 
5. WHERE   acinv_07.fiscal_year * 100 + acinv_07.fiscal_period  
6.         = ( SELECT DISTINCT 
7.                    ctlm1101.fiscal_year * 100 + ctlm1101.fiscal_period 
8.                    FROM ctlm1101 WHERE flag_curr = 'Y' 
9.                    AND id_oprcode = 'acinv' 
10.                    AND acinv_07.id_wh = ctlm1101.id_table) 
11. GROUP BY acinv_07.id_item 

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

1. ## 以下SQL来源于网络 
2. SELECT    id_item , 
3.                     SUM(dec_qty) dec_qty 
4.           FROM      dpurreq_03 
5.           GROUP BY  id_item 
6.         ) a , 
7.         ( SELECT    a.id_item , 
8.                     SUM(a.dec_endqty) dec_endqty 
9.           FROM      acinv_07 a , 
10.                     ( SELECT DISTINCT 
11.                                 ctlm1101.fiscal_year , 
12.                                 ctlm1101.fiscal_period , 
13.                                 id_table 
14.                       FROM      ctlm1101 
15.                       WHERE     flag_curr = 'Y' 
16.                                 AND id_oprcode = 'acinv' 
17.                     ) b 
18.           WHERE     a.fiscal_year = b.fiscal_year 
19.                     AND a.fiscal_period = b.fiscal_period 
20.                     AND a.id_wh = b.id_table 
21.           GROUP BY  a.id_item 

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

3、避免count(*)

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

4、避免使用NULL字段

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

1. 数据初始化: 
2. create table table1 ( 
3.     `id` INT (11) NOT NULL, 
4.     `name` varchar(20) NOT NULL 
5. ) 
6.  
7.  
8. create table table2 ( 
9.     `id` INT (11) NOT NULL, 
10.     `name`  varchar(20) 
11. ) 
12.  
13. insert into table1 values (4,"tianweichang"),(2,"zhangsan"),(3,"lisi") 
14. insert into table2 values (1,"tianweichang"),(2, null) 

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

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

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

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

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

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

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

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

1. select count(name) from table2; 

5、避免select

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

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

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

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

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

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

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

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

原文链接：https://mp.weixin.qq.com/s/PIQDfHVYBEJLzxaaRSfpgA