【MySQL】我必须得告诉大家的MySQL优化原理3（上）

最新推荐文章于 2023-04-11 11:16:25 发布

星辰_mya

最新推荐文章于 2023-04-11 11:16:25 发布

阅读量275

点赞数

分类专栏： ♥ 数据库和缓存 -----MySQL

♥ 数据库和缓存同时被 2 个专栏收录

53 篇文章 1 订阅

订阅专栏

-----MySQL

49 篇文章 1 订阅

订阅专栏

前言：

第三篇，来吧~

正文：

我先上一个我们公司的配置文件

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

ngram_token_size=2
ft_min_word_len = 2

简单吧~与我无关

聊聊MySQL配置

大多数开发者可能不太会关注MySQL的配置，毕竟在基本配置没有问题的情况下，把更多的精力放在schema设计、索引优化和SQL优化上，是非常务实的策略。这时，如果再花力气去优化配置项，获得的收益通常都比较小。更多的时候，基于安全因素的考量，普通开发者很少能够接触到生产环境的MySQL配置。正是这样，导致开发者（包括我）对MySQL的配置不甚了解，希望本文能帮你更好的了解MySQL配置。

如果让你在某种环境上安装配置MySQL，你会怎么做？安装后，直接copy修改示例配置文件，应该是大多数人的做法。但强烈建议不要怎么做，首先，示例配置文件有非常多注释掉的配置项，它可能会诱使你打开一个你并不了解的配置，而且这些注释还不一定准确。其次，MySQL的一些配置对于现代化的硬件和工作负载来说，有点过时了。

MySQL有非常多的配置项可以修改，但大多数情况下，你都不应该随便修改它，因为错误或者没用的配置导致的潜在风险非常大，而且还很难定位问题。确保基本配置正确，然后小心诊断问题，确认问题恰好可以通过某个配置项解决，紧接着再修改这个配置吧。

其实，创建一个好的配置，最好是从理解MySQL内核和行为开始，然后利用这些知识来指导你配置MySQL。

理解MySQL配置的工作原理

MySQL从哪儿获得配置信息：命令行参数和配置文件。类Unix系统中，配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在启动时，可以通过命令行参数指定配置文件的位置，当然命令行中也可以指定其它参数，服务器会读取配置文件的内容，删除所有注释和换行，然后和命令行选项一起处理。

任何打算长期使用的配置项都应该写入配置文件，而不是在命令行中指定。一定要清楚的知道MySQL使用的配置文件位置，在修改时不能想当然，比如，修改了/etc/my.cnf的配置项，但MySQL实际并未使用这个配置文件。如果你不知道当前使用的配置文件路径，可以尝试：

root@msc3:~# which mysqld
/usr/sbin/mysqld
root@msc3:~# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

一个典型的配置文件包含多个部分，每个部分的开头是一个方括号括起来的分段名称。MySQL程序通常读取跟它同名的分段部分，比如，许多客户端程序读取client部分。服务器通常读取mysqld这一段，一定要确认配置项放在了文件正确的分段中，否则配置是不会生效的。soga~

MySQL每一个配置项均使用小写，单词之间用下划线或者横线隔开，虽然我们常用的分隔符是下划线，但如果在命令行或者配置文件中见到如下配置，你要知道，它们其实是等价的：

# 配置文件
max_connections=5000
max-connections=5000
# 命令行
/usr/sbin/mysqld --max_connections=5000
/usr/sbin/mysqld --max-connections=5000

配置项可以有多个作用域：全局作用域、会话作用域(每个连接作用不同)、对象作用域。很多会话级配置项跟全局配置相等，可以认为是默认值，如果改变会话级配置项，它只影响改动的当前连接，当连接关闭时，所有的参数变更都会失效。下面有几个示例配置项：

query-cache-size 全局配置项
sort-buffer-size 默认全局相同，但每个线程里也可以设置
join-buffer-size 默认全局，且每个线程也可以设置。但若一个查询中关联多张表，可以为每个关联分配一个关联缓存(join-buffer)，所以一个查询可能有多个关联缓冲。

配置文件中的变量(配置项)有很多(但不是所有)可以在服务器运行时修改，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>//这个地方是不是少一个“l”？
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的影响差别很大(后文会具体说明)。最好把一些好的习惯作为规范合并到工作流程中去，就比如：

不要通过配置项的名称来推断一个变量的作用

read-buffer-size：当MySQL需要顺序读取数据时，如无法使用索引，其将进行全表扫描或者全索引扫描。这时，MySQL按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在缓存中，当缓存空间被写满或者全部数据读取结束后，再将缓存中的数据返回给上层调用者，以提高效率。
read-rnd-buffer-size：和顺序读取相对应，当MySQL进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。比如：根据索引信息读取表数据、根据排序后的结果集与表进行Join等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到read-rnd-buffer-size参数所设置的内存缓冲区。

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

不要轻易在全局修改会话级别的配置

对于某些会话级别的设置，不要轻易的在全局增加它们的值，除非你确认这样做是对的。

比如：sort-buffer-size，该参数控制排序操作的缓存大小，MySQL只会在查询需要做排序操作时才会为该缓冲分配内存，一旦需要排序，就会一次性分配指定大小的内存，即使是非常小的排序操作。因此在配置文件中应该配置的小一些，然后在某些查询需要排序时，再在连接中把它调大。比如：

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

配置变量时，并不是值越大越好

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

规范注释，版本控制

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

不要根据一些“比率”来调优

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

不要随便使用调优脚本

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

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