mysql性能优化方案

《智慧政务云服务标准方案相关产品介绍》课程大纲

1. MYSQL数据库参数设置

         MYSQL需要根据所在服务器的不同调整相关参数配置，大概分为三类：基础设置、innoDB设置、其他设置，根据系统需要进行设置

1. 基本设置：
   1. 1. innodb_buffer_pool_size:

典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，40-50G(64GB内存), 100-120GB(128GB内存)。

         说明：这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。

1. 1. 1. innodb_log_file_size：4G

一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的是MySQL 5.6，你可以一开始就把它这是成4G。

         说明：这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。

         一直到MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。

         一直到MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。

1. 1. 1. max_connections: 800

         说明：如果你经常看到‘Too many connections'错误，是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接，你需要比默认的151连接数更大的值。

         max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

 

1. InnoDB配置：

1.2.1 innodb_file_per_table：ON

         说明：这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table = OFF）或者为每张表的数据单独放在一个.ibd文件（innodb_file_per_table = ON）。

                   每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是：有非常多的表（比如10k+）。

                   MySQL 5.6中，这个属性默认值是ON，因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON，因为它只对新创建的表有影响。

1.2.2 innodb_flush_log_at_trx_commit：默认为1，可以设置为2

         说明：默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。

         将它的值设置为2会导致不太可靠（reliable）因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。

         如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。

1.2.3 innodb_log_buffer_size: 32M

         说明：这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。

                   看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。

1. 其他设置：

1.3.1 query_cache_size: 默认值0

         说明：query cache（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。

         最佳选项是将其从一开始就停用，设置query_cache_size = 0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。

         如果你已经为你的应用启用了query cache并且还没有发现任何问题，query cache可能对你有用。这是如果你想停用它，那就得小心了。

1. 全局参数

1. 执行以下语句：show variables like '%sync_bin%';如果结果为1，适量改成比较大的值，

如：set global sync_binlog=500;

         可以改善磁盘I/O占用情况，比如降低磁盘I/O为100%的情况,

注意：该参数值每次重启mysql时，都会重新还原为1，暂时还没有找到不失效的设置方法

1. 开启及优化redis缓存功能：

缓存机制主要是变动频率很小或基本不变动的数据读取到内存中，减小数据库对该部分数据的频繁读取，减小数据库的压力，现有的智慧政务云平台都已经配置了redis缓存，由于现场上线的数据量都比较小，redis缓存配置默认注销状态，需要项目研发手动开启。

         注意：1、redis最好和应用服务器（tomcat）在同一台机器上，这样能提高读取及传输效率；2、一个应用服务器对应一个redis服务，不要多个应用公用一个redis，这样能防止redis存储混乱和IO占用

3、redis缓存支持绿色安装（解压后即可使用），启动后用客户端程序查询一下redis的存储库，第一次使用记得清库，防止缓存数据错乱，具体清库方法请自行百度搜索。

1. 开启系统的redis缓存功能：

项目WebContent/WEB-INF/classes/下的redis.properties记录了redis数据库相关配置信息，根据实际情况进行修改，密码一般为空，不需要设置，

redis.expire 是缓存的过期时间，一般是3600秒（一小时）

 

项目WebContent/WEB-INF/classes/下的spring-ecache.xml 将标记的注释代码放开

 

 

1. 改造基础数据查询功能

公司新系统都是基于基础研发平台上进行二次开发，基础研发平台上针对基础数据进行了redis缓存集成，现场可以扩大redis缓存的业务范围。

1. 添加新的redis缓存内容

安庆的事项数据是直接由安徽省事项梳理平台直接交换到安庆本地，相对修改比较少，可以将事项信息添加至redis缓存

例如：事项类型信息

 

 

1. 系统索引添加

用explain测试查询sql，对外键、编码、排序等添加索引，针对性创建时间、删除标识等添加联合索引，排查比较慢的sql，如果有like 不要全匹配，改like '%xxx%'为：like 'xxx%'，

使用explain + sql  排查执行的sql，看一下索引使用情况，针对类型为All的sql进行修改

1. 优化平台的流程业务性能

目前平台中的办件和流程及待办中的关联关系比较复杂，需要涉及多张表进行联合查询，导致事项和办件数据过大时，查询非常缓慢，并发情况下，查询等待甚至锁死，需优化

1. 待办任务优化

现有待办任务功能需要联合10张表数据，进行查询，在事项、办件数据达到百万级别时，特别慢，解决方法：

改造工作流业务环节办理人员表，在此表里添加三个字段，分别是权力办件主键，服务办件主键和流程任务类型，并且在窗口登记进入受理时通过该业务流程表的数据添加塞入相应的字段数据，改联合查询为，单表直接查询，具体添加方法如下：

 

 

 

1. 流程查询优化

流程查询与待办任务查询相同，但是在每完成一步审批时，工作流业务环节办理人员表都会生成下一步的数据，因为上图所列的方法是同用的底层方法，所以添加了即可；这里需要注意的是，因为查询的对象发生了改变，所以在页面显示时必然会有问题，所以通过上面截图的方法查询的对象展示的页面也需要做相应的调整：如下图，

 

当然给予的页面代码可以直接替换；

上述为这次代办任务（业务审批）的优化要点，至于详细的需要改动的地方，通过流程以及跟代码就可以找到；

1. 修改二进制存储方式

安庆的t_sys_atta表中存的有附件二进制数据，系统的t_sys_atta表是使用比较频繁的表，所有附件相关的信息都会使用该表，二进制数据读取对整个表的查询响应影响很大，建议将这个字段用单独的表进行存储。