oracle大量删除数据之后索引是否需要重建_深入浅出索引
索引的出现
索引的出现用一句话来说就是为了提高数据的查询效率, 就像数据目录一样
索引的常见模型
实现索引的主要方式有三种
哈希表
哈希表是一种以键-值存储的数据结构,我们查找时只需要输入待查找值的key,数据能够以很快的效率返回。
哈希值的优势
- 查找快,时间复杂度时O(1)
哈希值的劣势:
- 进行区间查询的速度很慢()
- 只适用等值查询,无法进行大于小于
有序数组
有序数组时将数据以数组的形式来进行存储
优势:
- 数据进行等值查询和区间查询的效率都很高。
劣势:
- 插入和删除的效率很低
二叉搜索树
二叉搜索树增加、删除、查询的效率都很高,时间复杂度都是O(n), 但是实现上一般都不采用二叉搜索树来进行存储,因为二叉搜索树太高。如果一个表格有100万数据,树的高度可以达到20.那就代表需要访问20次磁盘,这在机械硬盘是十分耗费时间的。
所以一般使用N叉树,这个N一般是1200.这样树高4层就能存储1200的三次方,大概17亿条数据记录。
总结
一个数据库首先应该关注他的数据模型,根据数据模型就能大概知道这个数据库的性能优势和性能瓶颈,从而才能知道这个数据库的使用场景。
索引的类型
根据存储的内容可分为主键索引和非主键索引,主键索引也被称为聚簇索引,非主键索引也被称为二级索引。
基于主键索引和非主键索引的查询有什么区别。
- 基于主键索引查询,由于数据都存在于主键索引,因此我们搜索主键索引树便可直接获取结果集返回到执行器中
- 基于二级索引查询,由于二级索引树叶子结点存储的是主键的值,因此我们要通过主键id再去主键索引树进行一次查询,获得结果集,这个过程我们称为回表。
索引的维护
B+树为了维护索引有序性,在插入新值的时候需要做必要的维护。假设如果插入新的行ID值为最大值,则只索引的最后插入一个新记录。如果新插入的ID值为中间值,那就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。
而更糟的情况是,如果插入值所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。
除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。
当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。
根据索引树的这个特征,那如何做能够提高性能呢。
某些数据库规范可能规定一个表格必须有自增主键,自增主键代表了数据在插入过程中可以不指定主键,数据会自动按照最大值+1来作为下一个值,这样的规则能符合递增插入的过程,每次插入都是追加操作,不涉及到其他节点的挪动,也不会涉及到页分裂。
如果一个业务有一个唯一字段(身份证),那主键应该怎么选择呢。
- 如果是使用业务字段作为主键。
- 身份证的长度为18位,占用18个字节,会导致其他二级索引的消耗资源更大
- 插入过程涉及到页分裂,涉及到页之间其他节点的挪动(可将页的挪动看成数组插入过程)
- 使用身份证查询不用回表
- 如果使用自增主键
- int类型主键占用4个字节
- 插入过程很快,没有其他操作。
- 使用身份证查询需要回表
所以,使用自增主键似乎无论何时都是更好的选择。事无绝对,如果有以下场景
- 只有一个索引
- 该索引必须是唯一索引
这就是典型的KV场景,这时候就不用考虑其他索引占用空间的问题,相反我们应该尽量使用业务来保证查询不用回表。
如何使用索引?
索引的执行过程
通过以下代码我们创建这样一个场景
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc')
,(500,5,'ee'),(600,6,'ff'),(700,7,'gg');”
- 根据
小结
- B+树能够降低读取磁盘的次数,减少单次查询的寻址时间。
- 自增主键减少插入时无需其他的操作,减少插入时数据迁移操作,当然,如果使用业务id来做主键,会减少回表的次数,所以在某些场景需要综合考虑。
- MySQL5.6能通过设置page的值来间接的控制N叉树的N的值。
问题
重建一个索引时应该如何做?(普通索引、主键索引)
- 普通索引
alter table T drop index k;
alter table T add index(k);
这种做法是否合适?
新建普通索引需要在业务空闲期来做,防止业务抖动。可以先新建一个新的索引,然后再删除旧的索引。
- 主键索引
alter table T drop primary key;
alter table T add primary key(id);
这种做法是否合适?
不合适,如果删除主键索引,Mysql会默认找一个row_id来做索引,相当于重建了一次主键索引,增加id为主键索引,又会触发一次主键索引的重建。此外,重建主键索引会导致其他索引被重建。
删除索引是否会释放空间?