[MySQL] Mysql的并发参数调整详解

• 查询缓存优化
  
  
  
  • 概述
    
  • 查询流程
    
  • 查询缓存配置
    
  • 查询缓存失效的情况
    
  
  
• 内存管理优化
  
  
  
  • 内存优化原则
    
  • MyISAM内存优化
    
  • InnoDB内存优化
    
  
  
• 连接优化
  
  
  
  • max_connection
    
  • back_log
    
  • table_open_cache
    
  • thread_cache_size
    
  • innodb_lock_wait_timeout
    
  
  
• 日志
  
  
  
  • log_bin
    
  • binlog_do_db binlog_ignore_db
    
  • sync_binlog
    
  • general_log=1
    
  • general_log_filefile_name(默认host_name.log)
    
  • slow_query_log=1
    
  • long_query_time=3
    
  • log_slow_admin_statements
    
  
  

当开始Mysql的查询缓存，执行相同的sql语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存

1、客户端发送一条查询给服务器
2、服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一阶段
3、服务器端进行sql解析、预处理、再由优化器生成对应的执行计划
4、根据生成的执行计划，调用存储引擎的Api来执行查询
5、将结果返回给客户端--并将查询到的结果放到查询缓存中

查询数据库是否支持查询缓存(YES or ON)
  show variables like 'have_query_cache';
查看数据库是否开启了查询缓存 
  show variables like 'query_cache_type';
  OFF 或 0 查询缓存功能关闭
  ON 或 1查询缓存功能打开，select的结果符合缓存条件即会缓存，否则不予缓存，显示指定SQL_NO_CACHE,不予缓存
  DEMAND 或 2 查询缓存功能按需进行，显示指定SQL_CACHE的SELECT语句才会缓存；其他不予缓存
查看查询缓存的大小
  show variables like 'query_cache_size';
查看缓存的状态
  show status like 'Qcache%';
    Qcache_free_blocks 缓存中可用的内存块数量
    Qcache_free_memory 缓存中可用的内存量
    Qcache_hits 查询缓存命中数
    Qcache_lowmen_prunes 由于内存不足而从查询缓存中删除的查询数
    Qcache_not_cached 非缓存查询的数量（query_cache_type设置而无法缓存）
    Qcache_queries_in_cache 查询缓存中注册的查询数
    Qcache_total_blocks 查询缓存中的块总数
开启查询缓存
  找到mysql的配置文件添加 my.cnf 添加query_cache_type=1
  开启查询缓存结果是 
    query_cache_type=DEMAND   
    select SQL_CACHE id from table
  开启查询缓存结果是 
    query_cache_type=ON  
    select SQL_NO_CACHE id from table

sql语句不一致情况
查询sql中有不确定信息：
  如 now(),current_date(),curdate(),curtime(),rand(),uuid(),database()
不使用任何表查询语句
查询 mysql ，infomation_schema或 performance_schema数据库中的表时，不会走查询缓存
在存储函数，触发器或事件的主体内执行的查询
表修改会删除查询缓存，如 insert，update，delete，drop...

将尽量多的内存分配给Mysql做缓存，要给操作系统和其他程序预留足够内存
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存
排序区，连接区等缓存是分配给每个数据库会话（session）专用的，其默认的设置要根据最大的连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致无力内存耗尽

myisam存储引擎使用key_buffer缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的io缓存
key_buffer_size=512M
  key_buffer_size的值越大效率越高，建议至少将1/4可用内存分配给key_buffer_size
show variables like 'key_buffer_size'; 查询key_buffer_size的大小
red_buffer_size
  如果经常顺序扫描myisam表，可以通过增大read_buffer_size的大小值来改善性能，但不能调的过大，因为key_buffer_size是每个session独占的，如果设置多个会造成资源浪费
red_rnd_buffer_size
  经常需要排序的myisam表的查询，如果带有order by 子句的sql 适当增加red_rnd_buffer_size的值可以改善此类sql性能---也是session独享的，也不能设置过大

InnoDB用一块内存区做io缓存池，该缓存池不仅用来缓存innoDB的索引块，而且也用来缓存innoDB的数据块
inodb_buffer_pool_size
  该变量决定了innoDB存储引擎和索引数据的最大缓存区大小，在保证操作系统及其他程序有足够内存可用的情况下，值越大缓存命中率越高，访问innoDb表需要的磁盘io就越少，性能也就越高
  innodb_buffer_pool_size=521M
innodb_log_buffer_size
  决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size大小，可以避免innodb在事务提交前执行不必要的日志写入磁盘io
  innodb_log_buffer_size=10M
show variables like 'innodb_buffer_pool_size';
innodb_flush_log_at_trx_commit
  控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为，0，1，2
innodb_thread_concurrency
  设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的CPU核心数一致或者CPU核心数的两倍
innodb_log_buffer_size
  执行日志文件所用的内存大小，以M为单位
innodb_log_files_in_group
  以循环的方式将日志文件写到多个文件中
read_buffer_size
  mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size
  mysql随机读的缓冲区大小

最大连接数，默认是151，linux一般可以支持500--1000具体根据服务器性能评估
show variables like 'max_connections';
  查看最大连接数

超过最大连接数后最大的等待数量，存放在堆栈中（一般是50+（max_connections/5）），最大不超过900
show variables like 'back_log'

控制所有sql语句执行线程可打开表缓存数量，而在执行sql语句时，每一个sql执行线程至少要打开1个表缓存，该参数的值应该根据设置的最大连接数max_connectons以及每个连接执行关联查询中涉及的表的最大数量来设定

为了加快连接数据库的速度，mysql会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制Mysql缓存客户服务线程的数量

设置innoDB事务等待行锁的时间，默认是50ms，可以根据需要动态设置，对于需要快速反馈的业务系统，可以将行锁的等待时间调小，避免事务长时间挂起，对于后台运行的批量处理程序，可以将行锁的等待时间调大，以避免发生大的回滚操作
show variables like 'innodb_lock_wait_timeout';

指定二进制日志文件名称，用于记录对数据库造成更改的所有语句

指定将更新记录到二进制日志的数据库，其他所有没有显示指定的数据库更新将忽略，不记录在日志中

指定多少次写日志后同步磁盘

是否开启查询日志记录

指定查询日志文件名，用户记录所有的查询语句
查询日志文件名：slow_query_log_file=slow_query.log
mysqldumpslow slow_query.log;查看慢查询日志文件

是否开启慢查询日志，1代表开启，0代表关闭

设置慢查询的时间，超过这个时间的查询语句才会记录日志

是否将管理语句写入慢查询日志