MySQL 进阶指南

MySQL 进阶指南

一、高级查询技术

1. 复杂JOIN操作

• 自连接(Self Join): 用于处理层级数据或比较同一表中的记录

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

• 交叉连接(CROSS JOIN): 生成笛卡尔积，常用于生成组合数据

SELECT p1.product_name, p2.product_name 
FROM products p1
CROSS JOIN products p2
WHERE p1.id < p2.id;

2. 窗口函数(MySQL 8.0+)

SELECT 
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

3. 公用表表达式(CTE)

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region, product, SUM(quantity) AS product_units
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

二、性能优化

1. 索引策略

• 复合索引设计原则: 遵循最左前缀原则
• 覆盖索引: 查询只需通过索引即可获取所需数据

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(customer_id, order_date, status);

-- 使用覆盖索引的查询
SELECT customer_id, order_date, status