Mariadb 服务器配置

官方文档：

• 优化配置相关专题

  • 硬件层面调优

• 操作系统层面调优

  • 系统最大文件句柄数量
    • 系统内存不可交换 swappiness
  • 内存调优：MariaDB Memory Allocation

• Buffers, Caches and Threads 调优

  • 线程池配置[普通优化]：Thread Pool System and Status Variables
  • 线程状态查看[运维]：Thread States

    • 数据库内存缓存池大小（总大小 <= 70%）

      • InnoDB Buffer Pool

      • InnoDB Change Buffering —— 缓存更新策略配置 防止脏数据

    • 数据库查询缓存大小（单个查询大小 一般1M左右，不能太大 也不能太小 太小导致碎片化）

      • query cache
  • mariadb集群
  • mariadb同步和主从复制

• 系统可配置的变量

• mysqld Options

• Configuring MariaDB with Option Files

配置文件位置

mac 配置文件位置

# 通过mdfind查找
mdfind -name my.cnf

# 例子
zjh@zjhdeMBP ~ % mdfind -name my.cnf
/usr/local/etc/my.cnf.default
/usr/local/etc/my.cnf.default.default
/Users/zjh/Library/Containers/com.yinxiang.Mac/Data/Library/CoreData/com.yinxiang.Mac/4829142D-429B-4F11-A724-D99CD70FDCC1/ENNote/_records/p599.enspot
/usr/local/etc/my.cnf.d
/usr/local/etc/my.cnf
zjh@zjhdeMBP ~ % cat /usr/local/etc/my.cnf

官方配置文件位置说明

查看配置｜监控状态

查看mariadb版本

# 方式1 通过mariadb 命令查看
mariadb
# 结果：
  zjh@zjhdeMBP ~ % mariadb
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 13
  Server version: 10.5.6-MariaDB Homebrew
  Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 方式2 通过mysqld 命令查看
mysqld --version
#	结果：
  zjh@zjhdeMBP ~ % mysqld --version
  mysqld  Ver 10.5.6-MariaDB for osx10.15 on x86_64 (Homebrew)

监控运行状态

# 1 打开 
mariadb
# 2 显示当前状态
show global status;
# 3 查看慢查询日志
show VARIABLES like '%slow%';
show global status like '%slow%';
SHOW PROCESSLIST; 
# 查找大于10秒的SQL进程
grep Query sql.txt  |awk -F\" '$12>10{print "kill "$2}'
mysql -uroot -p'mypasswd' -e "SHOW PROCESSLIST;" |grep Query |awk '$6>10{print "kill "$1}'
# 查看当前连接数/最大连接数/响应过的最大连接数
show status like 'Threads_connected';
show variables like 'max_connections';
show global status like 'Max_used_connections';
# 查看当前打开文件数/最大打开文件数
show global status like 'open_files';
show variables like 'open_files_limit';

查看当前加载的配置

# 1 打开 mariadb
mariadb
# 2 查看 加载的配置项
SHOW VARIABLES;

显示的结果

配置文件示例

官方github项目/配置文件

查看配置项说明

# $program --help --verbose
mysqld  --help
mysqld  Ver 10.5.6-MariaDB for osx10.15 on x86_64 (Homebrew)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Starts the MariaDB database server.
Usage: mysqld [OPTIONS]
For more help options (several pages), use mysqld --verbose --help.
# 根据提示 通过 mysqld --verbose --help 查看配置的options
mysqld --help --verbose

配置文件示例A

#以下是针对 2核4G内存 的 MariaDB 配置优化建议。这些设置适用于大多数通用场景，重点是让 MariaDB 在内存有限的情况下发挥最大性能。
#配置文件路径
#通常 MariaDB 的配置文件位于 /etc/my.cnf 或 /etc/mysql/my.cnf。以下配置可以直接添加或修改：
[mysqld]
# 基本设置
user = mysql
port = 3306
bind-address = 0.0.0.0           # 如果需要远程访问
skip_name_resolve = 1            # 跳过主机名解析，加快连接速度
character-set-server = utf8mb4   # 默认字符集设置为 utf8mb4
collation-server = utf8mb4_general_ci

# 内存相关
innodb_buffer_pool_size = 2G     # 设置为总内存的50%左右
innodb_buffer_pool_instances = 2 # 缓冲池实例，1核1个，2核设置为2
innodb_log_file_size = 256M      # 日志文件大小，调整到缓冲池的 1/4 ~ 1/2
innodb_log_buffer_size = 16M     # 日志缓冲区，适中即可
tmp_table_size = 64M             # 临时表大小
max_heap_table_size = 64M        # 内存表最大大小
query_cache_type = 0             # 禁用查询缓存（已过时，避免浪费资源）
query_cache_size = 0

# I/O 相关
innodb_flush_method = O_DIRECT   # 避免双缓存
innodb_io_capacity = 500         # 设置 I/O 上限，根据 SSD 性能调整
innodb_flush_neighbors = 0       # SSD 环境下关闭邻居刷新

# 并发优化
innodb_read_io_threads = 4       # 读线程
innodb_write_io_threads = 4      # 写线程
innodb_thread_concurrency = 8    # 最大并发线程数，设置为核数的 2~4 倍
thread_cache_size = 16           # 缓存线程数，减少线程创建消耗

# 连接相关
max_connections = 200            # 最大连接数，视场景调整
table_open_cache = 400           # 表缓存大小
open_files_limit = 65535

# 日志与慢查询
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2              # 慢查询定义时间（秒）

# 缓存优化
key_buffer_size = 32M            # MyISAM 的索引缓存（如果使用 InnoDB，可以设小一些）
table_definition_cache = 400     # 表定义缓存大小
performance_schema = 0           # 关闭性能模式，节约内存

配置文件示例1

#针对客户端的设置
[client]														
default-character-set = utf8				#指定字符集(mariadb默认是拉丁文)
port = 3306													#指定端口
socket = /data/mydata/mysql.sock		#指定socket

#针对登录mysql服务器端的设置
[mysql]				
port = 3306
socket = /data/mydata/mysql.sock
default-character-set  = utf8

#设置mysql-server相关信息
[mysqld]						
user = mysql											#指定运行用户为mysql(默认是以mysql用户运行的),经过试验,可以用非mysql之外的用户运行的
default-storage-engine  = InnoDB 	#指定默认引擎为innodb,(反正跟系统默认指定的没有冲突)
socket = /data/mydata/mysql.sock	#略过
pid-file = /data/mydata/mysql.pid	#略过
#禁止域名解析(优化)
skip-name-resolve									#禁用DNS查询
skip-host-cache										#禁止缓存主机名
#--skip-networking   							#禁用TCP/IP连接
#跳过外部锁定(优化)
skip-external-locking 						#external-locking 主要用于多进程条件下对Myisam表进行锁定,在锁定的过程中,每个进程若要访问数据需等之前的进程完成操作并解除锁定后才能访问数据表,大大提升安全性，同时也造成了性能的消耗,怎么用看大家把。
#连接优化
max_connections = 2000  		 			#最大连接数(怎么设置等我有时间琢磨先)
max_allowed_packet = 64M  		 		#设置server接受数据包大小,数据包大于此值,将不会接收
table_open_cache = 2048						#主要设置table高速缓存数量(默认情况mysql服务器为2G内存设置256-512,4G内存则为2048,当然内存越大,你也可以设置更大)
#其他优化
explicit_defaults_for_timestamp = 1  #主要针对timetamp列进行优化,为1时允许默认为null值
lower_case_table_names  = 0				#区分大小写
 	# 默认有3个值
 	#	0   表名进行存储或者比较(查询,检索)的时候都区分大小写
 	#	1   表名进行存储是小写,但是比较不区分大小写
 	#	2 	 表明存储分大小写,比较的时候是小写
 	#	unix,linux默认值是0,window是1(winods默认不区分大小写的)
#设置colltion(排序规则),下面这个是服务器级别的设置方法,还有数据库,表级别的。。。。	
collation-server utf8_general_ci 
character-set-server = utf8            
#注：colltion用于指定数据集如何排序，以及字符串的比对规则。https://www.cnblogs.com/EasonJim/p/8128196.html  #这篇博文介绍的比较详细
 
#MyISAM 						
#对Myisam存储引擎进行优化
key-buffer-size = 32M									#设置键高速缓存大小(所有引擎通用的),如果数据库以Myisam为主,可以适当增加此值。
myisam-recover-options = FORCE,BACKUP #主要提供mysq启动时Myisam自动恢复功能,有4个值,可结合使用。
 	#	default:不用备份,强制或快速检查恢复。
 	#	backup:如果数据文件在恢复时被更改,会将MYD文件的备份保存为tbl_name‐datetime.BAK。
 	# force:即使MYD文件丢失多于一行，任进行恢复
 	#	quick：如果没有任何的delete快就不检查行。
 	
#DATA STORAGE #
datadir = /data/mydata

#BINARY LOGGING 二进制日志模块
log-bin = mysql-bin					#启用二进制日志
binlog_format = mixed				#binlog日志格式,默认采用staterment,建议采用mixed
expire-logs-days = 14				#设置binlog过期清理时间,当某部分二进制日志存在14天后就清理
sync-binlog = 1							#主要是进行日志存储操作,值的设置与mysql性能息息相关
 	# 注:sysnc-bing 值分为0,或者 n，
   # 为0时,当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
  	#	为n时,n可以为1,2,只要是非0,整数即可,意思就是当每进行n次事务提交之后,mysql将进行sync之类操作,将binlo_cache中的数据同步到磁盘。
#max_binlog_size = 100M			#每个binlog日志大小
#binlog_cache_size = 10M  	#binlog日志缓存大小
server-id  = 100						#设置本机数据库ID
performance_schema = 0 
max_connect_errors = 4294967295 	#主要提供访问限制功能(当此值过大的时,可以延迟触发被禁止访问的条件)，当某个ip触发此值时,就会被mysql禁止访问

#CACHES(缓存) AND LIMITS(限制) #看单词就知道是啥意思
# tmp-table-size = 32M   			#设置内存临时表的最大值(每个线程都要分配),如果临时内存表超过此值,就自动把它转为MyIsam表,存储在指定的tmpdir目录下。
# max-heap-table-size = 32M		#指定用户可以创建的内存表大小,这个值用来计算内存表的最大值。
#	注：在优化查询语句的时候,要避免使用临时表,避免不了,就得保证这些临时表是存在内存中的,简单来说,频繁进行查询的时候,得将此值设置大些,不多的话,可以不设置。
# query-cache-type = 0				#主要用来缓存select查询结果，不怎么变化的数据(静态页面及很少发生变化的数据),有3个值：
 	#	0 关闭此功能;
 	#	1 缓存所有结果,除非select语句使用sql_no_cache禁用查询缓存;
 	#	2 只缓存select语句中指定需要缓存的查询
# query-cache-size = 0 				#为0是禁用此功能,,此值的大小,根据实际查询情况来设置,最好是1024的倍数,参考值32M,128M.这个是跟query-cache-type一起使用的。
max-connections = 1024				#针对cache模块设计的最大连接数
thread-cache-size = 64				#设置线程池缓存大小,建议内存在3G以上用64(可根据show global status like 'Threads_%'; 结果进行设置)
open_files_limit = 65535	    #文件打开数量,最好与操作系统支持的文件打开数一致
table-definition-cache = 4096	#定义相关信息缓存,实际存放的是frm跟内存的映射关系
table-open-cache   = 512 			#指定了内存允许打开表的数量,有效的设置此值,可以降低热点表的频繁开关动作,提升性能。
 	#	注：什么是线程池缓存？当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程 ,存储线程的空间就叫线程池。

#INNODB #
innodb_data_file_path = ibdata1:2G:autoextend 	#指定数据文件，初始大小，指定扩展大小 注意与数据库中初始文件大小保持一致
innodb-flush-method = O_DIRECT									#控制着innodb数据文件及redo log的打开,刷写模式，有3个值：
 	#	fdatasync(是默认值)，调用fsync()去刷数据文件与redo log的buffer
 	#	O_DSYNC，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
 	#	O_DIRECT，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log												
innodb-log-files-in-group = 2 									#以循环的方式将日志写到多个文件,默认是2
innodb-log-file-size = 1024M 										#数据日志文件大小
innodb-flush-log-at-trx-commit = 1							#每次提交日志是否记录磁盘 默认值是1 2是日志写入系统缓存 0 不提交也不记录
innodb-file-per-table = 1												#设置独立表空间,innodb 默认是1个表空间
innodb-buffer-pool-size  = 12G									#(缓冲池)用于缓存索引,innodb数据页面，脏页等等，在专用数据库服务器上可以设置为物理内存的70%-80%

#logging
log-error = /data/logs/mysql/mysql-error.log  	#定义错误日志路径
log-queries-not-using-indexes  = 0							#记录没有使用索引查询语句
slow-query-log = 1															#记录慢日志
slow-query-log-file = /data/logs/mysql/mysql-slow.log #慢查询日志记录文件
long_query_time  = 1														#慢日志超时设置,默认是10s，记录超过查询时间的语句 这里的1 是1s

#[mysqldump]	#命令,用于数据库备份
[mysqldump]
quick													#强制mysqldump从服务器一次一行地检索表中的行
max_allowed_packet = 32M			#可接收数据包大小

#[mysql]     	#指的是mysql登录端
[mysql] 
no-auto-rehash								#开启命令自动补齐给你

#[myisamchk] 
[myisamchk]  									#是个命令,用于mysqld在不使用的情况修复表或者崩溃的情况下回复表
key_buffer_size   = 32M 					
sort_buffer_size  = 32M					
read_buffer = 8M
write_buffer = 8M

#[mysqlhotcopy]
[mysqlhotcopy]
interactive-timeout	
expire_logs_days  = 10
 	# mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上
 	#	与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包..

#[mysqld_safe]
[mysqld_safe] #以safe方式启动数据库，相比于mysqld,会在服务启动后继续监控服务状态，死机时重启
open-files-limit = 8192

配置文件示例2

[mysqld]
bind-address = ::ffff:127.0.0.1         ## Change to 0.0.0.0 to allow remote connections
#local-infile = 0                       ## Not needed, Up to you
performance_schema = ON

skip-external-locking
skip-character-set-client-handshake
skip-host-cache
skip-name-resolve
ignore-db-dirs=lost+found

max_allowed_packet = 32M                ## 128M for Recommend or 32M every 1GB of RAM

# Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
#sql_mode                       = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

# Buffer Settings
key_buffer_size = 32M                   ## 1024M for Recommend or 32M every 1GB of RAM
sort_buffer_size = 2M                   ## 1MB for every 1GB of RAM
read_buffer_size = 1M                   ## 1MB for every 1GB of RAM
read_rnd_buffer_size = 1M               ## 1MB for every 1GB of RAM
join_buffer_size = 1M                   ## 1MB for every 1GB of RAM

# Disable Cache
## Option supported by MariaDB & up to MySQL 5.7, comment line on MySQL 8.x
## Can disable if you not have MyISAM tables on your DBs or in server have 1-2 website controlled and able to work on InnoDB
query_cache_type = 1                    
query_cache_size = 32M                  ## Max Value = 96M
query_cache_limit = 4M                  ## 4M for Recommend

# Table Settings
table_definition_cache = 10000          ## 10000 for every 1GB of RAM or -1 (autosizing if supported)
table_open_cache = 10000                ## 10000 for every 1GB of RAM
open_files_limit = 20000                ## This can be 2x to 3x the table_open_cache value

max_heap_table_size = 32M               ## 32MB for every 1GB of RAM
tmp_table_size = 32M                    ## 32MB for every 1GB of RAM

# Innodb Settings
innodb_buffer_pool_instances = 1        ## Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 128M          ## 128M every 1GB of RAM Recommend or (Can use up to 60-70% of RAM)
innodb_log_buffer_size = 4M             ## 4M every 1GB of RAM - Max value = 64M
innodb_log_file_size = 64M              ## 64M every 1GB of RAM - Max value = 1G
innodb_file_per_table = 1
innodb_stats_on_metadata = 0
innodb_read_io_threads = 64             ## Max Value = 64 (Can reduce for 16 every 1GB of RAM)
innodb_write_io_threads = 64            ## Max Value = 64 (Can reduce for 16 every 1GB of RAM)
innodb_thread_concurrency = 1           ## Max Value = 2 or Optimize use by -1, -2 CPUs on server
                                     ## Such as your server has 8 CPUs, try 6 or 7 and check load by MySQL or MariaDB

# Log config
## Disabled for production
#slow_query_log = 1
#long_query_time = 5
#slow-query-log-file = /var/log/mysql-slow.log

# Connection Settings
## Can increase value follow you server resources
max_connections = 100                   ## Max for = 2000
max_user_connections = 80               ## Max for = 1800
interactive_timeout = 180               ## Max for = 300
wait_timeout = 180                      ## Max for = 300

配置文件示例3

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock

back_log = 50
max_connections = 100
wait_timeout = 256
max_connect_errors = 10

table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 512M

read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M

thread_cache_size = 8
thread_concurrency = 8
thread_stack = 240K

query_cache_size = 128M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M

slow_query_log
long_query_time = 2

server-id = 1

# INNODB options
innodb_buffer_pool_size = 4G 1
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8
innodb_read_io_threads = 8

innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25
innodb_log_file_size = 125M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256

innodb_file_per_table

default_time_zone = +00:00

[mysqldump]
quick
max_allowed_packet = 50M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

配置文件示例4

# FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server
# Location: %MYCNF%
# This template is intended to work with MySQL 5.7 and newer and MariaDB 10.0 and newer
# Get most recent updated from here:
# https://www.fromdual.com/mysql-configuration-file-sample

[client]
port                           = %PORT%					# default 3306
socket                         = %SOCKET%				# Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise

[mysql]
no_auto_rehash
max_allowed_packet             = 16M
prompt                         = '\u@\h [\d]> '	# 'user@host [schema]> '
default_character_set          = utf8           # Possibly this setting is correct for most recent Linux systems

[mysqldump]
max_allowed_packet             = 16M

[mysqld_safe]																		# Becomes sooner or later obsolete with systemd
open_files_limit               = 8192           # You possibly have to adapt your O/S settings as well
user                           = mysql
log-error                      = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_error.log   # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld


[mysqld]
# Connection and Thread variables
port                           = %PORT%					# default 3306
socket                         = %SOCKET%       # Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise
basedir                        = %BASEDIR%
datadir                        = %DATADIR%
# tmpdir                         = '%INSTANCEDIR%/tmp'
# innodb_tmpdir                  = '%INSTANCEDIR%/tmp'                 # MySQL 5.7

max_allowed_packet             = 16M
default_storage_engine         = InnoDB
# explicit_defaults_for_timestamp = 1           # MySQL 5.6, test carefully! This can have an impact on application.
# disable_partition_engine_check  = true        # Since MySQL 5.7.17 to 5.7.20. To get rid of nasty message in error log
# character_set_server           = utf8mb4      # For modern applications, default in MySQL 8.0
# collation_server               = utf8mb4_general_ci

max_connections                = 151         		# Values < 1000 are typically good
max_user_connections           = 145            # Limit one specific user/application
thread_cache_size              = 151            # Up to max_connections makes sense

# Query Cache (does not exist in MySQL 8.0 any more!)
# query_cache_type               = 1						# Set to 0 to avoid global QC Mutex
# query_cache_size               = 32M          # Avoid too big (> 128M) QC because of QC clean-up lock!

# Session variables
sort_buffer_size               = 2M							# Could be too big for many small sorts
tmp_table_size                 = 32M            # Make sure your temporary results do NOT contain BLOB/TEXT attributes
read_buffer_size               = 128k						# Resist to change this parameter if you do not know what you are doing
read_rnd_buffer_size           = 256k           # Resist to change this parameter if you do not know what you are doing
join_buffer_size               = 128k           # Resist to change this parameter if you do not know what you are doing

# Other buffers and caches
table_definition_cache         = 1400						# As big as many tables you have
table_open_cache               = 2000           # connections x tables/connection (~2)
table_open_cache_instances     = 16             # New default in 5.7

# MySQL error log
log_error                      = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_error.log   # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld
# log_timestamps                 = SYSTEM				# MySQL 5.7, equivalent to old behaviour
log_warnings                   = 2              # MySQL 5.6, equivalent to log_error_verbosity = 3
# log_error_verbosity            = 3            # MySQL 5.7, equivalent to log_warnings = 2, MariaDB does NOT support this!
innodb_print_all_deadlocks     = 1
# wsrep_log_conflicts            = 1            # for Galera only!

# Slow Query Log
slow_query_log_file            = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_slow.log   # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld
slow_query_log                 = 0
log_queries_not_using_indexes  = 0							# Interesting on developer systems!
long_query_time                = 0.5
min_examined_row_limit         = 100

# General Query Log
general_log_file               = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_general.log   # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld
general_log                    = 0

# Performance Schema
# performance_schema             = ON						# for MariaDB 10 releases
performance_schema_consumer_events_statements_history_long = ON      # MySQL 5.6/MariaDB 10 and newer

# Binary logging and Replication
server_id                      = %SERVERID%			# Must be set on MySQL 5.7 and newer if binary log is enabled!
log_bin                        = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog            # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld
# master_verify_checksum         = ON           # MySQL 5.6
binlog_cache_size              = 1M
binlog_stmt_cache_size         = 1M
max_binlog_size                = 128M           # Make bigger for high traffic to reduce number of files
sync_binlog                    = 1              # Set to 0 or higher to increase write performance
expire_logs_days               = 5              # We will survive easter holidays
binlog_format                  = ROW            # Use MIXED if you want to experience some troubles
# binlog_row_image               = MINIMAL      # Since 5.6
# auto_increment_increment       = 2            # For Master/Master set-ups use 2 for both nodes
# auto_increment_offset          = 1            # For Master/Master set-ups use 1 and 2

# Slave variables
log_slave_updates              = 1							# Use if Slave is used for Backup and PiTR
read_only                      = 0              # Set to 1 to prevent writes on Slave
# super_read_only                = 0            # Set to 1 to prevent writes on Slave for users with SUPER privilege. Since 5.7, not in MariaDB
# skip_slave_start               = 1            # To avoid start of Slave thread
# relay_log                      = %UNAME%_%INSTANCE%_relay-bin
# relay_log_info_repository      = table        # MySQL 5.6
# master_info_repository         = table        # MySQL 5.6
# slave_load_tmpdir              = '%INSTANCEDIR%/tmp'

# Crash-safe replication Master
# binlog_checksum                = CRC32				# default
# sync_binlog                    = 1            # default since 5.7.6, but slow!
# innodb_support_xa              = 1            # default, depracted since 5.7.10

# Crash-safe replication Slave
# master_info_repository         = TABLE
# relay_log_info_repository      = TABLE
# relay_log_recovery             = 1
# sync_relay_log_info            = 1
# relay_log_purge                = 1						# default
# slave_sql_verify_checksum      = 1            # default

# GTID replication
# gtid_mode                        = ON         # Master and Slave
# enforce_gtid_consistency         = 1          # Master and Slave

# log_bin                          = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog   # In 5.6 also on Slave
# log_slave_updates                = 1          # In 5.6 also on Slave

# Security variables
# local_infile                   = 0						# If you are security aware
# secure_auth                    = 1            # If you are security aware
# sql_mode                       = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER   # Be careful changing this afterwards
# skip_name_resolve              = 0            # Set to 1 if you do not trust your DNS or experience problems
# secure_file_priv               = '%INSTANCEDIR%/tmp'                   # chmod 750, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld

# MyISAM variables
key_buffer_size                = 8M							# Set to 25 - 33 % of RAM if you still use MyISAM
myisam_recover_options         = 'BACKUP,FORCE'
# disabled_storage_engines       = 'MyISAM,MEMORY'                     # MySQL 5.7, do NOT during/before mysql_upgrade, good for Galera!

# MEMORY variables
max_heap_table_size            = 64M                                 # Should be greater or equal to tmp_table_size

# InnoDB variables
innodb_strict_mode             = ON
# innodb_file_format_check       = 1						# Desupported in MySQL 8.0
# innodb_file_format             = Barracuda    # For dynamic and compressed InnoDB tables, default in 5.7
innodb_buffer_pool_size        = 128M           # Go up to 80% of your available RAM
innodb_buffer_pool_instances   = 8              # Bigger if huge InnoDB Buffer Pool or high concurrency

innodb_file_per_table          = 1              # Is the recommended way nowadays
# innodb_flush_method            = O_DIRECT     # O_DIRECT is sometimes better for direct attached storage
# innodb_write_io_threads        = 8            # If you have a strong I/O system or SSD
# innodb_read_io_threads         = 8            # If you have a strong I/O system or SSD
# innodb_io_capacity             = 1000         # If you have a strong I/O system or SSD

innodb_flush_log_at_trx_commit = 2              # 1 for durability, 0 or 2 for performance
innodb_log_buffer_size         = 8M             # Bigger if innodb_flush_log_at_trx_commit = 0
innodb_log_file_size           = 256M           # Bigger means more write throughput but longer recovery time

                                             # Since MariaDB 10.0 and MySQL 5.6
innodb_monitor_enable = all                     # Overhead < 1% according to PeterZ/Percona

# Galera specific MySQL parameter
# default_storage_engine         = InnoDB				# Galera only works with InnoDB
# innodb_flush_log_at_trx_commit = 2            # Durability is achieved by committing to the Group
# innodb_autoinc_lock_mode       = 2            # For parallel applying
# binlog_format                  = row          # Galera only works with RBR
# query_cache_type               = 0            # Use QC with Galera only in a Master/Slave set-up
# query_cache_size               = 0


# WSREP parameter
# wsrep_on                       = on                                  # Only MariaDB >= 10.1
# wsrep_provider                 = /usr/lib/galera/libgalera_smm.so    # Location of Galera Plugin on Ubuntu ?
# wsrep_provider                 = /usr/lib64/galera-3/libgalera_smm.so   # Location of Galera Plugin on CentOS 7
# wsrep_provider                 = none                                # Start mysqld without Galera
# wsrep_provider_options         = 'gcache.size = 1G'                  # Depends on you workload, WS kept for IST

# wsrep_cluster_name             = "My cool Galera Cluster"            # Same Cluster name for all nodes
# wsrep_cluster_address          = "gcomm://192.168.0.2,192.168.0.3"   # Start other nodes like this

# wsrep_node_name                = "Node A"                            # Unique node name
# wsrep_node_address             = 192.168.0.1                         # Our address where replication is done
# wsrep_node_incoming_address    = 10.0.0.1                            # Our external interface where application comes from
# wsrep_sync_wait                = 1                                   # If you need realy full-synchronous replication (Galera 3.6 and newer)
# wsrep_slave_threads            = 16                                  # 4 - 8 per core, not more than wsrep_cert_deps_distance

# wsrep_sst_method               = rsync                               # SST method (initial full sync): mysqldump, rsync, rsync_wan, xtrabackup-v2
# wsrep_sst_auth                 = sst:secret                          # Username/password for sst user
# wsrep_sst_receive_address      = 192.168.2.1                         # Our address where to receive SST


# Group Replication parameter
# default_storage_engine         = InnoDB                              # Group Replication only works with InnoDB
# server_id                      = %SERVERID%                          # Should be different on all 3 nodes
# log_bin                        = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog   # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld
# binlog_format                  = ROW
# binlog_checksum                = NONE                                # not default!
# gtid_mode                      = ON
# enforce_gtid_consistency       = ON
# master_info_repository         = TABLE
# relay_log_info_repository      = TABLE
# log_slave_updates              = ON

# slave_parallel_workers         = <n>                                 # 1-2/core, max. 10
# slave_preserve_commit_order    = ON
# slave_parallel_type            = LOGICAL_CLOCK

# transaction_write_set_extraction            = XXHASH64

# loose-group_replication_group_name          = "$(uuidgen)"           # Must be the same on all nodes
# loose-group_replication_start_on_boot       = OFF
# loose-group_replication_local_address       = "192.168.0.1"
# loose-group_replication_group_seeds         = "192.168.0.1,192.168.0.2,192.168.0.3"   # All nodes of Cluster
# loose-group_replication_bootstrap_group     = OFF
# loose-group_replication_single_primary_mode = FALSE                  # = multi-primary

参考文章

MariaDB/MySQL 配置文件 my.cnf 解读

MariaDB/MySQL 的默认设置性能非常差，仅仅起一个功能测试的作用，不能用在生产环境中，因此要对一些参数进行调整优化。当然，对配置文件各参数的调整需要根据实际环境，不同时期不同数量级的数据进行性能优化。

MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重启 MySQL 服务，以使更改生效。本文的参数配置基于 MariaDB 10.2，硬件内存 4G。文中一些选项值的设置只是推荐值，不要盲目的接受这些建议。每个 MySQL 设置都是不同的，在进行任何更改之前需要慎重考虑。

[client]
port		= 3306 
socket		= /tmp/mysql.sock  

[mysqld]  
port		= 3306 
socket		= /tmp/mysql.sock 
user    = mariadb 
basedir = /usr/local/mariadb 
datadir = /data/mysql 
log_error = /data/mysql/mariadb.err 
pid-file = /data/mysql/mariadb.pid  
skip-external-locking  
...

my.cnf 配置文件以方括号如[client]区分模块作用域，其中[client]表示客户端配置，[mysqld]是服务端配置。以上几个选项是最基础的，每个选项有简单说明，其中有关路径的配置是基于本站文章：CentOS7 下源码编译安装 MariaDB 10.2 和 Linux 下使用二进制格式安装 MariaDB 的安装路径来配置的，所以在配置前可以先按照两文先安装好 MairaDB。

选项配置及说明

• key_buffer_size = 32M
  • 这个参数用来缓存 MyISAM 存储引擎的索引参数。MySQL5.5 默认为 InnoDB 存储引擎，所以这个参数可以设置小点，64MB 即可。
• max_allowed_packet = 1M
  • 允许最大接收数据包的大小，防止服务器发送过大的数据包。可以设置为 16MB 或者更大，但设置的太大也可能有危险。
• table_open_cache = 128
  • #MySQL 每打开一个表，都会读入一些数据到 table_open_cache 缓存中，当 MySQL 在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值 64，假定系统有 200 个并发连接，则需将此参数设置为 200*N(N 为每个连接所需的文件描述符数目)；当把 table_open_cache 设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上。
• sort_buffer_size = 768K
  • 在表进行 order by 和 group by 排序操作时，由于排序的字段没有索引，会出现 Using filesort，为了提高性能，可用此参数增加每个线程分配的缓冲区大小。默认为 256KB，这个参数不要设置过大，一般在 128～256KB 即可。另外，一般出现 Using filesort 的时候，要通过增加索引来解决。
• net_buffer_length = 8K
  • 包消息缓冲区初始化为 net_buffer_length 字节，但需要时可以增长到 max_allowed_packet 字节。
• read_buffer_size = 768K
  • 该参数用于表的顺序扫描，表示每个线程分配的缓冲区大小。比如在进行全表扫描时，MySQL 会按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在 read_buffer_size 中，当 buffer 空间被写满或者全部数据读取结束后，再将 buffer 中的数据返回给上层调用者，以提高效率。默认为 128K，这个参数不要设置过大，一般在 128～256 之间。
• read_rnd_buffer_size = 512K
  • 该参数用于表的随机读取，表示每个线程分配的缓冲区大小。比如，按照一个非索引字段做 order by 排序操作时，就会利用这个缓冲区来暂存读取的数据。默认为 256KB，这个参数不要设置过大，一般在 128～512KB。
• myisam_sort_buffer_size = 8M
  • 当对 MyISAM 表执行 repair table 或创建索引时，用以缓存排序索引；设置太小时可能会遇到” myisam_sort_buffer_size is too small”
• thread_cache_size = 16
  • 线程池，线程缓存。用来缓存空闲的线程，以至于不被销毁，如果线程缓存在的空闲线程，需要重新建立新连接，则会优先调用线程池中的缓存，很快就能响应连接请求。每建立一个连接，都需要一个线程与之匹配。
• query_cache_size = 16M
  • 缓存 select 语句和结果集大小的参数。查询缓存会存储一个 select 查询的文本与被传送到客户端的相应结果。如果之后接收到一个相同的查询，服务器会从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。如果你的环境中写操作很少，读操作频繁，那么打开 query_cache_type=1，会对性能有明显提升。如果写操作频繁，则应该关闭它（query_cache_type=0）。
• tmp_table_size = 32M
  • 临时 HEAP 数据表的最大长度 (默认设置是 32M); 超过这个长度的临时数据表将被转换为 MyISAM 数据表并存入一个临时文件。
• explicit_defaults_for_timestamp = true
  • 是否显示默认时间戳。
• skip-name-resolve
  • 禁用 DNS 解析，使用 IP 访问的情况下加快访速度。
• max_connections = 500
  • 该参数用来设置最大连接数，告诉你当前你的服务器允许多少并发连接。默认为 100，一般设置为 512-1000 即可。请记住，太多的连接会导致内存的使用量过高并且会锁住你的 MySQL 服务器。一般小网站需要 100-200 的连接数，而较大可能需要 500-800 甚至更多。这里的值很大程度上取决于你 MySQL/MariaDB 的使用情况。
• max_connect_errors = 100
  • 如果有时网络抽风，或者应用配置错误，或者其他原因导致客户端短时间内不断的尝试连接，客户端可能会被列入黑名单，然后将无法连接，直到再次刷新主机缓存。这个选项默认值太小了，可以考虑设的足够大（如果你的服务器配置够强大的话）。
• open_files_limit = 65535
  • mysql 打开最大文件数
• log-bin=mysql-bin
  • 这些路径相对于 datadir
• binlog_format=mixed
  • 日志格式
• server-id = 1
  • 给服务器分配一个独一无二的 ID 编号; n 的取值范围是 1~2 的 32 次方启用二进制日志功能。在复制数据同步的时候会用到，Helloweba 后面会有文章介绍。
• expire_logs_days = 10
  • 启用二进制日志后，保留日志的天数。服务器会自动清理指定天数前的日志文件，如果不设置则会导致服务器空间耗尽。一般设置为 7～14 天。
• default_storage_engine = InnoDB
  • 新数据表的默认存储引擎 (默认设置是 MyISAM)。这项设置还可以通过–default-table-type 选项来设置。
• innodb_file_per_table = 1
  • InnoDB 提供了更灵活的方式，它把每个数据库的信息保存在一个 .ibd 数据文件中。每个 .idb 文件代表它自己的表空间。通过这样的方式可以更快地完成类似 “TRUNCATE” 的数据库操作，当删除或截断一个数据库表时，你也可以回收未使用的空间。这样配置的另一个好处是你可以将某些数据库表放在一个单独的存储设备。这可以大大提升你磁盘的 I/O 负载。
• innodb_data_home_dir = /data/mysql
  • InnoDB 主目录，所有与 InnoDB 数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是 MySQL 的数据目录。
• innodb_data_file_path = ibdata1:10M:autoextend
  • 用来容纳 InnoDB 为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节 (B)、兆字节(MB) 或千兆字节 (GB) 为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个 autoextend 属性和一个最大长度(max:n)。
• innodb_log_group_home_dir = /data/mysql
  • 用来存放 InnoDB 日志文件的目录路径 (如 ib_logfile0、ib_logfile1 等)。在默认的情况下，InnoDB 驱动程序将使用 MySQL 数据目录作为自己保存日志文件的位置。
• innodb_buffer_pool_size = 128M
  • 这个参数是 InnoDB 存储引擎的核心参数，默认为 128KB，这个参数要设置为物理内存的 60%～70%。
• innodb_log_file_size = 32M
  • 事务日志文件写操作缓存区的最大长度 (默认设置是 1MB)。
• innodb_log_buffer_size = 8M
  • 事务日志所使用的缓存区。InnoDB 在写事务日志的时候，为了提高性能，先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）时，再将日志写到文件（或者同步到磁盘）中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。默认是 8MB，一般为 16～64MB 即可。
• innodb_flush_log_at_trx_commit = 1
  • 这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写 (术语称为” 同步”)到硬盘上。设置值 0 的意思是每隔一秒写一次日 志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值 1(设置设置)的意思是在每执行完一条 COMMIT 命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值 2 是一般折衷的办法，即每执行完一条 COMMIT 命令写一次日志，每隔一秒进行一次同步。
• innodb_lock_wait_timeout = 50
  • 如果某个事务在等待 n 秒 (s) 后还没有获得所需要的资源，就使用 ROLLBACK 命令放弃这个事务。这项设置对于发现和处理未能被 InnoDB 数据表驱动 程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是 50s。

示例：内存 4G 的 my.cnf 配置

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user    = mariadb
basedir = /usr/local/mariadb
datadir = /data/mysql
log_error = /data/mysql/mariadb.err
pid-file = /data/mysql/mariadb.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 32M
tmp_table_size = 64M

explicit_defaults_for_timestamp = true

max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

chatGPT问答

配置一台MySQL 2核8GB内存的服务器,需要注意哪些配置参数来确保发挥最优性能

非常好的问题。在配置MySQL服务器时，确保优化性能需要注意以下配置参数：

1. innodb_buffer_pool_size：这是MySQL的内存缓存池大小，它用于缓存表和索引数据。建议将其设置为服务器内存总量的50%-75%。

2. innodb_log_file_size：这是MySQL的事务日志文件的大小。建议将其设置为innodb_buffer_pool_size的25%。

3. innodb_flush_log_at_trx_commit：这是MySQL的事务提交行为。将其设置为1将确保在每次事务提交时都将事务日志写入磁盘，从而确保数据的持久性和一致性。但是，这可能会影响性能。建议将其设置为2以在每秒钟写入一次事务日志。

4. query_cache_size：这是MySQL的查询缓存大小，它用于缓存查询结果。建议将其设置为0，因为它会降低性能。

5. max_connections：这是MySQL的最大连接数，它决定了服务器可以同时处理多少个客户端连接。建议将其设置为不超过100。

6. thread_cache_size：这是MySQL的线程缓存大小，它用于缓存线程。建议将其设置为不超过50。

7. key_buffer_size：这是MySQL的MyISAM索引缓存大小。建议将其设置为0，因为InnoDB已成为MySQL的默认存储引擎。

8. tmp_table_size：这是MySQL的临时表大小。建议将其设置为不超过1GB。

这些是您需要注意的最重要的MySQL配置参数，但请记住，实际的配置可能因为服务器硬件，负载类型和其他因素而有所不同。最好的方法是根据您的环境进行测试和优化配置。

我如何检查当前innodb_buffer_pool_size的配置大小和实际使用大小呢

要检查当前的innodb_buffer_pool_size配置大小和实际使用大小，可以使用以下步骤：

1. 打开MySQL客户端并登录到MySQL服务器。

2. 运行以下命令来查看当前innodb_buffer_pool_size的配置大小：

   sqlCopy codeSHOW VARIABLES LIKE 'innodb_buffer_pool_size';
   

   这将显示当前配置大小（以字节为单位）。

3. 运行以下命令来查看当前innodb_buffer_pool_size的实际使用大小：

   sqlCopy codeSHOW ENGINE INNODB STATUS\G
   

   这将显示MySQL的InnoDB引擎状态，其中包括Buffer pool size，Free buffers和Database pages等信息。Buffer pool size指的是当前InnoDB缓存池的大小，而Database pages指的是已经加载到缓存池中的数据库页数，可以通过这些指标来估算实际使用大小。

请注意，如果缓存池太小，可能会导致大量的磁盘I/O操作，从而降低系统性能。如果缓存池过大，也可能会浪费系统内存资源。因此，根据实际需要进行适当的配置是非常重要的。

运行"SHOW ENGINE INNODB STATUS\G"这个命令出错了

如果在MySQL客户端中运行SHOW ENGINE INNODB STATUS\G命令出现错误，可能是由于以下原因之一：

1. 没有足够的权限：如果您没有足够的权限来运行SHOW ENGINE INNODB STATUS命令，MySQL服务器会返回错误。请使用具有适当权限的用户登录。

2. InnoDB引擎未启用：如果MySQL服务器未启用InnoDB存储引擎，则无法运行SHOW ENGINE INNODB STATUS命令。您可以通过运行以下命令来检查是否启用了InnoDB引擎：

   sqlCopy codeSHOW ENGINES;
   

   如果没有InnoDB引擎，则需要启用它。

3. MySQL版本不兼容：SHOW ENGINE INNODB STATUS命令需要MySQL版本5.5或更高版本。如果您的MySQL版本低于5.5，则无法运行该命令。

如果您已排除以上问题，请尝试运行SHOW ENGINE INNODB STATUS命令（注意去掉"\G"）。这将返回一大段文本，其中包含InnoDB存储引擎的详细信息。您可以查找Buffer pool size，Free buffers和Database pages等指标，以估算当前的innodb_buffer_pool_size的实际使用大小。

binlog_cache_size这个参数有什么作用呢？

binlog_cache_size参数用于控制MySQL服务器写入二进制日志时使用的缓存大小。MySQL服务器在执行写入二进制日志时，会先将日志数据缓存在内存中，等到缓存区满了或者达到一定的时间间隔后，再将缓存区中的数据写入磁盘。binlog_cache_size参数就是用来控制这个缓存区的大小。

默认情况下，binlog_cache_size参数的值是32768，即32KB。这个值对于大多数情况来说是足够的，但如果您的服务器负载较高或者需要写入大量的二进制日志数据，您可以考虑增加这个值以提高性能。但是，将这个值设置得过大也会消耗大量的内存资源，因此需要根据实际情况来适当调整。

需要注意的是，如果binlog_cache_size参数的值太小，可能会导致频繁的写入磁盘操作，从而降低系统性能。如果binlog_cache_size参数的值太大，可能会导致内存占用过高，从而导致系统出现内存相关的问题。因此，需要根据实际情况进行适当的配置。

binlog_rows_query_log_events这个参数有什么用呢？

binlog_rows_query_log_events参数是一个用于MySQL复制和二进制日志功能的布尔类型参数，它控制是否记录执行DML语句的行数信息。当该参数设置为1时，MySQL服务器会在记录二进制日志时，同时将执行DML语句所影响的行数记录到二进制日志中。

这个参数的作用是帮助管理人员或开发人员了解数据库的变更情况，特别是在进行数据复制或恢复时。通过记录DML语句的行数信息，可以方便地了解哪些表、哪些行发生了变化，以及这些变化对数据库性能的影响。

需要注意的是，启用binlog_rows_query_log_events参数会增加二进制日志的大小，并且可能会稍微降低系统性能，因为在记录二进制日志时需要额外的操作。因此，需要根据实际情况进行适当的配置。如果您对数据库变更的详细信息并不是特别关心，可以考虑关闭这个参数来减少二进制日志的大小。

eq_range_index_dive_limit这个参数有什么用呢？

eq_range_index_dive_limit参数用于控制MySQL服务器执行eq_range索引扫描时的优化策略。eq_range索引是指在查询中使用等于操作符（=）的情况下，MySQL服务器会使用的索引类型之一。

当MySQL服务器执行eq_range索引扫描时，如果查询的数据量比较大，MySQL可能会对索引进行分段扫描，这个过程称为“索引潜水（index dive）”。eq_range_index_dive_limit参数就是用于控制这个索引潜水的深度限制。默认情况下，这个参数的值是200，表示最多可以执行200次索引潜水。

需要注意的是，如果eq_range_index_dive_limit参数设置得过小，可能会导致查询的结果不完整或者性能受到影响。如果设置得过大，则可能会导致内存占用过高或者查询时间过长。因此，需要根据实际情况进行适当的配置。通常情况下，这个参数的默认值是足够的，只有在特殊情况下才需要进行调整。

innodb_flush_neighbors这个参数有什么作用呢

innodb_flush_neighbors参数是一个用于控制InnoDB存储引擎刷写（flush）数据的布尔类型参数。在默认情况下，当InnoDB存储引擎需要刷写一个页时，它会检查是否有相邻的页需要被刷写。如果有，则会一并将这些页刷写到磁盘上，以提高性能。

具体来说，当innodb_flush_neighbors参数设置为ON时，InnoDB存储引擎会尝试将相邻的页一并刷写到磁盘上，从而减少磁盘I/O操作的次数，提高性能。当innodb_flush_neighbors参数设置为OFF时，InnoDB存储引擎只会刷写当前页，不会检查相邻的页。

需要注意的是，启用innodb_flush_neighbors参数可能会导致一些磁盘阵列（如RAID）的性能下降，因为这些阵列可能会对连续的写操作进行重新排序，导致写操作的效率降低。因此，在启用这个参数之前，需要了解系统的硬件配置和工作原理，以确定是否适合启用这个参数。

总之，innodb_flush_neighbors参数的作用是优化InnoDB存储引擎的数据刷写操作，从而提高系统的性能。

table_open_cache这个参数有什么作用

table_open_cache参数用于控制MySQL服务器中打开表的数量。在MySQL服务器启动时，会预先为每个表分配一个表缓存，以提高查询性能。这个表缓存包含表的元数据信息，如表结构、表名、索引信息等。

table_open_cache参数用于控制这个表缓存的大小，也就是MySQL服务器中同时可以打开的表的数量。如果这个参数设置得太小，可能会导致MySQL服务器无法打开足够的表，从而影响查询性能。如果设置得太大，可能会导致MySQL服务器占用过多的内存，影响系统的稳定性。

通常情况下，需要根据系统的实际情况来配置table_open_cache参数。如果MySQL服务器中经常需要打开大量的表，可以适当增加这个参数的值，以提高查询性能。如果MySQL服务器中的表数量比较少，可以适当降低这个参数的值，以节省内存资源。

需要注意的是，table_open_cache参数并不是越大越好，这个参数的最优值取决于系统的实际情况。通常情况下，这个参数的默认值是足够的，只有在特殊情况下才需要进行调整。

innodb_buffer_pool_instances这个参数有什么作用

innodb_buffer_pool_instances参数用于控制InnoDB缓冲池的分区数量。InnoDB缓冲池是InnoDB存储引擎用于缓存表数据和索引数据的内存池。将缓冲池划分为多个分区可以提高并发访问的效率，避免争用缓存池的单个锁。

具体来说，当innodb_buffer_pool_instances参数设置为N时，InnoDB缓冲池将被划分为N个等大小的分区。每个分区都有自己的锁和管理结构，以确保并发访问时的效率和稳定性。在多核系统上，将缓冲池划分为多个分区可以利用多个CPU核心并行处理缓存操作，提高性能。

需要注意的是，innodb_buffer_pool_instances参数的值应该与系统的CPU核心数量相匹配，以充分利用多核CPU的性能。通常情况下，建议将这个参数的值设置为CPU核心数量的2倍或4倍。

总之，innodb_buffer_pool_instances参数的作用是提高InnoDB缓冲池的并发访问性能和稳定性，从而提高系统的性能。

performance_schema这个参数有什么用

performance_schema是MySQL 5.5及以后版本中引入的一个新特性，用于提供MySQL服务器的性能分析和诊断信息。它提供了一系列的表和视图，包含了MySQL服务器内部的各种性能指标和状态信息，如CPU和IO负载、锁和等待事件、查询执行统计等等。

performance_schema参数用于控制是否启用performance_schema特性，以及占用多少系统资源来收集和存储性能分析信息。默认情况下，performance_schema是启用的，并且会占用一定的系统资源。如果需要禁用这个特性，可以将这个参数设置为0。

performance_schema特性的优点是可以提供非常详细和全面的MySQL性能分析信息，帮助开发人员和DBA快速定位和解决性能问题。通过分析这些信息，可以找到慢查询、锁竞争、磁盘IO瓶颈等问题的根源，从而进行相应的优化和调整。

需要注意的是，由于performance_schema需要占用一定的系统资源来收集和存储性能信息，因此在一些较低配置的服务器上，可能会影响系统的稳定性和性能。如果服务器的性能和稳定性比较关键，可以适当降低performance_schema的采样频率或者禁用这个特性。

innodb_adaptive_hash_index参数有什么用

innodb_adaptive_hash_index是InnoDB存储引擎的一个参数，用于控制是否启用自适应哈希索引(Adaptive Hash Index)特性。

自适应哈希索引是InnoDB存储引擎的一个特性，它用于加速对InnoDB表的单行查询。它的工作方式是，InnoDB会根据表的使用模式和数据分布情况，动态调整哈希索引的大小和位置，以尽可能减少磁盘IO操作和锁竞争。

启用innodb_adaptive_hash_index参数后，InnoDB存储引擎会自动调整哈希索引的大小和位置，以适应表的使用模式和数据分布情况。在大多数情况下，这个特性可以提高单行查询的性能和效率，从而提高整个系统的性能和吞吐量。

需要注意的是，启用innodb_adaptive_hash_index参数会占用一定的系统资源，尤其是内存资源。如果系统的内存资源比较紧张，可以适当降低innodb_adaptive_hash_index参数的值，或者禁用这个特性。另外，如果表的数据分布比较均匀，或者使用模式比较稳定，那么启用自适应哈希索引的优势可能会比较有限。

总之，innodb_adaptive_hash_index参数的作用是启用或禁用InnoDB存储引擎的自适应哈希索引特性。启用这个特性可以提高单行查询的性能和效率，但需要注意占用系统资源的问题。

Innodb_buffer_pool_pages_data代表什么数据？

Innodb_buffer_pool_pages_data是MySQL InnoDB存储引擎的一个状态变量，用于表示当前InnoDB缓冲池中已被使用的页的数量。其中，每个页的大小默认为16KB。

InnoDB缓冲池是一个内存区域，用于缓存InnoDB表的数据和索引。当用户查询InnoDB表时，InnoDB存储引擎首先会检查缓冲池中是否已经缓存了相关的数据和索引。如果数据和索引已经在缓冲池中，则可以直接从内存中读取数据，避免了磁盘IO操作，从而提高了查询性能和效率。

Innodb_buffer_pool_pages_data表示的是已经被缓存的页的数量，也就是已经在缓冲池中的数据和索引占用的页数。可以通过查询该状态变量来了解当前缓冲池的使用情况，从而进一步调整相关的配置参数，以优化系统的性能和吞吐量。

需要注意的是，Innodb_buffer_pool_pages_data并不等同于缓冲池的实际使用大小。因为InnoDB存储引擎在缓存数据和索引时，可能会使用一些辅助数据结构，例如哈希表、B+树等，这些数据结构也会占用缓冲池中的空间。因此，缓冲池的实际使用大小可能会比Innodb_buffer_pool_pages_data表示的数量要大一些。

Innodb_buffer_pool_extension_pages代表什么数据？

Innodb_buffer_pool_extension_pages是MySQL InnoDB存储引擎的一个状态变量，用于表示当前InnoDB缓冲池扩展区域中已被使用的页的数量。其中，每个页的大小默认为16KB。

InnoDB缓冲池扩展区域是在缓冲池满载时用于额外存储数据和索引的区域，可以通过配置innodb_buffer_pool_size和innodb_buffer_pool_instances参数来设置其大小。当缓冲池中的页被使用完毕时，InnoDB存储引擎就会将一部分数据和索引移动到扩展区域中，以保证系统的正常运行。

Innodb_buffer_pool_extension_pages表示的是已经被缓存的扩展页的数量，也就是已经在缓冲池扩展区域中的数据和索引占用的页数。可以通过查询该状态变量来了解当前扩展区域的使用情况，从而进一步调整相关的配置参数，以优化系统的性能和吞吐量。

需要注意的是，Innodb_buffer_pool_extension_pages表示的是缓冲池扩展区域中已被使用的页的数量，而不是扩展区域的实际使用大小。因为InnoDB存储引擎在缓存数据和索引时，可能会使用一些辅助数据结构，例如哈希表、B+树等，这些数据结构也会占用缓冲池扩展区域中的空间。因此，扩展区域的实际使用大小可能会比Innodb_buffer_pool_extension_pages表示的数量要大一些。