阿里云硬件服务器

说明

朋友们，您是否安装了MySQL直接使用？是否对调优比较迷茫？是否想找一套比较标准的调整配置？之前我也是这样，不知道怎么配置才能发挥MySQL最大能力。于是我近几年好好地研究了数据库（MySQL、PostGreSQL），下面把我的总结给大家分享。MySQL的参数配置没有一个标准的答案，要因地制宜，要根据数据库的实际运行情况来进行不断地调整。一套配置可以达到在某一个时间段运行比较好，但几乎不可能达到在所有时间都非常合适。下面的配置都是适用于大多数情况。

连接部分

max_connections=3000

最大连接数。空闲的连接几乎不使用CPU，也就是占用点内存（主要是线程栈），大概300K。活跃连接可能还会使用其他的缓冲区会占用稍大的内存（大小根据配置决定），按平均大概3M计算。假如MySQL的可用内存为 24G，24G ÷ 3MB ≈ 8000 连接。但这是理论最大值，实际上会受到CPU等各方面的限制，实际推荐先设置 2,000~4,000，然后观察数据库运行情况根据实际情况进行修改。

一个正常的业务系统要保证可用性，应该是让尽可能多的连接进来，如果数据库有压力也应该考虑怎么减少每个连接的占用内存。OLTP服务每个连接生命周期短，总连接数可以设置较高。OLAP服务每个连接占用资源多、时间长，总连接数应该较低。

back_log=500

连接请求队列容量。当服务器连接数达到max_connections时，还能够让多少个连接处于等待状态。建议为 max_connections 的 20% 到 50%。

innodb_thread_concurrency

Innodb最大并发线程数。处理请求的最大线程数，0表示不限制，建议CPU核心数的2倍（特别是和其他应用共用服务器时要保证别让MySQL自己就使用了过多的线程而影响其他应用）。

内存部分

innodb_buffer_pool_size

InnoDB存储引擎的中心缓存。建议为可用内存的 60-80%（留出足够的内存给其他进程）。这是MySQL最最最重要的配置，甚至对于大多数场景只设置这一个参数就可以让MySQL跑的很好。

一个健壮高效的MySQL缓存命中率（怎么计算？？）应大于99%，否则就应该调整 innodb_buffer_pool_size。

innodb_buffer_pool_instances

缓冲池实例数。当缓冲池大于1GB时建议多个缓冲池实例，增加并发。每个实例至少1GB大小（都有足够的空间来存储数据页），但实例数不应超过CPU核心数。

join_buffer_size=8M

关联缓冲区大小。 MySQL 为 块嵌套循环连接（Block Nested-Loop Join, BNL） 专门开辟的 会话级 内存区，用来 缓存驱动表的一批记录，减少被驱动表的扫描次数。也就是说只有被驱动表上没有用上索引时才使用它，主要是使用BNL或BKA算法时使用（NLJ算法不使用它）。建议 8-16MB。

注：我们要使用NLJ算法（用索引去关联被驱动表）以提升查询效能；不到万不得已不要使用这块内存。

sort_buffer_size=2M

排序缓冲区。为每个需要执行排序操作的连接分配的排序内存大小，可让查询充分使用内存排序而避免文件排序，不同会话之间不共享。ORDER BY、GROUP BY、DISTINCT都会使用到。建议 2-4MB。

read_rnd_buffer_size=3M

随机读缓冲区。 MySQL 在随机读场景下使用的会话级缓冲区，专门用来减少磁盘寻道、优化排序后回表的性能。通常在出现Using filesort+需要回表+数据行数较多的场景，建议 2-4MB。

当一个带有 order by 的查询使用了 Using filesort，查询结果挺多，还需要回表； 如果拿着每条数据里的主键去主键索引树捞数据，由于主键是无序的，就会产生很多的磁盘寻道，效能比较差。为了优化这块，MySQL专门分配一片块存，把这一批主键放进去，然后进行排序，最后拿着排序后的主键去主键索引树捞数据，这样就很大层度上变成了顺序读，效能提升很多，这块内存就是 read_rnd_buffer。

tmp_table_size

内部临时表大小。单个查询隐式创建的内存临时表的内存最大值，默认16M，建议默认。

max_heap_table_size

堆表最大内存。限制用户显式创建的 MEMORY 表（CREATE TABLE … ENGINE=MEMORY）在内存中的最大尺寸；同时也被优化器拿来当作单个内部临时表的上限，默认16M，建议默认。换句话说，单个查询隐式创建的内存临时表大小取 tmp_table_size 和 max_heap_table_size 的较小值。

日志部分

innodb_flush_log_at_trx_commit

RedoLog落盘机制。可选值如下：

0：事务提交后数据写入RedoLog Buffer，后台线程定期(1秒)进行write和sync，最高效；

1：事务提交后数据write到OS_Cache并立即sync，最安全；

2：事务提交后数据write到OS_Cache但不sync，1秒sync一次盘，折中方案。

建议首先设置 1（毕竟可靠性是非常非常重要的），如发现性能不好可以再酌情修改。

注：不光MySQL，其实计划所有的应用在写数据的时候都是先把数据写到操作系统缓存，然后再由操作系统写入磁盘。这样就涉及到用户态和内核态的切换，为什么要这样呢？主要是为了效能（写OS_Cache是写内存效能非常高）和安全（操作系统管理文件，不能让应用直接修改文件）。

innodb_log_file_size

RedoLog文件大小。决定了InnoDB引擎RedoLog文件的大小，默认48M，为避免峰值时RedoLog充满导致业务堵塞，建议1GB~4G。

innodb_log_files_in_group

RedoLog文件数量。决定了InnoDB引擎RedoLog文件数量，默认2，建议设置2~4。

innodb_redo_log_capacity

RedoLog文件总容量。决定了InnoDB引擎RedoLog文件总容量，建议至少2G（从MySQL 8.0.30开始引入，如果显示定义了此参数则覆盖innodb_log_file_size和innodb_log_files_in_group，如果未显示定义此参数则使用innodb_log_file_size和innodb_log_files_in_group，如果三者都未显示定义则使用innodb_redo_log_capacity的默认值）。

log_bin

是否开启binlog。ON：开启；OFF：不开启。为了数据恢复和主从同步建议开启。

云服务器的主板

binlog_format

binlog日志格式。可选值如下：

Statement：每一条修改数据的SQL都会记录 binlog 。Row：不记录SQL 语句上下文信息，仅保存哪条记录被修改。Mixed：Statement 和 Row 的混合体。

建议设置 Row。

binlog_cache_size

BinLog缓存大小。binlog的内存缓冲区的大小，建议 2-4M。

binlog_expire_logs_auto_purge

BinLog过期文件自动清理。为避免磁盘被占满，建议 ON。

binlog_expire_logs_seconds

BinLog过期时间。单位是秒，0表示永远不过期，大于0表示有具体的过期时间（MySQL5.7之前是expire_logs_days）。

binlog_cache_size

BinLog缓存大小。binlog的内存缓冲区的大小，建议 2-4M。

sync_binlog

BinLog落盘机制，可选值如下：

0：事务提交后数据只write到OS Cache但不sync，最高效；

1：事务提交后数据write到OS Cache并立即sync，最安全；

n（n>1）：事务提交后数据write到OS Cache但不sync，每n个事务sync一次，折中方案；

建议平时设置 1（毕竟可靠性是非常非常重要的），如发现性能不好可以再酌情修改。

注：sync_binlog=1 和innodb_flush_log_at_trx_commit=1 就是平时说的双一方案。

性能部分

transaction-isolation

事务隔离级别。默认RR（REPEATABLE READ），此时有间隙锁，并发高了会有更高的锁冲突几率；若能接受偶发的幻读，建议设置RC（READ-COMMITTED）。

注：大厂几乎都改成了RC，我觉得99%的场景都可以用RC，我们一直用RC。

default-time-zone

时区。默认值为 SYSTEM（跟 OS 走），若数据库都部署在国内强烈建议直接设置东八区 +08:00 以提升性能，还可以避免 数据库时间差 8 小时问题。

lower_case_table_names

表/库名自动转小写。initialize时可以设置，后面不好修改(可同时修改my.cnf和ibd文件)；建议 initialize时直接设置 1（规范起来：库名、表名、字段名等全表小写；windows本身就是大小写不敏感）。

skip-name-resolve

电脑小米云服务器

禁用DNS解析。禁用DNS解析可以减少连接时延，提高性能，建议设置为 ON。

max_execution_time

最大查询时长。单位毫秒，超过这个阈值MySQL服务器将终止该查询，只对顶层的 SELECT 语句有效，对于嵌套查询或存储程序中的查询不起作用；建议根据实际场景设置避免大查询耗尽CPU。

open_files_limit=65535

MySQL进程同时打开的最大文件数。实际生效值受操作系统限制影响，建议和操作系统的open_files_limit同时设置65535，避免出现 Too many open files问题。

max_allowed_packet

最大数据包。为避免主从同步因event包过大导致同步crash，建议将此参数和Replica最大数据包（replica_max_allowed_packet）、Slave最大数据包（slave_max_allowed_packet）、复制队列最大内存（slave_pending_jobs_size_max）均设置为最大值1G。

监控部分

slow_query_log

慢查询：会对MySQL性能产生一点影响，但为了方便排查问题建议将慢查询(slow_query_log)和慢查询扩充(log_slow_extra)均打开，为了保证慢查询日志的完整建议设置日志输出方式为FILE。

performance_schema

Performance监控。基于内存的轻量级监控，提供细致的运行时指标，可动态启用/禁用特定监控项，开启后才能采集未使用索引、额外排序查询、临时表查询、全表扫描查询等，对性能影响通常很小，建议设置ON。

总结

1、MySQL调优，很多地方就跟选美女一样（增一分则胖，减一分则瘦），并没有一套标准的完美的调优配置，比如 join_buffer_size、sort_buffer_size、tmp_table_size等几个线程级别的配置，要充分考虑实际的并发量，既要让单个线程够用，又要避免并发高时占满内存。

2、上面这些参数是我一直使用的，有不对或不足的地方欢迎一起讨论。

安卓连接云服务器