Mysql总结

MySQL简介

MySQL的事务隔离级别

聊到事务隔离级别，我们得先聊下事务。所谓事务，就是工作的原子单元，可以去进行提交、回滚。在数据库中的工作，就是执行的sql语句，这些语句可以是单条语句也可以是多条语句。所以，其实就是去保证单条语句或者多条sql语句作为一个原子操作，要么全部成功，要么全部回滚。
事务的四大特性

• A 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• C 一致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B账户转 10 块钱，不管成功与否，A 和B 的总金额是不变的。
• I 隔离性：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。
• D 持久性：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中

事务隔离级别：

• 读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。
• 读已提交，在这种隔离级别下，可能会产生不可重复读和幻读。
• 可重复读，在这种隔离级别下，可能会产生幻读。
• 串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全问题。

InnoDb引擎默认的隔离级别是可重复读，因为他需要保证事务ACID特性中的隔离性特征。实际业务中用的比较多的是读已提交。
在InnoDb里面通过MVCC跟LBCC来解决不可重复读跟幻读问题。
问题： RR通过MVCCC以及LBCC来解决幻读问题，但是RC也有啊，为什么RC没有解决不可重复读跟幻读。
回答：这其实是因为不同的隔离级别在MVCC和LBCC的实现逻辑有点不一样。首先，我们来讲下MVCC的流程，MVCC其实就是查询的时候，会去生成一个readView的class结构，里面会保存一些事务ID数据，比如当前存活的事务ID有哪些，然后跟数据的事务ID进行比对，来决定数据是否展示。
RR 只有在第一次查询的时候会生成一个readView，后面的查询比对的都是第一个查询的时候的结果，所以后面查不到新的值
RC是每次查询都会生成一个新的readView，这样每次查询我就能拿到最新的数据了，也就产生了不可重复读跟幻读问题
而LBCC是通过锁的形式去解决不可重复读跟幻读问题，就是我在改数据同一条或者同一批数据的时候，其他事务不能进行改动。但是RR相比于RC多了一个间隙锁，所谓间隙锁，就是会根据你锁的条件会锁定一个区间，所以没有幻读，但是RC隔离级别下，间隙锁只会用在重复键的检查。

什么是脏读、幻读、不可重复读呢

• 事务 A、B 交替执行，事务 A 被事务 B 干扰到了，因为事务 A 读取到事务 B 未提交的数据, 这就是脏读
• 在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。
• 事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入/删除了数据， 并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读

MVCC（Multiversion Concurrency Control）多版本并发控制；

MVCC

• 事务版本号
• 表的隐藏列
• read view
• undo log（撤销日志）：
  目的：用于事务回滚和多版本并发控制（MVCC）。
  应用场景：当执行事务时，会先在undo log中记录数据的旧版本，以便在事务回滚时可以恢复到事务开始时的状态。
• redo log（重做日志）：
  目的：用于恢复数据页（如果数据页发生故障）或在事务提交后保证事务的持久性。
  应用场景：当执行事务时，会先在redo log中记录数据的新版本，然后才会更新到数据文件中。如果数据库崩溃，可以通过redo log来恢复未应用到数据文件的已提交事务的更改。

LBCC（Lock-Base Concurrency Control）基于锁的并发控制；

LBCC

面试题

MySQL中in和exists的区别

• exists
  查询理解是先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果，来决定主查询的数据结果是否保留
• in是先执行子查询，获得数据后，在主查询中匹配子查询的条件，根据条件来决定主查询的数据是否保留。

因此，假如有A，B两个表，A为主表，B为子查询的表，我们要选择最外层循环小的，也就是，如果 B 的数据量小于 A，适合使用 in，如果 B 的数据量大于 A，即适合选择 exists，这就是 in 和 exists 的区别。

MySQL主从延迟怎么解决

主从复制分了五个步骤进行：

1. 步骤一：主库的更新事件(update、insert、delete)被写到 binlog
2. 步骤二：从库发起连接，连接到主库。
3. 步骤三：此时主库创建一个 binlog dump thread，把binlog 的内容发送到从库。
4. 步骤四：从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relaylog
5. 步骤五：还会创建一个 SQL 线程，从 relay log 里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave 的db

主从同步延迟的原因

一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致，主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法

6. 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置等。
7. 选择更好的硬件设备作为 slave。
8. 把一台从服务器当作为备份使用， 而不提供查询， 那边他的负载下来了， 执行relay log 里面的 SQL 效率自然就高了。
9. 增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载

什么是数据库连接池？为什么需要数据库连接池

首先，数据库连接池是一种池化技术，池化技术的核心思想是实现资源的复用，避免资源重复创建销毁的开销。
而在数据库的应用场景里面，应用程序每次向数据库发起CRUD操作的时候，都需要创建连接，在数据库访问量较大的情况下，频繁的创建连接会带来较大的性能开销。
而连接池的核心思想，就是应用程序在启动的时候提前初始化一部分连接保存到连接池里面，当应用需要使用连接的时候，直接从连接池获取一个已经建立好的链接。连接池的设计，避免了每次连接的建立和释放带来的开销。
数据库连接池的好处：
10. 资源复用
11. 更快地系统响应速度
12. 新的资源分配手段
13. 统一的连接管理，避免数据库连接泄露

一条SQL语句在MySQL中如何执行的

查询语句：
14. 先检查该语句是否有权限
15. 如果没有权限直接返回错误信息
16. 如果有权限，在MySQL8.0版本以前，会先查询缓存。
17. 如果没有缓存，分析器进行词法分析，提取sql语句select等的关键元素。然后判断sql语句是否有语法错误，比如关键词是否正确等等
18. 优化器进行确定执行方案
19. 进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。

InnoDB引擎中的索引策略

20. 覆盖索引
    只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。
21. 最左前缀原则
    MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了(a)、(a,b)、(a,b,c)三个索引。
22. 索引下推
    索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

一条 sql 执行过长的时间，你如何优化，从哪些方面入手？

23. 查看是否涉及多表和子查询，优化 Sql 结构，如去除冗余字段，是否可拆表等
24. 优化索引结构，看是否可以适当添加索引
25. 数量大的表，可以考虑进行分离/分表（如交易流水表）
26. 数据库主从分离，读写分离
27. explain 分析 sql 语句，查看执行计划，优化 sql
28. 查看mysql 执行日志，分析是否有其他方面的问题

MYSQL数据库服务器性能分析的方法命令有哪些?

29. Show status, 一些值得监控的变量值：
30. Bytes_received 和 Bytes_sent 和服务器之间来往的流量。
31. Com_*服务器正在执行的命令。
32. Created_*在查询执行期限间创建的临时表和文件。
33. Handler_*存储引擎操作。
34. Select_*不同类型的联接执行计划。
35. Sort_*几种排序信息
36. Show profiles 是MySql 用来分析当前会话 SQL 语句执行的资源消耗情况

Blob 和text 有什么区别？

37. Blob 值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基
    于列值中的字节的数值。
38. text 值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符
    集的排序规则对值进行排序和比较。

Mysql中有哪几种锁，列举一下？

如果按锁粒度划分，有以下 3 种：
39. 表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现
死锁。
40. 行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发
度高。
41. 页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和
行锁之间，并发度一般

1.共享/排它锁

InnoDB 呢实现了两种标准的行级锁：共享锁（简称 S 锁）、排他锁（简称 X锁）。

• 共享锁：简称为 S 锁，在事务要读取一条记录时，需要先获取该记录的 S 锁。
• 排他锁：简称 X 锁，在事务需要改动一条记录时，需要先获取该记录的 X 锁。

如果事务 T1持有行 R 的 S锁，那么另一个事务 T2请求访问这条记录时，会做如下处理：

• T2 请求 S锁立即被允许，结果 T1和 T2都持有 R 行的 S锁
• T2 请求 X锁不能被立即允许,此操作阻塞

如果 T1持有行 R 的 X锁，那么 T2请求 R 的 X、S锁都不能被立即允许，T2 必须等待T1释放X锁才可以，因为 X锁与任何的锁都不兼容。
S锁和 X锁的兼容关系如下：
X锁和 S锁是对于行记录来说的话，可以称它们为行级锁或者行锁。我们认为行锁的粒度就比较细，其实一个事务也可以在表级别下加锁，我们称之为表锁。给表加的锁，也是可以分为 X锁和 S锁的哈。
如果一个事务给表已经加了 S 锁，则：

• 别的事务可以继续获得该表的 S 锁，也可以获得该表中某些记录的 S 锁。
• 别的事务不可以继续获得该表的 X 锁，也不可以获得该表中某些记录的 X 锁。
  如果一个事务给表加了 X 锁，那么
• 别的事务不可以获得该表的 S 锁，也不可以获得该表某些记录的 S 锁。
• 别的事务不可以获得该表的 X 锁，也不可以继续获得该表某些记录的 X 锁。

2. 意向锁
   什么是意向锁呢？意向锁是一种不与行级锁冲突的表级锁。未来的某个时刻，事务可能要加共享或者排它锁时，先提前声明一个意向。注意一下，意向锁，是一个表级别的锁。
   为什么需要意向锁呢？ 或者换个通俗的说法，为什么要加共享锁或排他锁时的时候，需要提前声明个意向锁呢呢？

因为InnoDB 是支持表锁和行锁共存的，如果一个事务 A 获取到某一行的排他锁，并未提交，这时候事务 B 请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的，因此事务 B 想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。
然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDb 的设计大叔提出了意向锁。
意向锁是如何解决这个问题的呢？ 我们来看下
意向锁分为两类：

• 意向共享锁：简称 IS锁，当事务准备在某些记录上加 S 锁时，需要现在表级别加一个 IS锁。
• 意向排他锁：简称 IX锁，当事务准备在某条记录上加上 X 锁时，需要现在表级别加一个 IX锁。

意向锁解决这个办法的思路：
如果一个事务 A 获取到某一行的排他锁，并未提交,这时候表上就有意向排他锁和这一行的排他锁。这时候事务 B 想要获取这个表的共享锁，此时因为检测到事务 A 持有了表的意向排他锁，因此事务 A 必然持有某些行的排他锁，也就是说事务 B 对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁。

3. 记录锁
   记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10FOR UPDATE，如果 C1 字段是主键或者是唯一索引的话，这个 SQL 会加一个记录锁（Record Lock）记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB 也会隐式的创建一个索引，并使用这个索引实施记录锁。它会阻塞其他事务对这行记录的插入、更新、删除。
   一般我们看死锁日志时，都是找关键词，比如 lock_mode X locks rec but notgap），就表示一个 X 型的记录锁。记录锁的关键词就是 rec but not gap。
4. 间隙锁
   为了解决幻读问题，InnoDB 引入了间隙锁(Gap Lock)。间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
5. 临键锁
   Next-key 锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。说得更具体一点就是:临键锁会封锁索引记录本身，以及索引记录之前的区间，即它的锁区间是前开后闭，比如(5,10]。如果一个会话占有了索引记录 R 的共享/排他锁，其他会话不能立刻在 R 之前的区间插入新的索引记录。
6. 插入意向锁
   插入意向锁,是插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号。 它解决的问题：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。假设有索引值 4、7，几个不同的事务准备插入 5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了 4、7 之间的间隙，但是不阻塞对方因为插入行不冲突。
7. 自增锁
   自增锁是一种特殊的表级别锁。它是专门针对 AUTO_INCREMENT类型的列，对于这种列，如果表中新增数据时就会去持有自增锁。简言之，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

count(*)得区别

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了忽略所有列，用 1 代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是空字符串或者 0，而是表示 null）的计数，即某个字段值为 NULL 时，不统计。

总结：count，其实是一个聚合函数，聚合数据的总数。
不同的存储引擎实现的方式可能又稍许不一样，我们以默认的innoDB存储引擎为例。
其实就是根据where条件去进行扫描，得到扫描结果后，一行一行去判断，如果count括号里的不是null，那么累计值+1，否则不加，最后返回一个累计的总数。所以，括号里面的值在一定程度是会影响性能的。
count(*) ，也就是整条数据，由于整条数据肯定不会为null，所以，专门做了优化，不会去取全部字段。直接扫描多少数据统计数据即可。
count(1) 扫描到一条数据，直接返回一个数字1，不会取扫描的字段，也不会去判断是否为null。
这2个也是我们用得最多的，性能最好的。
但是如果你count(字段)，有2种情况。

• 首先去看字段是否能走索引，如果没索引，那么扫描数据不会走索引树，比有索引的字段慢。
• 字段是否可为null，如果可为null，那么必须要去判断扫描出来的数据是否为null，为null不统计，
  所以不可为null的比可为null的要快。

SQL的执行顺序

• from 左表的名字
• on join的条件
• join的类型 右表的名字
• where
• group by
• Having
• select
• distinct
• order by
• limit

服务器cpu 飙升的话，要怎么处理呢？

CPU是整个电脑的核心计算资源，对于一个应用进程来说，CPU的最小执行单元是线程。导致CPU飙高的原因有几个方面

• CPU上下文切换过多：
  对于CPU来说，同一时刻下每个CPU核心只能运行一个线程，如果有多个线程要执行，CPU只能通过上下文切换的方式来执行不同的线程。上下文切换需要做两个事情
  • 保存运行线程的执行状态
  • 让处于等待中的线程执行
    这两个过程需要CPU执行内核相关指令实现状态保存，如果较多的上下文切换会占据大量CPU资源，从而使得cpu无法去执行用户进程中的指令，导致响应速度下降。在Java中，文件IO、网络IO、锁等待、线程阻塞等操作都会造成线程阻塞从而触发上下文切换
• CPU资源过度消耗：
  也就是在程序中创建了大量的线程，或者有线程一直占用CPU资源无法被释放，比如死循环！CPU利用率过高之后，导致应用中的线程无法获得CPU的调度，从而影响程序的执行效率！

既然是这两个问题导致的CPU利用率较高，于是我们可以通过top命令，找到CPU利用率较高的进程，在通过Shift+H找到进程中CPU消耗过高的线程，这里有两种情况。

• CPU利用率过高的线程一直是同一个
  说明程序中存在线程长期占用CPU没有释放的情况，这种情况直接通过jstack获得线程的Dump日志，定位到线程日志后就可以找到问题的代码。
• CPU利用率过高的线程id不断变化，说明线程创建过多，需要挑选几个线程id，通过jstack去线程dump日志中排查。

最后有可能定位的结果是程序正常，只是在CPU飙高的那一刻，用户访问量较大，导致系统资源不够。

Innodb 的事务与日志的实现方式

innodb 两种日志 redo 和 undo。

• redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。
• Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata 文件里面， 5.6 之后，可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata 之外。

事务是如何通过日志来实现的

• 因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo的 redo， 然后修改数据页，再记数据页修改的redo。 Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。
• 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo 把该事务修改回滚到事务开始之前。
• 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

MySQL一条SQL加锁分析

• 组合一：id 列是主键，RC 隔离级别
• 组合二：id 列是二级唯一索引，RC 隔离级别
• 组合三：id 列是二级非唯一索引，RC 隔离级别
• 组合四：id 列上没有索引，RC 隔离级别
• 组合五：id 列是主键，RR 隔离级别
• 组合六：id 列是二级唯一索引，RR 隔离级别
• 组合七：id 列是二级非唯一索引，RR 隔离级别
• 组合八：id 列上没有索引，RR 隔离级别
• 组合九：Serializable 隔离级别