万字长文浅析配置对MySQL服务器的影响
有很多的服务器选项会影响这MySQL服务器的性能，比如内存中临时表的大小、排序缓冲区等。有些针对特定存储引擎（如InnoDB）的选项，也会对查询优化很有用。
调整服务器的配置从某种程度来说是一个影响全局的行为，因为每个修改都可能对该服务器上的每个查询造成影响。不过有些选项是针对特定类型的优化的，如果你的请求没有满足条件，它将没有任何作用。
首先我们需要检查的选项是缓冲区大小（buffer size）
每个缓冲区都有其存在的特定原因，一般的规律是大缓冲区意味着高性能，不过仅当请求可以针对该缓冲区扮演的特定角色使用大容量缓存的时候。
当然，增加缓冲区大小是有代价的，下面是一些大缓冲区可能带来的影响。
•交换区
大容量缓冲区可能会导致会使用到操作系统级别的交换区从而造成性能缓慢，这取决于系统内存大小。通常情况下，MySQL服务器在它所需的所有内存都来自物理内存的时候运行最快。当它使用到交换区的时候，性能显著下降。
当为缓冲区分配的内存大小超过服务器的物理内存大小的时候就会使用到交换区。有一些缓冲区是针对每个用户线程的。要确定这些缓冲区究竟需要多少内存，可以用公式 max_connections * buffer_size 来计算。计算出所有缓冲区的内存和，并确保小于mysqld服务器可以使用的内存大小。
•启动时间
mysqld需要分配的内存越多，其启动时间就越长。
•过期数据
我们还会有伸缩性问题，大部分时候是来自线程间的缓存共享。在这些场景中，扩充缓冲区做缓存会产生内存碎片。你通常会在服务器运行数小时后发现内存碎片问题，该问题发生在旧的数据需要从缓冲区中移除以给新数据腾出空间的时候。这会导致高速运转的服务器变慢。
其次我们不仅关注性能优化选项（如优化器选项），我们还会关注一些控制高可用的选项。事务运行得越安全，就需要更多的检查和更慢地执行性能。针对这些选项，只有在你可以为了性能牺牲安全的时候才可以调优它们。
在此，当你调优分配的时候，把性能作为整体来考虑尤为重要，因为每个选项都会影响整个服务器。
MySQL服务器提供了大量的选项，我们可以通过多种方式来对这些选项进行设置。
1.在my.cnf配置文件中进行设置
2.在使用命令行启动服务器的时候设置
3.在服务器正在运行的时候使用变量来设置
其中这些变量有些是GLOBAL类型的，有些是SESSION级别的，本文将阐述一些可以创建或能够产生变化的选项，能够帮助你解决MySQL发生的一些问题。
我们可以根据变量的用途来把它们分成多个不同的组：用来设置服务器的配置目录。限制对硬件资源的使用，改变mysqld应该如何应对一个或多个场景等。依照它们分配时间的不同，它们也可以分为不同的组，例如：当服务器启动的时候，一个线程连接创建的时候， 或者当服务器启动一个特定操作的时候。
服务器选项
当我们使用mysql客户端可执行文件连接mysql服务器时，需要指定IP地址、用户名及密码等信息，这些信息就是mysql客户端程序启动时的选项，通过这些选项可以连接到具体的mysql服务端上。
对于mysql服务端，在启动时可以指定同时连入的客户端数量、客户端/服务端的通信方式、表的默认存储引擎、查询缓存的大小等信息，
例如：向服务器指定目录或文件，提醒服务器是否打开一个特定的日志等诸如此类的功能。
有俩个典型的故障排除情景是由这种选项导致的：
当某选项使用错误路径的时候，你通常能够在服务器启动的时候注意到此类问题。例如，如果你对 datadir 选项指定一条错误路径，那么 mysqld 会拒绝启动并输出有关的错误消息
[root@dba-test ~]# /export/servers/mysql/bin/mysqld --defaults-file=/export/restore/tool/general_mysql_backup_extract_tool/mysql-5.cnf --user=mysql --datadir=/export/restore/
2023-03-22T05:50:50.004095Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-03-22T05:50:50.004203Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2023-03-22T05:50:50.004237Z 0 [Note] /export/servers/mysql/bin/mysqld (mysqld 5.7.24) starting as process 4232 ...
2023-03-22T05:50:50.012400Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-03-22T05:50:50.012440Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-03-22T05:50:50.012450Z 0 [Note] InnoDB: Uses event mutexes
2023-03-22T05:50:50.012458Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2023-03-22T05:50:50.012468Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-03-22T05:50:50.012475Z 0 [Note] InnoDB: Using Linux native AIO
2023-03-22T05:50:50.013078Z 0 [Note] InnoDB: Number of pools: 1
2023-03-22T05:50:50.013294Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-03-22T05:50:50.017366Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-03-22T05:50:50.027751Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-03-22T05:50:50.030019Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-03-22T05:50:50.040071Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2023-03-22T05:50:50.040081Z 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2023-03-22T05:50:50.040290Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2023-03-22T05:50:50.040295Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2023-03-22T05:50:50.040298Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2023-03-22T05:50:50.040301Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2023-03-22T05:50:50.040305Z 0 [ERROR] InnoDB: Cannot open datafile './ibdata1'
2023-03-22T05:50:50.040311Z 0 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2023-03-22T05:50:50.040316Z 0 [ERROR] InnoDB: InnoDB Database creation was aborted with error Cannot open a file. You may need to delete the ibdata1 file before trying to start up again.
2023-03-22T05:50:50.641211Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-03-22T05:50:50.641243Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-03-22T05:50:50.641256Z 0 [ERROR] Failed to initialize builtin plugins.
2023-03-22T05:50:50.641264Z 0 [ERROR] Aborting
2023-03-22T05:50:50.641291Z 0 [Note] Binlog end
2023-03-22T05:50:50.641384Z 0 [Note] Shutting down plugin 'CSV'
2023-03-22T05:50:50.641402Z 0 [Note] Shutting down plugin 'MyISAM'
2023-03-22T05:50:50.642273Z 0 [Note] /export/servers/mysql/bin/mysqld: Shutdown complete但是，当然，如果你在系统启动文件并用守护进程的方式启动 mysqld ，那么你无法在命令行中看到这些错误信息。
[root@exps-test2 ~]# /export/servers/mysql/bin/mysqld_safe --defaults-file=/export/restore/tool/general_mysql_backup_extract_tool/mysql-5.cnf --user=mysql --datadir=/export/restore/
Logging to '/export/restore/exps-test2.err'.
2023-03-22T05:53:47.138456Z mysqld_safe Starting mysqld daemon with databases from /export/restore
2023-03-22T05:53:47.347547Z mysqld_safe mysqld from pid file /export/restore/exps-test2.pid ended在这种情况下，需要检查错误日志文件中的信息，或者，如果没有任何错误日志文件，那么需要检查操作系统日志中有关mysqld相关的消息。
打开或关闭一个特定功能的时候。例如，关闭特性的选项。
当InnoDB引擎启动失败的时候，虽然服务器成功启动，但是InnoDB引擎并没有成功加载。
如果SQL模式不包含 NO_ENGINE_SUBSTITUTION，我们仍然能够成功创建引擎为InnoDB的表：
mysql> CREATE TABLE t1(f1 int) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.03 sec)我们需要检查上面的警告信息，在这个例子中，我们在创建表的时候使用了错误的存储引擎，因此我们在尝试启动 InnoDB 引擎的时候发生了错误：
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1286 | UnKnown table engine 'InnoDB' |
+---------+------+--------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 't1' |
+---------+------+--------------------------------------------+所以如果某个依赖的功能出现问题，该功能是否存在于服务器实例上。而错误消息清楚的说明了问题出在哪里。
可更改服务器运行方式的变量
这种类型的变量会影响 MySQL 服务器如何处理用户的输入，举一个简单的例子来清楚地说明设这种这种类型的变量而带来的效果。在这个例子中，将SQL模式设置为STRICT_TRANS_TABLES使无效数据插入事务性表的尝试被拒绝，而不是被忽略。
有关硬件资源限制的选项
本分类中的配置选项能够对硬件资源的利用施加限制。它们通常有两个用途:优化性能及限制某此操作。当你希望对客户端与服务器之间的流量施加一些限制，或者防止拒绝服务攻击时，刚才提到的两种用途中，后者对该场景非常有用。它能更好地让特定用户得到更优雅的错误，因为资源不足要好过 mysqld 进程终止，因为 mysqld 进程终止后便不能处理所有传入的请求。
使用--no-defaults 选项
检查 MySQL 服务器是否没有指定选项是较容易的，即，如果它的选项全部使用默认值如果你对于mysqld如何在未配置自定义选项的场景下运行有一个粗略的猜测,那么你就可以使用--no-defaults 选项启动 MySQL 服务器并与你设置了自定义选项的情况进行比较
性能选项
这一类选项通常不会引发错误，但它们有可能对服务器的性能产生巨大的影响。通常可以在不同的真实负载的生产服务器上调整这些选项，直到找到一个适合特定环境的性能选项配置组合。
然而，当你选择了这些选项的时候，有一种可能出现的情况会导致错误，所以从配置文件中移除或降低性能配置选项的权重是有意义的。这个场景是:你的服务器遇到一个资源不足错误。最常见的情况涉及缺少内存或者文件描述符。如果服务器存在此类问题，可以使用--no-defaults 办法来找明设置了太大的选项。
欲速则不达
调整 MySQL 服务器的时候，至少是当你无法 100%确定你知道自己在做什么的时候。
这意味着，如果你认为一组配置选项可以改变 MySQL 服务器的行为，并使它更好地为你的应用程序提服务，那么你可以改变一个选项，然后进行测试，如果结果是有效的，那么你可以继续添加其他选项，以此类推，直到你已经检查完毕每一个相关选项。这可能是一个很缓慢的过程，但如果在这一过程中发生错误，你可以放心的回滚到本次变化之前，并能够迅速地让你的服务器回到工作状态。
在你调整内存缓冲区或者其他选项以限制对硬件资源的利用时，这种方式是非常重要的。但这种方式也同样能够用于服务器行为配置选项的修改。即使你对变量到底在做什么有着良好的认知，这也仅仅能让你可以更容易地发现和修复某个错误，而不是在几十种选项择中找出错误的根源。
当你在使用该方法的时候，需要保存每一个测试结果。例如，如果你正在尝试提升服务器的性能，那么你需要运行基准测试或测试查询执行时间在配置选项改变前后的变化，然后重复同样的测试，再去修改每个选项。
通过基准测试迭代优化
你也许期望 (或者相信自己会期望) 通过建立一套基准测试方案，然后不断迭代地验证对配置项的修改来找到最佳配置方案。通常我们都不建议大家这么做。这需要做非常名的工作和研究，并且大部分情况下潜在的收益是非常小的，这可能导致巨大的时间浪费而把时间花在检查备份、监控执行计划的变动之类的事情上，可能会更有意义。
即使更改一个选项后基准测试出现了提升，也无法知道长期运行后这个变更会有什么副作用。基准测试也不能衡量一切，或者没有运行足够长的时间来检测系统的长期稳定性修改就可能导致如周期性性能抖动或者周期性的慢查询等问题。这是很难察觉到的。
有的时候我们运行某些组合的基准测试，来仔细验证或压测服务器的某些特定部分，使得我们可以更好地理解这些行为。一个很好的例子是，我们使用了很多年的一些基准测试，用来理解InnoDB 的刷新行为，来寻找更好的刷新算法，以适应多种工作负载和多种硬件类型。我们经常测试各种各样的设置，来理解它们的影响以及怎么优化它们。但这不是一件简单的事一一这可能会花费很多天甚至很多个星期一一而且对大部分人来说这没有收益，因为服务器特定部分的认识局限往往会掩盖了其他问题。例如，有时我们发现，特定的设置项组合，在特定的边缘场景可能有更好的性能，但是在实际生产环境这些配置项并不真的合适，例如，浪费大量的内存，或者优化了吞吐量却忽略了崩溃恢复的影响。
如果必须这样做，我们建议在开始配置服务器之前，开发一个定制的基准测试包。你必须做这些事情来包含所有可能的工作负载，其至包含一些边缘的场景，例如很庞大很复杂的查询语句。在实际的数据上重放工作负载通常是一个好办法。如果已经定位到了一个特定的问题点一一例如一个查询语句运行很慢一一也可以尝试专门优化这个点，但是可能不知道这会对其他查询有什么负面影响。
最好的办法是一次改变一个或两个变量，每次一点点，每次更改后运行基准测试，确保运行足够长的时间来确认性能是否稳定。有时结果可能会令你感到惊讶，可能把一个变量调大了一点，观察到性能提升，然后再调大一点，却发现性能大幅下降。如果变更后性能有隐患，可能是某些资源用得太多了，例如，为缓冲区分配太多内存、频繁地申请和释放内存。另外，可能导致 MySQL 和操作系统或硬件之间的不匹配。例如，我们发现 sort_buffer_size的最佳值可能会被 CPU 缓存的工作方式影响，还有 read_buffer_size需要服务器的预读和 I/O 子系统的配置相匹配。更大并不总是更好，还可能更糟糕。一些变量也依赖于一些其他的东西，这需要通过经验和对系统架构的理解来学习。
影响服务器与客户端行为的选项
服务器相关选项
这些选项影响所有的连接与语句。
•限制与 max_ 变量*
如果你发现mysqld限制了你发送语句或返回结果的大小，你只须检查它们的值。
•权限
如果语句失败，你的用户是否有执行它的权限，或特定数据库或者表的权限。
•SQL模式
当遇到“奇怪”的查询结果时，请检查 SQL 模式并分析它是否影响查询。
•字符集与排序规则
每当你怀疑某些关于字符集与排序规则的事情有错误时，请运行以下两个查询，然后分析查询结果与上下文。
mysql> SHOW VARIABLES LIKE '%char%';
mysql> SHOW VARIABLES LIKE '%coll%';通常的安全规则是获取所有的 character_set_* 变量、 collation_* 变量，并且创建选项相同的任何表与一起协同的连接。设置客户端选项最简单方式使用 SET NAMES 语句。
当在排序或者比较过程中遇到问题时，请检查字符集选项与表的定义。
•操作系统处理 lower_case* 参数
lower_case_filesystem 与 lower_case_table_names 选项跟字符集选项的作用非常相似。这些变量确定操作系统如何处理数据库对象的大小写情况。
最好不要修改它们的值，特别是操作系统不区分大小写时。
•初始 SQL
这些选项确定服务器在不同的时间是否应该自动执行某些 SQL 语句
如果指定，此变量将命名一个文件，其中包含在启动过程中要读取和执行的 SQL 语句。每条语句必须在一行中，并且不应包含注释。
服务器为每个连接的客户端执行的字符串。该字符串由一个或多个 SQL 语句组成，以分号字符分隔。
该变量类似于init_connect，但是是一个字符串，每次复制 SQL 线程启动时由副本服务器执行。字符串的格式与变量的格式相同init_connect。该变量的设置对后续START SLAVE语句生效。
这个重要选项限制 MySQL 服务器同时打开文件句柄的数量。限制数量越高，打开的表文件与临时表越多，因此处理的并发连接量数越多。如果这个限制在你的环境中设置得太低，在你试图连接、打开一个表或者执行一个需要创建临时表的查询时就会出现错误。
是否向错误日志生成额外的警告消息。
当此选项打开(非零)时，就会在服务器的错误日志文件中写人警告信息。它们不是在 SOL 执行期间发出的警告,而是显示服务器内到底是怎么回事的调试消息。如果设置为 2，此选项告诉服务器记录连接错误。当你正在对客户端无法连接或正在失去连接的情况做故障排除时，这非常重要。日志并非总是能找到问题所在，但其警告消息往往对要做什么能给出一些启发。当使用同步复制时，在主服务器上开启此选项非常重要，因为你能确定从服务器 IO 线程何时失去连接。反过来它是网络故障的一种症状，这在将来可能导致更严重的问题。
当在从服务器上设置为 1(默认值) 时，它将输出自己的诊断消息，例如:在二进制日志和中继日志中的位置及其复制状态。从 5.1.38 版本开始，在基于语句模式的同步复制中，需要启用此选项，以便在从服务器输出不安全语句的信息。( 5.1.38版本之前，从服务器在任何情况下都会输出此类消息。) 从 5.1.38 版本开始，可以关闭此选项( 设置为 0)，来丢弃你确定不需要该消息的日志。
服务器将错误、警告和注释消息写入错误日志的详细程度。下表显示了允许的值。默认值为 3。
log_error_verbosity value
允许的消息
1
错误讯息
2
错误和警告消息
3
错误、警告和信息消息
log_error_verbosity在 MySQL 5.7.2 中添加。它优于旧的系统变量，应该使用它来代替旧的log_warnings系统变量。log_warnings有关该变量如何与 相关的信息，请参阅 的描述log_error_verbosity。特别是，赋值给log_warnings赋值给log_error_verbosity，反之亦然。
复制选项
这些选项确定了主从服务器之间的关系。
•binlog-* 与 replicate-* 过滤器
通过 binlog-do-、 replicate-do-、 binllog-ignore-* 与 replicate-ignore-* 选项，在复制过程中， MySQL有能力过滤对象。 binlog-* 选项减少在主服务器上写入二进制日志文件的事件，而 replicate-* 指定在从服务器上记录到二进制日志。从服务器还有replicate-wild-do-* 与 replicate- wild-ignore* 选项，二者允许通过模式匹配，指定哪些应该或哪些不应该同步。
•二进制日志格式a. binlog_format
变量允许你选择复制的格式：STATEMENT、ROW、或MIXED。这是一个动态变量，它能在 SESSION 级别调整。
◦binlog_direct_non_transactional_updates
此选项指定何时非事务表更新应该写入二进制日志。
默认情况下，当使用事务时，MySQL将非事务表的更新写入事务缓存里，仅当事务提交后，才把缓存刷新到二进制日志里。这样做以便从服务器更有可能与主服务器数据最终一致，即使依赖事务表中的数据来更新非事务表，并且主服务器在许多并发线程中同步更新相同的表。
这是一个动态变量，可以在 SESSION 级别改变它，所以，可以在特定语句下使用它。它的工作原理决定了它只能在基于语句模式的复制中オ生效。
•log_bin_trust_function_creators
这个选项告诉 mysqld 当用户没有 SUPER 权限却试图创建一个不确定的函数时，不要触发警告。
•binloig_cache_size 与类似选项
此条目包括以下选项：
在写入二进制日志之前，这些缓存保存在事务期间提交的事务与非事务语句。如果一个事务需要超过这个字节数的内存，服务器会生成一个 “Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage” 错误。
检查 Binlog_cache_use、Binlog_stmt_cache_use、Binlog_cache_disk_use 与 Binlog_stmt_cache_disk_use状态变量来找出 binlog缓存使用的频率以及事务大小超过Binlog_cache_use与 Binlog_stmt_cache_use的频率。当事务大小操作缓存大小时，将会创建临时表来存储事务缓存。
此选项允许从服务器SOL线程即使遇到某类错误时还能运行。例如，主服务器运行在宽松的 SQL 模式而从服务器却有一个严格的SQL模式，当插入字符串到整数字段时，由于数据格式不一致，而报告1366( ERROR1366(HY000): Incorrect integer value)错误，可以设置 slave skip errors ，以便从服务器不会出现故障。
这个选项可能导致主服务器从服务器数据不一致而又很难诊断，所以，如果你遇到这样的问题，请检查该选项是否没有设置。
该选项使从服务器服务器只读。这意味着，仅仅只有从库SQL线程才能更新其数据，而其他连接只能读数据。该选项对于保持从服务器数据的一致很重要。然而，这个选项并不能限制具有 SUPER 权限的用户更改表。另外，所有用户仍允许创建临时表。
该选项使服务器甚至禁止拥有 SUPER 。
引擎选项
本节主要介绍 InnoDB 相关选项。
该选项用于生成自动增量值的锁定模式。
Innodb默认在共享表空间中存放表和索引数据。使用此选项，你可以告知它将表的索引和数据存放在单独的文件里。共享表空间仍然用来存放表定义。此选项在设置后创建的表上生效；之前创建的表依然使用共享表空间。
此变量定义了 InnoDB 是如何处理 LOCK TABLES 语句发出的表锁请求。默认（当设置了这刻返回并且内部将表锁住。当关闭时（设置为0），它会接收 LOCK TABLE 语句，线程直到所有锁释放后才从 LOCK TABLES …WRITE返回。
这是 InnoDB 等待行锁直到放弃的秒数。在 innodb_lock_wait_timeout秒后，它会返回错误 “ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction” 至客户端。我经常看到人们将这个变量设得很大来防止查询失败，这只会导致更严重的问题,因为许多阻塞的事务会互相锁住。尝试在应用程序层处理锁等待错误，并不要将它设置得过高。此参数的最佳值取决于应用程序，通常应该大约为正常事务所消耗的时间。它的默认值是50秒，对于需要立即返回结果的应用程序有些大。
当査询因锁等待错误而中断时，只有最后一条语句回滚了，整个事务还没有中止。如果将选项设置为1你将会改变此行为。这种情况下事务会在锁等待超时后立刻回滚。
指定 InnoDB 是否应该使用 Linux下原生的AIO接口，或者是自己来实现,称作 “模拟AIO“。如果设置 innodb_use native_aio，lmoD将分发IO请求至内核。这提高了可扩展性因为比起模拟AIO新内核能够处理更多的并行IO请求。
此选项默认开启,在正常操作下不应该改变。
运行大量InnoDBI/O 线程，尤其是在同一台服务器上运行多个此类实例，可能会超出 Linux 系统的容量限制。在这种情况下，您可能会收到以下错误：
EAGAIN: The specified maxevents exceeds the user's limit of available events.您通常可以通过将更高的限制写入 来解决此错误/proc/sys/fs/aio-max-nr。
•innodb_locks_unsafe_for_binlog
此变量定义 InnoDB如何使用间隙锁来搜索和扫描索引。默认值（设为0）下，间隙锁开启。如果设为1，大多数操作下会禁用间隙锁。其工作原理类似于隔离级别中的 READ COMMITTED，但由于不太好调节应尽量避免。即使它允许你来处理锁问题，当并行事务插入新行至间隙时它也会带来新的问题。所以推荐用 READ COMMITTED替代它。这个变量不能设置为 SESSION 级别,它会彩响所有事各
连接相关的选项
超时
这是 InnoDB 等待行锁直到放弃的秒数。在 innodb_lock_wait_timeout秒后，它会返回错误 “ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction” 至客户端。我经常看到人们将这个变量设得很大来防止查询失败，这只会导致更严重的问题,因为许多阻塞的事务会互相锁住。尝试在应用程序层处理锁等待错误，并不要将它设置得过高。此参数的最佳值取决于应用程序，通常应该大约为正常事务所消耗的时间。它的默认值是50秒，对于需要立即返回结果的应用程序有些大。
它适用于元数据锁。这个锁对所有需要元数据锁的操作有效：DML（数据操纵语言语句,如 INSERT、 UPDATE和 DELETE），DDL、LOCKTABLES等。默认值是3153600秒，也就是一年。所以默认情况下,有效MDL锁永远不会解除。然而，可以更改值为大于1秒的任何值。它是一个动态变量,可以在SESSION级别更改。
这个超时使用在 MySQL服务器在响应之前等待连接数据包的秒数。从5.1版本开始,此值默认设置为10秒。
服务器在关闭交互式连接之前等待其活动的秒数。
在断开连接前等待任何客户端中活动的时间。如果客户端是交互式的并且 interactive_timeout的值不同于wait_timeout,则以 interactive_timeout为准。
来自主机的连续连接请求在没有成功连接的情况下被中断后max_connect_errors，服务器阻止该主机进一步连接。如果在先前的连接中断后，在少于max_connect_errors尝试次数的时间内成功建立了来自主机的连接，则主机的错误计数将被清除为零。要取消阻止被阻止的主机，请刷新主机缓存。
允许的最大并发客户端连接数。最大有效值是的有效值和实际设置的值中的较小者。
检查客户端连接时是否解析主机名。如果这个变量是OFF，mysqld 在检查客户端连接时解析主机名。如果是ON，mysqld 只使用 IP 号码；在这种情况下，Host授权表中的所有列值都必须是 IP 地址。
从客户端写入 MySQL服务器等待应答的时间。例如,此超时会在客户端执行大的插入操作时起作用。
客户端从服务器中读取时等待应答的时间。例如,当客户端发送一个 SELECT查询读取结果,如果客户端等待一段时间未收到数据,这个超时会断开此连接。如果客户端需要在处理结果前做一些工作,检查工作的持续时间是否长于这个超时。
与安全相关的选项
--skip-grant-tables导致服务器不读取mysql系统数据库中的授权表，从而在根本不使用特权系统的情况下启动。这使任何有权访问服务器的人都可以不受限制地访问所有数据库。
不允许使用GRANT语句来创建用户,除非用户有mysql.user 表的INSERT权限才可以。
如果启用此变量，则服务器会阻止尝试使用以旧（4.1 之前）格式存储密码的帐户的客户端连接。启用此变量以防止所有使用旧格式的密码（因此通过网络进行不安全的通信）。
此变量已弃用；希望在未来的 MySQL 版本中将其删除。它始终处于启用状态，尝试禁用它会产生错误。
限制LOAD FILE函数以及LOAD DATA和SELECT… INTO OUTFILE语句只能使用指定目录。
与性能相关的选项
缓冲区和最大值
这是为连接操作分配的最小缓存大小，这些连接使用普通索引扫描、范围扫描，或者连接不使用索引。两表之间进行全连接时分配缓存。因此，连接两个表的一条查询分配一块缓存，连接3个表的一个分配两块查询缓存，以此类推。这个选项可在 SESSION级使用，能对于特定连接设置。
为了查出是否需要增加join_buffer_size,可以检查 Select_scan状态选项,它包括第一张表执行完整扫描的连接数量,同样 Select_ full_range_ join,它包含使用范围搜索的连接数量。这些状态变量的值不会随着 join buffer size的值的变化而变化,这样你可以利用它们来查出是否需要大的 join buffer size,而不是衡量该值改变后的有效性
服务器在客户端连接后立刻创建的缓存大小,用来保存请求和结果。根据需要这个大小可以增长至 max_allowed_packet正常情况下不用改变默认值(16384字节)，但当设置 max_connections选项时要记住此值。
此缓存为语句解析和执行而分配。语句间缓存是不释放的。如果运行复杂查询,增加缓存是合理的,这样 mysqld不会在执行查询的时候在分配内存上耗时。增加此大小到最大查询的字节数。
该变量是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
此变量用来控制在排序和发送结果至客户端之间存放读取结果的大小。大的值能提高包含ORDER BY的查询的性能。
每个线程需要排序的时候会分配此缓存。查明你是否需要增加此缓存的大小，检查 ort merge_ passes状态变量。也可以检查查明你执行了多少个排序操作。sort_range、 sort_rows和sort_scan来这些状态变量显示了排序操作的数量。
为了找出缓存合适的大小，需要检查一条或多条查询排序的行数，并乘以行大小。或者简单地设置不同的值直到sort_merge_passes停止增长。
sort_buffer_size缓存经常会分配,所以大的 GLOBAL值会降低性能而不是增加性能。因此,最好不要设置此选项为 GLOBAL变量,而是当需要时使用 SET SESSION增加它。
如果启用，则sql_buffer_result强制将语句的结果SELECT放入临时表中。这有助于 MySQL 尽早释放表锁，并且在需要很长时间才能将结果发送到客户端的情况下非常有用。默认值为OFF。
为了找出查询是否在发送结果集上消耗过多时间，执行 SHOW PROCESSLIST来检查查询在“ Sending data”状态下的时间。
为将来使用缓存起来的线程数量。当一个客户端断开连接时,通常其线程也被销毁。如果该选项设置为正值 N,那么连接断开后 N个线程将被缓存起来。在具有良好线程实现的系统上，该选项不能显著地提高性能，但是，对于一个应用使用成百上千连接的情况，还是很有用的。
每个线程的栈大小。如果该变量设置过小，将会限制SQL语句的复杂性、存储过程的递归深度，以及服务器上其他内存消耗型的操作。对于大部分安装来说,默认值(32位系统是192KB;64位系统是256KB)就可以。如果类似"Thread stack overrun"的错误消息，请增大该参数。
内存中，内部临时表的最大值。服务器默认设置为max_heap_table_size和tmp_table_size二者中的最小值。如果你有足够的内存,并且Created_tmp_disk_tables状态变量在增大，请增大该变量。把需要临时表的所有结果集放在内存中，可以大大提高性能。
MySQL服务器存储查询及其结果集的缓存大小。默认情况下，查询缓存是禁用的。于5.7.20版本之后弃用。
存储在缓存中的表定义的数量。当表数量很大时，可以增大该值。如果需要，可以调整该值，以便最近的表刷新( FLUSH TABLES)后,保持 Opened_table_definitions于或等于Open_table_definitions。
存储在缓存中的表描述符的数量。调整该值,以便 Opened_tables仍小于或等于Open_tables。
控制优化器的选项
这些变量可以在 SESSION 级别设置，所以，你可以实验它们是怎样影响特点查询的。
如果该变量设为on，优化器删除即时搜索发现的不太有效的计划；如果设置为off，优化器使用详尽的搜索。默认值为1(on)。如果你怀疑优化器选择的不是最优计划，你可以改变它的值。
优化器搜索的最大深度。该值越大，优化器越有可能为复杂的查询找到最优计划。提高该值的代价就是优化器在搜索计划时的时间开销增大。如果设置为0，服务器会自动选择一个合理的值，默认值为62(最大值)
该变量变量可以控制优化器的行为。
启用或禁用索引合并优化，该优化几个从合并扫描中获取行记录,并把结果合并为一条记录。在 EXPLAIN的结果输出中，Merge列显示的就是这个选项。
启用或禁用索引合并交叉访问算法
当 where从句包含key表示的范围条件并且与AND关键字时，将会使用该算法。
如：key_coll < 10 AND key_col2 = 'foo'
使 key_col2 = 'foo' 得到唯一值，优化器也把它当做范围条件
启用或禁用索引合并联合访问算法，当 where从句包含 key 表示的范围条件和OR关键字时，会用到该算法。
如：key_col1 = 'foo' OR (key_co12 = 'bar' AND key_col3 ='baz')
启用或禁用索引合并排序联合访问算法，当 where从句包含key表示的范围条件和OR关键字时，将使用该算法,但是,不会应用索引合并联合访问算法,如(key coll >10 OR key co12 ='bar)AND key co13
对于估计可能超过一定限制的 select 语句，(该选项)阻止优化器进行优化(如查看多于 max_join_size 的行记录 )。当调试找出没有使用索引的查询时，该选项会有很大帮助。
当无法使用索引时，如果对 ORDER BY 进行条件优化，MySQL 将使用文件排序算法。该算法有两个变体。原始算法读取所有匹配的行记录，在缓存里存储战对的键值和行指针，该缓存大小受 sort_buffer_size 限制。缓存中的值排序后，该算法再次读取表记录，但是，这次按照一定顺序读取。该算法的缺点是两次读取行记录。
改进后的方法是读取整个行记录到缓冲区，然后排序键值，从缓冲区中读取行记录。该方法的问题是结果集通常超过sort_buffer_size，所以，对于大数据集，磁盘I/O 操作使得该算法很慢。max_length_for_sort_data 变量限制键值和行记录指针对的大小，因此原始算法适用于键值和行指针对中额外列的总大小超过了该限制的情况。
磁盘活动多，并且 CPU 活动少是需要调低该变量的一个信号。
根据表扫描必须检查的记录行数量，为使用键值而不是表扫描设置阈值。设置该参数为一个较小的值，比如 100，在表扫描时，可以强制优化器优先查看索引。
设置对 BLOB 或 TEXT 值排序时用到的初始字节数，后面的部分将被忽略。
与引擎相关的选项
自适应InnoDB哈希索引是启用还是禁用。根据您的工作负载，可能需要动态启用或禁用自适应哈希索引以提高查询性能。由于自适应哈希索引可能并非对所有工作负载都有用，因此在启用和禁用它的情况下使用实际工作负载进行基准测试。
innodb_additional_mem_pool_size
InnoDB 用来存储数据字典信息和其他内部数据结构的内存池的大小。在 MySQL 5.7.4 中删除。
用于生成 自动增量值的 锁定模式 。允许的值为 0、1 或 2，分别表示传统、连续或交错。默认设置为 1（连续）。
InnoDB 为存储数据、索引、表结构、自适应散列索引等分配的内存大小，这是影响 innodb 性能最重要的选项。可以将其设置为物理内存的 80%。理想情况下，该缓冲区足够大，以致可以包含所有活动的 InnoDB 表和额外空间。同时，也要把其他缓冲区计算在内，寻找一个好的平衡。
匹配Innodb buffer pool %的，状态变量显示 InnoDB 缓冲池的当前状态。
该变量的设置需要经过实际环境中非常严谨的验证。
该选项设置 InnoDB 缓冲池应切分的实例数量。每个实例有它自己的空闲列表、刷新列表、使用LRU算法的存储对象的列表，以及其他数据结构，并且受到自身互斥体的保护。设置该变量大于 1，可以提高大型系统的并发性能。每个实例的(缓中区)大小是 innodb_buffer_pool_size/innodb_buffer_pool_instances，并且至少是1GB。如果 innodb_buffer_pool_size小于 1GB，该选项不生效。
innodb_buffer_pool_instances 切分缓冲区互斥体，所以，如果有 8个或者更多并行SESSION 同时访问 InnoDB 缓冲池，该选项可以设置为 4~16。该选项取决于innodb_buffer_pool_size值和可用的内存。
默认情况，InnoDB 使用校验和验证磁盘上所有页。该选项可以立即确定数据文件是否由于磁盘坏道或者其他原因而损坏。通常需要开启该功能，但是，在很少的情况下，当不关心数据时( 比如，只读从服务器，不提供(线上) 服务，只做备份 )，关闭该功能，可以提升性能。innodb_checksums已弃用，替换为innodb_checksum_algorith。
指定如何生成和验证存储在InnoDB表空间的磁盘块中的校验和。
可以同时提交事务的线程数量，默认值为 0( 没有限制 )。
InnoDB 内部同时运行的线程数量，不要把它和 MySQL 服务器创建的连接线程的数量混淆。默认值是 0:不限制并行或不检查并行。
尽管大量线程并行运行一般意味着高性能，但是，如果同时并行运行很多用户会话，你可能遇到互斥体争用，如果同时运行的用户线程不超过 16 个，通常不用担心该参数。如果有更多用户 SESSION，可以通过查询 Performance Schema 或者SHOW ENGINE INNODBMUTEX 来监控互斥锁。
如果出现互斥体争用，可以尝试限制该变量为 16 或 32，或者把 mysqld 进程放置到 Linux 的任务集里或者 Solaris 的处理器集里，同时限制它为更少的内核而不是所有内核。
当允许一个线程进人InnoDB 时，它接收 innodb_concurrency_tickets张并发“票”( ticket)，这些票允许线程离开和重新进人 InoDB，直到它使用完这此票。
默认值是 500。用完这些票后，把线程放置到等待队列中，以获取一组新票。
默认情况下，InnoDB 分两次存储数据: 第一次写人双写缓冲，第二次写人数据文件。像innodb_checksums一样，对于数据安全不是最重要的场景，这个安全选项可以关闭 of，以提升性能。
Innodb dblwr_writes 和 Innodb_dblwr pages written 状态变量分别显示两次写操作的数量和写页的数量。
•innodb_flush_log_at_trx_commit
定义何时把更改写人(重做日志文件以及刷新到磁盘。如果设置为 1(默认值)在每个事务提交时更改均会写人和刷新到磁盘。为了得到更好的性能，可以设置该值为 0( 每秒写人日志文件和刷新到磁盘，而每个事务提交时，不做操作)或者2( 每次提交事务时写人日志文件，但是，每秒刷新到磁盘 。注意，只有该选项为1时才符合 ACID 事务要求的。
Innodb_os_log_fsyncs 状态变量存储 fsync() 到日志文件的操作次数。Innodb_os_log_pending_writes 包含挂起的 fsync0写次数。Innodb_log_writes 和 Innodb_os_log_pending_writes分别包含写入次数和挂起的写次数。
默认情况下，fdatasync()是用来刷新数据文件的，fsync()是用来刷新日志文件到磁盘的，该值可以更改为以下值中的一个:
O_DSYNC
操作系统使用 O_SYNC 打开和刷新日志文件，同时使用 fsync()刷新数据文件
O_DIRECT
操作系统使用 O_DIRECT打开数据文件，并且使用 fsync()刷新数据文件。更改innodb_flush_method变量的值，或者提升性能或者降低性能，所以，在(生产)环境中，需要谨慎测试它。
后台 InnoDB 任务执行的 I/O 活动的上限。对于大多数现代系统来说，默认值 200是个不错的选择。但是，可以根据系统的 I/O 吞吐量，调整该值。在快速存储器上增大该值，才有意义。
InnoDB 用来把日志文件写人磁盘的缓冲区大小。当缓冲区满时，必须等待日志刷新到磁盘上后，才能继续进行操作。增大该参数，可以减少磁盘 I/O 操作，但是,只有在存在大量事务时，这才有意义。
状态变量Innodb_log_waits 包含缓冲区因太小而需要的 I/O 等待次数
每个日志文件的大小。大日志文件降低检查点的活动，节省磁盘I/O。但是，大日忐文件显著地延缓崩溃恢复过程。从 1MB 到不超过 innodb_buffer_pool_size/ innodb_log_files_in_group 的值是有合理的。所有日志文件加起来不能超过 4GB。
最佳实践是在不同的磁盘上存储日志文件、数据文件、如果使用的话，还有二进制日志文件，这样，即使一个设备故障，也不会同时丢失所有文件。
日志组中的日志文件数。以循环方式写入文件。默认（推荐）值为 2。文件的位置由innodb_log_group_home_dir指定。日志文件的组合大小（innodb_log_file_size*innodb_log_files_in_group）最高可达512GB。
该变量启用时，InnoDB自动配置以下变量：
◦innodb_redo_log_capacity或者，在 MySQL 8.0.30 之前，innodb_log_file_size和innodb_log_files_in_group。
非零值可防止缓冲池被仅在短时间内引用的数据填充，例如在完整表扫描期间。增加此值可以提供更多保护，防止全表扫描干扰缓冲池中缓存的数据。默认值为1000。
指定插入到旧子列表中的块在第一次访问后必须停留在那里的时间（以毫秒为单位），然后才能移动到新子列表。如果值为0，则插入到旧子列表中的块在第一次访问时会立即移动到新子列表，无论插入后多久进行访问。如果该值大于0，则块将保留在旧的子列表中，直到在第一次访问后至少几毫秒发生访问为止。例如，值1000会导致块在第一次访问后在旧的子列表中停留1秒，然后才有资格移动到新的子列表。
仅当使用innodb_file_per_table 时，该变量才有意义。innodb_open_files是InnoDB可以同时打开的.ibd 文件的数量。默认值是 300，该值增大到 InnoDB 所有的表的数量是有意义的。
InnoDB 读操作可以使用的 I/O 线程数。这些操作处理预读: I/O 请求以异步方式将一组页数据装进InnoDB 的缓冲池，然后清空和插人缓冲操作。默认值是 4。
InnoDB 从缓冲池中写脏数据的 I/O 线程数量。默认值是 4
服务器在收集索引值的统计信息时，处理 null 的方式。这会影响索引的基数，优化器因此生成的查询计划。
启用该变量(默认值)，每次执行元信息语句时，比如，SHOW TABLE STATUS或者 SHOW INDEX，或者当查询 InnoDB 表的INFORMATION SCHEMA 或统计数据时，InnoDB 都会更新统计信息。如果启用该变量，这些查询将和每次查询后执行 ANALYZE TABLE 有一样的效果。如果服务器频繁调用这些语句或者查询具有大量表的数据库时，可以禁用该变量。但是，当禁用该变量时，表统计数据将过时。
MySQL 优化器用来计算索引分布统计信息的抽样索引页的数量，例如调用ANALYZE TABLE。如果你怀疑基数计算不合理，请增大该值( 默认值是 8)。但是，如果启用innodb_stats_on_metadata，增大该值，打开表的时间会增加。
该变量启用时，InnoDB在检查无效或不兼容的表选项时返回错误而不是警告。
InnoDB尝试从缓冲池中刷新数据，以使脏页的百分比不超过此值。该变量设置建立了冲洗活动的目标，它不影响冲洗速度。
计算选项的安全值
通过增大缓冲区或最大值，试图优化服务器性能时，全面考虑内存使用情况是很重要的，大量缓冲区能因“内存不足”错误而导致服务器崩溃。这一节将提供一些公式帮助计算是否超出了可用内存。这部分不介绍选项本身，可以参考前面的内容或者 MySQL 手册，获取详细介绍。
服务器级选项
这些选项是 GLOBAL 的，影响所有连接和查询，一部分是服务器启动时分配的，而另部分是后来分配的，比如查询缓存，初始值是 0，(后续)不断增长，直至最大值。MySQL 服务器达到所有的限制，分配所有允许的内存，会花费很长时间。因此，需要计算 mysqld 获得的 RAM 大小，和所有的缓冲区大小保证不要超过它。
使用下面的公式计算需要为这些缓冲区分配多少 RAM(单位是 MB）。
SELECT (@@query_cache_size +@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size)/(1024*1024);服务器还有限制文件描述符数量和缓存线程数量的选项，该计算可以忽略它们，因为分配给它们的内存仅仅是系统的一个指针大小乘以分配项的数量，这个数量足够小，以至于在现代系统上可以忽略。在这里列举一下，仅供参考:
线程级选项
这些选项是基于每个线程分配(内存)的，这样，服务器分配 max_connections * sum( thread options )。设置 max_connections 和这些选项，保证物理内存总量 -max_connections* sum(thread option) 服务器级选项大于0。留一些内存给第三类选项和后台操作，这些操作不受这些变量控制。
线程级选项列表如下：
使用下面的公式计算需要为它们分配多少内存(单位是 MB):
SELECT @@max_connections * (@@global.net_buffer_length + @@thread_stack + @@global.query_prealloc_size + @@binlog_cache_size + @@binlog_stmt_cache_size) / (1024 * 1024)为特定操作分配的缓冲区
当服务器执行特殊操作时，根据需要分配的缓冲区。很难计算出分配的内存大小。分析查询，找出哪些需要很多资源，然后像下面这样计算:
缓冲区大小 * 为特定查询分配的缓冲区数量 * 并行执行的查询数量
对于所有变量，这样计算，求出总和。
只要对于大多数查询是足够的，保持这些选项(的值)小一点。如果一个特定查询需要更多内存，只对于这个会话增加这个变量的值。
以下选项针对每个线程分配一次(缓冲区)：
以下选项针对每个线程分配多次(缓冲区)：
可以使用以下公式计算 MySQL 为每个选项分配的内存的最大数量( 单位是 GB)
# set @join_tables = YOUR_ESTIMATE_PER_THREAD;
# set @scan_tables = YOUR_ESTIMATE_PER_THREAD;
# set @tmp_tables = YOUR_ESTIMATE_PER_THREAD;
SELECT @@max_connections * (@@global.read_rnd_buffer_size +@@global.sort_buffer_size + @@myisam_mmap_size + @@global.myisam_sort_buffer_size + @@global.bulk_insert_buffer_size + @@global.preload_buffer_size + @@global.join_buffer_size * IFNULL(@join_tables,1) + @@global.read_buffer_size * IFNULL(@scan_tables,1) +@@global.tmp_table_size * IFNULL(@tmp_tables, 1)) / (1024 * 1024 * 1024);这里给出一个综合的公式，计算 MySQL 安装时最多可以使用的内存(单位是 GB )
# set @join_tables = YOUR_ESTIMATE_PER_THREAD;
# set @scan_tables = YOUR_ESTIMATE_PER_THREAD;
# set @tmp_tables = YOUR_ESTIMATE_PER_THREAD;
SELECT (@@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size + @@max_connections * (@@global.net_buffer_length + @@thread_stack + @@global.query_prealloc_size + @@global.read_rnd_buffer_size + @@global.sort_buffer_size + @@myisam_mmap_size + @@global.myisam_sort_buffer_size + @@global.bulk_insert_buffer_size + @@global.preload_buffer_size + @@binlog_cache_size + @@binlog_stmt_cache_size + @@global.join_buffer_size * IFNULL(@join_tables,1) + @@global.read_buffer_size * IFNULL(@scan_tables, 1) +@@global.tmp_table_size * IFNULL(@tmp_tables, 1))) / (1024 * 1024 * 1024)参考资料
《高性能MySQL》
作者：京东物流 张泽龙
来源：京东云开发者社区 自猿其说Tech
手机扫一扫
移动阅读更方便
你可能感兴趣的文章