mysql 开发进阶篇系列 23 应用层优化与查询缓存

一.概述　　　　

　　前面章节介绍了很多数据库的优化措施，但在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对前台的应用来进行优化，使得前台访问数据库的压力能够减到最小。
　　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%';

　　　　　　　　参数解释：

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%' 

　　　　　　参数解释：

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 数量。

　　（查询缓存章节未完...）