Mysql常见的优化方法

数据库优化(底层基础优化)

数据库层面的优化是性能“基础"， 主要包含架构设计、存储引擎、表结构、索引策略、配置参数等方面考虑。目标是减少资源(CPU、IO和内存)消耗。

架构设计

• 读写分离：将"读操作"和"写操作"分离到不同的数据库节点。
  • 主库(Master)：负责写操作(INSERT/UPDATE/DELETE)，保证数据一致性。
  • 从库(Slave)：负责读操作(SELECT)，通过主从复制(基于binlog)同步主库数据，
  • 适用场景：读多写少的业务(如电商商品详情页，新闻网站)，可通过增加从库数量分摊读压力。
• 分库分表：当单表数据量过大时(超千万行)，或单库压力过高时，需拆分数据。
  • 水平分表(按行拆分)：将表数据按照不同行拆分到多表。(结构相同)。如按照时间拆分。
  • 垂直分表(按列拆分)：将大表中不常用的字段 拆分到子表中(减少单表宽度)
  • 分库：将多个表拆分到不同数据库(如按照业务模块分库：用户表、订单库)
• 使用缓存缓解数据库压力
  • 对高频访问且不常变化的数据（如商品分类、热门文章）通过Redis。Memcached等缓存中间件缓存，减少数据库的查询次数。
  • 注意：需要处理缓存一致性(如更新数据库后同步更新缓存)和缓存穿透/击穿/雪崩问题。
• 存储引擎优化：选择合适的存储引擎时性能优化的关键，需要根据业务场景匹配特性：
  • 优先选择InnoDB(MySQL 5.5默认)：
    适合需要外键、事务、行级锁、崩溃恢复的场景
    • 优化点：调整innodb_Buffer_pool_size(建议设为物理内存的50%~70%)，减少IO。
    • 开启innoDB_Flush_log_at_trx_commit(默认)保证事务持久性，若允许少量数据丢失可设置为2.提升性能。
  • MYISAM：适用于 读多写少，无需事务(如日志、静态数据)，优势索引缓存效率高，但不支持事务和行锁。崩溃后回复困难。
• 表结构设计优化：合理设计表结构 能够减少存储空间，提升查询效率。核心原则：精简、合适、平衡范式于反范式。
  • 数据类型选择：最寻最小够用，避免大类型存储小数据
  • 平衡范式和反范式：
    • 范式(1NF~3NF)：减少冗余(如避免同一字段再多表中重复)。但会导致多表联查增多。
    • 反范式：适量增加冗余，减少JOIN操作，提高性能。
  • 避免过度设计：
    • 不过多适用外键(外键会增加写操作开销)
    • 合理设置表中字段(建议不超过20个)，过多会导致IO和内存消耗。
• 核心索引设计原则：
  • 为WHERE、JOIN、GROUP BY的字段建立索引。
  • 遵循联合索引"最左匹配原则”
  • 优先分区分度大的字段建立索引。
• 避免索引失效
  • 索引字段适用函数/运算。
  • OR连接无索引字段。
  • 字符串不加引号，导致类型转换
  • 范围查询左边以%开始
  • NOT IN，!=，<>
• 索引维护
  • 定期删除冗余索引(如主键已索引，旧无需再建立二级索引)。
• 相关配置设置，通过调整MYSQL配置文件(my.config/my.ini)提升性能，
  • 内存相关：
  • innodb_buffer_pool_size：Innodb缓冲池大小(一般为物理内存的50%~70%).
  • key_buffer_size：MyISAM 索引缓存大小（仅用于 MyISAM 表）。
• IO相关
  • innodb_flush_log_at_trx_commit：控制 redo log 刷新策略（1 = 每次提交刷盘，最安全；2 = 每秒刷盘，性能更好）。
  • sync_binlog：控制 binlog 刷新策略（1 = 每次提交刷盘，主从同步更可靠；0 = 由 OS 决定，性能高但有丢失风险）。
• 连接相关：
  • max_connections：最大连接数（默认 151，需根据并发量调整，避免连接数不足）。
  • wait_timeout：空闲连接超时时间（释放长期闲置的连接，默认 8 小时）。

语句优化

针对单条语句的执行效率，尽可能让SQL走索引。核心通过EXPLAIN分析执行计划，优化语法

• 避免全表扫描(type:ALL)，全表扫描(遍历表中所有行)，

  • 明确查询条件：WHERE子句必须包含索引字段（或能触发索引的条件）。
  • 反例：SELECT * FROM user（无WHERE，必全表扫描，除非表极小）。

• 优化查询字段：

  • 避免SELECT＊：只需要查询需要的字段，减少数据传输和IO，且尽可能避免回表。
  • 减少SELECT DISTINCT：DISTINCT会触发排序去重，开销大，可通过索引或业务逻辑避免重复数据。

• 优化JOIN操作：JOIN是多表联合查询的核心，低效的JOIN会降低效率。

  • 小表驱动大表：JOIN时，用小数据量作为驱动*(左表)*，减少外层循环。
  • 关联字段加索引，JOIN的关联字段(如s.id = b.sid中id和sid)必须建索引，否则会导致全表扫描+嵌套循环。
  • 减少Join表的数量：尽量控制表数量在3张以内。

• 优化子查询：子查询(SELECT 中嵌套SELECT)可能产生临时表，效率较低，建议使用JOIN替代。

• 优化排序和分组

  • 利用索引排序：若排序字段是索引的一部分，可避免额外排序（索引本身有序）。
    例：索引(age, name)，查询SELECT * FROM user WHERE age > 18 ORDER BY age, name（直接用索引顺序，无需排序）。
  • 限制排序数据量：排序前通过WHERE过滤掉无关数据，减少排序行数。
    例：SELECT * FROM user WHERE age > 18 ORDER BY age LIMIT 10（仅排序符合条件的行，且只取前 10）。
  • GROUP BY优化：GROUP BY会先排序再分组，可通过ORDER BY NULL禁用排序（若无需分组后排序）：
    SELECT age, COUNT(*) FROM user GROUP BY age ORDER BY NULL 。

• 分页查询优化：大分页(如limit 100000,10)会扫描大量无用数据，

  • 基于主键分页：利用主键有序性，通过WHERE定位起始位置：
    优化前：SELECT * FROM order LIMIT 100000, 10（扫描 100010 行）
    优化后：SELECT * FROM order WHERE id > 100000 LIMIT 10（仅扫描 10 行，需id是主键）
  • 延迟关联：先查主键，再关联获取其他字段（减少扫描字段）：
    SELECT o.* FROM order o JOIN (SELECT id FROM order LIMIT 100000, 10) t ON o.id = t.id

• 避免频繁创建临时表
  以下操作可能触发临时表（内存或磁盘临时表，开销大）：

  • GROUP BY、DISTINCT、UNION
  • 子查询结果作为临时表
    优化：尽量用JOIN替代子查询，避免不必要的GROUP BY，或通过tmp_table_size和max_heap_table_size限制内存临时表大小（超过则转磁盘）。

• 用EXPLAIN分析执行计划
  EXPLAIN是 SQL 优化的 “利器”，通过它可查看 SQL 的执行方式（是否走索引、扫描行数等），重点关注：

  • type：访问类型（从差到好：ALL（全表扫描）→ index（索引扫描）→ range（范围扫描）→ ref（非唯一索引匹配）→ const（主键匹配））。
  • key：实际使用的索引（NULL表示未走索引）。
  • rows：预估扫描的行数（越小越好）。
  • Extra：额外信息（如Using filesort（需排序）、Using temporary（用临时表）、Using index（覆盖索引，无需回表））。