MySQL 技巧天花板！十個(gè)用法讓查詢效率翻倍
1. 使用JSON類型存儲(chǔ)靈活數(shù)據(jù)
場景：當(dāng)業(yè)務(wù)需求包含動(dòng)態(tài)擴(kuò)展字段（如用戶自定義配置）時(shí)，傳統(tǒng)字段擴(kuò)展會(huì)導(dǎo)致表結(jié)構(gòu)臃腫。MySQL的JSON類型支持動(dòng)態(tài)鍵值對存儲(chǔ)，完美解決此類問題。
-- 創(chuàng)建用戶偏好表（JSON字段存儲(chǔ)動(dòng)態(tài)配置）
CREATE TABLE user_preferences (
id INT PRIMARY KEY COMMENT '主鍵ID',
user_id INT COMMENT '用戶ID',
preferences JSON COMMENT '用戶偏好設(shè)置（JSON格式）'
) COMMENT '用戶偏好表';
-- 插入測試數(shù)據(jù)
INSERTINTO user_preferences VALUES
(1, 1, '{"theme": "dark", "notifications": true, "fontSize": 14}'),
(2, 2, '{"theme": "light", "notifications": false, "fontSize": 16}'),
(3, 3, '{"theme": "dark", "notifications": true, "fontSize": 12}');
-- 查詢指定用戶的主題配置（->符號提取JSON值）
SELECT preferences->'$.theme'AS theme FROM user_preferences WHERE user_id = 1;
-- 結(jié)果："dark"2. 使用WITH ROLLUP進(jìn)行分組統(tǒng)計(jì)
場景：生成報(bào)表時(shí)，不僅需要各分組數(shù)據(jù)，還需總計(jì)行。WITH ROLLUP可在一次查詢中同時(shí)返回分組結(jié)果和全局匯總。
-- 統(tǒng)計(jì)各部門員工數(shù)、薪資總和及總計(jì)
SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary
FROM employees
GROUPBY department WITHROLLUP;
-- 結(jié)果示例：
-- department | employee_count | total_salary
-- 技術(shù)部 3 50000.00
-- 市場部 2 25000.00
-- 人事部 2 21000.00
-- NULL 7 96000.00 （總計(jì)行）3. 使用CASE WHEN進(jìn)行條件統(tǒng)計(jì)
場景：按不同條件（如用戶狀態(tài)、訂單類型）統(tǒng)計(jì)數(shù)據(jù)時(shí)，CASE WHEN可在單條查詢中完成多條件聚合。
-- 統(tǒng)計(jì)活躍/非活躍用戶數(shù)量
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users
FROM users;
-- 結(jié)果示例：
-- active_users | inactive_users
-- 3 24. 使用INSERT IGNORE避免重復(fù)插入
場景：批量導(dǎo)入數(shù)據(jù)時(shí)，跳過已存在的記錄（基于唯一鍵），避免主鍵/唯一索引沖突報(bào)錯(cuò)。
-- 插入數(shù)據(jù)，重復(fù)的id=1記錄會(huì)被跳過，id=3正常插入
INSERT IGNORE INTO users (id, name, email) VALUES
(1, '張三', 'zhangsan@example.com'), -- 重復(fù)，跳過
(3, '王五', 'wangwu@example.com'); -- 新增，成功5. 使用ON DUPLICATE KEY UPDATE實(shí)現(xiàn)插入或更新
場景：需要根據(jù)唯一鍵（如用戶ID）實(shí)現(xiàn)“存在則更新，不存在則插入”的邏輯，替代傳統(tǒng)的先查詢再操作。
-- 插入或更新用戶信息（基于id唯一鍵）
INSERT INTO users (id, name, email) VALUES
(1, '張三', 'zhangsan_new@example.com'), -- 存在則更新
(4, '趙六', 'zhaoliu@example.com') -- 不存在則插入
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);6. 使用FIND_IN_SET進(jìn)行集合查詢
場景：當(dāng)字段存儲(chǔ)逗號分隔的集合（如多分類ID）時(shí)，快速查詢包含指定元素的記錄。
-- 查詢包含分類ID=1的商品
SELECT * FROM products WHERE FIND_IN_SET('1', category_ids);
-- 結(jié)果：商品A（1,2,3）、商品C（1,4）、商品E（1,5,6）7. 使用GROUP_CONCAT合并多行數(shù)據(jù)
場景：將分組后的多行數(shù)據(jù)合并為單個(gè)字符串（如部門員工列表、標(biāo)簽集合），簡化應(yīng)用層拼接邏輯。
-- 按部門合并員工姓名（以逗號分隔）
SELECT
department,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;
-- 結(jié)果示例：
-- 技術(shù)部 | 李四, 王五, 張三8. 使用EXISTS優(yōu)化子查詢
場景：替代低效的IN子查詢，判斷子查詢是否存在結(jié)果。尤其在大數(shù)據(jù)量時(shí)，EXISTS性能更優(yōu)。
-- 查詢包含高價(jià)商品（價(jià)格>100）的訂單
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM order_items oi
WHERE oi.order_id = o.id AND oi.price > 100
);9. 使用ROW_NUMBER()實(shí)現(xiàn)分頁
場景：MySQL 8.0+支持的窗口函數(shù)，通過行編號實(shí)現(xiàn)分頁，邏輯清晰且性能穩(wěn)定。
-- 查詢第1-10條最新文章（按創(chuàng)建時(shí)間倒序）
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
FROM articles
) t
WHERE row_num BETWEEN 1 AND 10;10. 使用WITH子句優(yōu)化復(fù)雜查詢
場景：將復(fù)雜查詢拆解為多個(gè)CTE（公共表表達(dá)式），提升可讀性和可維護(hù)性，類似“查詢中的變量定義”。
-- 統(tǒng)計(jì)用戶訂單數(shù)和總金額，再關(guān)聯(lián)用戶表
WITH user_stats AS (
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
)
SELECT u.name, us.order_count, us.total_amount
FROM users u
JOIN user_stats us ON u.id = us.user_id;結(jié)語
以上技巧均來自實(shí)際業(yè)務(wù)場景，兼具實(shí)用性和性能優(yōu)化價(jià)值。MySQL的強(qiáng)大不僅在于基礎(chǔ)功能，更體現(xiàn)在對復(fù)雜場景的靈活處理。建議在開發(fā)中多嘗試這些特性，同時(shí)注意：
- JSON類型適用于非高頻查詢的動(dòng)態(tài)數(shù)據(jù)，高頻查詢字段仍建議獨(dú)立建表；
- 窗口函數(shù)、CTE等功能需MySQL 8.0+支持，注意版本兼容性；
- 復(fù)雜查詢建議結(jié)合執(zhí)行計(jì)劃（
EXPLAIN）分析性能。
