怎么理解MySQL性能调优

本篇内容介绍了“怎么理解MySQL性能调优”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

MySQL性能调优概述

影响MySQL性能的因素主要有环境问题(CPU、磁盘I/O、网络性能、操作系统争用)和 MySQL 配置（数据库设计<索引、数据类型、标准化>、应用程序性能<特定请求、短时事务>、 配置变量<缓冲区、高速缓存、InnoDB 设置>）

MySQL 的性能受主机的性能特征影响。各种因素都会影响主机性能：CPU 速度和数量、磁盘吞吐量和访问时间、网络吞吐量以及操作系统上的竞争服务都对MySQL 实例的性能有一定的影响。

数据库内容及其配置也会影响MySQL 性能。

— 包含频繁小更新的数据库将因为精心设计和标准化而受益。

— 当您使用最小的适用数据类型来存储数据时，数据库吞吐量将提高。

— 仅请求表数据的一个子集的查询将从精心设计的索引中获益。

— 仅请求特定行和列的应用程序将减少冗余请求所产生的开销。

— 事务越短，越不会导致其他事务的锁定和延迟。

— 调节得当的服务器变量可优化针对特定工作负荷和数据集的MySQL 缓冲区、高速缓存和其他资源的分配。

1.1.        性能监控

要调节服务器的性能，必须了解其性能特征。为此，可以对整体性能进行基准测试，MySQL 安装提供了以下基准测试工具：

l  mysqlslap 是标准MySQL 分发的一部分。这是一个诊断程序，它模拟MySQL 服务器实例上的客户机负载，并显示每个阶段的计时信息。

l  sql-bench 是MySQL 源代码分发的一部分，它是一系列Perl 脚本，用于执行多个语句和收集状态计时数据。

以下是使用mysqlslap 在SQL 脚本中设置模式并运行其他脚本中的查询的示例：

shell>mysqlslap --iterations=5000 --concurrency=50 --query=workload.sql --create=schema.sql --delimiter=";"

此外，MySQL还提供了第三方基准测试套件。

也可以使用慢速查询日志、一般查询日志，EXPLAIN 、PROCEDURE ANALYSE逐个分析事件；慢速查询日志用于记录超过long_query_time 和min_examined_row_limit 变量所设置的限制的语句。使用mysqldumpslow 可查看慢速查询日志的内容。一般查询日志用于记录MySQL 所接收的所有客户机连接和请求。使用该日志可记录一段时间内（例如，生成供mysqlslap 或其他基准测试工具使用的工作负荷时）所接收的所有SQL语句。

使用SHOW STATUS和 mysqladmin extended-status 查看数据库状态，同时可以使用PERFORMANCE_SCHEMA 按组分析事件。

1.2.        性能模式

“性能模式”是在较低级别监控MySQL 服务器执行情况的功能。该功能是使用PERFORMANCE_SCHEMA 存储引擎和performance_schema 数据库实现的。从Oracle 下载的所有二进制版本的MySQL 中都提供了性能模式。默认情况下，将启用性能模式，并在服务器启动时使用performance_schema 变量对其进行控制。使用以下语句确认已启用性能模式：

mysql> SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| performance_schema | ON |

+--------------------+-------+

性能模式下允许您监控并检查MySQL 服务器中的被检测代码的性能特征。开发者将检测函数和其他编码事件以收集计时信息；公开的性能数据对MySQL 代码库的贡献者、插件开发者很有帮助，同时对识别低级别的性能瓶颈，如日志文件I/O 等待或缓冲池互斥等任务有极大好处。性能模式所公开的信息可用于识别低级别瓶颈。该信息中大部分都是低级别的，可帮助MySQL 服务器产品系列的开发者调试性能问题，或者帮助系统架构师和性能顾问调节InnoDB 数据和日志文件存储硬件。

l  检测（INSTRUMENT）、实例（INSTANCE）、事件（EVENT）和使用者（CONSUMER）

性能模式数据库包含配置和事件信息：

— 检测"是服务器代码中引发要监控的事件的点，在setup_instruments表中进行配置；

— 每个被检测的对象都是该检测的一个“实例”，记录在一系列实例表中。

— 当线程执行检测实例中的代码时，MySQL 将识别所发生的“事件”，将其记录在事件和汇总表中。

— 每个“使用者”都是性能模式中表的名称，用于记录和查询事件以及事件的摘要，在SETUP_CONSUMERS 表中进行配置。

性能模式中的检测是服务器源代码中的点，MySQL 从该点引发事件。检测具有分层命名约定。例如，以下是包含性能模式中数百个检测中的一部分的简短列表：

stage/sql/statistics

statement/com/Binlog Dump

wait/io/file/innodb/innodb_data_file

wait/io/file/sql/binlog

wait/io/socket/sql/server_unix_socket

每个检测由其类型、所属的模块、该特定检测的变量或类组成。通过查询performance_schema.setup_instruments 表可查看所有可用的检测。

性能模式将记录实例表中的每个检测实例。例如，以下查询显示检测wait/io/file/sql/FRM 记录文件实例/var/lib/mysql/mem/tags.frm 上的事件。

mysql> SELECT file_name, event_name FROM file_instances LIMIT 1\G

*************************** 1. row ***************************

FILE_NAME: /var/lib/mysql/mem/tags.frm

EVENT_NAME: wait/io/file/sql/FRM

以下输出显示了setup_consumers 表的内容：

mysql> SELECT * FROM setup_consumers;

+--------------------------------+---------+

| NAME | ENABLED |

+--------------------------------+---------+

| events_stages_current | NO |

| events_stages_history | YES |

| events_stages_history_long | NO |

| events_statements_current | YES |

| events_statements_history | NO |

| events_statements_history_long | NO |

| events_waits_current | YES |

| events_waits_history | YES |

| events_waits_history_long | NO |

| global_instrumentation | YES |

| thread_instrumentation | YES |

| statements_digest | YES |

+--------------------------------+---------+

12 rows in set (0.00 sec)

每个使用者的NAME 是性能模式中用于查询事件和摘要的表的名称。被禁用的使用者不记录信息，从而节省了系统资源。

当MySQL 识别了发生在检测实例中的事件后，会将其记录在事件表中。

— 主事件表为events_waits_current，该表中存储了每个线程最近的事件。

— events_waits_history 存储每个线程的最近10 个事件。

— events_waits_history_long 共存储10,000 个最近事件。

events_waits_* 表都使用相同的模式。有关该模式的结构信息，请访问

http://dev.mysql.com/doc/refman/5.6/en/events-waits-current-table.html

当使用性能模式识别瓶颈或其他问题时，请执行以下操作：

1. 确保已针对适用于您所遇到的问题类型的一系列检测和使用者启用了性能模式。例如，如果您确定问题出在I/O 限制上，请使用wait/io/file/* 检测；如果不确定根本原因，请使用更广范围的检测。

2. 运行用于产生该问题的测试用例。

3. 查询events_waits_* 表等使用者，尤其是使用适用的WHERE 子句过滤器查询events_waits_history_long，以便进一步缩小问题原因的范围。

4. 禁用那些用于评估已排除的问题的检测。

5. 重试该测试用例。

1.3.        一般数据库优化

1)         标准化

对数据进行标准化可以消除冗余数据，提高事务性工作负荷的性能，提供对数据的灵活访问，最大限度地减少数据不一致情况。

标准化是移除数据库中的冗余和不当依赖关系（以避免将相同的数据存储在多个地方以及出现异常的风险）的行为。标准化通常会产生以下结果：许多表的列变少，整体存储要求降低，I/O 需求降低以及单次插入、更新和删除操作加快。这提高了频繁执行小更新的事务性工作负荷的性能，但会使检索大量数据的查询变得复杂。

2)         数据类型和大小

选择正确的数据类型和大小可以避免NULL，提高性能，保护数据，在适当情况下使用数据压缩。

选择正确的数据类型是表设计中一个很重要却常常被忽视的部分，数据类型的大小可能会对表操作产生较大的影响。例如，选择将SMALLINT 数字存储为INT 会使该列所需的空间翻倍。在包含一百万个行的表中，该决策将导致浪费额外的2 MB 存储空间，并且磁盘操作速度会变慢，缓冲区和高速缓存将需要使用更多内存。使用INSERT … COMPRESS(field_name) … 和SELECT … UNCOMPRESS(column_name) ... 可以在存储和检索字符串数据时对其进行压缩和解压缩。尽管也可以使用CHAR 或VARCHAR 字段来实现此目的，但是通过使用VARBINARY或BLOB 列存储压缩数据可以避免字符集转换出现问题。

3)         高效索引

创建最佳索引可以提高查询吞吐量，减少I/O 开销。

如果您通过在WHERE 子句中指定一个字段来查询表中的特定行，并且该表没有为该字段创建索引，MySQL 将读取该表中的每一行以找到每个匹配的行。这将导致很多不必要的磁盘访问，并且对于大型表性能将显著降低。索引是有序的成组数据，通过索引，MySQL 可以更容易地找到查询行的正确位置。默认情况下，InnoDB 将按主键排列表的顺序；该有序表称为群集索引。InnoDB 表上的每个附加索引或辅助索引会在文件系统中占用额外的空间，因为索引包含索引字段的额外副本以及主键的副本。每次使用INSERT、UPDATE、REPLACE 或DELETE 操作修改数据时，MySQL 也必须更新所有包含修改字段的索引。因此，向表中添加多个索引会降低影响该表的数据修改操作的性能。不过，如果对索引进行了适当设计，依赖于索引字段的查询便会在性能上有较大的获益。如果查询无法使用索引找到特定行，则必须执行全表扫描；即，必须读取整个表来找到该行。使用索引的查询可以直接读取相应行而不读取其他行，这极大地提高了此类查询的性能。

1.4.        PROCEDURE ANALYSE

PROCEDURE ANALYSE() ，在优化表结构时可以辅助参考分析语句。利用此语句，MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。但是，只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

语法如下：

SELECT ... FROM table_name WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]);

l  max_elements (默认值 256) 是analyse注意到每列不同值的最高数目。analyse使用此参数来检查是否最优化的列的类型是ENUM类型。

l  max_memory (默认值 8192) 是analyse在查找所有不同值时分配给每列的最大内存数。

示例1：

以第4行的分析举例，可以看出，br_Task.task_name字段：

列最小值:121 new

列最大值:韩-软文-全国-银牌见

最小长度:3字节

最大长度:52字节

平均长度:24.1852

优化建议：字段的数据类型改成VARCHAR(52) NOT NULL。

示例2：

PROCEDURE ANALYSE 分析给定查询中的列，并提供对每个字段的调节反馈：

mysql> SELECT CountryCode, District, Population

-> FROM City PROCEDURE ANALYSE(250,1024)\G

默认设置通常建议使用ENUM 类型来优化表的设计。如果确定不想在分析列时使用PROCEDURE ANALYSE() 所建议的ENUM 值，请使用非默认参数。

第一个参数是分析ENUM 值是否适当时要考虑的不同元素数。此参数的默认值为256。

第二个参数是用于收集不同的值以供分析的最大内存量。此参数的默认值为8192，表示8 KB。如果为此参数设置值，则PROCEDURE ANALYSE() 无法检查不同的值以建议使用ENUM 类型。如果PROCEDURE ANALYSE() 无法存储可接受范围内的候选ENUM 值（在参数设置的限制内），则不会建议对该列使用ENUM 类型。

本示例建议对City.CountryCode 列使用CHAR(3) 类型。另一方面，如果使用默认参数，则PROCEDURE ANALYSE() 将建议ENUM('ABW','AFG',...,'ZMB','ZWE')，这是一种包含超过200 个元素的ENUM 类型，其中针对每个相应的CountryCode 值都包含一个不同值。

1.5.        EXPLAIN

EXPLAIN 命令描述MySQL 打算如何执行特定的SQL 语句，不返回数据集的任何数据，并提供有关MySQL 打算如何执行该语句的信息

使用EXPLAIN 可检查SELECT、INSERT、REPLACE、UPDATE 和DELETE 语句。 将EXPLAIN 置于语句之前，EXPLAIN SELECT ...、EXPLAIN UPDATE...；

EXPLAIN 将为语句中使用的每个表生成一行输出。该输出包含以下列：

— table：输出行所对应的表

— select_type：查询中使用的选择类型。SIMPLE 意味着查询未使用UNION 或子查询。

— key：优化程序所选择的索引

— ref：与索引比较的列

— rows：优化程序所检查的行的估计数目

— Extra：优化程序提供的每个查询的其他信息

有关输出列的完整论述，请访问：

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

使用EXPLAIN EXTENDED ... 可查看优化程序提供的其他信息。有关完整论述，请访问：

http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html

例如，以下查询可联接两个表的字段并执行聚合：

mysql> SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population,

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC;

+--------+--------------+---------------+

| Cities | Population | Continent |

+--------+--------------+---------------+

| 1765 | 900934498400 | Asia |

| 580 | 95052481000 | North America |

| 842 | 55127805400 | Europe |

| 470 | 48533025000 | South America |

| 366 | 16179610000 | Africa |

| 55 | 307500750 | Oceania |

+--------+--------------+---------------+

6 rows in set (0.01 sec)

以下输出显示了在查询前使用EXPLAIN 的结果：

mysql> EXPLAIN SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population,

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: Country

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 239

Extra: Using temporary; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: City

type: ref

possible_keys: CountryCode

key: CountryCode

key_len: 3

ref: world_innodb.Country.Code

rows: 9

Extra: Using index

2 rows in set (0.00 sec)

EXPLAIN 格式

EXPLAIN 输出也提供其他格式：

1）可视化EXPLAIN，图形格式的输出在MySQL Workbench 中提供

2）EXPLAIN FORMAT=JSON，JSON 格式的输出，当要将EXPLAIN 输出传递给程序以供进一步处理/分析时十分有用

JSON（JavaScript Object Notation，JavaScript 对象表示法）是一种简单的数据交换格式。以下输出显示了在EXPLAIN 语句中使用FORMAT=JSON 的结果：

mysql> EXPLAIN FORMAT=JSON SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population, Continent FROM Country JOIN City ON CountryCode = Code GROUP BY Continent ORDER BY Population DESC\G

*************************** 1. row ***************************

EXPLAIN: {

"query_block": {

"select_id": 1,

"ordering_operation": {

"using_filesort": true,

"grouping_operation": {

"using_temporary_table": true,

"using_filesort": false,

…

1 row in set, 1 warning (0.00 sec)

1.6.        服务器状态

1)         检查服务器状态的方法

MySQL 提供了多种查看服务器状态变量的方法：

l  在mysql 提示符下查看，命令STATUS、SHOW STATUS;

l  在终端上查看：

mysqladmin --login-path=login-path status

mysqladmin -u user -p extended-status

MySQL 通过mysql 命令STATUS 和mysqladmin 命令status 提供简短的状态消息。通过mysql 命令SHOW STATUS 和mysqladmin 命令extended-status 显示的长格式状态输出包含许多系统状态变量的值，后面的将讨论其中最重要的值。

对mysqladmin 使用选项可提供附加功能。例如，--sleep（或-i）选项可指定在迭代之间等待的秒数，并在等待该时间后自动重新执行命令。--relative（或-r）选项显示自上次迭代后每个变量的差异，而不是变量值。使用grep 等命令行工具可扩展mysqladmin 的使用方式。例如，使用以下命令可仅显示包含字符串cache_hits 的变量：

shell> mysqladmin --login-path=admin extended-status | grep cache_hits

| Qcache_hits | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_session_cache_hits | 0 |

| Table_open_cache_hits | 280 |

2)         主要状态变量

Ø  Created_tmp_disk_tables：显示磁盘上的内部临时表的数量；获取执行语句时服务器所创建的临时表数。如果该数值较高，则服务器已在磁盘上（而不是在内存中）创建多个临时表，从而导致查询执行较慢。

Ø  Handler_read_first：显示索引中第一个条目的读取次数，如果该数值较高，则服务器已执行多次完整索引扫描以完成查询请求。

Ø  Innodb_buffer_pool_wait_free：显示服务器等待干净页面的次数，等待InnoDB 缓冲池中的页面刷新后才可以完成查询请求。如果该数值较高，则未正确设置InnoDB 缓冲池的大小，因而查询性能受到影响。

Ø  Max_used_connections：显示自服务器启动以来的最大并发连接数；此变量提供非常有用的信息来确定服务器必须支持的并发连接数。

Ø  Open_tables：显示给定时间内打开的表的数量；将此变量与服务器系统变量table_cache 比较，可提供有关应该为表高速缓存预留多少内存量的有用信息。如果Open_tables 状态变量的值通常很低，请减小服务器系统变量table_cache 的大小。如果该值很高（接近服务器系统变量table_cache 的值），请增加分配给表高速缓存的内存量来缩短查询响应时间。

Ø  Select_full_join：显示执行表扫描而不是使用索引的联接数量，如果该值不是，则应该仔细检查表的索引。

Ø  Slow_queries：显示用时比long_query_time 系统变量所指定的秒数长的查询数；此状态变量取决于对long_query_time 变量（默认值为10 秒）设置的了解。如果Slow_queries 状态变量不是，请检查long_query_time 的值和慢速查询日志，并改进所捕获的查询。

Ø  Sort_merge_passes：显示排序算法所执行的合并传递次数；排序操作需要内存中的缓冲区。此状态变量计算排序操作所需的经过排序缓冲区的传递次数。如果该值较高，则可能表明排序缓冲区大小不足以执行查询的一次通过排序；请考虑增大sort_buffer_size 系统变量的值。

Ø  Threads_connected：显示当前打开的连接数；定期捕获该值可提供有关服务器何时最活跃的有用信息。使用此变量可确定执行服务器维护的最佳时间，或者可将其作为为服务器分配更多资源的依据。

Ø  Uptime：显示服务器持续运行的秒数；该值可以提供有关服务器运行状况的有用信息，例如服务器需要重新启动的频率。

1.7.        系统变量调优简介

MySQL性能调优应该首先调节查询、模式和索引，因为每次操作可获得比调节变量更多的收益；其次考虑针对服务器大小进行调节，例如内存和I/O；然后针对应用程序配置进行调节；例如，存储引擎设置，将物理RAM 的70%–85% 提供给InnoDB 缓冲池，最小化MyISAM 高速缓存和缓冲区等；最后，根据服务器负载类型（事务服务器、报告服务器）调整连接数等

一个常见的误区是认为服务器变量配置是服务器调节中最重要的部分。事实上，从花费的精力来看，优化模式、常见查询和典型数据库的索引可获得比调节变量更多的好处。

²  默认设置

Oracle 的MySQL 工程师选择默认设置来适应大多数生产系统，这些系统常常要处理频繁的小事务、许多更新和少数大型慢速查询（如用于生成报告的查询）。然而，由于MySQL 在从小型设备（如销售点系统和路由器）到具有大量内存和快速磁盘阵列的大型Web 服务器等各种系统上都在使用，您可能会发现，对于您的特定环境和工作负荷，可以从更改服务器的某些默认设置中获益。

²  InnoDB 设置

例如，在仅使用InnoDB 用户表的MySQL 专用服务器上，可以将innodb_buffer_pool_size 的值增大到占服务器总内存的较大比例(70%–85%)，同时要记住操作系统的需要，如cron 作业、备份、病毒扫描以及管理连接和任务。如果有几GB 的RAM，则还可以通过使用多个innodb_buffer_pool_instances 而获益，该设置可启用多个缓冲池，从而避免争用。

²  降低MyISAM 设置

在不将MyISAM 用作用户表的系统上，减小仅适用于MyISAM 的选项的值（例如将key_buffer_size 的值减小为16 MB 等较小值），同时要记住某些内部MySQL 操作将使用MyISAM。

²  报告系统

在用于运行少数大型慢速查询（例如用于业务智能报表的查询）的服务器上，使用join_buffer_size 和sort_buffer_size 等设置增加专用于缓冲区的内存量。虽然默认服务器设置更适合事务系统，但默认的my.cnf 文件包含这些变量适用于报告服务器的替代值。

²  事务系统

在用于支持许多反复断开并重新连接的快速并发事务的服务器上，请将thread_cache_size 的值设置为足够大的值，以便大多数新连接可以使用高速缓存的线程；这可避免创建和断开每个连接的线程时的服务器开销。

在支持多写入操作的服务器上，请提高innodb_log_file_size 和innodb_log_buffer_size 等日志设置，因为数据修改操作的性能在很大程度上依赖于InnoDB 日志的性能。请考虑更改innodb_flush_log_at_trx_commit 的值以提高每次提交的性能，但风险是：如果服务器出现故障，可能会丢失某些数据。

如果您的应用程序反复执行相同的查询（或多个相同的查询），请考虑启用查询高速缓存，并根据常见查询的结果调节其大小，方法是为query_cache_type 和query_cache_size 设置适当的值。

²  平衡内存使用

当您为每个查询或每个连接的高速缓存和缓冲区设置较大的值时，会减少缓冲池的可用大小。调节服务器的配置变量是一个平衡过程，需要从默认值开始，提供尽可能多的内存给缓冲池，然后调节与以下项最紧密相关的变量：调节目标、通过检查服务器状态识别出的问题以及通过查询性能模式识别出的瓶颈。

1)         主要服务器系统变量：

Ø  innodb_buffer_pool_size：定义InnoDB 用于缓存表数据和索引的内存缓冲区大小（以字节为单位）；要想获得最佳性能，请将此值设置为尽可能大，同时要记住值过高会导致操作系统交换页面，从而大大降低性能。如果在专用数据库服务器上仅使用了InnoDB 用户表，请考虑将此变量设置为介于物理RAM 的70% 到85% 之间的值。

Ø  innodb_flush_log_at_trx_commit：定义InnoDB 将日志缓冲区写入日志文件的频率，以及对日志文件执行刷新到磁盘操作的频率；此变量有三种可能的设置：

n  ：每秒将日志缓冲区写入磁盘一次。

n  1：每次提交时将日志刷新到磁盘；如果未发生提交，则每秒刷新一次。

n  2：将日志刷新到操作系统高速缓存中，并且每隔innodb_flush_log_at_timeout 秒（默认为一秒）刷新到磁盘一次。

Ø  innodb_log_buffer_size：定义InnoDB 用于写入磁盘上的日志文件的缓冲区的大小（以字节为单位）；此变量的默认值为8 MB。事务超过此大小会导致InnoDB 在事务提交之前将日志刷新到磁盘，从而降低性能。对于使用大量BLOB 或者在更新活动中具有较大峰值的应用程序，可通过增大该值提高事务性能。

Ø  innodb_log_file_size：定义日志组中每个日志文件的大小（以字节为单位）；对于大型数据集上的写入密集型工作负荷，请设置此变量以便所有日志文件的最大总大小（通过innodb_log_files_in_group 设置）小于或等于缓冲池的大小。大型日志文件会减缓故障恢复，但可以通过减少检查点刷新活动来提高整体性能。

Ø  join_buffer_size：定义用于使用表扫描的联接的最小缓冲区大小；对于包含无法使用索引的联接的查询，请以默认值(256 KB)为起点增大该值。运行此类查询时请更改每个会话的值，以避免设置全局设置而使无需这么大值的查询浪费内存。

Ø  query_cache_size：定义为缓存查询结果而分配的内存量；通过使用查询高速缓存，提高针对极少更改的数据发出重复查询的应用程序的性能。作为基线，请根据重复查询的数量和所返回数据的大小将此变量设置为介于32 MB 和512 MB 之间的值。请监控高速缓存命中率以确定此变量的有效性，并根据您的观察调节其值。

Ø  sort_buffer_size：定义分配给需要进行排序的会话的最大内存量；如果Sort_merge_passes 状态变量的值很高，请增大该值以提高ORDER BY 和GROUP BY 操作的性能。

Ø  table_open_cache：定义所有线程打开的表的数量；请设置该值以使其大于N * max_connections，其中，N 是在应用程序的所有查询中所使用的最大表数量。该值过高会导致出现错误“Too many open files（打开的文件太多）”。Open_tables 状态变量的值较高表示MySQL 频繁打开和关闭表，因此应该增大table_open_cache。

Ø  thread_cache_size：定义服务器应缓存以供重用的线程数；默认情况下，此变量将自动调节大小。评估Threads_created 状态变量可确定是否需要更改thread_cache_size 的值。

2)         准备调节

调节数据库服务器可以与调节乐器类比，选择要更改的值并确定一个目标，向上和向下调节该值并同时测试检测的行为，确定最佳设置。

准备调节环境要尽可能地复制生产系；要减小与正在调节的变量无关的已更改因素的影响，请在停机期间对生产服务器执行调节，或者最好在复制的系统上进行调节。

然后确定调节目标，例如每秒处理更多事务，更快生成复杂报表，通过并发连接的峰值提高性能；调节之前，请确定一个目标。所选的调节变量取决于您设置的目标。有很少几个连接的报告服务器的最佳设置与有许多连接且每秒处理数百个小型事务的事务应用服务器的最佳设置有很大的不同。内存与数据库大小的比率较高的服务器与内存较小但数据库较大的服务器相比，具有非常不同的性能特征。繁重的写入工作负荷需要的设置不同于只读系统。选择适当的变量进行调节，如缓冲区、高速缓存、日志设置等；

最后，应用程序代码，一般查询日志等方法收集代表性语句；为了最准确地模拟正在针对其进行调节的工作负荷，请收集一组有代表性的语句。从应用程序中选择查询和修改操作比例正确的语句序列。在要优化的每天或每周期间内，使用一般查询日志从生产服务器收集实际语句。

3)         练习调节

查找每个变量的最佳值的基准测试，首先将变量设置为低于其默认值的设置，然后进行基准测试，测量相关度量，如虚拟内存使用、所花费的平均时间、相关状态变量；

然后，增大变量值并重复基准测试，如果需要，刷新状态变量。

最后，将结果绘制成图，查找收益的下降点和性能的高峰，并根据所用资源和性能之间的最佳平衡来决定最终变量值。

要查看所选度量的值，请使用：

— mysqlslap 或mysql 来运行工作负荷并获取平均执行时间

— sql-bench 来运行更一般的基准测试

— mysqladmin extended-status 来获取工作负荷前后的状态变量的值

— top 等操作系统工具或/proc 文件系统来访问过程度量

如果要针对特定变量使用多个不同值运行微调基准测试，或者如果要在很长一段时间内反复运行相同的基准测试，请考虑使用脚本语言来自动化基准测试中所使用的步骤。

4)         调节示例：排序缓冲区大小

本示例显示了一系列针对具有繁重排序工作负荷的数据库的测试结果，其中，运行测试时更改了sort_buffer_size 变量。

图表显示：

— 在sort_buffer_size 从32 KB 增大到512 KB 时，Sort_merge_passes 状态变量的值（可使用mysqladmin extended_status -r 查看）急剧下降，在此之后又缓慢降低；

— 测试工作负荷所花的平均时间（可使用mysqlslap 查看）在sort_buffer_size为512 KB 时降低，在4 MB 时达到极大峰值，然后在8 MB 时下降，最终在32 MB时达到最佳性能；

— mysqld 进程的总虚拟内存（可使用top 查看）在sort_buffer_size 为512 KB时最小，此后一直到16 MB 都稳步上升，在32 MB 时急剧上升；

查询的平均时间最短时，sort_buffer_size 为32 MB，该设置使用了大量内存，而缓冲池本来可以更好地利用这些内存。在本示例中，针对测试中所使用的工作负荷、服务器和数据库的特定组合，512 KB 设置可在性能和所用内存之间提供最佳平衡。

“怎么理解MySQL性能调优”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！