在数据库运维与开发中，因为对底层原理和实际场景理解不足，我们常常陷入SQL性能优化的各类误区。下面一起来看看。

 

一、索引与查询相关误区

 

1、索引越多越好

 

误区：认为给表中所有字段都建索引，查询就能更快。

 

问题：索引会增加写操作（插入、更新、删除）的开销（需要维护索引结构），还会占用额外存储空间。过多索引可能导致数据库在选择索引时出错，反而降低查询效率。

 

正确做法：只给频繁用于查询条件、排序、连接的字段建索引，定期清理无用索引。

 

注：索引三星原则（Three-Star System）

 

1）WHERE条件匹配索引列；

 

2）ORDER BY/GROUP BY顺序与索引一致；

 

3）SELECT字段被索引覆盖。

 

2、盲目使用 SELECT *

 

误区：图方便用 SELECT 查询所有字段，觉得对性能影响不大。

 

问题：会读取不需要的字段，增加磁盘I/O和网络传输量；若表中包含大字段（如：TEXT、BLOB），还可能导致缓存失效，降低整体查询效率。

 

正确做法：明确指定需要的字段，只获取必要数据。

 

3、忽视 JOIN 的表顺序

 

误区：认为 JOIN 时表的顺序不影响性能，随便写。

 

问题：在某些数据库（如：早期MySQL版本）中，JOIN 优化器对表顺序处理不够智能，若将小表放在后面，可能导致大表先被扫描，增加匹配次数。

 

注：MySQL 8.0+ 优化器已支持自动重排序JOIN表（基于统计信息），手动指定顺序的必要性降低。但以下情况仍需注意：复杂JOIN（如5张表以上）时优化器可能失效；使用 STRAIGHT_JOIN强制指定顺序的特定优化场景。

 

正确做法：尽量让小表作为驱动表（放在 JOIN 左侧），减少外层循环的次数。

 

3、用 OFFSET 实现分页

 

误区：使用 LIMIT 10000, 10 实现深分页，认为简单直接。

 

问题：OFFSET 需扫描并跳过前N条数据，深度分页时性能极低。

 

正确做法：利用索引排序，通过条件定位起始位置，如：WHERE id > 100000 LIMIT 10（假设id有序且有索引）。

 

4、滥用子查询

 

误区：大量使用子查询，认为逻辑清晰，不影响性能。

 

问题：某些数据库对复杂子查询的优化较差，可能导致多次扫描表或临时表创建，增加开销；嵌套层级过深还会降低可读性。

 

正确做法：简单子查询可保留，复杂子查询尽量改用JOIN改写，利用JOIN优化器提升效率。

 

1）LIKE模糊查询索引失效

 

误区：WHERE name LIKE '%xxx%' 认为前缀模糊查询可利用索引。

 

问题：前导通配符（%开头）导致索引失效，触发全表扫描。

 

正确做法：

 

• 改用后缀模糊匹配（LIKE 'xxx%'）；

 

• 对全文搜索需求使用专用引擎（如：Elasticsearch）。

 

2）函数转换导致索引失效

 

误区：对索引字段使用函数（如：WHERE YEAR(create_time)=2023）。

 

问题：查询时需逐行计算函数值，索引失效。

 

正确做法：改写为范围查询——WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

二、数据库设计与配置误区

 

1、忽略数据类型的选择

 

误区：选择数据类型时不严谨，如用INT存储手机号（实际可存字符串），或用 VARCHAR(255) 存储所有字符串。

 

问题：不合适的数据类型会增加存储空间和I/O开销，还可能影响索引效率（如：字符串未指定长度导致索引过大）。

 

正确做法：根据数据特性选择最小且合适的类型，如:手机号用CHAR(11)，状态值用TINYINT。

 

2、忽视表结构设计范式

 

误区：过度追求范式化（如：第三范式），认为这样最规范。

 

问题：过度范式化会导致查询时频繁JOIN，增加复杂度；反范式化虽减少JOIN但可能引发数据冗余和更新异常。

 

正确做法：根据业务读写比例平衡范式化与反范式化，对高频查询场景适当冗余字段。

 

3、过度追求范式化设计

 

误区：机械遵循数据库范式（如：3NF），将所有字段拆分到独立表。

 

问题：过度联表查询增加优化器复杂度，可能引发嵌套循环或临时表，尤其在高并发时成为瓶颈。

 

正确做法：适度反范式化，对高频访问的关联字段冗余存储（如：订单表冗余用户名），用空间换时间。

 

分区表选择细化：按时间范围分区（如：日志表）外，哈希分区（如：商户ID）可均匀分散I/O压力，但需确保查询条件命中分区键。

 

4、忽视分区表的使用

 

误区：对大表（如：日志表）不分区，认为索引足够。

 

问题：单表数据量过大时，索引维护和查询效率会显著下降。

 

正确做法：按时间或范围分区，如：按月分区日志表，提升查询和维护效率。哈希分区（如：商户ID）分散I/O压力。

 

5、分区表使用不当

 

误区：为所有大表启用分区，认为必然提升查询性能。

 

问题：分区键选择错误导致查询无法剪枝（Partition Pruning），反而因跨分区扫描更慢；分区过多增加元数据管理开销。

 

正确做法：仅对有明显冷热或范围查询的表分区（如：按时间），确保查询条件能命中分区键。

 

注：分区表并非适用于所有大表，需同时满足：查询条件必须包含分区键（否则触发全分区扫描）；单分区数据量仍可控（否则分区内索引效率下降）。示例：

 

 

6、错误使用NULL值

 

误区：默认用NULL表示“无数据”，未考虑其对索引和查询的影响。

 

问题：WHERE field IS NULL可能无法利用索引（取决于数据库实现）；NULL值不计入聚合函数（如：COUNT(field)）。

 

正确做法：用默认值替代NULL（如：空字符串、0），或明确处理NULL场景（如：COALESCE()）。

 

注：MySQL的InnoDB存储NULL值仅占用1 bit，并非存储开销主因。核心问题：COUNT(field)忽略NULL导致统计偏差；WHERE field = value不会匹配到 NULL（需显式 IS NULL）。补充解决方案：

 

 

7、忽视数据库参数调优

 

误区：使用默认数据库参数（如：innodb_buffer_pool_size=128M），认为够用。

 

问题：参数未根据硬件配置调整，可能导致内存不足或I/O瓶颈。

 

正确做法：根据服务器内存、CPU、磁盘类型调整关键参数（如：缓冲池大小、连接数）。

 

8、忽略硬件特性适配

 

误区：在SSD硬盘服务器沿用HDD时代的优化策略（如：盲目追求随机写合并）。

 

问题：SSD随机读写性能远高于HDD，优化重点需转向并行度和内存利用；NVMe盘更需调整I/O调度策略。

 

正确做法：根据存储介质调整配置（如：SSD上增大innodb_io_capacity），利用多核并行查询（如：MySQL 8.0的parallel_read_threads）。

 

9、忽略连接池配置

 

误区：使用默认连接池参数（如：连接数=10），认为够用。

 

问题：连接数不足会导致请求排队，过多则消耗数据库资源；未设置超时可能引发连接泄漏。

 

正确做法：根据业务并发量调整连接池大小，配置合理的超时和回收策略。

 

10、忽略连接池预热

 

误区：应用启动后直接承载流量，未预热数据库连接池。

 

问题：冷启动时连接池无缓存，首批查询需硬解析执行计划并加载数据到Buffer Pool，响应延迟高。

 

正确做法：

 

• 启动时预热连接池，执行高频SQL模板加载缓存（如：SELECT 1不足以预热真实查询）。
• 执行真实高频SQL模板（如：EXPLAIN ANALYZE），提前加载执行计划到缓存，而非仅执行 SELECT 1。

三、事务与锁相关误区

 

1、忽视事务隔离级别

 

误区：默认使用最高隔离级别（如：SERIALIZABLE），认为这样最安全。

 

问题：高隔离级别会增加锁竞争和阻塞概率，降低并发性能。

 

正确做法：根据业务需求选择合适隔离级别（如：READ COMMITTED），必要时配合乐观锁。

 

2、忽视锁粒度控制

 

误区：默认使用行级锁，认为粒度越细越好。

 

问题：行级锁在高并发更新时可能导致锁升级（如：MySQL的间隙锁），反而增加死锁风险。

 

正确做法：根据场景选择锁策略（如：乐观锁、分布式锁），避免长事务。

 

3、忽略长事务的影响

 

误区：认为事务短时间未提交无影响，如：批量处理时未及时提交。

 

问题：长事务会占用锁资源，阻塞其他操作，且可能导致undo log膨胀。

 

正确做法：拆分大事务为小批次，及时提交；监控事务执行时长。

 

4、忽视死锁监控

 

误区：仅关注慢查询，忽略数据库死锁日志。

 

问题：死锁虽自动回滚，但频发会拖累吞吐量，且可能暴露业务逻辑缺陷（如：更新顺序不一致）。

 

正确做法：监控死锁频率，优化事务中操作顺序，对高冲突场景使用乐观锁或队列削峰。

 

5、不合理使用外键约束

 

误区：大量使用外键保证数据一致性，认为最可靠。

 

问题：外键会增加插入/更新的开销，且高并发下可能引发死锁。

 

正确做法：核心一致性用外键，非核心场景改用应用层逻辑或定时校验。

 

非核心数据一致性场景改用：应用层逻辑校验 + 异步对账任务（实现最终一致性）。

四、数据库功能使用误区

 

1、过度依赖数据库缓存

 

误区：认为数据库缓存（如：MySQL的InnoDB Buffer Pool）能解决所有性能问题。

 

问题：缓存命中率受数据量和访问模式影响，若数据远超缓存容量或访问随机性强，缓存效果极低。

 

正确做法：结合应用层缓存（如：Redis），对热点数据做多级缓存。

 

2、不合理使用存储过程

 

误区：将复杂业务逻辑全部放入存储过程，认为减少网络交互就能提升性能。

 

问题：存储过程调试困难，且数据库层逻辑膨胀会导致CPU和内存压力集中。

 

正确做法：仅将简单、高频的聚合操作放入存储过程，复杂逻辑交由应用层处理。

 

3、误用存储过程

 

误区：用存储过程封装所有业务逻辑，认为减少网络交互必能提速。

 

问题：复杂存储过程占用数据库连接时间长，阻塞简单查询；调试困难，版本管理复杂。

 

正确做法：仅将原子性操作封装为存储过程（如：支付扣款），避免在数据库中实现业务编排。

 

4、不合理使用触发器

 

误区：用触发器实现业务逻辑（如：自动更新关联表），认为能减少应用代码。

 

问题：触发器隐式执行，难以调试和追踪，且可能因级联操作导致性能雪崩。

 

正确做法：避免复杂触发器，将逻辑显式写在应用层或事务中。

 

5、滥用视图嵌套

 

误区：多层嵌套视图（如：View1调用View2再调用View3）简化查询逻辑。

 

问题：优化器难以合并嵌套逻辑，可能逐层物化中间结果，引发性能雪崩。

 

正确做法：扁平化视图逻辑，或改用CTE（Common Table Expressions）提升可读性与可优化性。

 

6、频繁使用临时表

 

误区：在复杂查询中频繁使用临时表存储中间结果。

 

问题：临时表创建和销毁消耗资源，若数据量大可能导致磁盘临时表（而非内存临时表），性能急剧下降。

 

正确做法：优化查询逻辑，减少临时表使用；必要时为临时表指定内存引擎（如：MEMORY）。

 

五、运维与监控误区

 

1、忽略慢查询日志分析

 

误区：只关注明显卡顿的查询，忽视慢查询日志的定期分析。

 

问题：部分“亚健康”查询（单次执行不慢但高频调用）可能累积消耗大量资源。

 

正确做法：开启慢查询日志，结合工具（如：pt-query-digest）定期分析并优化。

 

2、忽视数据库版本升级

 

误区：认为稳定版本无需升级，避免风险。

 

问题：旧版本可能存在性能缺陷（如：MySQL 5.6的优化器弱于8.0），且缺乏新特性（如：CTE、窗口函数）。

 

正确做法：定期评估新版本性能改进，在测试环境验证后升级。

 

3、忽略统计信息更新

 

误区：认为数据库会自动维护统计信息，无需手动干预。

 

问题：统计信息过时会导致优化器选择错误执行计划（如：误判索引选择性）。

 

正确做法：定期执行 ANALYZE TABLE（MySQL）或类似命令更新统计信息。

 

4、未优化统计信息收集策略

 

误区：依赖数据库自动更新统计信息，未主动干预。

 

问题：自动更新可能不及时或采样率不足（如：超大表仅采样10%），导致优化器误判数据分布。

 

正确做法：对数据变化剧烈的表手动定时更新统计信息（如：ANALYZE TABLE），并调整采样率。

 

5、忽视数据库监控指标

 

误区：仅关注SQL响应时间，忽略数据库底层指标（如：QPS、TPS、锁等待、缓冲池命中率）。

 

问题：单条SQL优化可能无法解决系统性瓶颈（如：连接池耗尽、锁竞争），需综合监控定位。

 

正确做法：建立全面的数据库监控体系，结合Prometheus、Grafana等工具分析性能趋势。
 

六、场景与任务适配误区

 

1、用数据库做计算密集型任务

 

误区：在SQL中实现复杂计算（如：字符串处理、数学运算），认为减少网络传输。

 

问题：数据库CPU资源有限，计算密集型任务会阻塞其他查询。

 

正确做法：将复杂计算卸载到应用层或专用计算服务（如：Spark）。

 

 

2、不区分OLTP和OLAP场景

 

误区：用同一套数据库处理事务（OLTP）和分析（OLAP）请求。

 

问题：OLAP查询（如：全表聚合）会占用大量资源，影响OLTP事务响应。

 

正确做法：分离读写库，或将分析任务迁移至专用数据仓库（如：ClickHouse）。

 

3、HTAP混合负载隔离缺失

 

误区：OLAP查询直接运行在OLTP主库。

 

问题：分析型查询消耗大量CPU/内存，阻塞高并发事务。

 

正确做法：

 

• 使用读写分离中间件；

 

• 构建专用分析副本（如：MySQL Group Replication只读节点）。

 

4、分布式数据库分片键设计不当

 

误区：未按业务关联性设计分片键（如：MongoDB、TiDB）。

 

问题：跨节点JOIN导致网络开销剧增。

 

正确做法：

 

• 冗余高频查询字段至关联表；
• 使用全局二级索引（GSI）避免跨分片扫描。

 

5、用数据库存储大文件

 

误区：将图片、视频等大文件直接存入数据库的BLOB字段。

 

问题：数据库I/O和内存压力剧增，备份和恢复效率低下。

 

正确做法：文件存储于对象存储（如：S3），数据库仅保存文件路径或元数据。

 

6、用数据库做消息队列

 

误区：用表模拟消息队列（如：轮询状态字段），认为简单可靠。

 

问题：频繁轮询导致数据库压力增大，且消息可靠性难以保证。

 

正确做法：使用专用消息队列（如：Kafka、RabbitMQ）解耦异步任务。

 

7、忽略冷热数据分离

 

误区：将历史数据与活跃数据存储在同一表中。

 

问题：查询时需扫描大量无用数据，索引效率下降。

 

正确做法：按时间归档冷数据，或使用分区表自动管理冷热数据。

 

注：补充技术方案

 

• MySQL 8.0+ 支持异步读取历史数据（SELECT /*+ SET_VAR(use_secondary_engine=ON) */ ...）；

 

• 结合分区表 + 对象存储（如AWS S3）实现自动分层存储。

 

8、高峰期执行全量备份

 

误区：认为备份是后台任务，不影响线上性能。

 

问题：全量备份（如：mysqldump）会锁表或增加I/O压力，高峰期执行可能导致业务卡顿。

 

正确做法：选择低峰期执行备份，使用热备工具（如：Percona XtraBackup）减少锁影响。

 

9、忽视批处理的重要性

 

误区：逐条执行大量数据操作（如：循环插入1000行），认为逻辑简单可靠。

 

问题：频繁网络交互和事务提交导致巨大开销（如：磁盘I/O、日志写入），性能呈线性下降。

 

正确做法：使用批处理（如：INSERT INTO ..、VALUES (...), (...), ...）或批量提交，减少事务次数和网络往返。

 

七、SQL性能优化原则

 

1）理解代价模型

 

优化器基于CPU、I/O、内存等成本估算执行计划，需通过统计信息让其“看清”数据。

 

2）平衡读写负载

 

写密集场景避免过多索引，读密集场景可适当牺牲写性能。

 

3）怀疑经验主义

 

数据库版本升级或数据量级变化后，历史优化策略可能失效，需重新验证。

 

4）关注边际效应

 

单个优化在微秒级提升无意义，聚焦高频或高延迟查询。

 

5）全链路视角

 

从设计（数据类型/范式）、开发（SQL编写）、运维（参数/监控）闭环优化。

 

特别注意：局部优化 ≠ 全局最优

 

• 示例1：索引提速查询但降低写入 → 需权衡读写比例。
• 示例2：分库分表提升查询性能，但引入分布式事务成本 → 需量化拆分收益是否覆盖复杂度代价。

 

一句话总结：基于业务场景量化分析（EXPLAIN ANALYZE + 真实负载压测）。

 

八、新技术演进下的优化更新

 

 

1、Keyset分页 vs OFFSET（深分页优化）

 

 

注意：需主键连续，若数据有删除，改用 WHERE id > last_id AND create_time > 'xxx'

 

2、函数索引（解决字段计算失效）

 

 

3、PostgreSQL WITH TIES（精准分页）

 

 

当第100名有相同金额时，返回所有并列记录，避免漏数据

 

4、CTE物化优化子查询（PostgreSQL）

 

 

九、各数据库适配建议

 

数据库	推荐优化策略
MySQL	Keyset分页 + 函数索引 + 并行查询（8.0+）
PgSQL	CTE物化 + WITH TIES分页 + 并行哈希聚合
分布式	全局二级索引（如TiDB）+ 冗余高频字段

 

使用前提：

 

• 更新统计信息（ANALYZE TABLE）
• 确认执行计划（EXPLAIN ANALYZE）
• 避免在未支持的版本（如MySQL 5.7）强行使用新特性

 

 

注：仍建议遵循基础优化原则，但需结合数据库版本验证特性支持。

 

由此可见，SQL性能优化时，我们需跳出“局部最优”陷阱，结合业务场景平衡读写负载，从设计、开发到运维形成闭环。只有基于真实负载持续验证，我们才能让优化真正服务于业务效率提升。

 

作者丨SQL数据库

来源丨公众号：SQL语句（ID：SQLquery）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn