干货｜MySQL配置优化32个技巧详解，附面试题+教程

希望小编本文能起到抛砖引玉的效果。但是总得来看，数据库优化的思想差不多就这些了。希望能对大家有所帮助。这边整理了一些有关Java架构的资料mssql 完整外部连接，包括（Dubbo、Redis、设计模式、Netty、zookeeper、Spring cloud、分布式、高并发等）

可以免费提供给大家，获取方式在文末。

扩展配置

max_connections

允许最大连接数，默认100，最大16384。这个根据性能调节，如果3000连接就会导致mysql的资源不够，那就给3000.因为再给多了，就会导致其它连接的资源被抢占。

建议：

根据需求来看，一般2核4G机器填写1000，16核64G填写5000。

测试运行后，查询当前所有连接数和服务器负载情况。若连接数满了，但负载还不是很大，可以加大连接数。

查询配置：statusThreads就是连接数

在线配置：

配置文件参数：max_connections=5000

connect_timeout

建立三次握手的超时时间，可能是客户端和服务端网络问题导致的链接超时，单位秒。

查询配置：

在线配置：

配置文件参数：connect_timeout=10

interactive_timeout|wait_timeout

控制连接最大空闲时长的参数。默认28800，也就是8小时，单位秒。

wait_timeout控制非交互，比如java程序的链接，interactive_timeout控制交互，比如mysql命令进行的操作。

建议：

通常情况下300秒就足够了，这样防止有些链接假死，不做操作单还占用链接。

查询：show global variables like '%timeout%';

在线配置：set global wait_timeout=300; | set global interactive_timeout=300;

配置文件：interactive_timeout = 300 | wait_timeout = 300

net_retry_count

如果读或写一个通信端口中断，mysql放弃前尝试连接的次数。在FreeBSD系统中此值应设置很高，因为FreeBSD内部中断被发送到所有线程去。

查询配置：

在线配置：

配置文件参数：net_retry_count = 100

thread_concurrency

这个变量是针对Solaris系统的，设定为内核数的2倍。

如果设置这个变量的话，mysqld就会调用thr_setconcurrency()。这个函数使应用程序给同一时间运行的线程系统提供期望的线程数目。

查询配置：

在线配置：

配置文件参数：thread_concurrency = 8

thread_cache_size|thread_stack

每一个客户端连接都会有一个与之对应的连接线程。在MySQL中实现了一个Thread Cache池，将空闲的连接线程存放其中，而不是完成请求后就销毁。

这样，当有新的连接请求时，MySQL首先会检查Thread Cache中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的连接线程。3G内存设置64个比较好

每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。thread_stack控制这个值。16G/32G机器设定512K，太小会有 Thread stack overrun 错误。

可以用sql语句show global status like 'Thread%';来查看参数

+—————————-+———-+

| Variable_name | Value |

+—————————-+———-+

| Threads_cached | 1 |

| Threads_connected | 1 |

| Threads_created | 2 |

| Threads_running | 1 |

+—————————-+———-+

Threads_cached，如果太大，证明一直在创建新的线程，可以将thread_cache_size调大。

查询配置：show variables like 'thread_%';

在线配置：

配置文件参数：thread_cache_size = 64 | thread_stack = 1M

open_files_limit

mysql可以打开的最大文件数，不能超过 ulimt -n 看到的数值

查询配置：

在线配置：

配置文件参数：open_files_limit = 65535

max_connect_errors

实验参考

当客户端连接延迟超过connect_timeout定义的时间时，将会在performance_schema数据库下host_cache表中进行记录。

可以用use performance_schema;select * from host_cache\G;来查看SUM_CONNECT_ERRORS字段将会增加。

当超过的次数等于max_connect_errors定义的次数时，将会报错如下：

ERROR 1129 (HY000): Host ‘10.10.10.101’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

尽量去改善网络环境，或者将max_connect_errors值调大

查询配置：show variables like '%max_connect_error%';

在线配置：set global max_connect_errors=3000;

配置文件参数：max_connect_errors = 3000

back_log

在MySQL的链接数达到max_connections时，当前无法处理新的请求，将存放到堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令

cat /proc/sys/net/ipv4/tcp_max_syn_backlog

建议：

推荐设置为350

查询：show variables like 'back_log';

在线配置：

配置文件：back_log= 350

max_allowed_packet

mysql根据配置文件会限制server接收的数据包大小。

有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。

建议：

大部分情况下4M就足够了，如果还是不够慢慢加。

查询：show VARIABLES like '%max_allowed_packet%';

在线配置：set global max_allowed_packet = 4*1024*1024*

配置文件：max_allowed_packet = 4M

ft_min_word_len

开启全文索引，默认关闭。根据需求开启，如果没使用全文索引，就不要开启。

查询：

在线配置：

配置文件：ft_min_word_len = 1

auto_increment_increment|auto_increment_offset

这两个参数一般用在主主同步中，用来错开自增值, 防止键值冲突

查询：show variables like 'auto_inc%';

在线配置：

配置文件：auto_increment_increment = 1 | auto_increment_offset = 1

log_bin_trust_function_creators

如果开启了主从复制，要设置为0，禁止用户创建函数，触发器。因为存储函数有可能导致主从的数据不一致。

如果只开启Binlog，没主从，则设置为1。

查询：

在线配置：

配置文件：log_bin_trust_function_creators = 1

read_buffer_size

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

建议：

8G机器可以设置此参数为1M

查询：

在线配置：

配置文件：read_buffer_sizes = 4M

performance_schema

5.5版本以后默认打开，用于收集性能参数，在实例中也会有对应名称的一个库。

查询：

在线配置：

配置文件：performance_schema = 1

skip-locking|skip-external-locking

避免MySQL的外部锁定，减少出错几率增强稳定性。

5以前版本skip-locking，新版本skip-external-locking

当外部锁定（external-locking）起作用时，每个进程若要访问数据表，则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁，因此在单服务器环境下external locking会让MySQL性能下降。

查询：

在线配置：

配置文件：skip-locking | skip-external-locking

skip-name-resolve

禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

查询：

在线配置：

配置文件：skip-name-resolve

table_cache

它的作用就是缓存表文件描述符，降低打开关闭表的频率

mysql只有一个全局锁来控制打开和关闭表，也就是说无论有多少个线程在并行执行，只有一个线程可以打开或关闭表，这也就会出现很多死锁，别的线程等待那个全局锁

相应地增加了cpu的消耗，延长了其他链接线程执行sql的时间，降低系统性能。所以在保证table_cache够用的情况下，尽量保持table_cache足够小

查询：

在线配置：

配置文件：table_cache = 128K

init_connect

init_connect是用户登录到数据库上之后，默认执行里面的内容，类似Linux系统的/etc/profile。在用户操作前，可以先进行设定字符集，或者初始化一些东西。

但内容里面语法有问题，会导致用户从mysql退出。init_connect 对具有super 权限的用户是无效的。

查询：

在线配置： set global init_connect=set autocommit=0; set names gbk;'

配置文件：init_connect='set autocommit=0; set names gbk;'

explicit_defaults_for_timestamp

参考实验

明确时间戳默认null方式。如果高于5.5.6版本，创建如下

create table mytime (

id int,

atime timestamp not null,

ctime timestamp not null

);

出现如下错误，将变量改为true即可

ERROR 1067 (42000): Invalid default value for ‘ctime’

=false时，按照如下规则”初始化”:

未明确声明为NULL属性的TIMESTAMP列被分配为NOT NULL属性。 （其他数据类型的列，如果未显式声明为NOT NULL，则允许NULL值。）将此列设置为NULL将其设置为当前时间戳。

表中的第一个TIMESTAMP列（如果未声明为NULL属性或显式DEFAULT或ON UPDATE子句）将自动分配DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。

（编辑：上海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!