MySQL优化秘籍

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

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

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

其实,创建一个好的配置,最快方法不是从学习配置项开始,也不是问哪个配置项应该怎么设置或者怎么修改开始,更不是从检查服务器行为和询问哪个配置项可以提升性能开始。最好是从理解MySQL内核和行为开始,然后利用这些知识来指导你配置MySQL。

就从理解MySQL配置的工作原理开始吧。

MySQL配置的工作原理

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

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

1
2
3
4
5
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这一段,一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的。

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

1
2
3
4
5
6
# 配置文件
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把这些归为动态配置变量:

1
2
3
4
5
6
7
8
9
10
11
分割线
感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS