CBO的来龙去脉
刚开始接触数据库优化那会儿,我就一头雾水。师傅总是念叨CBO CBO,听着玄乎,但具体是他也没细说。只知道这玩意儿跟SQL执行效率息息相关,你要是不懂它,写的SQL可能就跑得贼慢。
后来自己摸索,才知道CBO就是Cost-Based Optimizer的缩写,翻译过来就是“基于成本的优化器”。这名字一听就很官方,但说白了,它就是数据库里的一个“聪明人”,专门负责帮你找出执行SQL的最佳路径。
我记得我当时遇到一个大麻烦。我们一个报表查询,平时几秒钟就出结果了,突然有一天,跑了半小时还没动静。当时整个团队都炸了,生产环境,谁都扛不住。那次逼着我去彻底弄明白CBO是怎么回事。
CBO是怎么“算账”的?
CBO的工作原理就是“算账”。它拿到你写的SQL之后,并不会马上执行。它会先生成很多种可能的执行方案,每种方案都叫一个“执行计划”。它就开始给这些计划“估价”。
它咋估价?主要看“成本”。这个成本不是钱,而是数据库认为执行这个计划需要消耗的资源,比如CPU时间、内存、磁盘I/O次数等等。哪个计划的成本最低,CBO就认为哪个是最好的,然后选择那个计划去执行。
我当时那个慢查询的问题,就是因为CBO算错账了。它手上的“数据”不对,做出了错误的判断。
- 第一步:收集统计信息。 CBO要算账,手里得有基础数据。它会去查看表里有多少行数据,某个索引的分布情况,列值的重复度等等。这些就是“统计信息”。如果这些信息过时或者压根没有,CBO就是瞎子摸象。
- 第二步:生成候选计划。 比如你查一个大表,它可能会想:我是走全表扫描?还是走某个索引?如果连了两个表,我是先查A再连B,还是先查B再连A?甚至是用嵌套循环连接(Nested Loop Join)还是哈希连接(Hash Join)?它会把这些组合都列出来。
- 第三步:评估成本。 针对每一个候选计划,CBO都会根据它手里握着的统计信息,去计算一个预期的成本值。这个值是它内部的一套算法算出来的,通常是I/O成本和CPU成本的加权组合。
- 第四步:选定最佳计划。 成本最低的那个,就是它最终决定用的执行路径。
我遇到的实战问题和解决方案
回到我那个半小时的慢查询。我发现问题出在统计信息过期了。
那张大表每天都跑批导入大量数据,但是负责收集统计信息的任务却跑得不勤快。CBO拿到的信息还是几天前的数据量,它以为表很小,就选择了全表扫描这种方式,结果实际执行的时候,发现表早就膨胀了几十倍,全表扫描直接卡死。
怎么解决的? 简单粗暴,手动强制刷新了那张表的统计信息。
ANALYZE TABLE big_report_data COMPUTE STATISTICS;
我一运行这句,CBO马上重新收集了最新的数据分布情况。当我再次执行那个查询时,CBO这回算明白了,抛弃了全表扫描,转而使用了我们定义的一个复合索引。结果,查询时间从半小时直接降到了3秒!全场欢呼。
还有一次,我们发现CBO总是无视一个非常好的索引。查了半天,才发现是因为某个字段的数据分布不均匀。比如性别字段,99%都是男,只有1%是女。如果你去查“男”的数据,CBO会觉得就算走索引,也要扫描99%的数据块,效率不如全表扫描。但如果你查“女”,索引就很有用。
为了解决这种分布不均匀的问题,我们引入了直方图(Histograms)。直方图能让CBO更细致地了解数据分布,不再是简单地认为数据是平均分布的。一旦有了直方图,CBO在遇到这种偏斜数据时,就能做出更准确的判断。
总结一下我的经验教训
想驾驭CBO,你得记住三件事:
第一,统计信息是CBO的“眼睛”。 眼睛要是瞎了,它肯定走错路。必须保证统计信息新鲜、完整。数据变化大的表,要勤快地更新统计信息。
第二,CBO不是神仙,它只是个计算器。 它只能在已有的选择里挑一个最好的。如果你的索引建得不或者SQL写得太奇葩,它也无力回天。
第三,在它算错账时,你可以“干预”。 比如使用优化器提示(Hints)。但我的经验是,能不用Hints就不用,除非你确信你比CBO更懂当前这个特定的查询场景。Hints相当于你给CBO指定了执行计划,这治标不治本,最好的办法还是让CBO自己做出正确的选择。
搞懂CBO,你就抓住了数据库优化的牛鼻子。它复杂,但逻辑清晰,只要你明白它怎么算账,大部分慢查询都能迎刃而解。

还没有评论,来说两句吧...