前言

系列文章：
1.MySQL主從復(fù)制
2.OneProxy實(shí)現(xiàn)MySQL讀寫分離
3.MySQL數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)
4.MySQL基于GTID主從復(fù)制的雜談

在編碼過(guò)程中，如果MySQL數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)不好的話，會(huì)大大影響開發(fā)人員編碼效率。比如說(shuō)MySQL數(shù)據(jù)庫(kù)表設(shè)計(jì)不規(guī)范，創(chuàng)建時(shí)間字段設(shè)計(jì)成cjsj，創(chuàng)建者字段設(shè)計(jì)成cjr或者cjz。這樣的數(shù)據(jù)庫(kù)表可讀性和表意性相當(dāng)差。下面我們就來(lái)講講如何規(guī)范設(shè)計(jì)數(shù)據(jù)庫(kù)結(jié)構(gòu)。

數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化

數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化的目的有哪些？

• 減少數(shù)據(jù)冗余。
• 盡量避免數(shù)據(jù)維護(hù)中出現(xiàn)更新，插入，刪除異常。插入異常是指如果表中的某個(gè)實(shí)體隨著另一個(gè)實(shí)體而存在。更新異常是指如果更改表中的某個(gè)實(shí)體的單獨(dú)屬性時(shí)，需要對(duì)多行進(jìn)行更新。刪除異常是指如果刪除表中的某一實(shí)體則會(huì)導(dǎo)致其他實(shí)體消失。
• 節(jié)約數(shù)據(jù)查詢空間。

假設(shè)有一張學(xué)生選課表，字段信息如下：

id ,
stu_id //學(xué)生id,
stu_name //學(xué)生姓名,
stu_sex //學(xué)生性別,
course_name //課程名稱,
course_point //學(xué)分

插入異常就是當(dāng)插入幾門課程的時(shí)候，stu_id和stu_name，stu_sex會(huì)為空。如果將stu_id設(shè)置為非空，會(huì)造成異常。

更新異常就是當(dāng)更新某一個(gè)課程的學(xué)分時(shí)，那么這一門科目的選課記錄都將要更新。

刪除異常就是當(dāng)刪除某一個(gè)課程時(shí)，那么這一門科目的選課記錄都要?jiǎng)h除。

解決上述異常很簡(jiǎn)單，設(shè)計(jì)數(shù)據(jù)庫(kù)表時(shí)遵循數(shù)據(jù)庫(kù)三大范式即可。

數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)又分為邏輯設(shè)計(jì)和物理設(shè)計(jì)。
前面說(shuō)的數(shù)據(jù)庫(kù)三大范式可以說(shuō)是邏輯設(shè)計(jì)。邏輯設(shè)計(jì)是根據(jù)數(shù)據(jù)實(shí)體之間的邏輯關(guān)系對(duì)表進(jìn)行設(shè)計(jì)。一個(gè)好的邏輯設(shè)計(jì)可以解決數(shù)據(jù)冗余和數(shù)據(jù)維護(hù)的異常，反之亦然。

物理設(shè)計(jì)則是根據(jù)所使用的數(shù)據(jù)庫(kù)特點(diǎn)進(jìn)行表結(jié)構(gòu)設(shè)計(jì)。比如Myisam引擎不支持事務(wù)，但是支持并發(fā)插入的表級(jí)鎖，主要應(yīng)用于select,insert。不適合讀寫頻繁的場(chǎng)景。Innodb支持事務(wù)，支持MVCC(多版本并發(fā)控制)的行級(jí)鎖，可以應(yīng)用事務(wù)處理。
維護(hù)優(yōu)化是指根據(jù)實(shí)際情況對(duì)索引存儲(chǔ)結(jié)構(gòu)等進(jìn)行優(yōu)化。

一般數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)的步驟是：
1.需求分析：全面了解產(chǎn)品設(shè)計(jì)的存儲(chǔ)需求。存取需求是指數(shù)據(jù)庫(kù)要存儲(chǔ)什么樣的數(shù)據(jù)，這些數(shù)據(jù)具有什么特點(diǎn)。數(shù)據(jù)處理需是指如何對(duì)數(shù)據(jù)庫(kù)進(jìn)行讀取和寫入以及對(duì)數(shù)據(jù)的響應(yīng)時(shí)間有什么樣的要求，數(shù)據(jù)的安全性和完整性需求是數(shù)據(jù)的生命周期。
2.進(jìn)行邏輯設(shè)計(jì)和物理設(shè)計(jì)。
3.維護(hù)優(yōu)化。

有一些場(chǎng)景，我們需要反范式化設(shè)計(jì)。比如查詢訂單信息時(shí)，我們肯定是要查詢下單的收貨人信息。如果我們更改了收貨人信息，那么查詢訂單時(shí)，會(huì)出現(xiàn)收貨人信息不一致的現(xiàn)象。所以我們要在訂單表中冗余收貨人信息。

范式化與反范式化

反范式化設(shè)計(jì)就是為了性能和讀取效率的考慮而適當(dāng)?shù)膶?duì)數(shù)據(jù)庫(kù)設(shè)計(jì)范式進(jìn)行違反，而允許存在少量數(shù)據(jù)冗余。換句話來(lái)說(shuō)反范式化就是使用空間換時(shí)間。

范式化的優(yōu)點(diǎn)：
1.可以減少數(shù)據(jù)冗余。
2.范式化的更新操作比反范式化要快。
3.范式化的表同樣比反范式化的表要小。

范式化的缺點(diǎn)：
1.關(guān)聯(lián)查詢。
2.更難于索引優(yōu)化。

反范式化優(yōu)點(diǎn)：
1.減少表的關(guān)聯(lián)。
2.更好的索引優(yōu)化，覆蓋索引。

反范式化缺點(diǎn)：
1.存在數(shù)據(jù)冗余及數(shù)據(jù)維護(hù)異常。
2.對(duì)數(shù)據(jù)的修改需要更多的成本。

如何為表選擇字段類型

在物理設(shè)計(jì)中，我們要做到可讀性，表意性，長(zhǎng)名性。
當(dāng)我們?yōu)楸磉M(jìn)行物理設(shè)計(jì)時(shí)，常常為對(duì)表中的字段選擇合適的數(shù)據(jù)類型進(jìn)行糾結(jié)。當(dāng)一個(gè)列可以選擇多種數(shù)據(jù)類型時(shí)，應(yīng)該優(yōu)化考慮數(shù)字類型，其次是日期或者二進(jìn)制類型，最后是字符串類型。對(duì)于相同級(jí)別的數(shù)據(jù)類型，應(yīng)該優(yōu)化考慮占用空間小的數(shù)據(jù)類型。

Innodb一頁(yè)是16K。

tinyint 占1個(gè)字節(jié)
smallint占2個(gè)字節(jié)
mediumint占3個(gè)字節(jié)
int占4個(gè)字節(jié)
bigint占8個(gè)字節(jié)

float 占4個(gè)字節(jié)，非精確
double占8個(gè)字節(jié)，非精確
decimal 每4個(gè)字節(jié)存9個(gè)數(shù)據(jù)，小數(shù)點(diǎn)占1個(gè)字節(jié)。比如decimal(18,9)需要9個(gè)字節(jié)來(lái)存儲(chǔ)，最多支持65個(gè)數(shù)字。 精確

year占1個(gè)字節(jié)
time占3個(gè)字節(jié)
date占3個(gè)字節(jié)
datetime占8個(gè)字節(jié)
timestamp占4個(gè)字節(jié)

以UTF-8為例，中文占3個(gè)字節(jié)，英文占1個(gè)字節(jié)。
下面我們就以字符串和日期類型為例，講一講。

varchar和char中寬度的定義是字符長(zhǎng)度。
varchar用于存儲(chǔ)變長(zhǎng)字符串，只占用必要的存儲(chǔ)空間，最多65535。varchar的最大長(zhǎng)度小于255，則占用一個(gè)額外字節(jié)記錄字符串長(zhǎng)度。大于255，則占用2個(gè)額外字節(jié)用于記錄字符串長(zhǎng)度。在mysql老版本的時(shí)候，修改varchar的長(zhǎng)度會(huì)鎖表。在mysql5.7之后，修改之后不超過(guò)255，是不會(huì)鎖表。

varchar的適用場(chǎng)景：
1.字符串的最大長(zhǎng)度比平均長(zhǎng)度大很多。
2.字符串很少被更新。
3.使用了多字節(jié)字符集來(lái)存儲(chǔ)字符串。

char類型的存儲(chǔ)特點(diǎn)：
1.char類型是定長(zhǎng)的。
2.字符串存儲(chǔ)在char類型的列中會(huì)刪除末尾的空格。
3.最大寬度為255。

char適用的場(chǎng)景：
1.char類型適用于存儲(chǔ)所有長(zhǎng)度近似的值。
2.char類型適合存儲(chǔ)短字符串。
3.char類型適用存儲(chǔ)經(jīng)常更新的字符串，可以避免形成存儲(chǔ)碎片。

datetime類型以YYYY-MM-DD HH:MM:SS.[fraction]格式存儲(chǔ)日期時(shí)間。
datetime = YYYY-MM-DD HH:MM:SS
datetime(6)=YYYY-MM-DD HH:MM:SS.fraction
datetime類型與時(shí)區(qū)無(wú)關(guān)，占用8個(gè)字節(jié)來(lái)存儲(chǔ)時(shí)間。
時(shí)間范圍為1000-01-01 00:00:00 ~9999-12-31 23:59:59

timestamp占用4個(gè)字節(jié)，代表的時(shí)間為格林威治時(shí)間。時(shí)間范圍是1970-01-01到2038-01-19。timestamp類型顯示依賴于所指定的時(shí)區(qū)。在行的數(shù)據(jù)被修改時(shí)，可以自動(dòng)修改timestamp列的值。如果一行記錄有多個(gè)timestamp的字段，那么修改該記錄時(shí)只有第一個(gè)timestamp類型的字段會(huì)自動(dòng)更新時(shí)間。我們可以在定義timestamp類型字段時(shí)加上default current_timestamp on update current_timestamp

date占用的字節(jié)數(shù)要比使用字符串、datetime、int存儲(chǔ)的要少。使用date類型只需要3個(gè)字節(jié)。使用date類型還可以利用日期時(shí)間函數(shù)進(jìn)行日期相關(guān)的計(jì)算。時(shí)間范圍為1000-01-01~9999-12-31

time類型用于存儲(chǔ)時(shí)間數(shù)據(jù)，格式為HH:mm:ss

我們?cè)诖鎯?chǔ)日期格式相關(guān)的數(shù)據(jù)時(shí)，要注意以下幾點(diǎn)：
1.不要使用字符串類型來(lái)存儲(chǔ)日期時(shí)間數(shù)據(jù)。
2.日期時(shí)間類型通常要比字符串占用的存儲(chǔ)空間小。
3.日期類型在進(jìn)行查詢過(guò)濾時(shí)，可以利用日期來(lái)進(jìn)行對(duì)比，避免隱式轉(zhuǎn)換造成索引全盤掃描。
4.日期時(shí)間類型有豐富的處理函數(shù)，可以更加方便對(duì)日期類型數(shù)據(jù)進(jìn)行日期過(guò)濾。
5.使用int存儲(chǔ)日期時(shí)間還不如使用timestamp類型。

我們可以創(chuàng)建測(cè)試表，來(lái)測(cè)試不同日期類型的查詢速度。

create table `date_demo`
(
id int(11) not null auto_increment,
`time` TIME not null,
`timestamp` TIMESTAMP not null,
`datetime` datetime not null,
`date` date not null,
`int_date` bigint(20) not null,
primary key (`id`),
key `idx_time`(`time`),
key `idx_timestamp`(`timestamp`),
key `idx_datetime`(`datetime`),
key `idx_date`(`date`),
key `idx_int_date`(`int_date`)
)

為了更加直觀的看見結(jié)果，我們插入200w測(cè)試數(shù)據(jù)。

    public static void test() {
        try {
            long start = System.currentTimeMillis();
            String url = "jdbc:mysql://127.0.0.1:3306/groupon?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false";
            String username = "root";
            String password= "root";
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver);
            Connection conn  = DriverManager.getConnection(url, username, password);
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("insert into date_demo (time,timestamp,datetime,date,int_date) values(now(),now(),now(),now(),now())");

            for (int i = 1; i <= 1999; i++) {
                sqlBuffer.append(" ,(now(),now(),now(),now(),now()) ");
            }

            PreparedStatement pstmt = conn.prepareStatement(sqlBuffer.toString());

            for (int i = 1; i <= 500; i++) {
                int result = pstmt.executeUpdate();
                System.out.println("result=" + result);
            }
            pstmt.close();
            conn.close();
            long end = System.currentTimeMillis();
            System.out.println("cost=" + (end - start) + "ms");
        } catch (Exception e) {
           e.printStackTrace();
        }
    }

time查詢時(shí)間為0.233s

select * from date_demo
where time = '23:13:09'

image.png

timestamp查詢時(shí)間為0.230s

select * from date_demo
where `timestamp` = '2018-10-28 23:13:09'

image.png

datetime查詢時(shí)間為0.242s

select * from date_demo
where datetime = '2018-10-28 23:13:09'

image.png

date查詢時(shí)間為0.221s

select * from date_demo
where date = '2018-10-28' limit 9

image.png

int查詢時(shí)間為0.211s

select * from date_demo
where int_date = 20181028231309

image.png

查詢速度從快到慢如下：bigint> date>timestamp>time>datetime(僅供參考)

尾言

大家好，我是cmazxiaoma(寓意是沉夢(mèng)昂志的小馬)，感謝各位閱讀本文章。
小弟不才。
如果您對(duì)這篇文章有什么意見或者錯(cuò)誤需要改進(jìn)的地方,歡迎與我討論。
如果您覺得還不錯(cuò)的話,希望你們可以點(diǎn)個(gè)贊。
希望我的文章對(duì)你能有所幫助。
有什么意見、見解或疑惑，歡迎留言討論。

最后送上：心之所向，素履以往。生如逆旅，一葦以航。

saoqi.png