【Mysql优化】SQL优化最佳实践分析与总结

news/2024/12/22 15:42:01 标签: mysql, sql, 数据库, java, 开发

文章目录

      • SQL优化最佳实践分析与总结
        • 1.2.1. 避免使用 `SELECT \*`
        • 1.2.2. 小表驱动大表
        • 1.2.3. 用连接查询代替子查询
        • 1.2.4. 提升 `GROUP BY` 的效率
        • 1.2.5. 批量操作
        • 1.2.6. 使用 `LIMIT` 优化查询
      • 总结

SQL优化最佳实践分析与总结

SQL查询性能的优化是数据库设计与使用中的核心问题之一,通过以下六大实践,我们可以显著提升查询效率,降低资源消耗:


1.2.1. 避免使用 SELECT \*

原因:

  1. 查询解析成本增加SELECT * 会将字段解析为所有列,这一过程增加了解析器的负担。
  2. 覆盖索引失效SELECT * 通常无法利用覆盖索引,会导致大量回表查询,效率低下。
  3. 网络传输负担:无用字段的传输,特别是大字段 (如 TEXT) 会浪费带宽。

优化建议:

  • 明确列出所需字段,确保查询列与索引列匹配。
  • 使用工具(如 SHOW WARNINGS)检查字段映射与优化建议。

1.2.2. 小表驱动大表

原理:
在 JOIN 操作中,优化器优先使用驱动表的索引匹配被驱动表的数据。小表作为驱动表能减少扫描次数,降低磁盘读取量,提高效率。

注意点:

  • Join Buffer 限制:驱动表数据过大时,需分阶段加载,导致被驱动表扫描次数增加。
  • 优化方法:选择数据量小、索引完善的表作为驱动表。

优化示例:

  • 不推荐:SELECT * FROM scores LEFT JOIN student ON ... (大表驱动小表)
  • 推荐:SELECT * FROM student LEFT JOIN scores ON ... (小表驱动大表)

1.2.3. 用连接查询代替子查询

问题:

  • 子查询需要多次数据库查询,常依赖临时表或内存表,效率低。
  • 子查询通常无法充分利用索引。

优化方法:

  • 优先使用 JOIN 查询。
  • JOIN 查询直接利用索引,加速读取并降低资源消耗。

示例:

sql">-- 子查询方式
SELECT name, department 
FROM student 
WHERE id IN (SELECT student_id FROM scores WHERE grade > 90);

-- JOIN 查询方式(更高效)
SELECT s.name, s.department 
FROM student s 
JOIN scores sc ON s.id = sc.student_id 
WHERE sc.grade > 90;

1.2.4. 提升 GROUP BY 的效率

问题:

  • 无索引时,GROUP BY 会对全表进行排序与分组,耗时较长。

优化方法:

  1. 创建索引:在 GROUP BY 的列上添加适当索引。
  2. 优化查询:避免子查询,尝试使用 JOIN 或 EXISTS。
  3. 限制结果集:通过 LIMIT 缩小返回范围。

效果对比:

  • 无索引:SELECT remarks FROM scores GROUP BY remarks; 查询耗时:4.096秒
  • 添加索引后:耗时缩短至 0.001秒。

1.2.5. 批量操作

问题:

  • 单条插入导致多次数据库交互,性能低下。

优化建议:

  • 使用批量操作,减少数据库交互次数。
  • 每批次数据量建议控制在 500 条以内,避免单次操作过大导致数据库响应缓慢。

示例:

java">// 单条插入
for (Record record : records) {
    jdbcTemplate.update("INSERT INTO table (col1, col2) VALUES (?, ?)", record.getCol1(), record.getCol2());
}

// 批量插入
String sql = "INSERT INTO table (col1, col2) VALUES (?, ?)";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
    public void setValues(PreparedStatement ps, int i) {
        Record record = records.get(i);
        ps.setString(1, record.getCol1());
        ps.setString(2, record.getCol2());
    }
    public int getBatchSize() {
        return records.size();
    }
});

1.2.6. 使用 LIMIT 优化查询

优势:

  1. 限制数据行数:避免查询超大量数据造成系统崩溃。
  2. 优化分页查询:减少不必要的全表扫描。
  3. 减轻网络负担:仅返回关心的数据行,提高传输效率。

分页示例:

sql">-- 查询第一页,每页 10 条数据
SELECT * FROM scores ORDER BY id LIMIT 10 OFFSET 0;

-- 查询第二页
SELECT * FROM scores ORDER BY id LIMIT 10 OFFSET 10;

注意:

  • 对百万级表,LIMIT 翻页会导致性能下降,推荐使用基于索引的优化方式:
sql">-- 基于索引的分页
SELECT * FROM scores WHERE id > (SELECT id FROM scores ORDER BY id LIMIT 1 OFFSET 1000) LIMIT 10;

总结

优化 SQL 查询的核心思想是减少不必要的资源消耗,提高数据库的利用效率。通过避免全表扫描、使用合适的索引、小表驱动大表、批量操作和分页优化,可以显著提升系统性能。


博客主页: 总是学不会.


http://www.niftyadmin.cn/n/5795489.html

相关文章

远程医疗:科技助力健康触手可及

随着信息技术与医疗领域的深度融合,远程医疗正逐步改变着人们获取医疗服务的方式。它跨越地理距离的障碍,将优质的医疗资源延伸到更广泛的区域。 对于患者来说,远程医疗极大地便利了就医过程。一些患有慢性疾病需要定期复诊的患者&#xff0c…

奇怪问题| Chrome 访问csdn 创作中心的时候报错: 服务超时,请稍后重试

Chrome 访问csdn 创作中心的时候报错: 服务超时,请稍后重试用无痕浏览器可以正常访问 关闭代理无效清缓存和Cookies无效。考虑无痕浏览器模式下插件不生效,尝试把chrome 插件也禁用,发现有效,是该扩展程序的缘故

Next.js v15- Metadata

概念 在 Web 开发中&#xff0c;Metadata提供有关网页的其他详细信息。元数据对访问页面的用户不可见。相反&#xff0c;它在幕后工作&#xff0c;嵌入到页面的 HTML 中&#xff0c;通常在<head>元素中。这些隐藏信息对于需要更好地了解您网页内容的搜索引擎和其他系统至…

我的2024年度总结

称着这个周末&#xff0c;写一篇2024年度总结&#xff0c;主要记录我过去一年的成长经历以及自己的一些收获。 过去一年的经历&#xff0c;可谓刻骨铭心&#xff0c;一个是24考研惨败&#xff0c;一个是毕设的准备&#xff0c;一个是省考的陪考&#xff0c;一个是找工作的焦虑…

C++_数据结构_详解二叉搜索树

✨✨ 欢迎大家来到小伞的大讲堂✨✨ &#x1f388;&#x1f388;养成好习惯&#xff0c;先赞后看哦~&#x1f388;&#x1f388; 所属专栏&#xff1a;C学习 小伞的主页&#xff1a;xiaosan_blog 1.二叉树搜索树的概念 二叉搜索树又称二叉排序树&#xff0c;它或者是一棵空树&a…

积分管理系统的设计与实现【源码+文档】

目  录 摘  要 Abstract 引 言 1 第一章 绪论 1.1 课题研究背景 1.2 系统实现意义 1.3 本文主要工作及总体结构 2 第二章 系统开发环境及相关技术 2.1环境技术概述 2.1.1 B/S结构 2.1.2 JSP 2.1.3 JavaBean 3 第三章 系统需求分析 3.1 系统具体实现目标 3…

物联网:全面概述、架构、应用、仿真工具、挑战和未来方向

中文论文标题&#xff1a;物联网&#xff1a;全面概述、架构、应用、仿真工具、挑战和未来方向 英文论文标题&#xff1a;Internet of Things: a comprehensive overview, architectures, applications, simulation tools, challenges and future directions 作者信息&#x…

STM32 高级 物联网通信之CAN通讯

目录 CAN通讯介绍 物理层 协议层 CAN的帧(报文)种类 1 数据帧(发送单元->接受单元) 2 远程帧(接受单元->发送单元) 3 错误帧(发送方发送数据错误会发送的状态帧) 4 过载帧(接收方放不下会发送到的状态帧) 5 帧间隔(状态) 数据帧介绍 远程帧介绍 C…