详解MySQL的配置文件及优化

#头条创作挑战赛#

在Windows系统中，MySQL服务器启动时最先读取的是my.ini这个配置文件。在Linux系统中，配置文件为my.cnf，其路径一般为/etc/my.cnf或/etc/mysql/my.cnf。

由于MySQL的默认设置性能非常差，仅仅起一个功能测试的作用，不能用在生产环境中，所以需要对一些参数进行调整优化。需要注意的是每次更改文件后，需要重启MySQL服务，才能使修改生效。

本文就以Windows 系统下的 my.ini 配置文件为样板，讲解一下MySQL 配置文件中的主要参数和可能在某些场景需要设置其他参数。

[mysql]

default-character-set=utf8 #表示MySQL默认的字符集为utf8

[mysqld]

port=3306 #表示MySQL使用的端口号，默认的端口号为 3306。如果需要更改端口号的话，可以直接在这里修改。

basedir=D:/phpstudy_pro/Extensions/MySQL5.7.26/ # basedir表示MySQL 的安装路径

datadir=D:/phpstudy_pro/Extensions/MySQL5.7.26/data/ # datadir表示 MySQL 数据文件的存储位置，也是数据表的存放位置

character-set-server=utf8 #表示服务器端默认的字符集为utf8

default-storage-engine=MyIsam #表示创建数据表时，默认使用的存储引擎为MyIsam

max_connections=100 #表示允许同时访问 MySQL 服务器的最大连接数，默认为100个。其中有一个连接是保留的，专门留给管理员专用的

innodb_buffer_pool_size=64M #表示缓存的大小，InnoDB 使用一个缓冲池类保存索引和原始数据。

innodb_flush_log_at_trx_commit=1 #是设置提交日志的时机，默认设置为 1，表示InnoDB 会在每次提交后将事务日志写到磁盘上。

innodb_lock_wait_timeout=120 #表示InnoDB事务在被回滚之前可以等待一个锁定的超时秒数，默认值是50秒。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句时会查看到锁定设置。

innodb_log_buffer_size=4M #表示用来存储日志数据的缓存区的大小。

innodb_log_file_size=256M #表示日志文件的大小。

interactive_timeout=120 #表示服务器关闭交互式连接前等待活动的时间,默认是28800s（8小时）

join_buffer_size=2M #联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。

key_buffer_size=32M #指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。我的建议是对于内存在4GB左右的服务器该参数可设置为384M或512M。如果你使它太大，系统会将开始换页查询变得很慢。

max_allowed_packet=16M #表示接受的数据包大小，默认值为16M。增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。

max_heap_table_size=64M #这个值定义了用户可以创建的内存表(memory table)的大小。它是用来计算内存表的最大行数值，且支持动态改变。

myisam_max_sort_file_size=64G #表示 MySQL 重建索引时所允许的最大临时文件的大小。

myisam_sort_buffer_size=32M #表示重建索引时的缓存大小。

read_buffer_size=512kb #表示MyISAM 表全表扫描的缓存大小。

read_rnd_buffer_size=4M #表示将排序好的数据存入该缓存中

server_id=1 #表示是本机的序号为1,一般来讲就是master的意思。

skip-external-locking=off #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。如果值变为on，表示如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求。

sort_buffer_size=256kb #表示用于排序的缓存大小。

table_open_cache=256 #表示所有进程打开表的总数。

thread_cache_size=16 #表示缓存的最大线程数。

tmp_table_size=64M #表示内存中每个临时表允许的最大是多少，默认值为64M。

wait_timeout=120 #表示服务器关闭非交互式连接之前等待活动的时间，默认是28800s（8小时）

[client]

port=3306 #表示MySQL客户端连接服务器端时使用的端口号，默认的端口号为 3306。如果需要更改端口号的话，可以直接在这里修改。

default-character-set=utf8 #表示 MySQL 客户端默认的字符集为utf8。

接下来举2个优化方案的例子，方便理解：

优化方案一（对于小内存，服务器内存为4-8GB之间）：

可以修改以下参数：

key_buffer_size =512 MB,

tmp_table_size = 1024 MB,

innodb_buffer_pool_size = 1024 MB,

innodb_log_buffer_size = 128 MB,

sort_buffer_size = 2048 KB

read_buffer_size = 2048 KB

read_rnd_buffer_size = 1024 KB

join_buffer_size = 4096 KB

thread_cache_size = 192

table_open_cache = 1024

max_connections = 400

优化方案二（对于比较大的内存，服务器内存为16-32GB之间）：

可以修改以下参数：

key_buffer_size =1024 MB,

tmp_table_size = 2048 MB,

innodb_buffer_pool_size = 4096 MB,

innodb_log_buffer_size = 128 MB,

sort_buffer_size = 4096 KB

read_buffer_size = 4096 KB

read_rnd_buffer_size = 2048 KB

join_buffer_size = 8192 KB

thread_cache_size =256

table_open_cache = 2048

max_connections = 500