mysql有哪些优化场景

一、索引优化‌

‌场景1：索引失效的典型场景‌

• ‌问题‌：查询条件中WHERE create_time > '2023-01-01'未走索引。
• ‌原因‌：字段类型不匹配（例如create_time是datetime，但传入字符串未指定时区）。
• ‌优化方案‌：

  -- 显式指定时间格式 WHERE create_time > STR_TO_DATE('2023-01-01', '%Y-%m-%d');

• ‌效果‌：强制使用索引，查询时间从2s降到50ms。

‌场景2：联合索引设计‌

• ‌问题‌：查询条件为WHERE user_id=1 AND status=0 ORDER BY create_time DESC，响应慢。
• ‌优化方案‌：

  -- 创建联合索引 (user_id, status, create_time) ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

• ‌效果‌：避免回表+排序，查询时间从1.5s降到20ms。

‌场景3：覆盖索引优化‌

• ‌问题‌：分页查询SELECT id, name FROM users LIMIT 1000000, 10性能差。
• ‌优化方案‌：

  -- 使用覆盖索引（例如主键id或联合索引） SELECT id, name FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

• ‌效果‌：避免全表扫描，分页耗时从5s降到10ms。

‌二、表结构优化‌

‌场景4：大字段拆分‌

• ‌问题‌：用户表包含content TEXT字段，导致查询其他字段时IO压力大。
• ‌优化方案‌：

  -- 拆分到扩展表 CREATE TABLE user_profile ( user_id INT PRIMARY KEY, content TEXT );

• ‌效果‌：核心用户表体积减少70%，查询速度提升3倍。

‌场景5：冷热数据分离‌

• ‌问题‌：订单表历史数据占90%，但活跃查询仅最近3个月数据。
• ‌优化方案‌：

  -- 按时间分表（例如按月分区或归档历史数据） CREATE TABLE orders_2023q1 (...) PARTITION BY RANGE (YEAR(create_time)) (...);

• ‌效果‌：热点查询性能提升50%，归档数据可迁移至廉价存储。

‌三、SQL语句调优‌

‌场景6：避免全表扫描‌

• ‌问题‌：SELECT * FROM products WHERE price > 100未使用索引。
• ‌优化方案‌：

  -- 添加索引并限制查询范围 ALTER TABLE products ADD INDEX idx_price (price); SELECT id, name FROM products WHERE price > 100 AND price < 1000;

• ‌效果‌：扫描行数从1000万降到1万行。

‌场景7：子查询优化‌

• ‌问题‌：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)执行慢。
• ‌优化方案‌：

  -- 改用JOIN或EXISTS SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

• ‌效果‌：执行时间从8s降到200ms。

‌四、性能优化‌

‌场景8：连接池配置‌

• ‌问题‌：高并发下数据库连接数不足，导致Too many connections错误。
• ‌优化方案‌：

  # 调整MySQL配置（my.cnf） max_connections = 2000 # 应用端使用连接池（如HikariCP） spring.datasource.hikari.maximum-pool-size=100

• ‌效果‌：连接等待时间减少80%，TPS提升3倍。

‌场景9：读写分离‌

• ‌问题‌：读请求占80%，单库写入成为瓶颈。
• ‌优化方案‌：

  -- 主库负责写，从库负责读 -- 使用中间件（如ShardingSphere）自动路由读写请求。

• ‌效果‌：主库压力降低60%，QPS提升200%。

‌五、高频面试问题参考‌

1. ‌索引失效的常见原因有哪些？‌

   • 隐式类型转换（如字符串转数字）、函数操作、联合索引未遵循最左前缀、OR条件未全走索引。
   • ‌案例‌：WHERE DATE(create_time)='2023-01-01改为范围查询。

2. ‌如何优化深分页问题？‌

   • 使用覆盖索引+延迟关联，或记录上一页最大ID作为起点。
   • ‌案例‌：SELECT id FROM table WHERE id > 1000000 LIMIT 10。

3. ‌如何定位慢查询？‌

   • 开启慢查询日志（slow_query_log=1），用EXPLAIN分析执行计划，关注rows和type字段。

4. ‌分库分表的场景和方案？‌

   • 垂直分表（按业务拆分）、水平分表（按Hash或范围）。
   • ‌案例‌：用户表按user_id % 16分16张表，缓解单表5000万行压力。