mysql面试题

mysql问题

undo

利用Undo日志也称回滚日志，存储上一个一致性的状态，可以简单理解为记录了与执行sql相反的一条语句。

Undo log 是为了实现事务的原子性，在mysql数据InnoDB存储引擎中，还用Undo log 来实现多版本并发控制简称（MVCC）

redo log

MySQL的innoDB存储引擎，使用了redo log 保证事务的持久性。

当事务提交时，必须先将事务的所有日志写入日志文件进行持久化，就是我们常说的WAL(write ahead log) 机制（这个技术是保障持久性的关键技术，在HBase中扮演重要角色）。这样才能保证断电或宕机等情况发生后，已提交的事务不会丢失。这个叫crash-safe。

Redo log包括两部分 重做日志缓冲redo log buffer 和重做日志文件（redo log file）前者是易失的缓存，后者是持久化的文件。

undo log 和redo log 区别

redo log 日志记录的是数据页的物理变化。服务宕机可以用来同步数据。

而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log 日志文件中新增一条delete 语句，如果发生回滚就执行逆操作。

redo log 保证事务的持久性，undo log 保证事务的原子性，一致性。

事务的写入过程实际拆分

重点关注这个事务提交前，将redo log 的写入拆成两个步骤，prepare和commit,这就是两阶段 提交。

两阶段提交是分布式系统常用机制。MySQL使用了两个阶段提交后，也是为了保证事务的持久性。

Redo log和bingo 有一个共同的数据字段，叫XID，崩溃恢复的时候，会按顺序扫描redo log。

假设 在写入binlog前系统崩溃，那么数据库恢复后扫描redo log，碰到只有prepare ，而没有commit的redo log，就拿着XID去找对应的事务。而且binlog也没写入，所以事务就直接回滚。

假设在写入binlog之后，事务提交前数据库崩溃，那么数据库恢复后顺序扫描redo log，碰到既有prepare，又有commit的redo log，就直接提交，保证数据不丢失。

这个事务要往两个表插入记录，插入数据的过程中，生成的日志都得先写入redo log buffer,等到commit 的时候，才真正把日志写到redo log 文件，（当然，这里不绝对，因为redo log buffer可能因为其他原因被迫刷新到redo log）

而为了确保每次日志都能写入日志文件。在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作，确保写入磁盘。

事务的特性什么?

ACID 分别指的是 原子性，一致性，隔离性，持久性。

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要 么都成功，要么都失败

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰 在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）

sql语句优化

select 语句务必指明字段名称，不要直接使用select * ,还有就是要注意SQL语句避免造成索引失效的写法。如果是聚合查询，尽量用union all 来代替union ,union会多一次过滤，效率比较低。join 优化 能用inner join 就不用left join /right join 若必须使用一定要以小表为驱动 小表要放在外边，内连接会直接对两个表进行优化，优先把小表放外边，大表放里边。

left和right则不会重新调整顺序。

并发事务带来哪些问题

多个事务的并发进行时经常发生的，并发也是必然。有可能导致一些问题。

第一个是脏读

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

第二个是不可重复读

比如在一个事务内多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该数据。那么在第一个事务中的两次读的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读.

第三个是幻读

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

怎么解决并发事务所产生的三大问题 和MySQL默认隔离级别

解决方案是对事务进行隔离。

MySQL支持四种隔离级别

未提交读：不能解决问题，一般项目也不用

读已提交：可以解决脏读，不能解决不可重复读和幻读。

可重复读 ：可以解决脏读和和不可重复读，不能解决幻读。

串行化：能解决三个问题，但是由于是让事务串行执行，性能低。

一般默认使用的隔离级别就是 可重复读。

什么是索引

帮助mysql 高效获取数据的数据结构，主要用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低cpu的消耗。

索引的底层数据结构

mysql默认的存储引擎是InnoDB，它采用的是B+树的数据结构。选B+树的原因是因为第一阶段的数更多，路径短。第二个磁盘读写代价B+树更低，非叶子节点只存储指针。叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表。

B树和B+树的区别

第一 在B树中非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定。

第二 在进行范围查询的时候，B+树的效率高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表。

聚簇索引和非聚簇索引

聚簇索引就是数据和索引放到一起，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引

非聚簇索引就是数据和索引分开了，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引。

回表查询

回表查询就是通过二级索引找到对应的主键值，然后在通过主键值来找到聚集索引中所对应的整行数据，这个过程就是回表。

覆盖索引

指的是select 查询语句使用了索引。在返回队列必须在索引中全部能够找到，如果我们使用id查询，他会直接走聚集索引查询，一次索引扫描。

事务中的隔离性是如何保证的呢

事务的隔离性是由锁和mvvc 实现的

其中mvcc的意思是多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，他的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undolog日志，第三个是readView读视图。

隐藏字段是指:在mysql中给每个表都设置了隐藏字段，有一个是Trx_id(事务id),记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。

readView 解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的。最终访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成readView,如果是rr隔离级别仅在事务中第一次执行快照读时生成readView,后续复用。

MYSQL超大分页怎么处理

超大分页一般都是数据量比较大的时候，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

先分页查询数据的id字段，确定id之后，再用子查询来过滤，只查询这个id列表中的数据就可以。

因为查询id的时候走的覆盖索引，所以效率可以提升很多。

MySQL 主从同步原理

MySQL主从复制的核心就是二进制日志（DDL(数据定义语言) 语句和DML(数据操纵语言) 语句），它的步骤是这样的

第一:主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。

第二:从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log

第三 :从库重做中继日志中的事件，将改变反映它自己的数据。

MySQL分库分表

我们当时的业务是(xxx)，一开始，我们也是单库，后 来这个业务逐渐发展，业务量上来的很迅速，其中(xx)表已经存放了超过 1000万的数据，我们做了很多优化也不好使，性能依然很慢，所以当时就使 用了水平分库。 我们一开始先做了3台服务器对应了3个数据库，由于库多了，需要分片，我 们当时采用的mycat来作为数据库的中间件。数据都是按照id（自增）取模 的方式来存取的。 当然一开始的时候，那些旧数据，我们做了一些清洗的工作，我们也是按照 id取模规则分别存储到了各个数据库中，好处就是可以让各个数据库分摊存 储和读取的压力，解决了我们当时性能的问题