xml地图|网站地图|网站标签 [设为首页] [加入收藏]
mariadb多源主从复制错误跳过,索引阐述系列八
分类:数据库

mysql 的主从错误跳过和mariadb的多源主从复制错误跳过操作不同,请注意:
更改会话的default_master_connection变量

背景

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,因为它是一个能提供很好例子的数据库对象,并且在这个对象中能够提出问题和解决问题。

本篇我将会介绍元数据中的索引,不仅仅是因为它们本身很重要,更重要的是它们是很好的元数据类型,比如列或者分布统计,这些不是元数据中的对象。

索引对于任何关系数据库表都是必不可少的。然而,就像吐司上的黄油一样,过度使用它们可能会在数据库中产生问题。有时,可以对表进行过度索引或缺失索引,或者构建重复索引。有时问题是选择一个坏的填充因子,错误地设置ignore_dup_key选项,创建一个永远不会被使用(但必须被维护)的索引,丢失外键上的索引,或者将GUID作为主键的一部分。简而言之,任何频繁使用的数据库系统中的索引都需要定期维护和验证,而目录视图是完成这些工作的最直接的方式之一。

一.概述  

  sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。索引的统计值信息,还内置策略用来在没有索引的属性列上创建统计值。在有索引和没有索引的属性列上统计值信息会被自动维护。大部分场景下不需要手动去维护统计信息。   
  作用是 sqlserver 查询优化器使用统计信息来创建可提高查询性能的查询计划。 对于大多数查询,查询优化器已为高质量查询计划生成必要的统计信息。每个索引都会自动建立统计信息, 统计信息的准确性直接影响指令的速度,执行计划的选择是依据统计信息。

  1.1 属性列统计值
  默认情况下,每当在一个查询的where子句中使用非索引属性列时,sqlserver会自动地创建统计值,统计名称以_WA_Sys开头。

-- 查看表中非索引的统计信息
 sp_helpstats PUB_Search_Log

   如下所示:

 图片 1图片 2

  1.2 自动更新统计信息的阀值

  在自动更新统计信息选项 AUTO_UPDATE_STATISTICS 为 ON 时,查询优化器将确定统计信息何时可能过期。查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较,确定统计信息何时可能过期。
  (1)如果在评估时间统计信息时表基数为 500 或更低,则每达到 500 次修改时更新一次。
  (2)如果在评估时间统计信息时表基数大于 500,则改变每达到 500 + 20%的行数更新一次(大表特别要注意更新时间)

STOP SLAVE 'slave_account';
SET @@default_master_connection = 'slave_account';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE 'slave_account';
SET @@default_master_connection = '';

都有哪些索引可以查到?

让我们通过下面的简单语句来看一下都有哪些索引在你的数据库上,代码如下:

SELECT  convert(CHAR(50),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The Table', i.name AS index_name
FROM sys.indexes AS i
  INNER JOIN sys.tables t
    ON t.object_id=i.object_id
  WHERE is_hypothetical = 0 AND i.index_id <> 0;

结果如下:

图片 3

为什么要去引用sys.tables?这是因为它是确保只获得用户表的最简单方法。我们选择index_id 的values大于0,因为如果不为表创建集群索引,在sys中仍然有一个条目。索引,但它指向的是堆,不代表索引。每个表在sys中都有一行。索引值为0或1的索引。如果该表有一个聚集索引,则有一行数据且index_id值为1;如果该表是一个堆(这只是表示该表没有聚集索引的另一种方式),则会有一行的index_id值为0。此外,无论该表是否有聚集索引,每个非聚集索引都有一行,其index_id值大于1。我们过滤了的索引,这些索引是由数据库引擎优化顾问(DTA)创建的,目的仅仅是测试一个可能的索引是否有效。以防它们积累起来,最好把它们去掉。

如果你过一个多个指定的表,下面的这个查询是更为合理的,需要在上面的例子中增加对象的指定:

AND t.object_id = OBJECT_ID('Production.BillOfMaterials');

 

二. 统计信息分析

--查询统计信息
DBCC SHOW_STATISTICS(tablename,'indexname')

  下面是一个复杂的统计信息,上一次更新统计信息时间是2018年5月8日,距离现在有二个多月没更新了,也就是说更新条件没有达到(改变达到500次

  • 20%的行数变动)。

  图片 4

  图片 5

  2.1 统计信息三部分:头信息,字段选择性,直方图。
   (1) 头信息

    name:统计信息名称,也是索引的名字。
    updated:上一次统计信息更新时间(重要)。
    rows:上一次统计表中的行数,反映了表里的数据量。
    rows Sampled: 用于统计信息计算的抽样总行数。当表格数据比较大,为了降低消耗,只会取一小部分数据做抽样。  rows sampled<rows时候统计信息可能不是最精确的。
    steps:把数据分成几组。最多200个组,每个直方图梯级都包含一个列值范围,后跟上限列值。
    density:索引第一列前缀的选择性。查询优化器不使用此 Density, 值此值的目的是为了与 SQL Server 2008 之前的版本实现向后兼容。
    average key length:索引列平均字节数。
    string index: YES 代表字符串索引。

  (2)数据字段选择性

    all density: 反映了索引列的选择度。它反映了数据集里重复的数据量多少,如果数据很少有重复,那么它选择性就比较高。 密度为 1/非重复值。值越小选择性就越高。如果值小于了0.1,那索引的选择性就非常高了(这一点通过查看自增ID主键索引列,非常明显小于了0.1的值)。
    average length: 索引列平均字节长度 例如model 列值平均长度是25个字节。
    columns:索引列名称

  (3)直方图(对应steps 组)

      直方图度量数据集中每个非重复值的出现频率。 查询优化器根据统计信息对象第一个键列中的列值来计算直方图,它选择列值的方法是以统计方式对行进行抽样或对表或视图中的所有行执行完全扫描。
    range_hi_key: 列值也称为键值。直方图里每一组(step)数据最大值 。上图值是model字符串类型
    range_rows:每组数据区间估算数目。
    eq_rows:表中值与直方图每组数据库上限相等的数目
    distinct_range_rows:每组中非重复数目, 如果没有重复则range_rows等于distinct_range_rows值。
    avg_range_rows:每组数据区间重复值平均数目, (range_rows)

 

 三. 人工维护的几种情况

1.查询执行时间很长
  如果查询响应时间很长或不可预知,则在执行其他故障排除步骤前,确保查询具有最新的统计信息。
2.在升序或降序键列上发生插入操作。
  与查询优化器执行的统计信息更新相比,升序或降序键列(例如 IDENTITY 或实时时间戳列)上的统计信息可能要求更频繁地更新。插入操作将新值追加到升序或降序键列上
3.在维护操作后。
  考虑在执行维护过程(例如截断表或对很大百分比的行执行大容量插入)后更新统计信息。 这可以避免在将来查询等待自动统计信息更新时在查询处理中出现延迟。

-- 更新统计信息
UPDATE STATISTICS tablename(indexname)

  更新统计信息可确保查询使用最新的统计信息进行编译。 不过,更新统计信息会导致查询重新编译。 我们建议不要太频繁地更新统计信息,因为需要在改进查询计划和重新编译查询所用时间之间权衡性能。

每个表中有多少个索引,并展示他们的名字

前面的表并不特别有用,因为无法一眼看到每个表有多少索引,以及它们是什么。下面这个语句可以实现:

SELECT  convert(CHAR(20),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The_Table',
sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
coalesce(stuff(( 
     SELECT ', '+i2.name
       FROM sys.indexes i2
       WHERE t.object_ID = i2.object_ID
       ORDER BY i2.name
     FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
  FROM sys.tables AS t
  LEFT OUTER JOIN sys.indexes i
    ON t.object_id=i.object_id
      AND is_hypothetical = 0 AND i.index_id > 0 
GROUP BY t.Object_ID;

 

我在老的测试数据库上执行这个测试,对象名称比较短。

The_Table            The_Count   Index_List
-------------------- ----------- --------------------------------------------------
dbo.publishers       1           UPKCL_pubind
dbo.titles           2           titleind, UPKCL_titleidind
dbo.titleauthor      3           auidind, titleidind, UPKCL_taind
dbo.stores           1           UPK_storeid
dbo.sales            2           titleidind, UPKCL_sales
dbo.roysched         1           titleidind
dbo.discounts        0           
dbo.jobs             1           PK__jobs__6E32B6A51A14E395
dbo.pub_info         1           UPKCL_pubinfo
dbo.employee         2           employee_ind, PK_emp_id
dbo.authors          2           aunmind, UPKCL_auidind

(11 row(s) affected)

查找没有聚集索引的表

关于索引,您可以找到很多有趣的东西。例如,这里有一种快速查找表的方法,无需使用聚集索引(堆)

-- 展示所有没有聚集索引的表名称
SELECT  object_schema_name(sys.tables.object_id)+'.'
    +object_name(sys.tables.object_id) AS 'Heaps'
      FROM sys.indexes /* see whether the table is a heap */
      INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
      WHERE sys.indexes.type = 0;

每个索引中有多少行在表里面?

通过连接sys.partitions视图,我们可以计算出索引中大约有多少行。我修改了一些代码,关联了sys.extended_properties,这样可以把备注的信息带出来。

--列出每个索引/堆的行数
SELECT 
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) as 'Table',
  coalesce(i.NAME,'(IAM for heap)') as 'Index',
  Coalesce(
   (SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id
        AND s.index_id = i.index_ID    
    ), 0) 'Rows',coalesce(ep.Value,'') as comments
 FROM sys.tables t
   INNER JOIN sys.indexes i ON i.object_id = t.object_id
   LEFT OUTER JOIN sys.Extended_Properties ep
   ON i.Object_Id = ep.Major_Id AND i.Index_Id = Minor_Id AND Class = 7;

 

图片 6

然后,你可以修改这个代码,让其只是展示每个在索引表中的表有多少行。

SELECT
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) AS 'Table',  
  sum(rows) AS row_count
FROM sys.partitions p INNER JOIN sys.tables t 
   ON p.object_ID=t.object_ID 
WHERE index_id < 2 GROUP BY t.object_ID,Index_ID;

表中都有很多索引吗?

如果您对某些表具有大量索引感到怀疑,那么可以使用下面查询,该查询告诉您具有超过4个索引和索引计数超过列计数一半的表。它是一种任意选择具有大量索引的表的方法。

--超过4个索引的表 
--索引个数超过列数一半
SELECT object_schema_name(TheIndexes.Object_ID) + '.'+ object_name(TheIndexes.Object_ID) AS TableName,
       Columns, Indexes 
 FROM 
   (SELECT count(*) AS indexes, t.object_ID
      FROM sys.indexes i
      INNER JOIN sys.tables t
        ON i.object_ID=t.object_ID 
    GROUP BY t.object_ID) TheIndexes
 INNER JOIN
  (SELECT count(*) AS columns, t.object_ID
     FROM sys.columns c
       INNER JOIN sys.tables t
     ON c.object_ID=t.object_ID 
   GROUP BY t.object_ID)TheColumns
 ON TheIndexes.object_ID=TheColumns.object_ID
 WHERE indexes>columns/2 AND indexes>4;

 

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:mariadb多源主从复制错误跳过,索引阐述系列八

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文