本文首发于个人的知识星球，参考了网络上各类文章，感谢这些朋友们留下的宝贵材料和分享，让每个热爱 SQL 的人学到了更加深入的知识。本篇足足有 10945 字左右，大概需要花费半小时，感谢你的阅读！
以下是正文
SQL Server 的优化器是基于成本计算的，高质量的执行计划来自于对成本的准确估算。而整个计划成本的估算，则是基于对每一步操作或实现操作的每个算法的开销估算。
优化器总在寻找最优的计划，但无论计划是最优还是次优，最终的表现形式都是一棵语法树，挂满了各种操作符，用来从数据库结构中抓取相应的数据。随着查询涉及的表越多，可能的 Join 组合，操作逻辑组合也越多，要想穷尽这些组合，并合理评估计划的成本，显然是不合理的。优化器的作用就是在合理的时间范围内，找到可用的最优执行计划。
Statistics 帮助优化器实现基数估算（cardinality estimation)的各个方面，由三部分信息组成：柱状图，密度，字符统计（histogram, density, string statistics). 在接下来的文章中，我们将探讨如何建立 statistics 以及维护他们，如何找到哪些因为 statistics 不准确导致计划运行低效的查询。最后我们将一窥成本估算公式，它是如何估算每个操作的 I/O 以及 CPU 成本开销。
Statistics
Statistics 用来帮助优化器完成基数估算（cardinality estimation). 一次基数估算，算的是各种操作符，比如筛选，Join 条件，Group By 等返回的记录数，也可以称之为命中率（selectivity). 比如 ProductType 字段有三个可能的值：Phone, Pad, Laptop, 那么我们在此字段上加上一个条件筛选， ProductType='Phone'，那么理想中的返回记录数应当是整个数据集的 1/3, 而实际中我们知道这概率是很小的，整个结果集的分布并不是正态分散的，取决于真实的情况。有可能 200 万条数据中 ，Phone 占了 150 万，Pad 占 20 万，Laptop 占 30 万，这样 selectivity 就分别是 150/200, 20/200, 30/200 了。这很明显，数据是有倾斜的。
创建和更新 statistics
有很多种方法可以建立 statistics, 比如当 AUTO_CREATE_STATISTICS 设置为 on 的时候，查询优化器会自动创建；当有新的索引创建时；也可以由用户输入命令 CREATE STATISTICS 显式创建。
Statistics 可以以单列，或者多列来创建。比如查询优化器只创建单列的 statistics, 索引或者显式命令则可以创建多列 statistics，当然也包括单列。由此可见，statistics 是数据库的对象，而不是依附于表或索引的属性。histogram, string statistics 都只为 statistics 的第一个字段而建，string statistics 还有个特点就是字段类型必须是 string 才能有效创建。Density 则会为组成的顺序列组合创建，假设 statistics 的列组合为 ProductID, SalesOrderID, SalesOrderDetailID, 那么 Density 则会为 ProductID, ProductID+ SalesOrderID, ProductID+ SalesOrderID+ SalesOrderDetailID都建立一组 Density.
statistics 的更新最有异议的地方在于，这些更新是否能自动完成。在有着频繁事务的数据库中，更新数据已经非常吃力了，还要自动更新 statistics 无疑带来更大的压力。而不更新 statistics 则会导致优化器生成不了最优的执行计划。所以如果是我来设计，也不会允许数据库自动更新 statistics, 或者留一个开关给用户，开不开启自动 statistics 更新交由他们去选择。
当 AUTO_UPDATE_STATISTICS 设置为 ON 的时候，无论是优化器隐式创建的，还是 CREATE STATISTICS 显示创建的 statistics 都会被自动更新。如果想要更自主的去控制更新窗口，也可以使用命令 UPDATE STATISTICS. 值得注意的是，rebuild index 的时候，也会自动更新在索引上面已经建立的 statistics.
当表或索引有新建的 statistics 或者 statistics 被更新的时候，原本涉及到这些表或索引的缓存起来的执行计划，都会被统统释放掉。由优化器重新根据新的 statistics 来新建一个执行计划。
对大数据量的表做 statistics 建立与更新都是件非常复杂的事情。如果需要实时更新 statistics 那就更加困难了，势必给 IO 带来很大的压力。因此 SQL Server 优化器总是使用表的抽样值来建立和更新 statistics. 这个抽样的数据量大小最小是 8MB， 不到这个数字就按表的实际数据量来算。当然，表越大，抽样的数据量也越大。
我们都知道，在统计学中，一旦进行了抽样，肯定会有失真。要保证绝对优质的 statistics, 我们需要更大量或者甚至全量来创建和更新它，比如在 CREATE/UPDATE STATISTICS 带上一个抽样值或者 WITH FULLSCAN 来做全量抽取。在新建一个索引时，必然会扫描整张表，那么由此新建的 statistics 也相当于是用了 WITH FULLSCAN，因此这份 statistics 绝对含有全量的数据分布统计。当 AUTO_UPDATE_STATISTICS 设置为 ON 的时候，原本全量统计的 statistics 可能会被优化器用抽样的方式自动置回去，导致 statistics 并不准确。因此是否自动更新 statistics 也是个值得商榷的事情。
默认情况下，查询优化器会等待 statistics 的自动更新完成，基于最新的 statistics 生成执行计划。这意味着在 statistics 没有自动更新完成前，查询是被优化器给阻塞住了。现在的高版本 SQL Server（2005 及以后） 带了一个特性 AUTO_UPDATE_STATISTICS_ASYNC, 在查询生成执行计划和 statistics 更新的同时，优先让位于查询生成执行计划，把 statistics 的更新延迟一会。
那么是不是查询一结束，被延迟的 statistics 更新就马上开始了呢。并不是！在 SQL Server 的内部，为每张表的每个字段定义了一个计数器（counter), 叫做 colmodctr. 这个计数器用来记录字段更新的次数（column modification counter). 有个简单的公式给 SQL Server 来判断 statistics 是否过期：
colmodctr > 500 + 20%*n
当表中的数据大于 500 行，且 statistics 组合字段的 colmodctr 数量大于 500 加总数的 20% 时，statistics 就被判断为过期。n 在这里是指整张表的数据量。colmodctr 通过 DAC(dedicated administrator connection) 连接服务器，查看 sys.sysrscols 就可见了。
观察 statistics 对象
最低0.47元/天 解锁文章
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
