整体分为两大块数据库设计事前地基数据库优化事后调优核心思想先做好设计再做 SQL 和索引优化最后考虑硬件 / 架构扩容。适用MySQL 为主关系型数据库通用思路。一、数据库设计最重要80% 性能问题根源1. 三大范式理解不要死套第一范式 1NF列不可再分。一个字段只存一个值不要用逗号分隔存多个 id。第二范式 2NF消除部分依赖。主键是组合主键时非主键字段必须完全依赖整个主键不能只依赖主键一部分。第三范式 3NF消除传递依赖。非主键字段不能依赖其他非主键字段。注意范式是理论工程上经常反范式做冗余换取减少 JOIN 提升查询性能。 反范式场景读多写少、高频关联查询适当冗余字段牺牲写一致性换读性能。2. 表结构设计规范1主键设计优先用自增整型 / BIGINT做主键不要用业务字段手机号、身份证做主键业务唯一约束用唯一索引。自增主键聚簇索引有序页分裂少插入性能好。禁止 UUID 做主键随机值大量页分裂InnoDB 性能很差。2字段类型选择越小越好整型优先TINYINT/INT不要一律BIGINT状态字段用 TINYINT。字符串短字符串用CHAR变长用VARCHAR(n)n 不要过大长文本用TEXT不要用 VARCHAR 存大文本。时间优先DATETIME范围大不受时区影响不推荐 TIMESTAMP上限 2038。布尔用TINYINT(1)代替 bool。原则能小不大能不空就 NOT NULL。NULL 会带来索引额外开销、判断逻辑麻烦。3表拆分垂直分表一张表字段太多把大字段text、长描述拆到附属表主表只放高频查询字段。水平分表数据量巨大千万级以上按 id / 时间哈希或范围拆分多张表。4其他设计要点统一命名小写下划线分隔禁止关键字业务表必备id主键, create_time, update_time, is_deleted软删除不物理删数据外键生产环境尽量不用外键。外键会降低写入性能锁表约束逻辑交给应用程序保证。二、索引设计优化核心InnoDB 是聚簇索引主键就是聚簇索引叶子节点存整行数据二级索引叶子节点存主键值。回表二级索引查到主键再去聚簇索引读取完整行数据。1. 索引分类主键索引聚簇索引唯一非空普通索引最基础 B 树索引唯一索引索引列值唯一允许 NULL联合索引复合索引多个字段组成索引最左前缀原则覆盖索引查询字段全部包含在索引里不需要回表性能最好最左前缀原则联合索引(a,b,c)可以命中where a/where a and b/where a and b and c 不能单独命中where b/where c。覆盖索引查询需要的所有字段都在二级索引里面查询不需要回表去聚簇索引拿完整行数据直接从索引返回结果。InnoDB 二级索引叶子节点存【索引列 主键】如果你的 select 要查的字段全部包含在这个索引里就不用回表避免一次磁盘 IO。建表示例CREATE TABLE user ( id BIGINT PRIMARY KEY, -- 聚簇索引保存整行数据 name VARCHAR(50), age INT, phone VARCHAR(20) );新建联合索引idx_name_age (name, age)这个索引的叶子节点存储内容name, age, id二级索引自带主键 id✅ 案例 1触发覆盖索引SELECT name, age FROM user WHERE name 张三;查询字段name, age刚好都在索引idx_name_age里面不需要拿着查到的 id再回聚簇索引查数据explain的 Extra 字段会显示Using index→ 代表命中覆盖索引❌ 案例 2不触发覆盖索引会回表SELECT name, age, phone FROM user WHERE name 张三;phone 不在idx_name_age索引里索引只拿到 name、age、id拿着 id 回聚簇索引查询 phone这个动作叫回表Extra 不会出现Using index✅ 案例 3如果把 phone 加到索引再次变成覆盖索引CREATE INDEX idx_name_age_phone ON user(name,age,phone); SELECT name, age, phone FROM user WHERE name 张三;查询字段全部在索引中不需要回表Using index。✅ 案例 4只查主键 id天然覆盖索引SELECT id FROM user WHERE name张三;二级索引本身就存主键 id直接返回覆盖索引。关键点总结Using index是 explain 中判断覆盖索引的标志覆盖索引好处减少回表 IO性能提升明显代价索引字段变多 → 索引体积变大写入增删改开销上升联合索引顺序WHERE 条件字段放前面SELECT 需要的字段放后面总结InnoDB 二级索引叶子节点保存索引列和主键。如果一条 SQL 查询的所有字段都存在于索引中就不需要通过主键回聚簇索引读取完整行直接从索引返回数据这就是覆盖索引explain 中 Extra 显示 Using index。2. 索引创建原则✅ 适合建索引WHERE 条件频繁查询字段JOIN 关联字段两边字段都建索引ORDER BY、GROUP BY 字段❌ 不要建索引区分度很低字段性别、状态只有 0/1索引基本无效更新非常频繁的字段每次更新要维护索引写性能暴跌表数据量很小全表扫描比索引更快索引是空间换时间索引会加速查询但是降低 INSERT/UPDATE/DELETE 性能索引不是越多越好。3. 索引失效常见场景面试高频联合索引不满足最左前缀索引列做函数运算、隐式类型转换字符串和数字对比like %关键词前置通配符无法走索引like 关键词%可以使用or一侧字段无索引整体不走索引MySQL 优化器判断全表扫描比索引更快主动放弃索引三、SQL 优化1. 写 SQL 规范禁止select *只查需要的字段优先触发覆盖索引尽量减少 JOINJOIN 表不宜过多MySQL 建议不超过 3 张分页大偏移limit 100000,10不要直接写改成主键过滤where id100000 limit 10IN 里面值不要太多几百个以内大量值考虑分批或者 JOIN避免子查询优先 JOIN子查询容易创建临时表批量插入用INSERT INTO ... VALUES (...),(...)不要循环单条 insert2. explain 分析 SQL必备工具explain select ...重点看字段type访问类型性能从优到劣system const eq_ref ref range index ALL。至少达到 range最好 refALL 代表全表扫描需要优化。key实际使用的索引NULL 没用到索引rows预估扫描行数越小越好ExtraUsing filesort文件排序没有用到索引排序需要优化Using temporary创建临时表常见 group by 无索引性能差Using index覆盖索引优秀四、数据库服务层优化1. MySQL 参数调优InnoDBinnodb_buffer_pool_size最重要缓存数据和索引一般设置服务器内存 50%~70%innodb_log_file_sizeredo log增大减少刷盘不要过大max_connections最大连接数不要盲目调很大连接过多会 OOMsort_buffer_size、join_buffer_size会话级内存不要设置过大2. 事务与锁优化尽量缩小事务范围事务越早提交越好减少行锁持有时间避免锁等待、死锁避免长事务长事务会占用 undo 日志影响 MVCC甚至库膨胀更新尽量按主键顺序更新降低死锁概率InnoDB 行锁只有索引命中才是行锁索引失效行锁升级为表锁五、架构层面优化数据量大时读写分离主库写从库读主从复制分担查询压力。缺点从库存在数据延迟。分库分表水平 / 垂直分库解决单库单表数据量上限引入分布式事务、ID 生成、路由等复杂度。缓存Redis 做热点数据缓存挡掉大部分 DB 查询减少数据库压力。缓存设计要考虑缓存穿透、击穿、雪崩。冷热数据分离历史冷数据归档到其他存储主库只保留近期热数据。六、优化整体流程工作排查顺序慢查询开启slow_query_log捕获慢 SQLlong_query_time默认 10s一般设 1sexplain 分析慢 SQL定位是否没走索引、扫描行数过大优化 SQL 语句 调整索引检查表结构设计是否合理字段类型、是否冗余调整数据库参数架构方案缓存、读写分离、分库分表七、常见面试题小结范式和反范式怎么取舍读多写少可冗余减少 join写多读少尽量遵循范式减少更新成本。B 树索引为什么适合数据库叶子节点有序链表范围查询强非叶子节点只存主键树高度低磁盘 IO 少。聚簇索引和非聚簇索引区别InnoDB 聚簇索引叶子存整行MyISAM 是非聚簇索引叶子存数据文件地址。覆盖索引好处避免回表减少 IO。