底层原理 - Mysql索引分类
索引的作用
说白了索引就是数据的目录,根据索引去查数据必然比在库中一行记录一行记录的查更快
索引分类
按数据结构分类
Mysql的数据是存储在磁盘中的,每次从磁盘中读取数据都需要进行一次IO。一个表的数据在磁盘上由于插入顺序的原因肯定不是顺序存放,如果按照表字段内容顺序查找,如果一个500万条数据的表,要找的刚好是第500万个值,则需要与磁盘做500万次IO,效率低下
因此在选择索引的数据结构时,应尽量减少IO次数,提高效率。而Mysql就是使用的B+树
为什么不用hash表
这与hash表的原理有关。
使用hash表的目的是为了尽可能的散列,因此在使用hash表的时候要选择hash算法,避免hash碰撞和hash冲突。
这就导致了hash表存储的数据是无序的,当需要进行范围查询的时候,只能挨个进行遍历对比,效率极低
小结:
- 不用hash表的原因在于不适合范围查询
- 容易导致全表扫描,因为可能存在不同的key经过hash运算后值相同
- 索引列上的值相同的话,易造成hash冲突,效率低下
为什么不用二叉树
如果将一个乱序的数据放入二叉树中,效率会高,但是如果数据是有顺序的,比如1、2、3、4、5,则二叉树将会编程一个链表的样式,失去了二叉树的优势
总结:有可能退化成链表,读取数据需要频繁IO
为什么不用红黑树(或二叉平衡树)
红黑树也是一种二叉平衡树,红黑树可以有效解决掉顺序数据一次放入二叉树而导致的形成链表的结果,但是红黑树一个节点只能存储一个数据,就导致如果是大量的数据,红黑树的高度就不可控,如果一个红黑树是20的高度,要查询的数据在叶子结点,则表示需要需磁盘20次IO,效率还是不高
总结:相比于二叉树能避免退化成链表,但IO次数还是会很高
为什么不用B树而用B+树
B树比红黑树的优势是,B树是一个节点上存储多个数据,比如磁盘的一页数据,这样的横向扩展,相同的数据量就可以比红黑树减少更多的高度,从而减少了磁盘的IO次数
B树:data为数据的磁盘地址,还可能是整条列的数据
- 数据是分散在每个结点上的,所有结点元素不重复,
- 结点元素从左到右递增
- 叶子结点具有相同的深度,叶子结点的指针为空
B+树:data为数据的磁盘地址,还可能是整条列的数据
- 叶子结点包含了所有的索引字段,以及数据的磁盘地址,而非叶子结点不再存储data数据,作用只是便于查找的冗余索引
- 非叶子结点是从子结点选取一页的开头来作为自己的值,指针为子结点那页的地址
- 每一个结点里的值都是排好序的
- 叶子结点之间是用「双向链表」进行连接,这样方便了范围查找,比如where col > 10,这是mysql对B+的变种,也是对比B树的一个优势
- 由于data可能会很大,非叶子结点在不存储data后,非叶子可以存储的元素则会变多,还可以降低树的高度,提高了查询的效率,这是与B树对比,B+树的一个优势
总结:B+树相比于B树,非叶子节点不再存储data数据,非叶子可以存储的元素则会变多,一个非叶子节点就可以存储更多的索引数据,更进一步降低树的高度,提高了查询的效率。相比于hash表,B+树利用叶子节点之间的指针可以进行范围查询
按物理存储分类
索引分为聚簇索引(主键索引)、非聚簇索引(二级索引)
- 主键索引的 B+Tree 的叶子节点存放的是实际数据,所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里;
- 二级索引的 B+Tree 的叶子节点存放的是主键值,而不是实际数据。
MyISAM和InnoDB存储引擎指的是数据库表,一个数据库里可以给不同的表指明不同的存储引擎
MyISAM存储引擎的表会生成3个磁盘文件,.frm(表结构)、.MYD(表数据)、.MYI(表索引文件),MyISAM存储引擎是非聚簇索引,它将表数据与表索引文件分开存储,如图展示,绿色的索引部分存储在.MYI文件里,蓝色的数据部分存储在.MYD文件里,例如在.MYI文件里查询到了30值的data为0x6A,则会通过这个磁盘地址去.MYD文件里查询该行的数据,MyISAM的一级索引和二级索引结构一致
InnoDB存储引擎会生成2个磁盘文件,.frm(表结构)、.idb(表数据加索引),InnoDB存储引擎是聚簇索引,它将表数据与表索引一起存储,在B+树的叶子结点里不再存储的是该行的磁盘空间位置,而是该行的全部列数据,这样就可以不再一次寻址(回表),由于它表数据与表索引一起存储,所以就必须有一个主键索引来组建我们整张表,主键所以建议使用整型递增的类型,(整型是因为整型在值对比的过程中要快很多,且整型占用空间小,一页能存储的数据就会比较多,索引整体也会变小,自增是因为递增的值插入B+树的可以直接尾部加结点就好,如果是中间值就可能还需要拆分结点插入,毕竟B+树本身维护的就是递增的顺序)
InnoDB 在创建聚簇索引时,会根据不同的场景选择不同的列作为索引:
- 如果有主键,默认会使用主键作为聚簇索引的索引键;
- 如果没有主键,就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键;
- 在上面两个都没有的情况下,InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键;
一张表只能有一个聚簇索引,为了实现非主键字段的快速搜索,就引出了二级索引(非聚簇索引/辅助索引),它也是利用了 B+ 树的数据结构,但是二级索引的叶子节点存放的是主键值,不是实际数据。
因此,如果某个查询语句使用了二级索引,但是查询的数据不是主键值,这时在二级索引找到主键值后,需要去聚簇索引中获得数据行,这个过程就叫作回表,也就是说要查两个 B+ 树才能查到数据。不过,当查询的数据是主键值时,因为只在二级索引就能查询到,不用再去聚簇索引查,这个过程就叫作索引覆盖,也就是只需要查一个 B+ 树就能找到数据。
按字段特性分类
从字段特性的角度来看,索引分为主键索引、唯一索引、普通索引、前缀索引。
主键索引
主键索引就是建立在主键字段上的索引,通常在创建表的时候一起创建,一张表最多只有一个主键索引,索引列的值不允许有空值。
CREATE TABLE table_name (
....
PRIMARY KEY (index_column_1) USING BTREE
);
主键索引最好是自增的
如果使用自增主键,那么每次插入的新数据就会按顺序添加到当前索引节点的位置,不需要移动已有的数据,当页面写满,就会自动开辟一个新页面。因为每次插入一条新记录,都是追加操作,不需要重新移动数据,因此这种插入数据的方法效率非常高。
如果使用非自增主键,由于每次插入主键的索引值都是随机的,因此每次插入新的数据时,就可能会插入到现有数据页中间的某个位置,这将不得不移动其它数据来满足新数据的插入,甚至需要从一个页面复制数据到另外一个页面,我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。
唯一索引
唯一索引建立在 UNIQUE 字段上的索引,一张表可以有多个唯一索引,索引列的值必须唯一,但是允许有空值。
在创建表时,创建唯一索引的方式如下
CREATE TABLE table_name (
....
UNIQUE KEY(index_column_1,index_column_2,...)
);
建表后,如果要创建唯一索引,可以使用这面这条命令:
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
普通索引
普通索引就是建立在普通字段上的索引,既不要求字段为主键,也不要求字段为 UNIQUE。
在创建表时,创建普通索引的方式如下:
CREATE TABLE table_name (
....
INDEX(index_column_1,index_column_2,...)
);
建表后,如果要创建普通索引,可以使用这面这条命令:
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引,而不是在整个字段上建立的索引,前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间,提升查询效率。
在创建表时,创建前缀索引的方式如下:
CREATE TABLE table_name(
column_list,
INDEX(column_name(length))
);
建表后,如果要创建前缀索引,可以使用这面这条命令:
CREATE INDEX index_name
ON table_name(column_name(length));
按字段个数分类
从字段个数的角度来看,索引分为单列索引、联合索引(复合索引)。
- 建立在单列上的索引称为单列索引,比如主键索引;
- 建立在多列上的索引称为联合索引;
一般不建议建多个单值索引,而是推荐多个字段一起建一个组合索引,节省空间
图例是一个联合主键索引,也可以建立联合的二级索引
原理就是先用name排序,再用age排,再用position排,这里就是有了最左匹配原则,where条件可以name = ""、name = "" and age = ""和name = "" and age = "" and position = "" 都可以使用该联合索引查找
联合索引范围查询
联合索引有一些特殊情况,并不是查询过程使用了联合索引查询,就代表联合索引中的所有字段都用到了联合索引进行索引查询,也就是可能存在部分字段用到联合索引的 B+Tree,部分字段没有用到联合索引的 B+Tree 的情况。
例1
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 字段无法利用联合索引进行索引查询的意思)。
所以在执行这条查询语句的时候,对应的扫描区间是 (2, + ∞),形成该扫描区间的边界条件是 a > 1,与 b = 2 无关。
因此,这条查询语句只有 a 字段用到了联合索引进行索引查询,而 b 字段并没有使用到联合索引。
例2
select * from t_table where a >= 1 and b = 2,联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?
例2 和 例1 的查询语句很像,唯一的区别就是 a 字段的查询条件「大于等于」。
由于联合索引(二级索引)是先按照 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 字段都用到了联合索引进行索引查询。但是也仅仅只有 a = 1 时 b 用到了联合索引进行查询,而a > 1的部分与 例1 同理
例3
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2,联合索引(a, b)哪一个字段用到了联合索引的 B+Tree?
由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值,相当于 >= and =< ,所以类似于 例2 查询语句,因此 例3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
例4
SELECT * FROM t_user WHERE name like 'j%' and age = 22,联合索引(name, age)哪一个字段用到了联合索引的 B+Tree?
由于联合索引(二级索引)是先按照 name 字段的值排序的,所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的, 于是在进行索引扫描的时候,可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录,然后沿着记录所在的链表向后扫描,直到某条记录的 name 前缀不为 ‘j’ 为止。
所以 a 字段可以在联合索引的 B+Tree 中进行索引查询,形成的扫描区间是['j','k')。注意, j 是闭区间。如下图:
虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里,age 字段的值是「无序」的,但是对于符合 name = j 的二级索引记录的范围里,age字段的值是「有序」的(因为对于联合索引,是先按照 name 字段的值排序,然后在 name 字段的值相同的情况下,再按照 age 字段的值进行排序)。
于是,在确定需要扫描的二级索引的范围时,当二级索引记录的 name 字段值为 ‘j’ 时,可以通过 age = 22 条件减少需要扫描的二级索引记录范围(age 字段可以利用联合索引进行索引查询的意思)。也就是说,从符合 name = 'j' and age = 22 条件的第一条记录时开始扫描,而不需要从第一个 name 为 j 的记录开始扫描 。如下图的右边:
所以,这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
小结:联合索引中的字段顺序应该如何设计?
- 将最常用的查询条件列放在索引的前部,确保索引尽可能被查询使用。
- 选择性高的列(即唯一值多的列,区分度大的列)放在索引的前部,可以减少扫描的范围,提高查询效率。
- 索引的列顺序应尽量与查询的 WHERE 子句中的列顺序一致,也就是最左匹配原则。
联合索引的最左匹配原则,在遇到范围查询(如 >、<)的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。注意,对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配。