mysql优化
MySQL数据库优化：
1、硬件优化：物理机，不用虚拟机，因为数据库是IO密集型业务。
a.CPU 一台机器8-16颗CPU。2-4颗。
b.mem 96-128G。3-4个实例。32G-64G，跑2个实例。
c.disk数量越多越好。性能：ssd（高并发） > sas（普通业务线上） > sata(线下)
raid:RAID0 > RAID10 > RAID5 > RAID1
d.网卡 多块网卡bond,以及buffer,tcp优化。
2、软件优化
操作系统：X86_64系统 （32位系统单线程4G）
软件：mysql编译优化。
3、my.cnf里参数的优化
注意：my.cnf里参数优化的幅度很小。大部分架构以及SQL语句优化。
1）innodb_buffer_pool_size = 2048M
它会把数据库的数据全部缓存到buffer_pool里面，这个配置可以配到物理内存的30%到50%，建议不要超过50%
2）sort_buffer_size=2M 排序缓冲区，线程buffer
join_buffer_size = 2M
read_buffer_size = 1M
open_files_limit = 10240打开文件描述符
3）query_cache_size = 64M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
上面三项是数据库缓存，不建议调大，因为调大效果也不佳，我们工作中是直接加memcache缓存。
4）
tmp_table_size = 256M 临时表，会占用硬盘空间，可以给大点，但是不要给太大。
max_heap_table_size = 256M
5）
long_query_time = 2 慢查询
超过2秒的查询放到 log-slow-queries=/data/3306/slow-log.log到时候可以从这个文件里查看哪些慢查询，可以交给开发，让开发去优化。
6）不要打开连接访问记录日志，不记录访问日志。只记录错误日志即可：log-error = /data/3306/error.log
7）binlog缓存时间参数expire_logs_days = 7 ，我设置为7天，如果binlog日历超过7天，那么自动去除，不要手动去清除。
磁盘空间小，我们设置为3天，磁盘空间大，我们设置为7天。
8）key_buffer_size = 32M 这是索引的缓存，主要用于 myisam引擎，如果是MyIsam引擎，我们就给大点，可以给2048M。
9）skip-name-resolve 这个参数需要加，禁止MySQL进行反向DNS解析，如果不加，经常会有权限错误。例如：没有权限的用户等。
禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。
但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
这里要介绍一下这种方法的负面作用，以及不合理的时机使用这种方法会引发的不可发现的错误。
首先，回顾一下在my.ini文件中添加"SKIP-NAME-RESOLVE"参数来提高访问速度的原理：
在没有设置该参数的时候，客户端在登陆请求发出后，服务器要解析请求者是谁，经过解析，发现登录者是从另外的电脑登录的，也就是说不是服务器本机，那么，服务器会到mysql.user表中去查找是否有这个用户，假设服务器IP是192.168.0.1，而客户机的IP是192.168.0.2；那么查询的顺序是先找'root'@'192.168.0.2'这个user是否存在，若存在，则匹配这个用户登陆，并加载权限列表。若没有该用户，则查找'root'@'%'这个用户是否存在，若存在，则加载权限列表。否则，登录失败。
在设置了SKIP-NAME-RESOLVE参数后，客户端的登录请求的解析式同上面一样的，但是在服务器本机的解析过程却发生了改变：服务器会把在本机登录的用户自动解析为'root'@'127.0.0.1'；而不是'root'@'localhost'；这样一来就坏了，因为我们在服务器上登录是为了进行一些维护操作，但是显然，'root'@'127.0.0.1'这个用户是被默认为'root'@'%'这个用户的，这个用户还没有足够得权限去执行一些超级管理员'root'@'localhost'才能执行的大作。因为未分配权限。
所以结论是：加入你在服务器本机上登录mysql服务器的话，要么先取消SKIP-NAME-RESOLVE的参数设置，重新启动服务器再登陆，设置完成后，再设置上该参数；要么就给'root'@'127.0.0.1'分配超级管理员权限，但这么做显然是不明智的，因为任何人在任何机器上都可以用这个用户执行管理员操作，前提是知道了密码。
我有一次在mysql服务器上执行数据库创建脚本，并同时创建表、触发器、存储过程等。结果，总是失败，经过了一上午的折腾，最后发现时这个参数造成我以'root'@'127.0.0.1'这个用户登陆了服务器，这个用户没有创建触发器的权限。后来，取消了SKIP-NAME-RESOLVE参数后，执行成功，再把该参数设置回去。重启。OK。
所以，在设置这个参数的时候一定要注意时机：先用超级管理员将所有的用户创建好，再将权限分配好之后，才设置这个参数生效。
案例：
kip-name-resolve 解决局域网mysql连接慢的问题
mysqluser在my.ini中添加了skip-name-resolve，会导致grant授权不成功，出现1045的错误，经查证，
问题出在设置了skip-name-resolve之后，在mysql库的user表中有一条记录是主机名是‘localhost’，
这会导致在本机授权时没法检测权限，因为skip-name-resolve就是使得mysql不会进行域名反查而得到ip，
所以解决办法就是将‘localhost’更改为127.0.0.1，问题解决
在添加skip-name-resolve前，可以先将user表中的 localhost帐户记录删除
9）innodb_data_file_path = ibdata1:1024M:autoextend
这是innodb引擎的一个数据文件，这个文件会自动扩充。不够用的时候就扩充1G
10）max_allowed_packet = 16M # 把这个设置大点，备份时会更快。。服务器和客户端之间最大能发送的可能信息包
11）max_connections = n
MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。超过限制后会报 Too many connections 错误
12）下面两个是连接超时的参数。
wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。
interactive_timeout：
服务器在关闭它前在一个交互连接上等待行动的秒数。
一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。
默认数值是28800，可以把它改为3600。
注意：上面这两个参数不要设置太大。在备份时，如果这两个参数设置的太小，那么会自动解锁。
思想：我们做监控。
监控：生产参数是一般情况下参数。
命令监控：show global status\G
调优工具：mysqlreport自动分析参数是否配置合理。
3、SQL语句的优化
a.索引优化
1）白名单机制--百度，项目开发，DBA参与，减少上线后的慢SQL数量。
抓出慢SQL
long_query_time = 2
log-slow-queries=/data/3306/slow-log.log
按天轮询：slow-log.log
2）慢查询日志分析工具---mysqlsla
mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfilter比较
3）每天晚上0点定时分析慢查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里。
DBA分析给出优化建议--->核心开发确认更改---->DAB线上操作处理。
b.大的复杂的SQL语句拆分成多个小的SQL语句。
子查询，JOIN连表查询。某个表4000万条记录。
c.数据库时存储数据的地方，但是不是计算数据的地方。
对数据计算，应用类处理，都要拿到前端应用解决。禁止在数据库上处理。
d.搜索功能，一般不要用MySQL数据库。例如：like '%老男孩%'这些查询是不走索引的，所以这些查询没法优化。
4、架构上的优化
1）业务拆分：搜索功能，like '%老男孩%'，一般不要用MySQL数据库。
2）业务拆分：某些业务应用使用nosql持久化存储，例如：memcachedb,redis,ttserver。
粉丝关注，好友关系等等。
3）数据库前端必须要加cache。例如：memcached，用户登录，商品查询。
4）动态的数据静态化。整个文件静态化，页面片段静态化。
5）数据库集群与读写分离。一主多从。通过程序或者dbproxy进行集群读写分离。
6）单表超过2000万。拆库拆表。人工拆表拆库（登录、商品、订单）
7）百度、阿里国内前三公司会这样搞。
5、流程、制度、安全优化
任何一次人为数据库记录的更新，都要走一个流程：
a.人的流程：开发-->核心开发--->运维或DBA
b.测试流程：内网测试--->IDC测试--->线上执行
c.客户端管理，PHPMYADMIN
总结：
1）全备+增量=完整的备份。
2）在哪个库上备份，就在哪个库上开启binlog.
3）全备+增量要及时的备份到备份服务器上。
4）MySQL同步也是备份（仅能防止物理宕机的恢复及热备，如果人为通过SQL删除数据库，同步无能为力了）
