Logo

菜单

易君召
发布于 2026-08-20 / 作者：易君召 / 2 阅读
0

如何通过数据库优化提升性能

#Database

数据库优化整体分为：架构层、索引优化、SQL 语句、表结构设计、配置参数、业务与存储、读写分离分库分表几个维度，从成本最低（SQL / 索引）到成本最高（架构拆分）依次优化。

一、表结构设计优化（基础）

  1. 字段类型尽量小

    • 能用tinyint不用int，能用varchar(50)不用varchar(255)；时间优先用datetime，避免字符串存时间。

    • 主键优先自增整型 / BIGINT，尽量避免 UUID 当主键（InnoDB 聚簇索引，随机 UUID 会造成页分裂，性能下降）。

  2. 避免 NULL：NULL 会增加索引复杂度，尽量设置默认值。

  3. 大字段拆分：text、blob 大字段单独拆到扩展表，避免查询时加载大字段，影响缓冲池。

  4. 适度反范式：高并发场景允许少量冗余，减少多表 JOIN；不要过度范式化造成大量关联查询。

  5. 控制单表数据量：MySQL 建议单表千万级以内，超过考虑分表。

二、索引优化（收益最高，优先做）

1. 建立合适索引

  • InnoDB 主键是聚簇索引，二级索引存储主键值。

  • 经常where、order by、group by、join 关联字段建索引。

  • 联合索引遵循最左前缀原则，把区分度高的字段放前面。

例：index(a,b,c)，where a；where a and b；where a and b and c 可以命中；where b、where b and c 无法命中。

2. 索引避坑

  1. 不要建立过多索引：索引会加速查询，但拖慢 insert/update/delete，DML 需要维护索引。一张表索引建议不超过 5‑8 个。

  2. 避免索引失效场景：

    • 索引列做函数运算、隐式类型转换、like %xxx（前缀模糊）、or 非索引字段

    --失效
where date(create_time)='2026‑08‑20'
where phone = 13800138000  --字段varchar，传入数字，隐式转换
where name like '%张三'

  3. 区分度低的字段不要建索引（如性别、状态只有 0/1，索引几乎无效）。

  4. 优先覆盖索引：查询列全部包含在索引中，回表就可以避免，性能大幅提升。

--联合索引：idx_name_status(name,status)
select name,status from t where name='xxx'; --覆盖索引，不需要回表

3. 索引分析工具

explain分析执行计划，重点看：

  • type：优先ref/range，避免all全表扫描

  • key：实际使用索引

  • rows：扫描预估行数

  • Extra：Using filesort文件排序、Using temporary临时表，代表性能差，需要优化 SQL / 索引

三、SQL 语句优化

  1. 禁止 select *，只查需要字段，减少网络 IO，利于覆盖索引。

  2. limit 大偏移优化：limit 100000,10 性能很差；改用主键分页：where id>100000 limit 10。

  3. join 优化：小表驱动大表；join 字段类型保持一致；减少 join 表数量，建议不超过 3 张。

  4. in 不要集合过大，in 集合几千条建议拆分或者改成 join；避免 not in，优先 not exists。

  5. 避免 order by、group by 文件排序；排序字段尽量落在索引上。

  6. 批量操作：insert 使用批量插入，不要循环单条 insert；update 尽量批量，减少多次交互。

  7. 事务控制：事务尽量短小，不要把大量业务逻辑放在事务内，避免长事务锁等待、undo 日志膨胀。

四、数据库配置参数优化（以 MySQL InnoDB 举例）

  1. innodb_buffer_pool_size：最重要，物理内存 50%‑70%，缓存热点数据和索引，减少磁盘 IO。

  2. innodb_log_file_size：redo 日志文件大小，线上建议 4‑16G，太大故障恢复慢，太小频繁刷盘。

  3. innodb_flush_log_at_trx_commit：1（最安全，每次事务刷盘）；2 性能更高，丢失风险，根据业务取舍。

  4. join_buffer_size、sort_buffer_size：不要全局调很大，这是每个会话分配内存，容易内存打爆，按需会话级别设置。

  5. max_connections：不要设置过大，连接太多上下文切换开销高，搭配连接池。

  6. 开启慢查询日志：捕捉慢 SQL，定位性能瓶颈。

五、锁与事务优化

  1. 避免长事务，长事务会：锁持有时间变长、产生大量 undo、阻碍 purge 线程，数据库堆积历史版本，性能暴跌。

  2. 尽量走索引，InnoDB 行锁；如果不走索引，会升级表锁，并发直接卡死。

  3. 业务更新顺序统一，避免死锁；更新尽量按主键操作。

  4. 降低隔离级别：业务允许情况下，使用 RC（读已提交），减少间隙锁，提升并发。

六、架构层面优化（高并发大数据量）

1. 读写分离

主库写，从库读；分担读压力；注意主从延迟，实时查询依旧访问主库。

2. 分库分表

单表数据量过大（千万‑亿级别）：

  • 水平分表：按 id、时间哈希 / 范围拆分，把数据打散多张表。

  • 垂直分库：按业务模块拆分库，把大库拆多个业务库，降低单库压力。

代价：分布式事务、跨表 join、分页排序复杂度上升，不到万不得已不要做。

3. 引入缓存

Redis 等缓存热点数据，减少数据库访问，这是互联网最常用手段；处理缓存穿透、击穿、雪崩。

4. 冷热数据分离

历史归档冷数据迁移归档库，业务库只保留近期热数据。

七、存储与硬件

  1. 使用 SSD，随机 IO 性能远高于机械硬盘，数据库瓶颈大多是 IO。

  2. 避免 swap，内存充足关闭 swap，一旦数据库使用 swap 性能断崖下跌。

八、排查问题思路（实操流程）

  1. 打开慢查询日志，抓取耗时 SQL；

  2. explain分析慢 SQL 执行计划；

  3. 确认是否缺少索引、索引失效；

  4. 优化 SQL / 增加合适索引；

  5. 观察锁、长事务、连接数；

  6. 参数调优；

  7. 最后才考虑读写分离、分库分表。

优化优先级总结（从低成本到高成本）

SQL优化 → 索引优化 → 表结构设计 → 参数调优 → 缓存 → 读写分离 → 分库分表

很多系统性能差，问题 90% 都出在 SQL 和索引，不要一上来就搞分库分表这种重方案。

本文原创作者：易君召，详见：https://www.yijunzhao.cn/authors/yijunzhao，转载请注明出处。

原文链接 https://www.yijunzhao.cn/archives/database-optimization-performance-improvement-guide

欢迎访问 小易撩挨踢

https://www.yijunzhao.cn/

数据库管理提高数据安全性措施