1语句施行陈设预估原理,使用过滤计算音讯解决

 

 

基数预估是SQL Server里一颗隐藏的宝石。一般而言,基数预估指的是,在查询编译期间,查询优化器尝试找出在执行计划里从各个运算符平均返回的行数。这个估计用来驱动计划本身生成并选择正确的计划运算符——例如像Nested Loop, Merge Join,还是Hash Join的物理连接。当这些估计错误时,查询优化器就会选择错误的计划运算符,相信我——你的查询就会非常非常非常慢!

前言

本文出处: 

前提

查询优化器使用称为统计信息对象作为基数预估。每次当你创建一个索引,SQL Server在下面也会创建一个统计对象。这个对象描述了那个索引的数据分布。另外,在查询执行时,SQL Server也能创建统计信息对象,在必须的时候(自动创建统计信息)。数据分布本身(复合索引键的第一列)被描述为所谓的直方图(Histogram)

在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。

 

      本文仅讨论SQL Server查询时,
    对于非复合统计信息,也即每个字段的统计信息只包含当前列的数据分布的情况下,
    在用多个字段进行组合查询的时候,如何根据统计信息去预估行数的。
    利用不同字段的统计信息做数据行数预估的算法原理,以及SQL Server 2012和SQL Server 2014该算法的差异情况,
    这里暂时不涉及复合统计信息,暂不涉及统计信息的更新策略及优化相关话题,以及其他SQL Server版本计算方式。   

直方图最痛苦之一就是最大只有200的步长。步长是对于你所给定列数据一部分的数据分布情况描述。你的表变得越大,你的直方图就越不准确,因为你有最大200的步长(直方图必须尽可能紧凑,它必须复核8kb的页)。

图片 1

  现实中遇到过到这么一种情况:
  在某些特殊场景下:进行查询的时候,加了TOP 1比不加TOP 1要慢(而且是慢很多)的情况,
  也就是说对于符合条件的某种的数据,查询1条(符合该条件)数据比查询所有(符合该条件)数据慢的情况,
  这种情况往往只有在某些特殊条件下会出现,那么,就有两个问题:为什么加了TOP 1 会比不加TOP 1慢?这种“特殊条件”是什么条件?
  本文将对此情况进行演示和原理分析,以及针对此种情况采用什么方法来解决。

 

在复合索引键里其他列,SQL Server在统计信息对象里用所谓的密度向量(Density Vector)来保存,它是复合索引键唯一值是如何的情况描述(彼此结合在一起)。例如在某列里有3个不同值,那列的密度向量是0.33333(1/3)。

下面分别对EXPLAIN命令结果的每一列进行说明:

 

统计信息是什么

从SQL Server 2008开始,SQL Server支持所谓的过滤统计信息(Filtered Statistics)(和过滤索引对应)。使用过滤统计信息,你可以为数据的子集创建统计信息对象。对于那个数据子集,你也会有直方图和密度向量。如果在你的数据里有极端值,你可以对那个范围的数据创建过滤统计信息对象,当那个范围的数据被查询时,就可以让查询优化器更好的估计返回的行数。因此使用过滤统计信息,你就提高了基数预估的准确性,SQL Server就会给更好的执行计划性能。下面代码显示在SQL Server 2008及后续版本里如何创建过滤统计信息对象: 

.select_type:表示SELECT的类型,常见的取值有:

按照一贯风格,先造一个测试环境:1000W 的数据
数据的特点为:
1,表中有一个状态列BusinessStatus ,这个列的分布为1,2,3,4,5
2,表中有一个 业务ID列BusinessId , BusinessId列是呈递增趋势

    简单说就是对某些字段的数据分布的一种描述,让SQL Server在根据条件做查询的时候,大概知道预期的数据大小,
    从而指导生成合理执行计划的一种数据库对象

1 CREATE STATISTICS Country_Austria ON Country(ID) 
2 WHERE Name = 'Austria' 
3 GO
类型 说明
SIMPLE 简单表,不使用表连接或子查询
PRIMARY 主查询,即外层的查询
UNION UNION中的第二个或者后面的查询语句
SUBQUERY 子查询中的第一个
CREATE TABLE TestTOP
(
    Id                INT IDENTITY(1,1) primary key,
    BusinessColumn    VARCHAR(50),
    BusinessId        INT,
    BusinessStatus    TINYINT,
    CreateDate        DATETIME
)
GO

--5年的时间,一分钟六条数据的数据频率
DECLARE @i int = 0
WHILE @i<24*60*365*5
BEGIN
    INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5 1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE())))
    INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5 1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE())))
    INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5 1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE())))
    INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5 1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE())))
    INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5 1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE())))
    INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5 1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE())))
    SET @i=@i 1
END

 

 从上面代码可以看到,你用WHERE子句限制表数据的子集,那会通过新的过滤统计信息对象来描述这些数据。但也只有的你的查询也包含这个where条件,查询优化器才可以只用这个新的统计信息对象,就像这样:

.table:输出结果集的表(表别名)

另外,在此表中查询一小部分BusinessStatus=0的分布较少的数据,且分布在最大的BusinessId上,这里暂定为5000行,利用如下脚本生成 

统计信息的分类

1 SELECT SalesAmount FROM Country
2 INNER JOIN Orders ON Country.ID = Orders.ID
3 WHERE Name = 'Austria'
4 GO

.type:表示MySQL在表中找到所需行的方式,或者叫访问类型。常见访问类型如下,从上到下,性能由差到最好:

DECLARE @i int = 15768000
WHILE @i<15768000 5000
BEGIN
    INSERT INTO TestTOP VALUES (NEWID(),@i,0, DATEADD(SS,@i,GETDATE()))
    SET @i=@i 1
END

     索引上会自动创建统计信息,SQL Server也会根据具体的查询,在某些非索引自动创建索引,当然也可以通过手动方式创建统计信息。
     先来直观地了解一下统计信息长什么样,参考截图,就是这么个样子,
     _WA_Sys_****开头的是系统根据需要创建的统计信息,
    与索引同名的是索引上创建的统计信息,
    手动创建统计信息也可以在满足SQL Server命名要求的情况下自行命名。

如果在的查询里并不包含同样的WHERE子句,查询优化期在执行计划里访问的索引的统计信息还是原来默认的。如果你对刚才的查询启用9204的跟踪标记,你就可以看到在基数预估时,那个统计信息被查询优化器使用:

ALL 全表扫描
index 索引全扫描
range 索引范围扫描
ref 非唯一索引扫描
eq_ref 唯一索引扫描
const,system 单表最多有一个匹配行
NULL 不用扫描表或索引

  

  

 1 SELECT SalesAmount FROM Country
 2 INNER JOIN Orders ON Country.ID = Orders.ID
 3 WHERE Name = 'Austria'
 4 OPTION
 5 (
 6     RECOMPILE,-- Used to see the Statistics Output
 7     QUERYTRACEON 3604,-- Redirects the output to SSMS
 8     QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 9 )
10 GO

1、type=ALL,全表扫描,MySQL遍历全表来找到匹配行

  现在这个测试环境已经搭建完成,现在创建两个非聚集索引,一个是在BusinessStatus上,一个是在BusinessId

  下面一个是索引的统计信息。

查询本身也会编译(因为RECOMPLIE查询提示,即使查询计划已被缓存),因此在SSMS的消息窗,你就可以看到拿个统计信息被用做基数预估。

一般是没有where条件或者where条件没有使用索引的查询语句

CREATE INDEX idx_BusinessStatus ON TestTOP(BusinessStatus)

CREATE INDEX idx_BusinessId on TestTOP(BusinessId)

  图片 2  

以过滤统计信息的简单介绍为基础,我想给你通过实例展示下,过滤统计信息是如何提高执行计划质量的。 

EXPLAIN SELECT * FROM customer WHERE active=0;

 

 

 1 -- Create a new database
 2 CREATE DATABASE FilteredStatistics
 3 GO
 4  
 5 -- Use it
 6 USE FilteredStatistics
 7 GO
 8  
 9 -- Create a new table
10 CREATE TABLE Country
11 (
12 ID INT PRIMARY KEY, 
13 Name VARCHAR(100)
14 ) 
15 GO
16  
17 -- Create a new table
18 CREATE TABLE Orders
19 (
20 ID INT, 
21 SalesAmount DECIMAL(18, 2)
22 ) 
23 GO

图片 3

下面开始测试:

统计信息的作用

 我们在表上建立相应的索引:

2、type=index,索引全扫描,MySQL遍历整个索引来查询匹配行,并不会扫描表

  说明:1,以下测试,不用考虑缓存之类的因素,本机测试,内存也足够大,全部缓存这么点数据还是够的。也暂不分析IO具体值,粗看执行时间已经很明显了
     2,读者要对SQL Server索引结构,统计信息,执行计划,执行计划预估等知识有一定的认识,否则很多理论上的东西就看的云里雾里
     3,本文测试数据库为SQL Server 2012,SQL Server每个版本的预估算法可能都不一样,具体环境具体分析

    查询引擎根据统计信息提供的数据做出合理的执行计划。
    那么,查询引擎究竟是怎么利用统计信息做预估的呢,
    以及下面将要提到的SQL Server 2014中较之前的版本有哪些变化?
    本文将对此两点做一个简单的分析来说明SQL Server是怎么根据统计信息做估算的,下面开始正文。

1 -- Create a Non-Clustered Index
2 CREATE NONCLUSTERED INDEX idx_Name ON Country(Name) 
3 GO
4  
5 -- Create a Clustered Index
6 CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount) 
7 GO

一般是查询的字段都有索引的查询语句

 

 

最后往2个表里插入初始数据: 

EXPLAIN SELECT store_id FROM customer;

SELECT TOP 1 比不加 TOP 1慢

    测试环境搭建

 1 -- Insert a few records into the Lookup Table
 2 INSERT INTO Country VALUES(0, 'Austria') 
 3 INSERT INTO Country VALUES(1, 'UK')
 4 INSERT INTO Country VALUES(2, 'France') 
 5 GO
 6  
 7 -- Insert uneven distributed order data
 8 INSERT INTO Orders VALUES(0, 0)
 9  
10 DECLARE @i INT = 1 
11  
12 WHILE @i <= 1000
13 BEGIN 
14 INSERT INTO Orders VALUES (1, @i) 
15 SET @i  = 1
16 END
17 GO

图片 4

 

  习惯性地做一个演示的环境,创建一个表,写入100W的数据后面测试用。

 为了保证所有的统计信息都已经是最新的,我用全扫描更新了统计信息:

3、type=range,索引范围扫描,常用于<、<=、>、>=、between等操作

  1,首先执行TOP 1 *的查询,耗时13秒

create table TestStatistics 
(
    Id int identity(1,1),
    Status1 int,
    Status2 int,
    Status3 int
)

insert into TestStatistics values (RAND()*1000,RAND()*250,RAND()*50)
go 1000000
1 -- Update the Statistics on both tables
2 UPDATE STATISTICS Country WITH FULLSCAN 
3 UPDATE STATISTICS Orders WITH FULLSCAN 
4 GO
EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;

  图片 5

表中有四个字段,第一个是自增列,主要看Status1,Status2,Status3这三个字段,
三个字段的取值都是用随机数乘以一个常量系数的出来的,
因此这三个字段的数据分布范围分别是
Status1:0-999(1000种数据分布)
Status2:0-249(250种数据分布)
Status3:0-49(50种数据分布)
这个后面有用。

点击工具栏的图片 6显示包含实际的执行计划。我们来执行下列的查询:

图片 7

   2,然后执行不加TOP 1 *的查询,也即SELECT * ,如下,耗时0秒(当然不是0秒,意思是很快就可以完成这个查询)

 

 1 SELECT SalesAmount FROM Country
 2 INNER JOIN Orders ON Country.ID = Orders.ID
 3 WHERE Name = 'UK'
 4 OPTION
 5 (
 6 RECOMPILE,-- Used to see the Statistics Output
 7     QUERYTRACEON 3604,-- Redirects the output to SSMS
 8     QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 9 )
10 GO

注意这种情况下比较的字段是需要加索引的,如果没有索引,则MySQL会进行全表扫描,如下面这种情况,create_date字段没有加索引:

    图片 8

 

图片 9

EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;

 

 

从执行计划里可以看到,基数预估出现了大问题。

图片 10

  3,上面两个查询就可以重现第一个问题了,也就是说在当前这种查询条件下,TOP 1要比不加TOP 1慢很多  

首先在SQL Server 2012中做测试

SQL Server 估计行数是501,聚集索引查找运算符的实际行数是1000。SQL Server这里使用idx_ID_SalesAmount统计信息对象的密度向量来做那个估计:密度向量是0.5(在那列我们只有2个不同值),因此估计行数是501(1001 * 0.5)。

4、type=ref,使用非唯一索引或唯一索引的前缀扫描,返回匹配某个单独值的记录行

    分析两者的执行计划:

      先做这么一个查询:select * from TestStatistics where Status1=885 and Status2=88 and Status3=8
    这个查询完成之后,表上自动创建一个三个统计信息,
    这三个统计信息分别是Status1,Status2,Status3这个三个字段的数据分布描述

当你用Austria参数值执行同样的查询,SQL Server又一次估计行数是501,但是查询本身值返回1行……当其他运算符使用这些估计做运算时,这个行为在执行计划里会有巨大的副作用。例如,Sort和Hash运算符根据这些估计作为内存授予需要的大小。如果低估,你的查询会涌向TempDb,如果高估,你就在浪费内存,当你有大量的并发查询是,就会导致竞争问题(查询内存的最大数量是有资源管理器限制的……)

store_id字段存在普通索引(非唯一索引)

    首先看加了 TOP 1 的执行计划:可以看到走的是idx_BusinessId的索引扫描

  图片 11

你可以使用过滤统计信息来帮助这些特殊场景。这个会给SQL Server关于数据本身分布的更多信息,也会在基数预估里得到帮助。对于那个特殊场景,我创建2个不同的过滤统计信息,对于每个国家我都创建各自的过滤统计信息对象: 

EXPLAIN SELECT * FROM customer WHERE store_id=10;

    图片 12

  

1 -- Fix the problem by creating Filtered Statistics Objects
2 CREATE STATISTICS Country_UK ON Country(ID) 
3 WHERE Name = 'UK'
4  
5 CREATE STATISTICS Country_Austria ON Country(ID) 
6 WHERE Name = 'Austria' 
7 GO

图片 13

    接着看不加TOP 1 的执行计划:可以看到走的是idx_BusinessStatus这个索引的索引查找

 

 现在当你重新执行查询时,最后你会看到基数预估是正确的:

ref类型还经常会出现在join操作中:

    图片 14

      首先来看一下其中这个_WA_Sys_00000002_0EA330E9,也即Status1这个列的统计信息的详细信息,
    注意All density字段值,选择性是反应一个表中该字段的重复数据有多少或者说唯一性有多少,
    计算方法是:1/表中该字段非重复个数。

图片 15

customer、payment表关联查询,关联字段customer.customer_id(主键),payment.customer_id(非唯一索引)。表关联查询时必定会有一张表进行全表扫描,此表一定是几张表中记录行数最少的表,然后再通过非唯一索引寻找其他关联表中的匹配行,以此达到表关联时扫描行数最少。

 

  

 当你在你表上上创建了过滤统计信息时,你也要注意维护。从整个表本身——如果有20%的数据改变时,SQL Server会自动更新统计信息!!! 假设你有10000行的表,你在表的子集上创建了过滤统计信息,就定子集行数是500条。在这个情况下,当指定列有2000行改变时,SQL Server会更新过滤统计信息对象。因此你要更新过滤统计信息对象里4倍的数据,才会使统计信息失效然后它被更新(在过滤统计信息区间外,没有数据发生改变)。这是很糟糕的情况,当你使用过滤统计信息时,要记住这个。

图片 16

      原因分析:

    上面说了,这个Status1这个列的取值范围是0-999,一共有1000中取值可能行,
    那么这个选择行就是1/1000=0.001,所以也是吻合这里的All density=0.001的

希望这篇文章给你过滤统计信息的很好概述,对于给出的查询,你知道如何使用过滤统计信息帮助SQL Server提高基数预估。

因为customer、payment两表中customer表的记录行数最少,所以customer表进行全表扫描,payment表通过非唯一索引寻找匹配行。

    那么为什么加了TOP 1就走BusinessId列上的索引扫描,不加TOP 1就走BusinessStatus上的索引扫描?
    因为在加了TOP 1之后,只要求返回一条数据,
    优化器认为(应该说是误认为)可以很快找到符合条件的那条记录,采用了idx_BusinessId列上的索引扫描
    由于数据的分布可知,符合BusinessStatus=0的BusinessId,是分布在BusinessId值最大的一小部分数据中,而BusinessId又是递增的,
    也就是说复合条件的数据是集中分布在idx_BusinessId索引树的一个很小的特定区域,
    采用的是与idx_BusinessId顺序一致的(ForWard顺序)索引扫描,有数据分布特点可知,一开始找到的绝大多数的BusinessId,都不是符合BusinessStatus=0的
    以至于几乎要扫描整个idx_BusinessId索引树才能找到符合BusinessStatus=0条件的数据,因此效率就会很低
    反观不加TOP 1的时候,因为是要找所有符合BusinessStatus=0的数据,优化器就索引采取了idx_BusinessStatus索引查找的方式,至此,原因大概是这样的。

  图片 17

感谢关注!

EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;

 

  

参考文章:

https://www.sqlpassion.at/archive/2013/10/29/fixing-cardinality-estimation-errors-with-filtered-statistics/

图片 18

问题到这里才刚刚开始

  照这么计算,其余两个字段的选择度分别是1/250=0.004 和1/50=0.02,分别如下截图的 All density。

5、type=eq_ref,类似ref,区别在于使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配

    如果说上述推断不足以说明问题,那么我们继续看在加了TOP 1的时候,执行计划是怎么预估的?

  图片 19       

eq_ref一般出现在多表连接时使用primary key或者unique index作为关联条件。

    继续观察加了TOP 1的时候的预估,发现此时走idx_BusinessId的索引扫描,预估行数为3154.6行,这个数字是怎么得到的?

  图片 20

film、film_text表关联查询和上一条所说的基本一致,只不过关联条件由非唯一索引变成了主键。

    图片 21

 

EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;

 

 

图片 22

    现在观察idx_BusinessStatus列上的统计信息,统计信息是100%取样的,先不考虑统计信息不准确的问题
    因为在加了TOP 1的时候,优化器认为符合条件的数据是平均分布在整个表中的,
    也就是说BusinessStatus=0的5000行数据是平均分布在15773000行数据中,查询条件又要求按照BusinessId正向排序,
    那么干脆走BusinessId列上的索引扫描,(误以为)平均找15773000/5000 行数据,就可以找到一条(TOP 1)符合条件的数据

  执行计划对数据行的预估

6、type=const/system,单表中最多有一条匹配行,查询起来非常迅速,所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理

    图片 23

  

const/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询

     实际上是不是这样子呢?用总行数处于BusinessStatus=0的行数,与预估的值比较,都是3154.6呢?那么上面的推断也就是成立的

  说完统计信息的基础问题之后,我们就可以来观察执行计划对目标数据的预估规律了。
  我们来看这么一个查询,如下,注意这个是查询的条件是参数变量,而不是直接的值,后面我会解释为什么这么做。
  来观察执行计划对数据行的预估:可以看出来,预估为4行。

根据主键primary key进行的查询:

图片 24

  图片 25

EXPLAIN SELECT * FROM customer WHERE customer_id =10;

     

  

图片 26

    这里查询加了TOP 1比不加TOP 1慢的根本原因就是如下:
      事实情况下是复合条件的数据分布是不均匀的,而优化器误以为符合条件的数据分布(在整张表中)是均匀的,
    正是因为有了这么一个矛盾,所以在加了TOP 1 的时候,优化器采用非最优化的方式造成的。

  那么这个4行是怎么计算出来的呢?

根据唯一索引unique index进行的查询:

 

  这就要利用到我们上面的选择性了,
  Status1字段的选择性是0.001,Status2的选择性是0.04,
  在SQL Server 2012中,对数据行的预估计算方式是各个字段的选择性的乘积,
  假如Pn代表不同字段的选择性,那么预估行数的计算方法就是: 预估行数=p0*p1*p2*p3……*RowCount
  因此,执行计划显示的:预估行数=0.001*0.004*总行数(也即1000000)= 4  

EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';

继续测试 TOP N    

 

图片 27

     为了证明上述推断,关于TOP的预估,我再补充一个小例子,希望各位看官能明白

说到这里解释两个可能存在的几个疑问:

图片 28

    当符合条件的数据(BusinessStatus=0)为15000行的时候,我们看看TOP 1与TOP 2,以及继续增加TOP N的值得预估的行数,就大概明白了

  第一,上述示例是用两个字段查询的,为什么不拿三个字段做演示说明? 

7、type=NULL,MySQL不用访问表或者索引,直接就能够得到结果

DECLARE @i int = 15768000
WHILE @i<15768000 15000
BEGIN
    INSERT INTO TestTOP VALUES (NEWID(),@i,0, DATEADD(SS,@i,GETDATE()))
    SET @i=@i 1
END

 首选,不管是多少个字段查询,预估行数符合上述计算方式是没有问题的,
 但是如果通过上述公式计算出来的结果非常小,在少于1的情况下,SQL Server显示预估为1行。
 按照上述计算方法,用三个字段做查询,
 预估行数=0.001*0.004*0.02*总行数(也即1000000)= 0.08<1,所以预估为1行。

图片 29

TOP 1 的预估1052.2 = 1 * RowCount/15000

     图片 30

.possible_keys: 表示查询可能使用的索引

图片 31

 

.key: 实际使用的索引

    TOP 2的预估行数 2014.4 = 2 * RowCount/15000

  第二,为什么不直接用值查询,而是用变量做查询?

.key_len: 使用索引字段的长度

    图片 32

       熟悉SQL Server的同学应该都知道,直接用变量查询的时候,SQL Server编译的时候不知道具体的参数值,
       在不知道具体参数值的情况下,它是使用字段的选择性的时候是用到一般性(或者说是平均)的值,
       也就是统计信息中整体计算出来字段的选择性,也即All density=0.001
       这里暂定认为数据分布是均匀的,也即每个值分布差别不大。
       但事实上每个值的分布的差别还有存在的,
       尤其是分布不均匀的时候,当然这个是另外一个非常大的话题了,这里暂不讨论。

.ref: 使用哪个列或常数与key一起从表中选择行。

     TOP 14 的预估行数 2014.4 = 14 * RowCount/15000

    

.rows: 扫描行的数量

     图片 33

       如果直接用明确的值做查询。
         比如 select * from TestStatistic where Status1=885 and Status2=88
         SQL Server会根据统计信息中每个字段 :Status1=885 的行数和 Status2=88行数的具体的值,
         利用上述公式做预估
         那么就继续用具体的值做演示说明,
         可以直接用where Status1=885 and Status2=88这个条件查询来观察预估结果。

.filtered: 存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例(百分比)

 

     首先我们看统计信息中Status1=885 的分布行数,1079行

.Extra: 执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息

    为什么TOP 15开始峰回路转,执行计划也变成index seek了,打破 N * RowCount/15000这个规律??请自行思考

     图片 34

最主要的有一下三种:

    优化器会根据预估返回行数,因为TOP 15的时候,预估行数 =15 * RowCount/15000 =15783.0 >15000 ,

    

Using Index 表示索引覆盖,不会回表查询
Using Where 表示进行了回表查询
Using Index Condition 表示进行了ICP优化
Using Flesort 表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果

    优化器会回头选择一种他自己的预估方式较低的方式执行,选择一个它认为代价较小(预估行数较小)的执行方式.也即idx_BusinessStatus索引的Index Seek

     然后再看统计信息中Status2=88 的分布行数,3996行

什么是ICP?

  图片 35

     图片 36

MySQL5.6引入了Index Condition Pushdown(ICP)的特性,进一步优化了查询。Pushdown表示操作下放,某些情况下的条件过滤操作下放到存储引擎。

 

    

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;

 

     利用上述公式,预估行数为4.31168行

在5.6版本之前:

什么情况下才会发生TOP 1要比不加TOP 1慢(或者慢很多)

     图片 37

优化器首先使用复合索引idx_rental_date过滤出符合条件rental_date='2005-05-25'的记录,然后根据复合索引idx_rental_date回表获取记录,最终根据条件customer_id>=300 AND customer_id<=400过滤出最后的查询结果(在服务层完成)。

    事实上,类似结构的数据分布,并非所有的情况下都会出现TOP 1比不加TOP 1慢的情况
    那么什么时候TOP 1 可以选择正确的执行计划,而非采用低效的执行计划(排序列上的索引扫描)?
    当然是跟符合条件的数据BusinessStatus=0的数据行数有关,只有符合条件的数据(BusinessStatus=0)达到一定数量之后才会发生(TOP 1比不加TOP 1慢)
    上面说了,优化器误以为符合条件的数据(BusinessStatus=0)分布是均匀的,采用了排序列上的索引扫描的执行方式,
    即便是优化器误以为符合条件的数据(BusinessStatus=0)分布是均匀的,
    采用一开始的预估算法(平均分布:总行数/符合条件的数据行数)得到一个值,与符合条件的数据的行数本身对比,如果前者较大,就不会采用排序列上的索引扫描     

  

在5.6版本之后:

    这里太拗口了也很难表达清楚,直接上例子吧。
    首先我改变符合条件(BusinessStatus=0)的数据的行数,让复合条件的数据变的少一些,
    这里删除原来的BusinessStatus=0的5000行数据,插入符合条件的数据为1000行,然后重建索引,试试看TOP 1 的效果

      那么直接利用值做查询是不是这个预估的行数呢?直接上图,完美地吻合了上述的计算方法得到的结果。

MySQL使用了ICP来进一步优化查询,在检索的时候,把条件customer_id>=300 AND customer_id<=400也推到存储引擎层完成过滤,这样能够降低不必要的IO访问。Extra为Using index condition就表示使用了ICP优化。

     图片 38

     图片 39

图片 40

    (插入之后注意重建一下BusinessStatus上的索引,得到最准确的统计信息)

     

参考

 

    第三,没有索引的情况下是符合预估的计算方法,如果创建了索引呢?

《深入浅出MySQL》

    此时再看SELECT TOP 1的查询方式,不会走排序列上的索引扫描了,走了查询条件列(idx_BusinessStatus)的索引查找,效率也上来了。

       查询条件中的各个列的统计信息是非相关的,
       如果分别在各个列上创建单个列的索引信息,在查询的时候也属于非相关统计信息。
       如截图,也就是说,虽然创建了索引,执行计划发生了变化,
       从一开始的表扫描变成了通过两个索引查找后做hash join,然后Loop join查询数据,咱不管它就是变成什么执行计划了

总结

图片 41

 图片 42       

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

    

       但是统对数据的预估还是跟上面全表扫描一样的,都是预估为4.31168,没有因为创建了索引以及执行计划发生了变化而改变(预估行数)。

您可能感兴趣的文章:

  • MySQL查询优化之explain的深入解析
  • mysql中explain用法详解
  • mysql总结之explain
  • MySQL性能分析及explain的使用说明
  • Mysql之EXPLAIN显示using filesort介绍
  • Mysql中explain作用详解
  • MYSQL explain 执行计划
  • MySQL中EXPLAIN命令详解
  • 简述Mysql Explain 命令
  • mysql优化利器之explain使用介绍

    事实上我这里说了这么多,一直在想引出一个问题,那么符合条件(BusinessStatus=0)这个数据分布多少,SELECT TOP 1不会引起问题(比不加TOP 1慢)?
    根据上述推论,这个值是动态的,大概如下:
    假如:X=总行数/符合条件数据行数,Y = 符合条件数据行数
    在统计信息完全准确的请下
    如果X>Y,也即:总行数/符合条件数据行数>符合条件数据行数,则会导致在SELECT TOP 1的时候使用排序列的索引扫描替代查询列的索引查找。
    那么这个阈值是多少?按照这种算法推论,理论上讲,就是符合条件的数据的行数等于总行数的平方根,数学推到也很简单,事实上下面也测试了。

       因为即便是创建了单列上的索引,执行计划变了,但是统计信息还是非相关的,也就是一个统计信息只描述一列字段的分布情况。

    

       图片 43 

    这个阈值在理论上是:3970行左右,

 

    图片 44

   

    那么插入符合条件的数据为3900的时候(小于阈值,也即小于总行数的平方根),SELECT TOP 1是可以走索引的,如下两个截图

然后在SQL Server 2014中做测试

     图片 45

      上述同样的数据,我这里通过link server 将上述SQL Server 2012实例下的测试表的结果导入到SQL Server 2014的实例下的表中。
      现在表结构和数据完全一致。

图片 46

  

     

    首选,做一个同样的测试,利用两个变量查询的查询条件做查询,看看SQL Server 2014预估的算法有什么变化。

     修改符合条件(BusinessStatus=0)的数据分布
     而符合条件的数据大于阈值(大于阈值,也即大于总行数的平方根,)的时候,SELECT TOP 1 就开始走排序列的索引扫描,效率开始变慢

    图片 47

       图片 48  

 

图片 49

    还记得上面在 SQL Server 2012中同样的写法,同样的数据的预估的情况吧,刚才预估的是4行,现在怎么变成63.2456行了?
    预估行数的计算公式变了吗,当然变了,这正是本文要说的重点。
    那么SQL Server 2014中是怎么预估的呢?公式是这么来的:预估行数 = P0*P11/2  * P21/4 * P31/8……* RowCount 
      那么来根据此计算方式来计算预估行数的问题:预估行数=0.001*0.0041/2*1000000 = ?
    这里我就不做开方运算了,拿来主义,直接用SQL Server来算拉倒了,SQL Server给我们提供了一个开方函数(SQRT),真JB好用。

    

    计算一下结果吧,

    事实上导致SELECT TOP 1执行计划发生变化的这个阈值,具体的数值可以弄得更加精确,可以做到大于总行数的平方根一行,或者小于总行数的平方根一行。
    但实际上测试发现,这个误差在三行左右,也就是说阈值具体的值为总行数的平方根加减三条:POWER(TableRowCount,0.5)±3左右。

    图片 50

 

       没错,是63.24555,保留四位有效数字的话就是63.2456了,预估行数跟上面计算出来的结果也是完全吻合的。

 

 

    当然也不是说“SELECT TOP 1的时候使用排序列的索引扫描替代查询列的索引查找”永远是低效的,
    想象一下,整个表中绝大多数数据是复合条件的(BusinessStatus=0)的条件下,SELECT TOP 1可以很快地找到符合条件的一条数据
     只是说,在某个阈值区间内,SQL Server查询引擎在生成执行计划的时候有一个盲区,此时查询引擎无法做出最明智的决定。

 

    实际条件是千变万化的,规律是可寻的,不能认死了规律而不考虑实际情况。

  补充测试1:

 

     同样地,用三个条件做查询,预估算法也同样复合上述公式的结果。

 

    图片 51

如何解决SELECT TOP 1比不加TOP 1慢的情况:

      按照公式来计算预估行数,选择性按照整体计算出来的选择性来,同样也是吻合的。

    上文中说了,查询加了TOP 1比不加TOP 1慢的根本原因就是如下:
      事实情况下是复合条件的数据分布是不均匀的,而优化器误以为符合条件的数据分布(在整张表中)是均匀的,
    正是因为有了这么一个矛盾,所以在加了TOP 1 的时候,优化器采用非最优化的方式造成的。

    图片 52

     

 

    此时复合条件(BusinessStatus=0)为一开始的5000行,大于上述阈值
      如果此时将查询条件列和排序列做成一个复合索引,就可以避免这种情况,
    目的是走这个索引之后,找到的第一条复合条件的数据一定是拍序列上最小的,并且不会因为找多而再次排序浪费CPU时间
    比如 create index ix_indexName on TableName(查询字段列,排序字段列),且复合索引的顺序不能改变,自己结合B树索引的结构想清楚为什么
    具体原因,就不多说了,非要说的话,合理的索引就是让优化器更加清楚地弄清楚数据分布,可以做出更加明智的选择。

   补充测试2:

    另外可以针对具体情况做filter索引,使得索引更加精确

      如果把查询条件换做具体的值,跟在SQL Server 2012中一样,SQL Server2014 也同样会根据具体的值得数据做计算
    进行这么个查询:select * from TestStatistics2014 where Status1=858 and Status2=88 
    解释一下为什么这次Status1换成858了:
    因为即便表结构,数据完全一致吧,受限于统计信息的步长(Steps)只有200,两个库的统计信息也不完全一致,统计信息不能精确到任何一个值,
    我们这里为了演示这个算法,找一个具体的RANGE_HI_KEY值,比较容易说明问题。

    图片 53

 

     

      首先看Status1=858的数据分布情况

    当然也有其他办法,比如强制索引等,但是一旦加了强制索引就屏蔽掉优化器的作用了,如果没办法保证索引实在任何时候都是比较高效的情况下,不建议加强制索引。

    图片 54

 

      再看Status2=88的数据分布情况

总结:

    图片 55

    本文分析了在某些特定的场景下,重现了SELCET TOP 1比不加TOP 1慢的场景,导致的原因分析以及解决办法。
    事实上为了简明期间,还有非常多有意思的问题尚未展开,怕是写的越多,本文的主题就凸显不出来,有机会再对此尚未展开的问题继续进行分析。
    补充一点:事实上真要是测试的话,任何一点点小小的改变,
    比如查询语句中BusinessId排序改为DESC,甚至没有BusinessId上的索引,或者聚集索引建立在其他列上
    都可以避免TOP 1比不加TOP 1慢的问题,这里的目的是为了重现TOP 1比不加TOP 1慢的现象条件和原因,以及不改变外因的情况下如何解决这一问题
    谢谢。

    

    利用上述计算方法计算出来的预估:63.27713

    图片 56

 

    执行计划的预估:63.27713,也是完全吻合的。

    图片 57

 

    补充测试3,在查询列上创建创建单独的索引

      跟SQL Server 2012中一样,执行计划发生了变化 ,但是对于数据行的预估,同样并没有因为执行计划的变化而(预估行数)变化。

      图片 58

      虽然执行计划变了,但是对数据的预估并没有变化,预估的算法还是符合:预估行数 = P0*P11/2  * P21/4 * P31/8……* RowCount 

      图片 59

  

  在此可以看出,执行计划对于(未超过统计信息范围的情况下)数据行的预估,是有一定规律的,
  这个规律就是:
  SQL Server 2012 中,预估行数=p0*p1*p2*p3……*RowCount(Pn为查询字段的选择性),
    SQL Server 2014 中,预估行数= P0*P11/2 * P21/4 * P31/8……* RowCount(Pn为查询字段的选择性)。
  当然如果说统计信息过期或者取样密度不够,那就另当别论了,这个就关系到统计信息的更新策略问题了,也是一个非常大而且非常现实的问题,暂不深入展开讨论。
  所以一开始我说暂不考虑统计信息自身是否理想,这里是在统计信息非常完整的情况下做测试的。

 

  

  微软为什么在SQL Server 2014中,对非相关且未超出统计信息范围的预估行数算法做这么一个变化,
  因为PN的值是小于1的
  预估行数的计算方法从p0*p1*p2*p3……*RowCount变化为P0*P11/2 * P21/4 * P31/8……* RowCount,显然是增加了预估行数的大小,
  同时本文未提及的另外一种情况:对于超出统计信息范围的情况下,新的预估方法也增加预估行数的大小,
  从整体上看,算法是倾向于"估多不估少”的,有这么一个改变
  至于为什么要做出这个改变?
  如果经常做SQL优化的就会发现,不少问题都是少估了预期的数据行数(因为种种原因吧,这里暂时不讨论为什么少估),
  造成执行SQL时分配的资源不够,从而拖慢了SQL的执行效率
  一个非常典型的问题就是,预估的数据比实际的数据行数小,造成比如内存授予的不够大,以及实际运算过程中采用不合理的执行计划

  个人认为,(控制在一定范围之内的)估多的情况下可以通过获取更多的系统资源来提升SQL的执行效率,
  正常情况下也不会说是跟实际值差的太离谱造成资源的浪费。
  当然也有特殊情况,那就另当别论

  

  要注意的是我这里有个前提,非相关的统计信息,不管是没有任何索引,还是是创建和单列上的索引,对应的统计信息,都属于非相关统计信息,
  如果创建复合索引(有人习惯叫组合索引),那么执行计划对于数据行的预估并不符合上述算法,具体算法我也不清楚。
  此种情况下,在SQL Server 2012和SQL Server 2014中预估算法也不一样,这个有机会再研究吧。

 

 

对于测试结果的补充说明:

  测试过程中一定要保证统计信息的完整性,以及取样的百分比问题,理性情况下都是按照100%取样的,
  中间我略去了一些细节问题,比如没此测试之前都会 update statistics TestStatistic with fullscan,保证100%取样。
  既然要精确到小数点后几位,当然要求条件是理想情况下的,目的就是一定要排除其他条件对测试结果的影响。

 

  

总结:

本文通过一个简单的示例,来了解了SQL Server通过统计信息对数据预估的计算方式和原理,以及SQL Server 2012和SQL Server2014之间的差异。
统计信息对于SQL执行计划的选择起着中枢神经般的作用,不光是在SQL Server数据库中,包括其他关系数据库,统计信息都是一个非常重要的数据库对象。
可以说,SQL优化,统计信息以及与之息息相关的执行计划是一个非常重要的因素,了解统计信息方面的知识对性能调优有着非常重要的作用。

在涉及到组合索引上的统计信息情况下,执行计划对数据行的预估,SQL Server2012和SQL Server 2014中也不一样,问题将会更加有趣,待有时间再写吧。

 

 参考:Fanr_Zh 大神的  

          以及 http://msdn.microsoft.com/en-us/library/dn673537.aspx 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:1语句施行陈设预估原理,使用过滤计算音讯解决

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。