数据库索引相关问题点

数据库索引相关问题点

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 = '科比';

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