MySQL Select语句深度解析：从基础语法到高级优化

SELECT
    [DISTINCT | ALL] column1 [, column2, ...]
FROM table1 [, table2, ...]
    [JOIN table2 ON join_condition]
    [WHERE where_condition]
    [GROUP BY group_by_expression]
    [HAVING having_condition]
    [ORDER BY order_by_expression [ASC | DESC]]
    [LIMIT [offset,] row_count];

-- 简单的单表查询
SELECT * FROM employees;

-- 多表查询
SELECT e.name, d.department_name 
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- 使用多个条件
SELECT * FROM employees 
WHERE salary > 50000 
  AND department = 'Sales'
  AND hire_date > '2020-01-01';

-- 按部门分组并统计平均薪资，只显示平均薪资大于50000的部门
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

-- 按薪资降序排序，返回第6-15条记录（第2页，每页10条）
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10, 10;

-- 查询薪资高于平均薪资的员工
SELECT name, salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 使用IN的子查询
SELECT name, department
FROM employees
WHERE department IN (SELECT department_name FROM departments WHERE location = 'NY');

-- 使用FROM子查询获取每个部门的平均薪资，然后查询高于整体平均的部门
SELECT dept_avg.department, dept_avg.avg_salary
FROM (SELECT department, AVG(salary) as avg_salary 
      FROM employees 
      GROUP BY department) AS dept_avg
WHERE dept_avg.avg_salary > (SELECT AVG(salary) FROM employees);

-- 查询有订单的员工
SELECT e.name, e.employee_id
FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id);

SELECT name FROM employees 
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

SELECT name, salary, department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.department_id = e1.department_id);

-- 子查询方式
SELECT name FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

-- 重写为连接查询
SELECT e.name 
FROM employees e 
JOIN departments d ON e.department_id = d.id
WHERE d.location = 'NY';

-- 使用IN
SELECT name FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE active = 1);

-- 使用EXISTS（通常更高效）
SELECT e.name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d 
              WHERE d.department_id = e.department_id AND d.active = 1);

-- 低效写法
SELECT employee_id, name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 
                WHERE e2.department_id = e1.department_id);

-- 改进写法（使用窗口函数）
SELECT employee_id, name, salary
FROM (SELECT employee_id, name, salary, 
             AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
      FROM employees) t
WHERE salary > dept_avg_salary;

-- 创建各类索引
CREATE INDEX idx_lastname ON employees(last_name);                    -- 普通索引
CREATE UNIQUE INDEX idx_email ON employees(email);                   -- 唯一索引
ALTER TABLE employees ADD PRIMARY KEY (employee_id);                 -- 主键索引
CREATE INDEX idx_dept_title ON employees(department, job_title);     -- 复合索引

-- 计算某列的选择性
SELECT COUNT(DISTINCT department) / COUNT(*) as selectivity 
FROM employees;

-- 好的复合索引设计
CREATE INDEX idx_dept_hire_date ON employees(department, hire_date);

-- 以下查询可以利用该索引
SELECT * FROM employees WHERE department = 'Sales';
SELECT * FROM employees WHERE department = 'Sales' AND hire_date > '2020-01-01';
SELECT employee_id, department FROM employees WHERE department = 'Sales'; -- 覆盖索引

-- 假设employee_id是字符串类型，以下查询会导致索引失效
SELECT * FROM employees WHERE employee_id = 123; -- 应使用字符串'123'

-- 索引失效的写法
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
SELECT * FROM employees WHERE salary * 2 > 100000;

-- 优化后的写法
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';
SELECT * FROM employees WHERE salary > 50000;

-- 如果name有索引但department没有索引，此查询可能导致全表扫描
SELECT * FROM employees WHERE name = 'John' OR department = 'Sales';

-- 优化方案：使用UNION或分别查询
SELECT * FROM employees WHERE name = 'John'
UNION
SELECT * FROM employees WHERE department = 'Sales';

-- 假设有索引idx_dept_title (department, job_title)
-- 以下查询可以使用覆盖索引，无需访问数据行
SELECT department, job_title 
FROM employees 
WHERE department = 'Sales';

-- 假设有复合索引(department, salary)
SELECT * FROM employees 
WHERE department = 'Sales' AND salary > 50000;

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 在配置文件中启用慢查询日志
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 2  # 执行时间超过2秒的查询被记录

EXPLAIN SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000
ORDER BY e.hire_date DESC
LIMIT 10;

-- 查看哪些查询消耗最多资源
SELECT sql_text, rows_examined, rows_sent, 
       created_tmp_tables, created_tmp_disk_tables
FROM performance_schema.events_statements_history
ORDER BY rows_examined DESC
LIMIT 10;

-- 不推荐的写法
SELECT * FROM employees WHERE department = 'Sales';

-- 推荐的写法
SELECT employee_id, name, email, phone 
FROM employees 
WHERE department = 'Sales';

-- 低效的分页写法（OFFSET越大越慢）
SELECT * FROM employees ORDER BY name LIMIT 10000, 20;

-- 优化方案1：使用游标分页（基于上次查询的最后位置）
SELECT * FROM employees 
WHERE name > 'last_name_value' 
ORDER BY name 
LIMIT 20;

-- 优化方案2：使用覆盖索引+延迟连接
SELECT e.* 
FROM employees e
JOIN (SELECT employee_id FROM employees 
      ORDER BY name LIMIT 10000, 20) AS tmp
ON e.employee_id = tmp.employee_id;

-- 低效的JOIN查询（没有适当的索引）
SELECT e.name, d.department_name, p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON e.project_id = p.id
WHERE e.salary > 50000;

-- 优化：确保department_id、project_id和salary上有索引

-- 文件排序的情况
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
ORDER BY hire_date;

-- 优化：创建复合索引(department, hire_date)
CREATE INDEX idx_dept_hire ON employees(department, hire_date);

-- 反规范化示例：在orders表中存储customer_name，避免连表查询
SELECT order_id, order_date, customer_name 
FROM orders 
WHERE order_date > '2020-01-01';

-- 而不是每次连接customers表
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2020-01-01';

-- 为每个部门的员工按薪资排名
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

-- 计算移动平均、累计求和等
SELECT order_date, daily_revenue,
       AVG(daily_revenue) OVER (ORDER BY order_date ROWS 6 PRECEDING) as 7day_avg,
       SUM(daily_revenue) OVER (ORDER BY order_date) as cumulative_sum
FROM daily_orders;

-- 使用CTE组织复杂查询
WITH 
dept_stats AS (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
),
high_earners AS (
    SELECT e.name, e.department, e.salary
    FROM employees e
    JOIN dept_stats d ON e.department = d.department
    WHERE e.salary > d.avg_salary * 1.5
)
SELECT department, COUNT(*) as high_earner_count
FROM high_earners
GROUP BY department
ORDER BY high_earner_count DESC;

-- 创建表时定义JSON字段和生成列
CREATE TABLE users (
    id INT PRIMARY KEY,
    profile JSON,
    age INT AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age'))) STORED,
    INDEX idx_age (age)
);

-- 或者为已存在的表添加生成列和索引
ALTER TABLE users 
ADD COLUMN age INT AS (profile ->> '$.age') STORED;
CREATE INDEX idx_age ON users(age);

ALTER TABLE users 
ADD COLUMN age INT AS (profile ->> '$.age') STORED,
ADD COLUMN city VARCHAR(50) AS (profile ->> '$.city') STORED;
CREATE INDEX idx_age_city ON users(age, city);

-- 使用锁选项控制并发
SELECT * FROM orders WHERE order_id = 1001 FOR UPDATE;    -- 排他锁
SELECT * FROM orders WHERE order_id = 1001 LOCK IN SHARE MODE; -- 共享锁