粕RAG 架构地基工程-Retrieval 模块的系统设计分享I

SQL 优化是数据库性能调优的核心环节。合理的 SQL 优化可以显著提升数据库性能，减少资源消耗。

1、查询性能分析

1.1 识别慢查询

-- 开启慢查询日志

SET GLOBAL slow_query_log = 'ON';

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

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

-- 查看慢查询

SHOW VARIABLES LIKE 'slow_query%';

SHOW VARIABLES LIKE 'long_query_time';

-- 使用性能模式监控

SELECT * FROM performance_schema.events_statements_summary_by_digest

ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

1.2 使用 EXPLAIN 分析查询

-- 基本分析

EXPLAIN SELECT * FROM users WHERE age > 30;

-- 详细分析（MySQL 8.0+）

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

-- JSON格式输出

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;

1.3 解读 EXPLAIN 结果

关键字段解读：

type：访问类型，从好到坏：system > const > eq_ref > ref > range > index > ALL

key：实际使用的索引

rows：预估扫描行数

Extra：额外信息

Using index：覆盖索引

Using where：使用 WHERE 过滤

Using temporary：使用临时表

Using filesort：使用文件排序

2、索引优化策略

2.1 选择合适的索引列

-- 为高频查询条件创建索引

CREATE INDEX idx_user_status ON orders(user_id, status);

-- 为排序和分组字段创建索引

CREATE INDEX idx_created_at ON orders(created_at);

-- 使用覆盖索引

CREATE INDEX idx_covering ON users(name, age, email);

2.2 避免索引失效

-- 1. 避免在索引列上使用函数

-- 错误：SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 正确：SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- 2. 避免 LIKE 以通配符开头

-- 错误：SELECT * FROM users WHERE name LIKE '%John%';

-- 正确：SELECT * FROM users WHERE name LIKE 'John%';

-- 3. 避免对索引列进行运算

-- 错误：SELECT * FROM users WHERE age + 1 > 30;

-- 正确：SELECT * FROM users WHERE age > 29;

-- 4. 注意 OR 条件的使用

-- 错误：SELECT * FROM users WHERE name = 'John' OR age = 30;

-- 正确：SELECT * FROM users WHERE name = 'John'

-- UNION

-- SELECT * FROM users WHERE age = 30;

2.3 索引优化技巧

-- 使用前缀索引

SELECT

COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS selectivity_5,

COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS selectivity_10

FROM users;

CREATE INDEX idx_name_prefix ON users(name(10));

-- 使用索引合并

SET SESSION optimizer_switch = 'index_merge=on';

3、查询语句优化

3.1 SELECT 优化

-- 只选择需要的列

-- 错误：SELECT * FROM users;

-- 正确：SELECT id, name, email FROM users;

-- 使用 LIMIT 限制结果集

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 避免 SELECT DISTINCT 滥用

-- 错误：SELECT DISTINCT * FROM users;

-- 正确：SELECT DISTINCT department_id FROM users;

3.2 WHERE 优化

3.2.1 基础

-- 使用等值查询优先

SELECT * FROM users WHERE id = 100;

-- 避免使用 != 或 <>

SELECT * FROM users WHERE status IN ('active', 'inactive');

-- 使用 BETWEEN 代替范围查询

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;

3.2.2 避免索引失效的写法

失效场景 错误示例 优化示例

函数操作索引字段 WHERE YEAR(created_at) = 2024 WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

隐式类型转换 WHERE phone = 13800138000 WHERE phone = '13800138000'

模糊查询以%开头 WHERE username LIKE '%张三%' WHERE username LIKE '张三%'

使用OR连接非索引字段 WHERE id = 1 OR age = 20 拆分查询或为age建索引

3.2.3 优化范围查询

范围条件（>, <, BETWEEN）右侧的字段无法使用索引，复合索引中应将范围字段放右侧。

-- 复合索引 (user_id, created_at)

-- 有效：范围条件在右侧

SELECT * FROM `order` WHERE user_id = 100 AND created_at > '2024-01-01';

-- 低效：范围条件在左侧，右侧字段无法使用索引

SELECT * FROM `order` WHERE created_at > '2024-01-01' AND user_id = 100;

3.3 JOIN 优化

确保连接字段建立索引（尤其是外键字段）。

小表驱动大表（左表数据量应小于右表，减少匹配次数）。

避免JOIN过多表（建议不超过 5 张表）。

优化前：

-- 无索引，多表连接效率低

SELECT * FROM `order` o

JOIN `user` u ON o.user_id = u.id

JOIN `order_item` oi ON o.id = oi.order_id;

优化后：

-- 为连接字段建索引

ALTER TABLE `order` ADD INDEX `idx_user_id` (`user_id`);

ALTER TABLE `order_item` ADD INDEX `idx_order_id` (`order_id`);

-- 只查询必要字段

SELECT o.order_no, u.username, oi.product_id

FROM `order` o

JOIN `user` u ON o.user_id = u.id

JOIN `order_item` oi ON o.id = oi.order_id;

3.4 GROUP BY 和 ORDER BY 优化

ORDER BY和GROUP BY的字段应包含在索引中，避免额外排序（Using filesort）。

复合索引中，排序字段需按同一方向（全升序或全降序）

优化前：

-- 无索引，产生Using filesort

SELECT * FROM `order` WHERE user_id = 100 ORDER BY created_at;

优化后：

-- 创建包含排序字段的复合索引

ALTER TABLE `order` ADD INDEX `idx_user_created` (`user_id`, `created_at`);

-- 索引覆盖排序，无filesort

SELECT id, order_no FROM `order` WHERE user_id = 100 ORDER BY created_at;

3.5 子查询优化

-- 使用 JOIN 代替子查询

-- 错误：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 正确：SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;

-- 使用 EXISTS 代替 IN

SELECT * FROM users u

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 将子查询转换为连接

SELECT u.*, o.order_count

FROM users u

JOIN (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) o

ON u.id = o.user_id;

4、表结构优化

4.1 选择合适的数据类型

优先使用小字段类型（如INT代替BIGINT，VARCHAR(20)代替VARCHAR(255)）。

时间用DATETIME（需时区转换）或TIMESTAMP（自动时区转换，范围小）。

枚举类型用ENUM（如状态字段：ENUM('active', 'inactive')）。

优化前：

CREATE TABLE `user` (

`id` BIGINT PRIMARY KEY, -- 无需大整数

`status` VARCHAR(20), -- 状态值有限，可用ENUM

`create_time` VARCHAR(50) -- 应用层转换，效率低

);

优化后：

CREATE TABLE `user` (

`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

`status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',

`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP

);

4.2 分表分库

当单表数据量超过 1000 万行时，需考虑拆分：

水平分表：按行拆分（如按用户 ID 哈希、按时间范围）。

垂直分表：按列拆分（如将大文本字段拆分到独立表）。

示例：按时间水平分表

-- 订单表按年份拆分

CREATE TABLE `order_2023` (LIKE `order` INCLUDING ALL);

CREATE TABLE `order_2024` (LIKE `order` INCLUDING ALL);

4.3 分区表优化

-- 按范围分区

CREATE TABLE sales (

id INT AUTO_INCREMENT,

sale_date DATE,

amount DECIMAL(10,2),

PRIMARY KEY (id, sale_date)

) PARTITION BY RANGE (YEAR(sale_date)) (

PARTITION p2020 VALUES LESS THAN (2021),

PARTITION p2021 VALUES LESS THAN (2022),

PARTITION p2022 VALUES LESS THAN (2023),

PARTITION p2023 VALUES LESS THAN (2024)

);

-- 按列表分区

CREATE TABLE users (

id INT AUTO_INCREMENT,

region VARCHAR(20),

name VARCHAR(100),

PRIMARY KEY (id, region)

) PARTITION BY LIST COLUMNS(region) (

PARTITION p_north VALUES IN ('Beijing', 'Tianjin'),

PARTITION p_south VALUES IN ('Guangzhou', 'Shenzhen'),

PARTITION p_west VALUES IN ('Chengdu', 'Chongqing')

);

5、服务器参数

通过调整 MySQL 配置文件（my.cnf或my.ini）提升性能

[mysqld]

# 缓存相关

innodb_buffer_pool_size = 4G # InnoDB缓存池（建议物理内存的50%-70%）

query_cache_size = 0 # 禁用查询缓存（MySQL 8.0已移除）

# 连接相关

max_connections = 1000 # 最大连接数

wait_timeout = 600 # 连接超时时间（10分钟）

# 日志相关

slow_query_log = 1 # 开启慢查询日志

long_query_time = 1 # 慢查询阈值（1秒）

slow_query_log_file = /var/log/mysql/slow.log

# InnoDB优化

innodb_flush_log_at_trx_commit = 1 # 事务提交时同步日志（安全性优先）

innodb_flush_method = O_DIRECT # 绕过OS缓存，直接写磁盘