MySQL 常用函數性能優化指南（含索引影響分析）

以下是 MySQL 函數使用指南，新增性能影響評級、索引失效分析和優化方案，幫助您高效使用函數：

---

📜 一、字符串處理函數（含性能分析）

函數	示例	性能影響	索引影響	優化建議
CONCAT()	SELECT CONCAT(first_name, last_name) FROM users;	??	? 導致全掃描	存儲計算列：ALTER TABLE users ADD full_name VARCHAR(100) AS (CONCAT(first_name, last_name)) STORED;
SUBSTRING()	SELECT * FROM logs WHERE SUBSTRING(url, 1, 5) = 'https';	????	? 索引失效	改用前綴索引：ALTER TABLE logs ADD INDEX (url(5)); 后 WHERE url LIKE 'https%'
UPPER()/LOWER()	SELECT * FROM users WHERE LOWER(username) = 'admin';	???	? 索引失效	存儲時統一大小寫：INSERT INTO users (username) VALUES (LOWER('Admin'))
GROUP_CONCAT()	SELECT dept_id, GROUP_CONCAT(name) FROM emp GROUP BY dept_id;	???	無影響	設置長度限制：GROUP_CONCAT(name SEPARATOR ',' ORDER BY id DESC LIMIT 100)
JSON_EXTRACT()	SELECT JSON_EXTRACT(data, '$.price') FROM products;	??	? 索引失效	MySQL 8.0+使用生成列：ALTER TABLE products ADD price DECIMAL(10,2) AS (JSON_EXTRACT(data, '$.price')) STORED;

---

🔢 二、數值計算函數（含性能分析）

函數	示例	性能影響	索引影響	優化建議
ROUND()	SELECT ROUND(price*0.9, 2) FROM products;	?	? 小表無影響	大表避免實時計算，預計算存儲
RAND()	SELECT * FROM products ORDER BY RAND() LIMIT 5;	?????	? 全表掃描	改用ID范圍隨機：SELECT * FROM products WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 5;
MOD()	SELECT * FROM orders WHERE MOD(id, 10) = 0;	???	? 索引失效	添加分區列：ALTER TABLE orders ADD part TINYINT AS (id%10) STORED, INDEX(part);
GREATEST()	UPDATE sales SET bonus = GREATEST(sales*0.1, 1000);	??	寫操作無影響	批量更新分片執行

---

📅 三、日期時間函數（含性能分析）

函數	示例	性能影響	索引影響	優化建議
DATE_FORMAT()	SELECT * FROM orders WHERE DATE_FORMAT(create_time,'%Y-%m') = '2025-08';	????	? 索引失效	改用范圍查詢：WHERE create_time BETWEEN '2025-08-01' AND '2025-08-31'
YEAR()/MONTH()	SELECT * FROM logs WHERE YEAR(create_time) = 2025;	???	? 索引失效	存儲計算列：ADD INDEX (create_year)
DATE_ADD()	SELECT * FROM events WHERE event_time > DATE_ADD(NOW(), INTERVAL -1 HOUR);	?	? 索引有效	保持函數在比較符右側：WHERE event_time > (NOW() - INTERVAL 1 HOUR)
UNIX_TIMESTAMP()	SELECT * FROM sessions WHERE UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) > 3600;	??	? 索引失效	存儲持續時間：ADD COLUMN duration INT AS (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) STORED

---

🧠 四、條件判斷函數（含性能分析）

函數	示例	性能影響	索引影響	優化建議
IF()	SELECT id, IF(status=1, '啟用', '禁用') AS status_text FROM devices;	?	? 無影響	可安全使用
CASE	SELECT CASE WHEN score>90 THEN 'A' ... END FROM exams;	?	? 無影響	復雜邏輯建議應用層處理
COALESCE()	SELECT COALESCE(email, phone) AS contact FROM users;	??	? 無影響	避免在WHERE中使用：WHERE COALESCE(email,'') != '' → WHERE email IS NOT NULL
IFNULL()	SELECT IFNULL(discount, 0) FROM orders;	?	? 無影響	可安全使用

---

📊 五、聚合函數（含性能分析）

函數	示例	性能影響	索引影響	優化建議
COUNT()	SELECT COUNT(DISTINCT user_id) FROM big_table;	????	? 全表掃描	用近似計數：SELECT COUNT(*) FROM (SELECT user_id FROM big_table GROUP BY user_id) tmp;
AVG()	SELECT AVG(salary) FROM employees WHERE dept=3;	??	? 索引有效	確保dept有索引，大表分片統計
GROUP_CONCAT()	SELECT dept, GROUP_CONCAT(name) FROM emp GROUP BY dept;	???	? 內存消耗	設置group_concat_max_len限制長度
SUM()	SELECT SUM(amount) FROM sales WHERE date>'2025-01-01';	??	? 索引有效	添加覆蓋索引：INDEX(date, amount)

---

🔍 六、高級函數（含性能分析）

函數	示例	性能影響	索引影響	優化建議
ROW_NUMBER()	SELECT id, ROW_NUMBER() OVER(ORDER BY score DESC) FROM students;	???	? 無索引	MySQL 8.0+使用，避免大表全排序
CAST()	SELECT * FROM products WHERE CAST(price AS UNSIGNED) > 1000;	????	? 索引失效	存儲時使用正確類型，避免轉換
UUID()	INSERT INTO orders(id, ...) VALUES(UUID(), ...);	??	? 索引碎片	改用有序UUID：UUID_TO_BIN(UUID(), 1)
FULLTEXT()	SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');	??	? 專用索引	使用全文索引并優化配置

---

💎 函數使用黃金法則

1. 索引失效高危場景

-- 錯誤：函數包裹索引字段
SELECT * FROM users WHERE DATE_FORMAT(create_time,'%Y%m') = '202508';-- 正確：保持索引列純凈
SELECT * FROM users WHERE create_time BETWEEN '2025-08-01' AND '2025-08-31';

2. 預計算策略

-- 實時計算（避免）
SELECT *, price*0.9 AS discount_price FROM products;-- 預存儲方案（推薦）
ALTER TABLE products ADD discount_price DECIMAL(10,2) AS (ROUND(price*0.9,2)) STORED;
CREATE INDEX idx_discount ON products(discount_price);

3. 函數執行成本分級

級別	特征	代表函數
?	輕量級	IF(), COALESCE()
??	中等	DATE_ADD(), CONCAT()
???	較重	RAND(), GROUP_CONCAT()
????	高危	SUBSTRING(字段), CAST(字段)
?????	災難	ORDER BY RAND()

4. 優化檢測工具

-- 檢查索引使用
EXPLAIN SELECT * FROM users WHERE LOWER(username)='admin';-- 性能分析
SET profiling = 1;
SELECT ...;
SHOW PROFILE FOR QUERY 1;

📌 終極建議：

• WHERE條件中的列禁止使用函數
  
• ORDER BY/GROUP BY 避免復雜計算
  
• 大表查詢使用預計算列+索引
  
• 高頻計算邏輯移入應用層或存儲過程

通過遵循這些規則，您能在享受函數便利性的同時，保持數據庫的高性能運行！