Schema與數(shù)據(jù)類型優(yōu)化的示例

小編給大家分享一下Schema與數(shù)據(jù)類型優(yōu)化的示例，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

4.2MySQL schema設(shè)計(jì)中的陷阱

1、太多的列

MySQL存儲(chǔ)引擎api工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列，從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)的操作代價(jià)高，myisam定長(zhǎng)行與服務(wù)器行結(jié)構(gòu)正好匹配，不需要轉(zhuǎn)換；但是變長(zhǎng)行結(jié)構(gòu) InnoDB的行結(jié)構(gòu)總是需要轉(zhuǎn)換，轉(zhuǎn)換代價(jià)依賴于列的數(shù)量。

2、太多的關(guān)聯(lián)

實(shí)體-屬性-值EAV：糟糕的設(shè)計(jì)模式，mysql限制了每個(gè)關(guān)聯(lián)操作最多只能有61張表，但EAV數(shù)據(jù)庫需許多自關(guān)聯(lián)；一個(gè)粗略的經(jīng)驗(yàn)法則，如果希望查詢執(zhí)行得快速且并發(fā)性好，單個(gè)查詢最好在12個(gè)表內(nèi)做關(guān)聯(lián)；

3、防止過度使用枚舉

注意防止過度使用枚舉；使用外鍵關(guān)聯(lián)到字典表或查找表查找具體的值，在mysql中，需要在枚舉列表中添加值時(shí)，要做一次alter table；MySQL5.0更早alter table阻塞操作，5.1更新版本中，不是在列表末尾增加值也會(huì)一樣需要alter table

4、非此發(fā)明not invent here的null

建議存空值可以用0、特殊值、空字符串代替，盡量不要null；但是不要走極端，在某些場(chǎng)景下、使用null會(huì)更好：

create table ……（
//全0 （不可能的日期）會(huì)導(dǎo)致很多問題
    dt datetime not null default '0000-00-00 00:00:00'
    ……
）

MySQL會(huì)在索引中存儲(chǔ)null值，Oracle不會(huì)

4.3范式與反范式

4.3.1優(yōu)缺點(diǎn)

1、范式化的更新操作更快

2、當(dāng)數(shù)據(jù)較好地范式化時(shí)，很少有重復(fù)數(shù)據(jù)，只需要修改更少的數(shù)據(jù)

3、范式化的表更小，可更好地放到內(nèi)存里，執(zhí)行操作更快

4、很少冗余數(shù)據(jù)，檢索列表數(shù)據(jù)時(shí)更少需要distinct、group by語句

缺點(diǎn)：

需要關(guān)聯(lián)，有代價(jià)且可能使索引無效

4.3.2反范式的優(yōu)點(diǎn)和缺點(diǎn)

避免關(guān)聯(lián)，數(shù)據(jù)比內(nèi)存大可能比關(guān)聯(lián)要快很多（避免了隨機(jī)I/O）

4.4緩存表和匯總表

緩存表：

對(duì)優(yōu)化搜索和檢索查詢語句很有效，

存儲(chǔ)那些可以較簡(jiǎn)單地從其他表獲取數(shù)據(jù)（每次獲取速度比較慢）的表

匯總表：保存使用group by語句聚合數(shù)據(jù)的表

使用時(shí)決定是實(shí)時(shí)維護(hù)數(shù)據(jù)還是定期重建，定期重建：節(jié)省資源、碎片少、順序組織的索引（高效）

重建時(shí)，保證數(shù)據(jù)在操作時(shí)依然可用，通過“影子表”來實(shí)現(xiàn)，影子表：一張?jiān)谡鎸?shí)表背后創(chuàng)建的表，在完成建表操作后，可通過原子的重命名操作切換影子表和原表

4.4.1物化視圖

預(yù)先計(jì)算并存在磁盤上的表，可通過各種策略刷新和更新，mysql不原生支持，可使用Justin Swanhart工具flexviews實(shí)現(xiàn)：

flexviews組成：

• 變更數(shù)據(jù)抓取，讀取服務(wù)器二進(jìn)制日志且解析相關(guān)行的變更

• 一系列可以幫助  創(chuàng)建和管理  視圖 的定義   的   存儲(chǔ)過程

• 一些可應(yīng)用變更到    數(shù)據(jù)庫中的物化視圖    的工具

flexviews通過提取對(duì)源表的更改，可增量地重新計(jì)算物化視圖的內(nèi)容：不需要查詢?cè)紨?shù)據(jù)（高效）

4.4.2計(jì)數(shù)器表

計(jì)數(shù)器表：緩存一個(gè)用戶朋友數(shù)、文件下載次數(shù)等，推薦創(chuàng)建一張獨(dú)立的表存儲(chǔ)計(jì)數(shù)器，避免查詢緩存失效；

更新加事務(wù)，只能串行執(zhí)行，為了更高的并發(fā)性，可將計(jì)數(shù)器保存在多行，每次隨機(jī)選一行更新，要統(tǒng)計(jì)結(jié)果時(shí)，聚合查詢；（這個(gè)我讀了兩三邊，可能比較笨吧，就是同一個(gè)計(jì)數(shù)器保存多分，每次選其中一個(gè)更新，最后求和，好像還不是很好理解哈，多讀幾遍吧）

4.5加快alter table 操作的速度

mysql大部分修改表結(jié)構(gòu)是：用新的結(jié)果創(chuàng)建空表、從舊表中查出all數(shù)據(jù)插入新表，刪除舊表

mysql5.1及更新包含一些類型的“在線”操作的支持，整個(gè)過程不需要全鎖表，最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支持通過排序來建索引，建索引更快且緊湊的布局；

一般而言，大部分alter table導(dǎo)致mysql服務(wù)中斷，對(duì)常見場(chǎng)景，使用的技巧：

1、先在一臺(tái)不提供服務(wù)的機(jī)器上執(zhí)行alter table操作，然后和提取服務(wù)的主庫進(jìn)行切換

2、影子拷貝，用要求的表結(jié)構(gòu)創(chuàng)建張和源表無關(guān)的新表，通過重命名、刪表交換兩張表（上有）

不是all的alter table都引起表重建，理論上可跳過創(chuàng)建表的步驟：列默認(rèn)值實(shí)際上存在表的.frm文件中，so可直接修改這個(gè)文件不需要改動(dòng)表本身，但mysql還沒有采用這種優(yōu)化方法，all的modify column將導(dǎo)致表重建；

alter column：通frm文件改變列默認(rèn)值：alter table容許使用alter column、modify column change column修改列，三種操作不一樣;

alter table sakila.film alter column rental_duration set default 5;

4.5.1只修改frm文件

mysql有時(shí)在沒有必要的時(shí)候也重建表，如果愿冒一些風(fēng)險(xiǎn)，可做些其他類型的修改而不用重建表：下面操作可能不能正常工作，先備份數(shù)據(jù)

下面操作不需要重建表：

1、移除一個(gè)列的auto_increment

2、增加、移除、更改enum和set常量，如果移除的是被用到的常量、查詢返回空字符串

基本技術(shù)為想要的表結(jié)果創(chuàng)建新的frm文件，然后用它替換掉已經(jīng)存在的那張表的frm文件：

1、創(chuàng)建一張有相同結(jié)構(gòu)的空表，進(jìn)行所需的修改

2、執(zhí)行flush tables with read lock：關(guān)閉all正在使用的表且禁止任何表被打開

3、交換frm文件

4、執(zhí)行unlock tables釋放第2步的讀鎖

示例略

4.5.2快速創(chuàng)建myISAM索引

1、為高效地載入數(shù)據(jù)到MyISAM表，常用技巧：先禁用索引、載入數(shù)據(jù)、重啟索引：因?yàn)闃?gòu)建索引的工作延遲到數(shù)據(jù)載入后，此時(shí)可通過排序構(gòu)建索引，快且使得索引樹的碎片更少、更緊湊

但是對(duì)唯一索引無效（disable  keys），myisam會(huì)在內(nèi)存中構(gòu)造唯一索引且為載入的每一行檢查唯一性，一旦索引大小超過有效內(nèi)存、載入操作會(huì)越來越慢；

2、在現(xiàn)代版InnoDB中，有個(gè)類似技巧：先刪除all非唯一索引，然后增加新的列，最后重建刪除掉的索引（依賴于innodb快速在線索引創(chuàng)建功能）Percona server可自動(dòng)完成這些操作；

3、像前alter table 的駭客方法來加速這個(gè)操作，但需多做些工作且承擔(dān)風(fēng)險(xiǎn)，這對(duì)從備份中載入數(shù)據(jù)很有用，如already know all data is effective ，and no need to do the unique check

• 用需要的表結(jié)構(gòu)創(chuàng)建一張表，不包括索引（如用load data file 且載入的表是空的，myisam可排序建索引）

• 載入數(shù)據(jù)到表中以構(gòu)建MYD文件

• 按需要的結(jié)構(gòu)創(chuàng)建另外一張空表，這次要包含索引，會(huì)創(chuàng)建.frm .MYI文件

• 獲讀鎖并刷新表

• 重命名第二張表的frm文件 MYI，讓mysql認(rèn)為這是第一張表的文件

• 釋放讀鎖

• 使用repair table來重建表的索引，該操作會(huì)通過排序來構(gòu)建all索引、包括唯一索引

4.6總結(jié)

良好的schema設(shè)計(jì)原則是普通使用的，但mysql有自己的實(shí)現(xiàn)細(xì)節(jié)要注意，概括來說：盡可能保持任何東西小而簡(jiǎn)單總是好的；mysql喜歡簡(jiǎn)單（好恰、我也是）

1. 最好避免使用bit

2. 使用小而簡(jiǎn)單的合適類型；

3. 盡量使用整型定義標(biāo)識(shí)列

4. 避免過度設(shè)計(jì)，比如會(huì)導(dǎo)致極復(fù)雜查詢的schema設(shè)計(jì)，或很多列；

5. 應(yīng)該盡可能避免使用null值，除非真實(shí)數(shù)據(jù)模型中有確切需要

6. 盡量使用相同的類型存儲(chǔ)相似、相關(guān)的值，特別是關(guān)聯(lián)條件中使用的列

7. 注意可變長(zhǎng)字符串，其在臨時(shí)表和排序時(shí)可能導(dǎo)致悲觀的按max長(zhǎng)度分配內(nèi)存

8. 避免使用遺棄的特性，如指定浮點(diǎn)數(shù)的精度，或整數(shù)的顯示寬度

9. 小心使用enum和set，雖然他們用起來很方便，但不要濫用，有時(shí)會(huì)變陷阱

10. 范式是好的，但反范式有時(shí)也是必要的；預(yù)先計(jì)算、緩存或生成匯總表也可獲很大好處

11. alter table 大部分情況會(huì)鎖表且重建整張表（讓人痛苦）本章提供了一些有風(fēng)險(xiǎn)的方法，

看完了這篇文章，相信你對(duì)“Schema與數(shù)據(jù)類型優(yōu)化的示例”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！