SQL优化思路

SQL查询与建表优化技巧
最新推荐文章于 2025-01-09 19:00:00 发布
原创 最新推荐文章于 2025-01-09 19:00:00 发布 · 235 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文介绍了一系列SQL查询优化方法，包括避免全表扫描、合理使用通配符、替换OR为UNION ALL等；同时提供了建表时的优化建议，如选择合适的字段类型和减少字段长度等。
查询优化：避免全表扫描
  1. 避免通配符紧随like出现，如LIKE ‘%ABC’
  2. 避免在WHERE子句中使用IS NULL
  3. 避免使用不等于：!= , <>
  4. 避免使用OR, 尽量使用UNION ALL
  5. 尽量用EXIST子查询代替IN子程序
  6. 避免只给出复合索引中的其中一个字段
  7. 避免WHERE 1=1这样的写法
  8. 避免在WHERE子句中对字段进行表达式操作，如WHERE num/2=100
  9. 避免在WHERE子句中对字段进行函数操作，如SUBSTR(name，1，2)=‘ab’
建表优化
  1. 尽可能使用数字型字段
  2. 尽量使用varchar代替char，节省存储空间
其他优化
  1. 除非查询所有字段，否则应避免使用SELECT *
【PostgreSQL】PostgreSQL慢SQL优化思路
tttzzzqqq2018的博客
04-03 1051
【代码】【PostgreSQL】PostgreSQL慢SQL优化思路。
关系型数据库SQL优化的思路与技巧
qq_32389231的博客
09-14 369
SQL优化 1、介绍 随着互联网大数据的兴起，随着数据库中数据的增加，SQL语句的优劣对系统的响应速度的影响越来越明显，SQL优化也成为每一个数据相关工作人员的必备技能。 在多数情况下，使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。 2、常见优化规则 2.1 表
SQL优化的思路和步骤
weixin_68954386的博客
05-23 318
in查询在进行cost代价计算时(代价=元组数 *IO平均值)，是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MVSOL设置了个临界值(eg_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参计算了。对于大分页的场暴，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式，一个是把上一次的最后一条数据，也即上面的c传过来，然后做"c< xxx”处理，但是这种一般需要改接口协议，并不一定可行。在索引上，避免使用NOT、!
sql优化思路
xiao玖的博客
09-09 400
SQL优化 1、查询语句中不要使用select * 2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代 3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代 4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好) 5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 6、应尽量避免在
SQL优化的基本思路
mischen520的博客
03-29 3518
（1）建立物化视图或尽可能减少多表查询。 （2）以不相干子查询替代相干子查询。 （3）只检索需要的列。 （4）用带in的条件子句等价替换or子句。 （5）经常提交commit，以尽早释放锁。 （6）避免嵌套的游标（Cursor）和多重循环等。 ...
MYSQL SQL优化思路和方法
陈泽钦的博客
05-10 1623
Mysql SQL优化思路和优化方法......
sql优化思路.pptx
05-24
SQL优化是提升数据库性能的关键步骤，尤其是在大数据量的场景下，高效的SQL查询能显著提高系统响应速度。在MySQL中，优化SQL主要关注I/O成本和CPU成本，这两者是影响执行效率的核心因素。 I/O成本涉及到数据从磁盘...
SQL优化思路+经典案例分析
qq_66627105的博客
01-09 1385
SQL调优这块呢，大厂面试必问的。最近金九银十嘛，所以整理了SQL的调优思路，并且附几个经典案例分析。
Mysql-EXPLAIN分析sql语句-项目中慢SQL优化思路和示例
axiao321123的博客
09-18 1590
项目中利用EXPLAIN分析sql语句-优化慢SQL优化思路和示例
SQL优化的思路及基本原则（mysql）
莫失莫忘-云姐
09-28 5986
SQL优化的思路：  1.优化更需要优化的sql；  2.定位优化对象的性能瓶颈：优化前需了解查询的瓶颈是IO还是CPU，可通过PROFILING很容易定位查询的瓶颈。  3.明确优化目标；  4.从Explain入手；  5.多使用profile； ​ SQL优化的基本原则：  1.永远用小结果集驱动大结果集；     From子句中sq
SQL优化思路整理
点点的博客
11-08 813
sql优化思路
SQL优化的一些思路
c82525的博客
03-14 640
SQL优化的一些思路
SQL性能优化的几个思路
wxl847466025的博客
10-11 6167
　　其实SQL性能优化的本质就是减少运算次数、减少内存消耗、涉及尽量少的数据，建立索引、优化表格结构等手法均为此目的。 　　1、建立索引，目的是避免全表扫描。索引一般建立在WHERE、GROUP、JOIN经常使用的字段上，字段需要拥有这样的特点：数据行比较多，至少要过10万；字段的值项较多，至少要超过10个，比如某个字段的值只可能是1或0，建立索引就是浪费。另外索引的建立应秉持最少原则，一张数...
Mysql优化方案
YuanZhea的博客
09-19 344
这篇文章讲述了在大家平时学习和工作中，遇到的80% 的sql优化方案。 一、创建数据表 CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(20) NOT NULL DEFAULT '0' C...
sql调优思路整理
qq_32218473的博客
10-10 335
1、开启mysql慢日志 修改配置文件 Windows：配置文件为 my.ini，一般在 MySQL 安装目录下或者 c:\Windows 下。 Linux：配置文件为 my.cnf ，一般在 /etc 下。 在 my.ini 增加下面代码: [mysqld] long_query_time=2 #5.5以前版本配置如下选项 log-slow-queries="mysql_s...
聊聊SQL优化的基础思路
Enmotech的博客
05-24 412
点击上方"蓝字"关注我们，享更多干货！SQL优化是Oracle数据库中比较难的部分，需要对Oracle数据库具备非常扎实的理论基础。但是在刚开始接触时，往往不能很好地将理论...
慢sql优化思路
最新发布
11-19
慢 SQL 优化是数据库性能调优的核心环节。一个高效的 SQL 不仅能提升响应速度，还能降低服务器负载、减少锁竞争和资源消耗。 以下是 **系统化、可落地的慢 SQL 优化思路**，从“发现问题”到“彻底解决”，并附上代码示例与执行命令。 --- ## ✅ 一、慢 SQL 优化的整体思路（6 步法） ```text 1. 发现 → 2. 定位 → 3. 分析 → 4. 优化 → 5. 验证 → 6. 预防 ``` 我们逐层展开： --- ## ✅ 第一步：发现慢 SQL（如何知道有慢查询？） ### 🔹 方法 1：开启 MySQL 慢查询日志 ```ini # my.cnf 配置 [mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 # 超过1秒记录 log_queries_not_using_indexes = ON # 记录未用索引的SQL ``` 动态启用（无需重启）： ```sql SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; ``` ### 🔹 方法 2：使用 `pt-query-digest` 分析日志（推荐工具） ```bash pt-query-digest /var/log/mysql/slow.log > report.txt ``` 输出内容包括： - 最耗时 Top 10 SQL - 执行次数最多但总时间长的 SQL - 是否存在全表扫描、临时表、文件排序等问题 > 📌 推荐每天定时生成报告，自动告警异常 SQL --- ## ✅ 第二步：定位问题 SQL（哪条 SQL 最需要优化？） 通过 `pt-query-digest` 报告找到以下特征的 SQL： | 特征 | 说明 | |------|------| | `Query_time` 平均 > 1s | 用户已明显感知卡顿 | | `Rows_examined` > 1万 | 扫描行数过多，可能缺索引 | | `Executes` 次数高 × 单次慢 | 总耗时巨大，优先级最高 | 👉 示例： ```text # Query 1: 1.2k QPS, Avg time 1.8s, Examines 100K rows select * from orders where user_id=123 and status='PAID' ``` → 这类高频+高延迟 SQL 是首要优化目标！ --- ## ✅ 第三步：分析执行计划（为什么慢？） 使用 `EXPLAIN` 查看 SQL 执行路径： ```sql EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'PAID'; ``` 重点关注字段： | 字段 | 合理值 | 异常表现 | 含义 | |------|--------|----------|-------| | `type` | `ref`, `range`, `index` | `ALL` ❌ | 访问类型，`ALL`=全表扫描 | | `key` | 非 NULL | `NULL` ❌ | 实际使用的索引 | | `rows` | 越小越好 | >10000 ❌ | 预估扫描行数 | | `Extra` | — | `Using filesort`, `Using temporary` ❌ | 额外操作，影响性能 | --- ## ✅ 第四步：常见问题及优化策略 ### 🟢 优化策略 1：添加合适索引 #### 场景：WHERE 条件字段无索引 ❌ 慢： ```sql SELECT * FROM orders WHERE user_id = 123; -- type=ALL, key=NULL → 全表扫描 ``` ✅ 快： ```sql ALTER TABLE orders ADD INDEX idx_user_id (user_id); ``` #### 复合条件 → 建复合索引 ```sql SELECT * FROM orders WHERE user_id = 123 AND status = 'PAID'; ``` ✅ 创建联合索引： ```sql ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); ``` > ⚠️ 注意最左前缀原则：`WHERE status='PAID' AND user_id=123` 也能命中该索引 --- ### 🟢 优化策略 2：使用覆盖索引减少回表 当查询字段都在索引中时，无需回到主键查数据。 ❌ 回表多： ```sql -- 查询了 create_time，但索引只有 (user_id, status) SELECT create_time FROM orders WHERE user_id = 123; ``` ✅ 改为覆盖索引： ```sql ALTER TABLE orders ADD INDEX idx_covering (user_id, status, create_time); ``` 此时 `Extra=Using index` → 直接从索引获取数据，不回表！ --- ### 🟢 优化策略 3：避免 `Using filesort`（文件排序） #### 问题：ORDER BY 字段无索引 ❌ 慢： ```sql SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC; -- Extra: Using filesort ``` ✅ 解决：创建 `(user_id, create_time)` 联合索引 ```sql ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time); ``` 这样既能快速过滤用户订单，又能按时间排序，避免额外排序开销。 --- ### 🟢 优化策略 4：深分页优化（LIMIT M,N） #### 问题：跳过大量行 ❌ 慢： ```sql SELECT * FROM orders ORDER BY id LIMIT 10000, 20; -- 跳过1万行，效率极低 ``` ✅ 方案 1：基于主键继续查询（推荐） ```sql -- 上一页最后一条记录 id=10000 SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20; ``` ✅ 方案 2：延迟关联（适用于非主键排序） ```sql SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time LIMIT 10000, 20 ) t ON o.id = t.id; ``` 只对 ID 排序分页，再关联原表，大幅减少扫描。 --- ### 🟢 优化策略 5：避免函数操作字段（导致索引失效） ❌ 慢： ```sql SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01'; -- 函数导致索引无法使用 ``` ✅ 快： ```sql SELECT * FROM orders WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'; ``` 让字段独立出现在左边，才能走索引。 --- ### 🟢 优化策略 6：禁止 `SELECT *` ❌ 浪费带宽、增加回表概率、影响覆盖索引命中 ```sql SELECT * FROM large_table WHERE condition = 'xxx'; ``` ✅ 明确指定字段： ```sql SELECT id, name, email FROM users WHERE active = 1; ``` 尤其在有索引的情况下，可以更好地利用覆盖索引。 --- ### 🟢 优化策略 7：防止 N+1 查询（应用层问题） ❌ Java 示例： ```java List<User> users = userDao.findAll(); // 1次 for (User u : users) { List<Order> orders = orderDao.findByUserId(u.getId()); // N次 } ``` ✅ 改为批量查询： ```sql SELECT * FROM orders WHERE user_id IN (1,2,3,...); ``` 或使用 JOIN 一次性查出所有关联数据。 --- ## ✅ 第五步：验证优化效果 优化后必须验证是否真正变快！ ### 方法 1：对比执行时间 ```sql -- 优化前 SELECT SQL_NO_CACHE * FROM orders WHERE user_id = 123; -- 添加索引后 ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- 再次执行，观察时间变化 SELECT SQL_NO_CACHE * FROM orders WHERE user_id = 123; ``` > 使用 `SQL_NO_CACHE` 避免查询缓存干扰测试结果 ### 方法 2：再次运行 `EXPLAIN` 确认： - `type` 从 `ALL` 变成 `ref` - `rows` 从 10万 → 100 - `Extra` 不再出现 `Using filesort` --- ## ✅ 第六步：建立预防机制（防患于未然） | 措施 | 说明 | |------|------| | ✅ 开发规范 | 禁止 `SELECT *`、强制审核上线 SQL | | ✅ APM 监控 | 使用 Druid、SkyWalking 实时监控 SQL 耗时 | | ✅ 慢 SQL 告警 | 设置阈值（如 >500ms）自动通知 | | ✅ 定期巡检 | 每周分析慢日志，持续优化 | | ✅ 使用 ORM 注意事项 | MyBatis/Hibernate 避免自动生成低效 SQL | --- ## ✅ 附加技巧：SQL 重写建议 | 原写法 | 优化写法 | 原因 | |--------|-----------|--------| | `LIKE '%abc%'` | 尽量不用，或用全文索引 | 前导通配符无法用索引 | | `IN (子查询)` | 改为 `JOIN` 或缓存结果 | 子查询可能不走索引 | | `OR` 条件 | 改为 `UNION` | OR 容易导致索引失效 | | `NOT IN` / `!=` | 改为 `LEFT JOIN IS NULL` | 否定条件难优化 | --- ## ✅ 总结：慢 SQL 优化 Checklist ✅ 每次遇到慢 SQL，请按此清单排查： | 检查项 | 是否完成 | |--------|----------| | ☐ 是否开启了慢查询日志？ | □ | | ☐ 是否用 `pt-query-digest` 分析了 Top 慢 SQL？ | □ | | ☐ 是否使用 `EXPLAIN` 查看了执行计划？ | □ | | ☐ `type` 是否为 `ALL`？需加索引 | □ | | ☐ `Extra` 是否有 `Using filesort` 或 `Using temporary`？ | □ | | ☐ 是否存在深分页 `LIMIT 10000,20`？ | □ | | ☐ 是否用了 `SELECT *`？ | □ | | ☐ WHERE 中是否有函数操作字段？ | □ | | ☐ 是否存在 N+1 查询？ | □ | | ☐ 优化后是否验证了效果？ | □ | > 💡 **黄金法则：不要猜！要用工具看真实数据** --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值