数据库索引相关问题点

peili0 2019-11-17

数据库索引相关问题点

1.为什么要给表加上主键?

没有创建主键的表,他的数据是无序的放在磁盘中,一行行的排列很整齐,查询时也要一行行的扫描。

 创建了主键的表,在磁盘中的存储结构,由整齐的结构变成树状结构,也就是平衡树(B-Tree,B+Tree)结构。整个变成一个索引,也就是所谓的「聚集索引」。

 当然,也有使用哈希结构的索引,主流的关系型数据库还是默认平衡树作为索引数据结构的。

不同的数据库存储引擎,创建的索引结构也不同。

2.为什么一个表只能有一个主键?

一个表只能有一个「聚集索引」,因为主键的作用是把表的数据格式转化为树形结构放置,所以一个表只有一个主键。

联合主键是主键的一种,是由多个字段组成的主键,主键并不一定只有一个字段,这里要搞清楚。

3.为什么加索引后会使查询变快?

select * from table where id=82;

假如一张他table表有一亿条数据 ,需要查找id为82的数据,没创建索引的情况,可能是一条一条的去匹配,最差的打算是需要匹配一亿次,一亿次匹配查询,就是一亿次IO开销,按照服务器的I/O能力与CPU的运算能力,需要几个月才能得出结果。

往table表中添加id为主键,表的数据结构变化为平衡树结构。

数据库索引相关问题点

树形结构:由根节(root)、分支(branches)、叶(leaves)三级节点组成,其中分支节点可以有多层。

下图展示定位id为82的过程:

数据库索引相关问题点

算法步骤:

1.读取root节点,判断82大于在0-120之间,走左边分支;

2.读取左边branch节点,判断82大于80且小于等于120,走右边分支;

3.读取右边leaf节点,在该节点中找到数据82及对应的id;

4.使用rowid去物理表中读取记录数据块。

在整个索引中,只进行了三次I/O操作,就定位到了id。

对于一亿的数据量最多要查询多少次呢?

数据库索引相关问题点

树形结构,数据每增加一层,数据量就是成指数级的增长。

如果有2个分支,一亿条数据,最多查询27次就可以定位到数据,如果树结构是4个分叉,最多15次就能查到.

4.为什么加索引后会使写入、修改、删除变慢?

原因很简单,删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构,每次数据改变时,数据库管理系统都要重新梳理树结构,还会带来不小的性能开销。索引会给查询以外的操作带来副作用。

5.非聚集索引是怎样的呢?

非聚集索引,就是我们平时使用的向某个字段添加索引,非聚集索引和聚集索引一样,同样可以采用平衡树作为索引数据结构,索引树各节点中的值,来自于表中字段的值,如果给字段创建一个索引,字段的值就会被复制出来,用于创建索引。给表添加索引,就会增加表的体积,占用更多的磁盘存储空间。非聚集索引都是独立存在的,每个索引之间不存在关联。

数据库索引相关问题点

6.聚集索引和非聚集索引的区别?

通过聚集索引可以查到需要查找到的是每行的数据,通过非聚集索引查到的是记录对应的主键,再通过主键查找相应的值。不管任何查表的方式,都会通过主键定位到数据,聚集索引(主键)是通往数据的唯一途径。

7.什么情况下要同时在两个字段上建索引?

可能还有一种特殊情况,可以不通过聚集索引就得到需要的数据,这种非主流的方法 称之为「覆盖索引」查询。也就是平时创建的复合索引或者多字段索引查询。为一个索引指定两个字段,那么两个字段的内容就同步到索引中。如果通过其中的一个字段查另一个字段时,正好在索引中有存储,就不用通过聚集索引啦。

//创建用户的index_name_and_age 索引

create index index_name_and_age on user_info(name, age);

//通过用户名查找年龄

select age from user where name = '科比';

这种情况,可以创建复合索引了。

相关推荐