1. MySQL存储引擎基础解析MySQL作为最流行的开源关系型数据库之一其核心特性之一就是支持多种存储引擎。存储引擎决定了数据如何存储、索引如何组织以及事务如何实现是数据库性能的关键因素。1.1 InnoDB引擎深度剖析InnoDB是MySQL 5.5版本后的默认存储引擎它提供了完整的ACID事务支持。在实际项目中我90%以上的表都会选择InnoDB原因很简单行级锁定机制大大减少了并发操作的锁冲突支持外键约束保证数据完整性崩溃恢复能力强几乎不会出现数据损坏采用聚集索引主键查询性能极佳重要提示InnoDB的缓冲池(buffer pool)大小直接影响性能建议设置为可用内存的70-80%1.2 MyISAM引擎适用场景虽然现在MyISAM用得越来越少但在某些特定场景下它仍有优势全文索引功能在MySQL 5.6前是唯一选择表级锁定在只读场景下性能更好占用空间小适合存储静态数据我最近一个项目中就用MyISAM存储了上千万条日志数据因为完全不需要事务支持且查询都是批量操作。1.3 其他存储引擎对比引擎事务支持锁粒度适用场景注意事项Memory不支持表级临时表/缓存重启数据丢失Archive不支持行级日志归档只支持INSERT/SELECTNDB支持行级分布式集群配置复杂2. 主从复制实战指南2.1 主从复制原理详解MySQL主从复制的核心是二进制日志(binlog)工作流程如下主库记录所有数据变更到binlog从库IO线程请求主库的binlog主库dump线程发送binlog给从库从库SQL线程重放binlog中的事件这种设计带来了几个重要优势读写分离减轻主库压力数据备份更安全故障转移更快速2.2 主从配置实操步骤主库配置(my.cnf)[mysqld] server-id 1 log_bin mysql-bin binlog_format ROW sync_binlog 1从库配置CHANGE MASTER TO MASTER_HOST主库IP, MASTER_USERrepl, MASTER_PASSWORD密码, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS120;启动复制START SLAVE; SHOW SLAVE STATUS\G2.3 复制延迟问题排查复制延迟是生产环境最常见的问题之一。我总结的排查步骤检查Seconds_Behind_Master值分析主库写入压力检查从库服务器负载查看网络延迟优化方案升级从库硬件特别是SSD调整slave_parallel_workers参数使用GTID复制模式3. 分库分表架构设计3.1 何时需要考虑分库分表根据我的经验当单表数据量达到以下阈值时需要考虑拆分数据量超过500万行表大小超过10GB查询响应时间明显变慢3.2 常见分片策略对比策略优点缺点适用场景范围分片简单易实现热点问题有时间序列特征的数据哈希分片分布均匀扩容复杂无明显查询特征的表目录分片灵活度高维护成本高业务规则复杂的系统3.3 ShardingSphere实战案例最近一个电商项目使用了ShardingSphere实现分库分表核心配置示例spring: shardingsphere: datasource: names: ds0,ds1 sharding: tables: t_order: actual-data-nodes: ds$-{0..1}.t_order_$-{0..15} table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_$-{order_id % 16}关键点按order_id哈希分16张表分布在2个数据库实例上支持分布式事务4. 性能优化与监控方案4.1 关键性能指标监控我常用的监控指标包括QPS/TPS波动连接数使用率缓冲池命中率锁等待时间推荐使用PrometheusGrafana搭建监控系统配置示例- job_name: mysql static_configs: - targets: [mysql-server:9104]4.2 索引优化实战技巧创建索引的几个黄金法则为WHERE条件列创建索引联合索引遵循最左前缀原则避免在索引列上使用函数定期使用ANALYZE TABLE更新统计信息一个真实的优化案例-- 优化前全表扫描 SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; -- 优化后索引扫描 SELECT * FROM orders WHERE create_time 2023-01-01 00:00:00 AND create_time 2023-01-02 00:00:00;4.3 连接池配置建议连接池参数对性能影响巨大推荐配置# Druid连接池示例 initialSize5 maxActive50 minIdle5 maxWait60000 timeBetweenEvictionRunsMillis60000 minEvictableIdleTimeMillis3000005. 高可用架构设计5.1 MGR集群搭建MySQL Group Replication提供了原生高可用方案配置步骤准备至少3个节点配置group_replication参数引导第一个节点其他节点加入集群关键参数plugin-load-addgroup_replication.so group_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa group_replication_start_on_bootoff group_replication_local_address node1:330615.2 读写分离实现推荐使用ProxySQL实现智能路由INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,master,3306), (20,slave1,3306), (20,slave2,3306); INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,^SELECT.*FOR UPDATE,10,1), (2,1,^SELECT,20,1);5.3 备份恢复策略我坚持的备份原则每日全量备份binlog增量备份文件异地存储定期恢复测试xtrabackup使用示例# 全量备份 innobackupex --userroot --passwordxxx /backup/ # 增量备份 innobackupex --userroot --passwordxxx --incremental /backup/ --incremental-basedir/backup/base6. 开发规范与最佳实践6.1 SQL编写规范经过多个项目总结的SQL规范禁止使用SELECT *事务要短小精悍避免大表JOIN使用预编译语句反例SELECT * FROM users WHERE username LIKE %admin%;正例SELECT id, username FROM users WHERE username LIKE admin%;6.2 数据库设计原则我的设计checklist每个表必须有主键字段选择最小够用类型避免NULL值设置默认值适当使用枚举类型6.3 常见陷阱与规避踩过的坑大事务导致复制延迟隐式类型转换使索引失效UTF8MB4字符集问题自增ID用尽风险每个MySQL DBA都应该在办公桌上贴一张参数优化备忘单我的常用调优参数包括innodb_buffer_pool_size 12G innodb_log_file_size 2G innodb_flush_log_at_trx_commit 1 sync_binlog 1 max_connections 500在实际运维中我发现很多问题都是由于配置不当引起的。比如曾经遇到过一个案例tmp_table_size设置过小导致频繁磁盘临时表创建将值从16M调整到256M后性能提升了30%。这也提醒我们MySQL优化是一个需要持续观察和调整的过程。