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 = '科比';
这种情况,可以创建复合索引了。
另外一部分,则需要先做聚类、分类处理,将聚合出的分类结果存入ES集群的聚类索引中。数据处理层的聚合结果存入ES中的指定索引,同时将每个聚合主题相关的数据存入每个document下面的某个field下。