2020年前必须掌握的数据库面试问题~

一、为什么用自增列作为主键

1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引。

如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。

2、数据记录本身被存于主索引(一颗B+Tree)的叶子节点上,这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放

因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)

3、如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

4、如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置

此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销

同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

二、为什么使用数据索引能提高效率

  • 数据索引的存储是有序的
  • 在有序的情况下,通过索引查询一个数据是无需遍历索引记录的
  • 极端情况下,数据索引的查询效率为二分法查询效率,趋近于 log2(N)

三、B+树索引和哈希索引的区别

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的,如下图:

哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可,是无序的,如下图所示:

四、哈希索引的优势:

等值查询,哈希索引具有绝对优势(前提是:没有大量重复键值,如果大量重复键值时,哈希索引的效率很低,因为存在所谓的哈希碰撞问题。)

五、哈希索引不适用的场景:

  • 不支持范围查询
  • 不支持索引完成排序
  • 不支持联合索引的最左前缀匹配规则

通常,B+树索引结构适用于绝大多数场景,像下面这种场景用哈希索引才更有优势:

在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引,例如这种SQL:

# 仅等值查询select id, name from table where name='李明'; 

而常用的 InnoDB 引擎中默认使用的是B+树索引,它会实时监控表上索引的使用情况。

如果认为建立哈希索引可以提高查询效率,则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引(在InnoDB中默认开启自适应哈希索引)。

通过观察搜索模式,MySQL会利用index key的前缀建立哈希索引,如果一个表几乎大部分都在缓冲池中,那么建立一个哈希索引能够加快等值查询。

注意:在某些工作负载下,通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。

但某些时候,在负载高的情况下,自适应哈希索引中添加的read/write锁也会带来竞争,比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引,可能要关闭自适应哈希索引。

六、B树和B+树的区别

1、B树,每个节点都存储key和data,所有节点组成这棵树,并且叶子节点指针为nul,叶子结点不包含任何关键字信息。

2、B+树,所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接

所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。(而B 树的非终节点也包含需要查找的有效信息)

七、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

1、B+的磁盘读写代价更低。

B+的内部结点并没有指向关键字具体信息的指针,因此其内部结点相对B树更小。

如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2、B+-tree的查询效率更加稳定。

由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

八、MySQL联合索引

1、联合索引是两个或更多个列上的索引。

对于联合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。

2、利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。

复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。

如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓,电话簿将没有用处。

九、什么情况下应不建或少建索引

1、表记录太少

2、经常插入、删除、修改的表

3、数据重复且分布平均的表字段,假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

4、经常和主字段一块查询但主字段索引值比较多的表字段

十、什么是表分区?

表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。

十一、表分区与分表的区别

分表:指的是通过一定规则,将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。

十二、表分区有什么好处?

1、存储更多数据。分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比,可以存储更多数据

2、优化查询。在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果。

3、分区表更容易维护。例如:想批量删除大量数据可以清除整个分区。

4、避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3问价你系统的inode锁竞争等。

十三、分区表的限制因素

1、一个表最多只能有1024个分区

2、MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

3、如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。

4、分区表中无法使用外键约束

5、MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。

十四、如何判断当前MySQL是否支持分区?

命令:show variables like ‘%partition%’ 运行结果:

mysql> show variables like '%partition%';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| have_partitioning | YES |+-------------------+-------+1 row in set (0.00 sec)

have_partintioning 的值为YES,表示支持分区。

十五、MySQL支持的分区类型有哪些?

RANGE分区:这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区

LIST分区:这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区,与RANGE的区别是,range分区的区间范围值是连续的。

HASH分区 :这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

KEY分区 :上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

十六、四种隔离级别

  • Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
  • Repeatable read (可重复读):可避免脏读、不可重复读的发生。
  • Read committed (读已提交):可避免脏读的发生。
  • Read uncommitted (读未提交):最低级别,任何情况都无法保证。

十七、关于MVVC

MySQL InnoDB存储引擎,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)

注:与MVCC相对的,是基于锁的并发控制,Lock-Based Concurrency Control

MVCC最大的好处:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,现阶段几乎所有的RDBMS,都支持了MVCC。

  • LBCC:Lock-Based Concurrency Control,基于锁的并发控制
  • MVCC:Multi-Version Concurrency Control

基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低,MVCC是在基于锁的并发控制上的改进,主要是在读操作上提高了并发量。

十八、在MVCC并发控制中,读操作可以分成两类:

快照读 (snapshot read):读取的是记录的可见版本 (有可能是历史版本),不用加锁(共享读锁s锁也不加,所以不会阻塞其他事务的写)

当前读 (current read):读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录

十九、行级锁定的优点:

1、当在许多线程中访问不同的行时只存在少量锁定冲突。

2、回滚时只有少量的更改

3、可以长时间锁定单一的行。

二十、行级锁定的缺点:

比页级或表级锁定占用更多的内存。

当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。

如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。

用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

二十一、MySQL优化

  • 开启查询缓存,优化查询
  • explain你的select查询,这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的
  • 当只要一行数据时使用limit 1,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据
  • 为搜索字段建索引
  • 使用 ENUM 而不是 VARCHAR。如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是VARCHAR
  • Prepared StatementsPrepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 prepared statements 获得很多好处,无论是性能问题还是安全问题。
    Prepared Statements 可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击
  • 垂直分表
  • 选择正确的存储引擎

二十二、key和index的区别

key 是数据库的物理结构,它包含两层意义和作用,一是约束(偏重于约束和规范数据库的结构完整性),二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等

index是数据库的物理结构,它只是辅助查询的,它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。索引要分类的话,分为前缀索引、全文本索引等;

二十三、Mysql 中 MyISAM 和 InnoDB 的区别有哪些?

区别:

1、InnoDB支持事务,MyISAM不支持

对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;

2、InnoDB支持外键,而MyISAM不支持。

对一个包含外键的InnoDB表转为MYISAM会失败;

3、InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。

但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此主键不应该过大,因为主键太大,其他索引也都会很大。

MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4、InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;

5、Innodb支持全文索引,而MyISAM支持全文索引;

如何选择:

  • 是否要支持事务,如果要请选择innodb,如果不需要可以考虑MyISAM;
  • 如果表中绝大多数都只是读查询,可以考虑MyISAM,如果既有读写也挺频繁,请使用InnoDB
  • 系统奔溃后,MyISAM恢复起来更困难,能否接受;
  • MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM),说明其优势是有目共睹的,如果你不知道用什么,那就用InnoDB,至少不会差。

二十四、数据库表创建注意事项

1、字段名及字段配制合理性

  • 剔除关系不密切的字段;
  • 字段命名要有规则及相对应的含义(不要一部分英文,一部分拼音,还有类似a.b.c这样不明含义的字段);
  • 字段命名尽量不要使用缩写(大多数缩写都不能明确字段含义);
  • 字段不要大小写混用(想要具有可读性,多个英文单词可使用下划线形式连接);
  • 字段名不要使用保留字或者关键字;
  • 保持字段名和类型的一致性;
  • 慎重选择数字类型;
    -给文本字段留足余量;

2、系统特殊字段处理及建成后建议

  • 添加删除标记(例如操作人、删除时间);
  • 建立版本机制;

3、表结构合理性配置

  • 多型字段的处理,就是表中是否存在字段能够分解成更小独立的几部分(例如:人可以分为男人和女人);
  • 多值字段的处理,可以将表分为三张表,这样使得检索和排序更加有调理,且保证数据的完整性!

4、其它建议

  • 对于大数据字段,独立表进行存储,以便影响性能(例如:简介字段);
  • 使用varchar类型代替char,因为varchar会动态分配长度,char指定长度是固定的;
  • 给表创建主键,对于没有主键的表,在查询和索引定义上有一定的影响;
  • 避免表字段运行为null,建议设置默认值(例如:int类型设置默认值为0)在索引查询上,效率立显;
  • 建立索引,最好建立在唯一和非空的字段上,建立太多的索引对后期插入、更新都存在一定的影响(考虑实际情况来创建);

来源:http://uee.me/c8a2T

Image placeholder
lxxpsp2007
未设置
  32人点赞

没有讨论,发表一下自己的看法吧

推荐文章
15 个 Docker 初学者必须掌握的命令

这篇文章我基本上不会做put操作。如果你认为这些命令缺少了什么其他方面重要的东西,那么你需要自行检查Docker文档(https://docs.docker.com/) pull pull命令和gi

两万字的数据库面试题,不看绝对后悔

一、基本概念1.主键、外键、超键、候选键超键:在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。候选键:是最小超

【分享】2020年大前端面试题库 - 备战明年金三银四

https://segmentfault.com/a/119000002122195...2020年大前端面试题库-备战明年金三银四前端面试 阅读约10分钟写在前面现在面试文章已很多,更不觉得新鲜,本

2020年PHP面试总结

1.redis输出redis的数据结构?说出redis底层数据结构的实现说出redis的常用场景redis实现分布式锁。2.mysqlMySQL的最左匹配原则MySQL的索引MySQL的常用存储引擎M

2020年中大厂前端面试题(总结)

课程推荐:web全栈开发就业班--拿到offer再缴学费--融职教育 本次面试面试了很多家公司,包括360,美团,猿辅导,小米,腾讯地图,头条,新东方,快手,知乎等几家公司,刚开始去面试的时候那段时间

2020年,你是否更关注个人数据隐私了?

日前,阿里达摩院发布了2020年十大科技趋势,其中趋势九指出保护数据隐私的AI技术将加速落地。“数据流通所产生的合规成本越来越高。使用AI技术保护数据隐私正在成为新的技术热点,其能够在保证各方数据安全

Udemy:人工智能是2020年职场最需要的技能之一

TensorFlow是过去三年中最受欢迎的技术技能,根据Udemy的数据在2016年至2019年之间呈指数增长。·除了Web开发框架,云计算和IT认证(包括AWS、CompTIA和Docker)之外,

Gartner:2020年十大战略技术趋势

技术变革的步伐正在迅速加快,IT专业人员需要研究的因素,他们以前从未处理过的事情(例如超级自动化、多重体验和人员扩充)都将对企业产生重大的影响。Gartner高级研究副总裁ValSribar说:“自从

调查:2020年企业IT预算研究报告

随着云计算、人工智能等技术应用的落地,企业越来越多地用新技术支出来应对新的业务需求,大多数企业都在主动或被动地加入数字化转型浪潮,这一点在IT预算中表现得最为明显。TechRepublic最近进行了一

2020年将成为边缘计算的“突破年”

根据ForresterResearch的预测,2020年将是推动边缘计算技术的重要一年。尽管边缘计算主要是一种与物联网相关的技术,但Forrester表示,满足按需计算和实时应用程序的需求也将在推动边

在2020年及未来,网络会变成什么样子?

日前,由思科全球发起的“Networking.Next联网未来”线上活动取得圆满成功。此次活动上,未来学家、网络专家和Cisco首席战略官分享了对网络未来发展的洞察,并重磅发布了思科《2020年全球网

公布!达摩院2020年十大科技趋势

刚刚,“达摩院2020十大科技趋势”正式发布!去年,我们预测的科技趋势正一一变为现实:AI芯片崛起、智能城市诞生、5G催生全新应用场景……科技新十年开启,AI、芯片、云计算、区块链、工业互联网、量子计

2020年文案怎做的一手好图是多么重要

别说我标题念不通....对不起,我特意的....发博客,发朋友圈,发论坛,发自媒体.......不管你怎么发,都离不开图片....这里就文字做成图片的样式来说....以后的文案都得这样走...时尚无难

对2020年Linux和开源的5个大胆预测

在这篇文章中,omgubuntu网站列出了对2020年Linux的5个预测,大家也来预测下会不会实现。2020年Linux预测预测1:Linux设备的爆炸式增长好的,因此建议我们明年看到大量新的Lin

你真的了解 React 吗?这里有 50 个 React 面试问题

如果你是一位有理想的前端开发人员,并且正在准备面试,那么这篇文章就是为你准备的。本文收集了React面试中最常见的50大问题,这是一份理想的指南,让你为React相关的面试做好充分的准备工作。首先我

面试问题总结

作为一个一年半Java开发的技术小白面试的心得。在原先公司工作接触nginx、redis等工具。由于工作上对这些工具只做到会用的程度没了解深入。在面试中出现很多笑话(一问三不知的情况都出来)。今天做个

面试问烂的 Spring AOP 原理、SpringMVC 过程

  正文  SpringAOP,SpringMVC,这两个应该是国内面试必问题,网上有很多答案,其实背背就可以。但今天笔者带大家一起深入浅出源码,看看他的原理。以期让印象更加深刻,面试的时候游刃有余。

【搞定 Java 并发面试】面试最常问的 Java 并发基础常见面试题总结!

Java并发基础常见面试题总结 1.什么是线程和进程? 1.1.何为进程? 进程是程序的一次执行过程,是系统运行程序的基本单位,因此进程是动态的。系统运行一个程序即是一个进程从创建,运行到消亡的过程。

一文读懂数据库70年发展史

作者:常垒资本 冯斯基顾问:云和恩墨、戴工玖、周家晶零1949-19791956年,周恩来总理亲自领导了“科学技术发展十二年规划”,标志着我国计算机事业的开始。而那时,几乎没有人知道计算技术是怎么回事

2019数据库趋势研究:谁是最受青睐的数据库?

哪些数据库在2019年最受青睐?本文向DeveloperWeek的数百名业内人士咨询了当前NoSQL与SQL的使用情况,得到了有关MySQL、MongoDB、PostgreSQL、Redis和其他方面

2019数据库趋势研究:谁是最受青睐的数据库?

哪些数据库在2019年最受青睐?本文向DeveloperWeek的数百名业内人士咨询了当前NoSQL与SQL的使用情况,得到了有关MySQL、MongoDB、PostgreSQL、Redis和其他方面

5G基站辐射致癌?一个源自19年前的某权威报告引发的谣言

大数据文摘出品编译:邢畅、曹培信在这篇文章的内容正式开始之前,文摘菌先带大家看几张图。你看这根孤独的柱子,其实它是一个通信基站;再看这个平平无奇的路灯,它也是一个通信基站;再来看这颗不悲不喜的树,它依

码龄超过20年,依然对生活和编程充满激情,这是三位70后“老”程序员的故事

大数据文摘出品作者:周素云、张秋玥加班996,生病ICU。这是一句最近搅乱了很多程序员平静生活,也让所有的“社畜”认真反思人生的话题。但是,让程序员们真正感到焦虑的其实并不只是工作的压力,更多的是对未

再见,2019!你好,2020!

今天,是2019年的最后一天。虽有不舍,但终归还是得说再见!明天又会翻开新一年的篇章,希望各位在新的一年都能升职加薪迎娶白富美,走上人生巅峰。19年某月某日突发奇想,想用公众号分享一下这些年学到的技术

送别2019,期待2020!

概述2019年时间过得很快。有欢笑、有离别、有压力、有收获。关于工作项目发生了变动,团队也发生了变动,不过总体是成长的,在这感谢领导的关照、信任!下半年开始学习Go语言,并用Go进行搭建项目,也算是刚