MySQL面试-1

 InnoDB中ACID的实现

先说一下原子性是怎么实现的。 事务要么失败，要么成功，不能做一半。聪明的InnoDB，在干活儿之前，先将要做的事情记录到一个叫undo log的日志文件中，如果失败了或者主动rollback，就可以通过undo log的内容，将事务回滚。

那undo log里面具体记录了什么信息呢？

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作，使数据回到之前的状态

那隔离性怎么实现呢？

MySQL能支持Repeatable Read这种高隔离级别，主要是锁和MVCC一起努力的结果。 我先说锁吧。事务在读取某数据的瞬间，必须先对其加行级共享锁，直到事务结束才释放；事务在更新某数据的瞬间，必须先对其加行级排他锁，直到事务结束才释放； 为了防止幻读，还会有间隙锁进行区间排它锁定。
然后是MVCC，多版本并发控制，主要是为了实现可重复读，虽然锁也可以，但是为了更高性能考虑，使用了这种多版本快照的方式。
因为是快照，所以一个事务针对同一条Sql查询语句的结果，不会受其它事务影响。

MySQL为什么用树做索引？

一般而言，能做索引的，要么Hash，要么树，要么就是比较特殊的跳表。Hash不支持范围查询，跳表不适合这种磁盘场景，而树支持范围查询，且多种多样，很多树适合磁盘存储。所以MySQL选择了树来做索引。

MySQL锁的分类吧。

MySQL从锁粒度粒度上讲，有表级锁、行级锁。从强度上讲，又分为意向共享锁、共享锁、意向排它锁和排它锁。

间隙锁就是对索引行进行加锁操作，不仅锁住其本身，还会锁住周围邻近的范围区间。间隙锁的目的是为了解决幻影读，但也因此带来了更大的死锁隐患。
比如，一个任务表里面有个状态字段，是一个非唯一索引，有一个任务id，是唯一索引。 一个sql将状态处于执行中的任务设置为等待中，另一个sql正好通过任务id更新在范围内的一条任务信息。那么因为是在不同索引加锁的，所以都能成功。但是最后去更新主键数据的时候，就会死锁。

那要怎么找到MySQL执行慢的语句呢？

我们可以看慢查询日志，它是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句，这个阈值通常默认为10s，也可以按需配置。
Mysql是默认关闭慢查询日志的，所以需要我们手动开启。set global slow_query_log=1

那找到慢语句之后，怎么查看它的执行计划？

使用explain命令，它可以获取到MySQL语句的执行计划 ，包括会使用的索引、扫描行数、表如何连接等信息。 通过这个命令，我们很容易就看出一条语句是否使用了我们预期的索引，并进行相应的调整。

 MySQL如果查询压力太大该怎么办？

如果SQL语句已经足够优秀。那么就看请求压力是否符合二八原则，也就是说80%的压力都集中在20%的数据。 如果是，我们可以增加一层缓存，常用的实现是在MySQL前加个Redis缓存。当然，如果实在太大了，那么只能考虑分库分表啦。

如果是写入压力太大呢？

写缓冲。一般而言可以增加消息队列来缓解。这样做有两个好处，一个是缓解数据库压力，第二个可以控制消费频率。

如果发现线上Insert导致cpu很高，你会怎么解决？

1.查看是不是请求量突然飙升导致，如果是攻击，则增加对应的防护；
2.查看是否因为数据规模达到一个阈值，导致MySQL的处理能力发生了下降；
3.查看二级索引是否建立过多，这种情况需要去清理非必要索引。

Count操作的性能怎么优化？

第一种，是用Redis缓存来计数。每次服务启动，就将个数加载进Redis，这种方案适用于对数据精确度，要求不是特别高的场景。
第二种，为count的筛选条件建立联合索引。这样可以实现索引覆盖，在二级索引表中就可以得到结果，不用再回表，回表可是O(n)次随机I/O呢。这种方案适用于有where条件的情况，并且与其它方案不冲突，可共同使用。 第三种，可以多维护一个计数表，通过事务的原子性，维持一个准确的计数。这种方案适用于对数据精度高，读多写少场景。
你对MySQL分表有了解吗？
随着业务持续扩张，单表性能一定会达到极限，分表是把一个数据库中的数据表拆分成多张表，通过分布式思路提供可扩展的性能。
那你做过的项目中，分表逻辑怎么实现的？
分表逻辑一定是在一个公共的，可复用的位置来实现。我之前做的项目，是实现了一个本地依赖包，即将分表逻辑写在公共的代码库里，每个需要调用服务的客户方都集成该公共包，就接入了自动分表的能力。 优点在于简单，不引入新的组件，不增加运维难度。缺点是公共包更改后每个客户端都需要更新。

如果初期没做分表，已有3000W数据，此时要分库分表怎么做？

最复杂的情况，持续比较大的访问流量下，并且要求不停服。我们可以分几个阶段来操作：
1. 双写读老阶段：通过中间件，对write sql同时进行两次转发，也就是双写，保持新数据一致，同时开始历史数据拷贝。本阶段建议施行一周；
2. 双写双读阶段：采用灰度策略，一部分流量读老表，一部分流量读新表，读新表的部分在一开始，还可以同时多读一次老表数据，进行比对检查，观察无误后，随着时间慢慢切量到新表。本阶段建议施行至少两周； 3. 双写读新阶段：此时基本已经稳定，可以只读新表，为了安全保证，建议还是多双写一段时间，防止有问题遗漏。本阶段建议周期一个月； 4. 写新读新阶段：此时已经完成了分表的迁移，老表数据可以做个冷备；

MySQL都有哪些锁？举出所有例子，各个锁的作用是什么？区别是什么？
1.从数据操作的类型分类
共享锁（S锁）：也称为读锁，对于其他事务而言是可读不可写的。多个事务可以同时持有共享锁，并且共享锁之间不会互斥。
排他锁（X锁）：也称写锁，对于其他事务而言是不可读也不可写的，确保在多个事务中，对同一资源，只有一个事务能写入，并防止其他用户读取正在写入的资源。
2.从锁的粒度分类
2.1 表锁（Table Lock）
锁定整张表。表锁又可分为：表级别的S锁和X锁、意向锁、元数据锁、自增锁
2.11 表级别的S锁和X锁
一般情况下，不会使用到InnoDB中提供的表级别的S锁和X锁，只会在一些特殊情况下，比方说崩溃恢复过程中用到；而在MyISM比较常用。
2.1.2 意向锁
假如有事务T1和T2，T1获取了某表中最后一行记录的行锁（S锁），此时T2想加表锁（X锁），这是不允许的（S锁和X锁互斥），但是T2并不知道该表有没有加过行锁，需要一行一行的去检查，直到最后一行，效率非常低。但是如果有意向锁的话，T1获取行锁时，会额外加上表级别的意向锁，告诉其他事务该表已经有人加过锁了。此时T2只需要检查该表上是否有意向锁即可。
意向锁的作用就是加快表锁的检查过程。
意向锁是由存储引擎自己维护的 ，用户无法手动获取，在为数据行加共享/排他锁之前，InooDB会先获取该数据所在表的对应意向锁。意向锁可分为：
●意向共享锁（IS）：事务有意向对表中的某些行加共享锁（S锁），会自动加上意向共享锁
●意向排他锁（IX）：事务有意向对表中的某些行加排他锁（X锁），会自动加上意向排它锁
2.1.3 自增锁
表中有自增列时，插入记录会使用到自增锁，一个事务持有自增锁时，其他事务的插入语句会被阻塞。了解即可。
2.1.4 元数据锁
在对某个表执行一些诸如ALTER TABLE 、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他事务中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名： Metadata Locks ，简称 MDL）结构来实现的。
MDL主要是为了避免DML和DDL冲突，保证读写的正确性。
2.2 行锁
2.21 记录锁（Record Locks）
记录锁就是行级别的X锁和S锁，仅仅锁住一行记录，分S型记录锁和X型记录锁；
2.22 间隙锁（Gap Locks）
gap锁的提出仅仅是为了防止插入幻影记录而提出的，没有额外其他功能。
2.23 临键锁（Next-Key Locks）
InnoDB默认的锁就是Next-Key locks。
临键锁 = 记录锁 + 间隙锁
在可重复读隔离级别下默认加的行锁就是临键锁，防止幻读。但是有些时候InnoDB会将它优化为记录锁或间隙锁：
2.3全局锁
全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，主要是做全库逻辑备份；备份时应该锁定整个库，保证数据的完整性。
3.从锁的态度分类
分为悲观锁和乐观锁。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的设计思想 。
1.悲观锁（Pessimistic Locking）
假设最坏的情况，每次操作数据都会加上锁，如行锁、表锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。
2.乐观锁（Optimistic Locking）
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，它不采用数据库自身的锁机制，而是通过程序来实现。
在程序上，我们可以采用版本号机制或者CAS机制实现。乐观锁适用于多读和冲突不激烈的应用类型，这样可以提高吞吐量。在Java中通过CAS实现的。
4.死锁
●直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
●发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on ，表示开启这个逻辑。

一条sql更新的执行流程？

1.通过连接器建立连接；

2.解析SQL语句执行计划，交给执行器执行；

3.从磁盘加载page页到内存的buffer pool；

4.记录undolog日志，用于mvcc和回滚操作；

5.更新buffer pool的数据，记录page页的更改；

6.写入redo log，把redo log状态记为prepare；

7.记录bin log逻辑日志，用于主从同步和数据备份；

8.提交事务，将redo log状态改为commit；

MySQL的Server和引擎层有什么区别？为什么要分层？

连接层：用户与MYSQL服务进行TCP链接，校验用户身份，用户权限。

服务层：用户写的SQL语句会到服务层进行解析，生成语法树。优化SQL语句，生成执行计划。

引擎层：真正与磁盘进行交互，对数据进行存储和读取。

区别：

分层架构使得MySQL可以更加容易地扩展新的功能和服务。

MySQL可以更加高效地进行查询处理和事务管理。

MySQL有缓存机制吗？buffer pool和change buffer作用是什么？

Buffer Pool（缓冲池）：

内存中以页（page）为单位缓存磁盘数据，减少磁盘IO，提升访问速度

缓冲池大小默认128M，独立的MySQL服务器推荐设置缓冲池大小为总内存的80%。主要存储数据页、索引页更新缓冲（change buffer）等

Change Buffer（写缓冲）

如果每次写操作，数据库都直接更新磁盘中的数据，会很占磁盘IO。为了减少磁盘IO，

InnoDB在Buffer Pool中开辟了一块内存，用来存储变更记录，为了防止异常宕机丢失缓存，

当事务提交时会将变更记录持久化到磁盘（redo log），等待时机更新磁盘的数据文件（刷脏），

用来缓存写操作的内存，就是Change Buffer

Change Buffer默认占Buffer Pool的25%，最大设置占用50%：