mysql并发读多写少如何优化_mysql架构设计思路

不是必须但几乎总是值得做；读远超写时主库易因select压力过载，主从分离可释放主库压力，前提是从库能接受几秒延迟且应用层正确路由读写。

读多写少时，mysql 主从分离是不是必须的？

不是必须，但几乎总是值得做。当读请求远超写请求（比如 9:1），单点 MySQL 很快会因 SELECT 压力导致连接堆积、CPU 升高、复制延迟加剧。主从分离能立刻释放主库压力，让 INSERT/UPDATE/delete 更专注、更稳定。

关键判断点：只读从库是否能接受几秒级延迟？ 如果业务允许（如商品列表、用户资料页、后台报表），就适合；如果要求强一致性（如余额查询后立即下单），就得搭配 SELECT ... for UPDATE 或强制走主库逻辑。

• 从库至少部署 1 台，建议用 read_only=ON + super_read_only=ON 防误写
• 应用层路由要明确：写操作固定打到主库地址，读操作按策略分发（轮询、权重、或基于事务上下文判断）
• 避免在从库执行 LOAD DATA INFILE、CREATE TEMPORARY table 等不被复制的操作，否则可能引发 Slave_SQL_Running: No

索引优化比加机器更有效，但哪些字段该建联合索引？

读多场景下，慢查询往往集中在几个高频 WHERE + ORDER BY + LIMIT 组合。联合索引不是“把所有 WHERE 字段堆一起”，而是按 WHERE = 条件 → WHERE IN/范围 → ORDER BY → SELECT 列 的顺序组织。

例如常见查询：SELECT id, title FROM article WHERE status = 1 AND category_id = 123 ORDER BY created_at DESC LIMIT 20。最优索引是：INDEX(status, category_id, created_at) —— 注意 created_at 必须是 DESC 方向（MySQL 8.0+ 支持显式 DESC，5.7 及以前默认升序，逆序扫描效率低）。

• 避免对 TEXT、BLOB 字段建索引；大字段用前缀索引要谨慎，SELECT ... LIKE '%xxx' 无法用上索引
• 定期用 pt-query-digest 分析慢日志，重点关注 Rows_examined 远大于 Rows_sent 的查询
• 删除长期未命中的索引（通过 sys.schema_unused_indexes 或 performance_schema.table_io_waits_summary_by_index_usage）

连接池和查询缓存要不要开？

MySQL 8.0 已彻底移除 query_cache_type，别再配它。而连接池必须由应用层控制，MySQL 自身没有“连接池”概念 —— 它只有 max_connections 上限，超了直接报 Too many connections。

真实瓶颈常出现在应用频繁创建/销毁连接，而非 MySQL 处理不过来。java 用 HikariCP、Go 用 database/sql 自带池、python 用 SQLAlchemy 的 QueuePool，都要设合理 max_pool_size（通常 20–50，视并发量调）和 idle_timeout（避免空闲连接占着 wait_timeout 不放）。

• 不要把 max_connections 设得过大（如 2000+），会导致内存暴涨、上下文切换严重
• 监控 Threads_connected 和 Threads_running，后者持续 > 30 就说明有慢查询或锁等待在积压
• 短连接场景（如 php-FPM 模式）可考虑 mysqlnd_ms 或代理层（proxySQL）做连接复用

分库分表是不是现在就要做？

不是。95% 的读多写少系统，靠主从 + 索引 + 连接池 + 合理缓存（redis）就能撑到千万级日活。过早分库分表只会让事务、跨库 JOIN、数据迁移、运维复杂度指数上升。

真正该启动分片的信号是：单表超过 2000 万行且 ALTER TABLE 已无法在线完成，或主从延迟稳定在 60s 以上且无法通过降读、拆查询解决。此时优先考虑垂直拆分（如把日志表、统计表独立出主库），再评估水平分片。

• 分片键必须是高频查询的等值条件字段（如 user_id），不能是 create_time 这类范围字段
• 避免跨分片 ORDER BY ... LIMIT，它需要归并排序，性能陡降
• 用 ShardingSphere-Proxy 或 vitess 比硬编码分片逻辑更可控，但依然绕不开分布式事务的妥协（最终一致性 or TCC）

最常被忽略的一点：缓存穿透和雪崩问题在读多场景下爆发更快。哪怕加了 redis，也要给空结果设短 TTL，并用 SETNX 配合本地锁防击穿 —— 这比调优 MySQL 本身还关键。