澳门十大赌场最新排名 > 澳门十大赌场 > 开发进阶篇系列,Server的优化器会缓存标量子查

原标题:开发进阶篇系列,Server的优化器会缓存标量子查

浏览次数:119 时间:2019-09-25

一.概述    

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

  (查询缓存章节未完...)

在这篇博客“ORACLE当中自定义函数性优化浅析”中,我们介绍了通过标量子查询缓存来优化函数性能: 标量子查询缓存(scalar subquery caching)会通过缓存结果减少SQL对函数(Function)的调用次数, ORACLE会在内存中构建一个哈希表来缓存标量子查询的结果。 那么SQL Server的优化器是否也会有类似这样的功能呢? 抱着这样的疑问,动手测试了一下,准备测试环境

事务(transaction)其实就是一序列数据库命令,他们可以组合在一起当做一个单一逻辑单元

 

在一个事务被标记为完成之前(commited),任何改变可以被回滚(rolled back)

 

例子:转账,它可以包括(减少发送钱账户的金额量,增加收款人的金额量,给发送方和接收方都增加交易记录)

CREATE TABLE TEST

(

   ID  INT

);

 

 

DECLARE @RowIndex INT =1;

 

WHILE @RowIndex <= 8 

BEGIN

    INSERT INTO TEST

    SELECT @RowIndex ;

    

    SET  @RowIndex = @RowIndex  1;

END

如果连接被打断,或者说一些操作失败了,所有的操作都需要被回滚

 

→ Total failure is better than partial failure

 

 

然后创建函数SLOW_FUNCTION, 本想在函数里面使用WAITFOR DELAY延迟2秒构造那种性能开销较大的函数,来模拟达到实验效果。但是标量函数里面不允许使用WAITFOR DELAY,报“Invalid use of a side-effecting operator 'WAITFOR' within a function.”

每一个事务的终止信号都是通过commit或者roll back来传达的,没有commit,我们做出的改变不会被保存

 

但其实Sesssions都是有自动提交功能(auto-commit)的,也就是任何命令都能被马上提交,不需要roll back和commit,这种auto-commit方式都是被默认的(set sutocommit = 0 关闭autocommit)

 

→ START TRANSACTION or BEGIN

CREATE  FUNCTION SLOW_FUNCTION(@p_value INT )

RETURNS INT

AS

BEGIN

    WAITFOR DELAY '00:00:00.002';

    RETURN @p_value 10;

END;

BEGIN;

 

UPDATE accounts SET balance = balance - 100.00 WHERE acc_nr = 254231426;

 

UPDATE accounts SET balance = balance 100.00 WHERE acc_nr = 676535301;

那么我就变相构造一个这样的函数,用一个循环一直延迟2秒后,标量函数才返回执行结果。

INSERT INTO transfers (acc_nr, amount, reference) VALUES (254231426, -100.00, ’Dentist’);

 

INSERT INTO transfers (acc_nr, amount, reference) VALUES (676535301, 100.00, ’John Smith’);

 

COMMIT;

 

DROP FUNCTION SLOW_FUNCTION;

GO

CREATE  FUNCTION SLOW_FUNCTION ( @p_value INT )

RETURNS INT

AS

    BEGIN

        DECLARE @dt_start DATETIME;

        DECLARE @dt_end DATETIME;

 

        SET @dt_start = GETDATE();

        SET @dt_end = DATEADD(ss, 2, GETDATE())

        WHILE @dt_start < @dt_end

            SET @dt_start = GETDATE();

 

        RETURN @p_value 10;

    END;

 

 

同时事务:

 

复杂情况:多种同时事务

 

情况1:按顺序执行事务

 

  很简单去实行,在一些情况下工作的很好

图片 3

  但是有时过慢了:

 

    CPU可以保持空闲当I/O转换过程中,反之亦然

 

    慢的语句可能会阻碍快的那个

构造出现重复数据的情况,然后测试对比,测试对比发现,在SQL Server中,优化器根本不会缓存子查询结果集。这种优化函数的技术在SQL Server中根本行不通。优化器根本没有这样的优化功能。

    事务可能需要去等待用户输入

 

情况2:事务的交错执行

 

  等待时间一半都减少特别明显了

 

  在单命令事务中也能进行工作(分裂成多种更小的操作)

TRUNCATE TABLE TEST;

  但是难以正确实施进行

GO

  这就是DBMS所做的事

INSERT INTO TEST

 

SELECT 1  UNION ALL

ACID规则:

SELECT 1  UNION ALL

ACID规则是摆正多个并行事务能被可靠处理的一组方法策略

SELECT 1  UNION ALL

Atomicity:原子性   Consistency:一致性   Isolation:交易之间不被相互作用     Durability:持久性

SELECT 2  UNION ALL

复杂的是隔离或可串行化的:确保并行执行相当于对某个顺序的顺序执行。

SELECT 2  UNION ALL

这就是并发控制的全部内容。

SELECT 2  UNION ALL

注意:为了保持DB编程的可管理性,DBMS确保串行化是至关重要的,想象一下担心其他查询会妨碍运行的任何查询!

SELECT 3  UNION ALL

 

SELECT 3;

并发控制:

 

查询被分成许多较小的操作。

图片 4

允许对这些操作进行交错。

启用并发性

 

但如果使用相同的数据,则会出现问题。

不能更新(Lost Update)

不好识别(Dirty Reads)

不能一致识别(Inconsistent Reads)

 

并发控制的目的:

允许通用的并发,但禁止危险的相互作用。

 

Lost update:

图片 5

假设有人花了50,并得到了2000,两个事务是同时发生的

语句则被分成很多带顺序的低级操作:

图片 6

下面是交错操作:

图片 7

这种方式下在账户里增加2000的效率丢失了

 

Dirty Reads

当事务读取由另一事务修改的值时,会发生Dirty Reads,但修改不是最终的。

图片 8

交错操作如下:

图片 9

返回余额0,即便已经被回滚了

 

Inconsistent Reads

不一致(不可重复)的读取 发生在在另一事务之间改变时读值多次

交错操作如下:

本文由澳门十大赌场最新排名发布于澳门十大赌场,转载请注明出处:开发进阶篇系列,Server的优化器会缓存标量子查

关键词: mg老虎机官网

上一篇:JDBC的连接mySql的基本知识,Mysql占用内存过高的优

下一篇:没有了