本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分"测试环境"。这里主要介绍MySQL优化器的主要执行流程,而不是介绍一个优化器的各个组件(这是另一个话题)。

我们知道,MySQL优化器只有两个自由度:顺序选择;单表访问方式;这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择。

explain select * from employee as A,department as B where A.LastName = 'zhou' and B.DepartmentID = A.DepartmentID and B.DepartmentName = 'TBX';

1. 可能的选择

这里看到JOIN的顺序可以是A|B或者B|A,单表访问方式也有多种,对于A表可以选择:全表扫描和索引`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID)。对于B也有三个选择:全表扫描、索引IND_D、IND_DN。

2. MySQL优化器如何做

2.1 概述

MySQL优化器主要工作包括以下几部分:Query Rewrite(包括Outer Join转换等)、const table detection、range analysis、JOIN optimization(顺序和访问方式选择)、plan refinement。这个案例从range analysis开始。

2.2 range analysis

这部分包括所有Range和index merge成本评估(参考1 参考2)。这里,等值表达式也是一个range,所以这里会评估其成本,计算出found records(表示对应的等值表达式,大概会选择出多少条记录)。

本案例中,range analysis会针对A表的条件A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分别做分析。其中:[......]

Read more

index merge的补充说明

2013-03-13 14:05  |  分类:MySQL,代码细节

在除了前面介绍的常见index merge的案例(Index Merge Union Access Algorithm)之外,还有一类很少见也比较特殊的index merge,多个索引扫描后进行交集,即 Index Merge Intersection。这类执行计划比较少见(因为MySQL需要ROR的原因),但是,在合适的场景使用,效率仍然会有很大的提示,本文将看看MySQL优化器如何评估和选择此类执行计划。MySQL手册对此只是三言两语简单介绍了一下,这里做个较为详细的说明。

这类执行计划完整名称应该是:The Index Merge Intersection Access Algorithm,下文简称Intersection

1. 为什么需要考虑Intersection

考虑如下查询:

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

优化器可以考虑使用索引key1或者key2进行REF/Range访问,如果使用key1,那么key2=1则作为过滤条件。另外,优化器还会考虑使用Intersection,即同时使用索引key1和key2。这样做可能的好处是:

(a) 如果两次索引扫描后做交集,如果最后ROWID很少,则回表次数大大减少

(b) 如果扫描这两个索引能是覆盖扫描的话,则无需回表[......]

Read more

前面以案例的形式介绍了什么是index merge,以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前,需要先理解Range访问相关的数据结构介绍:SEL_ARG结构SEL_TREE结构。[......]

Read more

MySQL源码:JOIN顺序选择的复杂度

2012-12-11 16:00  |  分类:MySQL

在看MySQL优化器代码过程中,这应该是相对较简单/代码较清晰的部分了。MySQL优化器有两个自由度:单表访问方式,多表顺序选择。前文已经介绍过MySQL单表访问的一些考量(ref/range等),本文将介绍JOIN在顺序选择上的复杂度分析。

当有多个表需要JOIN的时候,MySQL首先会处理两类特殊情况,一个是常数表,一个是由于外连接导致顺序依赖关系。前者总是放在关联的最前面,后者会在遍历的时候考虑。本文将忽略上面两点,从较宏观角度看JOIN顺序选择时候的复杂度。[......]

Read more

在开始介绍index merge/ROR优化之前,打算先介绍MySQL是如何对range/ref做成本评估的。MySQL是基于成本(cost)模型选择执行计划,在多个range,全表扫描,ref之间会选择成本最小的作为最终的执行计划。仍然强烈建议先阅读登博的slide:《查询优化浅析》,文中较为详细的介绍MySQL在range优化时成本的计算。

本文将继续介绍range/ref执行计划选择的一些不容忽略的细节。希望看客能够通过此文能够了解更多细节。[......]

Read more

登博开了一个头,希望能够往前走一点。泛读了整个MySQL Range优化的相关代码,这里将总结Range优化相关的数据结构。本文不是从宏观(High Level)角度介绍Range优化相关内容,如果看客对此感兴趣,建议绕过本文,直接阅读参考文献,相信会有收获。

已经连续写了几篇关于优化器相关的数据结构的博客了,只是希望需要的人是在需要的时候能够看到。

1. 背景知识

在开始介绍Range的主要数据结构之前,我们先看Range优化的一些概念和背景。依旧建议先阅读参考文件的[1-8],Sergey Petrunya写的PPT和文档质量都很高,很多图示,非常直观的展示了原理。

(1) 什么是Range条件? 参考Range Optimization@MySQL Manual 单列Range多列Range

(2) 给定一个KEY(key1)对应的WHERE条件,如何将其转化成一个Range,下面是"简述",详细参考单列Range

SELECT * FROM t1 WHERE (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z');

1.1 替换所有非RANGE查询为TRUE

[......]

Read more

Pages: 1 2 3 Next