MySQL 进阶 SQL 与高级功能

一、窗口函数

实现跨行计算的同时保留原始行数据，典型场景：排名、累计值、移动平均等。

核心结构：

SELECT 
    column,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM table

常用函数：

• RANK()：允许并列排名（如1,1,3）
• DENSE_RANK()：密集排名（如1,1,2）
• LEAD(col, n)/LAG(col, n)：访问前后第$n$行的值
• SUM(col) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)：4日移动累计

二、CTE（公用表表达式）

普通CTE：

WITH sales_summary AS (
    SELECT product_id, SUM(quantity) AS total 
    FROM orders 
    GROUP BY product_id
)
SELECT * FROM sales_summary WHERE total > 100

递归CTE（处理树形结构）：

WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 AS level 
    FROM employees WHERE parent_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.parent_id, ot.level + 1 
    FROM employees e 
    JOIN org_tree ot ON e.parent_id = ot.id
)
SELECT * FROM org_tree

三、JSON 数据处理

创建JSON字段：

CREATE TABLE products (
    id INT PRIMARY KEY,
    details JSON
)

查询与修改：

-- 提取属性（两种等效写法）
SELECT details->'$.manufacturer' AS maker FROM products
SELECT JSON_EXTRACT(details, '$.price') AS price FROM products

-- 修改JSON路径值
UPDATE products 
SET details = JSON_SET(details, '$.stock', 50) 
WHERE id = 101

四、索引优化策略

1. 覆盖索引：索引包含所有查询字段

   CREATE INDEX idx_cover ON orders (customer_id, order_date, total_amount)
   

   -- 可完全通过索引获取数据
   SELECT customer_id, order_date 
   FROM orders 
   WHERE total_amount > 1000
   

2. 索引下推（ICP）：在存储引擎层提前过滤数据

3. 最左前缀原则：对复合索引(a,b,c)有效：

   • ✅ WHERE a=1 AND b>5
   • ❌ WHERE b=5（无法使用索引）

五、存储过程与触发器

存储过程示例：

DELIMITER //
CREATE PROCEDURE GetOrderStats(IN cust_id INT, OUT total_orders INT)
BEGIN
    SELECT COUNT(*) INTO total_orders 
    FROM orders 
    WHERE customer_id = cust_id;
END //
DELIMITER ;

-- 调用
CALL GetOrderStats(123, @count);
SELECT @count;

触发器示例（审计日志）：

CREATE TRIGGER log_salary_change 
BEFORE UPDATE ON employees
FOR EACH ROW 
BEGIN
    INSERT INTO salary_audit 
    VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;

六、事务与锁机制

事务控制：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK

锁类型：

• 共享锁（S）：SELECT ... LOCK IN SHARE MODE
• 排他锁（X）：SELECT ... FOR UPDATE

七、分区表

范围分区示例：

CREATE TABLE sensor_data (
    id INT,
    log_time DATETIME,
    value FLOAT
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

八、性能优化技巧

1. 查询重构：

   -- 原查询
   SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)
   
   -- 优化为JOIN
   SELECT u.* 
   FROM users u
   EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
   

2. 批量写入：

   INSERT INTO logs (time, message) 
   VALUES 
   ('2023-01-01 08:00', 'start'),
   ('2023-01-01 08:05', 'process'),
   ('2023-01-01 08:10', 'end')
   

3. 执行计划分析：

   EXPLAIN 
   SELECT customer_id, AVG(total) 
   FROM orders 
   WHERE status = 'completed'
   GROUP BY customer_id
   

附录：常用系统变量

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- 缓冲池大小
SET GLOBAL long_query_time = 2;  -- 慢查询阈值（秒）