以下是10道MySQL场景化面试题，结合实际业务场景考察问题分析与解决方案设计能力：
1. 场景：电商订单表（order）每日新增100万条记录，查询近7天的订单时响应缓慢，如何优化？
- 分析：大表+范围查询，可能因索引不合理或数据量过大导致性能问题。
- 优化方案：
- 分区表：按时间（如按天）创建分区表，查询近7天时仅扫描对应分区，减少扫描范围。
- 索引优化：为
create_time（订单创建时间）建立索引，结合最左前缀原则（若有其他查询条件可建联合索引）。 - 冷热数据分离：将超过7天的历史订单迁移至历史表，当前表仅保留热数据，降低单表数据量。
2. 场景：用户表（user）有1亿条记录，需要频繁根据手机号（phone）查询用户信息，如何设计索引和表结构提升查询速度？
- 分析：高频等值查询，手机号唯一，需避免全表扫描。
- 解决方案：
- 索引设计：为
phone字段建立唯一索引（UNIQUE INDEX idx_phone (phone)），确保查询时直接命中索引，避免扫描全表。 - 表结构优化：若表字段过多（如包含冗余信息），可拆分表为
user_core（核心信息：id、phone、name等）和user_extra（扩展信息），查询手机号时仅访问user_core，减少IO。 - 分表：按手机号哈希分表（如取phone后4位模16分16张表），分散数据量，查询时直接定位到对应分表。
- 索引设计：为
3. 场景：秒杀系统中，库存表（stock）的库存扣减出现超卖问题，如何用MySQL保证库存正确扣减？
- 分析：高并发下多线程同时扣减库存，可能因未加锁导致超卖（如库存10，两个请求同时读到10，都扣减为9）。
- 解决方案：
- 悲观锁：扣减时加行锁，
SELECT * FROM stock WHERE goods_id = 1 FOR UPDATE，确保同一时间只有一个事务修改库存。 - 乐观锁：用版本号控制，
UPDATE stock SET num = num - 1, version = version + 1 WHERE goods_id = 1 AND num > 0 AND version = 5，仅当版本号匹配且库存充足时扣减，失败则重试。 - SQL原子性：直接用
UPDATE stock SET num = num - 1 WHERE goods_id = 1 AND num > 0，利用MySQL单条SQL的原子性，避免先查后改的竞态条件。
- 悲观锁：扣减时加行锁，
4. 场景：社交APP的用户关注表（follow）存储用户间的关注关系，需要快速查询“用户A的粉丝”和“用户A关注的人”，如何设计表结构和索引？
- 分析：双向查询需求（粉丝列表、关注列表），需避免表扫描。
- 表结构与索引设计：
- 表结构：
follow(id, user_id, follow_user_id, create_time)，其中user_id是关注者，follow_user_id是被关注者。 - 索引设计：
- 查“用户A关注的人”：建索引
idx_user (user_id)，快速定位user_id = A的记录。 - 查“用户A的粉丝”：建索引
idx_follow_user (follow_user_id)，快速定位follow_user_id = A的记录。
- 查“用户A关注的人”：建索引
- 优化：若数据量过大，可按
user_id分表，同时缓存热门用户的关注/粉丝列表至Redis。
- 表结构：
5. 场景：日志表（log）每日写入1000万条记录，需要按日期查询，但很少更新或删除，如何设计表和索引提升写入和查询性能？
- 分析：写密集型场景，查询按时间范围，需优化写入速度和范围查询效率。
- 解决方案：
- 分区表：按
log_time（日志时间）分区（如RANGE分区，按天/周），写入时自动落在对应分区，查询时仅扫描目标分区。 - 索引策略：仅为
log_time建立索引（避免过多索引影响写入速度），其他字段（如user_id）若需查询，可结合分区过滤+log_time索引。 - 存储引擎：用MyISAM（写入性能优于InnoDB，无事务需求时），或InnoDB关闭事务（
autocommit=0批量提交）。 - 批量写入：应用层攒批插入（如每1000条一次
INSERT），减少SQL交互次数。
- 分区表：按
6. 场景：电商订单表（order）需要支持“按用户ID+订单状态”查询，且订单状态频繁更新（如待付款→已付款），如何设计索引和优化更新性能？
- 分析：查询条件为
user_id + status，状态更新可能导致索引失效或锁冲突。 - 解决方案：
- 联合索引：建
idx_user_status (user_id, status)，遵循最左前缀原则，优化WHERE user_id = ? AND status = ?查询。 - 避免索引失效：状态更新时（如
UPDATE order SET status = 2 WHERE id = ?），因id是主键，更新仅影响主键索引，不影响联合索引，性能稳定。 - 锁优化：更新时用主键过滤（
WHERE id = ?），避免全表扫描或范围锁，减少锁等待。
- 联合索引：建
7. 场景：报表系统需要统计“各省份的订单总金额”，订单表（order）有10亿条记录，如何优化统计性能？
- 分析：全表聚合查询，直接
GROUP BY province会扫描全表，耗时过长。 - 解决方案：
- 预计算：用定时任务（如每小时）统计各省份金额，存储到汇总表（
order_summary(province, total_amount, update_time)），查询时直接读汇总表。 - 分区+索引：按
province分区，同时为province和amount建联合索引idx_province_amount (province, amount)，聚合时仅扫描索引（覆盖索引），减少IO。 - 借助中间件：用ClickHouse等OLAP数据库同步订单数据，专门处理统计查询，MySQL仅存储原始数据。
- 预计算：用定时任务（如每小时）统计各省份金额，存储到汇总表（
8. 场景：用户登录表（login_log）存储用户每次登录的时间和IP，需要查询“用户最近一次登录时间”，如何优化？
- 分析：需按用户ID分组取最新记录，若数据量大，
GROUP BY user_id ORDER BY login_time DESC LIMIT 1性能差。 - 解决方案：
- 索引优化：建联合索引
idx_user_time (user_id, login_time DESC)，user_id用于分组，login_time DESC确保每组内按时间倒序，查询时直接命中索引，无需排序。 - 冗余存储：在用户表（
user）中增加last_login_time字段，每次登录时更新该字段，查询时直接读用户表，避免访问login_log。
- 索引优化：建联合索引
9. 场景：MySQL主从复制中，从库查询到的数据与主库不一致（延迟超过10分钟），如何排查和解决？
- 分析：主从延迟可能由大事务、从库性能不足、网络问题等导致。
- 排查与解决：
- 查看延迟原因：执行
SHOW SLAVE STATUS，检查Seconds_Behind_Master（延迟秒数）、Relay_Log_Space（中继日志大小），若中继日志过大，可能是从库SQL线程执行慢。 - 优化主库：拆分大事务（如批量更新拆分为多次小更新），减少binlog生成量。
- 优化从库：开启从库并行复制（
slave_parallel_workers = 8），让多个SQL线程并行执行中继日志；升级从库硬件（CPU、内存）。 - 网络优化：确保主从节点网络稳定，避免binlog传输延迟。
- 查看延迟原因：执行
10. 场景：多表JOIN查询（如订单表order、用户表user、商品表goods）返回“订单信息+用户姓名+商品名称”，数据量各1000万，如何优化JOIN性能？
- 分析：多表大表JOIN可能导致临时表过大、排序耗时，甚至内存溢出。
- 解决方案：
- 索引优化：为JOIN条件字段建索引（如
order.user_id → user.id、order.goods_id → goods.id），确保JOIN时通过索引匹配，避免全表关联。 - 小表驱动大表：让数据量小的表作为驱动表（如
user表小，则user JOIN order），减少外层循环次数。 - 分表JOIN：若表已分表，按相同规则分表（如都按
user_id哈希分表），确保JOIN时仅在对应分表内进行，避免跨表关联。 - 冗余字段：在
order表中冗余user_name和goods_name，避免JOIN查询（适合非实时更新的字段）。
- 索引优化：为JOIN条件字段建索引（如
这些场景题覆盖了MySQL在高并发、大数据量、复杂查询等实际业务中的常见问题，重点考察索引设计、锁机制、分表分区、主从优化等核心技能。
