讲到mysql,那么索引肯定是绕不开的话题,大家平时工作中应该也经常接触。索引,简单点概括,就是为了提高数据的查询效率,就像书的目录一样
常见的索引模型:
- 哈希索引:是一种键-值(key-value)的数据结构索引。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。
缺点:key的hash冲突问题;范围查询要遍历全部数据;如果是有序数组,查询范围查询虽然很快,但是更新数据时候就很麻烦 - 二叉搜索树:树形结构,每个节点的左儿子小于父节点,父节点又小于右儿子。查询时间复杂度为O(logN)。
在某些极端情况下,树可能会变成线性。为了保证查询时间复杂度为O(logN),这个时候又会有平衡二叉树。
为了减少和磁盘的IO,后面有引入了N叉树,通过降低树的高度方式减少磁盘IO。
InnoDB 的索引模型
InnoDB使用的是B+树的索引模型,每一个索引在 InnoDB 里面对应一棵 B+ 树。
例:
#1.新建一张表T,给字段k加上索引
create table T(
id int PRIMARY key,
k int not null,
name varchar(32),
INDEX(k)
)ENGINE=INNODB;
#2.添加些数据
INSERT INTO `t` (`id`, `k`, `name`) VALUES ('100', '1', 'a');
INSERT INTO `t` (`id`, `k`, `name`) VALUES ('200', '2', 'b');
INSERT INTO `t` (`id`, `k`, `name`) VALUES ('300', '3', 'c');
INSERT INTO `t` (`id`, `k`, `name`) VALUES ('500', '5', 'e');
INSERT INTO `t` (`id`, `k`, `name`) VALUES ('600', '6', 'f');
那么这张表中就有两棵树:1.主键索引ID字段的树,2.非主键的k字段索引树
两棵树如下图:R1~R5表示的是表中第几行整行数据
主键索引的叶子节点存的是整行数据
。在 InnoDB 里,主键索引也被称为聚簇索引
(clustered index)。
非主键索引的叶子节点内容是主键的值
。在 InnoDB 里,非主键索引也被称为二级索引
(secondary index)
基于主键索引的查询和普通索引的查询区别:
- 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
- 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。
因为基于非主键索引的查询需要多扫描一颗索引树,因此我们尽量使用主键索引查询比较好。
索引的维护
由于每个非主键索引的叶子节点都是主键的值。所以如果主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也越小。
例如有一张用户表,用户身份证号码也是唯一的,也可以用来做主键,那你是新增加一个自增的ID字段作为主键好呢?还是用身份真号码作为主键好呢?
从性能和存储空间方面考量,自增主键往往是更合理的选择。
有些场景也可以用业务字段做主键:
- 只有一个索引
- 该索引必须是唯一索引
例如上面说的这个例子,如果确定表就只有一个索引。由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。那么这个时候用身份证号码作为主键还是很合理的。
而且,通过身份证号码查询的时候,还可以避免回表
情况发生。(符合尽量使用主键查询
的原则)
索引重建
1.为什么要重建索引?
- 索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。
下一章:回表、覆盖索引、最左匹配原则