02-【面试题】业务增长-数据库性能优化思路讲解

• 面试官：这边有个数据库-单表1千万数据，未来1年还会增长多500万，性能比较慢，说下你的优化思路
• 思路
  • 千万不要一上来就说分库分表，这个是最忌讳的事项
  • 一定要根据实际情况分析，两个角度思考
    • 不分库分表
      • 软优化
        • 数据库参数调优
        • 分析慢查询SQL语句，分析执行计划，进行sql改写和程序改写
        • 优化数据库索引结构
        • 优化数据表结构优化
        • 引入NOSQL和程序架构调整
      • 硬优化
        • 提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘
    • 分库分表
      • 根据业务情况而定，选择合适的分库分表策略（没有通用的策略）
        • 外卖、物流、电商领域
      • 先看只分表是否满足业务的需求和未来增长
        • 数据库分表能够解决单表数据量很大的时,数据查询的效率问题
        • 无法给数据库的并发操作带来效率上的提高，分表的实质还是在一个数据库上进行的操作，受数据库IO性能的限制
      • 如果单分表满足不了需求，再分库分表一起
• 结论
  • 在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案
  • 如果数据量极大，且业务持续增长快，再考虑分库分表方案

补充：
MySQL数据库参数调优是指通过调整MySQL的配置参数，以提升数据库的性能、稳定性和资源利用率。这些参数控制着MySQL的内存使用、连接管理、查询处理、存储引擎行为等方面。

调优的主要目标：

1. 提升性能：加快查询速度，减少响应时间。
2. 提高稳定性：防止崩溃或资源耗尽。
3. 优化资源利用：合理分配内存、CPU和磁盘I/O，避免浪费。

常见的调优参数：

1. 内存相关参数：

   • innodb_buffer_pool_size：InnoDB缓冲池的大小，缓存数据和索引，建议设置为系统内存的50%-70%。
   • key_buffer_size：MyISAM存储引擎的键缓冲区大小。
   • query_cache_size：查询缓存大小（MySQL 8.0已移除）。

2. 连接相关参数：

   • max_connections：最大连接数，控制同时连接的客户端数量。
   • wait_timeout 和 interactive_timeout：控制空闲连接的超时时间。

3. 查询优化参数：

   • query_cache_type：查询缓存类型（MySQL 8.0已移除）。
   • tmp_table_size 和 max_heap_table_size：控制内存中临时表的大小。

4. 日志相关参数：

   • slow_query_log：启用慢查询日志，记录执行时间较长的查询。
   • log_queries_not_using_indexes：记录未使用索引的查询。

5. InnoDB相关参数：

   • innodb_log_file_size：InnoDB日志文件大小，影响事务写入性能。
   • innodb_flush_log_at_trx_commit：控制事务日志刷新到磁盘的频率，影响数据安全性和性能。

调优步骤：

1. 监控和分析：

   • 使用工具（如SHOW STATUS、SHOW VARIABLES、EXPLAIN、慢查询日志等）监控数据库性能，识别瓶颈。

2. 调整参数：

   • 根据监控结果调整相关参数，逐步优化。

3. 测试和验证：

   • 调整后测试性能，确保调优有效且无负面影响。

4. 持续优化：

   • 数据库负载变化时，定期监控和调整参数。

示例：

• 增大InnoDB缓冲池：

  SET GLOBAL innodb_buffer_pool_size = 2G;
  

• 调整最大连接数：

  SET GLOBAL max_connections = 500;
  

总结：

MySQL参数调优是通过调整配置参数来优化数据库性能、稳定性和资源利用率的过程。需要根据实际负载和硬件条件进行监控和调整。