MySql数据库优化常见设置 mysql数据库优化方案

MySql数据库优化是非常重要的一块，SQL语句方面的优化可以参见我以前的文章，我专门写过，这篇文章作为补充，主要是针对MySql本身参数的优化及注意事项。

• 单个文件的大小直接影响数据的写入及读取，我们可以给每个数据表设置一个独立的文件存储

innodb_file_per_table=ON

• 开启数据库本地的查询缓存机制，我们可以设置缓存数据的大小，数据库会根据查询参数自动变更缓存的内容。查看是否开启缓存命令

show VARIABLES like '%query_cache%';

如果query_cache_type为OFF，说明缓存没有开启，哪我们需要设置如下

query_cache_type=1
query_cache_limit=2M
query_cache_size=256M

query_cache_type 设置为1表示开启缓存

query_cache_limit 每次查询能使用的缓存大小

query_cache_size 缓存大小

• 开启MySql慢日志记录，这个建议针对开发、测试、模拟环境设置，生产环境建议关闭以便提高性能

查看慢查询是否开启

show VARIABLES like '%slow%';

slow_query_log 如果为ON则为慢日志开启

slow_query_log_file 日志文件路径开

启慢日志

slow_query_log=on
#判断标准超过3秒记录
long_query_time=3
slow_query_log_file=/cmp/mysql-wsrep/logs/slow.log

long_query_time 慢查询阈值，单位秒，只要查询超过这个时间就会记录到日志

• 数据库事务隔离级别决定锁的范围大小，直接影响到数据库的性能，下面是常见数据库隔离级别

隔离级别从上到下依次提高，但性能依次降低，所以，我们要根据具体的业务选择合理的数据库隔离级别，达到既满足业务又提高性能的效果。

常见的数据库比如：oracle、sqlserver 默认使用 RC，Mysql默认RR级别

没有特殊要求，我们可以把Mysql的隔离级别设置为RC

transaction-isolation=READ-COMMITTED

• 设置合适的数据表引擎，我们在开发中常用的数据库引擎为innodb，很少使用myisam，其实在不需要事务的需求下，我们可以把数据库引擎或者数据表引擎设置为myisam

ALTER TABLE access_log ENGINE = MyISAM;
OPTIMIZE TABLE access_log;

OPTIMIZE TABLE 命令可以实现空间。碎片清理

• 其它常用设置如下，基本上都加了注释，可以根据需要合理设置

[client]
port                  = 3306 					# 设置mysql客户端连接服务端时默认使用的端口
socket                = /mysql/mysql.sock
default-character-set = utf8					# 默认字符编码为utf8

[mysqld]
open_files_limit = 65535
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
port     = 3306 							# mysql服务端默认监听的TCP/IP端口
socket   = /cmp/mysql/mysql.sock
pid-file = /cmp/mysql/mysql.pid
basedir  = /cmp/mysql 						# 基准路径，其他路径都相对于这个路径
datadir  = /cmp/data/mysql/data 			# mysql数据库文件所在目录
tmpdir   = /cmp/data/mysql/data 			# mysql数据库临时文件目录

# explicit_defaults_for_timestamp = true

innodb_flush_method  = O_DIRECT  			# 直接写入磁盘，禁止系统Cache
innodb_io_capacity   = 800 					# 将其设置为磁盘子系统可以处理的写入iops数。
character-set-server = utf8 				# 服务端默认使用的字符集
key_buffer_size      = 1000M 				# 键高速缓存
max_allowed_packet   = 128M 				# 更新插入server接受的数据包大小
table_open_cache     = 2000 				# 表高速缓存的大小
sort_buffer_size     = 2M
read_buffer_size          = 2M				# MySql读入缓冲区大小。
read_rnd_buffer_size      = 8M				# MySql的随机读（查询操作）缓冲区大小。
myisam_sort_buffer_size   = 64M 			# MyISAM表发生变化时重新排序所需的缓冲
thread_cache_size         = 2000			# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量。
query_cache_type          = 0				# 只要query_cache_type没有关闭，sql查询总是会使用查询缓存，如果缓存没有命中则开始查询的执行计划到表中查询数据
max_heap_table_size       = 128M			# 这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。
bulk_insert_buffer_size   = 64M 			# 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
myisam_sort_buffer_size   = 128M 			# MyISAM表发生变化时重新排序所需的缓冲
myisam_max_sort_file_size = 20G 			# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
myisam_repair_threads     = 1 				# 如果一个表拥有超过一个索引
myisam_recover-options

# connect
max_connections    = 2000 					# MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值
max_connect_errors = 999999 				# 设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息
slow_launch_time   = 1 						# 捕获所有执行时间超过1秒的查询
skip-name-resolve 							# 禁用dns解析,加快mysql登录过程,但是,这样不能在mysql的授权表中使用主机名了,只能使用IP

# log
log-error               = /cmp/data/mysql/log/error.log
general_log_file        = /cmp/data/mysql/log/general_log.log 		# 有的查询语句都可以在general log文件中以可读的方式得到
binlog_format           = row
log_bin                 = /cmp/data/mysql/binlog/mysql-bin.log
relay_log               = /cmp/data/mysql/binlog/mysql-relay-bin.log
max_binlog_size         = 100M 										# 每份binlog日志大小,如果日志达到达到这个大小时,mysql会创建一份新的binlog日志
log_slave_updates 													# 主从复制重要参数,防止数据不同步
expire_logs_days        = 3 										# 二进制日志自动删除/过期的天数.默认值为0,表示“没有自动删除”
slow_query_log_file		= /cmp/data/mysql/log/slow.log 				# 慢查询日志
slow_query_log          = on
long_query_time         = 1
server-id                   = 1
replicate_ignore_db         = mysql 			# 主从同步的环境中，replicate-ignore-db用来设置不需要同步的库
replicate_wild_ignore_table = mysql.% 			# 主从同步中,过滤的规则
sync_binlog                 = 1
binlog_checksum             = none
binlog_format               = mixed
# innodb
innodb_data_home_dir           = /cmp/data/mysql/data 		# 这是InnoDB表的目录共用设置。如果没有在 my.cnf 进行设置，InnoDB 将使用MySQL的 datadir 目录为缺省目录。如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径。
innodb_data_file_path          = ibdata1:100M:autoextend 	# 指定 InnoDB 只建立一个最初大小为 100 MB 并且当表空间被用尽时以 8MB 每块增加的数据文件
innodb_log_group_home_dir      = /cmp/data/mysql 			# InnoDB 日志文件的路径。必须与 innodb_log_arch_dir 设置相同值。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的 ib_logfile... 文件
innodb_buffer_pool_size        = 2G 						# InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。在一个专用的数据库服务器上可以将它设置为物理内存的 80 %。 不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用
innodb_log_file_size           = 256M 						# 日志组中的每个日志文件的大小(单位 MB)。如果 n 是日志组中日志文件的数目，那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。 日志文件总和必须小于 2 GB，3.23.55 和 4.0.9 以上为小于 4 GB
innodb_log_buffer_size         = 64M 						# InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O
innodb_flush_log_at_trx_commit = 0 							# 通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为 0 ，以减少写日志文件的磁盘 I/O。这个选项默认设置为 0
innodb_log_files_in_group      = 4 							# 日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用
innodb_max_dirty_pages_pct     = 90 						# 用来控制在 InnoDB Buffer Pool 中可以不用写入数据文件中的Dirty Page 的比例(已经被修但还没有从内存中写入到数据文件的脏数据)。这个比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘IO
innodb_open_files              = 2000 						# 限制Innodb能打开的表的数据,默认值为300
innodb_doublewrite             = 1 							# 为了解决 partial page write 问题 ，当mysql将脏数据flush到data file的时候, 先使用memcopy 将脏数据复制到内存中的double write buffer ，之后通过double write buffer再分2次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，在这个过程中，doublewrite是顺序写，开销并不大，在完成doublewrite写入后，在将double write buffer写入各表空间文件，这时是离散写入。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个Page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了
innodb_file_per_table 										# 修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间,开启方法innodb_file_per_table= 1,查询方法show variables like '%per_table%';
innodb_read_io_threads         = 16 						# 配置io线程数量
innodb_write_io_threads        = 32 						# 配置io线程数量
innodb_thread_concurrency      = 16 						# 限制并发线程的数量，一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间,innodb_thread_concurrency的默认值为0，它表示默认情况下不限制线程并发执行的数量
innodb_purge_threads           = 1 							# 开启独立的碎片回收进程

[mysqldump]
quick 							# 加快mysql命令行下导出数据
max_allowed_packet = 16M 		# 服务器发送和接受的最大包长度

[mysql]
auto-rehash 					# 自动补全命令

[myisamchk]
key_buffer_size  = 256M 		# 键高速缓存
sort_buffer_size = 256M 		# 一次性分配的内存大小
read_buffer      = 2M
write_buffer     = 2M

[mysqlhotcopy]
interactive-timeout

喜欢的朋友，别忘个给个关注