面试题：在高并发场景下MySQL阻塞处理策略的优化与实践

Question

在高并发读写的MySQL数据库环境中，频繁出现阻塞影响系统性能。请阐述一套完整的阻塞处理优化策略，包括但不限于数据库参数调整、SQL语句优化、架构层面的改进等，并结合实际项目经验谈谈如何有效实践这些策略。

Answer 1

数据库参数调整

1. innodb_buffer_pool_size：适当增大该参数，它用于缓存innodb表的数据和索引。在高并发场景下，更大的缓存可以减少磁盘I/O，提升读写性能。例如在内存充足的服务器上，可设置为物理内存的 60% - 80%。
2. innodb_log_file_size：合理设置日志文件大小，过小可能导致频繁切换日志文件，增加I/O开销；过大则在恢复时需要更多时间。一般建议设置为innodb_buffer_pool_size的 25%左右。
3. innodb_thread_concurrency：控制InnoDB引擎允许同时进入内核的线程数量，避免过多线程竞争导致性能下降。根据服务器CPU核心数进行调整，一般设置为CPU核心数的 1 - 2 倍。

SQL语句优化

1. 添加合适索引：分析查询语句，对经常作为条件过滤的字段添加索引。例如在SELECT * FROM user WHERE age > 18 AND gender = 'male';中，可对age和gender字段添加联合索引。注意避免索引过多，因为索引维护也有开销。
2. 避免全表扫描：确保查询语句使用索引，通过EXPLAIN关键字分析查询计划，查看是否使用全表扫描。若发现全表扫描，优化查询条件或索引。
3. 减少锁粒度：对于更新操作，尽量精确锁定需要修改的行，而不是整表锁定。例如在多用户评论系统中，更新某条评论时，只锁定该评论所在行。

架构层面的改进

1. 读写分离：使用主从复制架构，主库负责写操作，从库负责读操作。通过中间件（如Mycat等）将读请求分发到从库，减轻主库压力。在电商订单系统中，订单写入主库，订单查询可从从库获取数据。
2. 分库分表：根据业务逻辑将大表拆分为多个小表。例如按时间范围将订单表按年份分表，或者按用户ID哈希分库分表，减少单个表的数据量和锁争用。
3. 缓存机制：引入缓存（如Redis），将热点数据缓存起来，减少对数据库的直接访问。在新闻资讯系统中，热门新闻内容可缓存到Redis，用户请求时先从缓存获取，缓存未命中再查询数据库。

实践经验

1. 在某电商项目中，首先通过SHOW STATUS命令收集数据库运行状态数据，发现innodb_buffer_pool_size过小，导致大量磁盘I/O。将其增大后，系统性能有明显提升。
2. 利用EXPLAIN对高频查询语句进行分析，发现部分查询未使用索引，通过添加合适索引，查询响应时间大幅缩短。
3. 实施读写分离架构，配置主从复制，使用Mycat作为中间件。监控发现主库写压力明显降低，读性能也得到提升。同时在商品详情页引入Redis缓存，商品详情数据的获取速度加快，数据库负载进一步减轻。
4. 针对订单历史数据量过大的问题，采用按年份分表策略，在查询历史订单时，查询性能显著提高，锁争用情况也有所改善。