MySQL应用优化

最新推荐文章于 2024-09-07 15:47:00 发布

bin_csdn_

最新推荐文章于 2024-09-07 15:47:00 发布

阅读量398

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/Chen_Victor/article/details/77996214

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MySQL学习笔记

一，应用优化

在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对前台的应用优化进行一些优化，使得前台访问数据库的压力能够减到最小。

二，使用连接池

对于访问数据库来说，建立连接的代价比价昂贵，因此，有必要建立连接池以提高访问的性能。连接池是一个存放连接的池子，以前需要直接访问数据库的地方，现在都改为从这个池子里面获取连接来使用。因为池子中的连接都已经预先创建好，可以直接分配给应用使用，因此大大减少了创建连接所耗费的资源。连接返回后，本次访问将连接交还给连接池，以供新的访问使用。

三，减少对MySQL的访问

1，避免对同一数据做重复检索

应用中需要理清对数据库的访问逻辑。能够一次连接就能够提取出所有结果的，就不用两次连接，这样可以大大减少对数据库无谓的重复访问。

2，使用查询缓存

MySQL的查询缓存的作用使存储select查询的文本以及相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询。

查询缓存的使用对象是更新不频繁的表，当表更改（包括表结构和表数据）后，查询缓存值的相关条目被清空。查询缓存相关的参数重要有以下几个：

mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set

have_query_cache表明服务器在安装时是否已经配置了高速缓存。
query_cache_size表明缓存区大小，单位是MB。
query_cache_type的变量值从0到2，含义分别为：0或者off（缓存关闭）、1或者on（缓存打开，使用SQL_NO_CACHE提示的select除外），2或者demand（只有带SQL_NO_CACHE的select语句提供高速缓存）。

通过show status命令，可以监视查询缓存的使用情况。
这里写图片描述

3，增加cache层

在应用中，可以在应用端增加cache层来达到减轻数据库负担的目的。cache层有很多种，也有很多种实现的方式，只要能达到降低数据库的负担又能满足应用就可以，这就需要根据应用的实际情况进行特殊处理。

比如，可以把部分数据从数据库中抽取出来放到应用端以文本方式存储，然后如果有查询需求，可以直接从这个cache中检索，由于这里的数据量小所以能够达到很高的查询效率，而且也减轻了数据库的负担。当然这种方案还涉及很多其他问题，比如如果有数据更新怎么办，多长时间刷新一次cache等，都需要更加具体应用环境进行相应的处理。

再比如用户可以在应用端建立一个二级数据库，把访问频度非常大的数据放到二级库上，然后设定一个机制与主数据库进行同步，这样用户的主要操作都在二级数据库上进行，大大地降低了主数据库的压力。

四，负载均衡

负载均衡（Load Balance）是实际应用中使用非常普遍的一种优化方式，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来减轻单台服务器的负载，达到优化的目的。负载均衡可以用在系统中的各个层面中，从前台的web服务器到中间层的应用服务器，最后到数据层的数据库服务器，都可以使用。

1，利用MySQL复制分流查询操作

利用MySQL的主从复制可以有效地分流更新操作和查询操作，具体的实现是一个主服务器承担更新操作，而多台服务器承担查询操作，主从之间通过复制实现数据的同步。多台服务器一方面用来确保可用性，一方面可以创建不同的索引以满足不同查询的需要。

对于主从之间不需要复制全部表的情况，可以通过在主服务器上搭建一个虚拟的从服务器，将需要复制到从服务器的表设置成BlackHole引擎，然后定义replicate-do-table参数只复制这些表，这样就过滤出需要复制的BINLOG，减少了传输的BINLOG的带宽。因为搭建的虚拟从服务器只起到过滤BINLOG的作用，并没有实际记录任何数据，所以对主数据库服务器的性能影响也非常有限。

通过复制来分离查询时减少主数据库负载的一个常用方法，但是这种方法也存在一些问题，最主要的问题时当主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在比较大的延迟更新，从而造成查询结果和主数据库上有所差异。因此应用在设计的时候需要有所考虑。

2，采用分布式数据库架构

分布式的数据库架构适合大数据量、负载高的情况，它具有良好的扩展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载平均，提高访问的执行效率。具体实现的时候，可以使用MySQL的CLUSTER功能或者通过用户自己编写的程序来实现全局事务。需要注意的是当前分布式事务只支持InnoDB存储引擎，因此如果自己编写程序来实现分布式架构数据库，那么就必须采用InnoDB存储引擎。