【9. Mysql常见面试题(一)】

Mysql常见面试题

MySQL 中有哪些存储引擎？

InnoDB 存储引擎

​ InnoDB 是 MySQL 的默认事务型引擎，也是最重要、使用最广泛的存储引擎。 它被设计用来处理大量的短期(short-lived)事务，应该优先考虑 InnoDB 引擎。

MylSAM 存储引擎

​ 在 MySQL 5.1 及之前的版本，MyISAM 是默认的存储引擎。MyISAM 提供了 大量的特性，包括全文索引、压缩、空间函数（GIS）等，但 MyISAM 不支持事 务和行级锁，而且崩溃后无法安全恢复。MyISAM 对整张表加锁，很容易因为表 锁的问题导致典型的的性能问题。

Mrg_MylSAM

​ Merge 存储引擎，是一组 MyIsam 的组合，也就是说，他将 MyIsam 引擎的 多个表聚合起来，但是他的内部没有数据，真正的数据依然是 MyIsam 引擎的表 中，但是可以直接进行查询、删除更新等操作。

Archive 引擎(/ˈɑːrkaɪv/ )

​ Archive 存储引擎只支持 INSERT 和 SELECT 操作，会缓存所有的写并利用 zlib 对插入的行进行压缩，所以比 MyISAM 表的磁盘 I/O 更少。但是每次 SELECT 查 询都需要执行全表扫描。所以 Archive 表适合日志和数据采集类应用，Archive 引 擎是一个针对高速插入和压缩做了优化的简单引擎。

Blackhole 引擎

​ Blackhole 引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做 任何保存。可以在一些特殊的复制架构和日志审核时发挥作用。但这种引擎在应 用方式上有很多问题，因此并不推荐。

CSV 引擎

​ CSV 引擎可以将普通的 CSV 文件(逗号分割值的文件）作为 MySQL 的表来处 理，但这种表不支持索引。因此 CSV 引擎可以作为一种数据交换的机制，非常有 用。

Federated 引擎

​ Federated 引擎是访问其他 MySQL 服务器的一个代理，它会创建一个到远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发 送需要的数据。默认是禁用的。

Memory 引擎

​ Memory 表至少比 MyISAM 表要快一个数量级，数据文件是存储在内存中。 Memory 表的结构在重启以后还会保留，但数据会丢失。

​ Memroy 表在很多场景可以发挥好的作用:

​ 用于查找(lookup）或者映射(mapping）表，例如将邮编和州名映射的表。

​ 用于缓存周期性聚合数据(periodically aggregated data)的结果。

​ 用于保存数据分析中产生的中间数据。

​ Memory 表支持 Hash 索引，因此查找操作非常快。Memroy 表是表级锁， 因此并发写入的性能较低，每行的长度是固定的，可能导致部分内存的浪费。

NDB 集群引擎

​ 使用 MySQL 服务器、NDB 集群存储引擎，以及分布式的、share-nothing 的、 容灾的、高可用的 NDB 数据库的组合，被称为 MySQL 集群（(MySQL Cluster)。

MyISAM 和 InnoDB 的区别是什么?

​ MyISAM 引擎是 5.1 版本之前的默认引擎，支持全文检索、压缩、空间函数 等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用， 而且 MyISAM 不支持外键，并且索引和数据是分开存储的。

​ InnoDB 是基于聚簇索引建立的，和 MyISAM 相反它支持事务、外键，并且 通过 MVCC 来支持高并发，索引和数据存储在一起。

请概述下数据库的范式设计

​ 目前关系数据库有六种范式，常见范式：第一范式：1NF 是对属性的原子性 约束，要求属性具有原子性，不可再分解；第二范式：2NF 是对记录的惟一性约 束，要求记录有惟一标识，即实体的惟一性；第三范式：3NF 是对字段冗余性的 约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

​ 范式化设计优缺点:

​ 优点:可以尽量得减少数据冗余，使得更新快，体积小；缺点:对于查询需要 多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

​ 反范式化:

​ 优点:可以减少表得关联，可以更好得进行索引优化；缺点:数据冗余以及数 据异常，数据得修改需要更多的成本，常见的反范式设计有缓存、冗余等等。

数据库表设计时，字段你会如何选择？

​ 更小的通常更好，应该尽量使用可以正确存储数据的最小数据类型。更小的 数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时 需要的 CPU 周期也更少。但是要确保没有低估需要存储的值的范围。

​ 简单就好，简单数据类型的操作通常需要更少的 CPU 周期。例如，整型比 字符操作代价更低，比如应该使用 MySQL 内建的类型而不是字符串来存储日期 和时间。

​ 尽量避免 NULL，如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化， **因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。**可为 NULL 的列会 使用更多的存储空间，在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时， 每个索引记录需要一个额外的字节。

mysql 里记录货币用什么字段类型好？

​ MySQL 既支持精确类型的存储 DECIMAL 类型，也支持不精确类型存储 FLOAT 和 DOUBLE 类型。对于货币记录，应该选择 DECIMAL 类型，但是 DECIMAL 类型 是以字符串形式存放的，所以性能会有影响。

​ 作为替代方案，可以在数据量比较大的而且要求精度时，虑使用 BIGINT 代 替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

谈谈 MySQL 里的字符串类型

​ MySQL 里的字符串类型有：SET、BLOB、ENUM、VARCHAR、CHAR、TEXT。 VARCHAR 和 CHAR 是两种最主要的字符串类型。VARCHAR 类型用于存储可变长 字符串，大部分的业务情况下比定长类型更节省空间，CHAR 类型是定长的，CHAR 适合存储很短的字符串，或者所有值定长或都接近同一个长度。

​ 使用 BLOB 和 TEXT 则要慎重，一般把 BLOB 或 TEXT 列分离到单独的表中， 还可以对 BLOB 或 TEXT 列使用合成的(Synthetic)索引，就是根据大文本字段的内 容建立一个散列值并单独存储在数据列中，可以通过检索散列值找到数据行。如 果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型。

VARCHAR(M)最多能存储多少数据？

​ 对于 VARCHAR(M)类型的列最多可以定义 65535 个字节。其中的 M 代表该类 型最多存储的字符数量，但在实际存储时并不能放这么多。

​ MySQL 对一条记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加 起来不能超过 65535 个字节。所以 MySQL 服务器建议我们把存储类型改为 TEXT 或者 BLOB 的类型。这个 65535 个字节除了列本身的数据之外，还包括一些其他 的数据，从行记录格式我们可以得知，为了存储一个 VARCHAR(M)类型的列，其 实需要占用 3 部分存储空间：真实数据、真实数据占用字节的长度、NULL 值标 识，如果该列有 NOT NULL 属性则可以没有这部分存储空间。

​ 我们假设表中只有一个 VARCHAR 字段的情况：

​ 如果该 VARCHAR 类型的列没有 NOT NULL 属性，那最多只能存储 65532 个 字节的数据，因为真实数据的长度可能占用 2 个字节，NULL 值标识需要占用 1 个字节。

​ 如果 VARCHAR 类型的列有 NOT NULL 属性，那最多只能存储 65533 个字节 的数据，因为真实数据的长度可能占用 2 个字节，不需要 NULL 值标识。

​ 如果 VARCHAR(M)类型的列使用的不是 ascii 字符集，那 M 的最大取值取决 于该字符集表示一个字符最多需要的字节数。在列的值允许为 NULL 的情况下， gbk 字符集表示一个字符最多需要 2 个字节，那在该字符集下，M 的最大取值就 是 32766（也就是：65532/2），也就是说最多能存储 32766 个字符；utf8 字符 集表示一个字符最多需要 3 个字节，那在该字符集下，M 的最大取值就是 21844， 就是说最多能存储 21844（也就是：65532/3）个字符。

​ 不管如何，请牢记：MySQL 一个行中的所有列（不包括隐藏列和记录头信 息）占用的字节长度加起来不能超过 65535 个字节。

什么是虚拟生成列？

​ 虚拟生成列又叫 Generated Column，是 MySQL 5.7 引入的新特性，就是数据 库中这一列由其他列计算而得。在 MySQL 5.7 中，支持两种 Generated Column，即 Virtual Generated Column（虚拟生成的列）和 Stored Generated Column（存储 生成的列），二者含义如下：

​ 1、Virtual Generated Column（虚拟生成的列）：不存储该列值，即 MySQL 只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上， 而是当读取该行时，触发触发器对该列进行计算显示。

​ 2、Stored Generated Column（存储生成的列）： 存储该列值，即该列值在 插入或更新行时进行计算和存储。**所以相对于 Virtual Column 列需要更多的磁盘 空间，与 Virtual Column 相比并没有优势。**因此，MySQL 5.7 中，不指定 Generated Column 的类型，默认是 Virtual Column

​ 在表中允许 Virtual Column 和 Stored Column 的混合使用

​ 提高效率：由于 mysql 在普通索引上加函数会造成索引失效，造成查询性能 下降，Generated Column（函数索引）刚好可以解决这个问题，可以在 Generated Column 加上索引来提高效率。但是不能建立虚拟列和真实列的联合索引，同时 虚拟列是不允许创建主键索引和全文索引。

​ 创建虚拟生成列的语法：

CREATE TABLE `triangle` ( 
    `a` double DEFAULT NULL, 
    `b` double DEFAULT NULL, 
    `sidec` double GENERATED ALWAYS AS (SQRT(a * a + b * b)) 
) ;
alter table triangle add column sided tinyint(1) generated always as (a*b) virtual;

请说下事务的基本特性

​ 事务应该具有 4 个属性：原子性、一致性、隔离性、持久性。这四个属性通 常称为 ACID 特性。

​ 原子性指的是一个事务中的操作要么全部成功，要么全部失败。

​ 致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状 态。比如 A 转账给 B100 块钱，假设中间 sql 执行过程中系统崩溃 A 也不会损失 100 块，因为事务没有提交，修改也就不会保存到数据库。(不能无中生有,也不能有中生无)

​ 隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。

​ 持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

事务并发可能引发什么问题？(脏读,不可重复读,幻读)

​ 当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。

​ 当事务内相同的记录被检索两次，且两次得到的结果不同时，此现象称为不 可重复读。

​ 在事务执行过程中，事务 2 将新记录添加到正在读取的事务 1 中，导致事务 1 按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，发 生幻读。

​ 事务 2 中是删除了符合的记录而不是插入新记录，那事务 1 中之后再根据条 件读取的记录变少了，在 MySQL 中这种现象不属于幻读，相当于对每一条记录 都发生了不可重复读的现象。

请描述下 MySQL 中 InnoDB 支持的四种事务隔离和 区别

​ read uncommitted：未提交读，可能发生脏读、不可重复读和幻读问题。

​ read committed：提交读，可能发生不可重复读和幻读问题，但是不会发生 脏读问题。

​ repeatable read：可重复读，在 SQL 标准中可能发生幻读问题，但是不会发 生脏读和不可重复读的问题，但是 MySQL 通过 MVCC 基本解决了幻读问题。这 也是 MySQL 的缺省隔离级别。

​ serializable：串行化读，脏读、不可重复读和幻读问题都不会发生。

MySQL 有哪些索引类型

​ 从数据结构角度可分为 B+树索引、哈希索引、以及 FULLTEXT 索引（现在 MyISAM 和 InnoDB 引擎都支持了）和 R-Tree 索引（用于对 GIS 数据类型创建 SPATIAL 索引）；

​ 从物理存储角度可分为聚集索引（clustered index）、非聚集索引 （non-clustered index）；

​ 从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一索引等等。

简单描述 MySQL 各个索引的区别

​ 索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)， 它们包含着对数据表里所有记录的引用指针。

​ 普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访 问速度。

​ 普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包 含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

​ 主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用 于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。(主键不能为null)

​ 索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合 索引。

MySQL 的索引对数据库的性能有什么影响

​ 索引（Index）是帮助 MySQL 高效获取数据的数据结构，所以索引可以极大 的提高数据的查询速度。

​ 但是每建立一个索引都要为它建立一棵 B+树，一棵很大的 B+树由许多数据 页组成会占据很多的存储空间。

​ 而且每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+树索 引，同时这些操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外 的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排 序。如果我们建了许多索引，每个索引对应的 B+树都要进行相关的维护操作， 这必然会对性能造成影响。

为什么 MySQL 的索引要使用 B+树而不是 B 树？

​ 降低树的高度, 提高检索的速度,因为B树的索引节点也存放数据,导致节点的存放指针减少,扇出减少,要存放同样的数据就必须增加树的高度

InnoDB 一棵 B+树可以存放多少行数据？

​ 计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金 的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的 大小是 512 字节，而文件系统（例如 XFS/EXT4）他的最小单元是块，一个块的 大小是 4k，而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页 （Page），一个页的大小是 16K。Innodb 的所有数据文件（后缀为 ibd 的文件）， 他的大小始终都是 16384（16k）的整数倍。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UEIwo0iT-1639669111567)(img/my_note/image-20211216222846595.png)]

​ 数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？ 假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。

​ 对于 B+树而言，只有叶子节点存放数据，非叶子节点存放的是只保存索引 信息和下一层节点的指针信息。一个非叶子节点能存放多少指针？

​ 其实这也很好算，我们假设主键 ID 为 常用的 bigint 类型，长度为 8 字 节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一 个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170 个。

​ 那么可以算出一棵高度为 2 的 B+树，存在一个根节点和若干个叶子节点能 存放 1170*16=18720 条这样的数据记录。

​ 根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放：

​ 1170117016=21902400 条这样的记录。

​ 所以在 InnoDB 中 B+ 树高度一般为 1-3 层，就能满足千万级的数据存储。

​ 那么为什么 MySQL 的索引要使用 B+树而不是 B 树？

​ 而 B 树和 B+树的最大区别就是，B 树不管叶子节点还是非叶子节点，都 会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为 扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变 多，查询性能变低。

HashMap 适合做数据库索引吗？

​ 1、hash 表只能匹配是否相等，不能实现范围查找；

​ 2、当需要按照索引进行 order by 时，hash 值没办法支持排序；

​ 3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了 阿和 b 也可以查询的，如果使用 hash 表，组合索引会将几个字段合并 hash，没 办法支持部分索引；

​ 4、当数据量很大时，hash 冲突的概率也会非常大。

InnoDB 中只有 B+树索引吗？

​ InnoDB 存储引擎不仅仅有 B+树索引，它还支持全文索引、哈希索引。

​ InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那 么就认为是热数据，然后内部自己创建一个 hash 索引，称之为自适应哈希索引 ( Adaptive Hash Index,AHI)。使用的哈希函数采用除法散列方式，其冲突机制 采用链表方式。我们对这个自适应哈希索引能够干预的地方很少，只能设定是否 启用和分区个数。

​ 从 MySQL5.6.x 开始，InnoDB 开始支持全文检索，内部的实现机制就是倒排 索引。但是 MySQL 整体架构上对全文检索支持并不好而且限制很多，比如每张表 只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，所 以如果有大批量或者专门的全文检索需求，还是应该选择专门的全文检索引擎。

什么是密集索引和稀疏索引？

​ 密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里 的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理 排列顺序，所以一个表只能创建一个密集索引。

​ 稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引 只保存了键位信息机器主键。

​ MyISAM 存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索 引，innodb 存储引擎：有且只有一个密集索引。

​ 所以，密集索引就是 innodb 存储引擎里的聚簇索引，稀疏索引就是 innodb 存储引擎里的普通二级索引。

为什么要用自增列作为主键？