数据库调优

下面以MySQL和Oracle两大主流数据库从设计、编码、调优、架构到Java应用层系统性地梳理优化思路与实践力求全面且深入。一、设计优化地基决定上层建筑1. 表结构设计原则字段选型最小化用能存储数据的最小类型。例如状态字段用TINYINT代替INTINT够用就别用BIGINT。MySQL中VARCHAR按需给长度避免过度预留但要注意UTF8字符集下一个汉字占3字节。避免使用NULLNULL值会使索引、聚合和比较变得更复杂。尽量设置NOT NULL并赋予默认值。MySQL中可为NULL的列会额外占用1字节且索引中NULL值不参与范围查找。垂直拆分将常用列和不常用的大字段如TEXT/BLOB拆到扩展表减少查询扫描的数据量提升内存缓存命中率。适当冗余与反范式高并发查询的关联字段可适当冗余存储用空间换时间避免频繁JOIN。主键设计MySQL InnoDB强烈建议使用自增整数或有序UUID作为主键。InnoDB是聚簇索引数据按主键顺序物理存储乱序插入会导致页分裂、碎片增加。Oracle通常使用SEQUENCE生成主键配合触发器或默认值索引组织表IOT类似聚簇表但非强制。2. 数据库架构优化读写分离主库负责写从库负责读通过主从复制MySQL Binlog/Oracle Data Guard实现。分库分表当单表数据量过大MySQL建议超2000万行考虑Oracle按性能实际评估进行水平拆分。分片键选择直接影响数据分布均匀性常用订单ID、用户ID取模。中间件ShardingSphere、Mycat、DRDS等。冷热数据分离历史归档数据移入历史表或OLAP库减少热数据体量。二、索引优化查询性能的核心索引是一把双刃剑用得好是加速器用得不好是拖累因为维护需要成本插入变慢等。1. MySQL索引要点BTree索引InnoDB的默认索引结构。所有辅助索引非聚簇索引的叶子节点存储主键值回表查询会产生随机IO。覆盖索引SQL所需列全部包含在索引中避免回表。SELECT id, name FROM user WHERE age 20可建联合索引(age, name, id)id已在主键索引末尾无需显式加入。最左前缀原则联合索引(a, b, c)等效于创建了(a),(a,b),(a,b,c)。查询条件必须从最左列开始且不能跳过中间列否则索引会部分失效。例如WHERE b1 AND c2无法用到该联合索引。索引下推ICPMySQL 5.6支持能在索引遍历过程中过滤掉不符合条件的记录减少回表次数。适合联合索引部分列用于过滤的场景。索引失效场景对索引列使用函数或运算WHERE DATE(create_time) 2024-01-01→ 应改为WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换WHERE phone 13800138000如果phone是varchar会导致索引失效。LIKE %abc左模糊。OR条件连接非索引列可能导致全表扫描。避免过多索引索引过多影响写入性能占用空间。定期分析sys.schema_unused_indexes删除无用索引。MySQL 提供了系统视图 sys.schema_unused_indexes5.6 之后引入。它的原理是基于 performance_schema.table_io_waits_summary_by_index_usage 统计每个索引的访问次数。如果一个索引从未被使用或者只被用于维持唯一性约束而从未用于查询就会显示在这个视图中。使用方式SELECT * FROM sys.schema_unused_indexes;然后可以谨慎删除这些索引注意有些索引虽无查询使用但可能用于防止重复插入或用于外键约束。2. Oracle索引要点B*Tree索引默认同样遵循最左前缀三、SQL编写优化从每一行代码开始1. 通用铁律只查需要的列杜绝SELECT *减少网络传输、内存占用、减少解析字段映射开销也有利于覆盖索引。小表驱动大表JOIN时将结果集较小的表作为驱动表。优化器通常会自己选择但复杂查询可用STRAIGHT_JOINMySQL或LEADING提示Oracle强制驱动顺序。用JOIN代替子查询关联查询通常比子查询效率高尤其是IN子查询可能被优化为相关子查询导致逐条执行。MySQL 5.6对半连接优化较好但仍需验证执行计划。分页优化避免大偏移量LIMIT 100000, 20实际上会扫描并丢弃前10万行。方案记录上一页最后一条ID用WHERE id last_id ORDER BY id LIMIT 20游标分页。Oracle可用ROWNUM分页或12c的OFFSET ... FETCH大翻页同样有性能问题。COUNT优化COUNT(*)与COUNT(1)性能无差别但COUNT(列名)会忽略NULL。MyISAM总行数快InnoDB需要扫描索引。想快速获取近似值可查TABLE_ROWS元数据但不精确。UNION 与 UNION ALLUNION会去重并排序如果确定无重复或允许重复使用UNION ALL。合理使用 EXISTS 与 IN外表大内表小用IN外表小内表大用EXISTS。Oracle会考虑统计信息但理解原理依然重要。