[[420165]]

本文技術(shù)面試題，是本人親身經(jīng)歷的。所以，十年老兵告訴你，作為一枚后端開發(fā)人員，不管你們公司有沒有DBA，數(shù)據(jù)庫相關(guān)技術(shù)都是必備的。

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

菜鳥我：(稍稍微笑，表示回應(yīng))

面試官：用了這么多年的MySQL，有哪些坑，讓你難以忘懷。

菜鳥我：巴拉巴拉 開始扯(這種面試題，我在面試前已經(jīng)準(zhǔn)備過了，所以，灑灑水啦)

下面，就根據(jù)自己的實(shí)戰(zhàn)經(jīng)歷整理了一些數(shù)據(jù)庫開發(fā)的規(guī)范用法，用6個(gè)“避免”來概括。

1、避免在數(shù)據(jù)庫中做運(yùn)算

有句話叫做“別讓腳趾頭想事情，那是腦瓜子的職責(zé)”，用在數(shù)據(jù)庫開發(fā)中，說的就是避免讓數(shù)據(jù)庫做她不擅長的事情。MySQL并不擅長數(shù)學(xué)運(yùn)算和邏輯判斷，所以盡量不在數(shù)據(jù)庫做運(yùn)算，復(fù)雜運(yùn)算可以移到程序端CPU。

2、避免對索引列做運(yùn)算

有次，有位同事讓我看一條SQL，說是在前臺查詢很快，但是把SQL取出來，在數(shù)據(jù)庫中執(zhí)行的時(shí)候，跑10分鐘都不出結(jié)果。看了一下SQL，最后定位到一個(gè)視圖中的一個(gè)子查詢上面。該子查詢的SQL文本如下：

## 以下SQL來源于網(wǎng)絡(luò) 
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是有索引的。但是，如果對索引列進(jìn)行運(yùn)算，就會導(dǎo)致原本可以走索引的走不了索引。于是，動手改寫成如下SQL：

## 以下SQL來源于網(wǎng)絡(luò) 
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 

再執(zhí)行，4s鐘左右就可以跑出結(jié)果了。總的來說，寫SQL時(shí)，不到萬不得已，不要對索引列進(jìn)行計(jì)算。

3、避免count(*)

在分頁查詢的時(shí)候，有的人總是習(xí)慣用select count()獲得總的記錄條數(shù)，實(shí)際上這不是一個(gè)高效的做法，因?yàn)椋矮@得數(shù)據(jù)的時(shí)候已經(jīng)查詢過一次了，select count()相當(dāng)于同一個(gè)語句查詢了兩次，對數(shù)據(jù)庫的開銷自然就大了，我們應(yīng)當(dāng)使用數(shù)據(jù)庫自帶的API，或者系統(tǒng)變量來完成這個(gè)工作。

4、避免使用NULL字段

大家在數(shù)據(jù)庫表字段設(shè)計(jì)的時(shí)候，應(yīng)該盡量都加上NOT NULL DEFAULT'。使用NULL字段會產(chǎn)生很多不好的影響，例如：很難進(jìn)行查詢優(yōu)化、NULL列加索引，需要額外空間、含NULL復(fù)合索引無效…… 看下面的案例：

數(shù)據(jù)初始化: 
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值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e(cuò)

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

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

select * from table2 where name != 'tianweichang' 

select * from table2 where name != 'zhaoyun1' 

(3) 使用concat拼接時(shí)，首先要對各個(gè)字段進(jìn)行非null判斷，否則只要任何一個(gè)字段為空都會造成拼接的結(jié)果為null

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

(4) 當(dāng)計(jì)算count時(shí)候,name為null 的不會計(jì)入統(tǒng)計(jì)

select count(name) from table2; 

5、避免select

使用select *可能會返回不使用的列的數(shù)據(jù)。它在MySQL數(shù)據(jù)庫服務(wù)器和應(yīng)用程序之間產(chǎn)生不必要的I/O磁盤和網(wǎng)絡(luò)流量。

如果明確指定列，則結(jié)果集更可預(yù)測并且更易于管理。想象一下，當(dāng)您使用select *并且有人通過添加更多列來更改表格數(shù)據(jù)時(shí)，將會得到一個(gè)與預(yù)期不同的結(jié)果集。

使用select *可能會將敏感信息暴露給未經(jīng)授權(quán)的用戶。

6、避免在數(shù)據(jù)庫里存圖片

圖片確實(shí)是可以存儲到數(shù)據(jù)庫里的，例如通過二進(jìn)制流將圖片存到數(shù)據(jù)庫中。

但是，強(qiáng)烈不建議把圖片存儲到數(shù)據(jù)庫中!!!!首先對數(shù)據(jù)庫的讀/寫的速度永遠(yuǎn)都趕不上文件系統(tǒng)處理的速度，其次數(shù)據(jù)庫備份變的巨大，越來越耗時(shí)間，最后對文件的訪問需要穿越你的應(yīng)用層和數(shù)據(jù)庫層。

圖片是數(shù)據(jù)庫最大的殺手。一般來說數(shù)據(jù)庫都是存儲一個(gè)URL，然后再通過URL來調(diào)用圖片。

圖片，文件，二進(jìn)制數(shù)這三樣?xùn)|西慎重存儲到數(shù)據(jù)庫中。