MySQL Server优化

1，MySQL 内存管理和优化:内存时影响数据性能的重要资源，也是MySQL优化的重要方面；

内存优化原则：

1.1 将更多地内存分配给MySQL做缓存，但是要给操作系统和其他的程序的运行预留足的内存，否则如果产生swap业交换，将严重影响系统性能；

1.2 MyISAM的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM的话，就要预留更多的内存给操作系统做IO缓存

1.3 排序区、连接区等缓存是分配给每个数据库会话(session)专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费内存资源，而且并发较高时会导致物理内存耗尽；

2，MyISAM 内存优化

MyISAM存储引擎使用key buffer 缓存索引块，以加速MyISAM表的存取效率，可以设置下面这个值key_buffer_size，一般这个值设置为内存的1/4

使用多个索引缓存

(1)，MySQL 通过各session共享的key buffer 提高了MyISAM索引存取的性能，但他并不能消除对于key buffer 的竞争，这样多索引缓存机制就诞生了，mysql5.1版本之后才有的；

创建新的key_buffer

set global test_cache.key_buffer_size=128*1024;

删除创建的key_buffer

set global test_cache.key_buffer_size=0;但是不能删除默认的key_buffer;默认情况下，mysql将会使用默认的key_buffer缓存MyISAM 表的索引，我们可以用cache index命令指定表的索引缓存如下

cache index test,test1 in hot_cache;

(2)，我们也可以使用配置文件的方式在msyql启动时自动创建并加载索引缓存：

key_buffer_size = 4G;

hot_cache.key_buffer_size = 2G;

cold_cache,key_buffer_size = 1G;

init_file = /path/to/data-directory/mysqld_init.sql

我们也可以在mysqld_init.sql中使用cache index 命令分配索引缓存，并用load index cache 命令来进行索引预加载

cache index test in hot_cache;

cache index test2 in cold_cache;

load index into cache test,test2;

(3)，适当调整read_buffer_size和read_rnd_buffer_size的值

如果你需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size得值来改善性能，如果需要对MyISAM数据表做排序并且里面带有order by 子句，但是这些值都不能太大，因为他们都是每个session独占的，如果默认值太大，就会造成内存浪费，甚至导致物理内存耗尽；

3,InnoDB内存优化

InnoDB的缓存机制与MyISAM不一致，InnoDB是一块内存区作为IO缓存池，即缓存索引，也缓存数据，这与oracle的缓存机制比较相似

优化方案：

(1)，innodb_buffer_pool_size 的设置

这个参数是决定InnoDB存储引擎表数据和索引数据的最大缓存区大小，在保证操作系统以及其他的程序有足够的内存可用的时候，这个参数值越大，说明缓存命中的可能性就越高，访问InnoDB需要的磁盘IO就越少，性能就越高，在一个专门的数据库服务器上可以将80%的内存分配给InnoDB buffer pool 。

(2)，调整old sublist大小

如果你的sql操作没有太多的大表扫描或者索引扫描的情况下，我们可以适当增大innodb_old_blocks_pct的值或者减少innodb_old_blocks_time的值

(3)，调整缓存池数量，减少内部对缓存池数据结构的争用

如果你的访问量比较大或者你设置的buffer pool的值较大的情况下可以考虑设置innodb_buffer_pool_instances配置参数，对于较大的缓存池，可以使用增加该值参数；

(4)，如果你想要对你的数据缓存时间做限制，减少磁盘IO你可以考虑调大innod_max_dirty_pages_pct(默认是75%)，或者减少innodb_io_capacity(默认是200)的值；但这个也不是绝对的，如果你的磁盘足够优质的话，也可以考虑适当调大这个值；

4，如果通过show global status 看到sort_merge_passes 的值很大，可以考虑通过调整参数sort_buffer_szie的值来增大排序缓冲区，以改善带有order by 字句或者group 字句SQL的性能。对于无法通过索引进行连接操作的查询，可以尝试增大JOIN_BUFFER_SIZE的值来改善性能。

                                    

5，调整MySQL并发相关的参数

1，调整max_connection,提高并发连接

这个参数控制允许连接到mysql数据库的最大数量，默认值是151。如果状态变量connection_errors_max_connections 不为零，并且一直在增长，就说明不断有连接请求因数据库连接数已经达到最大的值而失败，应考虑增大该参数的值

在linux平台下，一般支持500~1000个连接不是什么难事，如果内存足够，不考虑响应时间，甚至能达到上万个连接，但是也应该同时也要注意评估open-files-limit的设置是否够用；

2，调整back_log 如果需要数据库在较短时间内处理大量连接请求，一般是（50+(max_connections/5)）,可以考虑适当增大back_log的值,但是不能超过900.

3，调整table_open_cache:

每一个sql执行线程至少都要打开1个表缓存，参数table_open_cache控制所有SQL执行线程可打开表缓存的数量。这个参数的值应根据最大连接数max_connections 以及每个连接执行关联中所有所涉及的表但是最大关联数来设定max_connnections*n.

4，调整thread_cache_size

该参数主要是控制MySQL缓存客户服务线程的数量

5，innodb_lock_wait_timeout的设置：该值可以控制InnoDB事务等待行锁的时间默认是50ms,对于一些交互性要求比较高的应用可以将该值调小一点，以避免事务长期挂起，但是对于一些批处理比较大的处理可以将该值调大一点，以避免发生较大的回滚操作！