MySQL高级用法学习笔记

<窗口函数> ( <参数> ) OVER (
    [ PARTITION BY <分区表达式> ]
    [ ORDER BY <排序表达式> [ ASC | DESC ] ]
    [ frame_clause ]
)

-- ROW_NUMBER
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- RANK
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- DENSE_RANK
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)
LEAD(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

-- 优化前
SELECT * FROM (
    SELECT o.o_custkey, o.o_totalprice,
        RANK() OVER (PARTITION BY o.o_custkey ORDER BY o.o_totalprice) AS rn
    FROM orders AS o
    WHERE o.o_orderdate = '1996-06-20'
) AS A WHERE A.rn = 1;

-- 优化后
CREATE INDEX idx_order_date ON orders(o_orderdate, o_custkey, o_totalprice);

SELECT * FROM (
    SELECT o.o_custkey, o.o_totalprice,
        RANK() OVER (PARTITION BY o.o_custkey ORDER BY o.o_totalprice) AS rn
    FROM orders AS o
    WHERE o.o_orderdate = '1996-06-20'
) AS A WHERE A.rn = 1;

EXPLAIN
SELECT d.name Department, a.name Employee, a.salary Salary
FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS "rank"
      FROM employees) a
LEFT JOIN Department d ON a.departmentId = d.id
WHERE a.rank <= 3;

SELECT month, amount,
    SUM(amount) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_sum
FROM sales;

-- 创建覆盖索引
CREATE INDEX idx Department_salary ON employees(departmentId, salary, name);

-- 查询使用覆盖索引
SELECT departmentId, salary, name,
    RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM employees;

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.id = b.parent_id;

WITH RECURSIVE SubDepartments AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE parent_id = 1  -- 假设1是顶级部门的ID
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    JOIN SubDepartments sd ON d.parent_id = sd.id
)
SELECT * FROM SubDepartments;

DELIMITER //
CREATE FUNCTION ` gettingSubordinates `(root_id BIGINT) RETURNS VARCHAR(1000)
BEGIN
    DECLARE k INT DEFAULT 0;
    DECLARE fid INT DEFAULT 1;
    DECLARE str VARCHAR(1000) DEFAULT '$';
    DECLARE cid VARCHAR(1000);
    SET cid = CAST(root_id AS CHAR);
    IF cid IS NOT NULL THEN
        SET str = CONCAT(str, ',', cid);
        SET k = k + 1;
    END IF;
    label:WHILE cid IS NOT NULL DO
        SELECT GROUP_CONCAT(id) INTO cid
        FROM employees
        WHERE parent_id IN (SELECT id FROM JSON_TABLE(str, '$[*]' COLUMNS (id INT PATH '$')) AS t);
        IF cid IS NOT NULL THEN
            SET str = CONCAT(str, ',', cid);
            SET k = k + 1;
        END IF;
    END label;
    RETURN str;
END //
DELIMITER ;

-- EXISTS查询（性能更优）
SELECT * FROM t_customer tcustomer
WHERE EXISTS(
    SELECT 1 FROM t_order torder
    WHERE tcustomer.customer_no = torder.customer_no
);

-- IN查询（性能较差）
SELECT * FROM t_customer tcustomer
WHERE tcustomer.customer_no IN (
    SELECT customer_no FROM t_order
);

-- 子查询版本
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id FROM categories
    WHERE type = 'Electronics'
);

-- JOIN版本（性能更优）
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE c.type = 'Electronics';

-- 慢速分页查询
SELECT * FROM employees
ORDER BY id LIMIT 100000, 10;

-- 优化后的分页查询
SELECT * FROM employees
WHERE id > 100000
LIMIT 10;

-- 使用UNION去重
SELECT salesman_id FROM salesman
UNION
SELECT salesman_id FROM orders
ORDER BY salesman_id;

-- 使用UNION ALL保留所有行
SELECT salesman_id FROM salesman
UNION ALL
SELECT salesman_id FROM orders
ORDER BY salesman_id;

-- 提取JSON值
SELECT JSON_EXTRACT('{"name": "张三", "age": 30}', '$.name') AS name;  -- 返回 "张三"

-- 提取数组元素
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]') AS element;  -- 返回 "b"

-- 检查JSON是否包含特定值
SELECT JSON_CONTAINS('{"store": {"book": {"title": "MySQL指南"}}}', '"MySQL指南"');  -- 返回 1

-- 获取JSON长度
SELECT JSON_LENGTH('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*]') AS length;  -- 返回 2

-- 查询字符串类型的数字键
SELECT JSON_EXTRACT(price, "$.sku \"45453\".algorithm") AS algorithm
FROM price
WHERE id = 159540;

-- 创建虚拟列
ALTER TABLE employees ADD COLUMN json_name VARCHAR(100) GENERATED ALWAYS AS (json_column->>"$.name") VIRTUAL;

-- 创建索引
CREATE INDEX idx_json_name ON employees(json_name);

-- 查询使用索引
EXPLAIN
SELECT * FROM employees
WHERE json_name = 'John';

-- 创建多值索引
ALTER TABLE table_name ADD INDEX idx attrs_arr_key(
    (CAST(`business_attr`->"$[*].attrArrKey" AS CHAR(32) ARRAY))
);

-- 查询使用多值索引
SELECT * FROM table_name
WHERE JSON-overlaps(`business_attr`->"$[*].attrArrKey", CAST('"1000"' AS JSON));

-- 基本使用
SELECT o.id, jt.product, jt.price
FROM orders o
JOIN JSON_TABLE(
    o.order_data,
    '$.items[*]' COLUMNS (
        product VARCHAR(50) PATH '$.product',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt ON TRUE;

-- 嵌套JSON处理
SELECT p.id, jt1.name AS customer_name, jt2.product AS item_product
FROM people p
JOIN JSON_TABLE(
    p.info,
    '$' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt1 ON TRUE
JOIN JSON_TABLE(
    p.info,
    '$.items[*]' COLUMNS (
        product VARCHAR(50) PATH '$.product'
    )
) AS jt2 ON TRUE;

-- 统计订单总金额
SELECT o.id, SUM(jt.price) AS total_price
FROM orders o
JOIN JSON_TABLE(
    o.order_data,
    '$.items[*]' COLUMNS (
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt ON TRUE
GROUP BY o.id;

-- 修改JSON字段下的指定键值
UPDATE price
SET price = JSON_SET(price, "$.attr \"1280\".price_old", 300)
WHERE id IN (171314);

START TRANSACTION;

-- 更新JSON字段
UPDATE employees
SET attributes = JSON_SET(attributes, "$.status", "active")
WHERE id = 1001;

-- 插入相关记录
INSERT INTO employee_log (employee_id, action, action_time)
VALUES (1001, 'status updated to active', NOW());

COMMIT;

-- 创建复合索引
CREATE INDEX idx_name_age_city ON user(name, age, city);

-- 有效使用
SELECT * FROM user WHERE name = '张三';  -- 使用索引(name)
SELECT * FROM user WHERE name = '张三' AND age = 25;  -- 使用索引(name, age)
SELECT * FROM user WHERE name = '张三' AND age = 25 AND city = '北京';  -- 使用完整索引

-- 无效使用（无法命中索引）
SELECT * FROM user WHERE age = 25;  -- 跳过了索引的最左列name
SELECT * FROM user WHERE name = '张三' AND city = '北京';  -- 中间跳过了age列

-- 创建降序索引
CREATE INDEX idx created_desc ON log_entries (created_at DESC);

-- 创建复合降序索引
CREATE INDEX idx_date_status ON orders (create_date DESC, status);

-- 清理索引碎片
OPTIMIZE TABLE employees;

-- 使用pt-online-schema-change在线重建表
pt-online-schema-change --execute D=your_database,t=your_table

-- 全局设置
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 会话级设置
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 单事务设置
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

-- 执行一些操作
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO orders (user_id, amount) VALUES (1, 100);

-- 设置保存点
SAVEPOINT my_savepoint;

-- 执行更多操作
INSERT INTO payments (order_id, amount) VALUES (1, 100);

-- 假设这里发生错误，回滚到保存点
ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

-- 仅锁定必要的行
SELECT id, name FROM employees
WHERE department_id = 10
ORDER BY salary DESC
LIMIT 10 FOR UPDATE OF employees;

-- 锁定整个表（慎用）
LOCK TABLES employees WRITE;

-- 查看死锁信息
SHOW ENGINE INNODB STATUS;

-- 使用版本号实现乐观锁
UPDATE employees
SET salary = salary * 1.1, version = version + 1
WHERE id = 1001 AND version = 1;

-- 查询每个产品类别下JSON数组中的价格趋势
SELECT category_id, product,
    LAG(JSON_EXTRACT(product_data, '$.price'), 1, 0) OVER (
        PARTITION BY category_id ORDER BY update_time
    ) AS prev_price,
    JSON_EXTRACT(product_data, '$.price') AS current_price,
    (JSON_EXTRACT(product_data, '$.price') - LAG(JSON_EXTRACT(product_data, '$.price'), 1, 0) OVER (
        PARTITION BY category_id ORDER BY update_time
    )) / LAG(JSON_EXTRACT(product_data, '$.price'), 1, 0) OVER (
        PARTITION BY category_id ORDER BY update_time
    ) * 100 AS price_change百分比
FROM products
WHERE JSON_EXTRACT(product_data, '$.price') IS NOT NULL;

START TRANSACTION;

-- 查询并锁定符合条件的订单
SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'pending'
ORDER BY created_time DESC
LIMIT 10 FOR UPDATE SKIP LOCKED;

-- 更新订单状态
UPDATE orders
SET status = 'processing'
WHERE order_id IN (1001, 1002, 1003);

-- 创建保存点
SAVEPOINT order Processing;

-- 插入处理记录
INSERT INTO order log (order_id, action, action_time)
VALUES (1001, 'processing started', NOW());

-- 可能的错误处理
-- ROLLBACK TO SAVEPOINT order Processing;

COMMIT;

-- 查询每个部门员工的工资排名及与上级的工资差异
WITH RECURSIVE Department Hierarchy AS (
    SELECT id, name, manager_id, salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, e.salary,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
    FROM employees e
    JOIN Department Hierarchy dh ON e经理_id = dh.id
)
SELECT dh.id, dh.name, dh salary_rank,
    dh salary - LAG(dh salary) OVER (
        PARTITION BY dh.department_id ORDER BY dh.salary DESC
    ) AS diff_with_previous
FROM Department Hierarchy dh
ORDER BY dh.department_id, dh salary_rank;