【mysql 性能优化篇】性能配置

tmp_table_size：该参数用于决定内部内存临时表的最大值，每个线程都要分配（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值），如果内存临时表超出了限制，MySQL就会自动把它转化为基于磁盘的MyISAM表，优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。

现象：如果复杂的SQL语句中包含了group by/distinct等不能通过索引进行优化而使用了临时表，则会导致SQL执行时间加长。

建议：如果应用中有很多group by/distinct等语句，同时数据库有足够的内存，可以增大tmp_table_size(max_heap_table_size)的值，以此来提升查询性能。

query_cache_size：该参数用于控制MySQL query cache的内存大小。如果MySQL开启query cache，在执行每一个query的时候会先锁住query cache，然后判断是否存在query cache中，如果存在直接返回结果，如果不存在，则再进行引擎查询等操作。同时，insert、update和delete这样的操作都会将query cahce失效掉，这种失效还包括结构或者索引的任何变化，cache失效的维护代价较高，会给MySQL带来较大的压力。所以，当我们的数据库不是特别频繁更新时，query cache是比较好用的；但如果写入非常频繁，并集中在某几张表上的时候，query cache lock的锁机制会造成很频繁的锁冲突，对于这一张表的写和读会互相等待query cache lock解锁，导致select的查询效率下降。

现象：数据库中有大量的连接状态为checking query cache for query、Waiting for query cache lock、storing result in query cache。

建议：RDS默认关闭query cache功能，如果您的实例打开了query cache，当出现上述情况后可以关闭query cache。当然有些情况也可以打开query cache，以解决数据库性能问题。

案例分析

场景：某客户正在将本地的业务系统迁移上云，在RDS上运行时间明显要比线下自建数据库运行时间要慢1倍。

原因：自建数据库与RDS的参数配置不同，如下所示：

用户本地参数配置：

join_buffer_size = 128Mread_rnd_buffer_size = 128Mtmp_table_size = 128M

RDS参数配置：

join_buffer_size = 1Mread_buffer_size = 1Mtmp_table_size =256K

架构差异

RDS采用了主从复制的高可用模式，同时打开了半同步复制，半同步复制是MySQL异步复制的改进，当主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以半同步复制增加了事务的响应时间，详情如下图所示。