前言:

数据库的体量越来越大,作为一名优秀的程序员不能太依赖 DBA,趁着有空,梳理一下 MySQL 的配置知识。

坑:mysql.cnf 文件一般都会放在 /etc/mysql/mysql.cnf 或者 /etc/mysql.cnf 下,发现修改无效第一时间考虑是否改错路径了。

坑:谨慎修改每一个配置文件,不了解清楚原理优化的可能是导致副作用。不懂的配置不要瞎几把改。

一、MySQL 基本配置信息

查看本机安装的 MySQL 路径

# which mysqld/usr/sbin/mysql

#/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

vi mysql.cnf  # 以下配置节点描述,属个人理解,参考即可。

[mysqld]

# GENERAL (基础配置信息)

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

pid_file=/var/lib/mysql/mysql.pid

user=mysql

port=3306# 默认存储引擎

default_storage_engine=InnoDB

#InnoDB

# InnoDB 缓冲池大小

innodb_buffer_pool_size=

# InnoDB 日志文件大小innodb_log_file_size=innodb_file_per_table=1# InnoDB 写日志的方式 (O_DSYNC/O_DIRECT/O_SYNC)

innodb_flush_method=0_DIRECT

#MyISAM

# MyISAM 键缓存大小 ( MyISAM 引擎只支持键[索引]缓存,不缓存数据)

key_buffer_size=#Logging

# 错误日志目录

log_error=/var/lib/mysql/mysql-error.log

# 慢查询日志目录

slow_query_log=/var/lib/mysql/mysql-slow.log

#Other

# 临时表最大值

tmp_table_size=32M

max_heap_table_size=32M

# 是否开启 InnoDB 查询缓存

query_cache_type=0query_cache_size=0# MySQL 最大连接数

max_connections=

# 连接线程缓冲池大小

thread_cache=

# 所有连接打开的表数量

table_cache=open_files_limit=65535[client]

# 连接类型 (TCP/IP、Socket)

socket=/var/lib/mysql/mysql.sock

port=3306

配置文件配置的选项,并不是多才显得你专业。有些配置没有根据业务需求设置好,就画蛇添足了。

一般的系统以上的配置足够了。

二、让 MySQL 再快一些

1、配置内存使用

很关键的一个点,MySQL 正确的使用内存量对性能的影响很大。

一般消耗内存的地方我们可以分为两类:可控内存和不可控内存;不可控的如 MySQL 服务器运行、解析查询及 MySQL 程序管理消耗的内存;

如何配置内存:

1、确定可以使用的内存上限;

2、确定每个连接 MySQL 需要使用多少内存,例如排序缓冲和临时表;

3、操作系统需要的内存;

4、剩余的内存全部留给 MySQL 的缓存;

2、InnoDB 缓存如何配?

使用最为流行或者说最稳定最好性能的都会选择 InnoDB 引擎,那么在配置文件中,InnoDB 节点的配置显得尤为重要了。

有一个流行的经验说法,InnoDB 的缓冲池大小应该设置为当前服务器内存的 75%~80% 这个区间,

其实这是一个偶然有效的比率,但并不总是正确的。

有一个更好的办法来设置缓冲池的大小:

1、从服务器内存的总量开始;

2、减去服务器上其他服务可能占用的内存空间大小,及操作系统;

3、减去 MySQL 服务自身需要的内存,如每个查询分配一些缓冲;

4、减去足够让操作系统缓存 InnoDB 日志的文件的内存;

5、减去其他配置的 MySQL 缓冲和缓存需要的内存,如 MyISAM 健存、查存;

3、对缓冲池的一些误区

不要认为缓冲池只是用来存放你查询过的数据的,二次查询的情况小,所以不认真对待这个配置。

缓冲池缓存的的数据:

最核心的索引,你读取某一张较大的表的时候,缓存区的大小无法支撑的时候,可能会是用到硬盘来做索引的查询计算了;

其次会缓存一些行数据、自适应哈希索引、插入缓冲(延迟写入)、锁以及内部的数据结构;