MySQL参数优化（重点）

优化的起因：
1）网站出问题，很慢。show full processlist;
2）慢查询语句（日志文件）
long_query_time = 1
log-slow-queries = /var/lib/mysql/slow.log

案例：某公司案例:网站打开慢
数据库负载很高，load直20-30
[root@weixin-pin2 ~]# uptime
17:03:19 up 15 days, 7:11, 1 user, load average: 20, 15, 10
登录数据库查看：show full processlist;
说明：数据库中像like ‘%啊方法%’ 这样的语句特别多，导致数据库负载很高，like ‘%啊方法%’ 这样的语句对于MySQL数据库没有太大的优化余地。
优化方案思路：
1）从业务上实现用户登录后再搜索，这样减少搜索次数，从而减轻数据库服务的压力。
2）如果有大量频繁的搜索，一般是有爬虫子啊爬你的网站，分析WEB日志IP封掉
3）配置多个主从同步，程序上实现读写分离(最好让like ‘%啊方法%这样的查询去从库查)，减轻读写压力。
4）在数据库前端加上memcached缓存服务器
5）like ‘%啊方法% 的语句一般在mysql里很难优化，可以通过搜索服务Sphinx实现搜索
6）可以利用c,ruby开发程序，实现每日读库计算搜索索引，保存在服务器上提供搜索，如何，每五分钟多一个从库做一次增量。
MySQL数据库优化：
1.硬件优化：物理机
a.×64CPU 一台机器8-16颗 2-4颗
b.内存(mem) 96-128G 一台服务器 3-4个实例 32-64G，跑2个实例
c. disk 数量越多越好 性能：ssd（高并发）>sas（线上）>sata(线下)
raid 4块盘：RAID >RAID10>RAID5>RAID1
d.网卡 多块网卡bond，以及buffer，rcp优化。
软件优化：
操作系统：×86_64系统
软件：MySQL编译优化。

2.my.cnf参数优化
my.cnf优化的幅度很小。大部分架构以及SQL语句的调优。
innodb_buffer_pool_size = 4096M 配置到物理内存的二分之一或者三分之一
这个是Innodb最重要的参数，和MyISAM的key_buffer_size有相似之处，但也是有差别的。这个参数主要是缓存Innodb表的索引，数据，插入数据时的缓冲。
该参数分配内存的原则：
这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用DB服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使MySQL的查询特慢。如果你的数据比较小，那么可分配是你的数据大小+10%左右作为这个参数的值。
例如：数据大小为50M，那么给这个值分配Innodb_buffer_pool_size=64M
设置方法：在my.cnf文件里
innodb_buffer_pool_size=4G
注意：在MySQL5.7之前，调整innodb_buffer_pool_size大小必须在my.cnf配置里修改，然后重启mysql进程才可以生效。如今到了MySQL版本，就可以动态调整这个参数，方便了很多。尤其是在服务器内存增加了之后，运维人员不能粗心大意，要记得调大innodb_buffer_pool_size这个参数，数据库配置后，要检查Innodb_buffer_pool_size这个参数设置是否合理。
在调整innodb_buffer_pool_size期间用户的请求将会阻塞，知道调整完毕，所以请勿白天在白天调整，在凌晨3-4点低峰期调整。调整时，内部把数据页移动到一个新的位置，单位时块。如果想增加移动的速度，需要调整innodb_buffer_pool_chunk_size的参数的大小，默认时128M。
innodb_additional_men_pool_size = 16M
用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。通常设置16M够用了，如果表比较多，可以适当的增大。
设置方法，在my.cnf文件里：
innodb_additional_men_pool_size=16M

innodb_buffer_pool_size = 2048M
innodb_data_file_path = idbatal:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit= 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
作用：指定在一个日志组中，每个log的大小
结合innodb_buffer_pool_size设置其大小，25%-100%，避免不需要的刷新。
注意：这个值分配的大小和数据库的写入的速度，事务大小，异常重启后的恢复有很大的关系。一般取256M可以兼顾性能和recovery的速度。
分配原则：几个日志成员的大小加起来差不多和你的innodb_buffer_size相等。上限为每日志上限，大小为4G。一般控制在几个log文件相加在2G以内为最佳。具体情况还需要看你的事务大小，数据大小为依据。
设置方法：在my.cnf文件里：
innodb_log_file_size=256M
innodb_log_files_in_group = 3
作用：指定你有几个日志组
分配原则：一般我们可以用2-3个日志组，默认为两个。
设置方法：在my.cnf文件里：
innodb_log_files_in_group=3
innodb_log_buffer_size=3M
作用：事务在内存中缓冲，也就是日志缓冲区的大小，默认设置即可，具有大量事务可以考虑设置为16M，如果这个值增长过快，可以适当的增加innodb_log_buffer_size。另外如果你需要处理大量的test，或者blob字段可以考虑增加这个参数的值。
设置方法：在my,cnf文件里
innodb_log_buffer_size=3M
innodb_flush_logs_at_trx_commit
作用：控制事务的提交方式,也就是控制log的刷新到磁盘的方式。
分配原则：这个参数只有3个值（0，1，2）.默认为1，性能更高的可以设置为0或是2，这样可以适当的减少磁盘IO（但会丢失一秒钟的事务。），游戏库的MySQL建议设置为0。主库请不要更改了。
其中：
0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；
1：（默认为1）在每次事务提交的时候将logbuffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；
2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。
说明：
这个参数的设置对Ｉｎｎｏｄｂ的性能有很大的影响，所以在这里给多说明一下。
当这个值为1时：innodb 的事务LOG在每次提交后写入日志文件，并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。
当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。
当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。
从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。
设置方法：在my.cnf文件里：
innodb_flush_logs_at_trx_commit=1
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 300
read_buffer_size= 1M
open_files_limit = 10240
query_cache_size = 64M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
tmp_table_sizr = 256M
max_heap_table_size = 256M
log_query_time = 2
log_long_format
log-slow-queries = /var/lib/log/mysql/slow-log.log
relay-log=/var/lib/mysql/relay-bin
expire_logs_days = 7
key_buffer_size = 32M （索引的缓存，适用于MyISAM引擎需要给大）
skip-name-resolve 解决用户没有权限的问题
innodb_data_file_path = idbatal:1024M:axtoextend
max_allowed_packer = 16M 服务器和客户端之间最大能发送的信息包
wait_timeout: 服务器在关闭它之前在一个连接上等待行动的秒数
interactive_timeout:服务器在关闭它前在一个交互连接上等待行动的秒数。
一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。默认值28800，可以把它改成3600.
思想：
监控：生产参数是一般情况下的参数。
命令监控：show global status\G
调优工具：mysqlreport
3.SQL语句的优化
a.索引优化
1）白名单机制-百度，项目开发，DBA参与，减少上线后的慢SQL数量。
抓出慢SQL，配置my.cnf
log_query_time = 2
log-slow-queries = /var/lib/log/mysql/slow-log.log
按天轮询：slow-log.log
2）慢查询日志分析工具—mysqlsla（推荐）
mysqldumpslow，mysqlsla，myprofi，mysql-explain-slow-log，mysqllogfilter比较
3）每天晚上0点定时分析慢查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里。
DBA分析给出优化建议–>核心开发确认–>DBA线上操作处理。
b.大的复杂的SQL语句拆分成多个小的SQL语句
子查询，JOIN连表查询，某个表4000万条记录
c.数据库是存储数据的地方，但是不是计算数据的地方
对数据计算，应用类处理，都要拿到前端应用处理。禁止在数据库上处理。
d.搜搜功能，like “%发涩发%”；一般不要用MYSQL数据库
4.架构上的优化
1、业务拆分：搜搜功能，like “%发涩发%”；一般不要用MYSQL数据库
2、数据库前端必须要加cache。例如：memcached
用户登录，商品查询。
3、业务拆分：某些业务应用使用nosql持久化存储，例如：memcahcedb，redis，ttserver.
粉丝关注，好友关系等等。
4、动态的数据静态化。整个文件静态化，页面片段静态化。
5、数据库集群与读写分离。一主多从。通过程序或者dbproxy进行集群读写分离。
6、单表超过2000万。拆库拆表。
5.流程，制度，安全优化
任何一次人为数据库记录的更新，都要走一个流程：
a、人的流程：开发->核心开发->运维或DBA
b、测试的流程：内网测试->IDC测试->线上执行
c、客户端管理，PHPMYADMIN。