mysql联合索引

2020/05/28 posted in  测试
Tags:  #db #mysql

查询一张表的索引


SHOW INDEX FROM T

查询表 T 所有索引。

MySQL 中最多可以创建 16 个索引列。

联合索引

联合索引又叫复合索引,MySQL 中的联合索引,遵循最左匹配原则,比如,联合索引为 key(a,b,c),则能触发索引的搜索组合是 a|ab|abc 这三种查询。

where a=1 只使用了索引 a;
where a=1 and b=2 只使用了索引 a,b;
where a=1 and b=2 and c=3 使用a,b,c;
where b=1 or where c=1 不使用索引;
where a=1 and c=3 只使用了索引 a;
where a=3 and b like 'xx%' and c=3 只使用了索引 a,b。


select * from t where year(date)>2018;

不会,因为在索引列上涉及到了运算。

InnoDB 为什么要使用 B+ 树,而不是 B 树、Hash、红黑树或二叉树?

因为 B 树、Hash、红黑树或二叉树存在以下问题:

B 树:不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;
Hash:虽然可以快速定位,但是没有顺序,IO 复杂度高;
二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且 IO 代价高;
红黑树:树的高度随着数据量增加而增加,IO 代价高。

为什么 InnoDB 要使用 B+ 树来存储索引?
B+Tree 中的 B 是 Balance,是平衡的意思,它在经典 B Tree 的基础上进行了优化,增加了顺序访问指针,在B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的 B+Tree,这样就提高了区间访问性能:如果要查询 key 为从 18 到 49 的所有数据记录,当找到 18 后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率(无需返回上层父节点重复遍历查找减少 IO 操作)。