MYSQL性能优化方案

目录

系统地诊断和解决问题步骤

1. 确认问题和收集数据

2. 分析和定位问题

3. 实施优化措施

4. 监控和反馈

数据库配置审查

1. innodb_buffer_pool_size

2. innodb_log_file_size

3. innodb_flush_log_at_trx_commit

4. max_connections

5. query_cache_size（MySQL 5.7及之前版本）

6. table_open_cache

实施步骤

---

系统地诊断和解决问题步骤

1. 确认问题和收集数据

• 量化问题：记录具体的性能指标，比如查询响应时间、系统负载、IO等待时间等。
• 确定影响范围：确认问题是局限于特定的查询还是整个数据库系统的性能都有所下降。
• 收集日志和错误信息：查看数据库日志、慢查询日志以及应用日志，以便了解问题的背景。

2. 分析和定位问题

• 慢查询分析：利用慢查询日志定位哪些查询最耗时，使用EXPLAIN或其他工具分析这些查询的执行计划。
• 资源使用情况检查：检查数据库服务器的CPU、内存、磁盘IO等资源使用情况，确认是否有资源瓶颈。
• 索引优化：确认是否所有关键查询都在使用适当的索引。没有被利用的索引或者缺失的索引都可以导致查询性能问题。
• 配置检查：审查数据库的配置设置，特别是缓冲池大小、线程数量等，看是否有优化的空间。

3. 实施优化措施

根据分析结果，采取一系列优化措施：

• 优化查询：重写效率低下的查询，减少不必要的数据加载和处理。
  • 避免出现select * ,聚集索引和辅助索引；
  • 多表查询是mysql小表在前   oracle相反；
  • 使用表别名，减少解析时间并减少列名歧义引起的语法错误；
  • mysql采用从左往右 自上而下，解析where字句，过滤条件多的往前放；
  • 适当使用commit释放事务占用的资源 ；
  • 对于复杂的查询，可以使用中间临时表暂存数据；
  • 优化group by 语句。group by 会默认对分组字段进行排序，后面加用order by null  可以去掉；
  • 优化join语句；
  • union  会去重，union all 不会去重；
  • 拆分复杂sql为多个小sql 避免大事务；分解大的复杂查询为多个小查询，可以有助于数据库更好地利用缓存。
  • 使用合理分页方式提高分页效率 。如果中间数据量过大先分页查询id  在利用id，关联本表id查出所有字段；
  • 查询执行顺序：from   -  on  - join  -  where - group by  - having  -  select  - distinct  -  order by - limit
  • 用where替换having。having只会在检索出所有记录之后才对结果集进行过滤  where在聚合前刷选记录    having 一般用于聚合函数
• 调整或添加索引：基于查询分析结果，添加缺失的索引或调整现有索引，提高查询效率。
• 调整数据库配置：根据系统的具体需求调整配置，比如增加缓冲池的大小。
• 硬件升级：如果资源瓶颈是硬件限制导致的，考虑升级硬件，比如增加内存、使用更快的存储设备等。

4. 监控和反馈

• 监控变化：实施优化措施后，持续监控数据库的性能指标，确认优化效果。
• 调整优化策略：根据监控结果和反馈，继续调整优化策略。

---

数据库配置审查

针对数据库配置审查，需要关注多个维度的配置参数，这些参数会影响到MySQL数据库的性能表现。下面列出了一些关键的配置项，以及如何审查和调整它们以优化数据库性能。

以及数据库连接串配置的参数，连接串参数可以重点关注serverTimezone、rewriteBatchedStatements、netTimeoutForStreamingResults、allowMultiQueries等参数；

1. innodb_buffer_pool_size

• 描述：这是InnoDB存储引擎最重要的配置参数之一，用于缓存数据和索引。它应该被设置为系统可用内存的60%-80%。
• 审查策略：检查当前值是否设置得过低，导致大量的磁盘I/O操作。如果服务器专门用于MySQL，可以考虑增加这个值来提高缓存命中率。

2. innodb_log_file_size

• 描述：这个配置项设置了InnoDB重做日志文件的大小。较大的日志文件可以减少日志的回滚频率，提高事务的处理速度。
• 审查策略：如果你的数据库事务量大，考虑增加这个值。但需要注意，调整这个参数需要先停止数据库服务，调整参数后删除旧的日志文件，然后重启数据库。

3. innodb_flush_log_at_trx_commit

• 描述：这个参数控制着事务日志的刷新频率。设置为1表示每次事务提交时都会刷新日志，这提供了最好的耐久性，但可能影响性能。设置为2或0会减少I/O操作，提高性能，但在崩溃情况下可能会丢失数据。
• 审查策略：根据你的业务需求平衡性能和数据安全。对于需要高事务吞吐量且可以容忍极端情况下的微小数据丢失的应用，可以考虑设置为2或0。

4. max_connections

• 描述：这个参数定义了MySQL可以同时处理的最大连接数。
• 审查策略：如果你发现有大量的连接错误，或者Too many connections的错误，可能需要增加这个值。但是，增加最大连接数会增加内存使用量，因此需要确保服务器有足够的资源。

5. query_cache_size（MySQL 5.7及之前版本）

• 描述：查询缓存可以缓存SELECT查询的结果。在后续相同的查询请求时直接返回缓存的结果，以减少数据库的负载。
• 审查策略：在MySQL 8.0中，查询缓存被移除了。但在早期版本中，如果查询缓存命中率低或者查询模式频繁变化，可能需要禁用查询缓存或者调整大小。

6. table_open_cache

• 描述：这个参数设置了服务器可以缓存的打开表的数量。
• 审查策略：如果你的应用需要访问许多不同的表，增加这个值可以减少打开和关闭表的开销。

7. thread_cache_size

• 描述：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限).
• 即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。
• 审查策略：如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。

        对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

• 物理内存设置规则：通过比较Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(-->表示要调整的值)   根据物理内存设置规则如下：
       1G  ---> 8
       2G  ---> 16
       3G  ---> 32
      >3G  ---> 64

  查询thread_cache_size设置

  show global status like'thread_cache_size';

  设置命令：

  mysql> set global thread_cache_size=16
  

• 编辑/etc/my.cnf 更改/添加 thread_concurrency = 16

实施步骤

• 逐一审查：针对每个参数，基于当前的系统负载和性能表现进行审查。
• 逐步调整：调整任何参数时，推荐逐步进行，每次只调整一个参数，并监控变化对性能的影响。
• 监控和测试：使用性能监控工具来跟踪改变后的效果。确保在开发或测试环境中测试配置改变的影响，避免直接在生产环境中进行大幅度调整。

通过审查和调整这些数据库配置，可以显著提高MySQL数据库处理大规模数据的能力，优化查询响应时间，提升整体性能。