mysql~關於mysql分割槽表的測試

张占岭發表於2024-03-13
MySql

在MySQL 8.0中，可以透過建立自定義雜湊函式來處理VARCHAR型別的欄位，以便用作分割槽鍵。下面是一個簡單的示例，演示如何在MySQL8.0中建立自定義雜湊函式來處理VARCHAR型別的欄位

  • 分割槽後的表效果

方法一，自定義雜湊函式，失敗而告終

  1. 建立自定義雜湊函式：
DELIMITER //

CREATE FUNCTION custom_hash(str VARCHAR(255)) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE hash_val INT;
    SET hash_val = 0;
    
    -- 計算字串的雜湊值
    SET hash_val = CRC32(user_id) % 3;
    RETURN hash_val;
END//

DELIMITER ;

在上面的示例中，我們建立了一個名為custom_hash的自定義雜湊函式，接受一個VARCHAR型別的引數，並返回一個整數型別的雜湊值。這裡使用了MD5演算法計算字串的雜湊值，並將其轉換為有符號整數。

  1. 使用自定義雜湊函式進行分割槽：
CREATE TABLE user_attribute (
    user_id VARCHAR(50),
    attribute_name VARCHAR(50),
    attribute_value VARCHAR(255),
    PRIMARY KEY (user_id, attribute_name)
) PARTITION BY HASH(custom_hash(user_id)) PARTITIONS 4;

在上面的示例中，我們建立了一個名為user_attribute的表，並使用自定義雜湊函式custom_hash對user_id欄位進行雜湊計算，然後將結果用作分割槽鍵進行分割槽。

透過以上步驟，你可以自定義一個雜湊函式來處理VARCHAR型別的欄位，並將其應用於分割槽表的設計中。請注意，在實際應用中，需要根據具體需求和資料特徵來設計和調整雜湊函式的邏輯。希朼這個示例能夠幫助你解決問題。如果有任何其他問題，請隨時告訴我。

方法二，新增虛擬欄位，失敗而告終

  1. 為已有表新增虛擬欄位
 ALTER TABLE user_attribute_pattern ADD COLUMN user_id_hash2    generated always  varchar(512) AS (CRC32(user_id) % 3)  VIRTUAL;

2. 建立新表時，指定虛擬欄位
drop table user_attribute_pattern;
CREATE TABLE `user_attribute_pattern` (
  `NAME` varchar(255) ,
  `VALUE` varchar(255),
  `USER_ID` varchar(36) ,
  `ID` varchar(36),
	`user_id_hash` int(11)  generated always  AS (CRC32(user_id) % 4) virtual,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `IDX_USER_ATTRIBUTE2`(`USER_ID`) USING BTREE,
  INDEX `IDX_NAME2`(`NAME`) USING BTREE,
  INDEX `IDX_VALUE2`(`VALUE`) USING BTREE
)

最後相互矛盾，沒有實現

  • 因為我的主鍵是varchar的，而hash分割槽表的鍵需要是int的
  • 所以我要把它新增一個虛擬欄位取主鍵的hash值
  • 然後經過這些步驟之後，又需要把這個虛擬key新增為主鍵，當mysql又提示了，虛擬欄位不能設為主鍵

最後可能需要把VIRTUAL這個虛擬的鍵變成STORE儲存的鍵才行了

最後直接用KEY的方式，主鍵是字串的方式

事實上，它是mysql內部對這個varchar進行了hash的計算，咱們上面的測試多次一舉了!

ALTER TABLE user_attribute_copy1
PARTITION BY KEY(ID) PARTITIONS 5;

根據你提供的程式碼，針對表 user_attribute_copy1 進行了分割槽設定。在這段程式碼中，使用的是 KEY(ID) 來定義分割槽規則，其中 ID 是主鍵。根據這個規則，表 user_attribute_copy1 將會以主鍵 ID 作為分割槽欄位進行分割槽，共分為5個分割槽。

在MySQL中，使用 KEY 關鍵字指定主鍵作為分割槽鍵，表示根據主鍵的值來進行分割槽。具體地說，MySQL會根據主鍵的雜湊值或者範圍值來將資料行分配到不同的分割槽中。在這種情況下，如果主鍵 ID 是 varchar 型別，MySQL會根據該欄位的值進行雜湊或範圍分割槽，將資料均勻地分佈到5個分割槽中。

總的來說，這個分割槽規則會根據表的主鍵 ID 的值來進行分割槽，將資料分散儲存到5個不同的分割槽中，有助於提高查詢效率和管理大量資料。如果需要進一步瞭解分割槽規則的具體實現和效果，可以檢視錶的分割槽資訊或執行相應的查詢來驗證資料是否按照預期分佈到各個分割槽中。

分割槽的操作

  1. 對已經有表分割槽
ALTER TABLE user_attribute_pattern
PARTITION BY hash(user_id_hash) PARTITIONS 4;
  1. 檢視分割槽資訊
SELECT table_name, partition_ordinal_position, partition_method, partition_expression
FROM information_schema.partitions
WHERE table_name = 'users';

相關文章