Java面试之Mysql

如上图所示，MySQL服务器逻辑架构从上往下可以分为三层：

（1）第一层：处理客户端连接、授权认证等。

（2）第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。

（3）第三层：存储引擎，负责MySQL中数据的存储和提取。MySQL中服务器层不管理事务，事务是由存储引擎实现的。MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyIsam、Memory等。

1.mysql事务是怎么实现的？

事务、锁、MVCC三者之间的关系：事务依靠锁和MVCC实现，MVCC提高了并发度

• 事务的实现逻辑是位于引擎层的
• mysql引入了缓冲池的概念，好处是可以把大量的磁盘I/O转成内存读写，并且把对一个页面的多次修改merge成一次I/O操作（刷脏一次刷入整个页面），避免每次读写操作都访问磁盘，从而大大提升了数据库的性能。

1.1 Redo log（实现持久性）

刷脏的时候一定会保证对应的redo log已经落盘了，也即是所谓的WAL（预写式日志），否则会有数据丢失的可能性。

1.1.1 Redo log相比于直接脏刷的好处

1. 刷脏是随机I/O，但写redo log 是顺序I/O，顺序I/O可比随机I/O快多了，不需要。
2. 刷脏是以数据页（Page）为单位的，即使一个Page只有一点点修改也要整页写入；而redo log中只包含真正被修改的部分，数据量非常小，无效IO大大减少。
3. 刷脏的时候可能要刷很多页的数据，无法保证原子性（例如只写了一部分数据就失败了），而redo log buffer 向 redo log file 写log block，是按512个字节，也就是一个扇区的大小进行写入，扇区是写入的最小单位，因此可以保证写入是必定成功的。

1.1.2 先写redo log还是先修改数据

1. 先记录redo log，后修改data page（WAL当然是日志先写啦）
2. InnoDB是先修改Buffer Pool，后写redo log buffer的。（当修改数据时，在对内存Buffer Pool中的页面进行修改的同时，还会生成redo logrecord，保存在mtr_buf中。在执行mtr_commit函数提交本MTR的时候，会将mtr_buf中的redo logrecord更新到redo log buffer中）

1.1.3 恢复数据的过程

LSN也就是log sequence number，日志的序列号，是一个单调递增的64位无符号整数。redo log和数据页都保存着LSN，可以用作数据恢复的依据。

Checkpoint表示一个保存点，在这个点之前的数据页的修改（log LSN<Checkpoint LSN）都已经写入磁盘文件了。InnoDB每次刷盘之后都会记录Checkpoint，把最新的redo log LSN 记录到Checkpoint LSN 里，方便恢复数据的时候作为起始点的判断。

在任何情况下，InnoDB启动时都会尝试执行recovery操作。在恢复过程中，需要redo log参与，而如果还开启了binlog，那就还需要binlog、undo log的参与。因为有可能数据已经写入binlog了，但是redo log还没有刷盘的时候数据库就奔溃了（事务是InnoDB引擎的特性，修改了数据不一定提交了，而binlog是MySQL服务层的特性，修改数据就会记录了），这时候就需要redo log，binlog和undo log三者的参与来判断是否有还没提交的事务，未提交的事务进行回滚或者提交操作。

• 启动InnoDB时，找到最近一次Checkpoint的位置，利用Checkpoint LSN去找大于该LSN的redo log进行日志恢复。
• 如果中间恢复失败了也没影响，再次恢复的时候还是从上次保存成功的Checkpoint的位置继续恢复。
  

1.1.4 redo log和binlog的区别？

在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。

（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。

（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。

（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元。

1.2 Undo log（实现原子性）

Innodb为每行记录都实现了三个隐藏字段，用来实现MVCC

 1. 6字节的事务ID（DB_TRX_ID ,每处理一个事务，其值自动+1。
 2. 7字节的回滚指针（DB_ROLL_PTR），指向写到rollback segment（回滚段）的一条undo log记录。
 3. 隐藏的ID

如果undo log一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的时在Innodb中存在purge线程，它会查询那些比现在最老的活动事务还早的undo log，并删除它们，从而保证undo log文件不至于无限增长。

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

1.3 MVCC（隔离性）

详细解读MVCC
当前读、快照读、三个隐含字段，undo log read view

2.数据库三大范式？

1. 第一范式（1NF），强调原子性，即列不能够再分成其他几列。
   一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。
2. 第二范式（2NF）。首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
   考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
   因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
   可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。
3. 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
   考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。
   其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
   通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。
4. 第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。.

3.innodb和myisam区别？

2.详细区别
1.直白

4.索引原理？

5.聚簇索引

6.怎么解决幻读问题

7.为什么每个列都不能作为索引？

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

8.读多写少用啥锁，写多读少呢

9.你知道的索引，区别，分别什么时候用，针对B+索引和hash索引

10. 最左前缀则？

11. 男女字段可以建立索引吗?status呢？

12. B树和B+树的区别？减少IO次数？

11.数据库死锁是怎么产生的？

12.mysql回表问题

13.innodb页结构

14.mysql中的log（undolog）

15.insert数据wal技术

16.mysql预估能存多少数据

17.怎么解决幻读 当前读和快照读

读未提交、读已提交、可重复读、串行

1. 脏读：读取某个事务未提交的数据，数据可能会回滚

2. 不可重复度：事务A 先 查询了金额，是200块钱，未提交 。事务B在事务A查询完之后，修改了金额，变成了300, 在事务A前提交了；如果此时事务A再查询一次数据，就会发现钱跟上一次查询不一致，是300，而不是200。这就是不可重复读。强调事务A对要操作的数据被别人修改了，但在不知请的情况下拿去做之前的用途

3. 幻读：事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的，很是诧异，刚刚不是全部修改为已处理嘛，以为出现了幻觉，这就是幻读

18. having

19.查询表行数的时候，用那种方法（count* count 1 count字段）

21.mysql怎么解决幻读的

解决幻读
超哥

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

1. 像串行化一样，直接加锁（间隙锁）

SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。
2. 串行化解决
3. 使用更新的版本控制。维护一个字段作为 updateversion，修改时 updateversion 也作为一个参数传入，在条件语句中添加例如 where id=? and update_version = ? 当然 set 里面要 update_version+1。这样可以控制到每次只能有一个人更新一个版本。

22.查询的关键字包含了索引的关键字，但没有使用索引，是什么原因

1. 如果是联合索引的话，不符合最左原则；
2. mysql会进行一个优化，当发现全表扫面的性能高于索引查询时，直接扫描全表。
3. 条件查询中包含了关键字or，如果or关键字前面键有索引而后面的没有索引，那么最后会走全表扫面，就没必要在去根据索引查找了，反正最后必须得全表扫描。

23.为什么MyISAM的查询比Innodb快

1. 涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。
2. myisam不支持事务，在查询的时候不需要对事务进行判断
3. 对于非主键字段查询，Innodb需要回表，而myIsam通过索引一次就可以找到
4. MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。
5. Mysiam索引树是独立的，通过辅助键检索无需访问主键的索引树。（叶子节点存储了真实数据的地址）

24. 为啥用B+树

B+原理详解

1. trie树死在了开始，无疑AVL树在查询方面是最出色的，但是在删除的时候可能会引起噩梦；
2. 这样看来好像是红黑树最适合咯，虽然他牺牲了一部分查询性能，但是使删除性能在大部分情况保持了常数的时间复杂度。
3. 但是，有一个最重要的问题是，mysql的数据是放在外部存储的，也就是说磁盘IO才是性能瓶颈的关键，所以我们需要的是减少树的深度，所以我们需要更多分叉的树 ,还需要更适合磁盘操作特性的数据结构。
4. B+树是为磁盘或其他直接存取的辅助存储设备而设计的一种数据结构。mysql为什么选取B+树，本质上是因为mysql数据是存放在外部存储的。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

26.聚集索引和非聚集索引：

（1）先说区别，聚集索引是索引和数据的物理存储一致，因此聚集索引只能有一个，其类似与字典中的拼音查找。非聚集索引则是索引和物理位置顺序不同，类似与字典中的按偏旁查找。
（2）应用上来说，对于范围查找来说，选（1）。对于频繁的插入且索引不递增的话，选二。

27.主从复制

主从复制

1. 原因
   （1）主库加锁，锁表，读从库，防止停止服务；
   （2）数据的热备，主宕机，从库接着服务；
   （3）多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
2. （1）主库db的更新事件(update、insert、delete)被写到binlog
   （2）主库创建一个binlog dump thread，把binlog的内容发送到从库
   （3）从库启动并发起连接，连接到主库
   （4）从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
   （5）从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
3. 会存在主从不一致的情况，由于异步，怎么解决呢？
   （1）半同步复制，至少确保一个从节点接收到binlog日志，才返回给客户端。
   （2）多线程读取（每个线程负责一个事务的sql）

28.explain关键字

explain

29.sql关键字执行顺序

在使用 left jion 时，on 和 where 条件的区别如下：

1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是
   否为真，都会返回左边表中的记录。
2. where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时
   已经没有 left join 的含义（必须返回左边表的记录）了，条件不
   为真的就全部过滤掉。

30.慢查询优化

30.1具体 SQL 优化操作

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
4. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
5. in 和 not in 也要慎用，否则会导致全表扫描
6. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
7. 对于内容基本重复的列，比如只有 1 和 0，禁止建立索引，因为该索引选择性
   极差，在特定的情况下会误导优化器做出错误的选择，导致查询速度极大下降

30.2 优化数据库结构

1. 将字段很多的表分解成多个表
   对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
2. 增加中间表
   对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
3. 分解关联查询
   将一个大的查询分解为多个小查询是很有必要的。很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，
   
4. 优化LIMIT查询
   

30.3 超哥总结

1. 多表查询的时候，需要小结果集驱动大结果集。sql优化器已经帮我们做了。
2. 千万级别的数据存入数据库，怎么优化：（1）批量插入 （2）顺序插入
3. 对于列来说，只返回有用的列，不要使用select * ， 对于行来说，尽量使用limit来限制返回行的数量。除此之外可以采用缓存。
4. 重构查询的方式：（1）切分大查询，比如删除数据的时候，采用 某个字段 < 某个值 的条件进行删除，我们应该将这个切分。采用limit来限制每次锁定数据的行，以提高并发性。（2） 分解大连接查询。即将连接查询分解成单条查询语句。这样做的优点：减少锁竞争、应用层面上来说，更具有拓展性
5. 千万级别的表如何优化：优化在多不如加缓存。先说出大概3000的表需要io 3 - 4次，根据log(m)n来计算。 m是底数。 根据换底公式就等于：log n/ log m。 2的25次方3000w，mysql一个页16k，可以存储64个键。一般千万级别的表不建立索引应该不可能。那么就看sql写的是否有问题，是否走了索引。根据28准则，先进行读写分离。如果插入数据很猛的话，就需要考虑分库分表了。
6. 项目中出现了慢查询，怎么优化？（1）加缓存 （2）检查索引，没有索引先别急着加索引，可能否利用原有的联合索引，实在不行加索引（3）加了之后还是比较慢，就说明表太大了，可以考虑分表。

31.一条sql的执行流程

sql的执行顺序
两阶段提交
分为两种，一种为select，一种为增删改查

31.1 首先说一下select

select * from tb_student  A where A.age='18' and A.name='张三';

1. 先检查该语句是否有权限，如果没有权限，直接返回错误信息
2. 查询缓存，命中直接返回，这条sql就是key
3. 分析器提取关键成分（哪个表，哪个字段，条件是啥） 并进行sql语法分析
4. 优化器优化、执行器执行 取到结果 并往缓存中放一份

31.2 update（两阶段提交，保证一致性）

update tb_student A set A.age='19' where A.name='张三';

1. 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
2. 然后拿到查询的语句，把 age 改为19，然后调用引擎API接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo log，此时redo log进入prepare状态，然后告诉执行器，执行完成了，随时可以提交。
3. 执行器收到通知后记录binlog，然后调用引擎接口，提交redo log 为提交状态。
4. 更新完成。

32. 各种联接

1. left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录
2. right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录
3. inner join(等值连接) 只返回两个表中关联字段相等的行