mysql 优化

数据库层面：

• 应用系统层面优化
  

1. SQL优化

   SQL优化一般通过分析慢查询日志来抓取长事务高消耗的sql，通过结合具体业务，对sql逻辑进行分析and精简，or重写sql。通过配置slow_query_log=1和log_queries_not_using_indexes=1启动慢查询日志记录和记录下没有使用索引的查询,后者会让慢查询日志文件很快膨胀，需要定时对文件进行切割。分析慢日志的工具一般使用pt工具包的pt-query-digest或者mysql自带的mysqldumpslow，个人比较倾向用pt,分析出来的内容比较详细。需要注意pt-query-digest其实是一个perl脚本，如果慢日志文件较大（几G多），需要大量消耗CPU资源。建议在业务低峰时候调度该工具。

2. 索引优化

   索引是数据库最为常见的对象。基本上90%的sql性能问题都是没有建索引or低效索引导致的。所以根据实际业务场景建合适的索引，能够使得sql优化事半功倍。

   索引的设计规则：选择唯一性索引or主键；为经常需要排序、分组和联合操作的字段建立索引，尽量建立复合索引而非单列索引；为常作为查询条件的字段建立索引；限制索引的数目；尽量使用数据量少的索引；尽量使用前缀来索引；删除不再使用或者很少使用的索引。

   创建索引的一些注意事项：

   
   

   （1）避免在where子句中使用!=或者<>操作符，否则引擎会放弃索引而进行全表扫描

   （2）避免在where子句中使用or来连接条件，考虑用union代替。

   （3）避免在where子句中对字段进行表达式操作或者函数操作

   （4）先应考虑在 where 及 order by 涉及的列上建立索引

   （5）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件 时才能保证系统使用该索引， 否则该索引将不被使用，并且应尽可能的让字段顺序与索引顺序相一致。即最左原则。

   （6）索引固然可以提高相应的 select 的效率，但是也需要成本去维护索引，因此表的索引个数并非越多越好，一般不要超过7个。

   （7）如果使用到了临时表，在最后将所有的临时表显式删除时，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

   （8）避免频繁创建和删除临时表，以减少系统表资源的消耗。对于一次性事件， 最好使用导出表。

   （9）最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

   （10）对于varchar的字段创建索引，指定索引长度，避免创建全字段索引，可以通过count(distinct left(列名，索引长度))/count(*)计算区分度来确定索引长度。

   （11）多表关联查询的时候，必须保证关联的字段有索引。而且字段的类型必须一致，避免由于隐式转换导致索引失效。

3. 库表优化

   举个例子：业务前期不注重表设计，导致日志、报文、图片这类数据都通过表的方式存储在数据库。而这类数据一般是通过text/blob等类型的字段存储，极易使得表容量暴增，而且很难去优化这类表的查询sql。需要做好库表设计，对图片、报文这类数据，改为通过mongodb等NOSQL数据库进行存储。或者将表的部分大字段进行拆分，单独出来一个表，通过冗余部分字段，实现表与表之间的数据关联（不建议通过mysql的外键约束实现关联，因为在高并发的情况下，会出现大量行锁影响数据库性能，强烈建议通过应用程序实现数据关联。）

   采用统一的字符集和校验集，使用innodb引擎，表设计中采用与业务无关的自增ID列作为主键，减少存储过程and自定义函数，尽量不用text/blob这类字段类型。

4. 表设计规范

   请参考附件 阿里巴巴Java开发手册 和 58到家数据库30条军规解读 （https://www.oschina.net/question/54100_2231325）

   
   

5. 数据库对象优化

• 内存配置优化

  innodb缓冲池设置：innodb_buffer_pool_size，一般为整机内存的70%~80%

  缓冲池脏页占比：innodb_max_dirty_pages_pct，默认为75%，建议按照业务场景进行设置。

  强烈建议关闭query cache。通过配置文件设置query_cache_size = 0、query_cache_type = 0即可。

  redo log缓冲区设置：innodb_log_buffer_size，如果没大事务，控制在8M-16M即可，生产环境目前配置到64M。

  
  

• IO配置优化

  
  

  sync_binlog:

  sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
  

  sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

  
  

  sync_relay_log:

  同sync_binlog参数功能一样，只不过对象是relay log而不是binlog。

  
  

  innodb_flush_log_at_trx_commit:

  如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
  如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.
  如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

  
  

  sync_master_info:

  每间隔多少事务刷新master.info，如果是table（innodb）设置无效，每个事务都会更新

  
  

  sync_relay_log_info:

  每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新

  
  

  master_info_repository:

  记录主库binlog的信息，可以设置FILE（master.info）或者TABLE（mysql.slave_master_info）

  
  

  relay_log_info_repository:

  记录备库relaylog的信息，可以设置FILE（relay-log.info）或者TABLE（mysql.slave_relay_log_info）

  
  

  innodb_io_capacity:默认为200，如果是SSD盘，建议调整到5000
  

  
  

• 高并发设置

    扩大文件描述符：

    1、动态修改,重启失效,只能使用root,并且当前session有效：ulimit -n 65535

    2、修改配置文件,永久生效，在/etc/security/limits.conf配置文件中增加：          

        * soft nofile 65535
        * soft nproc 65535
        * hard nofile 65535
        * hard nproc 65535

主机层面：

1、CPU

将其调整为性能模式，即：performance，可以参考博客：http://blog.csdn.net/myarrow/article/details/7917181/

2、内存

（1）关闭NUMA特性。NUMA陷阱现象是当你的服务器还有内存的时候，发现它已经在开始使用swap了，甚至已经导致机器出现停滞的现象。这个就有可能是由于numa的限制，如果一个进程限制它只能使用自己的numa节点的内存，那么当自身numa node内存使用光之后，就不会去使用其他numa node的内存了，会开始使用swap，甚至更糟的情况，机器没有设置swap的时候，可能会直接宕机。所以，强烈建议在操作系统层面关闭NUMA特性。直接在/etc/grub.conf的kernel行最后添加numa=off即可。

（2）尽量配置高内存。这种mysql可以充分利用内存资源缓存热块数据，避免由于内存不足导致脏数据不断地刷盘从而产生IO瓶颈，也可以避免热块数据被挤出缓存区的情况发生。

（3）修改swappiness设置。swappiness是linux的一个内核参数,用来控制物理内存交换出去的策略.它允许一个百分比的值,最小的为0,最大的为100,改值默认是60.m.swappiness设置为0表示尽量少使用swap,100表示尽量将inactive的内存页交换到swap里或者释放cache。inactive内存的意思是程序映射着，但是”长时间”不用的内存。这个值推荐设置为1,设置方法如下,在/etc/sysctl.conf文件中增加一行：vm.swappiness = 1

3、磁盘IO

（1）尽量将数据文件和日志文件分开，各自承载相应的磁盘。避免日志刷盘和数据刷盘之间争用IO。

（2）尽量使用高IO的磁盘，或者使用raid10这类磁盘阵列，或者直接采用SSD盘

4、网络优化

集群内机器最好部署在同一内网or专线直连的网络环境，以保证低延迟，高吞吐的网络环境。避免由于网络问题导致的集群内脑裂or主从复制异常的情况。

操作系统层面：

1、文件系统

    强烈建议采用xfs文件系统，ext4文件系统存在bug，触发会占用自身大部分IO，造成IO瓶颈。详见http://1057212.blog.51cto.com/1047212/1891734

    优化文件系统挂载参数：文件系统挂载参数是在/etc/fstab文件中修改,重启时候生效。noatime表示不记录访问时间,nodiratime不记录目录的访问时间。barrier=0,表示关闭barrier功能。其中nobarrier是xfs文件系统特有，ext4文件系统并无此参数。

2、IO调度算法

    NOOP:NOOP算法的全写为No Operation。该算法实现了最最简单的FIFO队列，所有IO请求大致按照先来后到的顺序进行操作。

    CFQ:CFQ算法的全写为Completely Fair Queuing。该算法的特点是按照IO请求的地址进行排序，而不是按照先来后到的顺序来进行响应。

    DEADLINE:DEADLINE在CFQ的基础上，解决了IO请求饿死的极端情况。除了CFQ本身具有的IO排序队列之外，DEADLINE额外分别为读IO和写IO提供了FIFO队列。读FIFO队列的最大等待时间为500ms，写FIFO队列的最大等待时间为5s。FIFO队列内的IO请求优先级要比CFQ队列中的高，，而读FIFO队列的优先级又比写FIFO队列的优先级高。优先级可以表示为：FIFO(Read) > FIFO(Write) > CFQ

    一般mysql服务器的磁盘IO调度算法采用deadline，既可以保证IO请求不被饿死，又可以使得读IO的处理优先级大于写IO。

系统架构层面优化

1. 负载均衡

   这里可以分为两类：

   1、PXC or mysql cluster or mysql group replication这类数据库集群，由于支持多点写入的方式，这里的负载均衡可以实现读和写都均衡负载的情况，通过在数据库前端部署haproxy或者LVS的方式实现负载均衡。但是需要强调的是，目前这类型集群在多点写入的情况很容易产生锁冲突和更新丢失的情况，一般官方建议开启单点写入。也就是说，一般也只能实现单节点承载写操作，剩余节点均衡负载读操作。

   2、mysql一主多从架构：由于主库必须单独承载写操作，故均衡负载只是针对于读操作。也是通过在数据库前端部署haproxy或者LVS的方式实现读操作负载均衡。

2. 缓存

   一般采用内存数据库如Redis、memcached同mysql结合，将热点数据放在内存数据库上实现高并发。可以参考博客：http://blog.csdn.net/stubborn_cow/article/details/50586990

3. 分布式优化

   分库分表：

   这里也可以分为2类：

   （1）通过前端应用代码逻辑实现的方式，实现表分拆的方式。这样做对应用程序的侵入性比较大，但是数据处理逻辑的过程把控在自己手上，有异常可以自主定位。

   （2）通过中间件的方式实现，目前常用的mycat、cobar实现数据分片。

   读写分离：

   一般通过数据库中间件的方式实现，常用的中间件例如：maxscale、mycat、cobar、altas等