Mysql 性能调优参考2——筑梦之路

1. 版本选择

尽量使用最新版本：最新版本已经修复很多旧版本的bug，也做了很多优化

2. 存储引擎选择

InnoDB 与 MyISAM

低写入量和高读取量  ——  MyISAM   

其他情况选择InnoDB 推荐使用，性能角度InnoDB的优势在：行级锁和聚集索引

比较：

行级与表级锁：MyISAM 将为每次写入锁定全表，而 InnoDB 将仅锁定相应的行（或行）
在大量同时写入操作的情况下，这会导致巨大的性能差异。如您所见，MyISAM 只会将所有客户端排成长等待队列，而 InnoDB 将尽可能多地并行处理客户端（只要它们访问不同的行）。当您结合读/写环境（我们艰苦生活中的 99.99% 情况）时，这会显着提高性能。

聚集索引：nnoDB 将行数据存储在其主键“附近”（这就是它们被称为集群的原因）。这种方法节省了检索通过主键找到的行数据的时间
Mysql 服务器将需要单个操作来按主键查找行并获取全行数据。如果 InnoDB 表没有定义主键（请不要这样做），Mysql 将尽最大努力，使用唯一索引或虚拟列作为主键。

3. 优化InnoDB参数

1）增加缓冲池
InnoDB 存储表数据和索引缓存的内存区域

增加缓冲池始终是开始调整的好步骤。将缓冲池大小设置为服务器上可用 RAM 的 80%（例如，在 16G RAM 服务器上）：innodb_buffer_pool_size = 12G

2）增加日志文件和日志缓冲区
当 InnoDB 表中的数据发生变化时，Mysql 会先将变化写入缓冲池。之后，它将更改操作（以低级格式）记录到日志文件（所谓的“重做日志”）

如果这些日志文件很小，Mysql 必须直接对数据文件进行大量写入磁盘。因此，将日志文件设置为大（如果可能，与缓冲池一样大）：nnodb_log_file_size = 12G

注意：当您更改innodb_log_file_size选项时，您必须在重新启动服务器时删除旧的日志文件

systemctl stop mysqld
rm /var/lib/mysql/ib_logfile*
systemctl start mysqld

大型事务将迫使 Mysql 将大量数据写入日志文件。为了节省资源，Mysql 会先将数据写入日志缓冲区

如果日志缓冲区小于事务影响，Mysql 将不得不等到它将所有内容写入磁盘。如果您有一次插入/更新/删除多行的查询，请增加日志缓冲区：innodb_log_buffer_size = 128M

3）更改数据刷新周期和方法
Mysqly 必须偶尔将数据写入磁盘。这称为将数据刷新到磁盘。首先要改变的是 Mysql 刷新数据的频率，这由innodb_flush_log_at_trx_commit选项控制

默认值1将在每次事务后将数据刷新到磁盘。设置innodb_flush_log_at_trx_commit为0将要求 Mysql 每秒将数据刷新到磁盘。这将显着提高写入性能。但要小心。这可能导致系统崩溃时丢失最多 1 秒的事务。因此，在更改此选项之前，请绝对复制您的 Mysql 服务器以获得可靠性：innodb_flush_log_at_trx_commit = 0


不仅是刷新周期，刷新的方法也会影响Mysql的性能。在多种[方法]https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method中，有几个我们应该提到：

O_DSYNC在将数据写入磁盘时，使用选项将跳过双重缓存

innodb_flush_method = O_DSYNC

性能测试MySQL分析与调优是一个复杂的过程，它涉及到对MySQL数据库的全面监控、性能数据的收集、瓶颈的定位以及最终的优化措施。以下是进行MySQL性能测试与分析调优的基本步骤：

1. 性能监控与数据收集
监控工具：使用Percona Monitoring and Management (PMM)、Grafana、Prometheus等工具进行性能监控。

关键指标：监控CPU、内存、I/O读写时间、连接数、慢查询、系统负载等关键指标。

数据收集：定期收集性能数据，以便分析长期趋势。

2. 性能瓶颈定位
分析CPU使用率：使用top分析，了解是用户态还是系统态过高，需要分析是哪个线程或操作导致的。

慢查询分析：

使用MySQL慢查询日志分析工具，如pt-query-digest，找出执行效率低下的SQL语句。

编写SQL

        在业务发展过程中，随着数据量的增长，一些初期看似性能良好的SQL可能会逐渐变成慢SQL。为了预防这种情况，开发人员需要注意以下几点：

了解业务场景：在编写SQL前，深入了解业务需求和场景，确保SQL逻辑正确。

拆分复杂SQL：将复杂的SQL拆分成多个简单的SQL，并为其添加适当的索引。

使用内存处理：对于复杂查询，可以考虑在内存中分步骤处理数据。

性能验证：在开发阶段，使用大数据量场景来验证SQL性能。

使用EXPLAIN分析SQL

          EXPLAIN是MySQL提供的一个非常有用的工具，可以帮助开发人员分析SQL的执行计划，从而找出性能瓶颈。通过EXPLAIN，我们可以关注以下几个指标：

type：查询类型，不同的查询类型性能差异较大。理想的查询类型是system、const、eq_ref、ref。

possible_keys：可能使用的索引。这一列显示了所有可能用于查询的索引。

key：实际使用的索引。这一列显示了MySQL实际选择的索引。

rows：估计需要检查的行数。这个数字可以大致反映查询的复杂度和性能。

extra：额外信息。这一列提供了关于查询执行计划的额外信息，如Using filesort、Using temporary等。这些信息可以帮助我们识别性能问题。

     针对EXPLAIN的输出结果，我们可以进一步优化SQL语句，提高查询性能。例如，通过调整索引、改写SQL语句、拆分复杂查询等方式来减少查询时间。

I/O问题：使用iostat、iotop等工具检查I/O性能，确定是否有I/O瓶颈。

内存使用：分析缓存区使用情况，确定是否因为内存不足导致性能问题。

连接数问题：出现 Too many connections   适当的调整max_connections

3. 性能调优
配置文件优化：调整my.cnf/my.ini配置文件，包括缓冲区大小、连接数、查询缓存等参数。

存储引擎优化：针对使用的存储引擎（如InnoDB）进行特定的调优。

索引优化：对查询进行索引优化，减少查询时间。

SQL优化：

科学创建索引：确保关键查询列上有合适的索引，以提高查询速度。

减少不必要的列查询：只选择需要的列，避免SELECT * 语句。

使用覆盖索引：让索引包含查询所需的所有列，从而减少回表操作。

语句改写：通过改写SQL语句，使其更加高效。

适当应用内存进行条件组合分次查询：对于复杂查询，可以考虑分步骤在内存中处理。

选择合适的列进行排序：尽量利用索引进行排序，避免额外的文件排序操作。

适当的列冗余：有时，在某些表中添加冗余列可以减少跨表查询，提高性能。

SQL拆分：将复杂的SQL拆分成多个简单的SQL，以提高执行效率。

结合ES（Elasticsearch）：对于全文搜索、复杂聚合等场景，可以考虑使用ES。


4. 特定参数调优
双一参数：

innodb_flush_log_at_trx_commit：调整InnoDB日志刷新策略，以平衡性能与安全性。

sync_binlog：调整二进制日志的同步策略，以优化写入性能。

5. 性能测试
使用工具：使用JMeter、LoadRunner等工具模拟实际负载进行压力测试和并发测试。

测试场景：根据实际业务场景设计测试用例，确保测试结果的有效性。

6. 性能调优验证
回归测试：在调优后进行回归测试，验证优化效果。

持续监控：持续监控数据库性能，确保调优措施长期有效。

7. 注意事项
安全性：在追求性能的同时，确保数据的安全性和完整性。

备份：在进行重大调优之前，确保有完整的数据备份。

逐步进行：性能调优应该逐步进行，每次只调整一个或几个参数，以便于问题的定位。

 

搜集自网络

调整 Mysql 8.0 服务器的性能