[MySQL] schema 與資料類型優化

選擇優化的資料類型

原則:

  • 越小越好
    • 因為會佔用最小的磁碟、記憶體與CPU快取。
  • 越簡單越好
    • 操作整數比操作字串代價低，因為字串排序規則
    • ex:
      • 儲存ip應該用 unsigned int - 4 bytes, 用 varchar 會用到 15 bytes
      • 儲存時間應該用 mysql 內建的時間類型
        • DATETIME, TIMESTAMP可以儲存相同資料：時間日期，精確到秒
        • TIMESTAMP 只用 DATETIME 一半的儲存空間，根據時區自動變化
        • TIMESTAMP 允許的時間範圍比 DATETIME 小很多
  • 避免 NULL
    • 在 innodb 上有單獨的 bit 儲存 null 值，但在 myisam，但還是盡量避免

整數類型

  • tinyint - 8 bits
  • smallint - 16 bits
  • mediumint - 24 bits
  • int - 32 bits
  • bigint - 64 bits
  • 如果是有正負數的，會因為 1 bits 會用來記錄正負，故範圍為 -2^(n-1) ~ 2^(n-1)
  • unsigned 的範圍則是 -2^n ~ 2^n
    • MySQL 可以為整數指定寬度，例如 INT(11)，其實沒什麼意義，11只是表示與 MySQL 相關工具(client 端的 cli) 用來顯示的長度，對於儲存與計算來說，INT(1)與INT(20)是一樣的。

實數類型

decimal(M,N) - M: 最大位數, N: 小數點後有幾位
ex: decimal(18, 9) => 9位數字.9位數字，總共使用 9 個 bytes

float => 4 bytes
double => 8 bytes

decimal的計算雖然比較精確，但需要額外，但需要額外的空間與計算開銷，資料量較大時，可以用 bigint 代替 decimal，假設需要精準到萬分之一，則可以將數字*1百萬，將結果存在 bigint 內，可以同時避免福點數計算不精準與 decimal 計算代價太高。

字串類型

VARCHAR
row_format=fix，這樣就都會固定長度，沒意義
varchar會需要額外1~2 bytes記錄長度,
假設用 latin1 charset, varchar(10) => 11 bytes, varchar(1000) => 1002 bytes
varchar 適合使用情境
儲存資料的字串長度比平均字串長度大很多，或是像使用了 utf8 這種複雜的 charset，每個字都用不同的 bytes 進行儲存
InnoDB會把過長的 varchar 轉成 blob

CHAR
適合儲存很短的字串或是所有字串的長度接近同個值，在儲存效率上也比 VARCHAR 好。
ex: 用 CHAR(1) 存 Y or N 只需要用1個 byte，但 varchar 會用到2個 bytes

BINARY 與 VARBINARY
The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings. This means they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.
binary 跟 varbinary 存的是二進制的值, 所以 binary(10) => 固定 10 bytes

二進制資料的優勢在大小與 case sensitive 上，由於是根據由於是根據 byte 上的數值進行比較，所以會比比較字串的行為來的快

BINARY 如果有設定幾位，如果長度不足，MySQL 會補 \0 補到需要的長度

BLOB 與 TEXT
BLOB 與 TEXT 專門設計來儲存很大的字串，BLOB 為二進制，TEXT 則是字串
TEXT 就是 SMALLTEXT
BLOG 就是 SMALLBLOB

TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT
TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB

當 BLOB or TEXT 太大時，MySQL 會用外部儲存區域來存值，此時在資料內需要 1~4 bytes 去存放外部儲存區域的指針

MySQL 對於 BLOB, TEXT 的排序跟其他形態不同，它只對每列最前 max_sort_length bytes 去排序，而不是整個字串的值去排序，如果只需要排序前面一小部分的字串，可以減少 max_sort_length 的設定，或著使用 ORDER BY SUBSTRING(column, length)

MySQL 不能將 BLOB, TEXT內的值字串進行索引。
如果 explain 的 extra 包含 Using Temporary => 這個查詢用了隱式臨時表


使用 ENUM 代替字串類型
有時候可以把常用且不常變動的字串定義成 enum，在 MySQL 內部, 會將 enum 存為存為整數，並且在 table 的 .frm 這個檔案中存著"數字-enum字串" mapping 關係的查找表，且 enum 的順序是當初在建立的時候就定義好了，也就是按照當初建立時，內部整數表的大小去排序。


ENUM關聯VARCHAR或是CHAR會很慢，只有ENUM跟ENUM做關聯才會快

日期與時間類型

DATETIME
可以從西元1001年存到9999年，精準到秒。與時區無關，用 8 bytes 的儲存空間。

TIMESTAMP
西元1970年存到2038年，與時區相關，用 4 bytes 的儲存空間。
預設為 NOT NULL。

如果需要儲存如果需要儲存比秒更小粒度，可以用 BIGINT 儲存微秒，或是用 DOUBLE 儲存
秒之後的小數點！(MariaDB 支援到微秒)

Bit-Packed 資料類型

Bit 在 Memory or InnoDB 是用能夠容納指定 bits 最小的整數類型，所以根本不能節省儲存空間。
MySQL 把 bit 視為字串類型，不是數字類型。當你取出 BIT(1) 的值，你會取得一個字串，但這個字串是二進位的 0 or 1，不是 ASCII CODE 的 0 or 1。
舉例來說，假設你把 b'00111001' (也就是10進位的57)，存到BIT(8)並搜尋出來，你會拿到是字串 code 57，也就是 ASCII code 的 9

假設想做一個權限的表，可以用 bit operation 這種方式來實作

選擇 ID 的類型

如果已經選定好 id 的型態，在與這個 id 的所有關聯中，關聯的欄位必須跟這個 id 一模一樣。
ex: 如果這個 id 有 unsigned, 那關聯用的欄位也要有 unsigned. 不然會造成效能的問題。

在可以滿足值的範圍需求，且保留未來增長空間的情況下，應選擇最小的資料類型。
ex: state_id 儲存

如果可以，盡量不要用字串當成 id ，因為很耗空間外，通常還比數字類型的 ID 慢。
尤其是 myisam 使用字串當 id 要小心，因為預設會字串壓縮索引，會導致效能下降大約6倍！

如果用 UUID 當成 id, 比較好的做法是用 unhex() 轉換 UUID 的值轉成 16 bytes 的數字，並存在 BINARY(16) 中，如果要查詢的時候，再使用 hex() 格式化 16 進位。

特殊數據類型

IPV4通常會用VARCHAR(15)來存，但 ipv4 本來就是 32 bits unsigned integer, 所以其實可以直接用 unsigned integer 去存，MySQL 有 inet_aton(), inet_ntoa() 來轉換。



MySQL Schema 設計中的陷阱

1. 太多的欄位
MySQL engine API 是透過複製在伺服器與MySQL engine的 row buffer格式，伺服器再將 row buffer 轉成我們在用的 columns。但是從 row buffer 轉成 column 需要蠻高的成本。
MyISAM 的 fixed row 格式剛好跟伺服器的 row 格式一樣，所以不需要轉換。
但是 MyISAM 的 vairable row 格式與 InnoDB 的 row 格式就都需要轉換。
轉換的代價是看有多少 column 而定。
假如說有張表有幾百個 columns, 即便你只有用到其中幾個，還是會消耗許多CPU資源。

2. 太多 join
MySQL有個限制 - 一個 join 最多只能有 61 個 table. 即便少於 61 個, 解析與優化查詢 (planning and optimizing the query)也會成為 MySQL 的問題！

加快 ALTER TABLE 的速度

1. 先在一台不提供服務的機器上執行 ALTER TABLE 的操作，然後跟提供服務的 master 進行切換。
2. shadow copy: 把舊表的結構拿來建立一張新表，透過 rename 與 drop 來交換兩張表
3. 有些工具可以做到上述第二點
 - fb 維護的 online schema change
 - Shlomi Noach’s openark toolkit
 - Percona toolkit

MySQL而言，不是所有 ALTER TABLE 都會讓表重新建立。
可能假設我要修改一個欄位的預設值，
ex:
ALTER TABLE sakila.film -> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

用 SHOW STATUS; 看這個語法執行的行為，會發現這個語法做了1000次讀與1000次寫入。
MODIFY COLUMN 會拷貝整張表到一張新表 (慢)
ALTER COLUMN 則是直接修改 .frm 這個檔案 (快)

























留言

張貼留言

這個網誌中的熱門文章

[翻譯] 介紹現代網路負載平衡與代理伺服器

圖片
原文(Source): https://blog.envoyproxy.io/introduction-to-modern-network-load-balancing-and-proxying-a57f6ff80236 什麼是網路負載平衡 (network load balancing) 與 代理伺服器的代理行為 (proxying)? 維基百科是這樣定義負載平衡的 > 負載平衡藉由工作量分配到多台運算資源, 例如: 主機, 主機叢集, 網路連結, CPU,  硬碟等. > 負載平衡盡量最佳化資源的使用, 最大化每個最大化每個資源的吞吐量(生產能力), 最小化反應時間, 避免單一資源工作量過重 > 使用多個資源來當負載平衡來提升可靠與高可用而不是單一個資源 > 負載平衡總是使用獨立的軟體或硬體, 例如獨立的 multilayer switch, 或是 DNS主機的程式 上面的定義用於各資訊領域的各方面, 不單單只有網路而已. 作業系統使用負載平衡在實體處理器去分配運算任務, 容器編排服務像是K8S使用負載平衡在不同的主機叢集中去分配任務, 而網路負載平衡器則是將網路上進來的任務分配給可用的後端主機們. 剩下的文章主要都是講解關於網路負載平衡. 圖1 圖1展示了從應用層來看網路負載平衡. 許多的用戶端(Client)透過只需要向 load balancer 請求, load balancer就會將這些request, 也就是上述提到的任務分配給可用的後端服務. Load balancer位於Client 與後端之間執行幾個重要的任務: 服務發現 (Service discovery) 後端的服務在系統的哪裡, 他們的IP(位置)是什麼? (也就是load balancer該如何與後端主機溝通) 服務的狀態檢查 (Health checking) 現在的服務是否是可接受請求的 負載平衡 (Load Balancing) 該用什麼樣的演算法去平均將request分配給可用的服務 在分散式系統使用負載平衡主要有幾個好處: 主機抽象 (Naming abstraction): 每個client 不需要知道每個後端服務在哪, 只需要利用預先定義好的機制向負載平衡器發出請求
閱讀完整內容

Grafana K6

圖片
Grafana K6 最近工作需要做壓力測試, 由於 api 有做自定義的加密 在挑選壓力測試工具的時候就先想到了 k6 安裝 K6 mac: brew install k6. 其餘的可以參考這裡: Installation 使用 K6 撰寫 壓力測試腳本 基本的入門測試可以參考網路上其他大神寫的文章 或是參考這裡: Running K6 這裡主要介紹, 如果要透過額外寫 Node.js 的邏輯上去該怎麼處理。 因為 K6 底層是由 Go 撰寫而成的，js 只是對應的介面， 所以要加入 Node.js 邏輯要額外透過 Webpack 處理！ 步驟 0 : 先建立對應的目錄 mkdir . / example - project && \ cd "$_" && \ npm init - y 步驟 1 : 安裝會用到的工具 npm install -- save - dev \ webpack \ webpack - cli \ @types / k6 \ babel - loader \ @babel / core \ @babel / preset - env \ core - js 步驟 2 : 設定 webpack.config.js const path = require ( 'path' ) ; const webpack = require ( 'webpack' ) ; // 這是新增的，要引入 webpack。 module . exports = { mode : 'production' , entry : { login : './src/login.test.js' , signup : './src/signup.test.js' , } , output : { path : path . resolve ( __dirname , 'dist' ) , // eslint-disable-line li
閱讀完整內容

Linux 事件驅動筆記

圖片
 I/O Model Synchronous I/O Blocking Non-Blocking I/O multiplexing Asynchronous I/O Blocking 跟 Non-Blocking 差異只差在 選擇 ! Blocking 以 read (read from a file descriptor) 來舉例, 從 user space 發出 read (kernel提供的 system call) 之後， 這時候就會切換到 kernel mode，kernel 會依據給定的 file descriptor 去判斷， 這個 fd 來自哪裡，接下來等待資料，因為 kernel space 跟 user space 本身就是 isolated， 這樣在 user space 的 process crash，也不會影響到 kernel space， 所以 kernel space 拿到資料之後，因為 isolated 的關係，要將資料複製到 user space 去。 在這個過程中，user space 的 read 基本上就是一直在等結果。 read 如果失敗會有 err number 可以注意 EAGAIN EWOULDBLOCK Non-blocking 就是類似一直來問 read 目前的狀態， kernel 回到 user space 可能裝置還沒準備好，或是要等之後的操作才會生效， 這時候就會發出 EAGAIN 的錯誤，EAGAIN 不代表是真的錯誤，再試一次可能就可以， I/O Multiplexing (這個邏輯跟數位邏輯的 mux 很像，故取做 IO multiplexing) multiplexing => 有多個輸入(input), 可以透過一個 select 去決定輸出 假設 Linux 要等待很多種資料, 就可以用這個概念，去監控哪個 fd 有資料， 有資料的話就挑選出來! 所以 I/O Multiplexing 本質還是 blocking，只是可以一次監聽多個 所以像網頁伺服器要監聽很多個 incoming request，就可以用這種方式處理 select 單個 process 可以被監聽的 fd 數量有限制 (跟這個 /proc/sys/fs/file-max 有關) 因為是一個一個詢問(輪詢)，所以監聽越多 f
閱讀完整內容