MySQL性能优化 -- 服务器配置优化

常用查看性能参数的方法

show variables：查看服务器一些静态的参数，如缓冲区大小，字符集，数据文件名称等信息。
show rariables：查看的是MySQL启动之前已经配置号的一些系统静态参数。
show status：查看服务器运行中的状态信息，如当前连接数，锁等状态信息。

1、key_buffer_size的设置：
该参数用来设置索引块缓存的大小，只使用于MyISAM存储引擎，对MyISAM表性能影响最大的一个参数。
MySQL5.1以后提供多个key_buffer，可以将指定的表索引缓存到指定的key_buffer中，这样可以更好的降低线程之间的竞争。

常用操作：
查询缓存大小：show variables like ‘key_buffer_size’;
设置多个key_buffer：set global hot_cache2.key_buffer_size=128*1024;
将相关表的索引放到指定的索引缓存中：cache index t,t2 in hot_cache2;
将表t的索引加载到默认的缓存中：load index into cache t;
删除索引缓存：set global hot_cache2.key_buffer_size=0;
值得注意的是，不能删除默认的索引缓存区！

cache index可以将多个表的索引加载到指定的索引缓冲区中，但每次数据库重启后，索引缓冲区中的数据会被清空，可以在配置文件/etc/my.cnf中添加执行init-file选项，每次服务器启动自动执行脚本文件mysqld_init.sql，将多个表的索引加载到缓冲区中。

2、table_cache的设置：
该参数表示数据库用户打开表的缓存数量，每个连接进来，都至少打开一个表缓存，因此该参数的值与max_connections有关。只适用于MyISAM存储引擎。
当一个连接访问一个表时，如果该表已经在缓存中打开，则会直接访问缓存中的表信息。 如果该表没有被缓存，则会将当前的表添加到缓存中并进行查询。table_cache用于限制缓存表的最大数量。如果没有达到上限，则会将表缓存起来。如果达到上限，MySQL将根据缓存表的最后查询时间，查询率等规则释放之前的缓存。

设置该参数时，可先查看open_tables和opened_tables参数。
open_tables：表示当前打开的表缓存数。
opened_tables：表示曾打开的表缓存数。
执行flush table操作，系统会关闭一些当前没有使用的表缓存。

3、内存参数的设置
对于数据库，内存的读写速度要远远高于磁盘的速度，所以尽量让数据的读写直接在内存中进行，但由于内存资源的限制，过多地使用内存空间必将降低系统的整体性能。

1. innodb_buffer_pool_size的设置：
   该参数主要是设置缓存innodb表和索引数据，这个值设越高，访问表中的数据需要的磁盘I/O越少。默认为128MB，根据以下原则设置：

• 如果数据服务器是一个独立的服务器，考虑使用物理内存的70%-80%。
• 由于该参数不能动态更改，修改时需要重启mysql服务。
• 不宜设置过大，会导致操作系统的虚拟空间被占用，导致系统变慢，从而降低SQL查询的效率。

2）innodb_additional_mem_pool的设置：
该参数用来设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小。应用程序的表越多，需要分配的内存就越多。这个参数是稳定的，如果InnoDB用光了这个池的内存，就会开始从操作系统分配内存，并且往MySQL错误日志写警告信息。默认值是1MB，当发现错误日志中已经有相关的警告信息时，就应该适当增加该参数大小。

4、日志和事务参数的设置
以事务为主的系统，日志读写比较频繁，进行合理的设置可以降低系统I/O和其他资源开销，提升系统性能。
1）innodb_log_file_size的设置：
该参数表示设置日志组中每个日志文件的大小。在高写入负载尤其是大数据集的情况下很重要，这个值越大则性能相对越高。默认值为5MB，根据以下原则设置：

• 如果过小，日志切换比较频繁，会影响服务器性能，如果过大，当系统灾难时恢复时间会加大。
• 5.6后的版本，logfile最大可以为512GB，一般大小控制在几个日志文件相加后在2GB以内最佳。

2）innodb_log_files_in_group的设置：
该参数用来指定数据库有几个日志组，默认为2个，因为可能出现跨日志的大事务，所以一般建议使用3-4个日志组。

3）innodb_log_buffer_size的设置：
该参数的作用是设置日志缓存的大小，一旦提交事务，则将该缓存池中的内容写到磁盘的日志文件上，该参数的设置在中等强度写入负载以及较短事务情况下，一般都可以满足服务器的性能要求。如果服务器负载较大，可以加大该参数的值，一般缓存池中的内存每秒写到磁盘一次，所以设置过大则会浪费内存空间，一般为8-16MB即可。

4）innodb_flush_log_at_trx_commit的设置：
该参数用来控制缓冲区的数据写入到日志文件和将日志文件数据刷新到磁盘的操作时间。

• 设为0时，日志缓存每秒一次地被写入到日志文件中，并且将日志文件数据刷新到磁盘。
• 设为1时，InnoDB的事务日志在每次提交后写入日志文件，并对日志做刷新到磁盘的操作，这个可以做到不丢失任何一个事务，是最安全的设置。
• 设为2时，在每个事务提交时，日志缓存被写到日志文件，但不对日志文件做到磁盘刷新的操作，对日志文件的刷新也是每秒发生一次，但需要注意的是，由于进程调度的问题，并不能保证日志文件的刷新操作每秒一定会发生。
  处于性能考虑，可以设为0或2，但会在数据库崩溃的时候丢失一秒的事务。

5、存储和I/O相关参数的设置
数据库数据存储和I/O的性能直接影响到数据库数据读写执行的效率。
1）innodb_open_files的设置：
该参数是限制InnoDB存储引擎在同一时间同时能打开表的数量，默认为300，如果数据库表特别多，可以考虑增加该值。

2）innodb_flush_method的设置：
该参数是设置InnoDB引擎与操作系统进行I/O交互的模式，即刷新数据和日志的方法，有三个选项：

• Fdatasync（默认值）：InnoDB使用fsync（）函数去更新日志和数据文件。
• O_DSYNC：InnoDB使用O_SYNC模式打开并更新日志文件，使用fsync函数去更新数据文件。
• O_DIRECT：InnoDB使用O_DIRECT模式打开数据文件，使用fsync函数去更新日志和数据文件。

3）innodb_max_dirty_pages_pct的设置：
该参数是控制Innodb的脏页在缓存中的百分比，将脏页的比例控制在所设定的百分比值之下，建议设为15-90，如果设置过大，缓存中每次更新需要置换的数据页太多了。如果过小，可以存放脏数据页的缓冲区内存空间会很小，性能会受到一定的影响。

脏页：当事务需要修改某条记录时，InnoDB需要将该数据所在的page从磁盘读取到缓存池中，事务提交后，修改缓存池中的page的数据。这时候缓冲池的page和磁盘中的page就不一样了，缓冲池中的page就称为脏页，需要刷新到磁盘中，替换page。
如果突然断电，不需要担心该修改的数据会丢失。因为InnoDB采用Write Ahead Log策略来防止宕机数据丢失，即事务提交时，先写重做日志，再修改内存数据页。就是这个参数设置innodb_flush_log_at_trx_commit=1。重做日志就会发挥作用。

6、其他重要参数的设置
1）max_connect_errors的设置
该参数默认值为10，表示mysqld线程没重新启动过，一台物理服务器只要连接异常中断累计超过10次，就再也无法连接上mysqld服务。

2）interactive_timeout的设置
该参数用于设置处于交互状态连接的活动被服务器端强制关闭后等待的时间。

3）wait_time的设置
该参数用于设置客户端与服务器在无交互状态连接到被服务端强制关闭而等待的时间。此参数只有针对基于TCP/IP或基于Socket通信协议建立的连接才有效。

4）query_cache_type的设置
该参数用于控制查询结果是否放到查询缓存中。可选值为：

• 0表示禁止查询缓存。
• 1表示启用查询缓存。除了select sql_no_cache,以及不符合查询缓存设置的结果集外。
• 2仅仅缓存select sql_cache …子句的查询结果集，除不符合查询缓存设置的结果集外。

5）query_cache_size的设置
该参数用来设置查询缓存的大小。