xml地图|网站地图|网站标签 [设为首页] [加入收藏]
MySQL客户端连接方式,统计信息更新时采样百分比
分类:数据库

  一、游标的使用并不难,游标其实就是编程中的for循环,只不过在数据库中写法不同而已。

 

  

  DECLARE My_Cursor CURSOR --创建游标读取数据

MySQL连接方式
MySQL除了最常见的TCP连接方式外,还提供SOCKET(LINUX默认连接方式)、PIPE和SHARED MEMORY连接方式。
各连接方式的服务器、客户端启动选项,及连接默认值见下表:

为什么要写统计信息

  FOR

TCP连接(Linux,Windows):
目标 启动选项 默认值
SERVER --port=PORT 3306
CLIENT --protocol=TCP --port=PORT 3306

  最近看到园子里有人写统计信息,楼主也来凑热闹。
  话说经常做数据库的,尤其是做开发的或者优化的,统计信息造成的性能问题应该说是司空见惯。
  当然解决办法也并非一成不变,“一招鲜吃遍天”的做法已经行不通了(题外话:整个时代不都是这样子吗)
  当然,还是那句话,既然写了就不能太俗套,写点不一样的,本文通过分析一个类似实际案例来解读统计信息的更新的相关问题。
  对于实际问题,不但要解决问题,更重要的是要从理论上深入分析,才能更好地驾驭数据库。  

  select id,name from  表--数据源

SOCKET连接(Linux):
目标 启动选项 默认值
SERVER --enable-named-pipe --socket=SOCKET /tmp/mysql.sock
CLIENT --protocol=SOCKET --socket=SOCKET /tmp/mysql.sock

统计信息基础

  OPEN My_Cursor; --打开游标

PIPE连接(Windows):
目标 启动选项 默认值
SERVER --enable-named-pipe --socket=SOCKET MYSQL
CLIENT --protocol=PIPE --socket=SOCKET MYSQL

首先说一个老掉牙的话题,统计信息的更新阈值:
1,表格从没有数据变成有大于等于1条数据。
2,对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后。
3,对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于500

  FETCH NEXT FROM My_Cursor INTO @id,@name   --读取第一行数据

SHARED MEMORY连接(Windows):
目标 启动选项 默认值
SERVER --shared-memory --shared-memory-base-name=MEMORY MySQL
CLIENT --protocol=MEMORY --shared-memory-base-name=MEMORY MySQL

  • (20%×表格数据总量)以后。

   WHILE @@FETCH_STATUS = 0  
    BEGIN

图片 1

      --业务处理

做个查询,触发统计信息更新,rowmodct归0(继续累积直到下一个触发的阈值,触发更新之后再次归0)

    FETCH NEXT FROM My_Cursor INTO @id,@name   --读取下一行数据,没有则会无限循环

图片 2

    END

 

    CLOSE My_Cursor; --关闭游标
   DEALLOCATE My_Cursor; --释放游标

关于统计信息“过期”的问题

  二、在业务处理的地方,可以根据条件循环吧数据加到临时表中,这样一来临时表行转列的动态列数据就可以加到临时表中了。

下面开始正文,网络上很多关于统计信息的文章,提到统计信息,很多都是统计信息过期的问题,然后跟新之后怎么怎么样
尤其在触发统计信息自动更新阈值的第三个区间:也就是说数据累计变化超过20%之后才能自动触发统计信息的更新
这一点对于大表来说通常影响是比较大的,比如1000W的表,变化超过20%也+500也就是200W+500行之后才触发统计信息更新,
这个阈值区间的自动触发阈值,绝大多数情况是不能接受的,于是对于统计信息的诊断就变成了是否“过期”

  但是要添加进去的值是参数

 

  insert into #t_Data   select  @id,@name   --这样执行会报错

图片 3

  这里就需要用到拼接了,使用Exec()方法执行。

 

  Exec('insert into #t_Data   select  '  + @id +@name) --这里只是例子@id是int类型也会报错就需要cast转换类型了。  

判断统计信息是否过期,然后通过更新统计信息来促使执行计划更加准确地预估行数,这一点本无可厚非
但是,问题也就出在这里了:那么怎么更新统计信息?一成不变的做法是否可行,这才是问题的重点。
当然肯定有人说,我就是按照默认方式更新的,更新完之后SQL也变得更加优化了什么的
通过update statistics TableName StatisticName更新某一个索引的统计信息,
或者update statistics TableName更新全表的统计信息
这种情况下往往是小表上可以这么做,当然对于大表或者小表没有一个标准值,一切要结合事实来说明问题  

 

下面开始本文的主题:

抽象并简化出业务中的一个实际案例,创建这么一张表,类似于订单和订单明细表(主子表),
这里你可以想象成是一个订单表的子表,Id字段是唯一的,有一个ParentID字段,是非唯一的,
ParentID类似于主表的Id,测试数据按照一个主表Id对应50条子表明细的规律插入数据

CREATE TABLE [dbo].[TestStaitisticsSample](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [OtherColumn] [varchar](50) NULL
) 


declare @i int=0
while(@i<100000000)
begin

    insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())
    /*
    中间插入50条,也即一个主表Id对应50条子表明细
    */
    insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())

    set @i=@i+1
end
go

create nonclustered index [idx_ParentId] ON [dbo].[TestStaitisticsSample]
(
    [ParentId] 
)
go

 

本来打算插入1亿条的,中间我让他执行我睡午觉去了,醒来之后发现SSMS挂掉了,挂掉了算了,数据也接近1亿了,能说明问题就够了
现在数据分布的非常明确,就是一个ParentId有50条数据,这一点首先要澄清。

 

测试数据写入,以及所创建完成之后来更新 idx_ParentId 索引上的统计信息,就按照默认的方式来更新,然后来观察统计信息

* *

默认方式更新统计信息(未指定采样密度)

表里现在是8000W多一点记录,默认更新统计信息时取样行数是462239行,那么这个统计信息靠谱吗?

上面说了,造数据的时候,我一个ParentId对应的是50行记录,这一点非常明确,他这里统计出来的多少?

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:MySQL客户端连接方式,统计信息更新时采样百分比

上一篇:SQL获取数据库名,mysql字符集和字符排序 下一篇:没有了
猜你喜欢
热门排行
精彩图文