众所周知,MySQL联合索引遵循最左前缀匹配原则,虽然这在某些情况下似乎并不严格。对此有疑问的朋友,可以深入研究一下。
在创建联合索引时,建议优先选择具有高区分度的字段置于索引的第一列。那么,如何判断字段的区分度呢?可参考以下方法。
为了进行测试,我们可以创建一张测试表。统计每个字段的区分度:值越大,表示该字段的区分度越高,应优先考虑放在索引的第一列。
关于联合索引在B+树中的存储方式,许多人可能不太了解。下面我尽量通过图示来详细解释。
假设我们在(a,b)字段上创建联合索引,其存储结构大致如下所述:
叶子节点存储了全部数据,并通过顺序指针相连。数据首先按照a字段进行排序,当a字段的值相再按照b字段进行排序。
a字段的值在整个数据集中是全局有序的,例如:1、1、1、2、2、2这样的顺序。
而b字段的值则是全局无序的,例如:在a字段值相同的情况下,b字段的值可能是1、3、5、1、3、5,呈现出局部有序的状态。
在进行SQL查询时,如果where条件中没有涉及到a字段,而只使用了b字段进行筛选,那么该查询将无法有效利用索引。就如同以下情况所示:
有些文章中提到,在(a,b)两个字段上创建联合索引时,会生成两个独立的索引:(a)和(a,b)。但实际上并不是这样表述的,尽管这样的表述结果最终是正确的。
接下来,让我们通过几道与联合索引相关的经典面来检验一下大家的学习成果。
第一题:
对于下面的SQL语句,应该如何创建联合索引?
看似正确的答案是(a,b,c),但实际存在6种可能的组合(abc的排列):(a,b,c)、(a,c,b)、(b,a,c)、(b,c,a)、(c,a,b)、(c,b,a)。
MySQL优化器会调整查询条件的顺序以适应索引。在创建索引时,将区分度高的字段放在最前面会是一个很好的选择。
第二题:
对于下面的SQL语句,应该如何创建联合索引?请思考:
当联合索引遇到范围匹配时,它会停止匹配后续的索引字段。
合适的答案应该是:(a,c,b)和(c,a,b)。这样的索引设置可以让查询同时利用三个字段的索引信息,从而提高查询效率。
如何确定查询是否利用了全部三个字段的索引?有一个简单的方法就是查看执行计划的索引长度。例如,int类型的字段通常占用4个字节,三个int字段的总长度为12个字节的索引长度就表示查询利用了全部三个字段的索引。
第三题:
对于下面的SQL语句,应该怎样创建联合索引?请考虑:
in条件查询可以被转换为等值查询。这是一个可以验证的优化手段。
在平时的开发工作中,我们应尽量尝试将范围查询转换为in条件查询来获得更好的性能。