sql – 统计和基数估计 – 为什么我看到这个结果？
发布时间：2020-05-28 16:42:22 所属栏目：MsSql 来源：互联网
导读：当我试图解决一个更复杂的问题时,我遇到了这个小问题,并试图将优化器弄清楚.所以,假设我有一个名为`MyTable’的表,可以像这样定义： CREATE TABLE MyTable (GroupClosuresID int identity(1,1) not null, SiteID int not null, DeleteDateTime datetime null,
|
当我试图解决一个更复杂的问题时,我遇到了这个小问题,并试图将优化器弄清楚.所以,假设我有一个名为`MyTable’的表,可以像这样定义： CREATE TABLE MyTable ( GroupClosuresID int identity(1,1) not null,SiteID int not null,DeleteDateTime datetime null,CONSTRAINT PK_MyTable PRIMARY KEY (GroupClosuresID,SiteID)) 该表中有286,685行,运行DBCC SHOW_STATISTICS(‘MyTable’,’PK_MyTable’)将产生： Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- PK_MyTable Aug 10 2011 1:00PM 286685 286685 18 0.931986 8 NO NULL 286685 (1 row(s) affected) All density Average Length Columns ------------- -------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3.743145E-06 4 GroupClosuresID 3.488149E-06 8 GroupClosuresID,SiteID (2 row(s) affected) RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ------------ ------------- ------------- -------------------- -------------- 1 0 8 0 1 129 1002 7 127 7.889764 242 826 6 112 7.375 531 2010 6 288 6.979167 717 1108 5 185 5.989189 889 822 4 171 4.807017 1401 2044 4 511 4 1763 1101 3 361 3.049861 14207 24780 1 12443 1.991481 81759 67071 1 67071 1 114457 31743 1 31743 1 117209 2047 1 2047 1 179109 61439 1 61439 1 181169 1535 1 1535 1 229410 47615 1 47615 1 235846 2047 1 2047 1 275456 39442 1 39442 1 275457 0 1 0 1 现在我在此表上运行查询,但没有创建其他索引或统计信息. SELECT GroupClosuresID FROM MyTable WHERE SiteID = 1397 AND DeleteDateTime IS NULL 现在出现两个新的统计对象,一个用于SiteID列,另一个用于DeleteDateTime列.它们分别在这里(注意：一些不相关的信息已被排除在外)： Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- _WA_Sys_00000002_7B0C223C Aug 10 2011 1:15PM 286685 216605 200 0.03384706 4 NO NULL 286685 (1 row(s) affected) All density Average Length Columns ------------- -------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0.0007380074 4 SiteID (1 row(s) affected) RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ------------ ------------- ------------- -------------------- -------------- . . . 1397 59.42782 16005.02 5 11.83174 . . . Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- _WA_Sys_00000006_7B0C223C Aug 10 2011 1:15PM 286685 216605 201 0.7447883 0.8335911 NO NULL 286685 (1 row(s) affected) All density Average Length Columns ------------- -------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0.0001065871 0.8335911 DeleteDateTime (1 row(s) affected) RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ----------------------- ------------- ------------- -------------------- -------------- NULL 0 255827 0 1 . . . （编辑：安卓应用网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |