登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›MySQL SQL优化

MySQL SQL优化

予捻 2025-10-1 13:19:07
SQL 优化是数据库性能调优的核心环节。合理的 SQL 优化可以显著提升数据库性能，减少资源消耗。
1、查询性能分析

1.1 识别慢查询
  1. -- 开启慢查询日志
  2. SET GLOBAL slow_query_log = 'ON';
  3. SET GLOBAL long_query_time = 2; -- 超过2秒的查询
  4. SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
  6. -- 查看慢查询
  7. SHOW VARIABLES LIKE 'slow_query%';
  8. SHOW VARIABLES LIKE 'long_query_time';
  10. -- 使用性能模式监控
  11. SELECT * FROM performance_schema.events_statements_summary_by_digest
  12. ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
复制代码
1.2 使用 EXPLAIN 分析查询
  1. -- 基本分析
  2. EXPLAIN SELECT * FROM users WHERE age > 30;
  4. -- 详细分析（MySQL 8.0+）
  5. EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
  7. -- JSON格式输出
  8. 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 选择合适的索引列
  1. -- 为高频查询条件创建索引
  2. CREATE INDEX idx_user_status ON orders(user_id, status);
  4. -- 为排序和分组字段创建索引
  5. CREATE INDEX idx_created_at ON orders(created_at);
  7. -- 使用覆盖索引
  8. CREATE INDEX idx_covering ON users(name, age, email);
复制代码
2.2 避免索引失效
  1. -- 1. 避免在索引列上使用函数
  2. -- 错误：SELECT * FROM users WHERE YEAR(created_at) = 2023;
  3. -- 正确：SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
  5. -- 2. 避免 LIKE 以通配符开头
  6. -- 错误：SELECT * FROM users WHERE name LIKE '%John%';
  7. -- 正确：SELECT * FROM users WHERE name LIKE 'John%';
  9. -- 3. 避免对索引列进行运算
  10. -- 错误：SELECT * FROM users WHERE age + 1 > 30;
  11. -- 正确：SELECT * FROM users WHERE age > 29;
  13. -- 4. 注意 OR 条件的使用
  14. -- 错误：SELECT * FROM users WHERE name = 'John' OR age = 30;
  15. -- 正确：SELECT * FROM users WHERE name = 'John'
  16. --        UNION
  17. --        SELECT * FROM users WHERE age = 30;
复制代码
2.3 索引优化技巧
  1. -- 使用前缀索引
  2. SELECT
  3.     COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS selectivity_5,
  4.     COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS selectivity_10
  5. FROM users;
  7. CREATE INDEX idx_name_prefix ON users(name(10));
  9. -- 使用索引合并
  10. SET SESSION optimizer_switch = 'index_merge=on';
复制代码
3、查询语句优化

3.1 SELECT 优化
  1. -- 只选择需要的列
  2. -- 错误：SELECT * FROM users;
  3. -- 正确：SELECT id, name, email FROM users;
  5. -- 使用 LIMIT 限制结果集
  6. SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
  8. -- 避免 SELECT DISTINCT 滥用
  9. -- 错误：SELECT DISTINCT * FROM users;
  10. -- 正确：SELECT DISTINCT department_id FROM users;
复制代码
3.2 WHERE 优化

3.2.1 基础
  1. -- 使用等值查询优先
  2. SELECT * FROM users WHERE id = 100;
  4. -- 避免使用 != 或 <>
  5. SELECT * FROM users WHERE status IN ('active', 'inactive');
  7. -- 使用 BETWEEN 代替范围查询
  8. SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;
复制代码
3.2.2 避免索引失效的写法

失效场景错误示例优化示例函数操作索引字段WHERE YEAR(created_at) = 2024WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'隐式类型转换WHERE phone = 13800138000WHERE phone = '13800138000'模糊查询以%开头WHERE username LIKE '%张三%'WHERE username LIKE '张三%'使用OR连接非索引字段WHERE id = 1 OR age = 20拆分查询或为age建索引3.2.3 优化范围查询


范围条件（>,  '2024-01-01';-- 低效：范围条件在左侧，右侧字段无法使用索引SELECT * FROM `order` WHERE created_at > '2024-01-01' AND user_id = 100;[/code]3.3 JOIN 优化


  • 确保连接字段建立索引（尤其是外键字段）。
  • 小表驱动大表（左表数据量应小于右表，减少匹配次数）。
  • 避免JOIN过多表（建议不超过 5 张表）。
优化前：
  1. -- 复合索引 (user_id, created_at)
  2. -- 有效：范围条件在右侧
  3. SELECT * FROM `order` WHERE user_id = 100 AND created_at > '2024-01-01';
  5. -- 低效：范围条件在左侧，右侧字段无法使用索引
  6. SELECT * FROM `order` WHERE created_at > '2024-01-01' AND user_id = 100;
复制代码
优化后：
  1. -- 无索引，多表连接效率低
  2. SELECT * FROM `order` o
  3. JOIN `user` u ON o.user_id = u.id
  4. JOIN `order_item` oi ON o.id = oi.order_id;
复制代码
3.4 GROUP BY 和 ORDER BY 优化


  • ORDER BY和GROUP BY的字段应包含在索引中，避免额外排序（Using filesort）。
  • 复合索引中，排序字段需按同一方向（全升序或全降序）
    优化前：
  1. -- 为连接字段建索引
  2. ALTER TABLE `order` ADD INDEX `idx_user_id` (`user_id`);
  3. ALTER TABLE `order_item` ADD INDEX `idx_order_id` (`order_id`);
  5. -- 只查询必要字段
  6. SELECT o.order_no, u.username, oi.product_id
  7. FROM `order` o
  8. JOIN `user` u ON o.user_id = u.id
  9. JOIN `order_item` oi ON o.id = oi.order_id;
复制代码
优化后：
  1. -- 无索引，产生Using filesort
  2. SELECT * FROM `order` WHERE user_id = 100 ORDER BY created_at;
复制代码
3.5 子查询优化
  1. -- 创建包含排序字段的复合索引
  2. ALTER TABLE `order` ADD INDEX `idx_user_created` (`user_id`, `created_at`);
  4. -- 索引覆盖排序，无filesort
  5. SELECT id, order_no FROM `order` WHERE user_id = 100 ORDER BY created_at;
复制代码
4、表结构优化

4.1 选择合适的数据类型


  • 优先使用小字段类型（如INT代替BIGINT，VARCHAR(20)代替VARCHAR(255)）。
  • 时间用DATETIME（需时区转换）或TIMESTAMP（自动时区转换，范围小）。
  • 枚举类型用ENUM（如状态字段：ENUM('active', 'inactive')）。
优化前：
  1. -- 使用 JOIN 代替子查询
  2. -- 错误：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
  3. -- 正确：SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;
  5. -- 使用 EXISTS 代替 IN
  6. SELECT * FROM users u
  7. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
  9. -- 将子查询转换为连接
  10. SELECT u.*, o.order_count
  11. FROM users u
  12. JOIN (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) o
  13. ON u.id = o.user_id;
复制代码
优化后：
  1. CREATE TABLE `user` (
  2.   `id` BIGINT PRIMARY KEY,  -- 无需大整数
  3.   `status` VARCHAR(20),  -- 状态值有限，可用ENUM
  4.   `create_time` VARCHAR(50)  -- 应用层转换，效率低
  5. );
复制代码
4.2 分表分库

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

  • 水平分表：按行拆分（如按用户 ID 哈希、按时间范围）。
  • 垂直分表：按列拆分（如将大文本字段拆分到独立表）。
    示例：按时间水平分表
  1. CREATE TABLE `user` (
  2.   `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  3.   `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  4.   `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP
  5. );
复制代码
4.3 分区表优化
  1. -- 订单表按年份拆分
  2. CREATE TABLE `order_2023` (LIKE `order` INCLUDING ALL);
  3. CREATE TABLE `order_2024` (LIKE `order` INCLUDING ALL);
复制代码
5、服务器参数

通过调整 MySQL 配置文件（my.cnf或my.ini）提升性能
  1. -- 按范围分区
  2. CREATE TABLE sales (
  3.     id INT AUTO_INCREMENT,
  4.     sale_date DATE,
  5.     amount DECIMAL(10,2),
  6.     PRIMARY KEY (id, sale_date)
  7. ) PARTITION BY RANGE (YEAR(sale_date)) (
  8.     PARTITION p2020 VALUES LESS THAN (2021),
  9.     PARTITION p2021 VALUES LESS THAN (2022),
  10.     PARTITION p2022 VALUES LESS THAN (2023),
  11.     PARTITION p2023 VALUES LESS THAN (2024)
  12. );
  14. -- 按列表分区
  15. CREATE TABLE users (
  16.     id INT AUTO_INCREMENT,
  17.     region VARCHAR(20),
  18.     name VARCHAR(100),
  19.     PRIMARY KEY (id, region)
  20. ) PARTITION BY LIST COLUMNS(region) (
  21.     PARTITION p_north VALUES IN ('Beijing', 'Tianjin'),
  22.     PARTITION p_south VALUES IN ('Guangzhou', 'Shenzhen'),
  23.     PARTITION p_west VALUES IN ('Chengdu', 'Chongqing')
  24. );
复制代码
6、总结

MySQL SQL 优化是一个系统工程，需要从多个层面综合考虑：

  • 查询层面：编写高效的SQL，避免反模式
  • 索引层面：合理设计索引，使用覆盖索引
  • 结构层面：优化数据类型，适当反规范化
  • 系统层面：调整配置参数，优化硬件资源
  • 架构层面：考虑读写分离、分库分表

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
MySQLSQL优化

使用道具 举报

相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
予捻
2025-10-1 13:19:07

0

粉丝关注

17

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫