假定数据表有一个包含2列的联合索引(a, b),则索引的B+树结构可能如下:
从图中可以看出:B+树先按a排序,然后按b排序,所以从全局看,a是全局有序的,而b则不是。
建表:
create table t_u_index ( a int, b int, c int, key ui(a, b, c) );
查看索引:
show index from t_u_index;
使用到联合索引:
explain select * from t_u_index where a=1 and b=1 order by c; explain select * from t_u_index where a=1 order by b;
未使用到联合索引:
explain select * from t_u_index where a=1 order by c; explain select * from t_u_index where b=1 order by c;