mysql是最常用的數據庫之一，常見的函數用法大家應該都很熟悉，本文主要例舉一些相對出現頻率比較少的高級用法
(注：需注意mysql版本，大部分高級特性都是mysql8才有的)

多值索引與虛擬列

主要是解決字符串索引問題，光說概念會比較抽象 我們舉兩個例子來闡述

mysql文檔地址：

https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-multi-valued:~:text=%E7%9A%84%E8%AF%A6%E7%BB%86%E4%BF%A1%E6%81%AF%E3%80%82-,%E5%A4%9A%E5%80%BC%E7%B4%A2%E5%BC%95,-InnoDB%E6%94%AF%E6%8C%81%E5%A4%9A

場景一： 我們日常開發中 經常會使用,分隔 （例如userIds）, 但是隨著數據量和需求的增加，會造成效率問題；終極解決方案是拆表 建立一個新的關系表，但如果涉及改動大，拆表是個大工程；有一個技巧就是將數據升級成json格式，為json字段建立索引；如果代碼規范的話 我們只需要修改entity到DTO層的轉換，外部都是調用DTO，改動量小很多；

建立索引方式：

查詢方式：

-- 索引方式 ref
SELECT * FROM customers   WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
-- 或  （索引方式range）
SELECT * FROM customersWHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

json數據插入格式:

{"zipcode": [94536, 123]}

場景二： json字符串為普通的k-v 格式，但是我們需要通過對某個字段 例如姓名建立索引
可以建立虛擬列 對虛擬列建立索引
這樣可以簡化查詢代碼 (注意 如果是場景一 數組格式的數組要走索引 則不合適)

CREATE TABLE `file_test_phone` (`id` bigint NOT NULL,`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`real_phone` json DEFAULT NULL,`phone` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`real_phone`,_utf8mb4'$.phone'))) VIRTUAL,PRIMARY KEY (`id`),UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

json字段插入數據格式：

{"phone": "123"}

將字符串拼接數據拆分

還是上面場景，如果不打算用這種方式，想要用傳統的拆表來實現，拆表很容易，但是會涉及到歷史數據遷移問題。

我們假設舊表 user表 有id 和 role_id字段,其中role_id是逗號分隔,

現在希望將role_id拆分出去

我們可以先將逗號拼接的字符串先轉成數組字符串:

update user set role_id  = concat ('[',role_id,']')

接著用以下語句數據遷移：

INSERT INTO user_role (id,user_id, role_id)
SELECT UUID_SHORT(),u.id AS user_id,CAST(JSON_UNQUOTE(js.value) AS UNSIGNED) AS role_id
FROM user u
JOIN JSON_TABLE(u.role_id,'$[*]' COLUMNS (value VARCHAR(255) PATH '$')) AS js  
WHERE u.role_id IS NOT NULL  AND JSON_VALID(u.role_id) AND JSON_LENGTH(u.role_id) > 0;

JSON_TABLE 是作用于行數據的，所以我們看不到顯式的join關聯條件,執行后user_role數據示例：

分區

(僅討論分區語法 博主個人感覺分區有點雞肋 mysql的這個設計對數據來說或許合理 但對用戶使用來說 并不友好;
當然這也是見仁見智 感興趣可以自行造億級以上數據測試 這是只是提供一種思路)

常用分區策略：

range分區： 比如按照年份分區
list分區：按照枚舉值分區 比如根據省份
hash分區：按哈希值分區，適用于數據比較均勻的場景
key分區：類似HASH分區，但使用MySQL的內部哈希函數

mysql5.1之后就可以分區了 語法為

-- 移除分區
-- ALTER TABLE test_part REMOVE PARTITIONING;
-- 修改表分區 （如果是創建 則在建表語句后面跟上PARTITION ）
ALTER TABLE test_part
PARTITION BY RANGE (code) (PARTITION p1 VALUES LESS THAN (100000000),PARTITION p2 VALUES LESS THAN (200000000),PARTITION p3 VALUES LESS THAN (300000000),PARTITION p4 VALUES LESS THAN MAXVALUE
);

建表分區示例

-- 根據年份分區
CREATE TABLE orders (order_id INT NOT NULL,customer_id INT NOT NULL,order_date DATE NOT NULL,total DECIMAL(10, 2),PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION pmax VALUES LESS THAN MAXVALUE
);

分區可以避免跨表分頁的問題，雖然數據物理隔離了 但是終歸是在同一張表；但是必須注意的一點：分區字段必須是主鍵字段之一;

因為一旦有主鍵，它就成為表的核心約束，MySQL 必須保證 主鍵在全表范圍內唯一，但如果主鍵不包含分區字段，那主鍵值一樣的數據有可能落入不同分區這樣就出現了主鍵沖突，MySQL 沒法檢測這個沖突 —— 所以為了防止這種“隱形沖突”，它強制要求主鍵必須包含分區字段，否則干脆不讓你建表。
這個設計不得不吐槽了，例如oracle就可以做到分區后也全局檢測，所以不用限制主鍵分區

如下圖 分區后反而降低了效率，主鍵本來就是聚促索引 弄成聯合主鍵效率反而可能降低，在博主親測的幾千萬級別數據 是完全沒有必要分區（也沒必要分表） , 不分區 建索引反而會快些

但是當表不存在主鍵的時候，最核心的性約束就不是主鍵了，而是唯一索引,這個時候 分區鍵是唯一索引字段就能分區成功了:

CREATE TABLE t1 (col1 INT NOT NULL,col2 DATE NOT NULL,col3 INT NOT NULL,col4 INT NOT NULL,UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
-- 能執行成功
ALTER TABLE user_role_no_id PARTITION BY HASH(user_id) PARTITIONS 4;

那話又說回來，怎么會有大數據量的業務表不存在主鍵呢？
不存在主鍵的表（如配置表、字典表）又怎么會到分區的程度呢
這本身似乎是個悖論，所以我們平常見到的mysql分區應該也比較少;
[重申：本文僅介紹mysql有分區用法，實際使用可能需要斟酌再三]