运维实战：MySQL配置文件优化之（InnoDB存储引擎）

运维实战：MySQL配置文件优化之（InnoDB存储引擎）

        MySQL在配置文件的优化幅度小，大部分是架构以及sql语句的调优。

---

架构上的优化思路

1.1 降低服务器压力

      从业务上实现用户登陆后在搜索，这样减少搜索次数，从而减少数据库服务的压力；如果有大量频繁的搜索，一般是由爬虫在爬你的网站，分析web日志IP封掉；配置多个主从同步，程序上实现读写分离。还可以单独设置索引服务器，这些条件是建立在有足够资金的公司；

 

2 配置文件优化(InnoDB存储引擎)

2.1 参数介绍

Innodb_buffer_pool_size = 2048M
# 将数据库的数据缓存在buff_pool，一般建议为物理内存的50%一下，通常为30%;

sort_buffer_size = 2M
# 指明线程buffer大小，此参数为单个连接分配的Buffer，如果设置过大会导致性能降低;

query_cache_size = 64M
# 用于查询的缓存的内存被分成一个个变长数据块,用来存储类型,大小,数据等信息;

query_cache_limit = 4M
# 指定单个查询能够使用的缓冲区大小，缺省为1M;

query_cache_min_res_unit = 2k
# 查询缓存分配的最小块的大小，存小的频繁访问的语句;

expire_logs_days = 7
# 设置自动清理binlog的周期;

skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析，使用这一选项可能会导致权限错误的问题;

innodb_data_file_path = ibdata1:1024M:ibdata2:1024M:autoextend
# innodb引擎的数据文件，数据量大的场景多设置几个，自动扩充加8M;

back_log=600
# MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中;

max_connections=1000
# 最大连接数;

max_connect_errors=10000
# 如果MySQL服务器连续接收到了来自于同一个主机的请求，而且这些连续的请求全部都没有成功的建立连接就被中断了，当这些连续的请求的累计值大于max_connect_errors的设定值时，MySQL服务器就会阻止这台主机后续的所有请求。(网络不好时)

open_files_limit=65535
# 最大文件打开数;

table_open_cache=128
# 表文件描述符的缓存大小;

innodb_file_per_table ON
# 表示每张表使用单独的表空间，如果不指明默认是所有库的所有表放置在同一个统一的表空间中;
.idb为表空间;
.frm为表结构;

innodb_open_files
限制Innodb能打开的表的数据，默认为300，数据库里的表特别多的情况，可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小;

innodb_log_buffer_size
事务在内存中的缓冲，也就是日志缓冲区的大小，默认设置即可，具有大量事务的可以考虑设置为64-512MB;

innodb_data_file_path = ibdata1:1024M:autoextend:max:1G
# innodb引擎的单个数据文件的大小，数据量大的场景多设置几个;
# 初始大小为1024MB，在到达当前大小限制1G前，自动增加8MB;

sync_binlog = ON
#保证事务安全，在主节点上启用sync_binlog，遇到事务提交时，在缓冲区中记录的事件立即刷新到磁盘的二进制日志文件中;

innodb_flush_logs_at_trx_commit=ON
#事务提交时，要将内存中跟事务相关的数据立即写入事务日志中，属于同步操作;

innodb_support_xa=ON; #是否让innodb能否做分布式事务

skip_slave_start=ON  #关闭自动slave同步，保证事务安全，主从同步从节点理应开启;

binlog_cache_size=2M
# 为每个连接分配的缓存大小，未提交事务将缓存至此，而已提交事务将写入磁盘持久保存;

read_buffer_size = 2M
# MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率数据文件顺序;

read_rnd_buffer_size 8M
# MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率;

sort_buffer_size = 8M
# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段;

join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享;

thread_cache_size = 8
# 重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中;
# 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能。通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–>表示要调整的值)
# 根据物理内存设置规则如下：
# 1G  —> 8
# 2G  —> 16
# 3G  —> 32
# 大于3G  —> 64

query_cache_size = 8M
# 查询缓冲区大小，MySQL将SELECT语句和查询结果存放在缓冲区中;
SQL_CACHE:显示存储查询结果于缓存之中
SQL_NO_CACHE:显示查询结果不予缓存

query_cache_limit = 1M
#指定单个查询能够使用的缓冲区大小，默认1M;

query_cache_type ON
# 开启查询缓存功能，符合条件才会缓存。
如果值为DEMAND，查询缓存功能按需进行。必须使用指定SQL_CACHE才会缓存。
例子：
select SQL_CACHE count(*) from fp_data;
# 计算缓存命中率;
show global status like 'Qcache%';
Qcache_hits # 命中次数;
show global status like 'Com_se%';
Com_select  # 查询次数;

key_buffer_size = 4M
# 指定用于索引的缓冲区大小;

log_bin=mysql-bin
#开启binlog功能;

binlog_format=mixed
# 日志格式，建议为mixed;

slow_query_log = 1
# 当值为1的时表示开启慢查询;

long_query_time = 2
# 慢查询时间 超过2秒则为慢查询;

slow_query_log_file=/home/app/mysql/logs/mysql-slow.log
# 慢查询日志定义;