数据准备脚本
我把面试问烂了的⭐MySQL面试题⭐总结了一下

数据库存储日期格式时，如何考虑时区转换问题？

  1. 存储方式不一样
    1. 对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
    2. 而对于DATETIME，不做任何改变，基本上是原样输入和输出。
  2. 两者所能存储的时间范围不一样
    1. timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
    2. datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适

  1. create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  2. update_time  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  3. # 两者都能指定默认时间和自动更新
  5. # 时区转换timestamp更适合使用

服务器处理客户端请求 | SQL执行过程

MySQL面试题 - 图1
SQL在MySQL中的流程是：SQL语句→查询缓存→解析器→优化器→执行器

mysql5.7可以设置是否开启查询缓存，8则删除了此功能

MySQL面试题 - 图2

InnoDB与MyISAM的区别

InnoDB MYISAM
事务 支持 不支持
主键 必须有 可以没有
外键 支持 不支持
存储结构 索引和数据同一文件 索引和数据不同文件
存储空间 占用较多(有专用缓冲池用于高速缓冲数据和索引) 占用较少(可被压缩)
查询速度 相对慢 相对快
查询行数 慢 快(有保存总行数)
锁支持 表锁和行锁 表锁
死锁 容易发生 不容易发生
并发性 好 不好
关注点 事务：并发写、事务、更大资源 性能：节省资源、消耗少、简单业务
索引 聚簇索引+非聚簇索引 非聚簇索引
索引data域 数据或主键值 数据地址

B树和B+树有什么区别，为什么选择用B+树？

b+树和b树的区别

  1. b+树中1有k个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1。
  2. b+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）
  3. b+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
  4. b+树中所有关键字都在叶子节点出现，叶子节点构成一个有序键表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

MySQL索引为什么使用的是b+树

  1. 查询效率更稳定
  2. 非叶子节点不存数据，页可以存储更多的关键字，结构更矮胖，效率更高
  3. 范围查找上b+树效率更高，因为叶子节点上有所有的数据，而且是排号序的

MySQL面试题 - 图3MySQL面试题 - 图4

B+树的存储能力如何？为何说一般查找行记录，最大只需要1~3次磁盘io？

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节）或BIGINT(占用8个字节）)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3。也就是说一个深度为3的B+Tree索引可以维护10^310^310^3= 10亿条记录。(这里假定一个数据页也存储10^3条行记录数据了)
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/o操作。

索引概述

为什么要使用索引

减少磁盘io的次数，加快查询效率。

索引的定义

索引是帮助数据库快速查找数据的数据结构

索引是在存储引擎层实现的

索引的优缺点

  1. 优点
    1. 降低数据库的io成本
    2. 通过创建唯一索引，可以保证数据库表中的每一行的数据唯一性
    3. 加速表与表之间的连接。即对于有依赖关系的子表与父表联合查询时，可以提高查询速度。
    4. 减少查询中分组和排序的时间，降低cpu的消耗

  2. 缺点

    1. 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加
    2. 索引需要占用磁盘空间
    3. 降低表的更新速度。对表中数据进行增加修改删除时，索引也要动态维护

      聚集索引与非聚集索引的异同，何时使用？

      聚簇索引和非聚簇索引的区别：

  3. 聚簇索引非叶子节点不存储数据记录，非聚簇索引则存储

  4. 聚簇索引叶子节点包含了所有的非叶子节点，非聚簇索引则不包含。注意他们都是b+树的结构！！！
  5. 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个

聚簇索引和非聚簇索引的相同点：
都是B+树结构

聚集索引、非聚集索引、联合索引、索引真实存储、hash索引

聚簇索引

特点：

  1. 使用记录的主键值的大小进行记录和排序，包含3层含义：
    1. 页内记录按照主键的大小顺序排成一个单向链表
    2. 各个存放用户记录的页也是根据页中的记录的主键大小顺序排成一个双向链表
    3. 存放目录项记录的页分为不同的层次，在同一层次的页是根据页中目录项记录的主键大小顺序排成一个双向链表
  2. B+树叶子节点存放完整的用户记录

优点：

  1. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
  2. 聚簇索引对于主键的排序查找和范围查找速度非常快
  3. 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：

  1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
  2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
  3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

MySQL面试题 - 图5

非聚集索引

回表：我们根据绿色列大小排序的B+树只能确定我们要查找的记录的主键值，如果我们项根据该列找到完整的用户记录的化，仍需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据绿色列查询一条完整的用户记录需要用到2棵B+树。
MySQL面试题 - 图6

联合索引

让b+树根据c2和c3列建立联合索引包含两层含义：

  1. 先把各个记录和页按照c2进行排序
  2. 在记录的c2列相同的情况下采用c3进行排序

为c2和c3建立联合索引的示意图如下：
MySQL面试题 - 图7
注意：

  1. 每条目录项记录都由c2,c3,页号这三部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序
  2. b+树叶子节点出的用户记录由c2,c3和主键c1组成。

  3. 以c2和c3列的大小为排序规则建立的b+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不一样的

    索引真实存储

  4. 根页面位置万年不动
    我们前边介绍B+树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上B+树的形成过程是这样的:

    • 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
    • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
    • 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者b中，而根节点便升级为存储目录项记录的页。

      这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。
      由根节点裂变而来

  5. 聚簇索引：非叶子节点上面也存储了主键的值

    hash索引

    等值查找快，范围查找、排序慢。Memory引擎支持，其他不支持

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针

MySQL 索引使用有哪些注意事项呢？

索引适合哪些场景

  1. where字段很频繁
  2. order by、group by的字段

  3. 外键关系建立索引

    Innodb为什么要用自增id作为主键？

    如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。

    索引不适合哪些场景

  4. 数据量少的不适合加索引

  5. 更新比较频繁的也不适合加索引

  6. 过滤性低的字段不适合加索引（如性别）

    索引哪些情况会失效

  7. 查询条件包含or，可能会导致索引失效（其中有一个没有索引）

  8. like通配符会导致索引失效。注意：”ABC%” 会走range索引，”%ABC” 索引才会失效，实在要用可以用覆盖索引弥补。
  9. 违反最左前缀原则：联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。注意索引列的顺序优化调整。
  10. 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效
  11. 索引字段上使用is null， is not null，可能导致索引失效
  12. 在索引列上做任何操作（计算、函数、手动或者隐式类型转换）
  13. 索引列范围查询，列本身索引可能失效，右边的列索引失效，所以范围查询需靠后

  14. 如果mysql认为全表扫面要比使用索引快，则不使用索引

    索引优化原则

  15. 索引建立在选择性好，过滤性强的列上。复合索引中选择性强的列放到前面（保证只是用了部分索引的查询也能快速查找到数据）。

  16. 尽量使用全值匹配（把建的所有索引都用上）
  17. 索引建立符合最佳左前缀
  18. 尽量使用覆盖索引（索引列包含查询列，不用回表查询）
  19. 分页使用延迟关联
  20. 前缀索引和索引选择性 select count(DISTINCT left(code,12))/count(*) from sys_area;此时不能使用覆盖索引、排序、分组
  21. 后缀索引（存储的时候将值反向存储，然后加上前缀索引）
  22. 删除冗余索引，见下。 ```shell select from t_user where name = ? select from t_user where name = ? and phoneNum = ? create index index_name on t_user(name) create index index_name_phoneNum on t_user(name,phoneNum)

这种做法是错误的，根据最左匹配原则，两条查询都可以走index_name_phoneNum索引，index_name索引就是冗余索引

  1. <a name="yxcuc"></a>
  2. ## 查询优化
  4. 1. 小表驱动大表
  5. 1. 主查询数据集大用in,否则用exists
  6. 1. 单表：区间查找时右边失效，可以跳过该字段建立索引
  7. 1. 双表：左连右建，右连左建
  8. 1. 三表：a left join on b left join on c,分别在b和c上建立单索引
  9. 1. 小表驱动大表+左连右建：例如a表数据量小，b表数据量大，则a left join b,b上建立索引，或者b right join a,b上建立索引
  10. 1. 反范式化优化（允许存在少量冗余，以空间换取时间。范式化要求少冗余）。比如原来通过两个表关联查询，第一个查了3个字段，第二个1个字段，则考虑将这个字段冗余到第一个表中，这样只用查询一个表。
  11. <a name="qoq71"></a>
  12. # 概念理解
  13. <a name="CO01h"></a>
  14. ## 覆盖索引
  15. SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据（即回表查询）
  16. <a name="ydMUF"></a>
  17. ## 全值匹配
  18. 过滤条件中用到了所有的索引字段
  20. 注意：<br />一个表中建立索引（x,y,z）
  22. 1. `where x='' and y='' and z=''` 与`where z='' and x='' and y=''` 等效，索引均有效，优化器会自动调整顺序。
  23. 1. `where z>'' and x='' and y=''`会被自动调整顺序为`where x='' and y='' and z>''`，排序字段是最后一个列，此时可以使用联合索引
  24. 1. `where z='' and y='' and x>''`会被自动调整顺序为`where x>'' and y='' and z=''`，排序字段是第一个列，此时联合索引失效
  25. 1. `where y>'' and z='' and x=''`会被自动调整顺序为`where x='' and y>'' and z=''`，排序字段是第一个列，此时只能使用联合索引的一部分。**TODO->key_len的计算查看哪些列用到了索引**
  26. <a name="BmzQZ"></a>
  27. ## key_len的计算
  28. 参数：
  30. - 数据类型：varchar->+2 char->+0
  31. - 字符集：utf8mb3->3个字节，utf8mb4->4个字节 
  32. - 是否为空：null->+1      not null->+0
  33. - int: 4
  35. 例如varchar(50):50*3+2+0=152<br />作用：判断符合索引是否被充分使用
  36. <a name="JkYTK"></a>
  37. # 日常工作中你是怎么优化SQL的？
  39. 1. 表结构优化，主要是数据类型
  40. 1. 避免返回不必要的字段
  41. 1. SQL查询优化
  42. 1. 索引优化
  43. 1. 反范式优化（增加冗余字段）
  44. 1. 加缓存redis
  45. 1. 主从架构读写分离，提高读性能
  46. 1. 分库分表
  47. 1. MySQL服务器优化（内存、磁盘、处理器、MySQL**参数优化**）
  48. 1. 冷热数据分离
  49. <a name="KYyqM"></a>
  50. # 一条sql执行过长的时间，你如何优化，从哪些方面入手？
  52. - 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
  53. - 优化索引结构，看是否可以适当添加索引
  54. - 数量大的表，可以考虑进行分离/分表（如交易流水表）
  55. - 数据库主从分离，读写分离
  56. - explain分析sql语句，查看执行计划，优化sql
  57. - 查看mysql执行日志，分析是否有其他方面的问题
  58. <a name="BnQKG"></a>
  59. # 创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
  60. 查询执行计划：possilbe_key，key，key_len
  62. 1. id：编号
  63.    1. 相同从上往下执行
  64.    1. 不同先大后小
  65. 2. select_type：查询类型
  66.    1. simple、primary、subquery、derived、union、union result
  67. 3. type：类型
  68.    1. system、const、ref_eq、**ref**、**range**、index、all
  69. 4. table：表
  70. 4. possible_keys：可能用到的索引
  71. 4. key：实际用到的索引
  72. 4. key_len：判断复合索引是否被充分利用
  73.    1. 数据类型：varchar->+2 char->+0
  74.    1. 字符集：utf-8->3个字节 
  75.    1. 是否为空：null->+1 not null->+0
  76.    1. 本身的长度：varchar(?)
  77.    1. 例如varchar(50):50*3+2+0=152
  78. 8. ref：表之间的引用
  79. 8. rows：通过索引查询到的数据量
  80. 8. extra：额外的信息
  81. <a name="bKcUK"></a>
  82. # limit 1000000 加载很慢的话，你是怎么解决的呢？
  83. 表`user(id,name,address);`主键id<br />原始查询：<br />`select id,name,address from user order by name limit  1000000,10;`<br />添加 name 索引之后比较查询：<br />1、`select id,name from user order by name limit  1000000,10;`索引name为非聚簇索引，叶子节点存储了name列的值和主键值，刚好也只查询id,name这两个字段，符合覆盖索引。查询效率提高。<br />2、`select id,name,address from user order by name limit  1000000,10;`相比于1，查询字段增加了address，不能在name 索引树的叶子节点直接取到所有数据，需要再次用主键值到聚簇索引查询 address 字段。效率比较低。<br />**方案一**：如果id是连续的且根据主键id排序，可以这样，返回上次查询的最大记录(偏移量)，再往下limit<br />`select id,name from employee where id>1000000 limit 10.`<br />**方案二**：在业务允许的情况下限制页数：<br />建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。<br />**方案三**：order by + 索引（name）
  85. 1. 覆盖索引`select id,name from user order by name limit  1000000,10;`
  86. 1. 关联查询`select s.id,name,address from user s,(select id from user order by name limit 1000000,10) a where s.id=a.id;`
  87. 1. 延迟关联`select s.id,name,address from user s left join (select id from user order by name limit 1000000,10) a on s.id=a.id;`
  88. <a name="vvEJi"></a>
  89. # 如何选择合适的分布式主键方案呢？
  91. - 数据库自增长
  92. - 数据库号段模式
  93. - UUID
  94. - 雪花算法
  95. - Redis生成ID
  96. - 利用zookeeper生成唯一ID
  97. <a name="E40Tz"></a>
  98. # 事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
  99. **InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别**
  101. 1. 读未提交
  102.    1. **脏读**
  103. 2. 读已提交（互联网最常用的隔离级别）
  104.    1. **幻读**（其他事务新增/删除提交之后，当前事务可以看到）
  105.    1. **不可重复读**（其他事务修改提交之后，当前事务可以看到）
  106. 3. 可重复读（MySQL默认的隔离级别）
  107.    1. 解决了不可重复读问题
  108.    1. 解决了幻读问题，但某些情况会出现幻读->事务B：新增id=6操作提交，事务A修改id=6，事务A查询此时会出现幻读的情景！
  109. 4. 串行化
  110. <a name="bNjrD"></a>
  111. # 隔离级别的实现原理
  113. 1. 读未提交，采取的是读不加锁原理
  114.    1. 事务读不加锁，不阻塞其他事务的读和写
  115.    1. 事务写阻塞其他事务写，但不阻塞其他事务读；
  116. 2. 串行化
  117.    1. 读加共享锁，写加排他锁，读写互斥。如果有未提交的事务正在修改某些行，所有select这些行的语句都会阻塞
  118. <a name="N4Bbi"></a>
  119. ## [MVCC实现原理](https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html)
  120. <a name="fOYBb"></a>
  121. ### mvcc工作在哪些隔离级别？
  122. **读已提交**和**可重复读**通过MVCC来实现隔离级别。
  123. > 使用 `READ COMMITTED` 和 `REPEATABLE READ` 隔离级别的事务，都必须保证读到 `已经提交了的 事务`修改 过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问 题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。
  125. <a name="Gt726"></a>
  126. ### mvcc实现依赖于哪些？
  127.  MVCC 的实现依赖于：隐藏字段（事务id、回滚指针）、Undo Log版本链、Read View读视图。
  128. <a name="ybwYe"></a>
  129. ### mvcc的`ReadView`是什么？
  130. 这个`ReadView`中主要包含4个比较重要的内容，分别如下：  
  132. 1.  `creator_trx_id` ，创建这个 Read View 的事务 ID。
  133. >  说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为 事务分配事务id，否则在一个只读事务中的事务id值都默认为0。    
  135. 2.  `trx_ids` ，表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表 。  
  136. 2.  `up_limit_id` ，活跃的事务中最小的事务 ID。  
  137. 2. `low_limit_id`，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系 统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。  
  138. >  注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1， 2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时， trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。  
  140. <a name="FgB1N"></a>
  141. ### ReadView的规则  
  143. 1. 如果被访问版本的trx_id属性值与ReadView中的 `creator_trx_id` 值相同，意味着当前事务在访问 它自己修改过的记录，所以该版本可以被当前事务访问。 
  144. 1. 如果被访问版本的trx_id属性值小于ReadView中的 `up_limit_id` 值，表明生成该版本的事务在当前 事务生成ReadView前已经提交，所以该版本可以被当前事务访问。 
  145. 1. 如果被访问版本的trx_id属性值大于或等于ReadView中的 `low_limit_id` 值，表明生成该版本的事 务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  146. 1.  如果被访问版本的trx_id属性值在ReadView的 `up_limit_id` 和` low_limit_id` 之间，那就需要判 断一下trx_id属性值是不是在` trx_ids` 列表中。 
  147.    1. 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。 
  148.    1. 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问  
  149. <a name="FvgK4"></a>
  150. ### MVCC整体操作流程
  152. 1.  首先获取事务自己的版本号，也就是事务 ID； 
  153. 1.  获取 ReadView； 
  154. 1. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较； 
  155. 4.  如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照； 
  156. 4.  最后返回符合规则的数据。 
  158. 在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次 Read View。  <br />当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会 获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View
  159. <a name="qXnUH"></a>
  160. ### MVCC如何幻读的？
  162. - mvcc在可重复读时，读视图只读取一次就可以了，后面新增修改的事务id均不会添加到活跃事务集合中，相当于新插入/删除的内容对于此次读来讲，都不可见，仍然能不多不少地读取到之前本次事务中的数据。这样同时解决了不可重复读和幻读。
  163. - 相反地，在读已提交的隔离级别下，每次读都会重新生成视图，所以后面新增/修改的事务id会添加到活跃事务id集合中，一经提交，本事务就可以看到。因此存在不可重复读和幻读的情况。
  164. <a name="FyVd0"></a>
  165. # select for update有什么含义，会锁表还是锁行还是其他？
  166. select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引/主键啦。 没用索引/主键的话就是表锁，否则就是是行锁。
  167. <a name="GeNMy"></a>
  168. # 在高并发情况下，如何做到安全的修改同一行数据？
  169. ```shell
  170. select @@transaction_isolation;
  171. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  172. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  173. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  174. SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  176. https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

使用悲观锁
悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如，可以使用select…for update
select * from User where name=‘jay’ for update
以上这条sql语句会锁定了User表中所有符合检索条件（name=‘jay’）的记录。本次事务提交之前，别的线程都无法修改这些记录。
疑问： MySQL 里面 select for update的机制和 可重复读 都能保证A事务修改提交之前B事务不能进行修改操作，这是什么原理大佬能给解释一下吗？

使用乐观锁
乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现
地址

数据库的乐观锁和悲观锁

悲观锁
悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。

乐观锁
乐观锁的“乐观情绪”体现在，它认为数据的变动不会太频繁。因此，它允许多个事务同时对数据进行变动。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？

  1. 优化步骤
    1. 开启慢查询
    2. 执行计划分析
    3. 优化查询、索引

      MySQL事务得四大特性以及实现原理

      原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。(undolog)
      一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。(一致性由其他三个特性保证)
      隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。(MVCC)
      持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。（redolog，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复）

      如果某个表有近千万数据，CRUD比较慢，如何优化？（说一下大表查询的优化方案）

      索引优化

      优化表结构，当然还有所以sql优化、索引优化等方案~

      缓存优化

      redis、memcached、jvm本地缓存

      主从复制

      主从复制，读写分离，提高读性能

      分库分表

      某个表有近千万数据，可以考虑优化表结构，分表（水平分表，垂直分表），当然，你这样回答，需要准备好面试官问你的分库分表相关问题呀，如

分表方案（水平分表，垂直分表，切分规则hash等）
分库分表中间件（Mycat，sharding-jdbc等）
分库分表一些问题（事务问题？跨节点Join的问题）
解决方案（分布式事务等）

如何写sql能够有效的使用到复合索引？

  • 符合最左前缀

    mysql中in 和exists的区别

    in后面跟小表，exists后面跟大表。依据：小表驱动大表。
    in操作先执行in后面的子查询，然后在主查询里面过滤；exists先主查询执行，然后再去匹配exists后面的过滤查询。

    数据库自增主键可能遇到什么问题？

    分库分表可能出现主键重复

    数据库中间件了解过吗，sharding jdbc，mycat？

    sharding-jdbc目前是基于jdbc驱动，无需额外的proxy，因此也无需关注proxy本身的高可用。 Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而 Sharding-JDBC 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的。

    MySQL的主从延迟，你怎么解决？

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

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

    主从同步延迟的原因

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

    主从同步延迟的解决办法

    参考连接
  1. 短时间延迟或业务要求不严格一致，忽略
  2. 强制读主库（读写都在主库，需要主库高可用），使用缓存来增强读性能
  3. 选择性读主库，将[库名、表名、记录]缓存起来，假设延迟1s，那么缓存失效时间设为1s，读请求能在缓存中找到，说明可能还没同步到从库，此时需要读主库。读请求在缓存中没找到，说明数据已经同步，可以读从库。

    分库分表的设计

    分库分表方案

    水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
    垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
    水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中
    垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中
    分库分表.svg

    常用的分库分表中间件

  • sharding-jdbc

  • Mycat

    分库分表可能遇到的问题

  • 事务问题：需要用分布式事务啦

  • 跨节点Join的问题：解决这一问题可以分两次查询实现
  • 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
  • 数据迁移，容量规划，扩容等问题
  • ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID

  • 跨分片的排序分页问题（后台加大pagesize处理？）

    MySQL 遇到过死锁问题吗，你是如何解决的？

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

    连接池基本原理：
    数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

    应用程序和数据库建立连接的过程

  • 通过TCP协议的三次握手和数据库服务器建立连接

  • 发送数据库用户账号密码，等待数据库验证用户身份
  • 完成身份验证后，系统可以提交SQL语句到数据库执行

  • 把连接关闭，TCP四次挥手告别。

    数据库连接池好处

  • 资源重用 (连接复用)

  • 更快的系统响应速度

  • 新的资源分配手段 统一的连接管理，避免数据库连接泄漏

    Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

  • B+树可以进行范围查询，Hash索引不能。

  • B+树支持联合索引的最左侧原则，Hash索引不支持。
  • B+树支持order by排序，Hash索引不支持。
  • Hash索引在等值查询上比B+树效率更高。

  • B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询

    说一下数据库的三大范式

  • 第一范式：数据表中的每一列（每个字段）都不可以再拆分。

  • 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
  • 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键

    主从复制binlog格式有哪几种？有什么区别？

    ①STATEMENT，基于语句的日志记录，把所有写操作的sql语句写入 binlog （默认）
    例如update xxx set update_time = now() where pk_id = 1，这时，主从的 update_time 不一致
    优点：
    成熟的技术。
    更少的数据写入日志文件。当更新或删除影响许多行时，这将导致 日志文件所需的存储空间大大减少。这也意味着从备份中获取和还原可以更快地完成。
    日志文件包含所有进行了任何更改的语句，因此它们可用于审核数据库。

缺点：
有很多函数不能复制，例如now()、random()、uuid()等

②ROW，基于行的日志记录，把每一行的改变写入binlog，假设一条sql语句影响100万行，从节点需要执行100万次，效率低。
优点：可以复制所有更改，这是最安全的复制形式
缺点：如果该SQL语句更改了许多行，则基于行的复制可能会向二进制日志中写入更多的数据。即使对于回滚的语句也是如此。这也意味着制作和还原备份可能需要更多时间。此外，二进制日志被锁定更长的时间以写入数据，这可能会导致并发问题。

③MIXED，混合模式，如果 sql 里有函数，自动切换到 ROW 模式，如果 sql 里没有会造成主从复制不一致的函数，那么就使用STATEMENT模式。（存在问题：解决不了系统变量问题，例如@@host name，主从的主机名不一致）

Mysql主从复制方式？有什么区别？

InnoDB内存结构包含四大核心组件

  1. 缓冲池(Buffer Pool)：缓存表数据与索引数据（热数据），把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。
  2. 写缓冲(Change Buffer)
    1. 修改内容命中缓冲池，则在缓冲池中进行修改，随后写入redo log
    2. 修改内容未命中缓冲池，先将改变写入写缓冲，等数据读入缓冲池之后将写缓冲的变化同步到缓冲池，随后写入redo log，如果没有读取操作将数据放到缓冲池，则写缓冲也会定时写入到redo log
    3. 注意：写缓冲针对的是非唯一的二级索引，如果是主键索引或者唯一索引，其修改要进行唯一性判断，必须进行一次磁盘io，此时从磁盘读取数据页到缓冲池，再进行修改！
  3. 自适应哈希索引(Adaptive Hash Index)：InnoDB发现b+树结构的索引查询效率不如hash索引时会自动创建哈希索引，以提高效率
  4. 日志缓冲(Log Buffer)：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log . undo log)

    索引有哪几种类型？

    主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
    唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
    普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
    全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
    覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
    组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

    百万级别或以上的数据，你是如何删除的？

  • 我们想要删除百万数据的时候可以先删除索引
  • 然后批量删除其中无用数据

  • 删除完成后重新创建索引。

    覆盖索引、回表等这些，了解过吗？

  • 覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

  • 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表

    B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？

    在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
    当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询

    非聚簇索引一定会回表查询吗？

    不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（哈哈，覆盖索引就是这么回事）。

举个简单的例子，假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询

MySQL锁相关

当数据库有并发事务的时候，可能产生数据的不一致，需要一些机制来保证访问的次序，锁就是这样的一个机制。

MySQL锁分类

  1. 按照锁粒度分类：
    1. 表锁
      1. 针对整张表加锁
      2. 锁粒度最大
      3. 开销小，加锁快
      4. 不会出现死锁
      5. 发生锁冲突的概率最高，并发度最低
      6. 分类表级共享锁和排他锁
    2. 行锁
      1. 针对当前行加锁
      2. 锁粒度最小
      3. 开销最大，加锁慢
      4. 会出现死锁
      5. 最大限度减少并发冲突，发生锁冲突的概率最小，并发度最高
      6. 分为行级共享锁和排他锁
    3. 页锁
      1. 锁粒度介于行锁和表锁中间的一种锁
      2. 开销和加锁介于行锁和表锁之间
      3. 会出现死锁
  2. 按照使用方式分类
    举例：共享锁：多个用户一起看房是可以接收的。排他锁：真正入住一晚，其他人想入住或看房都不可以
    1. 共享锁
      1. 读锁，读取操作创建的锁
      2. 其他用户可以并发读取数据，任何事务都不能对数据进行修改，直到已释放所有共享锁
      3. 事务A对数据A加上共享锁之后，其他事务只能对A加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据
      4. select … for share 查询结果每行都加共享锁。当没有其他事务获得查询结果集中任何一行的排他锁时，可以成功获取共享锁，否则会被阻塞。
    2. 排他锁
      1. 写锁、独占锁，写操作创建的锁
      2. 事务T对数据A加排他锁后，其他事务不能再对A加任何类型的锁。获准排他锁的事务既能读数据，也能写数据
      3. select… for update 对查询结果中的每行都加上排他锁。当没有其他线程对查询结果集中任何一行使用 共享锁或者排他锁时，可以申请到排他锁，否则会阻塞。

  3. 按照思想分类

    1. 乐观锁
      1. 假设不会发生冲突，只在提交操作时检查是否违反数据完整性。
      2. 每次拿数据时认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间有没有其他人去更新这个数据，可以使用版本号机制。
      3. 适用于多读的应用类型，这样可以提高吞吐量
    2. 排他锁
      1. 假定会发生冲突，直接屏蔽一些可能违反数据完整性的操作
      2. 每次拿数据都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。
      3. 传统的关系型数据库里面就用到了很多这种锁机制，比如行锁，表锁，读锁，写锁等，都是在操作之前先上锁。

        什么是死锁？怎么解决？

        多个进程正在进行的时候，因争夺资源造成相互等待的现象，导致进程处于等待中，无法得到释放，这种状态叫做死锁。

        死锁的4个必要调教

        死锁有四个必要条件：互斥条件，请求和保持条件，环路等待条件，不剥夺条件。 解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

        如何处理死锁

  4. 设置超时时间，一致等待直到超时

  5. 发起死锁检测，发现死锁之后，主动回滚死锁中的事务，不需要其他事务继续。

    如何避免死锁

  6. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

  7. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  8. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表级锁定来减少死锁产生的概率；
  9. 如果业务处理不好可以用分布式事务锁或者使用乐观锁
  10. 死锁与索引密不可分，解决索引问题，需要合理优化你的索引
  11. 尽量使用较低的隔离级别
  12. 除非必要，查询时不要显示加锁。MySQL的mvcc可以实现事务中的查询不用加锁，优化事务性能；mvcc只在读提交和可重复读两个隔离级别下工作。

    innodb是如何对待死锁的

    设置超时时间

    什么时全局锁，应用场景有哪些

    全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份，这个命令可以使用整个库处于只读状态，使用该命令之后，数据更新语句，数据定义语句，更新类事务的提交语句等操作都会被阻塞。

    使用全局锁会导致的问题？

  • 如果在主库备份，在备份期间不能更新，业务停止，所以更新业务会处于等待状态
  • 如果在从库备份，在备份期间不能执行主库同步的binlog，导致主从延迟

    MySQL数据库cpu飙升的话，要怎么处理呢？

    排查过程：
    使用top 命令观察，确定是mysqld导致还是其他原因。
    如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
    找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。
    处理：

kill 掉这些线程(同时观察 cpu 使用率是否下降)，
进行相应的调整(比如说加索引、改 sql、改内存参数)
重新跑这些 SQL。