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.pswp.cn/bicheng/95487.shtml
繁体地址，请注明出处：http://hk.pswp.cn/bicheng/95487.shtml
英文地址，请注明出处：http://en.pswp.cn/bicheng/95487.shtml

如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

探索数据结构中的 “树”：揭开层次关系的奥秘

探索数据结构中的 “树”：揭开层次关系的奥秘

在计算机科学的广袤森林中&#xff0c;有一种数据结构如同参天大树般支撑着无数应用的根基 —— 它就是 “树”&#xff08;Tree&#xff09;。它不仅仅是一个抽象概念&#xff0c;更是我们理解和组织信息、模拟现实世界层级关系的强大工具。1. 什么是 “树”&#xff1f;从家族…
阅读更多...
技术框架之RPC

技术框架之RPC

一、序言&#xff1a;为什么我们需要RPC&#xff1f;在单体应用时代&#xff0c;函数调用是进程内的简单操作。但随着业务规模扩大&#xff0c;系统被拆分为多个独立服务&#xff08;如订单服务、支付服务&#xff09;&#xff0c;服务间通信成为刚需。早期开发者常使用HTTPJSO…
阅读更多...
【光照】Unity中的[光照模型]概念辨析

【光照】Unity中的[光照模型]概念辨析

【从UnityURP开始探索游戏渲染】专栏-直达 基础光照模型‌ ‌标准光照模型&#xff08;Standard Lighting Model&#xff09;‌ ‌定义‌&#xff1a;传统光照计算的框架&#xff0c;通常包含漫反射、镜面反射和环境光三部分。‌特点‌&#xff1a;非物理经验模型&#xff0c…
阅读更多...
MCU上跑AI—实时目标检测算法探索

MCU上跑AI—实时目标检测算法探索

MCU上跑实时目标检测算法 前几年一直忙着别的事情没有在技术分享上下功夫, 这段时间稳定下来就想和几个志同道合的朋友做点有意义的事情, 于是乎就使用MCU做了个与AI有识别相关的 “小玩意儿”. 本人负责嵌入式端相关的编码, AI相关的工作由好友 AgeWang 负责. 这儿把一些成果给…
阅读更多...
SpringBoot 整合 RabbitMQ 的完美实践

SpringBoot 整合 RabbitMQ 的完美实践

引言： 本文总字数：约 9200 字 预计阅读时间：38 分钟 为什么 RabbitMQ 是消息中间件的优选？ 在分布式系统架构中，消息中间件扮演着 "交通枢纽" 的角色，负责协调各个服务之间的通信。目前主流的消息中间件有 RabbitMQ、Kafka 和 RocketMQ，它们各具特色： Kafka…
阅读更多...
nestjs 发起请求 axios

nestjs 发起请求 axios

1、下载npm i --save nestjs/axios axios2、全局配置import { HttpModule } from nestjs/axios;Global() Module({imports: [HttpModule.registerAsync({inject: [ConfigService],useFactory: async (configService: ConfigService) > {return {timeout: configService.get(…
阅读更多...
将 Logits 得分转换为概率，如何计算

将 Logits 得分转换为概率，如何计算

场景&#xff1a;动物识别&#xff0c;输入一张28*28的图像&#xff0c;模型输出属于 猫、狗、鸟 哪个类型。需求&#xff1a;假设模型 ​​Logits&#xff08;模型在每个类别的置信度得分&#xff09; 输出为​​&#xff1a;[猫: 3.2, 狗: 1.5, 鸟: -0.8]。计算 ​​Softmax …
阅读更多...
【Qt】bug排查笔记——QMetaObject::invokeMethod: No such method

【Qt】bug排查笔记——QMetaObject::invokeMethod: No such method

问题如题目所示&#xff1a;QMetaObject::invokeMethod: No such method xxxx&#xff0c;在网上好一顿查&#xff0c;又将查到的资料喂给了 Ai&#xff0c;才最终将问题解决&#xff0c;特此记录下。 一、问题背景 在做公司项目时&#xff0c;使用了插件的方式开发。主程序加载…
阅读更多...
Spring Boot手写10万敏感词检查程序

Spring Boot手写10万敏感词检查程序

使用Spring Boot手写10万敏感词检查程序 本文将介绍如何使用Spring Boot构建一个高效的敏感词检查系统，能够处理多达10万个敏感词的检测需求。我们将使用DFA（Deterministic Finite Automaton）算法来实现高效匹配，并提供RESTful API接口。 实现步骤 1. 创建Spring Boot项…
阅读更多...
零构建的快感！dagger.js 与 React Hooks 实现对比，谁更优雅？

零构建的快感！dagger.js 与 React Hooks 实现对比，谁更优雅？

“Add Tags” 技术方案并行对比&#xff1a;React Hooks vs dagger.js&#xff08;含核心 JS 代码&#xff09; 源码&#xff1a; React Hooks&#xff1a;https://codepen.io/prvnbist/pen/jJzROe?editors1010dagger.js&#xff1a;https://codepen.io/dagger8224/pen/ZErjzw…
阅读更多...
矩池云中LLaMA- Factory多机多卡训练

矩池云中LLaMA- Factory多机多卡训练

LLaMA Factory 是一款开源低代码大模型微调框架&#xff0c;集成了业界最广泛使用的微调技术&#xff0c;支持通过 Web UI 界面零代码微调大模型&#xff0c;目前已经成为开源社区内最受欢迎的微调框架之一。但是在矩池云上如何使用LLaMA-Factory多机多卡训练模型呢&#xff1f…
阅读更多...
Nginx的反向代理与正向代理及其location的配置说明

Nginx的反向代理与正向代理及其location的配置说明

一、Nginx中location匹配优先级Nginx中location匹配优先级location支持各种匹配规则&#xff0c;在多个匹配规则下&#xff0c;Nginx对location的处理是有优先级的&#xff0c;优先级高的规则会优先进行处理&#xff1b;而优先级低的规则可能会最后处理或者不进行处理。注意&am…
阅读更多...
神经网络正则化三重奏：Weight Decay, Dropout, 和LayerNorm

神经网络正则化三重奏：Weight Decay, Dropout, 和LayerNorm

正则化是机器学习中防止模型过拟合、提升泛化能力的核心技术。Weight Decay、Dropout和LayerNorm是三种最常用的方法&#xff0c;但它们的工作原理和首要目标截然不同。下面的流程图揭示了它们的核心区别与联系&#xff1a; #mermaid-svg-vymek6mFvvfxcWiM {font-family:"…
阅读更多...
两台电脑通过网线直连共享数据，设置正确，却互相ping不通的解决方法

两台电脑通过网线直连共享数据，设置正确，却互相ping不通的解决方法

因为某些原因&#xff0c;需要两台电脑互传资源&#xff0c;但是某台电脑可能无法连接外网。如果手头有根网线&#xff0c;很容易想到通过一根网线连接两台电脑互传数据。 这里先说一下基本的设置&#xff1a; 两台电脑最好都关闭防火墙&#xff1b;两台电脑都打开专用网络和公…
阅读更多...
面试新纪元：无声胜有声，让AI成为你颈上的智慧伙伴

面试新纪元：无声胜有声，让AI成为你颈上的智慧伙伴

面试&#xff0c;无论是对于面试官还是求职者&#xff0c;都像一场无声的战争。 一方要精准识人&#xff0c;一方要完美自荐&#xff1b;一方怕问不到点子上&#xff0c;一方怕答不到心坎里。 紧张、遗忘、表达失误、准备不足……这些问题几乎每个人都经历过。 有没有一种方…
阅读更多...
qt-C++笔记之QtDesigner-Creator按钮图标与样式

qt-C++笔记之QtDesigner-Creator按钮图标与样式

qt-C笔记之QtDesigner-Creator按钮图标与样式 整理&#xff1a;如何用 .qrc 管理资源、在 Designer/Creator 中为 QPushButton 设置图标&#xff08;资源或系统主题&#xff09;&#xff0c;以及用样式表调整文字样式。涵盖 C/Qt 与 PySide/PyQt&#xff1b;Linux 桌面优先&am…
阅读更多...
maven 常用指令

maven 常用指令

Maven 是 Java 项目构建和依赖管理的得力助手。这里为你总结了一些常用指令&#xff0c;希望能帮你提升开发效率。下面这个表格汇总了 Maven 最核心和常用的一些命令&#xff1a;命令主要功能典型使用场景mvn clean清理项目&#xff0c;删除 target 目录及其所有编译输出文件。…
阅读更多...
# pdf.js完全指南：构建现代Web PDF查看与解析解决方案

# pdf.js完全指南：构建现代Web PDF查看与解析解决方案

在当今Web开发中&#xff0c;实现高质量的PDF查看功能一直是前端开发者面临的挑战之一。作为最受欢迎的JavaScript PDF库&#xff0c;pdf.js已经成为解决这一问题的行业标准。由Mozilla开发并维护的pdf.js项目&#xff0c;通过纯JavaScript实现PDF解析与渲染&#xff0c;彻底改…
阅读更多...
高效对象属性复制工具

高效对象属性复制工具

日常编程中&#xff0c;经常会碰到对象属性复制的场景&#xff0c;比如 VO、DTO、PO、VO 等之间的转换&#xff0c;关于什么是VO、DTO、PO、VO 等可以看上篇文章&#xff0c;VO、DTO、PO、VO 等对象具体有哪些方式可以使用呢&#xff1f; set/get 方式 性能最好的方式&#x…
阅读更多...
大疆图传技术参数对比 你了解多少？

大疆图传技术参数对比 你了解多少？

无人机是现代航空技术与智能控制技术结合的产物&#xff0c;已从军事领域广泛渗透至民用场景&#xff0c;成为推动各行业效率升级的关键工具。无人机的全称为 “无人驾驶航空器&#xff08;Unmanned Aerial Vehicle&#xff0c;简称 UAV&#xff09;”&#xff0c;简言之&#…
阅读更多...
最新文章

Copyright @ 2022~2023