数据库面试题

数据库的架构

如何设计一个关系型数据库(数据库的架构)

数据库面试题

索引

索引的简介

MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。

索引的数据结构(B+ 树)

  • B+ 树结构图

数据库面试题

  • B+ 树的特点
1.非叶子节点的子树指针与关键字个数相同
2.非叶子节点的子树指针P[i],指向关键字值[K[i],k[i+1])的子树
3.非叶子节点仅用来索引,数据都保存在叶子节点中(查询只在叶子节点结束)
4.所有叶子节点均有一个链指针指向下一个叶子节点(便于数据库的范围统计)
  • B+ 树更适合用来做存储索引的原因
1.B+树的磁盘读写代价更低(树的深度低,IO次数少)
2.B+树的查询效率更加稳定(所有查询结果都会在叶子节点结束)
3.B+树更有利于对数据库的扫描(通过数据指针横向扫描数据)

密集索引和稀疏索引的区别

  • 密集索引文件中的每个搜索码值都对应一个索引值(即可根据索引值找到对应的一条数据,因此每张表只能创建一个密集索引)

  • 稀疏索引文件只为索引码的某些值建立索引项(即根据索引值找到对应一条数据的地址或主键,再根据地址或主键找到数据)

MySQL两种存储引擎的索引区别

参考的博客

  • MyISAM
在MyISAM中不管是主键索引唯一键索引、普通索引,其索引都属于稀疏索引
  • Innodb
在Innodb中 有且仅有一个密集索引 
Innodb中选取规则:
1.如果一个主键被定义了,则该主键作为密集索引
2.若该主键没有被定义,则该表的第一个唯一非空索引作为密集索引
3.若不满足上述条件 则Innodb内部会生成一个隐藏主键(密集索引)
4.非主键索引存储相关键位和其对应的主键值,包含两次查找(第一次根据索引值找到对应数据的主键,然后根据主键找到相应的数据)
  • 两种引擎索引的实例图

数据库面试题

  • 两种引擎会建立不同的文件
1.建立一个MyISAM引擎的数据表 test1 会建立三个文件
test1.frm   存储表的结构信息
test1.MYI  存储表的索引信息
test1.MYD 存储表的数据
2.建立一个InnoDB引擎的数据表 test2 会建立两个文件
test2.frm 存储表的结构信息
test2.ibd 存储表的数据和索引

联合索引的最左匹配原则

参考的博客

select * from t where a=1 and b=1 and c =1;     #这样可以利用到定义的索引(a,b,c)

select * from t where a=1 and b=1;     #这样可以利用到定义的索引(a,b,c)

select * from t where a=1;     #这样也可以利用到定义的索引(a,b,c)

select * from t where b=1 and c=1;     #这样不可以利用到定义的索引(a,b,c)

select * from t where a=1 and c=1;     #这样不可以利用到定义的索引(a,b,c)

也就是说通过最左匹配原则你可以定义一个联合索引,但是使得多中查询条件都可以用到该索引。
值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。也就是:

select * from t where a=1 and b>1 and c =1;     #这样a,b可以用到(a,b,c),c不可以

总结

  • 在 InnoDB 中联合索引只有先确定了前一个(左侧的值)后,才能确定下一个值。如果有范围查询的话,那么联合索引中使用范围查询的字段后的索引在该条 SQL 中都不会起作用。

  • 值得注意的是,in= 都可以乱序,比如有索引(a,b,c),语句 select * from t where c =1 and a=1 and b=1,这样的语句也可以用到最左匹配,因为 MySQL 中有一个优化器,他会分析 SQL 语句,将其优化成索引可以匹配的形式,即 select * from t where a =1 and a=1 and c=1

索引是建立得越多越好吗

  • 不是
1.数据量小的表不需要建立索引,建立会增加额外的索引开销
2.数据变更需要维护索引,因此更多的索引意味着更多的维护成本
3.更多的索引意味着也需要更多的空间

数据库面试题

上一篇:SHELL脚本执行mongodb 添加用户权限


下一篇:System.Windows.Forms.ListView : Control