一.概述    

  前面章节介绍了很多数据库的优化措施,但在实际生产环境中,由于数据库服务器本身的性能局限,就必须要对前台的应用来进行优化,使得前台访问数据库的压力能够减到最小。
  1. 使用连接池   对于访问数据库来说,建立连接的代价比较昂贵,因为连接到数据库服务器需要经历多个步骤如:建立物理通道,服务器进行初次握手,分析连接字符串信息,由服务器对连接进行身份验证等。因此,有必要建立”连接池”以提高访问的性能。连接池中的连接已经预先创建好了,可以直接分配给应用层使用,减少了创建新连接所消耗的资源,连接返回后,本次访问将连接交还给”连接池”,以供新的访问使用。

(1)如果池中有空闲连接可用,返回该连接。
(2)如果池中连接都已用完,创建一个新连接添加到池中。
(3)如果池中连接已达到最大连接数,请求进入等待队列直到有空闲连接可用。

//下面以ado.net 连接数据库为例:
             //引用 System.Data.SqlClient
            //可以使用字符串connectionString来实例化SqlConnection对象
            string connectionString ="Integrated Security=False;server={0};database={1};User ID={2};Password={3};Max Pool Size=512;Connect Timeout=30";

            //也可以使用SqlConnectionStringBuilder类来实例化SqlConnection对象
            SqlConnectionStringBuilder sqlconnStringBuilder = new SqlConnectionStringBuilder();
            //连接池是否默认打开 默认为true
            sqlconnStringBuilder.Pooling = true;
            //连接池中最大连接数
            sqlconnStringBuilder.MaxPoolSize = 512;
            //连接请求等待超时时间。默认为15秒,单位为秒。
            sqlconnStringBuilder.ConnectTimeout = 30;
            sqlconnStringBuilder.DataSource = "";
            sqlconnStringBuilder.UserID = "";
            sqlconnStringBuilder.Password = "";
            //使用用户名和密码连接
            sqlconnStringBuilder.IntegratedSecurity = false;

            SqlConnection sql = new SqlConnection(connectionString);
            //or
            sql = new SqlConnection(sqlconnStringBuilder.ConnectionString);

            //用完后记得关闭当前连接
            sql.Close();

            //使用mysql一样 引用MySql.Data.dll
            MySql.Data.MySqlClient.MySqlConnection mysqlconn = new MySql.Data.MySqlClient.MySqlConnection();
            MySql.Data.MySqlClient.MySqlConnectionStringBuilder mysqlconnStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();

  2.使用查询缓存   mysql的查询缓存在4.1版本以后新增的功能,它的作用是存储select
查询的文本以及相应结果。如果随后收到一个相同的查询,服务器会从查询缓存中重新得到查询结果,而不再需要解析和执行查询。查询缓存的适用对象是更新不频繁的表,当表更改(表结构和表数据)后,查询缓存值的相关条目被清空。

--  查询缓存相关的参数
SHOW VARIABLES LIKE '%query_cache%';

图片 1

        参数解释:

have_query_cache

表示这个mysql版本是否支持查询缓存。

query_cache_limit

表示单个结果集所被允许缓存的最大值。

1048576.0/1024.0/1024.0=1.0M 默认1M,超过空间大小不被缓存。

query_cache_min_res_unit

每个被缓存的结果集要占用的最小内存。

query_cache_size

用于查询缓存的内存总大小。

1048576.0/1024.0/1024.0=1.0M 默认1M,超过空间大小不被缓存。

query_cache_type

默认关闭缓存

query_cache_wlock_invalidate

控制当有写锁加在表上的时候,是否先让该表相关的 Query Cache失效。

OFF: 是指在锁定时刻仍然允许读取该表相关的 Query Cache。

ON: 写锁定的同时将使该表相关的所有 Query Cache 失效。

-- 监视查询缓存的使用状况
SHOW STATUS LIKE 'Qcache%' 

图片 2

      参数解释:

Qcache_free_memory  

查询缓存目前剩余空间大小。

Qcache_hits          

查询缓存的命中次数。

Qcache_inserts      

查询缓存插入的次数

Qcache_free_blocks

目前还有多少剩余的blocks。FLUSH QUERY CACHE 会对缓存中的碎片进行整理,从而得到一个空闲块。这个值比较大,意味着内存碎片比较多

Qcache_lowmem_prunes 多少条Query 因为内存不足而被清除出Query Cache。缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。

Qcache_not_cached

不能被cache 的Query 的数量。不适合进行缓存查询的数量,通常是由于这些查询不是 SELECT 语句

Qcache_queries_in_cache

当前Query Cache 中cache 的Query 数量.

Qcache_total_blocks

当前Query Cache 中的block 数量。

  (查询缓存章节未完…)

1:使用数据库连接池

  • 什么是数据库连接池?

    • 数据库连接池(Connection pooling)是 程序启动时
      建立足够的数据库连接,并将这些连接组成一个连接池,由程序动态地对池中的连接进行申请,使用,释放。
  • 为什么要使用数据连接池?

    • 通过数据库连接池可以直接将某个数据连接对象分配给应用使用,由于数据连接对象是程序初始化时存放在连接池中,这时直接使用就省去了
      创建新连接 所耗费的资源。连接返回后,本次访问将连接交还给
      “连接池” 以供新的访问使用。
  • 数据库连接池运行机制

    • (1) 程序初始化时创建连接池
    • (2) 使用时向连接池申请可用连接
    • (3) 使用完毕,将连接返还给连接池
    • (4) 程序退出时,断开所有连接,并释放资源

    ##### 传统访问数据库模式

    图片 3

    image

    ##### 数据库连接池访问数据库模式

    图片 4

    image

感谢 && 参考文章

数据库连接池的理解和使用

谈谈数据库连接池的原理

PHP 设计模式系列 – 对象池模式(Object
Pool)


<!-- 数据源1 -->  
    <bean id="dataSource"  
          class="org.apache.commons.dbcp.BasicDataSource"  
          destroy-method="close">  
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>  
        <property name="url" value="jdbc:mysql://192.168.0.109:3306/test?useUnicode=true&characterEncoding=UTF-8"/>  
        <property name="username" value="root"/>  
        <property name="password" value="root"/>  
        <!--maxActive: 最大连接数量-->    
        <property name="maxActive" value="150"/>  
        <!--minIdle: 最小空闲连接-->    
        <property name="minIdle" value="5"/>  
        <!--maxIdle: 最大空闲连接-->    
        <property name="maxIdle" value="20"/>  
        <!--initialSize: 初始化连接-->    
        <property name="initialSize" value="30"/>  
        <!-- 连接被泄露时是否打印 -->  
        <property name="logAbandoned" value="true"/>  
        <!--removeAbandoned: 是否自动回收超时连接-->    
        <property name="removeAbandoned"  value="true"/>  
        <!--removeAbandonedTimeout: 超时时间(以秒数为单位)-->    
        <property name="removeAbandonedTimeout" value="10"/>  
        <!--maxWait: 超时等待时间以毫秒为单位 1000等于60秒-->  
        <property name="maxWait" value="1000"/>  
        <!-- 在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位. -->  
        <property name="timeBetweenEvictionRunsMillis" value="10000"/>  
        <!--  在每次空闲连接回收器线程(如果有)运行时检查的连接数量 -->  
        <property name="numTestsPerEvictionRun" value="10"/>  
        <!-- 1000 * 60 * 30  连接在池中保持空闲而不被空闲连接回收器线程-->  
        <property name="minEvictableIdleTimeMillis" value="10000"/>  
    <property name="validationQuery" value="SELECT NOW() FROM DUAL"/>  
    </bean>  

2:减少对MySQL的访问

  • 2.1:避免对同一数据做重复查询(减少数据库查询访问

  • 2.2:使用查询缓存(Query Cache

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

    • 查询缓存 的使用对象是 更新不频繁 的表;当表更改(数据 ||
      结构
      )后,查询缓存 值相关条目将被清空。

    • 查询缓存查看

    mysql> show variables like '%query_cache%';
    
    #开启查询缓存
    mysql> set session query_cache_type = ON;
    

    图片 5

    QUERY_CACHE参数及开启.png

  • 扩展-MySQL体系结构

图片 6

MySQL体系结构.png

2.3:增加 Cache 层(应用层实现)


tomcatde DHCP的配置

3:负载均衡(Load Balance)

  • 3.1:主从复制,读写分离

  • 3.2:使用分布式数据库架构


<Resource driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
logAbandoned="true" maxActive="20" maxIdle="2" maxWait="5000" name="system" 
removeAbandonedTimeout="60" removeAbandoned="true" 
password="xx" type="javax.sql.DataSource"
url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=base" 
username="sa"/>

4:其他优化措施

  • 4.1:对于使用MyISAM的数据表,定期执行 optimize table tableName
    操作。

    • 注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。
  • 4.2:充分使用列 存在默认值
    的事实。只有当插入值不同与默认值时,才明确插入值。因为这会减少 MySQL
    所做的 语法分析 从而提高插入速度。

  • 4.3:表的非主键字段尽量不要使用 自增长变量

《深入浅出MySQL 数据库开发 优化与管理维护
第2版》

当中的

logAbandoned="true"  removeAbandoned="true" removeAbandonedTimeout="60"

就是用来配置数据库断开后自动连接的。

数据库连接池会在启动时就建立所需的若干连接,并一直保持连接状态,
但是当数据库服务停止后,这些连接就被外部因素给中断了
网上优化了的配置信息:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
<property name="driverClassName" value="${db.driverClassName}"/>  
<property name="url" value="${db.url}"/>  
<property name="username" value="${db.username}"/>  
<property name="password" value="${db.password}"/>  
<!--initialSize: 初始化连接-->  
<property name="initialSize" value="5"/>  
<!--maxIdle: 最大空闲连接-->  
<property name="maxIdle" value="10"/>  
<!--minIdle: 最小空闲连接-->  
<property name="minIdle" value="5"/>  
<!--maxActive: 最大连接数量-->  
<property name="maxActive" value="15"/>  
<!--removeAbandoned: 是否自动回收超时连接-->  
<property name="removeAbandoned" value="true"/>  
<!--removeAbandonedTimeout: 超时时间(以秒数为单位)-->  
<property name="removeAbandonedTimeout" value="180"/>  
<!--maxWait: 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒-->  
<property name="maxWait" value="3000"/>  
<property name="validationQuery">  
<value>SELECT 1</value>  
</property>  
<property name="testOnBorrow">  
<value>true</value>  
</property>  
</bean>  

发表评论

电子邮件地址不会被公开。 必填项已用*标注