面试题：MySQL高并发场景下查询性能瓶颈与优化

性能瓶颈分析

数据库层面
- 查询商品详情：在高并发情况下，全表扫描 SELECT * FROM products WHERE product_id =?; 会带来较大压力，若 product_id 没有索引，数据库需逐行匹配，I/O 开销大。
- 下单操作：涉及订单插入和库存更新两个操作，通常在一个事务中。高并发时，事务竞争资源，可能导致锁等待，进而产生死锁情况。例如，多个事务同时尝试更新同一商品库存。
- 库存查询更新：UPDATE stock SET quantity = quantity -? WHERE product_id =? AND quantity >=?; 语句，若 product_id 无索引，更新操作定位数据缓慢。并且 quantity >=? 条件可能无法有效利用索引，也会影响性能。同时，高并发更新库存容易出现数据不一致问题，如超卖现象。
架构层面
- 单点数据库压力：高并发请求集中在单个数据库实例，可能导致 CPU、内存、I/O 资源耗尽。

优化策略

数据库架构调整
- 主从复制：设置主从数据库，主库负责写操作（订单插入、库存更新），从库负责读操作（商品详情查询、库存查询）。这样可以分散读压力，提高系统整体性能。例如，使用 MySQL 主从复制，通过配置 log - bin 等参数实现数据同步。
- 读写分离：结合主从复制，应用程序通过中间件（如 MyCat）实现读写分离。根据操作类型自动路由到主库或从库，减轻主库读压力。
- 分库分表：当数据量过大时，对商品表、订单表和库存表进行分库分表。例如，按商品类别对商品表分表，按时间范围对订单表分表，按商品 ID 对库存表进行水平分库分表。可使用哈希算法或范围分区等方式实现。
索引优化
- 商品表：在 product_id 字段上创建索引，加快商品详情查询速度。如在 MySQL 中执行 CREATE INDEX idx_product_id ON products(product_id);
- 库存表：在 product_id 字段创建索引，同时考虑创建联合索引 (product_id, quantity)，以优化库存查询更新语句的性能。执行 CREATE INDEX idx_product_quantity ON stock(product_id, quantity);
事务处理
- 减少事务粒度：下单事务中，尽量缩短事务的执行时间。比如先插入订单记录获取订单 ID，再进行库存更新，将两个操作放在不同事务中（前提是业务允许）。这样可以减少锁的持有时间，降低死锁风险。
- 乐观锁与悲观锁：
  - 悲观锁：在库存更新时，使用悲观锁，如 SELECT quantity FROM stock WHERE product_id =? FOR UPDATE; 先锁定库存记录，防止其他事务并发更新。但这种方式会增加锁等待时间，降低并发性能。
  - 乐观锁：在库存表中增加版本号字段，每次更新库存时，先比较版本号，若一致则更新并增加版本号，否则重试操作。如 UPDATE stock SET quantity = quantity -?, version = version + 1 WHERE product_id =? AND version =?; 乐观锁适合读多写少场景，可提高并发性能。
缓存策略
- 商品详情缓存：使用 Redis 等缓存工具，将商品详情数据缓存起来。查询商品详情时，先从缓存获取，若缓存不存在再查询数据库，并将结果存入缓存。例如在 Java 中使用 Spring Cache 集成 Redis 实现缓存功能。
- 库存缓存：对于库存数据，也可进行缓存。但由于库存数据一致性要求较高，需要合理设置缓存更新策略。如在库存更新后，立即失效缓存，下次查询时从数据库重新加载。
数据库配置优化
- 调整数据库参数：根据服务器硬件资源，合理调整数据库参数，如 innodb_buffer_pool_size（InnoDB 存储引擎缓冲池大小）、max_connections（最大连接数）等，提高数据库性能。

星途面试题库

面试题：MySQL高并发场景下查询性能瓶颈与优化

知识考点

面试题答案

性能瓶颈分析

优化策略