MySQL性能优化实战，MySQL性能调优和系统资源优化解决
⽅案（⼆）
⽬录
前⾔
在上第⼀部分⽂章中完成了MySQL数据库的结构优化、性能调优，接下来这部分主讲系统资源调优、通过MySQL的系统配置参数实现。
服务器优化
⼋、服务器优化
1、优化服务器硬件
服务器的硬件性能直接决定着 MySQL数据库的性能，硬件的性能瓶颈，直接决定 MySQL数据库的运⾏速度和效率。
硬件服务器的优化需要从以下⼏个⽅⾯考虑：
1. 配置较⼤的内存，⾜够⼤的内存，是提⾼ MySQL数据库性能的⽅法之⼀。内存的 IO⽐硬盘快的多，可以增加系统的缓冲区容量，使
数据在内存停留的时间更长，以减少磁盘的 IO；
2. 配置⾼速磁盘，⽐如 SSD；
3. 合理分配磁盘 IO，把磁盘 IO分散到多个设备上，以减少资源的竞争，提⾼并⾏操作能⼒；
4. 配置多核处理器，MySQL 是多线程的数据库，多处理器可以提⾼同时执⾏多个线程的能⼒；
2、优化 MySQL的参数
通过优化 MySQL的参数可以提⾼资源利⽤率，从⽽达到提⾼MySQL服务器性能的⽬的。在 MySQL交互模式下的命令：“ SHOW VARIABLES ”，如下图：
MySQL 的配置参数都在 myf或者 my.ini⽂件的 [mysqld] 组中，配置⽰例如下：
# For advice on how to change settings please see
# sql/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
lower_case_table_names = 1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
九、MySQL 中的配置参数详解
1）、MySQL 的基础配置参数
参数名说明
user⽤户名
bind-address绑定IP地址
port端⼝号
datadir数据⽂件存放⽬录
basedir MySQL 应⽤程序存放⽬录socket socket ⽂件存放⽬录⽂件default-table-type配置系统默认引擎
参数名说明
2）、MySQL 查询缓存配置参数
参数名说明
query_cache_size 配置 MySQL的查询缓存⼤⼩，
缓存⼤⼩的直接因素：
1、对DDL和DML语句的性能影响；
2、内部维护成本；
3、命中率及内存使⽤率；
4、项⽬/产品的业务类型；
query_cache_type 查询缓存类型。取值如下：
0 OFF，禁⽤查询缓存功能；
1 ON，启⽤缓存的功能，缓存所有符合要求的查询结果集，除SELECT SQL_NO_CACHE ...，以及不符合查询缓存设置的结果集外；
2 DEMAND，仅仅缓存SELECT SQL_CACHE ...⼦句的查询结果集，除不符合查询缓存设置的结果集外；
query_cache_limit查询结果长度的长度限制，⼩于这个长度的数据才能被缓存
3）、MySQL 中 InnoDB 存储引擎的配置参数
InnoDB 参数较少，内存的管理多由 InnoDB引擎⾃⼰负责，主要配置参数为“innodb_buffer_pool_size”，参数分配的缓冲。
参数名说明
innodb_buffer_pool_size新开辟⼀⽚内存⽤于缓存 InnoDB引擎表的数据和索引，⼀般设置为机器内存的50%-80%（关
键参数）
该参数的最⼤值受限于服务器本⾝ CPU的架构，⽀持32位还是⽀持64位，另外还受限于操作
sql语句实现的四种功能系统32位还是64位；
合理设值 innodb_buffer_pool_size，有利于节约访问表对象中数据的物理IO。在官⽅⼿册上
建议专⽤的数据库服务器，考虑设置为物理内存总量的80%。除了物理服务器的物理内存总量
外，还需要考虑整个数据库中是否只使⽤ InnoDB引擎、mysqld内部管理占⽤的内存、最⼤线
程连接数和临时表等额外的因素，官⽅提供的 80%仅作为⼀个参考。
在以下四种情况中，需要考虑降低 innodb_buffer_pool_size的设值：
1、出现物理内存的竞争，可能导致操作系统的分页；
2、InnoDB预分配额外的内存给缓冲区和结构管理，当分配的总内存量超过
innodb_buffer_pool_size值的10%；
3、地址内存空间要求必须为连续的，在windows系统有⼀个严重问题，DLL需要加载在特定
的地址空间内；
4、初始化缓冲区的时间消耗，与缓冲区的⼤⼩成正⽐；
innodb_log_buffer_size InnoDB ⽇志缓冲区⼤⼩
innodb_flush_method刷新⽇志的⽅法
innodb_additional_mem_pool_size开辟⼀⽚内存⽤于缓存 InnoDB引擎的数据字典信息和内部数据结构（⽐如：⾃适应HASH索
引结构），默认值：build-in版本默认值为：1M；Plugin-innodb版本默认值为：8M；
注：若是 mysqld服务上的表对象数量较多，InnoDB引擎数据量很⼤，且
innodb_buffer_pool_size的值设置较⼤，则应该适当地调整
innodb_additional_mem_pool_size的值。若是出现缓存区的内存不⾜，则会直接向操作系统
申请内存分配，并且会向 MySQL的 error⽇志⽂件写⼊警告信息；
innodb_data_home_dir InnoDB 数据⽂件的⽬录
innodb_data_file_path数据⽂件配置
innodb_log_files_in_group InnoDB ⽇志的
innodb_log_file_size指定 InnoDB ⽇志⽂件的⼤⼩
innodb_lock_wait_timeout等待锁的超时时间，避免死锁
innodb_flush_log_at_trx_commit⽇志提交⽅式 （关键参数）：
0，每秒写1次⽇志，将数据刷⼊磁盘，相当于每秒提交⼀次事务；
1，每次提交事务写⽇志，同时刷新相应磁盘 -> 默认参数；
2，每提交事务写⼀次⽇志，但每隔⼀秒刷新⼀次相应的磁盘⽂件；
innodb_force_recovery在 InnoDB 的⾃动恢复失败后，从崩溃中强制启动，有1-6个级别，数值越低恢复的⽅式也保
守，默认为4。尽量使⽤较保守⽅式恢复。恢复后要注释删除这⼀⾏。
4）、MySQL 中 MyISAM 存储引擎的配置参数
参数名说明
key_buffer_size索引块⽤的缓冲区⼤⼩，所有的连接程序线程共⽤（关键参数）
key_buffer_size只缓存MyISAM或MyISAM引擎的索引数据，⽽ innodb_buffer_pool_size不仅
能缓存索引数据，还能缓存元数据，但是对于只使⽤ InnoDB引擎的数据库⽽⾔，此参数值也不能
设置过于偏⼩，因为临时表可能会使⽤到此缓存空间，推荐⼤⼩：64M。
key_cache_block_size每⼀个索引 Block 的⼤⼩，默认 1024 字节，从4.1.1后才出现这个参数，原来都是直接采⽤
1024 字节作为 Block 的长度
5）、MySQL ⽇志配置参数
MySQL中⽇志是监控数据库系统的重要途径。MySQL的⽇志分为6种：查询⽇志、慢查询⽇志、变更⽇志、⼆进制变更⽇志、告警⽇志、错误⽇志。
log查询⽇志，记录所有的 MySQL的命令操作，在跟踪数据库运⾏时⾮常有帮助
log-slow-queries慢查询⽇志
log-update变更⽇志，⽤⽂本⽅式记录所有改变数据的变更操作
log-bin⼆进制变更⽇志，更加紧凑，使⽤mysqlbinlog读取，操作，转换
log-warnings告警⽇志
log-error错误⽇志
binlog_cache_size临时存放某次事务的 SQL语句缓冲长度
max_binlog_cache_szie最⼤的⼆进制缓存⽇志缓冲区⼤⼩
max_binlog_size最⼤的⼆进制⽇志⼤⼩
long_query_time慢查询时间限度，超过这个限度，MySQL 将认为是⼀个慢查询，并记录到慢查询⽇志中
log-queries-not-using-indexes没有使⽤索引查询的⽇志，⽅便记录长时间访问的查询进⾏优化
6）、MySQL 连接通信参数配置
参数名说明
max_connections MySQL 的最⼤连接数，该值⼀般情况下都会设值在128-1024的范围，再结合实际业务可能的最
⼤事务并发度。
max_connect_errors同⼀个连接地址最⼤错误连接数，防⽌恶意攻击，默认值为10，也即 mysqld线程没重新启动过，
⼀台物理服务器只要连接异常中断累计超过10次，就再也⽆法连接上 mysqld服务；若异常中断累
计超过参数设置的值，有两种解决⽅案，执⾏命令：FLUSH HOSTS 或者重新启动 mysqld服
务。
net_buffer_length服务器和客户端之间通讯，使⽤的缓冲区长度，单位：字节
max_allowed_packet服务器和客户端之间最⼤的通信缓冲区长度，单位：字节
net_read_timeout读取超时
net_write_timeout写⼊超时
interactive_timeout交互模式下，没有操作后的超时时间，单位：秒
wait_ timeout⾮交互模式，没有操作后的超时时间，此参数只对基于 TCP/IP或基于 Socket通信协议建⽴的连
接才有效，单位：秒
7）、MySQL 的Buffer缓冲区参数设置
在前⾯⼀篇⽂章MySQL的MyCat中间件中介绍到了Buffer 池的概念，以及其设值的规则，和这⼉的 Buffer 类似，但⼜不同。
参数名说明
read_buffer_size读取缓冲区⼤⼩

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。