MySQl数据库的一些面试问题

MySQl数据库的一些面试问题

前言

面试了一些公司 也见识到了一些套路
对于数据库考察方面 一般也就是一下这些问题

对于MySQL数据库 主要关注点就在

• 存储引擎
• 事务
• 索引
• 锁

这几个方面

存储引擎

考点一般是问一下MySQL有哪些存储引
你用过哪几种？

mysql提供了多种存储引擎

使用

SHOW ENGINES;

语句可以显示MySQL支持的所有存储引擎

此处使用的MySQL版本是8.0.11

可以看到MySQL 8.0 提供了9种存储引擎，各有用处

字段	内容
Support	表示是否支持
Comment	简要描述此存储引擎
Transaction	事务支持
XA	分布式事务支持
Savepoints	保存点支持

不同的存储引擎用于不同的情况

Memory 引擎

基于散列表的内存存储引擎
主要用于保存临时数据
支持的索引是散列索引

MyIsam 引擎

常用引擎
MySQL 5.1之前的默认存储与年轻。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

MRG_MyIsam 引擎

支持分库分表的MyIsam引擎 ，用于大数据量的情况行 进行分库分表。
可以理解为分表时的MyIsam引擎

CSV 引擎

将数据存储为CSV格式的引擎。
数据以文本方式存储在文件中
不支持索引
不常用的引擎
说实话 我不知道这个引擎有啥用

Federated 引擎

远程连接其他数据库的引擎，用于创建一个远程数据库映射到本地数据库的景象

Performance 引擎

用于监控数据运行情况的引擎
一般数据存储用不到

InnoDB 引擎

常用引擎
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，MySQL 5.1之后，InnoDB是默认的MySQL引擎。

Blackhole 引擎

黑洞引擎，写入的任何数据都会消失，用于记录二进制日志 做复制的中继存储
用于MySQL主从复制的拓扑中

Archive 引擎

归档引擎 用于存储需要归档数据
不支持事务 也不支持索引
特点是存储空间大小无限制

---

从以上内容可知
常用的存储引擎就是
MyIsam和InnoDB
所以一般回答
用过MyIsam和InnoDB即可

知识点

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

5、InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

要点

• 支持ACID事务
• 支持外键
• 支持行级锁
• 支持多版本并发
• 支持数据缓存和索引缓存
• 索引为B+树
• 不支持全文索引

一般问题就在这些里面

MyISAM存储引擎

MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

5、BLOB和TEXT列可以被索引

6、NULL被允许在索引的列中，这个值占每个键的0~1个字节

7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

9、可以把数据文件和索引文件放在不同目录

10、每个字符列可以有不同的字符集

11、有VARCHAR的表可以固定或动态记录长度

12、VARCHAR和CHAR列可以多达64KB

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

要点

• 不支持事务 但是每次查询都是原子的
• 不支持外键
• 不支持行级锁 只支持表级锁
• 不支持数据缓存 只索引缓存
• 索引为B+树
• 支持全文索引

存储引擎的考点就以上这些内容

索引与锁

索引的考点 一般是
索引怎么创建最好
索引失效的情景

MySQL 一共有两种索引 散列索引和B+树索引
Memory引擎支持三类索引
而MyIsam引擎和InnoDB引擎是B+树索引

B+树引擎又分为聚簇索引和非聚簇索引
MyIsam引是非聚簇索引，InnoDB是聚簇索引

由于Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到叶子节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
但Hash索引也存在许多自身的问题
所以常用的存储引擎MyIsam和InnoDB都没有选择Hash索引

我们只需熟记B+树索引的问题即可

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检 索中有非常优异的表现。

Innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用。
myisam中, 主索引和次索引,都指向物理行(磁盘位置).

哪些字段需要创建索引？

• 表的主键
• 直接条件查询的字段
• 经常用于条件约束的字段
• 查询中与其它表关联的字段
• 查询中排序的字段
• 查询中统计或分组统计的字段

什么情况下不需要索引？

• 表记录太少
• 频繁插入、删除、修改的表
• 数据重复且分布平均的表字段

根据以上知识点
回答索引创建的问题
为主要查询的字段添加索引，尽量创建复合索引
根据Mysql的左侧匹配原则 在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减
索引不应包含有NULL值的列

索引失效的情况

1.索引无法存储null值
所以要索引的行 不要允许null值
2.前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
尽量使用 like ‘XX%’
3.如果条件中有or，即使其中有条件带索引也不会使用
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
4.对于多列符复合索引，查询条件不是从第一个索引开始，则不会使用索引
左匹配原则 所以要存在多个索引时 尽量将查询条件中存在索引的字段放在最左侧
5.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

锁的考点一般是
什么情况下是行级锁
什么情况下是表级锁

InnoDB的锁其实还是比较复杂的 一共有七种锁
但实际上面试考不了那么深入
所以只要记住一部分内容即可

InnoDB的是在SQL语句的执行过程中通过扫描索引记录的方式来实现加锁行为的
即InnoDB实际上是给索引加锁

InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

select … from for update语句 强制使用表锁

update…where，delete…where 语句 使用行级锁
insert 在要插入的一行上使用行级锁