B.40.5.2-SQL优化与高级查询

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

SQL优化与高级查询

为什么需要学习？

SQL性能问题频繁出现，影响系统整体效能！

你是否正面临：

• SQL查询响应迟缓，影响用户体验和系统效能？
• 复杂业务逻辑导致SQL语句冗长，难以维护？
• 面对海量数据查询，缺乏有效的优化策略？

本指南直击这些难点，提供从SQL优化基础、索引设计、执行计划分析到系统级优化的完整技术体系！

学习目标与价值

通过系统学习，掌握SQL性能优化的核心方法和高级查询技巧！

你将系统掌握以下核心基础技术：

• SQL优化基础：掌握SQL执行原理和核心优化原则
• 索引优化深度解析：精通索引设计、失效场景和实战技巧
• 高级查询模式：掌握窗口函数、递归查询等复杂技术
• 执行计划分析：深入理解EXPLAIN工具和查询优化
• 慢查询诊断与优化：掌握性能诊断和优化实战方法
• 系统级优化策略：架构设计、硬件配置、系统运维等全方位优化

一、SQL优化基础与执行原理

1.1 SQL执行原理深度解析

SQL查询执行流程

MySQL SQL执行完整流程：

客户端请求 → 连接器 → 查询缓存(MySQL 8.0已移除) → 分析器 → 优化器 → 执行器 → 存储引擎 → 返回结果

各阶段详细功能：

1. 连接器（Connector）

   功能：管理客户端连接，验证用户身份

   关键参数：
   

   max_connections

   （最大连接数）、
   

   wait_timeout

   （连接超时）

   优化要点：合理设置连接池，避免连接泄露

2. 分析器（Parser）

   词法分析：识别SQL关键字、表名、列名等

   语法分析：检查SQL语法正确性

   错误处理：语法错误在此阶段抛出

3. 优化器（Optimizer）

   查询重写：优化SQL逻辑结构

   执行计划生成：选择最优执行路径

   成本估算：基于统计信息估算执行成本

4. 执行器（Executor）

   调用存储引擎：执行实际的数据操作

   结果返回：处理查询结果并返回客户端

1.2 SQL优化核心原则

SQL优化基本原则

1. 减少数据访问量
   -- 不推荐：全表扫描
   SELECT * FROM users WHERE age > 18;
   -- 推荐：使用索引，减少扫描量
   SELECT id, name FROM users WHERE age > 18 AND status = 'active';
2. 避免不必要的数据传输
   -- 不推荐：返回所有列
   SELECT * FROM orders WHERE order_date >= '2023-01-01';
   -- 推荐：只返回需要的列
   SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_date >= '2023-01-01';
3. 利用索引优化查询
   -- 创建合适的索引
   CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);
   -- 使用索引覆盖查询
   SELECT order_id, customer_id
FROM orders
WHERE order_date >= '2023-01-01' AND customer_id IN (1001, 1002);
4. 避免SELECT所有
   -- 不推荐
   SELECT * FROM users WHERE status = 1;
   -- 推荐：只查询需要的字段
   SELECT id, name, email FROM users WHERE status = 1;
5. 合理使用LIMIT
   -- 不推荐：大偏移量分页
   SELECT * FROM orders ORDER BY id LIMIT 10000, 20;
   -- 推荐：使用游标分页
   SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;
6. 优化子查询
   -- 不推荐：相关子查询
   SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);
   -- 推荐：使用JOIN
   SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;
7. 避免在WHERE子句中使用函数
   -- 不推荐：索引失效
   SELECT * FROM users WHERE YEAR(create_time) = 2023;
   -- 推荐：使用范围查询
   SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
8. 合理使用JOIN替代子查询
   -- 不推荐：IN子查询
   SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders WHERE amount > 1000
);

SELECT * FROM products WHERE category_id IN (
SELECT id FROM categories WHERE status = 'active'
);
-- 建议：采用JOIN
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.status = 'active';
二、索引优化深入分析
2.1 索引基础与选择性提升
索引选择性的计算方法
索引选择性计算：
-- 列选择性的评估
SELECT
COUNT(DISTINCT column_name) / COUNT(*) as selectivity
FROM table_name;
选择性评判准则：
高选择性
：> 0.1，适宜建立索引
中等选择性
：0.01-0.1，依据查询频次决定
低选择性
：< 0.01，通常不建议建立索引
2.2 组合索引优化策略
组合索引的设计准则
1. 最左侧前缀原则
-- 索引：idx_name_age_city (name, age, city)
-- 能够有效利用索引的查询：
SELECT * FROM users WHERE name = 'John';                    -- ? 可以使用索引
SELECT * FROM users WHERE name = 'John' AND age = 25;        -- ? 可以使用索引
SELECT * FROM users WHERE name = 'John' AND city = 'Beijing'; -- ? 可以使用索引
-- 不能使用索引的查询：
SELECT * FROM users WHERE age = 25;                          -- ? 无法使用索引
SELECT * FROM users WHERE city = 'Beijing';                  -- ? 无法使用索引
2. 索引字段排序优化
-- 查询模式：WHERE status = ? AND create_time > ?
-- 索引设计：idx_status_time (status, create_time)
-- 查询模式：WHERE user_id = ? AND order_date BETWEEN ? AND ?
-- 索引设计：idx_user_date (user_id, order_date)
3. 索引字段选择策略
优先考虑高选择性字段
：区分度较高的字段置于前列
等值查询优先，范围查询随后
频繁查询的字段前置
2.3 索引失效情境及其解决方案
索引失效的情境分类
1. 计算与函数操作引起失效
-- 索引失效示例：
SELECT * FROM users WHERE YEAR(create_time) = 2023;          -- ?
SELECT * FROM products WHERE price * 1.1 > 100;              -- ?
-- 改进方案：
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; -- ?
SELECT * FROM products WHERE price > 100 / 1.1;              -- ?
2. 操作符使用不当造成失效
IN操作符的索引使用情况：
IN操作符常引发索引失效
，MySQL优化器可能会选择全表扫描
大量值的IN查询
：几乎必然导致索引失效
子查询IN
：无法利用索引，应转换为JOIN
-- 可能导致索引失效的查询：
SELECT * FROM users WHERE status != 'active';                -- ?
SELECT * FROM users WHERE user_id NOT IN (1, 2, 3);         -- ?
SELECT * FROM users WHERE status IN ('inactive', 'pending'); -- ? IN操作符通常会导致索引失效
SELECT * FROM users WHERE status IN (SELECT status FROM inactive_statuses); -- ? 子查询IN
SELECT * FROM users WHERE status IN (大量值列表);            -- ? 大量值必然导致索引失效
-- 对于少量值的IN查询，使用OR操作符同样可能引起索引失效
    

SELECT * FROM users WHERE status = 'inactive' OR status = 'pending'; -- ? OR操作符通常引起索引失效

-- 推荐优化方案：

-- 1. 使用UNION ALL代替OR（适用于少量条件）

SELECT * FROM users WHERE status = 'inactive'

UNION ALL

SELECT * FROM users WHERE status = 'pending'; -- ?

-- 2. 使用JOIN代替IN查询

SELECT u.* FROM users u

JOIN (SELECT 'inactive' as status UNION ALL SELECT 'pending') s ON u.status = s.status; -- ?

-- 3. 对于子查询IN，改为JOIN

SELECT u.* FROM users u

JOIN inactive_statuses s ON u.status = s.status; -- ?

-- 4. 使用NOT EXISTS代替NOT IN

SELECT * FROM users u WHERE NOT EXISTS (

SELECT 1 FROM excluded_users eu WHERE eu.user_id = u.user_id

); -- ?

3. 模式匹配与类型问题

-- 索引失效：

SELECT * FROM users WHERE name LIKE '%john%'; -- ?

SELECT * FROM users WHERE user_id = 12345; -- ? user_id是字符串类型

-- 优化方案：

SELECT * FROM users WHERE name LIKE 'john%'; -- ?

SELECT * FROM users WHERE user_id = '12345'; -- ?

4. 逻辑操作与条件组合问题

-- 索引失效：

SELECT * FROM users WHERE status = 'active' OR age > 30; -- ?

SELECT * FROM users WHERE phone IS NULL; -- ?

-- 优化方案：

SELECT * FROM users WHERE status = 'active'

UNION

SELECT * FROM users WHERE age > 30; -- ?

SELECT * FROM users WHERE phone = ''; -- ?

5. 复合索引使用不当

-- 索引失效（跳过联合索引的第一列）：

CREATE INDEX idx_status_time ON users(status, create_time);

SELECT * FROM users WHERE create_time > '2023-01-01'; -- ?

-- 索引失效（联合索引顺序问题）：

CREATE INDEX idx_time_status ON users(create_time, status);

SELECT * FROM users WHERE create_time > '2023-01-01' AND status = 'active'; -- ?

-- 优化方案：

SELECT * FROM users WHERE status = 'active' AND create_time > '2023-01-01'; -- ?

CREATE INDEX idx_status_time ON users(status, create_time); -- ?

6. 隐式类型转换问题

-- 索引失效：

SELECT * FROM orders WHERE order_no = 123456; -- ? order_no是字符串类型

-- 优化方案：

SELECT * FROM orders WHERE order_no = '123456'; -- ?

2.4 索引在JOIN查询中的应用

JOIN查询中的索引优化技巧

1. 选取适当的驱动表

-- 小表带动大表原则

-- 假设：departments表较小，employees表较大

SELECT e.name, d.department_name
FROM departments d -- 驱动表（小表）
INNER JOIN employees e ON d.department_id = e.department_id;

2. 利用覆盖索引减少回表操作

-- 创建覆盖索引

CREATE INDEX idx_employee_dept_cover ON employees(department_id, name, salary);

-- JOIN查询使用覆盖索引

SELECT d.department_name, e.name, e.salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id IN (1, 2, 3); -- 索引覆盖，无需回表

3. 避免在JOIN条件中使用函数

-- 不建议：JOIN条件使用函数

SELECT *
FROM orders o
JOIN customers c ON DATE(o.order_date) = DATE(c.registration_date);

-- 建议：预处理或使用范围查询

SELECT *
FROM orders o
JOIN customers c ON o.order_date >= c.registration_date
AND o.order_date < DATE_ADD(c.registration_date, INTERVAL 1 DAY);

4. 使用NOT EXISTS代替NOT IN

-- 不建议：NOT IN在JOIN中可能导致索引失效

SELECT * FROM users u
WHERE u.user_id NOT IN (
SELECT eu.user_id FROM excluded_users eu
);

-- 建议：使用NOT EXISTS，通常可以利用索引

SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM excluded_users eu WHERE eu.user_id = u.user_id
);

5. 优化JOIN条件的索引顺序

-- 不建议：JOIN条件顺序不妥

CREATE INDEX idx_time_status ON users(create_time, status);

SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.create_time > '2023-01-01' AND u.status = 'active';

-- 建议：依据JOIN查询模式设计索引

CREATE INDEX idx_status_time ON users(status, create_time);

SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active' AND u.create_time > '2023-01-01';

2.5 索引的维护与监控

索引维护策略

1. 查看索引使用状况

-- 查看表的所有索引

SHOW INDEX FROM table_name;

-- 分析索引使用统计

SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'your_database' AND table_name = 'your_table';

-- 查看索引使用频率

SELECT
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_READ DESC;

2. 整理索引碎片

-- 检查索引碎片

SELECT
TABLE_NAME,
INDEX_NAME,
ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) as fragmentation_percent
FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'your_database'
AND DATA_FREE > 0;

-- 重建索引
ALTER TABLE table_name ENGINE=InnoDB; -- 重构表（包括索引）
OPTIMIZE TABLE table_name; -- 优化表（包括索引整理）

三、高级查询模式

3.1 窗口函数深入应用

核心概念：
在不更改行数的前提下，对查询结果的“窗口”执行计算

窗口函数结构：
窗口函数() OVER (
[PARTITION BY 分区字段]
[ORDER BY 排序字段]
[窗口框架]
)

常用窗口函数分类

1. 排名函数
   • -- 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;
2. 聚合窗口函数
   • -- 计算移动平均
     SELECT
     order_date, amount,
     AVG(amount) OVER (
     ORDER BY order_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) as moving_avg
     FROM daily_sales;
   • -- 累计求和
     SELECT
     month, revenue,
     SUM(revenue) OVER (
     ORDER BY month
     ROWS UNBOUNDED PRECEDING
     ) as cumulative_revenue
     FROM monthly_sales;
3. 偏移函数
   • -- LAG(): 获取前一行数据
     SELECT
     date, sales,
     LAG(sales) OVER (ORDER BY date) as prev_sales,
     sales - LAG(sales) OVER (ORDER BY date) as sales_growth
     FROM daily_sales;
   • -- LEAD(): 获取后一行数据
     SELECT
     employee_id, evaluation_date, score,
     LEAD(score) OVER (PARTITION BY employee_id ORDER BY evaluation_date) as next_score
     FROM performance_reviews;

3.2 递归查询（CTE递归）

递归CTE语法结构

递归查询基本格式：
WITH RECURSIVE cte_name AS (
-- 锚点查询（初始结果）
SELECT ... FROM ... WHERE ...
UNION [ALL]
-- 递归查询
SELECT ... FROM cte_name JOIN ... WHERE ...
)
SELECT * FROM cte_name;

递归查询应用场景

1. 层次结构查询（组织架构）
   -- 查询员工及其所有下属
   WITH RECURSIVE employee_hierarchy AS (
   -- 锚点：顶级管理者
   SELECT
   employee_id,
   name,
   manager_id,
   0 as level,
   CAST(name AS CHAR(1000)) as path
   FROM employees
   WHERE manager_id IS NULL
   UNION ALL
   -- 递归：下属员工
   SELECT
   e.employee_id,
   e.name,
   e.manager_id,
   eh.level + 1,
   CONCAT(eh.path, ' -> ', e.name)
   FROM employees e
   INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
   )
   SELECT * FROM employee_hierarchy ORDER BY level, employee_id;

四、SQL执行计划分析与优化

4.1 EXPLAIN深度解析

EXPLAIN关键字段解读

1. type字段（访问类型）
   const/system
   ：最优，通过主键或唯一索引直接定位
   ref/eq_ref
   ：良好，使用非唯一索引或外键关联
   range
   ：较好，索引范围扫描
   index
   ：一般，全索引扫描

ALL：最糟糕，全表扫描

2. key/possible_keys字段

key：实际上用的索引

possible_keys：可能用到的索引

若key为NULL而possible_keys有值，则表明索引未被使用

3. rows字段

预计需扫描的行数，数值越低越好

4. Extra字段

Using index：覆盖索引，效能最优

Using where：在存储引擎层筛选

Using temporary：使用临时表

Using filesort：文件排序，需优化

4.2 执行计划实战分析

EXPLAIN实战案例解析

案例：复杂查询执行计划分析

-- 复杂查询示例

EXPLAIN

SELECT o.order_id, c.customer_name, p.product_name, oi.quantity

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.product_id

WHERE o.order_date >= '2023-01-01'

AND o.status = 'completed'

AND c.country = 'China'

ORDER BY o.order_date DESC

LIMIT 100;

执行计划分析要点：

检查type字段：确保不是ALL（全表扫描）

检查key字段：确认使用了恰当的索引

检查rows字段：估计扫描行数是否合理

检查Extra字段：避免Using temporary和Using filesort

优化建议：

为orders表的order_date和status字段建立复合索引

为customers表的country字段建立索引

确保JOIN条件字段都具有适当的索引

五、慢查询诊断与优化

5.1 慢查询日志配置与分析

慢查询日志配置

MySQL配置示例：

-- 查看当前慢查询设置

SHOW VARIABLES LIKE '%slow_query%';

SHOW VARIABLES LIKE '%long_query_time%';

-- 启用慢查询日志

SET GLOBAL slow_query_log = 1;

SET GLOBAL long_query_time = 2; -- 记录超过2秒的查询

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录未使用索引的查询

SET GLOBAL log_queries_not_using_indexes = 1;

慢查询分析工具

1. mysqldumpslow

# 分析慢查询日志

mysqldumpslow -s t /var/log/mysql/slow.log

# 按执行时间排序

mysqldumpslow -s t /var/log/mysql/slow.log

# 按执行次数排序

mysqldumpslow -s c /var/log/mysql/slow.log

2. pt-query-digest

# 使用Percona工具分析

pt-query-digest /var/log/mysql/slow.log

# 输出至文件

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

5.2 慢查询优化实战

慢查询优化黄金法则

1. 索引优先原则

为WHERE、JOIN、ORDER BY、GROUP BY相关的列创建索引

优先考虑组合索引，遵循最左前缀规则

避免在索引列上使用函数或运算

2. 查询简化原则

避免SELECT *，仅查询所需的字段

使用LIMIT限制返回的行数

避免在WHERE子句中使用不等于操作符(!=, <>)

使用EXISTS替代IN子查询

3. 批量操作原则

使用批量INSERT替代单独插入

使用JOIN替代子查询

避免在循环中执行SQL查询

4. 执行计划优化原则

避免全表扫描：确保type不为ALL

使用覆盖索引：争取Extra显示Using index

减少临时表：避免Using temporary

优化排序：消除Using filesort

六、系统级性能优化策略

核心目标：从系统架构层面提高数据库整体性能，构建高可用、可扩展的数据库环境

6.1 架构设计优化

读写分离架构

适用场景：读多写少、读写比例>3:1的业务

主从复制：主库负责写操作，从库负责读操作

负载均衡：利用MyCat、ProxySQL等中间件分配读请求

数据一致性：保证主从同步延迟<1秒

分库分表策略

适用场景：单表数据量>1000万、业务模块独立

垂直分库

按业务功能划分（用户库、订单库、商品库）

横向分割表

按时间/地区/哈希算法分割大型表

分片键选取

选择数据分布均衡的字段（如用户ID）

6.2 硬件配置改进

存储系统改进

优先选用SSD

相较于HDD提高I/O效能5-10倍

RAID 10设置

同时确保性能和数据安全性

文件系统

推荐XFS或ext4，不建议使用FAT32

内存配置改进

缓冲池尺寸

innodb_buffer_pool_size = 系统RAM × 70%

缓冲池实例数量

innodb_buffer_pool_instances = CPU核心数目

监控参数

目标缓冲池命中率>95%

连接管理改进

最大连接数量

max_connections = 应用程序连接数 × 1.5

线程缓存

thread_cache_size = max_connections × 15%

连接超时时间

wait_timeout = 300秒（防止长时间连接占用）

6.3 系统运维改进

重要监控参数

QPS/TPS

查询/事务处理量

连接数量

活动连接、最大连接限制

锁等待

InnoDB行级锁等待时长

I/O效能

磁盘读写吞吐量和延迟

内核参数调整

# 调整文件描述符上限（防止“Too many open files”错误）

ulimit -n 65536 # 默认值1024不足，高并发情况需更多文件句柄

# 优化TCP参数（增强数据库连接性能）

net.core.somaxconn = 65535 # 增加最大连接队列长度，避免连接请求被拒

net.ipv4.tcp_max_syn_backlog = 65535 # 扩大SYN队列长度，抵御SYN Flood攻击影响

进一步学习

建议学习路线：

下一篇文章

《B.40.5.3-数据库设计与范式理论.md》

学习要点

数据库规范化理论、表结构设计准则、数据建模实践

推荐理由

掌握设计标准化的数据库结构，减少数据重复和异常，使你的系统构架更加稳固、易于维护。这是架构师必须具备的核心技能！

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：sql registration Departments Connections performance