Re: [請益] 資料庫規劃問題 (MySQL)

[gpmm (銀色)]

※ 引述《alpe (薛丁格的貓)》之銘言：
: ※ 引述《characterlu (MineMine)》之銘言：
: : 標題: [請益] 資料庫規劃問題 (MySQL)
: : 時間: Tue Mar 13 23:48:04 2012
: : 資料庫有兩種規劃方式
: : A: 有100個欄位 但資料有10萬筆
: : B: 20個資料表，每個資料表5個欄位 資料有200萬筆
: : 這兩種方式，讀取、寫入、搜尋
: : 請幫忙比較這二種規劃方式
: : 電腦負載及執行速度
: : 本身是新手，如果有問錯的地方請多多包含
: : → eugene2528:這確實像課本問題                                     03/14 12:31
: 早上看到的時候也真的覺得好作業的問題

    我也以為是作業文，
    為什麼？怎麼看都是「未經思考」就丟出來的問題…

: : → characterlu:先聲明這並不是課本問題,是我實務上遇到的             03/14 13:07
: 在現在公司我目前經手的網站會員大概 7M+, 一個會員的資料過百個cols.
: 1. 簡單問一句, 你會很常一次就抓出 70 cols 嗎?
: 我猜不會... table 100 cols 光 programmer要看col就累了
: 2. 你會讓 user 一次寫 50 個欄位嗎?
: 我猜還是不會...  如果會... UI設計的人抓出去斃了
: 1 + 2 一般就可以讓你打消 A 方案了.
: 但... A會不會存在??
: 我可以肯定的回答 Yes, it's exist! 但通常用在統計 or 彙整.
: 從效能看
: SELECT : 看你的怎樣用. Index 建法  一般是 a >= b
:        如果where條件都在一個table,
:        其他table都是 join 資料, 那不會慢太多
:       你可以用 EXPLAIN 看看 SQL, 現在最佳化做的都不錯.
: INSERT : 1個 insert 的話 A >> B , 但
:         100 cols 1次 insert?? 這不常發生啊.
:         多個 insert 下... B 可能會比較好, 看index 設計.
: 負載,多叫幾台機器來檔.
: 到一定的量之後, 都要用空間換取時間. 預先做好類似的table.
: 這樣才會快
: 我這邊同個event的table可能有100個. 會員資料表也有近百個
: 有7個以上的會員搜尋用的table.
: 在7M+ where10個條件下 order 3個col 頁面還是可以2sec給出來


    基本上你的問題簡直是有描述跟沒描述一樣…

    資料庫實做是非常非常依需求判定的東西，並不是靠正規/反正規就可以解出來，
    你至少要描述（釐清）自己的需求，旁人才有辦法幫你想，

    而且我覺得你的說明有問題…

    A: 有100個欄位 但資料有10萬筆
    B: 20個資料表，每個資料表5個欄位 資料有200萬筆
                                           ^^^

    這邊應該是 20 個表加起來總和是 200 萬吧？ -_-


    資料表設計，欄位的數量絕對不是唯一考量，
    還包括存取頻率、存取方式（指 SQL）、欄位性質…等等，

    這樣說好了，在你的二分題目裡，如果

    1. 你的 100 欄位的資料表是因為這些資料真的都是榜在一起的，
       每次都會需要一起存取，那你應該考慮的是當表養大之後要怎麼處理，
       是真的有必要切欄位（增加 SQL 數），或著是切資料（手動轉索引）也可以？

    2. 如果你的 100 欄位其實存取率高的只有那 40 個，
       其餘都是在某個特定情況才會用到，那這樣榜在一起就顯然不合理了，

    簡單來說，當你切割欄位只會在特定情況增加 SQL 存取數，那就有切割的價值，
    不過除非你資料表的量大到百萬外加高吞吐，100 欄位其實也還好。


    一個重要的資料表的設計絕對有很多要考量的因素在裡面，欄位不是唯一，
    我前公司 50萬+ 會員，資料表數 150+，其中有個站內訊息的表資料量破億，

    這個表欄位數不到 10 欄，但只要幾個人同時各刪 10 筆資料，
    伺服器大概就要花上一兩分鐘來解，很恐怖吧？

    原本的設計者一開始也沒想過資料會變這麼大，所以整個就是天然呆的設計…



    提供幾個資料庫設計的小細節（在 MySQL 下），


    ※ 欄位有分為固定長度和變動長度，兩種欄位分開放是有某些好處，
       因為變動長度的欄位資料在不斷新增過程中是碎裂的，
       所以會常需要跑最佳化來維持讀取上的效能

    ※ 延續上一點，雖然 char 是固定長度格式，
       但只要你塞 text 之類的變動長度欄位，char 會自動轉為 varchar

    ※ 如果你弄了很多欄位來放 flag / 只有 Yes,No 的 enum / 只有 0,1 的 char …
       建議把相關的「設定欄位」合起來變 bit 運算，PHP 裡宣告常數去判定，
       相信我，你會習慣的

    ※ 新增資料並不會磨損資料表的最佳化（大部分來說），刪除和更新才會

    ※ 索引只下必要的就好，大量無謂的索引只會令資料在異動時異常辛苦


    ※ … 一下想不出來還有什麼了（搔頭）


--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 61.219.113.121
※ 編輯: gpmm            來自: 61.219.113.121       (03/15 02:20)
推 mrbigmouth:推全是精華
推 mervynW:大推.
推 liaosankai:推經驗分享
推 LaPass:推
→ chrisQQ:bit 運算超好用！
推 kusoayan:可以解釋一下什麼是用 bit 運算嗎@@？
推 mervynW:我猜是早期mysql沒有boolean, 所以用 bit(1) 來代替
推 characterlu:受教了,還是聲明一下,因為是新手,也許問的問題會讓人
→ characterlu:覺得不經思考或問錯,這我願意受教,但只是想不想回答是
→ characterlu:用是否為作業去思考,那跟態度或內容恐怕就無關了
→ mervynW:多個boolean也行 bit(3) ex rwx
推 LaPass:我還以為是拿個int當一串bit那種位元運算的技巧 囧"
推 mervynW:用 int 所佔得空間比較大. bit 你知道的.
→ MOONRAKER:c先生得了便宜就不用賣乖了。