美團面試：使用MySQL，你遇到過哪些坑洞？

面試官：對於鎖定這一塊，你還是掌握的蠻好的。

菜鳥我：（稍微笑，表示回應）

面試官：用了這麼多年的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)

(2) 欄位值允許為空，索引不儲存null值，結果集中不會包含這些記錄。

select * from table2 where name != 'tianweichang'

#

select * from table2 where name != 'zhaoyun1'

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

######### ######(3) 使用###concat###拼接時，首先要對各個欄位進行非null判斷，否則只要任何一個欄位為空都會造成拼接的結果為null####

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

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

select count(name) from table2;

5、避免select

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

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

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

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

以上是美團面試：使用MySQL，你遇到過哪些坑洞？的詳細內容。更多資訊請關注PHP中文網其他相關文章！