mysql优化参考(五)-服务器参数

可以用客户端连接后通过命令来查看：

show variables;

show variables like '%connection%';

参考地址：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

一、通用（General）

• datadir=/var/data/mysql     数据文件存储目录
• socket=/var/lib/mysql/mysql.sock　　服务器和客户端在同机会使用这个socket文件进行连接
• pid_file=/var/lib/mysql/mysql.pid　　  mysql的进程ID
• default_storage_engine=InnoDB　　    默认存储引擎
• skip-grant-tables　　　　                 如果忘记密码可以通过这个跳过验证，免密登录

二、编码（Character）

• character_set_client    客户端数据的编码集，可以动态连接指定
• character_set_connection  客户端连接传输信息的字符集，可以动态指定
• character_set_results　　服务端返回客户端结果的字符集
• character_database　　数据库默认字符集
• character_set_server　　mysql服务器的默认字符集

三、连接（Connection）

• max_connections=151        mysql的最大连接数
• max_user_connections=0   mysql指定单个用户的最大连接数，默认是0，表示不限制
• connect_timeout               连接超时时间
• back_log　　                    表示连接数满了之后，新的请求有多少可以暂存在堆栈中，超出的部分则不再接受请求
• wait_timeout                     关闭一个非交互连接需要等待的时间
• interactive_timeout　　      关闭一个交互连接需要等待的时间 

四、日志（log）

• log_error　　      错误日志存储地址
• log_bin               二进制日志存储地址
• binlog_do_db       指定需要存储到二进制日志的数据库
• binlog_ignore_db 指定不需要存储到二进制日志的数据库
• sync_binlog         多少次写日志后同步磁盘
• general_log         ON|OFF 是否开启查询日志
• general_log_file   查询日志的存储地址
• slow_query_log    ON|OFF是否开启慢查询日志
• slow_query_log_file 慢查询日志的存储文件地址
• long_query_time   慢日志中需要的一个参考值，多少查询时间算是慢查询

------------恢复内容开始------------

可以用客户端连接后通过命令来查看：

show variables;

show variables like '%connection%';

参考地址：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

一、通用（General）

• datadir=/var/data/mysql     数据文件存储目录
• socket=/var/lib/mysql/mysql.sock　　服务器和客户端在同机会使用这个socket文件进行连接
• pid_file=/var/lib/mysql/mysql.pid　　  mysql的进程ID
• default_storage_engine=InnoDB　　    默认存储引擎
• skip-grant-tables　　　　                 如果忘记密码可以通过这个跳过验证，免密登录

二、编码（Character）

• character_set_client    客户端数据的编码集，可以动态连接指定
• character_set_connection  客户端连接传输信息的字符集，可以动态指定
• character_set_results　　服务端返回客户端结果的字符集
• character_database　　数据库默认字符集
• character_set_server　　mysql服务器的默认字符集

三、连接（Connection）

• max_connections=151        mysql的最大连接数
• max_user_connections=0   mysql指定单个用户的最大连接数，默认是0，表示不限制
• connect_timeout               连接超时时间
• back_log　　                    表示连接数满了之后，新的请求有多少可以暂存在堆栈中，超出的部分则不再接受请求
• wait_timeout                     关闭一个非交互连接需要等待的时间
• interactive_timeout　　      关闭一个交互连接需要等待的时间 

四、日志（log）

• log_error　　      错误日志存储地址
• log_bin               二进制日志存储地址，避免数据丢失（恢复）
• binlog_do_db       指定需要存储到二进制日志的数据库
• binlog_ignore_db 指定不需要存储到二进制日志的数据库
• sync_binlog         多少次写日志后同步磁盘
• general_log         ON|OFF 是否开启查询日志
• general_log_file   查询日志的存储地址
• slow_query_log    ON|OFF是否开启慢查询日志
• slow_query_log_file 慢查询日志的存储文件地址
• long_query_time   慢日志中需要的一个参考值，多少查询时间算是慢查询
• log_slow_admin_statements

五、缓存（cache）

• key_buffer_size     索引缓存区的大小，只对MyIsAM生效
• query cache　　8之后废弃

• • query_cache_size  查询缓存的大小
    • show status like '%Qcache%';
    •  
    • Qcache_free_blocks：缓存中相邻内存块的个数，数值比较大表示内存碎片比较多
    • Qcache_free_memory：查询缓存中剩余的内存大小
    • Qcache_hits：命中缓存的次数（总统计）
    • Qcache_inserts：没命中多少次（没命中会导致插入）
    • Qcache_lowmem_prunes：多少条内存因为内存不足而被清除
    • Qcache_not_cached：没有被cache的只读查询数量
    • Qcache_queries_in_cache：当前缓存的query数量
    • Qcache_total_blocks：当前cache中block的数量
  • query_cache_limit  超出此大小的数据将不被缓存
  • query_cache_min_res_unit 缓存块最小大小
  • query_cache_type
    • 0 or OFF ：不缓存,query_cache_size最好对应设置为0
    • 1 or ON：除了SELECT SQL_NO_CACHE外，其他都缓存
    • 2 or DEMAND：仅缓存SELECT SQL_CACHE
      
• sort_buffer_size   每个需要排序的线程分配该大小的缓冲区
  
• max_allowed_packet=32M  限制server接受的数据包大小
• join_buffer_size=2M  表示关联缓存的大小
• thread_cahce_size 
• (show status) Threads_cached  代表当前此时此刻线程缓存有多少空闲线程
• (show status) Threads_connected
• (show status) Threads_created
• (show status) Threads_running

六、InnoDB

　　日志机制

• redo log： 一致性、持久性。  先写入日志，再提交；相当于两阶段提交，或者二次持久化（先持久化到日志【磁盘】，再持久化到数据库【磁盘】。也就是说日志持久化作为数据库持久化的担保）。
• undo log：原子性支持。相当于命令回滚，会对应事务的一个反向操作，比如delete一条记录，undo log就会对应生成一条insert记录。在事务回滚的时候执行undo log，来回滚操作。

• innodb_buffer_pool_size  指定大小的内存来缓冲数据和索引，最大可以设置物理内存的80%
• innodb_flush_log_trx_commit  主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0 1 2（dml commit->log buffer->oss buffer->file system；一种直接读入oss buffer并触发异步同步 应该是1，一种是直接读入oss buffer，并每秒触发fsync，应该是0；还有一种是读取log buffer，并每秒读入oss buffer并触发fsync，应该是2）
• innodb_thread_concurrency  设置innodb的线程并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
• innodb_log_buffer_size 此参数确定日志文件所用的内存大小，以M为单位
• innodb_log_file_size 此参数确定日志文件的大小，以M为单位
• innodb_log_files_in_group 以循环方式（redo log）将日志写入到多个文件
• read_buffer_size         mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
• read_rnd_buffer_size   mysql随机读的缓冲区大小
• innodb_file_per_table  此参数确定为每张表分配一个新的文件，不配置的话，一个表只有一个文件（表结构文件，数据文件统一存储）

注1  binlog：通用的操作日志，可用于数据恢复，保存的是原始的操作内容，与存储引擎无关。

注2 ACID：

• 原子性（atomic）：MVCC（多版本高并发控制） -undo机制
• 一致性（Consistency）：redo log一致性，二次提交
• 隔离性（Isolation）：锁机制
• 持久性（Durability）：redo log最终也是要从内存（日志）->硬盘（日志）->内存（数据）->硬盘（数据）

注3 DEMAND：需求