编程 mysql 优化指南

2024-11-18 21:01:24 +0800 CST views 568

SQL 优化指南

在数据库设计和查询优化中,有许多策略可以提升性能、减少资源消耗。以下是一些关键的优化方法和建议。

一、查询SQL尽量不要使用 SELECT *

  1. 反例SELECT * FROM users;
  2. 正例SELECT id, name, email FROM users;
  3. 理由
    • 节省资源、减少网络开销。
    • 可能用到覆盖索引,减少回表,提高查询效率。

注意:为节省时间,下面的样例字段都用 * 代替了。

二、避免在 WHERE 子句中使用 OR 来连接条件

  1. 反例
    SELECT * FROM users WHERE id = 1 OR salary = 1000;
    
  2. 正例
    • 使用 UNION ALL
    • 分开两条 SQL 写
  3. 理由
    • 使用 OR 可能会使索引失效,从而导致全表扫描。
    • 当某个条件无法使用索引时,可能会导致整个查询过程都需要全表扫描,增加查询成本。

三、尽量使用数值替代字符串类型

  1. 正例
    • 主键(id):使用数值类型 inttinyint 优先。
    • 性别(sex):0 代表女,1 代表男。
  2. 理由
    • 数字比较效率更高,仅需一次比较,而字符串需要逐字符比较。
    • 数字型数据在查询和连接时性能更好,并且存储开销更小。

四、使用 VARCHAR 代替 CHAR

  1. 反例CHAR(10)
  2. 正例VARCHAR(10)
  3. 理由
    • VARCHAR 变长字段按实际长度存储,节省存储空间。
    • CHAR 按声明大小存储,不足补空格,查询效率可能较低。

五、CHARVARCHAR2 的区别

  1. 区别
    • CHAR 的长度固定,VARCHAR2 的长度可变。
    • CHARVARCHAR2 效率稍高,但会占用更多空间。
  2. 使用建议
    • CHAR 适合长度固定且不常修改的字段。
    • VARCHAR2 更适合长度可变的字段,但可能引发“行迁移”问题,导致额外的 I/O。

六、WHERE 中使用默认值代替 NULL

  1. 反例SELECT * FROM users WHERE status IS NULL;
  2. 正例SELECT * FROM users WHERE status = 0;
  3. 理由
    • 避免使用 IS NULLIS NOT NULL 导致索引失效。
    • 使用默认值可以使索引更有效,且语义更加清晰。

七、避免在 WHERE 子句中使用 !=<> 操作符

  1. 反例SELECT * FROM users WHERE status != 1;
  2. 理由
    • 使用 !=<> 可能导致索引失效,从而引发全表扫描。
    • 如果必须使用,请考虑业务优先。

八、优先使用 INNER JOIN 而非 LEFT JOINRIGHT JOIN

  1. 理由
    • INNER JOIN 只返回完全匹配的结果,通常性能更好。
    • LEFT JOINRIGHT JOIN 会返回一侧表的所有行,可能增加返回结果的行数,降低查询性能。

九、提高 GROUP BY 语句的效率

  1. 反例:先分组,再过滤
  2. 正例:先过滤,后分组
  3. 理由
    • 先过滤掉不必要的记录,减少 GROUP BY 处理的数据量,提高效率。

十、清空表时优先使用 TRUNCATE

  1. 理由
    • TRUNCATEDELETE 快,且消耗的系统和事务日志资源少。
    • TRUNCATE 释放存储页,记录更少的日志信息。

十一、操作 DELETEUPDATE 语句时加 LIMIT

  1. 理由
    • 降低误操作的风险,减少删除或更新数据的影响范围。
    • 避免长事务,提高系统并发能力。

十二、UNION 操作符优化

  1. 建议
    • 使用 UNION ALL 代替 UNION,因为 UNION 会去重,导致额外的排序和计算开销。

十三、批量插入性能提升

  1. 建议
    • 使用批量提交来减少事务开销,提升插入性能。

十四、表连接与索引数量控制

  1. 建议
    • 表连接数控制在 5 个以内,避免复杂查询。
    • 索引数量控制在 5 个以内,平衡查询效率与插入/更新效率。

十五、避免在索引列上使用内置函数

  1. 反例SELECT * FROM users WHERE YEAR(created_at) = 2023;
  2. 理由
    • 使用内置函数会导致索引失效,增加查询成本。

十六、组合索引与最左特性

  1. 建议
    • 创建符合最左特性的组合索引,优化查询性能。

十七、优化 LIKE 语句

  1. 反例SELECT * FROM users WHERE name LIKE '%John%';
  2. 正例SELECT * FROM users WHERE name LIKE 'John%';
  3. 理由
    • 右模糊查询(LIKE 'John%')可以使用索引,而左模糊查询(LIKE '%John')无法使用索引。

十八、使用 EXPLAIN 分析 SQL 执行计划

  1. 理由
    • 通过 EXPLAIN 查看 SQL 的执行计划,可以识别性能瓶颈,进行优化。

十九、其他优化建议

  1. 设计表时添加注释:提高表和字段的可读性。
  2. SQL 书写格式统一:提高代码的可维护性。
  3. 重要数据操作前备份:防止数据丢失。
  4. 使用 EXISTS 代替 IN:提高查询性能。
  5. 避免 WHERE 子句中的隐式类型转换:避免索引失效。
  6. 避免在 WHERE 子句中使用表达式:确保索引有效性。
  7. 减少大事务操作:提高系统并发能力。
  8. 使用 InnoDB 存储引擎:支持事务,性能优良。
  9. 避免使用游标:游标效率低,应尽量改写为批量操作。

通过遵循这些最佳实践,可以有效提升 SQL 查询性能,减少资源消耗,确保系统的高效稳定运行。

复制全文 生成海报 数据库 性能优化 编程技巧

推荐文章

jQuery中向DOM添加元素的多种方法
2024-11-18 23:19:46 +0800 CST
Nginx 实操指南:从入门到精通
2024-11-19 04:16:19 +0800 CST
PHP 8.4 中的新数组函数
2024-11-19 08:33:52 +0800 CST
html一些比较人使用的技巧和代码
2024-11-17 05:05:01 +0800 CST
deepcopy一个Go语言的深拷贝工具库
2024-11-18 18:17:40 +0800 CST
Vue3如何执行响应式数据绑定?
2024-11-18 12:31:22 +0800 CST
从Go开发者的视角看Rust
2024-11-18 11:49:49 +0800 CST
纯CSS实现3D云动画效果
2024-11-18 18:48:05 +0800 CST
html折叠登陆表单
2024-11-18 19:51:14 +0800 CST
10个几乎无人使用的罕见HTML标签
2024-11-18 21:44:46 +0800 CST
Vue3中如何处理组件间的动画?
2024-11-17 04:54:49 +0800 CST
Flet 构建跨平台应用的 Python 框架
2025-03-21 08:40:53 +0800 CST
MySQL死锁 - 更新插入导致死锁
2024-11-19 05:53:50 +0800 CST
使用Vue 3和Axios进行API数据交互
2024-11-18 22:31:21 +0800 CST
PHP如何进行MySQL数据备份?
2024-11-18 20:40:25 +0800 CST
H5保险购买与投诉意见
2024-11-19 03:48:35 +0800 CST
微信内弹出提示外部浏览器打开
2024-11-18 19:26:44 +0800 CST
程序员茄子在线接单