SQL多表连接查询优化相关研究.ppt

多表连接查询优化的相关研究 吕 彬 2009.3.5 拥骂游噶蹲赛搏口实蜕呀惊跪菇氖建灵葱獭丧狠绎盐牛郑敝刃恐颐洽辊唁SQL多表连接查询优化相关研究SQL多表连接查询优化相关研究 motivation 图中表示连接顺序对查询效率的影响： 准确估计选择度要考虑属性间的相关性 问题关键： 高效地计算属性间的相关度 愤蹄巡席派佃口胁挡狮枝蒙痞潜裴匀杂址恍终恫极弱胚爷厉芬董芳店芹锐SQL多表连接查询优化相关研究SQL多表连接查询优化相关研究 Agenda Multi-table join overview Heuristic and randomized optimization for the join ordering problem Michael Steinbrunn，et al, The VLDB Journal (1997) 6: 191–208 Attribute correlation detection BHUNT: Automatic Discovery of Fuzzy Algebraic Constraints in Relational Data Paul G. Brown Peter J. Haas , Proceedings of the 29th VLDB Conference, 2003 CORDS: Automatic Discovery of Correlations and Soft Functional Dependencies Ihab F. Ilyas，Volker Markl, et al, SIGMOD 2004, June 13–18, 2004, COCA: More Accurate Multidimensional Histograms out of More Accurate Correlations Detection CAO Wei1, QIN Xiongpai, WANG Shan,WAIM2008 Star join Star Gazing from atop your DB2 z/OS Database Server Terry Purcell, et al, Intelligent Optimizer Star join revisited: Performance internals for cluster architectures Josep Aguilar-Saborit, Data Knowledge Engineering 63 (2007) 995–1013 李枣磨截姚侣窒旁铃抠卖甘摈批睫箩驯蝇窥佐客臣皋缎进近隘芬忍藤呐捍SQL多表连接查询优化相关研究SQL多表连接查询优化相关研究 Heuristic and randomized optimization for the join ordering problem Choosing join type based on cost Solution space for the join ordering problem Join ordering strategies Quantitative analysis Conclusion Multi-table join overview 顷砰惹断褪羹婿改韦惹枷蔬桌闽蛊窿阁躺磁峪勿怪毡啼估淹橡冤打瞎怨豪SQL多表连接查询优化相关研究SQL多表连接查询优化相关研究 Choosing join type based on cost Cost models Nested loop join Sort-merge join Hash join Multi-table join overview 石伊琵礼淮歇醛兑尉崔棒远囱威絮拽损楼窍冒砰雅裴瘤故询袁妈管耿喝蹦SQL多表连接查询优化相关研究SQL多表连接查询优化相关研究 Solution space for the join ordering problem Left-deep trees n! ways to allocate n base relations to the tree’s leaves good solutions because of exploiting the cost-reducing pipelining technique Bushy trees an adaptable plan enumeration strategy linear graphs (n3 − n)/6 star graphs (n−1) 2n−2 Multi-table join overview 哥篱涩蘸样锤币耸愁文拆吨俱嚣禾袁屯缝哼猾弥晋暗体株丈蔬济丘亏浸懊SQL多表连接查