MySQL参数优化

• 最大连接数

  • max_connections是MySQL最大并发连接数，默认值151
  • MySQL允许的最大连接数上限是16384
  • 实际连接数是最大连接数的85%较为合适

  SHOW VARIABLES LIKE 'MAX_CONNECTIONS'; //查看数据库设置的最大连接数
  SHOW STATUS LIKE 'MAX_USED_CONNECTIONS';//查看当前并发连接数
  

  可以在my.cnf 文件中[mysqld] 下面设置参数max_connections=5000

  具体设置大小

  mysql为每个连接创建缓冲区，所以不应该盲目上调最大连接数

  需要根据主机的内存进行设置

  #消耗约800m内存
  max connections3000
  

• 请求堆栈的大小

  • back_log是存放执行请求的堆栈大小，默认值是50
  • 一般堆栈大小设置成最大连接数的1/3

  首先mysql不是实际的连接数达到了最大的并发连接数。再有新的请求，数据库就会拒绝，

  而是数据库会把新的连接请求存放在堆栈里边。等到有空闲连接的时候再把连接分配给堆栈中的请求

  一般设置成最大连接数儿的20%到30%是比较恰当的

  可以在my.cnf 文件中[mysqld] 下面设置参数back_log=90

• 修改并发线程数

  • innodb_thread_concurrency代表并发线程数，默认是0，也就是说没有设置线程数量的上限。
  • 并发线程数应该设置为CPU核心数的两倍

  不是分配给mysql的线程越多越好，线程多了反而调度大量的线程，会损耗CPU的性能。导致运行速度的变慢，所以呢，给mysql分配最大的线程数量应该是CPU内核数量的两倍。

  比如主机有32个核心 可以在my.cnf 文件中[mysqld] 下面设置参数innodb_thread_concurrency=64

  

• 修改连接超时时间

  • wait-timeout是超时时间，单位是秒
  • 连接默认超时为8小时，连接长期不用又不销毁，浪费资源

  可以在my.cnf 文件中[mysqld] 下面设置超时时间为10分钟 参数wait-timeout=600

• 数据缓存

  • innodb_buffer_pool_size是InnoDB的缓存容量，默认是128M
  • InnoDB缓存的大小可以设置为主机内存的70%~80%

innoDB缓存是把innoDB数据页缓存到内存上，注意缓存的不是查询结果集。而是数据表的数据，为了避免每次都要从磁盘读取数据表的内容，提高查询效率。

可以在my.cnf 文件中[mysqld] 下面设置超时时间为10分钟 参数innodb_buffer_pool_size=400M

慢查询日志

慢查询日志会把查询耗时超过规定时间的SQL语句记录下来，利用慢查询日志，定位分析性能的瓶颈。

利用慢查询日志，定位分析性能的瓶颈

SHOW VARIABLES LIKE 'slow_query%'

slow_query_log 可以设置慢查询日志的开闭状态

long_query_time 可以规定查询超时的时间，单位是秒

slow_query_log = ON
long_query_time = 1

对于慢查询语句可以使用EXPLAIN查询语句执行计划进行相应优化