# MySQL

# 数据常用存储方式？

顺序存储和链式存储

# MySQL 主从复制原理的是什么？

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点。

  • 主节点 binary log dump 线程

当从节点连接主节点时，主节点会为其创建一个log dump 线程，用于发送和读取bin-log的内容。 在读取bin-log中的操作时，log dump线程会对主节点上的bin-log加锁，当读取完成，在发送给从节点之前，锁会被释放。
主节点会为自己的每一个从节点创建一个log dump 线程。

  • 从节点I/O线程

用来连接主节点，请求主库中更新的bin-log。
I/O线程接收到主节点的blog dump进程发来的更新之后，保存在本地relay-log（中继日志）中。

  • 从节点SQL线程

负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

# MySQL 主从复制主要用途?

  1. 主从切换 如果主库出现问题，可以快速切换到从库提供服务。
  2. 读写分离 可以在从库执行查询操作，降低主库的访问压力。
  3. 数据备份 可以在从库进行备份，以免备份期间影响主库的服务。

# MySQL 主从复制的模式有哪些？

  • 异步模式

MySQL 默认采用异步复制方式。
主节点不会主动推送bin-log到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。

  • 半同步模式

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端。
而是等待至少一个从库接收到并写到relay-log中才返回成功信息给客户端。

相对于异步复制，半同步复制提高了数据的安全性，一定程度的保证了数据能成功备份到从库，
同时它也造成了一定程度的延迟，半同步复制最好在低延时的网络中使用。

  • 全同步模式

指当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。

  • GTID复制模式

基于GTID的复制是MySQL 5.6后新增的复制方式。
GTID (global transaction identifier) 即全局事务ID，保证了在每个在主库上提交的事务在集群中有一个唯一的ID。

通过全局的事务ID确定从库要执行的事务的方式代替了以前需要用bin-log和pos点确定从库要执行的事务的方式。

# MySQL 主从复制的方式有哪些？

  • 基于SQL语句的复制

只需要记录会修改数据的sql语句到bin-log中，减少了bin-log日质量，节约I/O，提高性能。
缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。

  • 基于行的复制

只记录哪条数据被修改了，修改成什么样。
优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。
缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增，同时增加bin-log同步时间。
也不能通过bin-log解析获取执行过的sql语句，只能看到发生的data变更。

  • 混合模式复制

是以上两种模式的混合，
对于一般的复制使用STATEMENT模式保存到bin-log。
对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

对应的bin-log文件的格式也有三种：STATEMENT, ROW, MIXED。

# MySQL 主从同步延时的解决方案？

判断主从延时，通常有两个方法：

  1. Seconds_Behind_Master
  2. mk-heartbeat Maatkit万能工具包中的一个工具，被认为可以准确判断复制延时的方法。

造成延迟的可能因素：

  1. 网络延迟
  2. master负载
  3. slave负载

解决方案：

  1. 使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作，
    执行relay log 里面的SQL效率自然就高了，能相对最大限度地达到'实时'的要求了。
  2. 开启MySQL的 Semi-sync（半同步），还是会存在延迟，是一种折中方案。
  3. 对于强一致场景，完全同步，但完全同步是一个非常昂贵和复杂的操作，负载量大的话几乎不可能完成。
  4. 不在主从架构上解决该问题，基于业务场景来解决
    如：分布式缓存

# 事务有时候需要手动提交，容易忘记，Java代码使用什么方式可以实现自动提交（commit）？

try-with-resource。

# Mysql如果一个事务一直没有commit并且也没有rollback会怎样？

断开链接会自动rollback，死锁或者这个锁超时的时候，也会rollback。

# 索引实现原理？

  • B+树

# 文件存储要选用B+树这样的数据结构？

  • B+ tree 的磁盘读写代价更低
    内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小。
    如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。
    一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了。

  • B+ tree 的查询效率更加稳定
    由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，
    所以任何关键字的查找必须走一条从根结点到叶子结点的路，
    所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

# MySQL为什么选取B+树?

本质上是因为MySQL数据是存放在外部存储的
B+树是为磁盘或其他直接存取的辅助存储设备而设计的一种数据结构。

能够提供稳定高效的范围扫描（range-query）功能
这也是为什么数据库和操作系统中的文件系统通常会采用b+树作为数据索引的原因
这个特点主要因为所有叶子节点相互连接，并且叶子节点本身依关键字的大小自小而大顺序链接。

# 怎样实现有条件的索引?

例如：
有A、B两列，B为一个状态列取值 0 和1，要求当B = 1时，AB为唯一索引，而等于 0 时，可以任意存储。

# 怎样在 MySQL 表中存储树形结构数据？

  1. Adjacency List：每一条记录存parent_id。
  2. Path Enumerations：每一条记录存整个tree path经过的node枚举。
  3. Nested Sets：每一条记录存 nleft 和 nright。
  4. Closure Table：维护一个表，所有的 tree path 作为记录进行保存。

# 为什么要分库分表？

  • 分表
    单表数据量太大，会极大地影响 sql 执行的性能。
    一般来说，单表到几百万的时候，性能就会相对差一些了，就要考虑分表了。

  • 分库
    一般而言，一个库最多支撑 2000 左右并发，超过一定要扩容了，
    一个健康的单库并发值最好保持在每秒 1000 左右。

主要考虑：
并发支撑情况、磁盘使用情况、SQL 执行性能。

# 用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

原理：根据指定的某个字段值，比如说 userid，自动路由到对应的库上去，然后再自动路由到对应的表里去。

  • Cobar
    阿里 b2b 团队开发和开源的，属于 proxy 层方案，介于应用服务器和数据库服务器之间。

  • TDDL
    淘宝团队开发的，属于 client 层方案。

  • Atlas
    360 开源的，属于 proxy 层方案。

  • Sharding-jdbc
    当当开源的，属于 client 层方案。

  • Mycat

基于 Cobar 改造的，属于 proxy 层方案，支持的功能非常完善，
而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃。

  • 总结

可以考虑使用 Sharding-jdbc 和 Mycat。

Sharding-jdbc 这种 client 层方案的优点在于 不用部署，运维成本低，不需要代理层的二次转发请求，性能很高。
但是个系统都需要耦合 Sharding-jdbc 的依赖。（中小型公司）

Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，
但是好处在于对于各个项目是透明的。（中大型公司，支撑的业务多，可单独维护）

# 如何对数据库做拆分？

  • 水平拆分
    每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全量数据。
    水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来扛更高的并发，还有就是用多个库的存储容量来进行扩容。

    • 按照 range 来分
      一般是按时间范围，好处在于扩容的时候很简单，按照时间新建表即可。
      实际生产用 range，要看场景。
    • hash 分发
      可以平均分配每个库的数据量和请求压力，扩容起来比较麻烦，会有一个数据迁移的过程。

  • 垂直拆分
    把一个有很多字段的表给拆分到多个表中，或者是多个库上去。
    例如： 把一个大表拆开，订单表、订单支付表、订单商品表。

一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。
因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。

# 分库分表如何平滑过渡？

  • 停机迁移方案
  • 双写迁移方案

# 分库分表之后，id 主键如何处理？

实际上就是实现分布式唯一ID。

  • 数据库自增 id
    往一个库的一个表里插入一条没什么业务含义的数据，然后获取一个数据库自增的一个 id。
    拿到这个 id 之后再往对应的分库分表里去写入。

  • 设置数据库 sequence 或者表自增字段步长

  • UUID

  • snowflake 算法

# MySQL 的查询效率影响因素？

参考

# MySQL 性能优化思路？

  • 读写分离
  • 水平分表
  • 分库
  • 垂直分表
  • 引入Cache

# 如何实现 MySQL 的读写分离？

基于MySQL的主从复制机制。

注意点：
会存在主从复制延迟，导致数据不一致情况。
所以对于实时性要求高的场景，是不能采用异步主从复制来实现读写分离的。

  • 基于程序代码内部实现

在代码中根据select,insert进行路由分类。

  • 基于中间代理层实现

# 如何解决MySQL主库宕机导致的数据丢失情况？

一般是 MySQL 异步复制导致，可以采用半同步复制模式。

← Redis →