MySQL 技巧天花板！十個(gè)用法讓查詢效率翻倍

作者：一安 2025-11-12 07:43:00

當(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ǔ)，完美解決此類問題。

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            2

4. 使用`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;