首页 > 数据库 >Mysql面试题

Mysql面试题

时间:2023-11-15 22:04:29浏览次数:29  
标签:面试题 范式 字节 存储 查询 索引 Mysql 数据

1、MySQL有哪些数据库类型?

数值类型

有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。

1)任何整数类型都可以加上 UNSIGNED 属性,表示无符号整数。

2)任何整数类型都可以指定长度,但它不会限制数据的合法长度,仅仅限制了显示长度。

还有包括 FLOAT、DOUBLE、DECIMAL 在内的小数类型。

字符串类型

包括 VARCHAR、CHAR、TEXT、BLOB。

注意:VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数,而是代表字符的个数。

日期和时间类型

常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。

尽量使用 TIMESTAMP,空间效率高于 DATETIME。

ref MySQL 数据类型


2、CHAR 和 VARCHAR 区别?

1)首先可以明确的是 CHAR 是定长的,而 VARCHAR 是可以变长。

CHAR 会根据声明的字符串长度分配空间,并会使用空格对字符串右边进行尾部填充。所以在检索 CHAR 类型数据时尾部空格会被删除,如保存的是字符串 'char ',但最后查询到的是 'char'。又因为长度固定,所以存储效率高于 VARCHAR 类型。

VARCHAR 在 MySQL 5.0 之后长度支持到 65535 字节,但会在数据开头使用额外 1~2 个字节存储字符串长度(列长度小于 255 字节时使用 1 字节表示,否则 2 字节),在结尾使用 1 字节表示字符串结束。

2)再者,在存储方式上,CHAR 对英文字符(ASCII)占用 1 字节,对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。

虽然 VARCHAR 是根据字符串长度分配存储空间的,但在内存中依旧使用声明长度进行排序等作业,故在使用时仍需综合考量字段长度。


3、CHAR 和 VARCHAR 如何选择?

1)对于经常变更的数据来说,CHAR 比 VARCHAR更好,因为 CHAR 不容易产生碎片。

2)对于非常短的列或固定长度的数据(如 MD5),CHAR 比 VARCHAR 在存储空间上更有效率。

4)使用时要注意只分配需要的空间,更长的列排序时会消耗更多内存。

4)尽量避免使用 TEXT/BLOB 类型,查询时会使用临时表,导致严重的性能开销。


4、CHAR,VARCHAR 和 Text 的区别?

1)长度区别

Char 范围是 0~255。

Varchar 最长是 64k(注意这里的 64k 是整个 row 的长度,要考虑到其它的 column,还有如果存在 not null 的时候也会占用一位,对不同的字符集,有效长度还不一样,比如 utf-8 的,最多 21845,还要除去别的column),但 Varchar 在一般情况下存储都够用了。

如果遇到了大文本,考虑使用 Text,最大能到 4G(其中 TEXT 长度 65,535 bytes,约 64kb;MEDIUMTEXT 长度 16,777,215 bytes,约 16 Mb;而 LONGTEXT 长度 4,294,967,295 bytes,约 4Gb)。

2)效率区别

效率来说基本是 Char > Varchar > Text,但是如果使用的是 Innodb 引擎的话,推荐使用 Varchar 代替 Char。

3)默认值区别

Char 和 Varchar 支持设置默认值,而 Text 不能指定默认值。


1.2 数据库设计

1、什么是三大范式?

第一范式(1NF):字段(或属性)是不可分割的最小单元,即不会有重复的列,体现原子性

第二范式(2NF):满足 1NF 前提下,存在一个候选码,非主属性全部依赖该候选码,即存在主键,体现唯一性,专业术语则是消除部分函数依赖

第三范式(3NF):满足 2NF 前提下,非主属性必须互不依赖,消除传递依赖

ref:如何理解关系型数据库的常见设计范式?

除了三大范式外,还有BC范式和第四范式,但其规范过于严苛,在生产中往往使用不到。


2、什么是范式和反范式,以及各自优缺点?

范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中,这种规则就是范式。

名称 优点 缺点

范式 范式化的表减少了数据冗余,数据表更新操作快、占用存储空间少。 查询时通常需要多表关联查询,更难进行索引优化

反范式 反范式的过程就是通过冗余数据来提高查询性能,可以减少表关联和更好进行索引优化 存在大量冗余数据,并且数据的维护成本更高

所以在平时工作中,我们通常是将范式和反范式相互结合使用。


2 索引

首先了解一下什么是索引,索引是对数据库表中一列或多列的值进行排序的数据结构,用于快速访问数据库表中的特定信息。


1、索引的几种类型或分类?

1)从物理结构上可以分为聚集索引和非聚集索引两类:

聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致,即每张表只能有一个聚簇索引,也就是我们常说的主键索引;

非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

2)从应用上可以划分为一下几类:

普通索引:MySQL 中的基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建;

唯一索引:索引列中的值必须是唯一的,但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建;

主键索引:特殊的唯一索引,也成聚簇索引,不允许有空值,并由数据库帮我们自动创建;

组合索引:组合表中多个字段创建的索引,遵守最左前缀匹配规则;

全文索引:只有在 MyISAM 引擎上才能使用,同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。


2、索引的优缺点?

先来说说优点:创建索引可以大大提高系统的性能。

通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

既然增加索引有如此多的优点,为什么不对表中的每一个列都创建一个索引呢?这是因为索引也是有缺点的:

创建和维护索引需要耗费时间,这种时间随着数据量的增加而增加,这样就降低了数据的维护速度。

索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间就会更大。


3、索引设计原则?

选择唯一性索引;

唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。

为常作为查询条件的字段建立索引;

如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。

为经常需要排序、分组和联合操作的字段建立索引;

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。

限制索引的数目;

每个索引都需要占⽤用磁盘空间,索引越多,需要的磁盘空间就越大,修改表时,对索引的重构和更新很麻烦。

小表不建议索引(如数量级在百万以内);

由于数据较小,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。

尽量使用数据量少的索引;

如果索引的值很长,那么查询的速度会受到影响。此时尽量使用前缀索引。

删除不再使用或者很少使用的索引。


4、索引的数据结构?

索引的数据结构和具体存储引擎的实现有关,MySQL 中常用的是 Hash 和 B+ 树索引。

Hash 索引底层就是 Hash 表,进行查询时调用 Hash 函数获取到相应的键值(对应地址),然后回表查询获得实际数据.

B+ 树索引底层实现原理是多路平衡查找树,对于每一次的查询都是从根节点出发,查询到叶子节点方可以获得所查键值,最后查询判断是否需要回表查询.


5、Hash 和 B+ 树索引的区别?

Hash

1)Hash 进行等值查询更快,但无法进行范围查询。因为经过 Hash 函数建立索引之后,索引的顺序与原顺序无法保持一致,故不能支持范围查询。同理,也不支持使用索引进行排序。

2)Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测,如 AA 和 AB 的算出的值没有相关性。

3)Hash 任何时候都避免不了回表查询数据.

4)虽然在等值上查询效率高,但性能不稳定,因为当某个键值存在大量重复时,产生 Hash 碰撞,此时查询效率反而可能降低。

B+ Tree

1)B+ 树本质是一棵查找树,自然支持范围查询和排序。

2)在符合某些条件(聚簇索引、覆盖索引等)时候可以只通过索引完成查询,不需要回表。

3)查询效率比较稳定,因为每次查询都是从根节点到叶子节点,且为树的高度。


6、为何使用 B+ 树而非二叉查找树做索引?

我们知道二叉树的查找效率为 O(logn),当树过高时,查找效率会下降。另外由于我们的索引文件并不小,所以是存储在磁盘上的。

文件系统需要从磁盘读取数据时,一般以页为单位进行读取,假设一个页内的数据过少,那么操作系统就需要读取更多的页,涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问,是数据库里面成本最高的操作之一。

因而这种树高会随数据量增多急剧增加,每次更新数据又需要通过左旋和右旋维护平衡的二叉树,不太适合用于存储在磁盘上的索引文件。


7、为何使用 B+ 树而非 B 树做索引?

在此之前,先来了解一下 B+ 树和 B 树的区别:

B 树非叶子结点和叶子结点都存储数据,因此查询数据时,时间复杂度最好为 O(1),最坏为 O(log n)。而 B+ 树只在叶子结点存储数据,非叶子结点存储关键字,且不同非叶子结点的关键字可能重复,因此查询数据时,时间复杂度固定为 O(log n)。

B+ 树叶子结点之间用链表相互连接,因而只需扫描叶子结点的链表就可以完成一次遍历操作,B 树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引?

B+ 树减少了 IO 次数。

由于索引文件很大因此索引文件存储在磁盘上,B+ 树的非叶子结点只存关键字不存数据,因而单个页可以存储更多的关键字,即一次性读入内存的需要查找的关键字也就越多,磁盘的随机 I/O 读取次数相对就减少了。

B+ 树查询效率更稳定

由于数据只存在在叶子结点上,所以查找效率固定为 O(log n),所以 B+ 树的查询效率相比B树更加稳定。

B+ 树更加适合范围查找

B+ 树叶子结点之间用链表有序连接,所以扫描全部数据只需扫描一遍叶子结点,利于扫库和范围查询;B 树由于非叶子结点也存数据,所以只能通过中序遍历按序来扫。也就是说,对于范围查询和有序遍历而言,B+ 树的效率更高。


8、什么是最左匹配原则?

顾名思义,最左优先,以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

如建立 (a,b,c,d) 索引,查询条件 b = 2 是匹配不到索引的,但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以,因为优化器会自动调整 a,b 的顺序。

再比如 a = 1 and b = 2 and c > 3 and d = 4,其中 d 是用不到索引的,因为 c 是一个范围查询,它之后的字段会停止匹配。

最左匹配的原理

Mysql面试题_数据

上图可以看出 a 是有顺序的(1、1、2、2、3、3),而 b 的值是没有顺序的(1、2、1、4、1、2)。所以 b = 2 这种查询条件无法利用索引。

同时我们还可以发现在 a 值相等的情况下(a = 1),b 又是顺序排列的,所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。

ref 最左匹配原则


9、什么是覆盖索引?

在 B+ 树的索引中,叶子节点可能存储了当前的键值,也可能存储了当前的键值以及整行的数据,这就是聚簇索引和非聚簇索引。 在 InnoDB 中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引。如果没有唯一键,则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询。


10、什么是索引下推?

索引下推(Index condition pushdown) 简称 ICP,在 Mysql 5.6 版本上推出的一项用于优化查询的技术。

在不使用索引下推的情况下,在使用非主键索引进行查询时,存储引擎通过索引检索到数据,然后返回给 MySQL 服务器,服务器判断数据是否符合条件。

而有了索引下推之后,如果存在某些被索引列的判断条件时,MySQL 服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少 MySQL 服务器从存储引擎接收数据的次数。


标签:面试题,范式,字节,存储,查询,索引,Mysql,数据
From: https://blog.51cto.com/u_16358095/8407806

相关文章

  • Mysql数据库查询之模糊查询
    一、什么是模糊查询模糊查询是根据一定的模式匹配规则,查找与指定条件相似或相符的数据。二、模糊查询实操通配符查询1、%表示任意0个或多个字符形式一:select查询字段from表名where字段like’%’代码演示查询结果 形式二:select查询字段from表名where字段like......
  • mac上python安装mysqlclient模块方法
    运行pycharm后,打开命令行窗口确保您已经安装了MySQL,并且包括了MySQL的开发文件。您可以通过Homebrew来安装MySQL:brewinstallmysql#安装mysqlclient之前,先安装openssl:brewinstallopenssl#使用以下命令来安装mysqlclient:envLDFLAGS="-I/usr/local/opt/openssl/i......
  • 每天5道Java面试题(第12天)
    1. 为什么要ArrayList扩容要不是固定值?扩容固定容量,很难决定到底取多少值合适,取任何具体值都不太合适,因为所需数据量往往由数组的客户端在具体应用场景决定。所以依赖于当前已经使用的量*系数,才比较符合实际应用场景。2. Vector为什么是线程安全的集合?Vector的所有方法加上了s......
  • windows安装mysql5.7.24
    下载mysql5.7.24.ziphttps://downloads.mysql.com/archives/community/解压1.安装,管理员权限打开cmd窗口,输入下边命令mysqld--installmysql--defaults-file=D:\mysql-5.7.24-winx64\mysql-5.7.24-winx64\my.ini(注:若出现MSVCR120.dll、MSVCP120.dll缺失,则安装vcredist_x64.exe......
  • mysql 人大金仓 按照日 周 月 年 分组查询
    查询每天的数据myslq:SELECTCOUNT(1)AStotal,DATE_FORMAT(created_time,'%Y-%m-%d')ASdaysFROMtable_testGROUPBYdays;金仓:SELECTCOUNT(1)AStotal,to_char(created_time,'%Y-%m-%d')ASdaysFROMtable_testGROUPBYdays;查询每周的数据mys......
  • 软件测试|MySQL HAVING分组筛选详解
    简介在MySQL数据库中,HAVING子句用于在使用GROUPBY子句对结果进行分组后,对分组后的数据进行筛选和过滤。它允许我们对分组后的结果应用聚合函数,并基于聚合函数的结果进行条件过滤,从而得到我们需要的最终结果集。本文将详细介绍HAVING子句的用法,并提供一些实际示例以帮助大......
  • 软件测试|MySQL中的GROUP BY分组查询,你会了吗?
    MySQL中的GROUPBY分组查询:详解与示例在MySQL数据库中,GROUPBY语句用于将数据按照指定的列进行分组,并对每个分组执行聚合函数操作。这就是的我们可以在查询中汇总数据并生成有意义的结果。本文将深入介绍MySQL中的GROUPBY语句,并提供示例来说明其用法。基本语法在MySQL中,GRO......
  • Mysql免安装版(windows)配置
    1.安装包见附件注意:附件不允许上传分卷包,只能zip后缀,下载后需要将.z01,z02,z03后面的.zip删除,最终附件如下mysql-5.7.38-winx64.z01mysql-5.7.38-winx64.z02mysql-5.7.38-winx64.z03mysql-5.7.38-winx64.zip或者去官网下载 https://dev.mysql.com/downloads/mysql/2.安......
  • MySQL事务
    MySQL事务事务是什么事务是一个操作序列,这些操作要么都做,要么都不做,是数据库环境中不可分割的逻辑工作单位。事务和程序是两个不同的概念,一般一个程序可包含多个事务。在SQL语言中,事务定义的语句有以下三条:1)BEGINTRANSACTION:事务开始。2)COMMIT:事务提交。该操作表示事务成功地......
  • Java Mysql 类型为Long 转 前端String
    一、背景JavaMysql类型为Long转前端会丢失精度,在原先基础上补0000;二、实现1.//@JsonSerialize(using=ToStringSerializer.class)但是对我这里是不生效的@JSONField(serializeUsing=com.alibaba.fastjson.serializer.ToStringSerializer.class)生效三、遇......