小編給大家分享一下Schema與數(shù)據(jù)類型優(yōu)化的示例，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
成都創(chuàng)新互聯(lián)公司電話聯(lián)系：18980820575，為您提供成都網(wǎng)站建設(shè)網(wǎng)頁設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù)，成都創(chuàng)新互聯(lián)公司網(wǎng)頁制作領(lǐng)域十載，包括成都玻璃貼膜等多個(gè)方面擁有豐富設(shè)計(jì)經(jīng)驗(yàn)，選擇成都創(chuàng)新互聯(lián)公司，為企業(yè)錦上添花。
MySQL存儲(chǔ)引擎api工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列，從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)的操作代價(jià)高，myisam定長行與服務(wù)器行結(jié)構(gòu)正好匹配，不需要轉(zhuǎn)換；但是變長行結(jié)構(gòu) InnoDB的行結(jié)構(gòu)總是需要轉(zhuǎn)換，轉(zhuǎn)換代價(jià)依賴于列的數(shù)量。
實(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)；
注意防止過度使用枚舉；使用外鍵關(guān)聯(lián)到字典表或查找表查找具體的值，在mysql中，需要在枚舉列表中添加值時(shí)，要做一次alter table；MySQL5.0更早alter table阻塞操作，5.1更新版本中，不是在列表末尾增加值也會(huì)一樣需要alter table
建議存空值可以用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ì)
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à)且可能使索引無效
避免關(guān)聯(lián)，數(shù)據(jù)比內(nèi)存大可能比關(guān)聯(lián)要快很多（避免了隨機(jī)I/O）
緩存表：
對(duì)優(yōu)化搜索和檢索查詢語句很有效，
存儲(chǔ)那些可以較簡單地從其他表獲取數(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)建的表，在完成建表操作后，可通過原子的重命名操作切換影子表和原表
預(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ù)（高效）
計(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è)更新，最后求和，好像還不是很好理解哈，多讀幾遍吧）
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;
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步的讀鎖
示例略
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索引、包括唯一索引
良好的schema設(shè)計(jì)原則是普通使用的，但mysql有自己的實(shí)現(xiàn)細(xì)節(jié)要注意，概括來說：盡可能保持任何東西小而簡單總是好的；mysql喜歡簡單（好恰、我也是）
最好避免使用bit
使用小而簡單的合適類型；
盡量使用整型定義標(biāo)識(shí)列
避免過度設(shè)計(jì)，比如會(huì)導(dǎo)致極復(fù)雜查詢的schema設(shè)計(jì)，或很多列；
應(yīng)該盡可能避免使用null值，除非真實(shí)數(shù)據(jù)模型中有確切需要
盡量使用相同的類型存儲(chǔ)相似、相關(guān)的值，特別是關(guān)聯(lián)條件中使用的列
注意可變長字符串，其在臨時(shí)表和排序時(shí)可能導(dǎo)致悲觀的按max長度分配內(nèi)存
避免使用遺棄的特性，如指定浮點(diǎn)數(shù)的精度，或整數(shù)的顯示寬度
小心使用enum和set，雖然他們用起來很方便，但不要濫用，有時(shí)會(huì)變陷阱
范式是好的，但反范式有時(shí)也是必要的；預(yù)先計(jì)算、緩存或生成匯總表也可獲很大好處
alter table 大部分情況會(huì)鎖表且重建整張表（讓人痛苦）本章提供了一些有風(fēng)險(xiǎn)的方法，
看完了這篇文章，相信你對(duì)“Schema與數(shù)據(jù)類型優(yōu)化的示例”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！
重慶分公司
重慶分公司
