MySql数据库优化：应用优化和MySql Server优化

最新推荐文章于 2023-04-14 22:01:35 发布

WolfOnTheWay

最新推荐文章于 2023-04-14 22:01:35 发布

阅读量313

点赞数 1

分类专栏：数据库文章标签：数据库网络 mysql

本文链接：https://blog.csdn.net/qq_42214953/article/details/106085480

版权

数据库专栏收录该内容

12 篇文章 1 订阅

订阅专栏

1 应用优化

除了优化MySQL Server端的SQL和索引，很多时候，在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对前台的应用来进行一些优化，使得前台访问数据库的压力能够减到最小。

1.1 连接池

应用上一般访问数据库，都是先和MySQL Server创建连接，然后发送SQL语句，Server处理完成后，再把结果通过网络返回给应用，然后关闭和MySQL Server的连接，因此短时间大量的数据库访问，消耗的TCP三次握手和四次挥手所花费的时间就很大了，稍微大一点的项目，我们都会在应用访问数据库的那一层上，添加连接池模块，相当于应用和MySQL Server事先创建一组连接，当应用需要请求MySQL Server时，不需要再进行TCP连接和释放连接了，一般连接池都会维护以下资源：

连接池里面保持固定数量的活跃TCP连接，供应用使用。
如果应用瞬间访问MySQL的量比较大，那么连接池会实时创建更多的连接给应用使用。
当连接池里面的TCP连接一段时间内没有被用到，连接池会释放多余的连接资源，保留它设置的最大空闲连接量就可以了。

连接池可以自己实现，也可以用第三方写好的库。

1.2 增加Cache缓存层

比较有名的，提高数据库访问效率的缓存层当然是redis和memcache了，一般用缓存把经常访问的数据缓存起来，相当于就是提高了数据库的访问效率。

但是，添加Cache缓存层有一个非常大的问题，就是需要考虑“缓存数据的一致性”问题。

当多个事务并发读取的时候，那么先从缓存命中，如果不能命中，则去数据库中读取，再写缓存，并发读没有问题！

当多个事务进行读写的时候，事务1先写数据库，还没等更新缓存，事务2把缓存的旧值读走了，数据不一致；或者事务1写之前先把缓存删掉，再写数据库，最后写缓存，但是当事务1写数据库还没写完呢，事务2把旧值又读走了，又造成缓存数据不一致的问题！

为了保证数据的一致性，我们可以：

更新数据时：先删除缓存，再更新数据库（并发量大的时候仍会造成数据不一致）
缓存设置过期时间（从一定程度上解决数据不一致的情况，并不能完全解决）
使用队列，保证对相同id操作在同一队列中，一个操作执行完成再执行下一个操作
通过binlog来更新缓存

2 MySql Server优化

2.1 MySql查询缓存

MySQL的查询缓存是把select查询语句上一次的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询。但是当两个select查询中间出现insert，update，delete语句的时候，查询缓存就会被清空。

查询缓存适用更新不频繁的表，因为当表更新频繁的话，查询缓存也总是被清空，过多的查询缓存的数据添加和删除，就会影响MySQL的执行效率，还不如每次都从磁盘上查来得快（缓存指的就是一块内存，内存I/O比磁盘I/O快很多）。

可以在MySQL上通过以下命令，来查看查询缓存的设置：

通过show status命令，可以查看MySQL查询缓存的使用状况，如下：

那查询缓存怎么开启呢，很简单，可以通过set命令设置上面的缓存参数开启MySQL查询缓存功能，也可以找到MySQL的配置文件（windows是my.ini，linux是my.cnf），修改query_cache_type参数为1就可以了，然后重启MySQL Server就可以使用了！如下：

再配置查询缓存的大小，单位为M，如下：

修改完成，重启MySQL Server，查询缓存生效。

2.2 索引和数据缓存

主要指的就是innodb_buffer_pool_size配置项，从名字上就能看到，该配置项是针对InnoDB存储引擎起作用的，

这个参数定义了InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。innodb_buffer_pool_size是同时为数据块和索引块做缓存，这个值设得越高，访问表中数据需要的磁盘 I/O 就越少。

这个是配置文件上的配置项

这个key_buffer_size只对MyISAM存储引擎起作用，还记得吗？MyISAM存储引擎采用非聚集索引，所以它只缓存索引块！

2.3 MySql线程缓存

主要指配置文件中thread_cache_size配置项，MySQL Server网络模块采用经典的I/O复用（Java selector） +线程池模型（注意：MySQL的源码是C语言实现的，这里的网络模型就相当于是Java的NIO Selector + 线程池模型），之所以引入线程池，主要就是为了在业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的。