前言

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

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

數(shù)據(jù)庫結構優(yōu)化

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

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

假設有一張學生選課表，字段信息如下：

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

插入異常就是當插入幾門課程的時候，stu_id和stu_name，stu_sex會為空。如果將stu_id設置為非空，會造成異常。

更新異常就是當更新某一個課程的學分時，那么這一門科目的選課記錄都將要更新。

刪除異常就是當刪除某一個課程時，那么這一門科目的選課記錄都要刪除。

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

數(shù)據(jù)庫結構設計又分為邏輯設計和物理設計。
前面說的數(shù)據(jù)庫三大范式可以說是邏輯設計。邏輯設計是根據(jù)數(shù)據(jù)實體之間的邏輯關系對表進行設計。一個好的邏輯設計可以解決數(shù)據(jù)冗余和數(shù)據(jù)維護的異常，反之亦然。

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

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

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

范式化與反范式化

反范式化設計就是為了性能和讀取效率的考慮而適當?shù)膶?shù)據(jù)庫設計范式進行違反，而允許存在少量數(shù)據(jù)冗余。換句話來說反范式化就是使用空間換時間。

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

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

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

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

如何為表選擇字段類型

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

Innodb一頁是16K。

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

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

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

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

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

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

char類型的存儲特點：
1.char類型是定長的。
2.字符串存儲在char類型的列中會刪除末尾的空格。
3.最大寬度為255。

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

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

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

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

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

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

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

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`)
)

為了更加直觀的看見結果，我們插入200w測試數(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查詢時間為0.233s

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

image.png

timestamp查詢時間為0.230s

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

image.png

datetime查詢時間為0.242s

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

image.png

date查詢時間為0.221s

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

image.png

int查詢時間為0.211s

select * from date_demo
where int_date = 20181028231309

image.png

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

尾言

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

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

saoqi.png