jieye の 数字花园

Search

Search IconIcon to open search

mysql联合索引

Last updated Unknown

# Mysql 联合索引

# Mysql 联合索引

1
2
3
4
5
6
7
8
9
(1) select * from myTest where a=3 and b=5 and c=4; ---- abc顺序 abc三个索引都在where条件里面用到了,而且都发挥了作用 
(2) select * from myTest where c=4 and b=6 and a=3; where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样 
(3) select * from myTest where a=3 and c=7; a用到索引b没有用,所以c是没有用到索引效果的 
(4) select * from myTest where a=3 and b>7 and c=3; ---- b范围值,断点,阻塞了c的索引 a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引 
(5) select * from myTest where b=3 and c=4; --- 联合索引必须按照顺序使用,并且需要全部使用 因为a索引没有使用,所以这里 bc都没有用上索引效果 
(6) select * from myTest where a>4 and b=7 and c=9; a用到了 b没有使用c没有使用 
(7) select * from myTest where a=3 order by b; a用到了索引b在结果排序中也用到了索引的效果a下面任意一段的b是排好序的 
(8) select * from myTest where a=3 order by c; a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort 
(9) select * from mytable where b=3 order by a; b没有用到索引,排序中a也没有发挥索引效果

联合索引要遵循最左匹配才能走索引,但是如果数据库表中的字段都是索引的话,即使查询过程中,没有遵循最左匹配原则,也是走全扫描二级索引树 (type=index)

# Where a>1 and b=2

select * from t_table where a > 1 and b = 2,联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?

由于联合索引(二级索引)是先按照 a 字段的值排序的,所以符合 a > 1 条件的二级索引记录肯定是相邻,于是在进行索引扫描的时候,可以定位到符合 a > 1 条件的第一条记录,然后沿着记录所在的链表向后扫描,直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合 a > 1 条件的二级索引记录的范围里,b 字段的值是无序的。比如前面图的联合索引的 B+ Tree 里,下面这三条记录的 a 字段的值都符合 a > 1 查询条件,而 b 字段的值是无序的:

因此,我们不能根据查询条件 b = 2 来进一步减少需要扫描的记录数量(b 字段无法利用联合索引进行索引查询的意思)。

所以在执行 Q1 这条查询语句的时候,对应的扫描区间是 (2, + ∞),形成该扫描区间的边界条件是 a > 1,与 b = 2 无关。

因此,Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询,而 b 字段并没有使用到联合索引

# Where a>=1 and b=2

如果改成 where a >= 1,由于联合索引(二级索引)是先按照 a 字段的值排序的,所以符合 >= 1 条件的二级索引记录肯定是相邻,于是在进行索引扫描的时候,可以定位到符合 >= 1 条件的第一条记录,然后沿着记录所在的链表向后扫描,直到某条记录不符合 a>= 1 条件位置。所以a 字段可以在联合索引的 B+Tree 中进行索引查询

虽然在符合 a>= 1 条件的二级索引记录的范围里,b 字段的值是「无序」的,但是对于符合 a = 1 的二级索引记录的范围里,b 字段的值是「有序」的(因为对于联合索引,是先按照 a 字段的值排序,然后在 a 字段的值相同的情况下,再按照 b 字段的值进行排序)。

于是,在确定需要扫描的二级索引的范围时,当二级索引记录的 a 字段值为 1 时,可以通过 b = 2 条件减少需要扫描的二级索引记录范围(b 字段可以利用联合索引进行索引查询的意思)。也就是说,从符合 a = 1 and b = 2 条件的第一条记录开始扫描,而不需要从第一个 a 字段值为 1 的记录开始扫描。

所以,此时查询语句 a 和 b 字段都用到了联合索引进行索引查询。

# WHERE a BETWEEN 2 AND 8 AND B = 2

这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

# WHERE Name Like ‘j%’ and Age = 22

这条查询语句 a 和 b 字段都用到了联合索引进行索引查询