mysql性能优化之my.cnf配置优化

Linux如何修改mysql配置my.cnf

1.查找路径:

mysqld --verbose --help| grep -A 1 "Default options"
1

2.修改my.cnf (或根据路径用 xftp 记事本打开修改)：

vim /etc/my.cnf 
1

3.保存并退出

:wq
1

4.重启服务后生效

service mysqld start
1

my.cnf 配置文件说明：

## global_buffers 在内存中缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能

## 计算公式：Innodb_buffer_pool_size + Innodb_additional_mem_pool_size + Innodb_log_buffer_size + key_buffer_size + query_cache_size

## per_thread_buffers 线程独享内存大小

## 计算公式（ read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size ）* max_connections

## 注意：global_buffers + per_thread_buffers 不能大于实际物理内存，否者并发量大时会造成内存溢出、系统死机 ！

[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

## 定义客户端连接信息，端口号、socket 存放位置

[mysqld]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
temdir = /usr/local/mysql/data

## Mysql 基本信息，端口号、socket、安装目录、数据存放目录、临时目录

# skip-name-resolve

## 禁止 Mysql 对外部连接进行 DNS 解析，加快连接速度。开启后所有远程连接主机只能使用 IP 的方式

skip_external_locking

## 避免 Mysql 外部锁定，减少出错几率、增强稳定性

local-infile = 0

## 禁止 SQL 读取本地文件

character-set-server = utf8

## 默认字符集 utf8

default-storage-engine = innodb

## 默认存储引擎

# general_log = on

## 开启查询日志，一般选择不开启，因为查询日志记录很详细，会增大磁盘 IO 开销，影响性能

# general_log_file = /usr/local/mysql/data/mysql.log

## 查询日志存放位置及文件名

log-error = /usr/local/mysql/data/error.log

## 错误日志位置跟文件名

# slow_query_log = on

## 开启慢查询日志，开启后将会记录执行时间超过 long_query_time 参数值的 SQL 语句（ 一般临时开启即可 ）

# long_query_time = 2

## 定义执行时间超过多少秒为慢查询，默认 10s

# slow_query_log_file = /usr/local/mysql/data/slow.log

## 定义慢查询日志存放位置

# server-id = 1

## Mysql Server 唯一标识，用来做主同同步（ 主从时开启 ）

log-bin = mysql-bin

## 开启 binlog ( 二进制 ) 日志，主要用来做增量备份跟主从同步

binlog_format = mixed

## Mysql binlog 的日志格式，Statement、ROW 跟 Mixed（ 混合模式 ）

binlog_cache_size = 2M

## 二进制日志缓冲大小，此参数是为每 Session 单独分配的，当一个线程开始一个事务时，Mysql 就会为此 Session 分配一个 binlog cache，当这个事务提交时，binlog cache 中的数据被写入 binlog 文件
## 通过 show status like 'binlog_cache%'; 来查看使用 binlog cache 的次数及使用磁盘的次数

sync_binlog = 0

## 这个参数对 Mysql 系统来说很重要，不仅影响到 binlog 对 Mysql 所带来的性能损耗，还影响到 Mysql 中数据的完整性。
## 值为 0 时代表事务提交后，Mysql 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候同步，或者 cache 满了之后才同步磁盘。
## 值为 n 时代表进行 n 次事务提交后，Mysql 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。
## 系统默认将此参数设置为 0 ，即不做任何强制性的磁盘刷新指令，性能最好，但是风险也最大。当系统崩溃时 binlog_cache 中的所有 binlog 信息都会丢失。
## 而设置为 1 时，是最安全但是性能损耗最大。当系统崩溃时，最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有实质性的影响。

expire_logs_days = 30

## 保留 30 天的 binlog 日志，系统重启、执行 flush logs 或 binlog 日志文件大小达到上限时删除 binlog 日志

back_log = 500

## Mysql 连接请求队列存放数，当某一时刻客户端连接请求过多，造成 Mysql Server 无法为其建立连接时存放的数量，最大 65535（ 增大时需同时调整 OS 级别的网络监听队列限制 ）

max_connections = 1000

## Mysql 最大连接数，直接影响 Mysql 应用的并发处理能力（ 500~1000 是个比较合适的值，注意每建立的连接都会占用一定的内存空间，直到连接被关闭才释放内存 ）

wait_timeout = 100
interactive_timeout = 100

## 服务器关闭非交换连接之前等待活动的秒数，默认 28800 秒（ 注意：在 my.cnf 中修改这个参数需要配合 interactive_timeout (超时间隔)参数一起修改，否则不生效）
##（ Mysql 处理完一条连接后所等待释放的时间，如果并发很高，默认设置会导致最大连接被占满，出现 "too many connections" 错误 ）
##（ 如果这个值很低，比如 5 ，可能会导致出现 "ERROR 2006 (HY000) MySQL server has gone away" 的错误，出现这个错误还有可能是 max_allowed_packet 设置过小 ）
## 真实案例

max_connect_errors = 1000

## 最大连接失败次数，跟性能没有太大关系，主要跟安全方面有关（ 达到此上限后会无条件阻止其连接数据库 ，默认 100 ）

connect_timeout = 20

## 连接超时时间为 20 秒

max_allowed_packet = 16M

## 网络传输中一次消息量的最大值，默认 4M ，必须设为 1024 的整倍数

table_open_cache = 2000

## 打开文件描述符的缓存个数，防止系统频繁打开、关闭描述符而浪费资源（ 对性能有影响，默认 2000 ）

read_buffer_size = 512K

## Mysql 读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，Mysql 会为其分配一段内存缓冲区（ 默认 128K ，此参数为每线程分配 ）

read_rnd_buffer_size = 512K

## Mysql 随机 Query 缓冲区大小，当按任意顺序读取行时，将分配一个随机读取缓冲区。如进行排序查询时，Mysql 会首先扫描该缓冲，避免磁盘搜索，提高查询速度（ 默认 256K ，该缓冲也是为每线程分配 ）

sort_buffer_size = 512K

## 系统中对数据进行排序时使用的 buffer ，如果系统中排序比较大，且内存充足、并发不大时，可以适当增大此值（ 默认 256K ，此参数为每线程分配独立的 buffer ）

join_buffer_size = 512K

## join 为 ALL、index、rang 或 index_merge 时使用的 buffer（ 默认 256K ，每 Thread 都会建立自己独立的 buffer ）

thread_stack = 256K

## 参数表示每线程的堆栈大小

thread_cache_size = 64

## Thread Cache 池中存放的连接线程数（ 此池中的线程不是启动服务时就创建的，而是随着连接线程的创建和使用，逐渐将用完的线程存入其中，达到此值后将不再缓存连接线程 ）
## 缓存命中率计算公式：Thread_Cache_Hit = ( Connections - Thread_created ) / Connections * 100%
## 系统运行一段时间后，Thread Cache 命中率应该保持在 90% 以上

explicit_defaults_for_timestamp = 1

## 如果此参数不开启，error_log 中会有警告信息

query_cache_type = 1

## 是否启用 query_cache ，0 为不使用（ 若要关闭 query_cache 时，需同时将 query_cache_size 、query_cache_limit 设为 0 ）

query_cache_size = 32M

## 查询缓冲大小，当重复查询时会直接从该缓冲中获取，但是当所查询表有改变时，缓冲的查询将失效（ 频繁写入、更新、高并发的环境下建议关闭此缓冲 ）

query_cache_limit = 1M

## 单个查询所能够使用的缓冲区大小

ft_min_word_len = 1

## 使用全文索引最小长度

transaction_isolation = REPEATABLE-READ

## 事务隔离级别，为了有效保证并发读取数据的正确性（ 默认 Repeatables Read 即：可重复读 ）
## Innodb 有四种隔离级别：Read Uncommitted（ 未提交读 ）、Read Committed（ 已提交读 ）、Repeatable Read（ 可重复读 ）、Serializable（ 可序列化 ）

tmp_table_size = 32M

## 临时表大小

key_buffer_size = 32M

## 用来缓存 MyISAM 存储引擎的索引（ 默认 8M ，如果使用 Innodb 存储引擎，此值设为 64M 或更小 ）
## 计算公式：key_reads / key_read_requests * 100% 的值小于 0.1%

## Innodb 存储引擎相关参数

innodb_file_per_table = 0

## 关闭独享表空间，使用共享表空间

innodb_buffer_pool_size = 256M

## Innodb 存储引擎核心参数，用于缓存 Innodb 表的索引、数据（ 默认 128M ，单独使用 Innodb 存储引擎且单一 Mysql 服务时建议设为物理内存的 70% - 80 % ）
## 可以通过 show status like 'innodb_buffer_pool_%'; 来获取 innodb buffer pool 的实时状态信息
## Innodb_buffer_pool_pages_total 总共的 pages（ Innodb 存储引擎中所有数据存放最小物理单位 page ，每个 page 默认为 16KB ）
## Innodb_buffer_pool_pages_free  空闲的 pages
## Innodb_buffer_pool_pages_data  有数据的 pages
## Innodb_buffer_pool_read_requests  总共的 read 请求次数
## Innodb_buffer_pool_reads  读取物理磁盘读取数据的次数，即：在 buffer pool 中没有找到
## Innodb_buffer_pool_wait_free  因 buffer 空间不足而产生的 wait_free
## Innodb_buffer_pool_read_ahead_rnd  记录进行随机读的时候产生的预读次数
## Innodb_buffer_pool_read_ahead_seq  记录连续读的时候产生的预读次数
## Innodb_buffer_pool_size  使用率 = innodb_buffer_pool_pages_data / innodb_buffer_pool_pages_total * 100%
## Innodb_buffer_pool_read  命中率 = （ innodb_buffer_pool_read_requests - innodb_buffer_pool_reads ）/ innodb_buffer_pool_read_requests * 100%

innodb_use_sys_malloc = 1

## 使用系统自带的内存分配器，替代 innodb_additional_mem_pool_size 参数

innodb_data_file_path = ibdata1:512M:autoextend

## 指定一个大小为 512M 的、可扩展的 ibdata1 数据文件

innodb_read_io_threads = 4
innodb_write_io_threads = 4

## Innodb 使用后台线程处理数据页上的 IO 请求，根据 CPU 核数修改，默认 4

innodb_thread_concurrency = 0

## Innodb 线程并发数，0 为不限制，默认 0

innodb_flush_log_at_trx_commit = 2

## Innodb 事务日志刷新方式，0 为每隔一秒 log thread 会将 log buffer 中的数据写入到文件，并通知文件系统进行文件同步 flush 操作，极端情况下会丢失一秒的数据
## 1 为每次事务结束都会触发 log thread 将 log buffer 中的数据写入文件并通知文件系统同步文件，数据最安全、不会丢失任何已经提交的数据
## 2 为每次事务结束后 log thread 会将数据写入事务日志，但只是调用了文件系统的文件写入操作，并没有同步到物理磁盘，因为文件系统都是有缓存机制的，各文件系统的缓存刷新机制不同
## 当设为 1 时是最为安全的，但性能也是最差的。0 为每秒同步一次，性能相对高一些。设为 2 性能是最好的，但故障后丢失数据也最多（ OS 跟主机硬件、供电足够安全可以选择，或对数据少量丢失可以接受 ）。

innodb_log_buffer_size = 8M

## 事务日志所使用的缓冲区。Innodb 在写事务日志时，为了提高写 Log 的 IO 性能，先将信息写入 Innodb Log Buffer 中，当满足 Innodb_flush_log_trx_commit 参数或日志缓冲区写满时，再将日志同步到磁盘中。
## 默认 8M ，一般设为 16~64M 即可，可以通过 show status like 'innodb_log%'; 查看状态

innodb_buffer_pool_dump_at_shutdown = 1

## 关闭数据库时把热数据 dump 到本地磁盘。

innodb_buffer_pool_dump_now = 1

## 采用手工方式把热数据 dump 到本地磁盘。

innodb_buffer_pool_load_at_startup = 1

## 启动时把热数据加载到内存。

innodb_buffer_pool_load_now = 1

## 采用手工方式把热数据加载到内存。

## 以上四条参数会快速预热 Buffer_pool 缓冲池，当机器正常重启后，热数据还保留在内存中，避免瞬间连接数爆满导致机器死机。

[mysqldump]
quick
max_allowed_packet = 4M

## 使用 mysqldump 工具备份数据库时，当某张表过大时备份会报错，需要增大该值（ 增大到大于表大小的值 ）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263

my.cnf服务器参数配置

1.使用innodb注意事项

a) 所有InnoDB数据表都创建一个和业务无关的自增数字型作为主键，对保证性能很有帮助；
b) 杜绝使用text/blob，确实需要使用的，尽可能拆分出去成一个独立的表；
c) 时间戳建议使用 TIMESTAMP 类型存储；
d) IPV4 地址建议用 INT UNSIGNED 类型存储；
e) 性别等非是即非的逻辑，建议采用 TINYINT 存储，而不是 CHAR(1)；例可以使用0,1,2来表示，未知,男,女，优点搜索快，缺点显示/存储都要转换
f) 存储较长文本内容时，建议采用JSON/BSON格式存储；
1
2
3
4
5
6

skip-innodb 
#如果你的MySQL服务包含 InnoDB 支持但是并不打算使用的话,
#使用此选项会节省内存以及磁盘空间,并且加速某些部分
 
innodb_status_file = 1 
#启用InnoDB的status file，便于管理员查看以及监控等 show engine innodb status\G
 
innodb_open_files = 2048 
#限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
 
innodb_additional_mem_pool_size = 100M 
#设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，
#所以当我们一个MySQL Instance中的数据库对象非常多的时候，
#是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
 
innodb_write_io_threads = 4
innodb_read_io_threads = 4
#innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
#注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，
#修改完后重启MySQL服务,允许值的范围从 1-64
 
innodb_data_home_dir =  /usr/local/mysql/var/ 
#设置此选项如果你希望 InnoDB 表空间文件被保存在其他分区.默认保存在 MySQL 的 datadir 中.
 
innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend
#InnoDB将数据保存在一个或者多个数据文件中成为表空间.如果你只有单个逻辑驱动保存你的数据,
#一个单个的自增文件就足够好了.其他情况下.
#每个设备一个文件一般都是个好的选择.你也可以配置 InnoDB 来使用裸盘分区
 
innodb_file_io_threads = 4 
#用来同步 IO 操作的 IO 线程的数量. 
#此值在 Unix 下被硬编码为 4,但是在 Windows 磁盘 I/O 可能在一个大数值下表现的更好.
 
innodb_thread_concurrency = 16
#在InnoDb 核心内的允许线程数量,InnoDB 试着在 InnoDB 内保持操作系统线程的数量少于或等于这个参数
#给出的限制,最优值依赖于应用程序,硬件以及操作系统的调度方式.过高的值可能导致线程的互斥颠簸.
#默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量
 
innodb_flush_log_at_trx_commit = 1 
#0 每秒将日志缓冲区写入log file，并同时flush到磁盘。跟事务提交无关。在机器crash并重启后，
#0 会丢失一秒的事务日志数据（并不一定是1s，也许会有延迟，跟操作系统调度有关）。
#1 每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。（crash不会丢失事务日志）
#2 每次事务提交将日志缓冲区写入log file，每秒flush一次到磁盘。（crash有可能丢失数据）
 
innodb_log_buffer_size = 8M 
#用来缓冲日志数据的缓冲区的大小.当此值快满时, InnoDB 将必须刷新数据到磁盘上.
#由于基本上每秒都会刷#新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)
 
innodb_log_file_size = 500M 
#事务日志大小.在日志组中每个日志文件的大小，
#你应该设置日志文件总合大小到你缓冲池大小的5%~100%，
#来避免在日志文件覆写上不必要的缓冲池刷新行为.不论如何,
#请注意一个大的日志文件大小会增加恢复进程所需要的时间.
 
innodb_log_files_in_group = 2 
#在日志组中的文件总数.通常来说 2~3 是比较好的.
 
innodb_log_group_home_dir =  /usr/local/mysql/var/
#InnoDB的日志文件所在位置. 默认是 MySQL 的 datadir.
#你可以将其指定到一个独立的硬盘上或者一个 RAID1 卷上来提高其性能
 
innodb_max_dirty_pages_pct = 90 #innodb 
#主线程刷新缓存池中的数据，使脏数据比例小于 90%,这是一个软限制,不被保证绝对执行.
 
innodb_lock_wait_timeout = 50 
#InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数。
#InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。
#InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
 
innodb_flush_method = O_DSYNC 
#InnoDB 用来刷新日志的方法.表空间总是使用双重写入刷新方法.
#默认值是 “fdatasync”, 另一个是 “O_DSYNC”.
 
innodb_force_recovery=1
 
#如果你发现InnoDB 表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.
#从1 开始并且增加此值知道#你能够成功的导出表.
 
innodb_fast_shutdown 
#加速InnoDB的关闭. 这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.
#但是取而代之的是InnoDB可能在下次启动时做这些操作.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

2.查询缓存相关

query_cache_type=1  #0表示禁用缓存，1表示会缓存所有的结果，2表示只缓存SQL_CACHE缓存
query_cache_limit = 2M
query_cache_size = 64M 或32M 或128M 
1
2
3

3.系统资源相关

back_log = 500  #如果系统在一个短时间内有很多连接，则需要增大该参数的值小于512
max_connections = 1000 #默认100,生产则需要增大该参数值,最大连接数16384
1
2

4.二进制日志相关

server_id=1                    
log_bin=mysql-bin
#log-bin-index=master-bin.index
expire_logs_days = 7
#binlog_format = row    #默认为mix，新版中设为这两项可提高安全性
#binlog_row_image = minimal
max_binlog_size = 100m   #默认是1G
binlog_cache_size = 4m
#binlog-do-db = DBNAME #指定mysql的binlog日志只记录哪个库
max_binlog_cache_size = 512m  #生产4g
#skip-slave-start
1
2
3
4
5
6
7
8
9
10
11

系统资源相关

max_connect_errors = 10000  
#如果某个用户发起的连接 error 超过该数值，则该用户的下次连接将被阻塞，直到管理员执行 flush hosts命令或者服务重启， 
#防止非法的密码以及其他在链接时的错误会增加此值
 
connect-timeout = 10 
#连接超时之前的最大秒数,在 Linux 平台上，该超时也用作等待服务器首次回应的时间
 
wait-timeout = 28800 #等待关闭连接的时间
 
interactive-timeout = 28800 
#关闭连接之前，允许 interactive_timeout（取代了wait_timeout）秒的不活动时间。
#客户端的会话 wait_timeout 变量被设为会话interactive_timeout 变量的值。
#如果前端程序采用短连接，建议缩短这2个值, 如果前端程序采用长连接，可直接注释掉这两个选项，
#默认配置(8小时)  
 
slave-net-timeout = 600 
#从服务器也能够处理网络连接中断。但是，只有从服务器超过slave_net_timeout 秒没有从主服务器收到数据才通知网络中断
 
net_read_timeout = 30      #从服务器读取信息的超时
 
net_write_timeout = 60     #从服务器写入信息的超时
 
net_retry_count = 10       #如果某个通信端口的读操作中断了，在放弃前重试多次。
 
net_buffer_length = 16384  
#包消息缓冲区初始化为 net_buffer_length 字节，但需要时可以增长到 max_allowed_packet 字节
 
max_allowed_packet = 64M
#服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小
#当与大的BLOB 字段一起工作时相当必要， 每个连接独立的大小.大小动态增加。
#设置最大包,限制server接受的数据包大小，避免超长SQL的执行有问题 默认值为16M，
#当MySQL客户端或mysqld服务器收到大于 max_allowed_packet 字节的信息包时，将发出“信息包过大”错误，并关闭连接。
#对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与 MySQL 服务器的连接”错误。默认值 16M。
 
table_cache = 512 
# 所有线程所打开表的数量. 增加此值就增加了mysqld所需要的文件描述符的数量这样你需要确认在
# [mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少4096
 
thread_stack = 192K 
# 线程使用的堆大小. 此容量的内存在每次连接时被预留.
# MySQL本身常不会需要超过 64K 的内存如果你使用你自己的需要大量堆的 UDF 函数
# 或者你的操作系统对于某些操作需要更多的堆,你也许需要将其设置的更高一点.默认设置足以满足大多数应用
 
thread_cache_size = 20 
#在 cache 中保留多少线程用于重用.当一个客户端断开连接后,
#如果 cache 中的线程还少于 thread_cache_size,则客户端线程被放入 cache 中.
#这可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)
 
thread_concurrency = 8  
#允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.该参数取值为服务器逻辑CPU数量×2
 
query_cache_min_res_unit = 2K 
#查询缓存分配的最小块大小.默认是 4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，
#就容易造成内存碎片和浪费查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks*100%
#如果查询缓存碎片率超过 20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，
#或者试试减小query_cache_min_res_unit，
#如果你的查询都是小数据量的话。查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size *100%
#查询缓存利用率在25%以下的话说明 query_cache_size 设置的过大，可适当减小;
#查询缓存利用率在 80%以上而且 Qcache_lowmem_prunes > 50 的话
#说明 query_cache_size 可能有点小，要不就是碎片太多。
#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
 
tmp_table_size = 512M 
#临时表的最大大小，如果超过该值，则结果放到磁盘中,此限制是针对单个表的,而不是总和
 
max_heap_table_size = 512M 
#独立的内存表所允许的最大容量.此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

结合一下实际问题来回顾这个配置的使用：

第一个常见问题：

mysql数据库磁盘io高的排查

第二个常见问题：默认存储路径

这个就更常见了，几乎每装一次mysql都会遇到。

centos7 修改MySQL默认存储路径

第三个常见问题：mysql的最大连接数

MySQL: ERROR 1040: Too many connections”的异常情况，造成这种情况的一种原因是访问量过高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力；另一种原因就是MySQL配置文件中max_connections值过小。

Mysql5.5 mysql5.6  mysql5.7：默认的最大连接数都是151，上限为：100000
1

Mysql5.0版本：默认的最大连接数为100，上限为16384
1

修改mysql配置文件my.cnf，在[mysqld]段中添加或修改max_connections值：

max_connections=512
1

重启mysql服务即可。

总体来说，该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求。否则将会出现类似”Too many connections”的错误。

第三个常见问题：mysql开启慢查询

slow_query_log="ON"
slow_query_log_file="文件路径（绝对路径）"
log_queries_not_using_indexes="ON"
long_query_time=1
1
2
3
4

slow_query_log = 1
表示开启慢查询，0表示关闭

slow_query_log_file
指定慢查询日志路径
需要MySQL对该路径有写权限

long_query_time = 1
表示查询时间>=1秒才记录日志
默认10s

log_queries_not_using_indexes = 1
表明记录没有使用索引的 SQL 语句

重启后生效。

第四个常见问题：group_concat字符长度限制

group_concat长度限制默认是1024，有两个解决方法：

my.cnf,在里面添加以下信息：

group_concat_max_len = -1  # -1为最大值或填入你要的最大长度

或者自己设置group_concat_max_len = 1024000。
1
2
3

注意：需要重启MySQL才能生效。

好吧，结合你们自己的情况，实际去操作一下，理解一下吧，祝你们好运。