Categories
程式開發

MySQL數據庫開發中的6個“避免”


關注“Java後端技術全棧”**

回复“面試”獲取全套大廠面試資料

由於近期工作涉及數據庫相關的操作較多,就根據自己的實戰經歷整理了一些數據庫開發的規範用法,利用6個“避免”來概括。

1、避免在數據庫中做運算

有句話叫做“別讓腳趾頭想事情,那是腦瓜子的職責”,用在數據庫開發中,說的就是避免讓數據庫做她不擅長的事情。 MySQL並不擅長數學運算和邏輯判斷,所以盡量不在數據庫做運算,複雜運算可以移到程序端CPU。

2、避免對索引列做運算

有次,有位同事讓我看一條SQL,說是在前台查詢很快,但是把SQL取出來,在數據庫中執行的時候,跑10分鐘都不出結果。

看了一下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:

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,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values (1,"zhaoyun"),(2, null)

看下面的案例:

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

MySQL數據庫開發中的6個“避免” 1

(1) NOT IN子查詢在有NULL值的情況下返回永遠為空結果,查詢容易出錯

select * from table2 where name != 'zhaoyun'

MySQL數據庫開發中的6個“避免” 2

select * from table2 where name != 'zhaoyun1'

MySQL數據庫開發中的6個“避免” 3

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

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

MySQL數據庫開發中的6個“避免” 4

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

select count(name) from table2;

MySQL數據庫開發中的6個“避免” 5

(4) 當計算count時候null column不會計入統計

5、避免select *

使用select *可能會返回不使用的列的數據。它在MySQL數據庫服務器和應用程序之間產生不必要的I/O磁盤和網絡流量。如果明確指定列,則結果集更可預測並且更易於管理。想像一下,當您使用select *並且有人通過添加更多列來更改表格數據時,將會得到一個與預期不同的結果集。使用select *可能會將敏感信息暴露給未經授權的用戶。

6、避免在數據庫裡存圖片

圖片確實是可以存儲到數據庫裡的,例如通過二進制流將圖片存到數據庫中。

但是,強烈不建議把圖片存儲到數據庫中! ! ! !首先對數據庫的讀/寫的速度永遠都趕不上文件系統處理的速度,其次數據庫備份變的巨大,越來越耗時間,最後對文件的訪問需要穿越你的應用層和數據庫層。

圖片是數據庫最大的殺手。一般來說數據庫都是存儲一個URL,然後再通過URL來調用圖片。

圖片,文件,二進制數這三樣東西慎重存儲到數據庫中。

推薦閱讀【原創】SpringBoot快速整合Thymeleaf模板引擎

【原創】Spring Boot 集成Spring Data JPA的玩法

【原創】Spring Boot集成Mybatis的玩法

MySQL數據庫開發中的6個“避免” 6