当前位置： 首页 > ai >正文

SQL优化_以MySQL为例

ai 2025/9/2 5:08:38

MySQL SQL 优化详细教程与案例

1. 理解SQL执行过程

在优化之前，需要了解MySQL如何处理SQL查询：

  1. 客户端发送SQL语句到服务器
  2. 服务器检查查询缓存（MySQL 8.0已移除查询缓存）
  3. 解析器解析SQL，生成解析树
  4. 预处理器验证权限和表结构
  5. 优化器生成执行计划
  6. 执行引擎执行查询并返回结果

2. 使用EXPLAIN分析查询

EXPLAIN是优化SQL的最重要工具，它显示MySQL如何执行查询。

基础使用：

sql

EXPLAIN SELECT * FROM users WHERE age > 30;

解读EXPLAIN结果的关键列：

  • id: 查询标识符
  • select_type: 查询类型（SIMPLE, PRIMARY, SUBQUERY等）
  • table: 访问的表
  • type: 访问类型（从好到坏：system > const > eq_ref > ref > range > index > ALL）
  • possible_keys: 可能使用的索引
  • key: 实际使用的索引
  • rows: 估计要检查的行数
  • Extra: 额外信息（Using where, Using temporary, Using filesort等）

案例：分析慢查询

sql

-- 原始查询
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY total_amount DESC;-- 可能显示type: ALL（全表扫描），需要优化

3. 索引优化策略

3.1 创建合适的索引

sql

-- 案例：为上述查询创建复合索引
ALTER TABLE orders ADD INDEX idx_customer_date_amount (customer_id, order_date, total_amount);-- 再次分析
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY total_amount DESC;
-- 现在应该显示type: range，使用索引

3.2 避免索引失效的情况

sql

-- 1. 不要在索引列上使用函数
-- 不好的写法
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 好的写法
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';-- 2. 注意LI查询的通配符位置
-- 不能使用索引
SELECT * FROM users WHERE name LIKE '%john%';
-- 可以使用索引
SELECT * FROM users WHERE name LIKE 'john%';-- 3. 避免对索引列进行运算
-- 不能使用索引
SELECT * FROM products WHERE price * 1.1 > 100;
-- 可以使用索引
SELECT * FROM products WHERE price > 100 / 1.1;

3.3 使用覆盖索引

sql

-- 需要回表查询
SELECT * FROM orders WHERE customer_id = 100;-- 使用覆盖索引（只需要索引列）
SELECT customer_id, order_date, total_amount 
FROM orders 
WHERE customer_id = 100;
-- 为这个查询创建索引
ALTER TABLE orders ADD INDEX idx_customer_cover (customer_id, order_date, total_amount);

4. 查询优化技巧

4.1 避免SELECT *

sql

-- 不好的写法
SELECT * FROM users WHERE age > 30;-- 好的写法
SELECT id, name, email FROM users WHERE age > 30;

4.2 优化JOIN查询

sql

-- 确保JOIN字段有索引
EXPLAIN SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA';-- 为user_id和country添加索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE users ADD INDEX idx_country (country);-- 使用小表驱动大表
SELECT /*+ STRAIGHT_JOIN */ u.name, o.order_date, o.amount
FROM users u -- 假设users表比orders表小
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA';

4.3 优化子查询

sql

-- 使用JOIN代替子查询（通常更快）
-- 原始子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);-- 使用JOIN优化
SELECT DISTINCT u.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;-- 或者使用EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

4.4 优化GROUP BY和ORDER BY

sql

-- 确保GROUP BY和ORDER BY使用索引
EXPLAIN SELECT category, COUNT(*) 
FROM products 
GROUP BY category;-- 为category添加索引
ALTER TABLE products ADD INDEX idx_category (category);-- 对于混合排序和分组，可以使用索引优化
ALTER TABLE products ADD INDEX idx_category_price (category, price);EXPLAIN SELECT category, AVG(price)
FROM products
GROUP BY category
ORDER BY AVG(price) DESC;

4.5 分页优化

sql

-- 传统分页在大偏移量时很慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;-- 使用索引优化分页
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 20;-- 或者使用JOIN方式
SELECT o.* 
FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

5. 数据库设计优化

5.1 规范化与反规范化

  • 规范化：减少数据冗余，提高数据一致性
  • 反规范化：适当增加冗余，提高查询性能

sql

-- 示例：在订单表中反规范化存储用户名
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.customer_name = u.name;-- 这样查询订单时就不需要JOIN用户表
SELECT order_id, order_date, customer_name, amount
FROM orders
WHERE customer_name LIKE 'John%';

5.2 选择合适的数据类型

sql

-- 使用更小的数据类型
-- 不好的设计
CREATE TABLE users (id BIGINT, -- 过度设计，除非真有数十亿用户age INT,   -- 用TINYINT足够(0-255)status VARCHAR(10) -- 用ENUM更高效
);-- 好的设计
CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT,age TINYINT UNSIGNED,status ENUM('active', 'inactive', 'pending'),PRIMARY KEY (id)
);

5.3 分区表

sql

-- 按时间范围分区 orders 表
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION pfuture VALUES LESS THAN MAXVALUE
);-- 查询特定年份的数据，只会扫描相关分区
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

6. 服务器配置优化

6.1 调整缓冲区大小

ini

# 在my.cnf或my.ini中配置
[mysqld]
# 分配给InnoDB缓冲池的内存，建议为系统内存的50-70%
innodb_buffer_pool_size = 4G# 键缓冲区大小，主要用于MyISAM
key_buffer_size = 256M# 查询缓存大小（MySQL 8.0已移除）
# query_cache_size = 128M

6.2 其他重要配置

ini

# 最大连接数
max_connections = 200# 临时表大小
tmp_table_size = 256M
max_heap_table_size = 256M# InnoDB日志文件大小
innodb_log_file_size = 512M

7. 实战优化案例

案例：电商平台订单查询优化

问题：订单查询页面响应缓慢，特别是筛选和分页功能

原始查询：

sql

SELECT * FROM orders 
WHERE status = 'completed' 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND customer_id IN (SELECT id FROM customers WHERE country = 'USA')
ORDER BY order_date DESC
LIMIT 0, 20;

优化步骤：

  1. 分析查询：

    sql

    EXPLAIN SELECT ...;
-- 发现全表扫描，使用了文件排序

  2. 创建索引：

    sql

    ALTER TABLE orders ADD INDEX idx_status_date (status, order_date);
ALTER TABLE customers ADD INDEX idx_country (country);

  3. 重写查询：

    sql

    SELECT o.* 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND c.country = 'USA'
ORDER BY o.order_date DESC
LIMIT 0, 20;

  4. 进一步优化分页：

    sql

    SELECT o.* 
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN (SELECT id FROM orders WHERE status = 'completed'AND order_date BETWEEN '2023-01-01' AND '2023-12-31'AND customer_id IN (SELECT id FROM customers WHERE country = 'USA')ORDER BY order_date DESCLIMIT 0, 20
) AS tmp ON o.id = tmp.id;

  5. 考虑反规范化：

    sql

    -- 在orders表中添加country字段
ALTER TABLE orders ADD COLUMN customer_country VARCHAR(50);UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.customer_country = c.country;-- 新查询
SELECT * FROM orders 
WHERE status = 'completed'
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND customer_country = 'USA'
ORDER BY order_date DESC
LIMIT 0, 20;

8. 监控与持续优化

8.1 启用慢查询日志

ini

# 在my.cnf中配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 记录执行时间超过2秒的查询
log_queries_not_using_indexes = 1

8.2 使用Performance Schema

sql

-- 查看最耗时的SQL
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;-- 查看全表扫描最多的表
SELECT * FROM sys.schema_table_statistics
WHERE rows_full_scanned > 0
ORDER BY rows_full_scanned DESC LIMIT 10;

8.3 定期优化表

-- 优化碎片化的表
OPTIMIZE TABLE orders, customers;-- 分析表统计信息
ANALYZE TABLE orders, customers;

总结

MySQL SQL优化是一个持续的过程，需要结合查询分析、索引优化、数据库设计调整和服务器配置优化。关键步骤包括：

  1. 使用EXPLAIN分析查询执行计划
  2. 创建合适的索引，避免索引失效
  3. 重写低效的查询语句
  4. 优化数据库 schema 设计
  5. 调整服务器配置参数
  6. 持续监控和优化性能

记住优化黄金法则：测量→优化→验证。永远基于实际性能数据做优化决策，而不是假设。

http://www.xdnf.cn/news/19240.html

相关文章：

  • ROS2的编译机制和工程组织形式
  • C++：list容器--模拟实现（下篇）
  • （链表）Leetcode206链表反转+Leetcode6删除链表的倒数第N个结点+虚拟头节点使用
  • Linux shell命令扩涨
  • 有限字长效应
  • Qt中的锁和条件变量和信号量
  • 数据结构青铜到王者第十三话---优先级队列（堆）（2）
  • Spring Cloud 和 Dubbo 是目前主流的两大微服务框架，分别代表了两种不同的技术路线
  • Systemd 启动初探
  • IPv6过渡技术6VPE
  • 【MYSQL】GET_LOCK使用方法简单解析
  • 直线与椭圆相交弦长计算公式
  • 【物联网】BLE Fundamentals 核心概念总结-广告-读写特征-LED控制-传感器通知-上下游通信过程
  • hashmap计算key的hash的时候为什么要右移16位
  • [光学原理与应用-329]：ZEMAX - 主要用途与主要功能
  • 复现 RoboDK 机械臂几何校准（Staubli TX2‑90L / TX200）
  • Redis（自写）
  • MySQL 简介
  • RocketMQ源码详解(NameServer启动流程)
  • Altium Designer中电路板设计
  • 【ICO】快速制作ICON教材/使用icofx3快速制作ico
  • 生成对抗网络（GAN）：深度学习领域的革命性突破
  • 深入解析HarmonyOS：UIAbility与Page的生命周期协同
  • var maxScore = Int.MinValue 详解
  • 最长递增子序列（LIS）的 DFS 解法详解与实现
  • 【69页PPT】智慧工厂数字化工厂蓝图规划建设方案（附下载方式）
  • 【计算机组成原理】LRU计数器问题
  • 项目管理的五个阶段是什么
  • 关于PXIe工控机的网速问题XH-PXIe7313万兆网卡
  • Java学习day_14之API（正则表达式）