Mysql 配置优化

一、Innodb 配置优化

线程参数配置：

sort_buffer_size （排序缓冲区）， join_buffer_size（join缓冲大小），read_buffer_size(读取缓冲区大小)，read_rnd_buffer_size（索引缓冲区大小）

 

mysql innodb系统参数配置：

innodb_buffer_pool_size=1024M #innodb 缓存池大小，设置innodb使用缓存的大小  设置内存的80%左右 一般设置和数据量大小一直  (总内存-每个线程所需要的内存*连接数-系统保留的内存) 

 

innodb_log_file_size 控制单个事务日志文件的大小

innodb_log_files_in_group 控制事务日志文件的个数

innodb_log_buffer_size 日志缓冲区大小 32M 64M 

innodb_flush_log_at_trx_commit=2 #设置为 0 每秒log buffer的内容写入日志，并且刷新到磁盘 设置为1 每个事务提交后，将log buffer的内容写事务日志并且写入磁盘，设置2每个事务提交，将log buffer 内容写入事务日志，但不写入磁盘，机械硬盘设置为2，固态硬盘设置为1

innodb_flush_method=O_DIRECT 通知操作系统不要缓存数据，数据直接写入到磁盘。

innodb_file_per_table=1 打开独立表空间

innodb_doublewrite=1  开启双写缓存，防止数据丢失

expire_logs_days=0 binlog日志保存的天数

max_allowed_packet=1073741824 mysql最大接收的包的大小

read_only=1 禁止任何没有super权限的账号修改权限，一般在从库中启用该选项

sql_mode 可指定sql的标准规范可选值：

STRICT_TRANS_TABLES  如果给定的数据不能插入到事务中会中断sql中
NO_ENGINE_SUBSTITUTION  如果在create table中指定的存储引擎不可用的情况下，让表创建失败，不是默认的存储引擎创建表
only_full_group_by 要求查询中group by没有group by的字段不能进行查询
no_zreo_date 表是日期中不允许存储 0000-00-00 这样的值

 sync_binlog 控制mysql如何向磁盘刷新binlog 默认0由系统决定什么时候刷新binlog，设置为1，每次事务执行完成后刷新binlog 影响性能，但是数据安全

 

tmp_table_size 和 max_heap_table_size 控制内存临时表大小，两个参数值应该保持一致，并且不要设置太大，防止内存溢出

 

max_connections mysql最大的链接数，默认100，通常会修改为2000或者更大

 

二、数据库设计对性能的影响：

1、过分的反范式化建立太多的列

2、过分的范式化造成太多的表关联

3、在交易系统中中使用不合适的分区表

4、使用外键保证数据的完整性

 

性能优化顺序：

1、数据库设计和sql语句

2、数据库存储引擎和参数的配置

3、系统选择及优化

4、硬件升级