mysql server配置如何优化

共计 4611 个字符，预计需要花费 12 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 mysql server 配置如何优化，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

在这之前，我们需要掌握以下方法：

查看 MySql 状态及变量的方法：
Mysql show status mdash; mdash; 显示状态信息（扩展 show status like XXX）
Mysql show variables mdash; mdash; 显示系统变量（扩展 show variables like XXX）
Mysql show innodb status mdash; mdash; 显示 InnoDB 存储引擎的状态
Shell admin variables -u username -p password mdash; mdash; 显示系统变量
Shell mysqladmin extended-status -u username -p password mdash; mdash; 显示状态信息
查看状态变量及帮助：
Shell mysqld –verbose –help [|more #逐行显示]

首先，让我们看看有关请求连接的变量：
为了能适应更多数据库应用用户，MySql 提供了连接（客户端）变量，以对不同性质的用户群体提供不同的解决方案，笔者就 max_connections，back_log 做了一些细结，如下：

max_connections 是指 MySql 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于 MySql 会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过 conn% 通配符查看当前状态的连接数量，以定夺该值的大小。

back_log 是要求 MySQL 能有的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间 (尽管很短) 检查连接并且启动一个新线程。back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果 MySql 的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。另外，这值（back_log）限于您的操作系统对到来的 TCP/IP 连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制（可以检查你的 OS 文档找出这个变量的最大值），试图设定 back_log 高于你的操作系统的限制将是无效的。

优化了 MySql 的连接后属性后，我们需要看看缓冲区变量：

使用 MySql 数据库存储大量数据（或使用复杂查询）时，我们应该考虑 MySql 的内存配置。如果配置 MySQL 服务器使用太少的内存会导致性能不是最优的；如果配置了太多的内存则会导致崩溃，无法执行查询或者导致交换操作严重变慢。在现在的 32 位平台下，仍有可能把所有的地址空间都用完，因此需要审视。

计算内存使用的秘诀公式就能相对地解决这一部分问题。不过，如今这个公式已经很复杂了，更重要的是，通过它计算得到的值只是“理论可能”并不是真正消耗的值。事实上，有 8GB 内存的常规服务器经常能运行到最大的理论值（100GB 甚至更高）。此外，你轻易不会使用到“超额因素”（它实际上依赖于应用以及配置）。一些应用可能需要理论内存的 10% 而有些仅需 1%。
那么，我们可以做什么呢？

来看看那些在启动时就需要分配并且总是存在的全局缓冲吧！

全局缓冲：
key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size，innodb_log_buffer_size, query_cache_size

注：如果你大量地使用 MyISAM 表，那么你也可以增加操作系统的缓存空间使得 MySQL 也能用得着。把这些也都加到操作系统和应用程序所需的内存值之中，可能需要增加 32MB 甚至更多的内存给 MySQL 服务器代码以及各种不同的小静态缓冲。这些就是你需要考虑的在 MySQL 服务器启动时所需的内存。其他剩下的内存用于连接。

key_buffer_size 决定索引处理的速度，尤其是索引读的速度。一般我们设为 16M，通过检查状态值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 设置是否合理。比例 key_reads / key_read_requests 应该尽可能的低，至少是 1:100，1:1000 更好（上述状态值可以使用 key_read% 获得用来显示状态数据）。key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables 得知详情。

innodb_buffer_pool_size 对于 InnoDB 表来说，作用就相当于 key_buffer_size 对于 MyISAM 表的作用一样。InnoDB 使用该参数指定大小的内存来缓冲数据和索引。对于单独的 MySQL 数据库服务器，最大可以把该值设置成物理内存的 80%。

innodb_additional_mem_pool_size 指定 InnoDB 用来存储数据字典和其他内部数据结构的内存池大小。缺省值是 1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL 会在错误日志中写入一条警告信息。

innodb_log_buffer_size 指定 InnoDB 用来存储日志数据的缓存大小，如果您的表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率。

query_cache_size 是 MySql 的查询缓冲大小。（从 4.0.1 开始，MySQL 提供了查询缓冲机制）使用查询缓冲，MySQL 将 SELECT 语句和查询结果存放在缓冲区中，今后对于同样的 SELECT 语句（区分大小写），将直接从缓冲区中读取结果。根据 MySQL 用户手册，使用查询缓冲最多可以达到 238% 的效率。通过检查状态值 rsquo;Qcache_% rsquo;，可以知道 query_cache_size 设置是否合理：如果 Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况，如果 Qcache_hits 的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果 Qcache_hits 的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在 SELECT 语句中加入 SQL_NO_CACHE 可以明确表示不使用查询缓冲。

除了全局缓冲，MySql 还会为每个连接发放连接缓冲。

连接缓冲：
每个连接到 MySQL 服务器的线程都需要有自己的缓冲。大概需要立刻分配 256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size 大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达 MySQL 所能分配给这个操作的最大内存空间了。注意，这里需要考虑的不只有一点 mdash; mdash; 可能会分配多个同一种类型的缓存，例如用来处理子查询。一些特殊的查询的内存使用量可能更大 mdash; mdash; 如果在 MyISAM 表上做成批的插入时需要分配 bulk_insert_buffer_size 大小的内存；执行 ALTER TABLE，OPTIMIZE TABLE，REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

read_buffer_size 是 MySql 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql 会为它分配一段内存缓冲区。read_buffer_size 变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

sort_buffer_size 是 MySql 执行排序使用的缓冲大小。如果想要增加 ORDER BY 的速度，首先看是否可以让 MySQL 使用索引而不是额外的排序阶段。如果不能，可以尝试增加 sort_buffer_size 变量的大小。

read_rnd_buffer_size 是 MySql 的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 MySql 会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

tmp_table_size 是 MySql 的 heap（堆积）表缓冲大小。所有联合在一个 DML 指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的 (HEAP) 表。具有大的记录长度的临时表 (所有列的长度的和)或包含 BLOB 列的表存储在硬盘上。如果某个内部 heap（堆积）表大小超过 tmp_table_size，MySQL 可以根据需要自动将内存中的 heap 表改为基于硬盘的 MyISAM 表。还可以通过设置 tmp_table_size 选项来增加临时表的大小。也就是说，如果调高该值，MySql 同时将增加 heap 表的大小，可达到提高联接查询速度的效果。

当我们设置好了缓冲区大小之后，再来看看：

table_cache 所有线程打开的表的数目，增大该值可以增加 mysqld 需要的文件描述符的数量。每当 MySQL 访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值 rsquo;Open_tables rsquo; 和 rsquo;Opened_tables rsquo;，可以决定是否需要增加 table_cache 的值。如果你发现 open_tables 等于 table_cache，并且 opened_tables 在不断增长，那么你就需要增加 table_cache 的值了（上述状态值可以使用 rsquo;Open%tables rsquo; 获得）。注意，不能盲目地把 table_cache 设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

做了以上方面的调优设置之后，MySql 应该基本能满足您需求

以上是“mysql server 配置如何优化”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！