面试必掌握的Mysql的11个问题

高频面经汇总：https://blog.csdn.net/qq_40262372/article/details/116075528

二、数据库:Mysql(原文pdf请至末尾获取)

2.1.Mysql的简介

  Mysql是关系型数据库管理系统(DBMS)，Sql是结构化查询语言。

DBMS负责执行SQL去操作数据库(DB,一般存储在硬盘上)中的数据。

2.2.存储引擎

（1）常用的指令

  Show engines;  查看MySql提供的所有存储引擎

  

可以看出有9个引擎，但是只有InnoDB具有事务。

Show table status like “表名”：查看表的存储引擎

(2)MyISAM和InnoDB的区别

  MyISAM是Mysql的默认数据库引擎(5.5版本之前)。因为不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。5.5版本之后，MySql就引入了InnoDB数据库引擎。

  大多数我们使用InnoDB,但是某些情况下还是可以使用MyISAM，在读密集型。如果你不介意MyISAM崩溃恢复问题的话

两者对比：

1. 是否支持行级锁：MyISAM只有表级锁，InnoDB支持表级锁和行级锁，默认是行级锁。
2. 是否支持事务，数据崩溃后的数据恢复：MyISAM不支持事务与数据恢复，强调的是性能，在某些方面比InnoDB快；InnoDB支持事务，外部键等高级数据库功能。具有事务、回滚和崩溃修复能力的事务安全型表。
3. 是否支持外键：MyISAM不支持，InnoDB支持。
4. 是否支持MVCC(多版本并发控制)：只有InnoDB支持

(3)InnoDB的锁的算法有三种：

   1.Record lock:单个行记录上的锁

   2.Gap Lock:间隙锁，锁定一个范围，不包括记录本身

   3.Next-key lock: record+gap锁定一个范围，包含记录本身

 

2.3索引

  MySQL索引使用的数据库结构主要是BTree索引和哈希索引。绝大多数需求为单条记录查询，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

  MySQL的InnoDB的详解：

  

执行SQL的过程：

执行这一句的时候，操作如下：

先会把数据库表从磁盘取到内存，然后在和语句中的a<1比较(cpu筛选)，然后取出结果。

 

A、InnoDB页表

局部性原理：运行到一个命令只需要取一个数据，但是CPU认为你之后会用到相邻的数据，所以会把这个数据的相邻数据从磁盘一起取出来到内存，多取一些到内存，这样快啊。

直接取一页(从磁盘中取到内存)，一页4KB。

 

InnoDB取的时候取16KB,除了取当前命令的数据还会取相邻数据。

取的时候，不仅取了1，还取了相邻的数据到内存

 

User Records+Free Space： 当插入数据了，User Records 就会多，Free Space就会少

 

B、InnoDB行格式

如果没有定义主键或者唯一索引，那么会多出row_id这个，默认给你定义了。

 

 

    记录头信息：里面包括了下一个节点的地址。

用不同的编码会不同，字符具有不同的字节。ACII一个字节一个字符。UTF-8 一个字符0~4字节

 

65535不行，因为行表格里面有变长字段和NULL标志位，占了三字节。

如果一页都放不下的话，那么格式就这样了(Compact)

C、查询步骤(主键索引：聚簇)

MyISAM:插入什么数据就显示什么数据

InnoDB:插入数据后会自动排序

排好序的好处：

1. a<3,找到了4就不用看之后的了。链表方式查询慢
2. 开始分组，这样可以快速定位在那部分。

 

左边这一行放在PageDireraction(页目录)

 

3.两个页了,又会新开辟一个页，存目录页

 

现在目录页找，然后再去页号找，然后再去页目录找，最后找到数据

简化成以下：

 

这种结构就是B+树！！！为什么要用B+数呢？因为一个节点可以存多个节点，但是其他二叉树只能存两个节点，相同高度，B+数存得更多。而且子节点还是相连接的，容易查询。

叶子节点：数据   非叶子节点：主键+指针  存的很多，比如有二层。

Int:8 指针：6  一页:16KB,  所以一页就会有 16KB/14B=1170对，存了多少对就有多个叶子节点。如果一个叶子节点每行1kb,那么叶子节点也就有16条。所以1170*16 =1W8条数据。    三层的话，1170*1170*16,接近2000W。   首页在缓存，所以去找的时候只需要找两次。

 

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

如果主键不是顺序插入，那么已有的数据库会断开，还有可能会把一条数据挤到下一页去，浪费时间！

如果是自增Id那就直接在后面直接插入即可。主键还要比较小，如果太大了，那么一页存储的行数就会被比较少了。页数就会变多，进而数的高度会变高。查询会又会更慢一些。这样的话起始页一直在变化，肯定不行。MySQL又优化了一下

 

要扩充的时候，先把第一页的内容copy放入一个，然后新开第二页，最后修改起始页的指向地址，这样可以目录页放在缓存这样操作会更快了。

 

辅助索引+回表：

通过辅助索引找出主键，然后再去主键索引的B+树找寻。

全表扫描：

 一行一行查了，不会用索引了！！！

D、索引分类

 1.聚簇索引(主键索引)

2.普通索引(2次,需要回表)

3.哈希索引

 哈希索引在InnoDB引擎中叫做自适应哈希索引，它是由数据库自身根据你的使用情况创建的，并不能认为的干预，所以叫做自适应哈希索引，采用的是哈希表数据结构，所以对于字典类型查询就非常的快，但是对于范围查询就不能为力了。

4.全文索引

倒序索引进行

 

E、JavaGuide

2.4事务

 2.4.1什么是事务？

  事务是逻辑上的一组操作，要么都执行，要么都不执行。

  

2.4.2事务的四大特性

 

ACID：

(1)原子性(Atomicity):事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作业；

(2)一致性(Consistency):执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的;

(3)隔离性(Isolation):并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库时独立的。

(4)持久性(Durability):一个事务被提交之后。它对数据库中的数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

2.4.3并发事务带来那些问题

  在经典的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务(多个用户对同一数据进行操作)。并发虽然是必须的，但可能会导致以下的问题。

 脏读(写(还没提交)读)：读正在修改的数据

  当一个事务正在访问数据库并且对数据进行了修改，而这种修改还没有提交到数据库，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外个事务读取到的这个数据是”脏数据”，依据”脏数据”所作的操作可能不正确的。

丢失修改(两写(覆盖))：

  指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据v后，第二个事务也修改了这个数据。这样第一个事务内的修改内容就会丢失，因为称为丢失修改。

不可重复读(读写(修改)读)：

  指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问了该数据。那么，在第一个事务中的两次读数据之间，由于第二事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此成为不可重复读。

幻读(读写(插入))：读了写了多余数据

  幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据，接着另一个并发事务(T2)插入了一些数据时。在随后的查询中，第一个事务(T1)就会发生多了一些原来不存在的记录，就好像发生了幻觉一样没所以称为幻读。

 

不可重复读和幻读区别：

不可重复读的重点是修改比如读取一条记录后发现某些列的值改变了；幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或者减少了。

 

2.4.4事务隔离级别有那些？默认的是？

SQL标准执行了四个隔离级别：

  READ-UNCOMMITED(读取未提交):最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或者不可能重复读。

  READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  REPEATABLE-READ(可重复读)：对同一个字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  SERIALIZABLE(串行化)：最高的隔离界别，完全服从ACID的隔离级别。所有的事务一次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读、幻读。

Mysql的InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ(可重复读)。

这里需要注意的是：与SQL标准不同的地方在于InnoDB储存引擎在REPEATABLE-READ（可重复读）事务隔离级别下使用的是Next-Key Lock锁算法，因此可以避免幻读的产生，这与其他数据库系统(如SQL Server)是不同的。所以InnoDB的可重复读的隔离级别已经可以达到SQL的串行化隔离级别。

InnoDB在分布式事务的情况下一般会用到SERIALIZABLE(串行化)隔离级别。

2.5大表的优化

  当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化性能措施如下：

限定数据的范围

禁止不带任何限制数据范围条件的查询语句。比如：当用户查询订单历史的时候，我们可以控制在一个月的范围内；

读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

垂直分区

根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

优点：从上图可以看出，表的列变少了，进而Lock数会变少，I/O次数也会变少。简化了表的结构，易于维护。

缺点：从上图可以看出，多出了1的列！主键会出现冗余，需要管理冗余列，并引起Join操作，可以通过在应用层Join。表多了所以会让事务变得复杂。

水平分区

  保持列不变，把行分表，达到分布式的目标。水平拆分可以支持非常大的数据量。

  因为实际得到，当表的行数超过了200W,查询效率就会变低。所以讲多行拆分成不同的表进行处理。

如果水平分区在一台机器上是没效果的，因为始终是一台cpu在进行查询。

  水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度。一般在优化得当的情况千万级数据量没问题。如果非要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

2. 6池化设计思想。什么是数据库连接池？为什么需要数据库连接池？

  这种设计是预设资源 ，解决的问题是避免每次获取资源的消耗。如创建线程的开销,获得远程连接的开销。就好比你去食堂打饭，食堂阿姨会先把饭盛好几份，有人来了就直接给他，而不是来了之后再进行打饭，这样就节约时间了。除了预设资源，池化设计还包括：池子的初始值、活跃值、最大值等。

  数据库的连接本质就是一个Socket的连接。数据库服务端要维护一些缓存和用户信息等。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来对数据进行请求的时候可以重用这些连接。在连接池中，创建连接后，把连接放进池子中，下次连接的时候，直接从池子里取，这样就会减少用户与数据库的之间的连接时间。

2.7分库分表之后，id主键如何处理？

  因为要是分成多个表之后，每个表都是从1开始累加，那肯定是不对的。因为分表的前提是因为一个表太多了，为了查询更快才分出来，所以不可能出现id会相同的，此时需要一个全局唯一的Id来支持。

生成全局Id有下面这几种方法：

  UUID：是通用唯一识别码，是一种软件建构的标准，太长，32个16位数字，查询效率比较低，主键长，空间大，那么每页能存的就少。比较适合用于生成唯一的名字的标识比如文件的名字。

  数据库自增id：两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的id有序，但是需要独立步数数据库实例，成本高，还会有性能瓶颈。

利用redis生成id:性能表较好，灵活方便，不依赖数据库。但是引入了redis的新组件会造成系统更加复杂，可用性降低，增加系统成本。

Twitter的snowflake算法：64位的整数(1位标识+41时间戳+10位节点+12位序列号)

美团的Leaf分布式ID生成系统：能保证全局唯一性、趋势递增、单调递增、信息安全，但也需要关系型数据库、Zookeeper等中间件。美团技术文章：https://tech.meituan.com/2017/04/21/mt-leaf.html

2. 8一条SQL语句在Mysql中如何执行的

  来一个图直接简单明了！！

主要分为Server层与储存引擎：

Server层：

  直接上逻辑~！ 客服端要连接Mysql， 第一步连接器来连接；第二分析器分析客户端给的sql是否正确；第三，优化器选最优查询方面！比如查一个id=1 name=“万小猿”  到底是先筛选那个字段呢？这就是优化器做的。第四，执行器调用存储引擎去执行sql。

储存引擎层：

  主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

查询语句：

   权限校验-》查询缓存-》分析器-》优化器-》权限校验-》执行器-》引擎

              连接器     分析器    优化器      执行器(执行前权限检验是否能执行)

更新语句：

分析器-》权限校验-》执行器-》引擎-》redo log prepare->binlog->redo log commit

Redo是InnoDB引擎独有，binlog是Mysql的日志。InnoDB引擎先执把数据保存在内存中，同时记录在redo log，这个时候redo log进入prepare状态，然后告诉执行器，随时可以进行提交，然后执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为提交状态。

2. 9Mysql高性能优化规范建议

  https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485117&idx=1&sn=92361755b7c3de488b415ec4c5f46d73&chksm=cea24976f9d5c060babe50c3747616cce63df5d50947903a262704988143c2eeb4069ae45420&token=79317275&lang=zh_CN%23rd

 

2. 10一条SQL语句执行得很慢的原因有那些？

一、开始装逼：分类讨论

  1.大多数情况是正常的，只是偶尔会出现很慢的情况。

  2.在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

二、针对偶尔很慢的情况

  一条SQL大多数情况正常，偶尔才能出现很慢的情况，针对这种情况，我觉得这条SQL的书写本来是没有问题的，而是其他原因导致，那会是什么原因呢？

 

1.数据库在刷新4脏页（flush）我也无奈啊

  当我们要往数据库插入一条数据、或者更新一条数据的时候，我们知道数据会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久到磁盘中去，而是把这些更新的记录写入到redo log日记中去，等到空闲的时候，再通过redo log里的日历把最新的数据同步到磁盘中去。

刷新脏页有下面4种场景(后两种不用太关注“性能问题”)：

1.Redolog写满了：因为执行sql的更新语句的时候，如果更新很多的话，很有可能relog里面有很多语句都处于commit状态，还未到从内存提取到硬盘去,所以这边想进入redolog的prepare状态是不行的，要等redolog里面的内容执行完后(SQL语句慢)，才会执行新的更新语句。

2.内存不够：如果一次查询较多的数据，恰好所要查询的数据页不在内存中，需要把查询的内容从硬盘放入内存中，但是内存中是脏页，所以先要把内存中的脏页写入硬盘后，然后把硬盘中所需页取到内存中，去执行这个sql所需要的数据。

3.Mysql认为系统空闲:因为要从redo-prepare到commit，将内存的东西同步到硬盘，

4.MySQL正常关闭的时候：这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读取诗句，启动速度会很快。

 

2.拿不到锁，我能怎么办

  我们要执行表或者行操作的时候，别的进程在进行操作，所以只能等着别人释放，才能操作。

  判断是否在等待锁，我们可以用show processlist这个命令来查看当前的状态。

三、针对一直都这么慢的情况

索引大宝贝没有用上

1.字段没有索引

如果字段没有索引，那么只有走全表扫描，把你等到花儿谢

 

2.字段有索引，但却没有用索引(左边运算了)

  你给下面这个sql的c字段加了索引，但是用不到的。

  

  因为左边做了运算，就不会用上索引！所以想用上索引，就要这样写：

 

3.函数操作导致没有用上索引

  如果我们对字段进行函数操作后，也会导致没有用上索引。

 

呵呵，数据库自己选错索引了

   如我们在进行查询操作的时候：

  

  C的索引可以使主键索引或者是非主键索引。如果是主键索引那么直接查最快。但是如果c是非主键索引的话，那么就有一种可能：系统根据判断误走了全局查询。为什么呢？

因为非主键索引，索引所存的值是主键的值，所以用非主键索引查询他是需要两次查询的，然后系统评估非主键索引快还是全局扫描快。

为什么会有误判呢？

既然要误判，那我们首先先要把判断搞清楚：进行非主键索引的时候，系统会判断这个非主键索引的基数(基数：比如性别是索引，那么基数为2；如果骰子(六面)点数是索引，基数为6)。比如数基数很小，那么索引查出某个字段(比如男)那么也会查询出一半的数，那么索引就没有意义了。

所以，基数越大那么索引查询的效果越好！！然后根据基数的大小，系统选择用索引还是全表扫描。但是系统不可能去扫描全部的基数，只是采样计算！！那么采样就意味着是随机！！刚好这次采样结果是基数很小，所以就选择了全表扫描而不是索引，导致慢起来了。这就是统计随机的结果！

但是，我们可以强制走索引的方式来查询：

 当然我们也可以去查询索引的基数

  

可查询出下列表格：

Cardinality是基数，如果和实际基数差距太大，那么可以用下面语句重新计算一次看看，然后再看索引。

 因为选索引的时候会导致随机计算采样，所以当sql语句有多个索引的时候(mysql只能选择一个索引)，如果我没有指定，那么只能由mysql的优化器自己去决定使用哪个索引，所以有可能mysql采样随机导致选错了索引，查询变慢。

四、总结

  分为两种情况，偶尔比较慢和一直很慢

  偶尔比较慢：

1.数据库在刷新4脏页（flush）

2. 拿不到锁，其他进行将表或者行一直锁着。

一直很慢：

 1.没有用上索引

 2.数据库自己判断，选错索引

2.11手写高质量SQL的30条建议

https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247486461&idx=1&sn=60a22279196d084cc398936fe3b37772&chksm=cea24436f9d5cd20a4fa0e907590f3e700d7378b3f608d7b33bb52cfb96f503b7ccb65a1deed&token=1987003517&lang=zh_CN%23rd

 

JAVA基础高频面试题：

https://blog.csdn.net/qq_40262372/article/details/112556249

 

B站视频讲解MySQL

https://www.bilibili.com/video/BV1FN411R7GJ

https://www.bilibili.com/video/BV1ar4y1K74C/

B站视频讲解如何三个月学习JAVA拿到实习Offer:

https://www.bilibili.com/video/BV1dV411t71K

如果想要在学习的道路上和更多的小伙伴们交流讨论

请加Q群：725936761   

欢迎每一位小伙伴的加入

我们一起朝着目标加油！冲锋陷阵！

想要了解更多请关注微信公众号：万小猿  

回复“Mysql”即可获取原文PDF文件