我必须得告诉你的MySQL优化原理3

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了我必须得告诉你的MySQL优化原理3，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16539字，纯文字阅读大概需要24分钟。

内容图文

配置文件中的变量(配置项)有很多(但不是所有)可以在服务器运行时修改，MySQL把这些归为动态配置变量：

# 设置全局变量，GLOBAL和@@global作用是一样的
set   GLOBAL   sort-buffer-size  = <value>
set   @@global.sort-buffer-size := <value>
# 设置会话级变量，下面6种方式作用是一样的
# 即：没有修饰符、SESSION、LOCAL等修饰符作用是一致的
set  SESSION   sort-buffer-size  = <value>
set  @@session.sort-buffer-size := <value>
set          @@sort-buffer-size  = <value>
set  LOCAL     sort-buffer-size  = <value>
set     @@ocal.sort-buffer-size := <value>
set            sort-buffer-size  = <value>
# set命令可以同时设置多个变量，但其中只要有一个变量设置失败，所有的变量都未生效
SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000;
SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;

动态的设置变量，MySQL关闭时这些变量都会失效。如果在服务器运行时修改了变量的全局值，这个值对当前会话和其他任何已经存在的会话都不起效果，这是因为会话的变量值是在连接创建时从全局值初始化而来的。注意，在配置修改后，需要确认是否修改成功。

你可能注意到，上面的示例中，有些使用“=”，有些使用“:=”。对于set命令本身来说，两种赋值运算符没有任何区别，在命令行中使用任一运算符符，均可以生效。而在其他语句中，赋值运算符必须是“:=”，因为在非set语句中“=”被视为比较运算符。具体可以参考如下示例： 详细示例可以参考：stackoverflow

// @exp 表示用户变量，上面的示例均是系统变量
// 错误
set @user = 123456;
set @group = select GROUP from USER where User = @user;
select * from USER where GROUP = @group;
// 正确
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

有一些配置使用了不同的单位，比如 table-cache变量指定表可以被缓存的数量，而不是表可以被缓存的字节数。而 key-buffer-size则是以字节为单位。

还有一些配置可以指定后缀单位，比如 1M=1024*1024字节，但需要注意的是，这只能在配置文件或者作为命令行参数时有效。当使用SQL的SET命令时，必须使用数字值1048576或者1024*1024这样的表达式，但在配置文件中不能使用表达式。

小心翼翼的配置MySQL

们常常动态的修改配置，但请务必小心，因为它们可能导致数据库做大量耗时的工作，从而影响数据库的整体性能。比如从缓存中刷新脏块，不同的刷新方式对I/O的影响差别很大(后文会具体说明)。最好把一些好的习惯作为规范合并到工作流程中去，就比如：

好习惯1：不要通过配置项的名称来推断一个变量的作用

不要通过配置项的名称来推断一个变量的作用，因为它可能跟你想象的完全不一样。比如：

• read-buffer-size：当MySQL需要顺序读取数据时，如无法使用索引，其将进行全表扫描或者全索引扫描。这时，MySQL按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在缓存中，当缓存空间被写满或者全部数据读取结束后，再将缓存中的数据返回给上层调用者，以提高效率。

• read-rnd-buffer-size：和顺序读取相对应，当MySQL进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。比如：根据索引信息读取表数据、根据排序后的结果集与表进行Join等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到 read-rnd-buffer-size参数所设置的内存缓冲区。

这两个配置都是在扫描MyISAM表时有效，且MySQL会为每个线程分配内存。对于前者，MySQL只会在查询需要使用时才会为该缓存分配内存，并且一次性分配该参数指定大小的全部内存，而后者同样是需要时才分配内存，但只分配需要的内存大小而不是参数指定的数值， max-read-rnd-buffer-size(实际上没有这个配置项)这个名字更能表达这个变量的实际含义。

好习惯2：不要轻易在全局修改会话级别的配置

对于某些会话级别的设置，不要轻易的在全局增加它们的值，除非你确认这样做是对的。比如： sort-buffer-size，该参数控制排序操作的缓存大小，MySQL只会在查询需要做排序操作时才会为该缓冲分配内存，一旦需要排序，就会一次性分配指定大小的内存，即使是非常小的排序操作。因此在配置文件中应该配置的小一些，然后在某些查询需要排序时，再在连接中把它调大。比如：

SET @@seession.sort-buffer-size := <value>
-- 执行查询的sql
SET @@seession.sort-buffer-size := DEFAULT #恢复默认值
# 可以将类似的代码封装在函数中方便使用。

好习惯3：配置变量时，并不是值越大越好

配置变量时，并不是值越大越好，而且如果设置的值太高，可能更容易导致内存问题。在修改完成后，应该通过监控来确认变量的修改对服务器整体性能的影响。

好习惯4：规范注释，版本控制

在配置文件中写好注释，可能会节省自己和同事大量的工作，一个更好的习惯是把配置文件置于版本控制之下。

说完了好习惯，再来说说不好的习惯。

坏习惯1：根据一些“比率”来调优

一个经典的按“比率”调优的经验法则是，缓存的命中率应该高于某个百分比，如果命中率过低，则应该增加缓存的大小。这是非常错误的意见，大家可以仔细思考一下：缓存的命中率跟缓存大小有必然联系吗？(分母变大，值就变大了？)除非确实是缓存太小了。关于MyISAM键缓冲命中率，下文会详细说明。

坏习惯2：随便使用调优脚本

尽量不要使用调优脚本！不同的业务场景、不同的硬件环境对MySQL的性能要求是不一样的。比如有些业务对数据的完整性要求较高，那么就一定要保证数据不丢失，出现故障后可恢复数据，而有些业务却对数据的完整性要求没那么高，但对性能要求更高。因此，即使是同一个变量，在这两个不同场景下，其配置的值也应该是不同的。那你还能放心的使用网上找到的脚本吗 ？

本小节示例的几个配置项，仅用于举例说明，并不代表它们有多么重要，请根据实际应用场景配置它们。就比如sort-buffer-size，你真的需要100M内存来缓存10行数据？

给你一个基本的MySQL配置

前面已经说到，MySQL可配置性太强，看起来需要花很多时间在配置上，但其实大多数配置的默认值已经是最佳的，最好不要轻易改动太多的配置，你甚至不需要知道某些配置的存在。这里有一个最小的示例配置文件，可以作为服务器配置文件的一个起点，其中有一些配置项是必须的。本节将为你详细剖析每个配置有何作用？为什么要配置它？怎么确定合适的值？

[mysql]
# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user                           = mysql
port                           = 3306
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
# DATA STORAGE #
datadir                        = /var/lib/mysql/
# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP
# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1
# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240
# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12G

分段

MySQL配置文件的格式为集中式，通常会分成好几部分，可以为多个程序提供配置，如[client]、[mysqld]、[mysql]等等。MySQL程序通常是读取与它同名的分段部分。

• [client] 客户端默认设置内容

• [mysql] 使用mysql命令登录mysql数据库时的默认设置

• [mysqld] 数据库本身的默认设置

例如服务器mysqld通常读取[mysqld]分段下的相关配置项。如果配置项位置不正确，该配置是不会生效的。

GENERAL

首先创建一个用户mysql来运行mysqld进程，请确保这个用户拥有操作数据目录的权限。设置默认端口为3306，有时为了安全，可能会修改一下。默认选择Innodb存储引擎，在大多数情况下是最好的选择。但如果默认是InnoDB，却需要使用MyISAM存储引擎，请显式地进行配置。许多用户认为其数据库使用了某种存储引擎但实际上却使用的是另外一种，就是因为默认配置的问题。

接着设置数据文件的位置，这里把pid文件和socket文件放到相同的位置，当然也可以选择其它位置，但要注意的是不要将socket文件和pid文件放到MySQL编译的默认位置，因为不同版本的MySQL，这两个文件的默认路径可能会不一致，最好明确地设置这些文件的位置，以免版本升级时出现问题。

在类UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字（socket）文件，即配置中的 mysql.sock文件。 当MySQL实例启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。该文件可由参数 pid-file控制，默认位于数据库目录下，文件名为主机名.pid

DATA STORAGE

datadir用于配置数据文件的存储位置，没有什么好说的。

为缓存分配内存

接下来有许多涉及到缓存的配置项，缓存设置多大，最直接的因素肯定是服务器内存的大小。如果服务器只运行MySQL，所有不需要为OS以及查询处理保留的内存都可以用在MySQL缓存。为MySQL缓存分配更多内存，可以有效的避免磁盘访问，提升数据库性能。大部分情况来说最为重要的缓存：

• InnoDB缓冲池

• InnoDB日志文件和MyISAM数据的操作系统缓存(MyISAM依赖于OS缓存数据)

• MyISAM键缓存

• 查询缓存

• 无法配置的缓存，比如：bin-log或者表定义文件的OS缓存

还有一些其他缓存，但它们通常不会使用太多内存。关于查询缓存，前面文章(参考本系列的第一篇)已有介绍，大多数情况下我们不建议开启查询缓存，因此上文的配置中 query-cache-type=0表示禁用了查询缓存，相应的查询缓存大小 query-cache-size=0。除开查询缓存，剩下关于InnoDB和MyISAM的相关缓存，在接下来会做详细介绍。

如果只使用单一存储引擎，配置服务器就会简单许多。如果只使用MyISAM表，就可以完全关闭InnoDB，而如果只使用InnoDB，就只需要分配最少的资源给MyISAM（MySQL内部系统表使用MyISAM引擎）。但如果是混合使用各种存储引擎，就很难在他们之间找到恰当的平衡，因此只能根据业务做一个猜测，然后在运行中观察服务器运行状况后做出调整。

MyISAM

key-buffer-size

key-buffer-size用于配置MyISAM键缓存大小，默认只有一个键缓存，但是可以创建多个。MyISAM自身只缓存索引，不缓存数据(依赖OS缓存数据)。如果大部分表都是MyISAM，那么应该为键缓存设置较多的内存。但如何确定该设置多大？

假设整个数据库中表的索引大小为X，肯定不需要把缓存设置得比X还大，所以当前的索引大小就成为这个配置项的重要依据。可以通过下面两种方式来查询当前索引的大小：

// 1.通过SQL语句查询
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = ‘MYISAM‘
// 2.统计索引文件的大小
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
比如：
root@dev-msc3:# du -sch `find /var/lib/mysql -name "*.MYI"`
72K        /var/lib/mysql/static/t_global_region.MYI
40K        /var/lib/mysql/mysql/db.MYI
12K        /var/lib/mysql/mysql/proxies_priv.MYI
12K        /var/lib/mysql/mysql/tables_priv.MYI
4.0K       /var/lib/mysql/mysql/func.MYI
4.0K       /var/lib/mysql/mysql/columns_priv.MYI
4.0K       /var/lib/mysql/mysql/proc.MYI
4.0K       /var/lib/mysql/mysql/event.MYI
4.0K       /var/lib/mysql/mysql/user.MYI
4.0K       /var/lib/mysql/mysql/procs_priv.MYI
4.0K       /var/lib/mysql/mysql/ndb_binlog_index.MYI
164K       total

你可能会问，刚创建好的数据库，根本就没什么数据，索引文件大小为0，那如何配置键缓存大小？这时候只能根据经验值：不超过为操作系统缓存保留内存的25% ~ 50%。设置一个基本值，等运行一段时间后，根据运行情况来调整键缓存大小。总结来说，索引大小与OS缓存的25%~50%两者间取小者。当然还可以计算键缓存的使用情况，如果一段时间后还是没有使用完所有的键缓存，就可以把缓冲区调小一点，计算缓存区的使用率可以通过以下公式：

// key_blocks_unused的值可以通过 SHOW STATUS获取
// key_cache_block_size的值可以通过 SHOW VARIABLES获取 
(key_blocks_unused * key_cache_block_size) / key_buffer_size

键缓存块大小是一个比较重要的值，因为它影响MyISAM、OS缓存以及文件系统之间的交互。如果缓存块太小，可能会碰到写时读取(OS在写数据之前必须先从磁盘上读取一些数据)，关于写时读取的相关知识，大家可以自行查阅。

关于缓存命中率，这里再说一点。缓存命中率有什么意义？其实这个数字没太大的作用。比如99%和99.9%之间看起来差距很小，但实际上代表了10倍的差距。缓存命中率的实际意义与应用也有很大关系，有些应用可以在命中率99%下良好的工作，有些I/O密集型应用，可能需要99.99%。所以从经验上来说，每秒未命中次数这个指标实际上会更有用一些。比如每秒5次未命中可能不会导致IO繁忙，但每秒100次缓存未命中则可能出现问题。

MyISAM键缓存的每秒未命中次数可以通过如下命令监控：

# 计算每隔10s缓存未命中次数的增量
# 使用此命令时请带上用户和密码参数：mysqladmin -uroot -pxxx extended-status -r -i 10 | grep Key_reads
$ mysqladmin extended-status -r -i 10 | grep Key_reads

最后，即使没有使用任何MyISAM表，依然需要将 key-buffer-size设置为较小值，比如32M，因为MySQL内部会使用MyISAM表，比如GROUP BY语句可能会创建MyISAM临时表。

myisam-recover

myisam-recover选项用于配置MyISAM怎样寻找和修复错误。打开这个选项会通知MySQL在打开表时，检查表是否损坏，并在找到问题时进行修复，它可以设置如下值：

• DEFAULT：表示不设置，会尝试修复崩溃或者未完全关闭的表，但在恢复数据时不会执行其它动作

• BACKUP：将数据文件备份到.bak文件，以便随后进行检查

• FORCE：即使.myd文件中丢失的数据超过1行，也让恢复动作继续执行

• QUICK：除非有删除块，否则跳过恢复

可以设置多个值，每个值用逗号隔开，比如配置文件中的 BACKUP,FORCE会强制恢复并且创建备份，这样配置在只有一些小的MyISAM表时有用，因为服务器运行着一些损坏的MyISAM表是非常危险的，它们有时可能会导致更多数据损坏，甚至服务器崩溃。然而如果有很大的表，它会导致服务器打开所有的MyISAM表时都检查和修复，大表的检查和修复可能会耗费大量时间，且在这段时间里，MySQL会阻止这个连接做其它任何操作，这显然是不切实际的。

因此，在默认使用InnoDB存储引擎时，数据库中只有非常小的MyISAM表时，只需要配置 key-buffe-size于一个很小的值(32M)以及 myisam-recover=BACKUP,FORCE。当数据库中大部分表为MyISAM表时，请根据上文的公式合理配置 key-buffer-size，而 myisam-recover则可以关闭，在启动后使用 CHECK TABLES和 REPAIR TABLES命令来做检查和修复，这样对服务器的影响比较小。

SAFETY

基本配置设置到位后，MySQL已经比较安全了，这里仅仅列出两个需要注意的配置项，如果需要启用一些使服务器更安全和可靠的设置，可以参考MySQL官方手册，但需要注意的是，它们其中的一些选项可能会影响性能，毕竟保证安全和可靠需要付出一些代价。

max-allowed-packet

max-allowed-packet防止服务器发送太大的数据包，也控制服务器可以接收多大的包。默认值4M，可能会比较小。如果设置太小，有时复制上会出问题，表现为从库不能接收主库发过来的复制数据。如果表中有Blob或者Text字段，且数据量较大的话，要小心，如果数据量超过这个变量的大小，它们可能被截断或者置为NULL，这里建议设置为16M。

max-connect-errors

这个变量是一个MySQL中与安全相关的计数器值，它主要防止客户端暴力破解密码。如果某一个客户端尝试连接MySQL服务器失败超过n次，则MySQL会无条件强制阻止此客户端连接，直到再次刷新主机缓存或者重启MySQL服务器。

这个值默认为10，太小了，有时候网络抽风或者应用配置出现错误导致短时间内不断尝试重连服务器，客户端就会被列入黑名单，导致无法连接。如果在内网环境，可以确认没有安全问题可以把这个值设置的大一点，默认值太容易导致问题。

LOGGING

接下来看下日志的配置，对于MySQL来说，慢日志和bin-log是非常重要的两种日志，前者可以帮助应用程序监控性能问题，后者在数据同步、备份等方面发挥着非常重要的作用。

关于bin-log的3个配置， log-bin用于配置文件存放路径， expire_logs_days让服务器在指定天数之后清理旧的日志，即配置保留最近多少天的日志。除非有运维手动备份清理bin-log，否则强烈建议打开此配置，如果不启用，服务器空间最终将会被耗尽，导致服务器卡住或者崩溃。

sync-binlog

sync-binlog控制当事务提交之后，MySQL是否将bin-log刷新到磁盘。如果其值等于0或者大于1时，当事务提交之后，MySQL不会将bin-log刷新到磁盘，其性能最高，但存在的风险也是最大的，因为一旦系统崩溃，bin-log将会丢失。而当其值等于1时，是最安全的，这时候即使系统崩溃，最多也就丢失本次未完成的事务，对实际的数据没有实质性的影响，但性能较差。

需要注意的是，在5.7.7之前的版本，这个选择的默认值为0，而后默认值为1，也就是最安全的策略。对于高并发的性能，需要关注这一点，防止版本升级后出现性能问题。

剩下的4个配置项就没太多要说的。

• log-error：用于配置错误日志的存放目录

• slow-query-log：打开慢日志，默认关闭

• slow-query-log-file：配置慢日志的存放目录

• log-queries-not-using-indexes：如果该sql没有使用索引，会将其写入到慢日志，但是否真的执行很慢，需要区分，默认关闭。

CACHES AND LIMITS

tmp-table-size && max-heap-table-size

这两个配置控制使用Memory引擎的内存临时表可以使用多大的内存。如果隐式内存临时表的大小超过这两个值，将会被转为磁盘MyISAM表(隐式临时表由服务器创建，用户保存执行中的查询的中间结果)。

如果查询语句没有创建庞大的临时表(通过合理的索引和查询设计来避免)，可以把这个值设大一点，以免需要把内存临时表转换为磁盘临时表。但要谨防这个值设置得过大，如果查询确实会创建很大的临时表，那么还是使用磁盘比较好，毕竟并发数一起来，所需要的内存就会急剧增长。

应该简单的把这两个变量设为同样的值，这里选择了32M，可以通过仔细检查 created-tmp-disk-tables和 created-tmp-tables两个变量来指导你设置，这两个变量的值将展示临时表的创建有多频繁。

query-cache-type && query-cache-size

看前面

max-connections

用于设置用户的最大连接数，保证服务器不会应为应用程序激增的连接而不堪重负。如果应用程序有问题，或者服务器遇到连接延迟问题，会创建很多新连接。但如果这些连接不能执行查询，那打开一个连接没什么好处，所以被“太多的连接”错误拒绝是一种快速而且代价小的失败方式。

在服务器资源允许的情况下，可以把 <code class="prettyprint code-in-text prettyprinted" style="margin:0px;padding:0px;background:rgb(243,241,241);color:rgb(88,88,88);line-h 本文系统来源：http://luecsc.blog.51cto.com/2219432/1953842

内容总结

以上是互联网集市为您收集整理的我必须得告诉你的MySQL优化原理3全部内容，希望文章能够帮你解决我必须得告诉你的MySQL优化原理3所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端