Products
GG网络技术分享 2025-11-23 07:47 7
前言：
MySQL肯定是Zui流行啦！ZuoWEB后端数据库的。虽然NOSQLZui近被提到hen多，但是大部分架构师还是会选择MySQL来Zuo数据存储。本文作者梳理MySQL性Neng调优的15个重要变量，又不足需要补充的还望大佬指出，说白了...。
Ru果你Yi经在用MySQL .6或者5.， 并且你的数据表dou是InnoDB，那么表示你Yi经设置好了。Ru果没有， 躺赢。 确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB。
为什么？简单说主要原因是InnoDB是MySQLZui好的存储引擎 – 它支持事务， 嗯，就这么回事儿。 高并发，有着非常好的性Neng表现。
这个是InnoDBZui重要变量。其实吧，Ru果你的主要存储引擎是InnoDB，那么对于你，这个变量对于MySQL是Zui重要的。
实际上... 大体上， innodb_buffer_pool_size指定了MySQL应该分配给InnoDB缓冲池多少内存，InnoDB缓冲池用来存储缓存的数据，二级索引，脏数据以及各种内部结构如自适应哈希索引。
根据经验，在一个独立的MySQL服务器应该分配给MySQL整个机器总内存的80%。Ru果你的MySQL运行在一个共享服务器， 他急了。 或者你想知道InnoDB缓冲池大小是否正确设置，详细请kan这里。
InnoDB重Zuo日志文件的设置在MySQL社区也叫Zuo事务日志。直到MySQL .8事务日志默认值innodb_log_file_size=5M是唯一大的InnoDB性Neng杀手。从MySQL .8开始，默认值提升到48M,但对于许多稍繁忙的系统，还远远要低。
根据经验，你应该设置的日志大小Neng在你服务器繁忙时Neng存储1-2小时的写入量。Ru果不想这么麻烦，那么设置1-2G的大小会让你的性Neng有一个不错的表现。这个变量也相当重要，geng详细的介绍请kan这里，记住...。
翻旧账。 默认下innodb_flush_log_at_trx_commit设置为1表示InnoDB在每次事务提交后马上刷新同步数据到硬盘。Ru果你使用autocommit，那么你的每一个INSERT, UPDATE或DELETE语句dou是一个事务提交。
同步是一个昂贵的操作，主要原因是它涉及对硬盘的实际同步物理写入。所以Ru果可Neng，并不建议使用默认值。
两个可选的值是0和2:
* 0表示刷新到硬盘， 但不同步
* 2表示不刷新和不同步
所以你Ru果设置它为0或2，则同步操作每秒施行一次。所以明显的缺点是你可Neng会丢失上一秒的提交数据。比如你的事务Yi经提交了但服务器马上断电了那么你的提交相当于没有发生过，你看啊...。
显示的，对于金融机构，如银行，这是无法忍受的。不过对于大多数网站， Ke以设置为innodb_flush_log_at_trx_commit=|，即使服务器到头来崩溃也没有什么大问题。毕竟仅仅在几年前有许多网站还是用MyISAM，当崩溃时会丢失30s的数据，弄一下...。
优化一下。 那么0和2之间的实际区别是什么？性Neng明显的差异是Ke以忽略不计，主要原因是刷新到操作系统缓存的操作是非常快的。所以hen明显应该设置为0， 万一MySQL崩溃，你不会丢失任何数据，主要原因是数据Yi经在OS缓存，到头来还是会同步到硬盘的。
Yi经有大量的文档写到sync_binlog， 以及它和innodb_flush_log_at_trx_commit的关系，下面我们来简单的介绍下：
a) Ru果你的服务器没有设置从服务器，而且你不Zuo备份，那么设置sync_binlog=0将对性Neng有好处，希望大家...。
在理。 b) Ru果你有从服务器并且Zuo备份， 但你不介意当主服务器崩溃时在二进制日志丢失一些事件，那么为了geng好的性Neng还是设置为sync_binlog=.
c) Ru果你有从服务器并且备份，你非常在意从服务器的一致性，以及Neng及时恢复到一个时间点，那么你应该设置innodb_flush_log_at_trx_commit=，并且需要认真考虑使用sync_binlog=，我算是看透了。。
问题是sync_binlog=1代价比较高 – 现在每个事务也要同步一次到硬盘。你可Neng会想为什么不把两次同步合并成一次 想法正确 – 新版本的MySQLYi经Neng合并提交，那么在这种情况下sync_binlog=1的操作也不是这么昂贵了但在旧的mysql版本中仍然会对性Neng有hen大影响，简单来说...。
勇敢一点... 将innodb_flush_method设置为O_DIRECT以避免双重缓冲.唯一一种情况你不应该使用O_DIRECT是当你操作系统不支持时。但Ru果你运行的是Linux，使用O_DIRECT来激活直接IO。
不用直接IO， 双重缓冲将会发生，主要原因是所有的数据库geng改先说说会写入到OS缓存然后才同步到硬盘 – 所以InnoDB缓冲池和OS缓存会一边持有一份相同的数据。特别是Ru果你的缓冲池限制为总内存的50%，那意味着在写密集的环境中你可Neng会浪费高达50%的内存。Ru果没有限制为50%，服务器可Neng由于OS缓存的高压力会使用到swap，栓Q了...。
简单地说设置为innodb_flush_method=O_DIRECT。
MySQL .5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段，没耳听。。
在5.5版本这个对提升吞吐量帮助hen小， 然后在MySQL .6版本这个提升就非常大了所以在MySQL5.5中你可Neng会保守地设置innodb_buffer_pool_instances=，在MySQL .6和5.7中你Ke以设置为8-16个缓冲池实例，地道。。
你设置后观察会觉得性Neng提高不大，但在大多数高负载情况下它应该会有不错的表现，走捷径。。
我服了。 对了不要指望这个设置Neng减少你单个查询的响应时间。这个是在高并发负载的服务器上才kan得出区别。比如多个线程一边Zuo许多事情。
上手。 你可Neng会经常听到应该设置innodb_thread_concurrency=0然后就不要管它了。不过这个只在低负载服务器使用时才正确。然后 Ru果你的服务器的CPU或者IO使用接受饱和，特别是有时候出现峰值，这时候系统想在超载时Neng正常处理查询，那么强烈建议关注innodb_thread_concurrency。
没法说。 InnoDB有一种方法来控制并行施行的线程数 – 我们称为并发控制机制。大部分是由innodb_thread_concurrency值来控制的。Ru果设置为0，并发控制就关闭了所以呢InnoDB会马上处理所有进来的请求。
交学费了。 在你有32CPU核心且只有4个请求时会没什么问题。不过想象下你只有4CPU核心和32个请求时 – Ru果你让32个请求一边处理，你这个自找麻烦。主要原因是这些32个请求只有4 CPU核心， 明摆着地会比平常慢至少8倍，而然这些请求每个dou有自己的外部和内部锁，这有hen大可Neng堆积请求。
下面介绍如何geng改这个变量， 在mysql命令行提示符施行：，害...
对于大多数工作负载和服务器，设置为8是一个好开端，然后你Ke以根据服务器达到了这个限制而资源使用率利用不足时逐渐增加。Ke以通过show engine innodb status\G来查kan目前查询处理情况， 查找类似如下行：
换位思考... 这一项不得不提及，主要原因是仍然有hen多人没有添加这一项。你应该添加skip_name_resolve来避免连接时DNS解析。
大多数情况下你geng改这个会没有什么感觉，主要原因是大多数情况下DNS服务器解析会非常快。不过当DNS服务器失败时 它会出现在你服务器上出现“unaunticated connections” ，而就是为什么所有的请求dou突然开始慢下来了。
所以不要等到这种事情发生才geng改。现在添加这个变量并且避免基于主机名的授权。
* innodb_io_capacity：用来当刷新脏数据时控制MySQL每秒施行的写IO量。
* innodb_io_capacity_max: 在 精神内耗。 压力下 控制当刷新脏数据时MySQL每秒施行的写IO量
先说说这与读取无关 – SELECT查询施行的操作。对于读操作，MySQL会尽大可Neng处理并返回后来啊。至于写操作， MySQL在后台会循环刷新， 本质上... 在每一个循环会检查有多少数据需要刷新，并且不会用超过innodb_io_capacity指定的数来Zuo刷新操作。这也包括geng改缓冲区合并。
换言之... 第二， 我需要解释一下什么叫“在压力下”，MySQL中称为”紧急情况”，是当MySQL在后台刷新时它需要刷新一些数据为了让新的写操作进来。然后MySQL会用到innodb_io_capacity_max。
那么应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢？
Zui好的方法是测量你的存储设置的随机写吞吐量，然后给innodb_io_capacity_max设置为你的设备Neng达到的大IOPS。innodb_io_capacity就设置为它的50-%，特别是你的系统主要是写操作时，盘它。。
百感交集。 通常你Ke以预测你的系统的IOPS是多少。比方说由8 15k硬盘组成的RAID10NengZuo大约每秒1000随机写操作，所以你Ke以设置innodb_io_capacity=600和innodb_io_capacity_max=。许多廉价企业SSDKe以Zuo4,-, IOPS等。
这个值设置得不好问题不大。但是要注意默认的200和400会限制你的写吞吐量，所以呢你可Neng有时候会捕捉到刷新进程。Ru果出现这种情况，可Neng是Yi经达到你硬盘的写IO吞吐量，或者这个值设置得太小限制了吞吐量，可不是吗！。
Ru果你跑的是MySQL .6或5.， 你不需要geng改innodb_stats_on_metadata的默认值，主要原因是它Yi经设置正确了，挺好。。
不过在MySQL .5或5.， 强烈建议关闭这个变量 – Ru果是开启，像命令show table status会马上查询INFORMATION_SCHEMA而不是等几秒再施行，这会使用到额外的IO操作，他急了。。
从5..32版本开始，这个是动态变量，意 切记... 味着你不需要重启MySQL服务器来关闭它。
求锤得锤。 innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性Neng无关， 不过Ru果你有时候重启mysql服务器，那么就有关。当两个dou激活时MySQL缓冲池的内容在停止MySQL时存储到一个文件。当你下次启动MySQL时它会在后台启动一个线程来加载缓冲池的内容以提高预热速度到3-5倍。
两件事：
第一， 它其实吧没有在关闭时复制缓冲池内容到文件，仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了。 百感交集。 然后它就Neng以大量的顺序读非常快速的加载那些页面而不是需要成千上万的小随机读。
第二， 启动时是在后台加载内容，主要原因是MySQL不需要等到缓冲池内容加载完成再开始接受请求，就这？。
从MySQL .7开始， 默认只有25%的缓冲池页面在mysql关闭时存储到文件，但是你Ke以控制这个值 – 使用innodb_buffer_pool_dump_pct，建议75-，我emo了。。
这个特性从MySQL .6才开始支持。
Ru果你运行着一个大量SELECT查询的MySQL服务器，那么自适应哈希索引将下你的下一个瓶颈。自适应哈希索引是InnoDB内部维护的动态索引，Ke以提高Zui常用的查询模式的性Neng。这个特性Ke以重启服务器关闭，不过默认下在mysql的所有版本开启，原来如此。。
这个技术非常复杂，在大多数情况下它会对大多数类型的查询直到加速的作用。不过当你有太多的查询往数据库， 我们都... 在某一个点上它会花过多的时间等待AHI锁和闩锁。
Ru果你的是MySQL ， 没有这个问题 – innodb_adaptive_hash_index_parts默认设置为8，所以自适应哈希索引被切割为8个分区，主要原因是不存在全局互斥。
我天... 不过在mysql .7前的版本，没有AHI分区数量的控制。换句话说有一个全局互斥锁来保护AHI，可Neng导致你的select查询经常撞墙。
所以Ru果你运行的是5.1或5.， 并且有大量的select查询，Zui简 那必须的！ 单的方案就是切换成同一版本的Percona Server来激活AHI分区。
Ru果人认为查询缓存效果hen好，肯定应该使用它。好吧，有时候是有用的。不过这个只在你在低负载时有用，特别是在低负载下大多数是读取，小量写或者没有，原来如此。。
Ru果是那样的情况，设置query_cache_type=ON和query_cache_size=256M就好了。不过记住不Neng把256M设置geng高的值了否则会由于查询缓存失效时导致引起严重的服务器停顿，栓Q！。
Ru果你的MySQL服务器高负载动作， 建议设置query_cache_size=0和query_cache_type=OFF，并重启服务器生效。那样Mysql就会停止在所有的查询使用查询缓存互斥锁，就这？。
从MySQL .6开始，表缓存Neng分割到多个分区。
表缓存用来存放目前Yi打开表的列表，当每一个表打开或关闭互斥体就被锁定 – 即使这是一个隐式临时表。 蚌埠住了... 使用多个分区绝对减少了潜在的争用。
从MySQL .8开始，table_open_c 层次低了。 ache_instances=16是默认的配置。
网页名称：MySQL性Neng调优 – 你必须了解的15个重要变量
本文 成dou网站建设公司_创新互联，为您提供建站公司、外贸建站、微信小程序、ChatGPT、Zuo网站、自适应网站，物超所值。
Demand feedback
