1．MySQL数据库高效优化解析

Mysql优化是一项非常重要的工作，而且是一项长期的工作，曾经有一个为位DBA前辈说过:mysql的优化，三分配置的优化，七分sql语句的优化。
Mysql的优化：一般分为配置的优化、sql语句的优化、表结构的优化、索引的优化，而配置的优化：一般包括系统内核优化、mysql本身配置文件的优化。
MySQL常见的优化参数详解：
硬件上的优化：增加内存和提高磁盘读写速度，都可以提高MySQL数据库的查询，更新的速度。另一种提高MySQL性能的方式是使用多块磁盘来存储数据。因为可以从多块磁盘上并行读取数据，这样可以提高读取数据的速度。
MySQL参数的优化：内存中会为MySQL保留部分的缓冲区。这些缓冲区可以提高MySQL的速度。缓冲区的大小都是在MySQL的配置文件中进行设置的。
下面对几个重要的参数进行详细介绍：
key_buffer_size：表示索引缓存的大小。这个值越大，使用索引进行查询的速度就越快。
table_cache：表示同时打开的表的个数。这个值越大，能同时打开的表的个数就越多。这个值不是越大越好，因为同时打开的表过多会影响操作系统的性能。
query_cache_size：表示查询缓冲区的大小。使用查询缓存区可以提高查询的速度。这个方式只使用与修改操作少且经常执行相同的查询操作的情况；默认值是0。
query_cache_type：表示查询缓存区的开启状态。0表示关闭，1表示开启。
max_connections：表示数据库的最大连接数。这个连接数不是越大越好，因为连接会浪费内存的资源。
sort_buffer_size：排序缓存区的大小，这个值越大，排序就越快。
Innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。这个值太大了就会影响操作系统的性能。
当然了Mysql是一个长期的优化过程，所以在日常的运维工作中，需要不断去总结和学习。

2.建议调整下面几个关键参数以获得较好的性能：

1、选择Percona或MariaDB版本的话，强烈建议启用thread pool特性，可使得在高并发的情况下，性能不会发生大幅下降。此外，还有extra_port功能，非常实用， 关键时刻能救命的。还有另外一个重要特色是 QUERY_RESPONSE_TIME 功能，也能使我们对整体的SQL响应时间分布有直观感受；
2、设置default-storage-engine=InnoDB，也就是默认采用InnoDB引擎，强烈建议不要再使用MyISAM引擎了，InnoDB引擎绝对可以满足99%以上的业务场景,在高版本中已经默认InnoDB了，已经完全取代MyISAM；
3、调整innodb_buffer_pool_size大小，如果是单实例且绝大多数是InnoDB引擎表的话，可考虑设置为物理内存的50% ~ 70%左右；
4、根据实际需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求数据不能丢失，那么两个都设为1。如果允许丢失一点数据，则可分别设为2和10。而如果完全不用care数据是否丢失的话（例如在slave上，反正大不了重做一次），则可都设为0。这三种设置值导致数据库的性能受到影响程度分别是：高、中、低，也就是第一个会另数据库最慢，最后一个则相反；
5、设置innodb_file_per_table = 1，使用独立表空间，我实在是想不出来用共享表空间有什么好处了；
6、设置innodb_data_file_path = ibdata1:1G:autoextend，千万不要用默认的10M，否则在有高并发事务时，会受到不小的影响；
7、设置innodb_log_file_size=256M，设置innodb_log_files_in_group=2，基本可满足90%以上的场景；
8、设置long_query_time = 1，而在5.5版本以上，已经可以设置为小于1了，建议设置为0.05（50毫秒），记录那些执行较慢的SQL，用于后续的分析排查；
9、根据业务实际需要，适当调整max_connection（最大连接数）、max_connection_error（最大错误数，建议设置为10万以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小；
10、常见的误区是把tmp_table_size和max_heap_table_size设置的比较大，曾经见过设置为1G的，这2个选项是每个连接会话都会分配的，因此不要设置过大，否则容易导致OOM发生；其他的一些连接会话级选项例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等，也需要注意不能设置过大；
11、由于已经建议不再使用MyISAM引擎了，因此可以把key_buffer_size设置为32M左右，并且强烈建议关闭query cache功能；

3.Mysql Innodb 引擎优化

1.首先介绍一个Innodb最重要的参数：
innodb_buffer_pool_size=4G
该参数分配内存的原则：这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用ＤＢ服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小＋１０％左右做为这个参数的值。例如：数据大小为５０Ｍ,那么给这个值分配innodb_buffer_pool_size＝６４Ｍ
innodb_additional_mem_pool=16M
作用：用来存放Innodb的内部目录
这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。
２.关于日志方面：
innodb_log_file_size=256M
作用：指定日值的大小
分配原则：几个日值成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日值上限大小为4G.一般控制在几个ＬＯＧ文件相加大小在２Ｇ以内为佳。具体情况还需要看你的事务大小，数据大小为依据。
innodb_log_files_in_group=3
作用：指定你有几个日值组。
分配原则：　一般我们可以用２－３个日值组。默认为两个。
innodb_log_buffer_size=3M
作用：事务在内存中的缓冲。
分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Ｏｒａｃｌｅ等数据库了解这个，一般最大指定为３Ｍ比较合适。
innodb_flush_logs_at_trx_commit=1
作用：控制事务的提交方式
分配原则：这个参数只有３个值，０，１，２请确认一下自已能接受的级别。默认为１，主库请不要更改了。
性能更高的可以设置为０或是２，但会丢失一秒钟的事务。
3.　文件IO分配，空间占用方面
innodb_file_per_table=1
作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。
分配原则：只有使用不使用。但ＤＢ还需要有一个公共的表空间。
innodb_file_io_threads=4
作用：文件读写ＩＯ数，这个参数只在Windows上起作用。在LINUX上只会等于４
innodb_open_files=1024
作用：限制Innodb能打开的表的数据。
分配原则：如果库里的表特别多的情况，请增加这个。这个值默认是３００。
4. 其它相关参数
这里说明一个比较重要的参数：
innodb_flush_method=O_DIRECT
作用：Ｉｎｎｏｄｂ和系统打交道的一个ＩＯ模型
分配原则：Windows不用设置。
innodb_max_dirty_pages_pct=90
作用：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.
这个参数的另一个用处：当Ｉｎｎｏｄｂ的内存分配过大，致使Ｓｗａｐ占用严重时，可以适当的减小调整这个值，使达到Ｓｗａｐ空间释放出来。建义：这个值最大在９０％，最小在１５％。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。
动态更改需要有Ｓｕｐｅｒ权限：
set global innodb_max_dirty_pages_pct=50;

附一个真实环境MySQL配置my.cnf内容，可以根据实际情况修改：

  1. [client]
  2. port = 3306
  3. socket = /tmp/mysql.sock
  4. [mysqld]
  5. user = mysql
  6. server_id = 10
  7. port = 3306
  8. socket = /tmp/mysql.sock
  9. datadir = /data/mysql/data1
  10. old_passwords = 1
  11. lower_case_table_names = 1
  12. character-set-server = utf8
  13. default-storage-engine = INNODB
  14. log-bin = bin.log
  15. log-error = error.log
  16. pid-file = /tmp/mysql.pid
  17. long_query_time = 2
  18. slow_query_log = 'on'
  19. slow_query_log_file = /data/mysql/data1/slow.log
  20. binlog_cache_size = 4M
  21. binlog_format = mixed
  22. max_binlog_cache_size = 16M
  23. max_binlog_size = 1G
  24. expire_logs_days = 30
  25. ft_min_word_len = 4
  26. back_log = 512
  27. max_allowed_packet = 64M
  28. max_connections = 4096
  29. max_connect_errors = 100
  30. join_buffer_size = 2M
  31. read_buffer_size = 2M
  32. read_rnd_buffer_size = 2M
  33. sort_buffer_size = 2M
  34. query_cache_size = 64M
  35. table_open_cache = 10000
  36. thread_cache_size = 256
  37. max_heap_table_size = 64M
  38. tmp_table_size = 64M
  39. thread_stack = 192K
  40. thread_concurrency = 24
  41. local-infile = 0
  42. skip-show-database
  43. skip-name-resolve
  44. skip-external-locking
  45. connect_timeout = 600
  46. interactive_timeout = 600
  47. wait_timeout = 600
  48. #*** MyISAM
  49. key_buffer_size = 512M
  50. bulk_insert_buffer_size = 64M
  51. myisam_sort_buffer_size = 64M
  52. myisam_max_sort_file_size = 1G
  53. myisam_repair_threads = 1
  54. concurrent_insert = 2
  55. myisam_recover
  56. #*** INNODB
  57. innodb_buffer_pool_size = 16G
  58. innodb_additional_mem_pool_size = 32M
  59. innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
  60. innodb_read_io_threads = 8
  61. innodb_write_io_threads = 8
  62. innodb_file_per_table = 1
  63. innodb_flush_log_at_trx_commit = 2
  64. innodb_lock_wait_timeout = 120
  65. innodb_log_buffer_size = 8M
  66. innodb_log_file_size = 256M
  67. innodb_log_files_in_group = 3
  68. innodb_max_dirty_pages_pct = 90
  69. innodb_thread_concurrency = 16
  70. innodb_open_files = 1024
  71. read-only
  72. relay-log = relay.log
  73. log-slave-updates
  74. [mysqld_safe]
  75. log-error=/var/log/mysqld.log
  76. pid-file=/usr/local/mysql/data/mysqld.pid
  77. replicate-do-db =all


下面是实验虚拟机得mysql参数配置 在真实企业里 以实际优化参数来配置
Master主：

  1. [client]
  2. port = 3306
  3. default-character-set=utf8 （字符集）
  4. [mysqld]
  5. datadir=/usr/local/mysql/data
  6. socket=/usr/local/mysql/mysql.sock
  7. user=mysql
  8. character-set-server=utf8
  9. symbolic-links=0
  10. open_files_limit = 102400
  11. max_connections=2000
  12. skip-name-resolve
  13. #skip-grant-tables
  14. slow_query_log = 'on'
  15. long_query_time=2 （超过2秒的记录在慢查询）
  16. slow_query_log_file=/usr/local/mysql/mysql-slow.log （慢查询日志目录）
  17. tmp_table_size=256M
  18. key_buffer_size=512M
  19. read_buffer_size=32M
  20. sort_buffer_size=32M
  21. query_cache_limit=1M
  22. query_cache_size=16M
  23. default-storage-engine=INNODB （这里只设置默认mysql引擎）
  24. innodb_buffer_pool_size=4G
  25. innodb_buffer_pool_instances=8
  26. innodb_log_file_size=10M
  27. innodb_log_buffer_size=8M
  28. innodb_lock_wait_timeout=50
  29. innodb_file_per_table=OFF
  30. innodb_flush_log_at_trx_commit=2
  31. server-id = 1
  32. log-bin=mysql-bin （开启bin-log日志）
  33. log-slave-updates
  34. binlog_cache_size = 4M
  35. binlog_format = mixed
  36. max_binlog_cache_size = 1G
  37. max_binlog_size = 1G
  38. auto_increment_offset=1
  39. auto_increment_increment=2
  40. [mysqld_safe]
  41. log-error=/var/log/mysqld.log
  42. pid-file=/usr/local/mysql/data/mysqld.pid
  43. replicate-do-db =all



slave 从：

  1. [client]
  2. port = 3306
  3. default-character-set=utf8 （字符集）
  4. [mysqld]
  5. datadir=/usr/local/mysql/data
  6. socket=/usr/local/mysql/mysql.sock
  7. user=mysql
  8. character-set-server=utf8
  9. symbolic-links=0
  10. open_files_limit = 102400
  11. max_connections=2000
  12. skip-name-resolve
  13. #skip-grant-tables
  14. slow_query_log = 'on'
  15. long_query_time=2 （超过2秒的记录在慢查询）
  16. slow_query_log_file=/usr/local/mysql/mysql-slow.log （慢查询日志目录）
  17. tmp_table_size=256M
  18. key_buffer_size=512M
  19. read_buffer_size=32M
  20. sort_buffer_size=32M
  21. query_cache_limit=1M
  22. query_cache_size=16M
  23. default-storage-engine=INNODB （这里只设置默认mysql引擎）
  24. innodb_buffer_pool_size=4G
  25. innodb_buffer_pool_instances=8
  26. innodb_log_file_size=10M
  27. innodb_log_buffer_size=8M
  28. innodb_lock_wait_timeout=50
  29. innodb_file_per_table=OFF
  30. innodb_flush_log_at_trx_commit=2
  31. server-id = 2
  32. log-bin=mysql-bin （开启bin-log日志）
  33. log-slave-updates
  34. binlog_cache_size = 4M
  35. binlog_format = mixed
  36. max_binlog_cache_size = 1G
  37. max_binlog_size = 1G
  38. auto_increment_offset=1
  39. auto_increment_increment=2
  40. [mysqld_safe]
  41. log-error=/var/log/mysqld.log
  42. pid-file=/usr/local/mysql/data/mysqld.pid
  43. replicate-do-db =all

参考