大数据学习 -MYSQL进阶（第二章）（三）

1 MySQL服务器参数介绍

1.1 MySQL获取配置信息路径

• 命令行参数
  mysqlId_safe --datadir=/data/sql_data
• 配置文件
  mysqlId --help --verbose | grep -A 1 'Default options' /etc/my.cnf
  结果：/etc/my.cnf /etc/mysql/my.cnf /home/mysql/my.cnf ~/.my.cnf

1.2 MySQL配置参数的作用域

• 全局参数
  set global 参数名=参数值;
set @@global.参数名 :=参数值;
• 会话参数
  set [session] 参数名=参数值;
set @@session.参数名 :=参数值;

1.3 内存配置相关参数

• 确定可以使用的内存的上限
• 确定MySQL的每个连接使用的内存
  sort_buffer_size：排序缓冲区大小，一旦有排序，系统会将参数的最大值分配给线程，尽量设计合理，不然会造成内存溢出。
  join_buffer_size：每个线程连接缓冲区的大小。
  read_buffer_size：当MyISAM读缓存时的大小。
  read_rnd_buffer_size：索引缓冲区大小。
• 确定需要为操作系统保留多少内存
• 如何为缓存池分配内存
  Innodb_buffer_pool_size：定义了Innodb缓存池的大小。尽可能的大。
  总内存 - (每个线程所需要的内存 * 连接数) - 系统保留内存
  key_buffer_size：主要用于MyISAM存储引擎。因为MyISAM存储引擎只缓存索引。通过下列命令获取MyISAM索引缓存池的大小：
  select sum(index_length) from information_schema.tables where engine='myisam';
  尽管所使用的数据表都是Innodb存储引擎，但是还是需要给key_buffer_size分配一定的空间，因为MySQL数据库系统表默认使用MyISAM存储引擎。

1.4 I/O相关配置参数

Innodb I/O相关配置：

• Innodb_log_file_size
• Innodb_log_files_in_group
• 事务日志总大小 = Innodb_log_file_size * Innodb_log_files_in_group
• Innodb_log_buffer_size
• Innodb_flush_log_at_trx_commit
  控制日志缓冲刷新到磁盘的频率
  0：每秒进行一次log写入cache，并flush log到磁盘
  1[默认]：在每次事务提交执行log写入cache，并flush log到磁盘
  2[建议]：每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘。
• Innodb_flush_method=O_DIRECT：通知操作系统不要缓存数据，也不要预读
• Innodb_file_per_table=1：控制Innodb如何使用表空间，如果设置为1，Innodb会对每个表建立一个单独的表空间，否则会把所有Innodb的表存储在系统表空间中。
• Innodb_doublewrite=1：控制Innodb是否使用双写缓存，主要作用是避免没有写完整所导致的这种数据损坏。

MyISAM I/O相关配置：

• delay_key_write
  与Innodb_flush_log_at_trx_commit参数相似。
  OFF：每次写操作后刷新键缓冲中的脏块到磁盘
  ON：只对在建表时指定了delay_key_write选项的表使用延迟刷新
  ALL：对所有MyISAM表都是用延迟键写入

1.5 安全相关配置参数

• expire_logs_days：指定自动清理binlog的天数
• max_allowed_packet：控制MySQL可以接收的包的大小
• skip_name_resolve：禁用DNS查找
• sysdate_is_now：确保sysdate()返回确定性日期
• read_only：禁止非super权限的用户写权限
• skip_slave_start：禁用Slave自动恢复
• sql_mode：设置MySQL所使用的SQL模式
  • strict_trans_tables：如果给定的数据不能插入到事务型存储引擎中，则会中断当前操作，对非事务引擎的参数是没有任何影响
  • no_engine_subtitution：保证如果在create table语句中所指定的存储引擎不可用的情况下，不会使用默认的存储引擎来建立表，这保证了我们在建表时，可以使用我们认为MySQL所使用的存储引擎，如果我们指定的存储引擎是不可用的，这个表也建立不成功。
  • no_zero_date：在严格的SQL模式下，不能把0年0月0日这样的日期写入到表中的日期字段上。
  • no_zero_in_date：在严格的SQL模式下，不接受部分日期为0的日期。
  • only_full_group_by：要求在分组查询中，group by语句把所有的没有聚合函数的列全部要列出来，否则sql语句不能执行。

建议不要轻易改动已经使用在生产环境中的sql_mode值，以免造成应用程序出错的问题。

1.6 其它常用配置参数

• sync_binlog：控制MySQL如何向磁盘刷新binlog
  0[默认]：MySQL不会主动刷新，由操作系统自己决定什么时候刷新cache到磁盘。
  大于0：表示两次刷新到磁盘操作之间的间隔多少次二进制的写操作，通常一次事务就有一次写操作，由此可见，如果我们把值设置为1，事务完成后都会有一个binlog写日志的操作，这样操作是最安全的情况，同时也要进行磁盘的写操作，所以成本也是最高的。建议将参数的值设置为1。
• tmp_table_size和max_heap_table_size：控制Memory存储引擎的内存临时表大小，这两个值的设置尽量保持一致，并且不要设置太大，以免造成内存溢出。
• max_connections：控制允许的最大连接数。默认值为100

第二章总结

性能优化顺序

• 数据库结构设计和SQL语句
• 数据库存储引擎的选择和参数配置
• 系统选择及优化
• 硬件升级