索引的常见模型
- 哈希表
基于散列函数,不是有序的,而且散列抵触带来的链表构造等,会导致哈希索引做区间查问的速度很慢。因而这种构造实用于只有等值查问的场景,比方 Memcached 及其他一些 NoSQL 引擎。 - 有序数组
有序数组在等值查问和范畴查问场景中的性能就都十分优良, 然而在须要增
或删
数据的时候就麻烦了,往两头插入一个记录就必须得移动前面所有的记录,老本太高。只实用于动态存储引擎 - 搜寻树
等值查问和范畴查问性能都很好,且增
或删
数据也很不便,被广泛应用在数据库引擎中。
基于主键索引和一般索引的查问有什么区别?
- 如果语句是 select * from T where ID=500,即主键查问形式,则只须要搜寻 ID 这棵 B+ 树;
- 如果语句是 select * from T where k=5,即一般索引查问形式,则须要先搜寻 k 索引树,失去 ID 的值为 500,再到 ID 索引树搜寻一次。这个过程称为回表。
也就是说,基于非主键索引的查问须要多扫描一棵索引树。因而,咱们在利用中应该尽量应用主键查问。
联结索引
假如,咱们对 (a,b) 字段建设索引,那么入下图所示
他们是依照 a 来进行排序,在 a 相等的状况下,才按 b 来排序。
因而,咱们能够看到 a 是有序的 1,1,2,2,3,3。而 b 是一种全局无序,部分绝对有序状态! 什么意思呢?
从全局来看,b 的值为 1,2,1,4,1,2,是无序的。
从部分来看,当 a 的值确定的时候,b 是有序的。例如 a = 1 时,b 值为 1,2 是有序的状态。当 a = 2 时候,b 的值为 1,4 也是有序状态。
最左前缀
依据联结索引剖析,咱们能够晓得,联结索引跟程序无关。因而,当执行 a = 1 and b = 2
是 a,b 字段能用到索引的。而执行 a > 1 and b = 2
时,a 字段能用到索引,b 字段用不到索引。因为 a 的值此时是一个范畴,不是固定的,在这个范畴内 b 值不是有序的,因而 b 字段用不上索引。这就是最左匹配准则,在遇到范畴查问的时候,就会进行匹配。
以下是一些例子:
例 1: 建设索引 (a,b,c)、(b,a,c)、(c,a,b) 都能够,因为无范畴查问,优化器会主动判断抉择索引
SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;
例 2: 建设索引(b,a)
SELECT * FROM table WHERE a > 1 and b = 2;
例 3:(b,a)或者 (b,c) 都能够,要联合具体情况具体分析
SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;
例 4: 对 (a,b) 建索引,当 a = 1 的时候,b 绝对有序,能够防止再次排序
SELECT * FROM `table` WHERE a = 1 ORDER BY b;
例 5:(a,b,c)或 (b,a,c) 都能够
SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
例 6: 对 (a,b) 建设索引,因为 IN 在这里能够视为等值援用,不会停止索引匹配,所以还是(a,b)
SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;
笼罩索引
概念:如果索引蕴含所有满足查问须要的数据的索引成为笼罩索引(Covering Index),也就是平时所说的不须要回表操作。explain extra 列值为 using index