文章详情

登录

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

最新Mysql大厂面试必会的34问题

2018-12-18 15:09

关注

最新Mysql大厂面试必会的34问题

1、mysql的隔离级别 2、MYSQL性能优化 常用5种方式 3、索引详解 1、何为索引，有什么用？ 2、索引的优缺点 4、什么情况下需要建索引？ 5、什么情况下不建索引？ 6、索引的底层数据结构 1、hash表 Hash 冲突 问题 既然哈希表这么快， 为什么MySQL 没有使用其作为索引的数据结构呢？ 2、B 树& B+树 B 树& B+树两者有何异同呢？ 7、Hash索引和B+树索引的区别？ 8、为什么B+树比B树更适合实现数据库索引？ 9、索引有什么分类？ 10、什么是最左匹配原则？ 11、MySQL聚簇和非聚簇索引 。。。。。。 目录

1、mysql的隔离级别

四种隔离级别:

隔离级别脏读不可重复读幻读READ UNCONMMITTED可能可能可能READ COMMITTED不可能可能可能REPRATABLE READ不可能不可能可能SERIALIZABLE不可能不可能不可能

脏读：A，B两事务，A事务会读取到B事务未提交的数据，然后B因为某些原因回滚数据，所以A就读取了B没有提交的数据，也称脏数据。

不可重复读：在A事务中对同一数据两次查询不一致，可能原因是在A事务提交之前B事务对该数据进行了操作

幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

2、MYSQL性能优化

常用5种方式

**修改my.ini文件(永久修改)**

特别注意：查询缓存从MySQL 5.7.20开始已被弃用，并在MySQL 8.0中被删除。、

MyISAM存储引擎：

场景 ：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

优点：MyISAM引擎能提供较高的查询效率，适用于对数据进行频繁查询操作的数据表（InnoDB提供提交、回滚、崩溃恢复能力及并发控制能力，适用于对数据更新操作频率高的数据表）

这里是便于查询可以设置索引，让查询效率变高.

聚合索引按照从左到右的匹配原则。也就是必须先匹配ID才能匹配name查询。

全文检索的查询方式：

SELECT * FROM article WHERE MATCH(title, content) AGAINST("查询字符串")

全文索引只适合MyISAM引擎的数据表。并且只能对英文进行检索

SQL优化的重心是查询优化，查询优化的重心是建立索引。所以查询优化主要是避免出现导致索引失效的查询。

①避免在索引列上出现null。

②不要在索引列上进行算术运算。：select age+1 from user

③避免实现!=或者<>、is null或者is not null、in等可能导致全表遍历的操作。

④模糊查询只能使用右边%。

⑤where语句后尽可能少用小括号、或者不要出现小括号嵌套小括号。

3、索引详解

1、何为索引，有什么用？

索引是存储引擎用于提高数据库表的访问速度的一种 数据结构 。。常见的索引结构有：Hash、B数，B+树。

索引的作用就是相当于目录的作用。打个比方： 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的 那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

2、索引的优缺点

注意： 使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

4、什么情况下需要建索引？

  1. 经常用于查询的字段
  2. 经常用于连接的字段建立索引，可以加快连接的速度
  3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

5、什么情况下不建索引？

  1. where条件中用不到的字段不适合建立索引
  2. 表记录较少
  3. 需要经常增删改
  4. 参与列计算 的列不适合建索引
  5. 区分度不高 的字段不适合建立索引，如性别等

6、索引的底层数据结构

1、hash表

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1）

​ 也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法 。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。

Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

2、B 树& B+树

B 树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是

Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

进行查找操作时，首先在根节点进行二分查找，找到

key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出

key所对应的数据项。

  1. B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  2. B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
  4. innoDB存储引擎中的页大小为16kb，一般主键类型int（占4字节）或者bigint（占8字节），指针类型也一般占4或8字节，也就是说一个页（B+Tree中的一个节点）中大概可以存储16KB/（8B+8B）=1K个键值。也就是说一个深度为3的B+Tree索引跨行业维护10^3 10^3 10^3=10亿条记录（8亿）
  5. 实际情况中，每个节点并不能【填充满，因此在数据库中，B+Tree的高度一般都在2-4层，mysql的innoDB索引引擎在设计的时候是将根节点常驻放在内存中的，也就是说查找某一键值记录最多只需要1-3次I/O操作。注意顶层页常驻内存

7、Hash索引和B+树索引的区别？

8、为什么B+树比B树更适合实现数据库索引？

9、索引有什么分类？

主键索引 ：名为primary的唯一非空索引，不允许有空值。

唯一索引 ：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

组合索引 ：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

普通索引（ 单值索引）

全文索引 ：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

10、什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。如果查询条件为

a = 1 and b > 2 and c = 3，那么a、b个字两段能用到索引，而c无法使用索引，因为b字段是范围查询，导致后面的字段无法使用索引。如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）。

11、MySQL聚簇和非聚簇索引

都是B+树的数据结构

叶子节点不存储数据、存储的是数据的行地址（索引），也就是说根据索引查询到的数据行的位置，再取磁盘查找数据，这个就类似一本树的目录。

在innoDB中，在聚簇索引之上创建的索引是非聚簇索引，非聚簇索引是辅佐索引，像复合索引、前缀索引、唯一索引。辅佐索引的叶子节点存储的不再是行的物理位置，而是主键值，辅佐索引访问数据总是需要二次查找

01、InnoDB中

02、MYISAM中

MYISAM使用的是非聚簇，非聚簇索引的两棵B+树看上去没有太大的区别，节点的结构完全一致，至少2存储的内瑞内容不一样。主键索引B+树的节点的存储了主键，辅佐索引B+树存储了辅佐键，表的数据在独立的地方，这两课B+树的叶子节点都使用地址指向真正的表数据，对表数据来说，这两个键没有任何差别，由于索引树是独立的，通过辅佐键检索无需访问主键的索引树

03、使用聚簇索引的优势

由于行数据和聚簇索引的叶子节点存储在一起，在同一页会有多条行数据，访问同一数据页不同行记录时，已经把页的加载到Buffer（缓冲器），再次访问的时，会再内存中完成访问，不必再访问磁盘，这样主键和行数据是一起载入内存的，找到叶子节点就立刻将行数据返回了，如果按照ID来组织数据，获取数据更快

辅佐索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据发生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发送一次新的IO操作时，可以避免对辅佐索引的维护工作，只需要维护聚簇索引树就好。另外好处就是，因为辅佐索引存放的时主键值，减少辅佐索引占用的存储空间大小

05、为什么主键通常建议使用自增ID

12、什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要 回表 进行二次查询，也就是说查询列要被所使用的索引覆盖。对于

innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

13、索引的设计原则？

14、什么情况下无法利用索引（索引失效）

导致索引失效的情况：

以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引

查询条件使用or连接

15、什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以 保证较高的索引选择性 。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

16、常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是： MyISAM 、 InnoDB 、 MEMORY 、 ARCHIVE 。MySQL 5.5版本后默认的存储引擎为InnoDB。

17、 Mysql锁有哪些、如何理解

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uVWXa7Hj-1650358046863)(https://pizximfzuc.feishu.cn/space/api/box/stream/download/asynccode/?code=MjZhZDY3OTBhMzE1ODE4MGUxZTUyZTEyMjFhZjU0OTJfQVpRSDlaaERqU05HWVNrWlRwWjNzUDNobThvVzN1cUtfVG9rZW46Ym94Y25sVEtmcDFkdXYzZjVjbkJPbGkwUDRlXzE2NTAzNTc2NTY6MTY1MDM2MTI1Nl9WNA)]

18、 Mysql慢查询如何优化

19、 Explain语句结果中查询的各个字段的意思

20、 Innodb如何实现事务的

Innodb通过Buffer Pool，Redo Log，Undo Log来实现事务，以一个update语句为例：

21、 Redis和Mysql如何保证数据一致

先更新Mysql，再更新Redis，如果redis失败，依旧可能数据不一致

先删除redis中数据，再更新mysql，再次查询的时候再更新数据添加到缓存中。（这个方案解决了1的问题）。但是再高并发的情况下效率比较低下，并且还是可能有数据不一致的可能。比如线程1删除后，再更新msql时，但是此时有一个线程2进来查询时，就会将mysql中老数据又查询到redis中。

3、``延迟双删。步骤：先删除redis中的数据，再更新mysql，再隔几百毫秒再删除redis中缓存数据。这样就算在更新mysql时，有其他线程读取mysql时，把老数据读到redis中也会很快被删掉

22、 索引的基本原理

索引是用来快速查询有特定值的记录。如果没有索引，一般来说执行查询时候需要查询全表

索引基本原理： 把无序的数据变成有序的查询

23、 MyISAM和innoDb的区别

MyISAM:

InnoDb：

24、ACID靠什么保证的？

redo log的刷盘会在系统空闲时候进行

25、 什么是MVCC

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁就不冲突了，不同的事务session会看到自己的特定版本的数据，版本链

MVCC只在READ COOMMITED 和REPETABLE READ 两个隔离级别下工作。其他两个隔离级别和MVCC冲突不兼容，因为READ UNCOMMITED 总是读取最新的数据行，而不是 符合当前事务版本的数据行。而SESRIALIZABLE则会对所有的读取行加锁

聚簇索引记录中有两个必要的隐藏列

trx_id：用来存储每次对某条聚簇索引记录进行修改的时候事务id

roll_pointer：每次对哪条索引记录进行修改的时候，都会把老版本写入undo日志中，这个roll_pointer就是保存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获取上一个版本的记录信息。（注意插入操作的undo日志没有这个属性，因为它没有老版本）

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

  1. 用排他锁锁住该行；
  2. 将该行原本的值拷贝到

undo log，作为旧版本用于回滚；

  1. 修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

已提交读和可重复读的区别在于他们生成的ReadView的策瑜不同

无法复制加载中的内容

接下来了解下read view的概念。

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。

在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。

不同隔离级别创建read view的时机不同。

总结 ：InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

26、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化？

在业务系统中，除了使用主键进行查询，还有其他的在测试库上测试其耗时，慢查询的统计一般主要由运维在做，会定期的将业务中的慢查询反馈给我们、慢查询的优化首先需要慢的原因是什么？是查询条件没有命中索引？是load了需要的数据列，还是数据量过大？

所以优化也是针对这三个方向来的。

25、mysql中char与varchar的区别

都是用来存储字符串的，只是他们的保存方式不一样罢了

26、 mysql中的delete , drop 和truncate 区别

delete 是删除一条数据，truncate是将这个表的所有数据都删除，这两种不删除表的结构，可以用

27、关于sql和MySQL的语句执行顺序

1、sql执行顺序

  1. from

  2. join

  3. on

  4. where

  5. group by(开始使用select中的别名，后面的语句中都可以使用)

  6. avg,sum....

  7. having

  8. select

  9. distinct

  10. order by

  11. limit

从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

第一步：首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）。

第二步：接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2 。

第三步：如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3 。

第四步：如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。

第五步：应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4，在这有个比较重要的细节不得不说一下，对于包含outer join子句的查询，就有一个让人感到困惑的问题，到底在on筛选器还是用where筛选器指定逻辑表达式呢？on和where的最大区别在于，如果在on应用逻辑表达式那么在第三步outer join中还可以把移除的行再次添加回来，而where的移除的最终的。举个简单的例子，有一个学生表（班级,姓名）和一个成绩表(姓名,成绩)，我现在需要返回一个x班级的全体同学的成绩，但是这个班级有几个学生缺考，也就是说在成绩表中没有记录。为了得到我们预期的结果我们就需要在on子句指定学生和成绩表的关系（学生.姓名=成绩.姓名）那么我们是否发现在执行第二步的时候，对于没有参加考试的学生记录就不会出现在vt2中，因为他们被on的逻辑表达式过滤掉了,但是我们用left outer join就可以把左表（学生）中没有参加考试的学生找回来，因为我们想返回的是x班级的所有学生，如果在on中应用学生.班级="x"的话，left outer join会把x班级的所有学生记录找回（感谢网友康钦谋__康钦苗的指正），所以只能在where筛选器中应用学生.班级="x" 因为它的过滤是最终的。

第六步：group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

第七步：应用cube或者rollup选项，为vt5生成超组，生成vt6.

第八步：应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

第九步：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.

第十步：应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

第十一步：应用order by子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

第十二步：应用top选项。此时才返回结果给请求者即用户。

2、mysql的执行顺序

1、SELECT语句定义

一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下：

SQL代码