1. MySQL

1.1. 常见问题

1.1.1. sql 的执行流程

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存, 如果命中了缓存, 则立刻返回储存在缓存中的结果. 否认这进入下一阶段
   • 查询缓存
     • 缓存会保存查询返回的完整结构
     • 当缓存涉及的表发生了更新, 则这些表的缓存全部失效
     • 缓存命中必须要求所有字符一致, 空格, 注解等的修改都会导致缓存不命中
     • 当语句中有不确定数据时, 不会被缓存. 例如 NOW(), CURRENT_DATE() 等查询
     • 任何用户自定义函数, 储存函数, 用户变量, 临时表, 系统表, 列级别的权限表都不会被缓存
     • 当语句中包含不确定函数时, 也会查询缓存, 但是一定不会查询到结果, 因为包含不确定函数的查询结果不会加入到缓存中
3. 服务器端进行 SQL 解析, 预处理, 再 由优化器生成对应的执行计划
   • 解析
     • 解析器将 SQL 语句进行解析, 并生成解析树.
     • MySQL 解析器将使用 MySQL 语法规则验证和解析查询
   • 预处理
     • 根据一些 MySQL 规则进行进一步检查解析树是否合法, 例如检查数据表和数据列是否存在, 还会解析名字和别名, 看看它们是否有歧义.
   • 查询优化器
     • 查询优化器会将解析树转化为执行计划
     • 一条查询可有多种执行方法, 最后都是返回相同结果. 优化器的作用就是找出这其中最好的执行计划
     • 生成执行计划的过程会消耗较多的时间, 特别是存在许多可选的执行计划时
     • 执行计划会被缓存, 当类似的 SQL 查询再次到来时, 将会使用已缓存的执行计划, 从而跳过生成执行计划的过程, 提高语句的执行速度
     • MySQL 使用基于成本的查询优化器 (Cost-Based Optimizer, CBO), 会尝试预测一个查询使用某种执行计划时的成本, 并选择最少成本中的一个.
4. MySQL 根据优化器生成的执行计划, 再调用储存引擎的 API 来执行查询
   • 查询执行引擎
     • MySQL 查询执行引擎根据上一步产生的执行计划来完成整个查询
     • 执行计划是一个数据结构
5. 将结果返回给客户端
   • 如果查询可以被缓存, 那么在这个阶段将会把结果存放到查询缓存中.
   • 结果集返回给客户端是一个增量, 逐步返回的过程. 在查询生成第一条结果时, MySQL 就可以开始向客户端逐步返回结果集了.

1.1.2. select、from、where、join等子句执行顺序

完整顺序

(8) select (9) distinct <select_list>
(1) from <left_table> (3) <join_type>join<right_table>
(2) on <join_condition>
(4) where <where_condition>
(5) group by <group_by_list>
(6) with {cube|rollup}
(7) having <having_condition>
<10> order by <order_by_list>
<11> limit <limit_number>

from 后面的表关联, 是自右向左的

where 后面的解析顺序是自下而上的

所以在写 SQL 时, 尽量把数据量大的表放在最右边进行关联.

group by 执行顺序从左往右分组

1.1.3. (3) MySQL 有哪些储存引擎, 有哪些区别

• InnoDB
  • MySQL 5.5 以后的默认储存引擎
  • 灾难恢复性好
  • 支持事务
  • 使用行级锁
  • 支持外键关联
  • 支持热备份
  • 不支持 FULLTEXT 类型的索引
  • 不保存表的行数
  • 清空表时时一行行的删除
  • 数据储存的物理结构是聚簇, 索引key和数据存放在一起
  • 索引数据结构: B+树, 数据结构中直接储存实际数据, 叫做聚簇索引

• MyISAM

  • 不支持事务
  • 使用表级所, 并发性差
  • 主机宕机后, MyISAM表易损坏, 灾难恢复性不佳
  • 只缓存索引, 数据的缓存时利用操作系统缓冲区来实现的, 可能引发过多的系统调用且效率不佳
  • 数据紧凑储存, 因此可获得更小的索引和更快的全表扫描性能
  • 保存表的行数
  • 清空表时重建表
  • 索引数据结构: B+树, 数据结构中储存数据的地址, 叫做非聚簇索引

• MEMORY

  • 提供内存表
  • 不支持事务
  • 不支持外键
  • 重启数据丢失
  • 不支持 TEXT, BLOB
  • 是临时表的引擎, 如果表中有 TEXT, BLOB 字段, 那么临时表会转换成基于磁盘的 MyISAM 表, 严重降低性能.
  • 使用表级锁, 频繁写表时这里是瓶颈
  • 表被储存在内存中

1.1.4. 为什么选择 InnoDB

• 支持事务处理, 支持外键, 支持崩溃修复能力和并发控制.

1.1.5. MySQL 索引在什么情况下会失效

• 在 where 子句中进行 null 值判断 (在建立索引时最好设置默认值)
• !=, <> 对导致权标扫描
• 在 where 子句中使用 or 来连接条件 (可以考虑使用 between)
• 在 where 子句中使用 in 语句 (in(1) 只有一个值的还是会走索引)
• where 子句 = 号左边使用表达式操作或者函数的
• like 模糊查询, 且以 % 开头
  • 在使用覆盖索引的情况下, %aa 可以被使用
• 不符合最左原则
• 如果 MySQL 估计使用全表扫描要比使用索引快, 则不适用索引 (查询数量时大表的大部分, 应该是30%以上)

1.1.6. (10) MySQL 索引模型, 底层实现? 如何实现加速查询?

索引的优缺点

• 优势
  • 加速检索, 减少 I/O 次数; 根据索引分组和排序, 可以加快分组和排序
• 劣势
  • 索引本身也是表, 会占用储存空. 一般来说, 索引表占用的空间时数据表的 1.5 倍. 索引表的创建和维护需要时间成本. 这个时间成本还随着数据量增大而增大. 构建索引会降低数据表的修改操作的效率.

索引分类

1. 主键索引, 不允许重复, 不允许空值
   • 联合主键: 限制组合值不允许重复, 不推荐使用
   • 主键不应该带有业务含义
   • 主键不允许 null
2. 唯一索引, 允许空值
3. 普通索引, 没有任何限制
4. 全文索引: 用大文本对象的列构建的索引
5. 组合索引: 用多个列组合构建的所有, 这个列中的值不允许有空值
   • 遵循最左前缀原则, 把最常用作为检索或排序的列放在最左, 依次递减, 组合索引相当于建立了 [col1], [col1, col2], [col1, col2, col3] 这三个索引.
   • 使用组合索引的时候可能因为列名太长而导致索引的 key 太大, 导致效率降低, 所以可以只取列前几个字符来创建索引

索引实现原理

1. 哈希索引

• 只有 memory 储存引擎支持
• 哈希索引用索引列的值计算该值的 hashCode, 然后再 hashCode 相应的位置存该值所在行数据的物理位置, 访问速度非常快, 但是一个值只能对应一个 hashCode, 而且是散列的分布方式, 因此哈希索引不支持范围查找和排序的功能.

2. 全文索引

• 仅支持 MyISAM, InnoDB
• 对于较大的数据, 生成全文索引非常的耗时间和空间.
• 全文索引对于文本会生成一份单词的清单, 在索引时根据这个单词的清单来索引.
• MySQL 5.7 以后已经支持中文了

3. BTree 索引

• 平衡搜索多叉树
• 设树的高度为 h, 树的度为 d
• 每个叶子节点的高度一样, 等于 h
• 非叶子节点由 n-1 个 key 和 n 个指针 point 组成, 其中 d<=n<=2d, key 和 point 互相间隔, 节点两端一定是 key
• 叶子节点指针都为 null
• 非叶子节点的 key 都是 [key, data] 二元组, key 表示索引的键, data 为键值所在行的数据
• 二分查找的方式, 查找复杂度为 h*log(n), 一般来说树的高度是很小的, 一般为 3 左右.

4. B+Tree 索引

• B+树是B树的一个变种, 设d为树的度数, h为树的高度, B+Tree和BTree主要不同在于
  • B+数的非叶子节点不储存数据, 只储存键值
  • B+Tree的叶子节点没有指针, 所有键值都会出现在叶子节点上, 且 key 存储的键值对应的数据的物理地址
• 一般来说, B+Tree 比 BTree 更适合实现外存的索引结构
  • 利用了外存(磁盘)的储存结构, 即磁盘的一个扇区是整数倍的 page(页), 页是储存中的一个单位, 通常默认为4k, 因此索引结构中的节点被设计为一个页的大小, 然后利用外存的 预读取 原则, 每次读取的时候, 把整个节点的数据读取到内存中, 然后在内存中查找.
  • 内存的读取速度是外存读取 I/O 的几百倍, 那么, 每个节点中的 key 个数越多, 那么树的高度越小, 需要 I/O 的 次数也就越少.
  • 所有一般来说 B+Tree 比 BTree 更快, 因为 B+Tree的非叶子节点中不储存data, 就可以储存更多的key.

5. 带顺序的 B+Tree

• 对 B+Tree的基础上做了优化, 添加了指向相邻节点的指针, 提高了查找效率, 只要找到第一个值, 那么就可以顺序的找到后面的值

聚簇索引与非聚簇索引

MyISAM 采用的是非聚簇索引.

非聚簇索引的主索引和辅助索引几乎是一样的, 只是主索引不允许重复, 不允许控制, 它们的叶子节点的 key 都储存指向键值对应的数据的物理地址.

InnoDB 采用的是聚簇索引

• 聚簇索引的主索引的叶子节点存储的是键值对应的数据本身.
• 辅助索引的叶子节点储存的是键值对应的数据的主键键值. 因此主键的值长度越小越好, 类型越简单越好.
• 聚簇索引的数据时根据主键顺序保存. 因此适合按主键索引的区间查找, 可以有更少的磁盘 I/O, 加快查询速度
• 聚簇索引在插入新数据的时候比非聚簇索引慢很多, 因为插入新数据时需要检测主键是否重复, 这需要遍历主索引的所有叶节点, 而非聚簇索引的叶节点保存的是地址树,占用空间少,因此分布几种, 查询的手 I/O 更少, 单聚簇索引的主索引中储存的是数据本身 数据占用空间大, 分布范围更大, 可能占用好多扇区, 因此需要更多次 I/O 才能遍历完毕.

索引的使用策略

• 什么时候使用索引
  • 主键自动建立唯一索引
  • 经常作为查询条件在 where, group by 语句中出现的列要建立索引
  • 作为排序的列要建立索引
  • 查询中与其他表关联的字段, 外键关系建立索引
  • 高并发下倾向于组合索引
• 什么时候不要使用索引
  • 经常增删改查的列不要建立索引
  • 有大量重复的列不建立索引
  • 表记录太少不要建立索引
• 索引失效的情况
  • 在组合索引中不能有列为 null, 如果有, 那么这一列对组合索引就是无效的了
  • 在一个 select 语句中, 索引只能用一次, 如果 where 中用了, 那么在 group by 中就不要用了
  • like 操作中, %aaa% 不会使用索引, aaa% 会使用索引
  • 在索引的列上使用表达式或者函数会失效
  • 在查询条件中使用正则表达式时, 只有在搜索模板的第一个字符不是通配符的情况下才使用索引
  • 在查询条件中使用 !=, '<>' 会失效
  • 在查询条件中使用 IS NULL 会失效
  • 使用 or 连接多个条件会导致索引失效, 应该改为两次查询, 然后用 union all 连接起来
  • 尽量不要用多列排序, 如果一定要, 最好为这个组合构建组合索引
  • 当数据库中有足够多的数据时, 才会走索引, 否则会缓存在内存中, 不经过索引
• 索引的优化
  • 最左前缀
    • 把排序分组频率最高的列放在最左边, 以此类推
  • 带索引的模糊查询优化
    • 为查询的字段构建全文索引
    • 使用 select * from tablename match(index_colum) angainst('word')

1.1.7. MySQL 主从怎么同步? 分哪几个过程? 如果有一台新机器要加入到从机里, 怎么个过程?

概念

主从复制是指数据可以从一个 MySQL 数据库服务器节点复制到一个或更多的从节点.

MySQL 默认采用异步复制方式, 这样从节点不用一直访问主服务器来更新自己的数据. 数据的更新可以再远程连接上进行, 从节点可以复制主数据库中的所有数据库或者特定的数据库, 或者特定的表.

采取从节点向主节点 pull 的方式同步.

主要用途

• 读写分离
  • 在发开工作中, 有时候会遇到某个 sql 语句需要锁表,导致暂时不能使用读的情况出现, 这样就会影响现有的业务, 使用主从复制, 让主库负责写, 从库负责读, 这样, 即使主库出现表锁, 通过读从库也可以保证业务的正常运行
• 数据实时备份
  • 当系统中某个节点发生故障时, 可以方便的故障切换
• 高可用
• 架构扩展

主从方式

• 一主一从
• 一主多从
• 多主一从: 5.7 开始支持, 可也将多个 MySQL 数据库备份到一台储存性能较好的服务器上
• 双主复制: 互做主从复制, 任意一方发生变更, 都会同步到对方
• 级联复制: 部分 slave 连接其他的 slave, 缓解 master 的同步压力

主从复制原理

主从复制涉及三个线程

• master
  • log dump thread
    • 当从节点连接主节点时, 主节点会创建一个 log dump 线程, 用于发送 bin-log 的内容. 在读取 bin-log 中的操作时, 此线程会对主节点上的 bin-log 加锁, 当读取完成, 甚至在发给从节点之前, 锁会被释放.
• slave
  • I/O thread
    • 当从节点上执行 start slave 命令后, slave 会差UN根据爱你一个 I/O 线程用来连接 master, 请求主库 中更新的 bin-log. I/O 线程收到主线程 bin-log dump 线程发来的更新后, 保存在本地的 relay-log 中.
  • SQL thread
    • 负责读取 relay log 中的内容, 解析成具体的操作并执行, 最终保证主从的一致性.

复制过程

• slave I/O 进程连接 master, 并请求从指定日志文件的指定位置 (或从日志的最开始) 之后的日志内容.
• master 接收到 slave 的 I/O 请求后, 通过负责复制的 I/O 根据请求信息读取指定日志位置之后的日志信息, 返回给 slave, 返回信息中除了日志所包含的信息之外, 还包括本次返回本次信息的 bin-log file 以及 bin-log position;
• slave 接收到日志内容更新到本机 relay log中, 并将读取到的 binary log 文件名和位置保存到 master-info 文件中, 以便下一次读取的时候能够清楚的告诉 master 自己需要从哪个文件哪个位置开始读取.
• salve 中的 SQL 线程检测到 relay-log 中新增了内容后, 会将 relay-log 的内容解析成在主节点上实际执行过的操作, 并在本数据库中执行.

复制模式

• 异步模式
  • 默认复制模式是异步模式.
  • MySQL 增删改操作都会全部记录在 binary log 中. 当 slave 连接 master 时, 会主动从 master 处获取最新的 bin log 文件. 并把 bin log 中的 sql relay.
  • 这种模式下, master 不会主动 push bin log 到从节点, 这样有可能导致 failover 的情况
• 半同步模式 (非内置, 5.5开始集成)
  • 这种模式下 master 只需要接收到其中一台 slave 的返回信息, 就会 commit, 否则会等到超时时间切换成异步模式再提交.
  • 这样做的目的可以使主从数据库数据延迟缩小, 可以提高数据安全性. 确保事务提交后, binlog 至少传输到了一个从节点上, 不能 保证 slave 将此事务更新到db中. 性能上会有一定的降低, 响应时间会变长.
• 全同步模式
  • 需要主节点和从节点全部执行了 commit 并确认才会向客户端返回成功.

复制方式

• 基于 SQL 语句的复制 (binlog 格式: STATEMENT)
  • 记录 sql 语句到 binlog 中.
  • 优点是只需要记录会修改数据的 sql 语句到 binlog 中, 减少 binlog 日志量.
  • 缺点是在某些情况下, 会导致主从节点中的数据不一致 (比如 sleep(), now() 等函数)
• 基于行的复制 (binlog 格式: ROW)
  • 将 SQL 语句分解成基于 Row 更改的语句并记录在 bin-log 文件中. 也就是记录哪条数据被改了, 改成了什么样.
  • 优点是不会出现数据不一致
  • 缺点是会产生大量的日志, 比如说修改 table 的时候会让日志暴增, 同时增加 binlog 同步时间. 也不能通过 binlog 查看 执行的 sql 语句, 只能看到 data 发生的变更.
• 混合复制模式 (binlog 格式: MIXED)
  • 对于一般的复制使用 STATEMENT 模式保存到 binlog, 对于 statement 模式无法复制的操作则使用 row 模式来保存, MySQL 会根据执行的 SQL 语句选择日志保存方式.

1.1.8. 乐观锁与悲观锁的区别?

• 悲观锁
  • 总是假设最坏的情况, 每次拿数据时都认为别人会修改, 所以在每次拿数据的时候都会上锁. 锁同时只能有一个
  • 使用场景:
• 乐观锁
  • 总假设最好的情况, 每次拿数据的时候都认为别人不会修改, 所以不会上锁. 但是在更新的时候回判断一下在此期间 别人有没有去更新这个数据, 看也是用版本号机制和CAS算法实现.
  • 乐观锁适用于多度的应用类型, 可以提高吞吐量.
  • 实现
    • 版本号机制
      • 在更新数据时, 会判断当前所在版本和查询时的版本是否一致, 不一致就更新失败
    • CAS 算法 (compare and swap 比较与交换, 无锁算法的一种)
      • 涉及三个操作数: 需要读写的内存值 V, 进行比较的值 A, 拟写入的新值 B
      • 当且仅当 V 的值等于 A 时, CAS通过院子方式使用新值 B 来更新 V 的值, 否则不会进行任何操作 (比较和替换是一个原子操作), 一般情况下是一个自旋操作, 即不断的重试.
      • 缺点
        • ABA 问题, 可能 变量 V 的值在中间变化为了 C 然后又变化为了 A, 这个时候无法确定数据时没有变化的.
        • 循环时间长开销大: 如果长时间不成功, 会给 CPU 带来非常大的执行开销.

1.1.9. binlog 日志是 master 推的还是 slave 来拉的?

slave pull.

具体流程: slave 连接到 master 时, master 机器会为 slave 开启 binlog dump 线程, binlog 发生变化时, binlog dump 线程会通知 salve, 并将相应的内容发送给 slave

1.1.10. [5] MySQL 事务的四个隔离级别? (哪几个级别, 每个级别可能有的问题, 分别解决什么问题)

• 四个隔离级别
  • 可队列化 (Serializable)
    • 问题: 无
    • 原理: 从操作开始, 独占读锁, 写锁, 范围锁, 直到事务完成
  • 可重复读 (Repeatable read): 同一个事务中多次读取同样记录(指一行数据)时是一致的
    • 问题: 幻读
    • 原理: 通过悲观锁或者乐观锁的方式实现
      • 悲观锁: 读锁写锁从开始就持有, 直到事务结束才释放
      • 乐观锁(快照读): 多版本机制(MVCC) + 冲突检测(事务提交时检查与其它事务有没有冲突, 有冲突会报错)
      • 通过 Next-Key Locks + MVCC / 行锁 可以解决幻读问题
  • 可读已提交 (Read committed)
    • 问题: 不可重复读, 幻读
    • 原理: 读锁仍然在一次原子操作后释放, 但是写锁在事务完成后才会释放, 这种情况下会从快照读取, 不会加共享锁
  • 可读未提交 (Read uncommitted)
    • 问题: 脏读, 不可重复读, 幻读
    • 原理: 在 update, insert 等原子操作执行完后, 就立即释放读锁或写锁
• 三种问题
  • 脏读
    • 一个事务读到了另一个事务未提交的数据
  • 不可重复读 (读其他事务修改)
    • 一个事务读取到了另一个事务已经提交的数据(对行数据的修改), 也就是说一个事务可以看到其他事务所做的修改
    • 解决方案
      • MVCC
      • 行锁
  • 幻读 (读其他事务插入)
    • 是指一个事务内读取到了别的事务插入的数据, 导致前后读取不一致
    • 解决方案
      • Next-Key Locks
        • Record Locks
        • Gap Locks

1.1.11. binlog 和 redolog 日志的作用和使用阶段

redolog 是 InnoDB 特有 日志, 只能被 InnoDB 引擎使用. binlog 是 MySQL server 层提供的日志, 是可以被所有的引擎使用.

• WAL (预写式日志 Write-ahead logging): 是关系型数据库用于提供原子性和持久性的一系列技术.
  • 所有的修改在提交之前都需要写入 log 文件中, log 文件中通常包括 redo, undo 信息.
  • 举个例子, 在一个程序的某些操作过程中机器断电了, 在重新启动时, 程序就可以通过log文件和数据库对比, 决定是撤销操作还是继续完成已做的操作, 或者是保持原样.
• redolog (重做日志)
  • 在事务执行的过程中逐步写入
  • 内存中的日志和log文件中的日志是在写到末尾后会开始覆盖开头处重写. 内存中 日志会持久化到文件中
• undolog (保存事务发生前的数据 一个版本, 可以用于回滚, 同时可以提供多版本并发控制下的读(MVCC))
  • 在事务发生之前生成的
  • 事务提交后会加入到待清理的链表中

1.1.12. (2) 访问数据库请求过慢可能是什么原因

常见瓶颈

1. SQL 效率低
2. 选项配置不当
3. 访问飙升
4. 硬件性能低
5. 其它进程抢资源

如果确认是不是 MySQL 存在瓶颈

htop 查看

1. mysqld 进程的 CPU 消耗占比
2. mysqld 进程的 CPU 消耗是 user 高, 还是 sys 高
   • user 占比高, 说明用户进程消耗的 CPU 时间多. 如果长期超过 50%, 需要优化程序算法了
   • sys 高, 需要具体检查原因
3. 确认物理内存是否不够了
   • 当有 swap 产生的时候, 说明物理内存不够了.
4. 是否有 swap 产生
5. 确认 CPU 上是否有大量中断 (中断不均)
6. 查看 CPU 状态
   • IO 瓶颈
   • 等待分配内存
   • CPU 处理能力低

7. 查看 MySQL 线程在干嘛

   • show processlist
     • Sending Data , 表示从引擎中读取数据返回给 Server 端的状态
       • 没有合适的索引, 查询效率低 (加上合适的索引, 优化 SQL)
       • 读取大量的数据, 读取缓慢 (使用 Limit)
       • 系统负载高, 读取缓慢 (升级设备)
     • Waiting for table metadata lock 长时间等待 MDL 锁
       • DDL 被阻塞, 进而阻塞后边的 SQL (把 DDL 放在半夜低谷时间执行)
       • DDL 之前的 SQL 长时间未结束 (提高 SQL 效率, 干掉长时间运行的 SQL / 采用 pt-osc 执行 DDL)
     • Sleep 线程睡眠中
       • 占用连接数
       • 消耗内存未释放
       • 可能有行锁(甚至是表锁)未释放
       • 适当调低 timeout
       • 主动 kill 不活跃连接
       • 定期检查锁, 锁等待
       • 可以利用 pt-kill 工具
     • Copy to tmp table
       • 执行 alter table 修改表结构, 需要生成临时表
       • 建议放在夜间低谷时运行, 或者使用 pt-osc
     • Copying to tmp table [on disk]
       • 常见于 group by 没有索引的情况下, 需要拷贝数据到临时表中 (内存/磁盘上), 执行计划中会出现 Using temporary 关键字, 建议创建合适的所有, 消除临时表.
       • order by 没有索引的情况下, 需要进行 filesort 排序, 执行计划中会出现 Using filesort 关键字, 建议创建排序索引.

8. 其它

   • 查看锁
   • 查看 Innodb 状态
   • 查看慢日志
   • 上线前
     • 提现消灭垃圾 SQL
     • 在开发或者压测环境中, 将 long_query_time 调低, 分析 slow query log
     • 使用更好的设备

1.1.13. (6) 数据库 ACID 指什么? 有什么含义? 索引触发有什么原则?

• Atomicity 原子性
  • 指一个事务是一个不可分割的工作单位, 其中的操作要么都做, 要么都不做
  • 比如转账过程中, 要么转账成功, 要么转账失败
  • 原理
    • 利用 InnoDB 的 undolog (回滚日志), 记录事务相关的操作
      • delete 一条数据时, 记录这条数据, 回滚时 insert 这条旧数据
      • update 一条数据时, 记录这条数据之前的值, 回滚的时候根据旧值update
      • insert 一条数据时, 记录这条数据的主键, 回滚时 delete
• Consistency 一致性
  • 是指事务执行前后, 数据处于一种合法的状态 (满足预定的约束就叫合法的状态, 通俗来讲, 这状态是自己定义的, 满足这个状态, 数据就是一致的, 不满足这个状态, 数据就是不一致的).
  • 无法保证一致性会怎么样?
    • A账户有200元, 转账300元出去, 此时A账户余额-100元, 这时数据不一致的, 因为自己定义了一个状态, 余额这列必须大于0
    • A账户有200元, 转账50元给B账户, A账户的签扣了, 但是B账户因为意外, 余额并没有增加. 我们也知道 此时数据是不一致的, 因为我们自己定义了一个状态, 要求 A+B 的余额必须不变.
  • 原理
    • 从数据库层面来说, 一致性是由 原子性, 隔离性, 持久性来保持一致性的.
    • 在应用层面, 通过代码判断数据库数据是否有效, 然后决定回滚还是提交数据
• Isolation 隔离性
  • 是指多个事务并发执行的时候, 事务内部的操作与其他操作时隔离的, 并发执行的各个事务之间不能互相干扰.
  • 原理: 使用锁和 MVCC 机制
    • MVCC 即多版本并发控制 (Multi Version Concurrency Control), 一个行记录数据有多个版本快照数据, 这些快照数据在 undolog 中
    • 如果一个事务读取的行正在做 Delete 或者 Update 操作, 读取操作不会等行上的锁释放, 而是读取该行的快照版本.
• Durability 持久性
  • 事务一旦提交, 它对数据库的改变就应该是永久性的. 接下来其它操作或故障不应该对其有任何影响.
  • 原理
    • redolog
      • 修改数据前, 会在 redo log 中记录这次操作, 当事务提交的时候, 会将 redolog 日志进行刷盘(redo log 一部分在内存中, 一部分在磁盘上) , 当数据库宕机重启的时候, 会根据 redo log 中的内容恢复到数据库中, 再根据 undo log 和 binlog 内容决定回滚数据还是提交数据

1.1.14. innodb 如何解决幻读问题?

幻读是指在事务中读取到了其它事务insert的数据, 导致查询不一致, 提高隔离级别到 可队列化可以解决这个问题. 底层实现是使用 Next-key-locks 实现的.

1.1.15. 超键、候选键、主键、外键分别是什么？

• 超键: 在关系中能唯一标识元组的属性集称为关系模式的超键. 一个属性可以为作为一个超键, 多个属性 组合在一起也可以作为一个超键. 超键包含候选键和主键
• 候选键(候选码): 是最小超键, 即没有冗余元素的超键
• 主键(主码): 数据库表中对储存数据对象予以唯一的完整标识的数据列或属性的组合. 一个数据列只能 有一个主键, 且主键的取值不能为空
• 外键: 在一个表中存在的另一个表的主键称为此表的外键

1.1.16. 画出 B 树, B+树, B+树比起B树有什么优点? 为什么不用哈希表做索引?

• B+Tree 在磁盘 I/O 读取上较 BTree 有优势, B+ 在非叶子节点只储存key, 在同样的页大小下, B+ 可以一次从磁盘中读取更多的 key 到内存中进行查询.
• B+ 的叶子节点连接成了一个链表, 可以便捷的进行范围查询.
• B+ 的主键索引数据直接存放在叶子节点中, 在查询完树后不需要回表去再查表的数据.

1.1.17. 最左匹配原则是什么

• 是指联合索引检索数据时, 会从联合索引的最左侧开始匹配.
• 查询的顺序可以是任意顺序的, where col1='1' and col2='2' 和 where col1='2' and col2='1' 是一样的, 查询优化器会纠正顺序

1.1.18. 分库分表具体实现

• 为什么分库分表
  • 单表数量达到千万级, 索引查询很慢
  • 数据量持续增加, 磁盘大部分空间被使用, 导致复制备份很慢
  • 系统耦合性太高, 所有业务都在一个数据库中
  • 大量的数据写入, 降低了系统的写入性能
• 垂直拆分
  • 将一堆的统一数据放到其他节点数据库中或者表中进行储存
  • 好处
    • 有效提高了单个数据库或者表的数据储存瓶颈
    • 有效提高了数据查询性能
    • 有效提高并发写入性能, 应为可以写到多个库里面了
  • 策略
    • 按照业务拆分, 根据业务的不同将数据库中那些表分到不同数据库节点中
• 水平拆分
  • 将单一数据表数据按照我们约定的某种规则拆分到多个数据库和数据表中
  • 规则
    • 按照表的数量对主键进行hash取余, 找到对应的表
    • 按照某一表字段的区间进行拆分, 比如说日期字段
• 分库分表带来的问题
  • 分区键: 分区键就是我们用来进行分库分表的字段, 我们每次查询的时候都得带上这个字段才能够知道数据所在的库和表, 但是如果想按照其他字段来查询怎么办呢
    • 另外建立一张映射表
  • 多表 join
    • 放在业务层用代码来进行处理
  • 统计类
    • 通过另外建立一张表或者放到 redis 中, 最后再合并
• 建议
  • 业务不要一开始就去分库分表, 在没必要的情况不去分库分表. 真的有必要分库分表时, 建议一步分到位.
• 分库分表主键唯一性
  • 主键需要单调递增
    • 优势:
      • 可以排序
      • 提升数据的写入性能
      • 可以具备业务相关性
  • 使用策略
    • UUID
    • 雪花算法
      • 使用 64 比特二进制数据组成的
      • 组成
        • 1位默认不适用
        • 41位时间戳
        • 10位机器ID
        • 12位序列号
      • 坑
        • 是基于系统时间的, 系统时间不准时, 需要暂停发号
        • 当 QPS 并发不高时, 末尾号码基本都是1, 这样取模分库分表时, 就会数据不均匀
          • 随机开始序号的起始点
          • 时间戳记录从毫秒改为秒
    • Redis 自增或单独表生成自增
• 扩容
  • 推荐做法是第一次分库时就按照高数量来分, 一般推荐 32*32, 即 32 个库, 每个库 1024张表. 某个id先根据数据库32取模路由到库, 再根据一个库的表数量 32 取模路由到表里. 一开始可能就是一些逻辑库, 建立在一个 MySQL 实例中. 之后如果需要进行拆分, 就是不断在库和 mysql 示例之间迁移就可以了. 将 MySQL 服务器的库搬到另一个的服务器上去.
  • 一般这种扩容方式都按照倍数来扩容
  • 缩容也简单, 减少服务器数量, 将数据库搬运回来即可.
• 业务兼容
  • 报表: 多线程查询分表数据汇总
  • 查询: 只能通过分表的字段进行查询分页, 查询条件中得带上这个字段.
• 分表策略
  • 按照日期来划分表储存
  • Hash 取模来分表

1.1.19. 权限系统数据库表如何设计

1.1.20. 复合索引如何查询才能使用

最左匹配原则

1.1.21. 平衡二叉树、b树、b+树区别以及查询时间复杂度

• 二叉树
  • 平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据.
  • 特点
    • 非叶子节点都有最多拥有两个子节点
    • 非叶子节点, 左 < 中 <右
    • 树的左右两边的层级相差不会大于1
    • 没有值相等重复的节点
  • 查询时间复杂度: O(log(n))
• B树
  • 平衡多路查找树
  • 特点
    • 排序方式: 所有节点关键字是按照递增次序排列, 左 < 右
    • 子节点数: 非叶子节点的子节点数 > 1, 且 <= M, 且 M>= 2, 空树除外(M代表一个树节点最多有多少个查找路径, M=M路, 当 M=2 则是 2叉树, M=3则是3叉)
    • 关键字数: 枝节点的关键字数量大于等于 ceil(m/2) - 1个且小于等于 M-1 个(ceil()是个朝 正无穷方向取整的函数)
    • 所有叶子节点均在一层, 叶子节点包含关键字和关键字记录的指针
• B+树
  • 平衡多路查找树, 是 B树的升级版
  • 特点
    • 非叶子节点不保存关键字记录的指针, 只进行数据索引, 这样使得 B+树 每个非叶子节点能保存的关键字大大增加
    • 所有查询必须到叶节点才能查询到
    • 叶子节点从小到达排列, 且链接到下一个叶子节点, 形成链表
    • 非叶子节点数 = 关键字数
• B+树比B树好的地方
  • B+树的层级更少: 非叶子节点储存的关键字数跟多, 树的层级更少所以查询速度更快
  • B+树的查询更稳定
  • B+树天然具有排序功能
  • B+树全节点遍历更快
• B树优点
  • 如果最近访问的数据离根节点很近, 可以直接查到结果, 比 B+树快
  • B树范围查询得需要重复的中序遍历

1.1.22. 优化与灾难备份

• 优化
  • SQL 优化
    • form 选择最有效率的表名顺序
      • 数据库解析器从右到左解析 from 子句表名, 写在最后的表将被最先处理
        • 选择记录和列名最少的表写在最后
        • 选择记录条数最少的表放在最后
    • where 子句的连接顺序
      • 右 -> 左的解析顺序
        • 能过滤条数最多的条件放到最后
    • select 避免使用 *
    • 使用 truncate 代替 delete
      • delete 是一条条删, truncate 是清空记录
    • 使用内部函数提高 SQL 效率
      • 例如使用 MySQL 的 concat() 函数会比使用 || 来进行拼接快
    • 使用索引
    • 避免索引失效的用法
    • 用 union, in 代替 or
  • 数据库结构优化
    • 分库分表
    • 读写分离
    • 范式优化: 减少冗余
    • 反范式优化: 增加冗余, 减少 join
  • 服务器硬件优化
    • 加钱买机器
• 灾难备份
  • 备份
    • 状态
      • 冷备份: 数据库关闭情况下直接复制相关物理文件
      • 热备份: 在数据库运行过程进行备份, 对生产环境没有任何应影响
        • mysqldump
      • 温备份
        • 在数据库运行过程中进行备份, 但是备份对数据库操作有影响
          • 利用锁表原理备份数据库, 用的很少
    • 逻辑
      • 物理备份: 复制数据库的物理文件
      • 逻辑备份: 将数据导出成一条条的 SQL 文本文件.
        • mysqldump
        • mydumper
    • 备份内容
      • 全量备份: 数据库一次完整的备份
      • 增量备份: 针对于上次全量备份或者增量备份, 对数据库新增的修改进行备份.
      • 日志备份: 对 MySQL 数据库二进制日志的备份.
    • 文件的储存位置
      • 本地备份
      • 远程备份
    • 备份原则
      • 每周一次全备, 每天一次增备, 本地统一保留21天.
    • 备份方案
      • 使用 crontab 进行调度, 每天凌晨2点备份. 备份形势为物理备份.
  • 容灾
    • 主从复制

1.1.23. 触发器和储存过程

1.1.24. 索引查找在Linux的磁盘上是怎么操作的

1.1.25. 聚簇索引和非聚簇索引的区别

区别在于叶子节点是否存放一整行数据

InnoDB 主键使用的是聚簇索引. MyISAM 不管是主键索引还是二级索引, 使用的都是非聚簇索引.

• 聚簇索引
  • 对于聚簇索引来说, 表数据是和主键一起储存的.
  • 优点
    • 范围取数据优于非聚簇索引
    • 非聚簇索引查到数据时需要一次回表, 聚簇索引不需要
    • 二级索引中覆盖索引也不需要到主键索引中再查数据
  • 缺点
    • 插入数据验证依赖于插入顺序, 按照主键的顺序插入时最快的方式
    • 更新主键的代价很高, 因为会导致被更新的移动, 所以对于 InnoDB 表, 一般定义主键不可更新
    • 二级索引访问需要两次索引查找, 第一次找到主键值, 第二次根据主键值找到行数据
    • 采用聚簇索引插入新值比采用非聚簇索引插入新值慢很多. 因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。
• 非聚簇索引
  • 对于非聚簇索引来说, 表数据和索引时分成两部分储存的.

1.1.26. MySQL的IO过高怎么优化，分库分表及分区

1.1.27. 添加索引，为什么可以减少io操作（磁盘页）

MySQL 储存数据在磁盘中, 读取速度瓶颈在于 磁盘 I/O 的读取, 索引一般使用 B+树结构, 这种数据结构在查找时类似于二分查找, 能够有效的降低查找次数, 再则 B+树 叶节点形成了链表, 在范围查询的这种情形下更有利. 关于磁盘来说, 机械硬盘分扇区, 每个扇区由页组成, 页一般大小为 16k, 处于读取速度的考虑, MySQL 在申请磁盘空间时, 都是按照整页来申请的, 索引储存也是储存在页中, 在读取磁盘数据时, 会读取整页的数据到内存中, 再进行查找, 索引以关键字作为检索条件, 能够将读取 I/O 次数降到很低.

1.1.28. 回表概念

非聚簇索引的叶子节点上只储存行数据的指针, 在查询到指针后得回到数据表去获取想要的数据, 所以叫回表

1.1.29. mvcc 原理? 多版本数据放在那里

• 什么是 MVCC
  • 是在并发访问数据库时, 通过对数据做多版本管理, 避免因为写锁的阻塞而造成读数据的并发阻塞问题.
• 事务版本号
  • 每次事务开启前都会从数据库获得一个自增长的事务ID, 可以从事务ID判断事务执行的先后顺序.
• 表格的隐藏列
  • DB_TRX_ID: 记录操作该数据的事务ID
  • DB_ROLL_PTR: 指向上一个版本数据在 undo log 中的位置指针.
  • DB_ROW_ID: 隐藏ID, 当创建表没有合适的索引作为聚簇索引时, 会使用该隐藏ID创建聚簇索引.
• undo log
  • undo log 主要用于记录数据被修之前的日志, 在表信息修改之前会把数据拷贝到 undo log 里, 当事务回滚时可以通过 undo log 里的日志进行回滚.
  • 用途
    • 保证事务进行时的原子性和一致性.
    • 用于 MVCC 快照读取的数据, 在 MVCC 多版本控制中, 通过读取 undo log 的历史版本数据可以实现不同事务版本号拥有自己独立的快照数据版本
• read view
  • InnoDB 中每个 SQL 语句执行前都会得到一个 read_view, 主要保存了当前数据库系统中正处于活跃(没有 commit) 的事务的 ID 号, 简单来说这个副本中保存的是系统中当前不应该被本事务看到的其他事务id列表
• MVCC 流程
  • 获得事务版本号
  • 获得一个 read view
  • 查询到数据, 与 read view 事务版本号进行匹配
  • 不符合 read view 规则的从 undo log 里获取历史版本数据
  • 返回符合规则的数据

1.1.30. mysql 脏页？

1. MySQL 脏页

   当内存数据页和磁盘数据页上的内容不一致时, 就称这个内存页为脏页. 内存数据写入磁盘后, 内存页上的数据就和磁盘页上一致了, 我们称这个内存页为干净页.

2. 脏页刷新时机

   1. redo log 写满了, 没有空间了, 此时需要将 checkpoint 向前推进, 推进的这部分日志对应的脏页刷入到磁盘, 此时所有的更新全部阻塞, 此时写的性能变为0, 必须待刷一部分脏页后才能更新.
   2. 系统内存不足时, 需要将一部分数据页淘汰掉, 如果淘汰的是脏页, 需要将脏页同步到磁盘
   3. MySQL 认为空闲的时间, 这种没有性能问题
   4. MySQL 正常关闭之前,会把所有脏页刷入磁盘, 不存在性能问题.

1.1.31. redo log, undo log

• redo log (重做日志)
  • 主要作用于数据库的崩溃恢复
  • 保证事务的持久性
  • 分类
    • 物理 Redo 日志 (记录数据页的物理变化) (大部分情况下都是这种)
    • 逻辑 Redo 日志 (记录修改页的操作)
  • 组成
    • 日志缓冲 (redo log buffer), 是易失的, 在内存中
    • 日志文件 (redo log file), 是持久的, 保存在磁盘中的
  • 什么时候写
    • 在数据页修改完成后, 在脏页刷出磁盘前, 写入 redo 日志 (先修改数据, 后写日志)
    • redo 日志比数据页先写回磁盘
    • 聚簇索引, 二级索引, undo 页面的修改, 均需要记录 redo 日志
  • 流程
    • 先将原始数据从磁盘中读入内存中来, 修改数据的内存拷贝
    • 生成一条重做日志写入 redo log buffer, 记录的是数据被修改后的值
    • 当事务 commit 时, 将 redo log buffer 中的内容刷新到 redo log file, 对 redo log 采用追加写的方式.
    • 定期将内存中修改的数据刷新到磁盘.
  • 如果保证持久性
    • Force Log at Commit 机制实现事务的持久性, 即当事务提交时, 先将 redo log buffer 写到 redo log file 进行持久化, 待事务 commit 操作完成时才算完成. 这种做法也叫做 Write-Ahead Log (预先日志持久化), 在持久化一个数据页之前, 现将内存中相应的日志页持久化.
• undo log
  • 主要记录数据的逻辑变化, 为了在发生错误时回滚之前的操作, 需要将之前的操作都记录下来, 然后在发生错误时才可以回滚
  • 作用
    • 用于事务的回滚
    • MVCC
  • 只将数据库逻辑的恢复到原来的样子, 在回滚的时候, 实际上做的是反操作.
  • 写入时机
    • DML 操作修改聚簇索引前, 记录 undo 日志
    • 二级索引记录的修改, 不记录 undo 日志
    • undo 页面的修改, 同样要记录 redo 日志
  • 储存位置
    • undo 储存在回滚段(Rollback Segment)中, 每个回滚段记录了 1024 个 undo log segment, 而在每个 undo log segment 段中进行 undo 页的申请.
  • 类型
    • insert undo log
    • update undo log

1.1.32. 为什么非主键索引结构叶子节点储存的是主键值?

1. 主键索引和二级索引分别维护各自的 B+树 结构, 当插入数据的时候, 由于数据只有一份, 通过非主键索引获取到主键值, 然后去主键索引的 B+ 树数据结构中找到对应的行数据, 节省了内存空间.
2. 如果非主键索引的叶子节点也储存一份数据, 那么通过非主键索引插入数据, 需要向主键索引进行数据同步, 会带来数据一致性的问题.

1.1.33. 多列索引结构

每个节点都包含联合索引每一列的关键字, 并且按照层级进行的顺序全排列. 搜索数据时, 会按照一级级的进行过滤查询, 这就是最左前缀原则

1.1.34. 字符串类型和数字类型索引的效率?

• 两者最主要的区别就在于, 字符类型有字符集的概念, 每次从储存端到展现端之间都有一个字符集编码的过程, 而这一过程主要消耗的就是 CPU 资源.
• 在主键索引中, 主键最好是有序的自增数字, 而不是字符串, 因为 B+树 的叶子节点是有序的, 如果使用无序的字符串来做主键的话, B+树的维护将会非常耗时.

1.1.35. 数据类型隐式转换.

当操作数与不同类型的操作数一起使用时, 会发生类型转换以使操作数兼容. 则会发生隐式转换.

• 隐式转换规则:
  • 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
  • 如果比较操作中的两个数都是字符串, 那么会作为字符串来比较
  • 如果两个参数都是整数, 则将它们作为整数进行比较
  • 十六进制的值和非数字做比较时，会被当做二进制串
  • 如果一个参数是十进制, 则比较取决于另一个参数
    • 如果另一个参数是十进制或整数值, 则将参与十进制进行比较, 如果另一个参数是浮点数, 增将参数与浮点值进行比较
  • 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
  • 在所有其它情况下, 参数都是作为浮点数(实数)比较的

1.1.36. join 和直接 from 有什么区别

性能上有很大差别

• select * from a, b wher a.id = b.a_id 先会生成 a*b 的笛卡尔积临时表, 然后 where 过滤.
• select * from a left join b on a.id = b.a_id 会产生 on 条件下的临时表, 然后再过滤数据

1.1.37. 索引用得不太正常怎么处理？同时有（a，b）和（a，c）的索引，查询 a 的时候，会选哪个索引？

一次简单查询只会用到一次 索引. 查询优化器会判断 b 和 c 的区分度谁高, 区分度高的查询到的叶子节点数更少.

1.1.38. 对 uuid 的理解？知道哪些 GUID、Random 算法？

GUID: 统一标识符算法

• UUID
  • 用来区分信息数目的全局唯一标识, 不依赖中央机构的注册和分配. UUID 具有唯一性, 重复概率接近0
  • 36位(32个字符 + 4个连接符)
  • 格式: xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx M 是版本, N 是变体, 变体是为了保持向后兼容以及能应对未来的变更.
• 雪花算法
  • 分布式环境下生成唯一ID的算法

1.1.39. 如果由大量的增删操作，那么应该选择哪个存储引擎，为什么？

从锁上来看, 选择 InnoDB, 因为 InnoDB 使用的是行级锁, 一般不会锁表, 并发性更好. 而 MyISAM 是的是表锁, 在增删高并发下很容易因为表锁导致阻塞.

1.1.40. 常见数据库中间件有哪些

• 阿里巴巴 cobar
• 阿里云 drds
• mycat
• 官方的 mysql-proxy
• 当当网 sharing-sphere

1.1.41. [2] 分布式事务

• 什么是分布式事务
  • 分布式事务就是指事务的参与者, 支持事务的服务器, 资源服务器以及事务管理器分别位于不同的分布式系统的不同节点上.
  • 原因
    • 微服务
    • 分库分表
  • 理论
    • CAP (布鲁尔定理)
      • 解释
        • C (一致性): 对某个指定的客户端来说, 读操作能够返回最新的写操作.
        • A (可用性): 非故障的节点在合理的时间内返回合理的响应(不是错误和超时响应)
        • P (分区容错性): 当网路出现分区后, 系统依然能够继续工作.
      • 三者不能共有
        • CA: 基本不可能, 因为在网络中分区是一个必然现象
        • CP: 放弃可用性, 追求一致性和分区容错性
        • AP: 放弃一致性(这里的一致性是指强一致性), 最求分区容错和可用性, 这是很多分布式设计师的选择. 下面的 BASE 也是根据 AP 来扩展的
    • BASE
      • 解释
        • Basically Available (基本可用): 分布式系统在出现故障时, 允许损失部分可用功能, 保证核心功能可用.
        • Soft state (软状态): 允许系统中存在中间状态, 这个状态不影响系统可用性, 这里指 CAP 中的不一致.
        • Eventually consistent (最终一致性): 最终一致是指经过一段时间后, 所有节点数据都将会达到一致.
      • BASE 解决了 CAP 中理论没有网络延迟的问题. 在 BASE 中用软状态和最终一致, 保证了延迟后的一致性. BASE和 ACID 是相反的，它完全不同于ACID的强一致性模型，而是通过牺牲强一致性来获得可用性，并允许数据在一段时间内是不一致的，但最终达到一致状态。
  • 解决方案
    • 2PC
      • XA 协议
        • 第一阶段: 事务管理器要求每个涉及到事务的数据库预提交 (precommit) 此操作, 并反映是否可以提交.
        • 第二阶段: 事务协调器要求每个数据库提交数据, 或者回滚数据
        • 优点: 尽量保证了数据的强一致, 实现成本较低, 在各大主流数据库都有自己的实现.
        • 缺点
          • 单点问题: 如果事务管理器宕机, 会导致数据库无法使用
          • 同步阻塞: 在准备就绪之后, 资源管理器中的资源就一直处于阻塞, 知道提交完成, 释放资源.
          • 数据不一致: 两阶段立交协议虽然为分布式数据强一致锁设计, 但是因为网络问题该通知仅被一部分参与者收到并执行了 commit 操作, 其余参与者则因为没有收到通知一直处于阻塞状态, 这时候就产生了数据不一致性.
    • TCC (Try-Confirm-Cancel) 概念
      • 步骤
        • try阶段: 尝试执行, 完成所有业务检查(一致性), 预留必须业务资源(准隔离性)
        • confirm阶段, 确认执行真正执行业务, 不做任何业务检查, 只使用try阶段预留的业务资源, confirm 操作满足幂等性. 要求具备幂等设计, confirm失败后需要进行重试.
        • cancel阶段: 取消执行, 释放 try 阶段预留的业务资源, cancel 操作满足幂等性, cancel 阶段的异常和 confirm 阶段异常处理方案基本一致.
      • 特点
        • 强隔离性
        • 执行时间较短的业务
    • 本地消息列表
      • 核心是将分布式处理的任务通过消息日志的方式来异步执行. 消息日志可以储存到本地文件, 数据库或消息队列, 在通过业务规则自动或人工发起重试. 人工重试更多的是用于支付场景, 通过对账系统对事后问题的处理.

1.1.42. innodb是如何支持的事务

通过 undo log 来实现的事务

1.1.43. 为什么myisam不采用和innodb相同的方案来解决事务问题

MyISAM 是为了性能而生的引擎, 没有 InnoDB 那样写多个 log 文件, 因为速度更快, 但是不支持事务, 崩溃恢复的能力.

1.1.44. 为什么数据量大的时候会出现慢sql？

1.1.45. 慢 SQL 如何解决

1.1.46. T（a,b,c,d） index（a,c,d） ， x = a and x = d / x = c and x = a / x = c and x = d

index(a, c, d) 会产生 (a), (a, c), (a, c, d) 三个索引都可以使用, 所以:

• x = a and x = d 用到 (a) 索引
• x = c and x = a 会用到 (a, c) 索引, where 顺序会被优化器优化
• x = c and x = d 无法使用到所有

1.1.47. innodb存储引擎中是如何为磁盘io优化的。

使用 B+树 + 聚簇索引来优化的.

1.1.48. 缓冲区的数据结构是怎样的？

1.1.49. 你们生成id的速率超过了mysql的性能极限了吗？没超过为什么你们不采用MySQL生成全局唯一性id。

1.1.50. 死锁什么时候会出现？应用层应该怎么做避免死锁？mysql 是怎么处理死锁的呢？

1.1.51. int 占多少字节？bigint 呢？int (3) 和 int (11) 有区别吗？可以往 int (3) 里存 1 亿吗？varchar 最长多少？

TinyInt 1字节 SmallInt 2字节 MediumInt 3字节 Int 4 字节: 4 8 = 32 位, [-2^31, 2^31 - 1] [0, 2^32 - 1] BitInt 8字节: 8 8 = 64 位, [-2^63, 2^63 - 1], [0, 2^64 - 1]

Int(3) 括号里的数只是显示宽度不同, 实际储存长度是一样的. Varchar 最大 65535 字节, 3字节的固定使用(起始位和结束位), 所以可以放 65532 字节的内容. Varchar(n) 表示能存放 n 个字符, 不论汉字和英文 Char 最大 255 字节, 固定长度, 不满补空格

• 汉字占用长度和编码的关系
  • UTF-8: 一个汉字=3个字节, 英文=1字节
  • UTF8MB4: 支持 emoji 表情=4字节, 汉字3字节, 英文1字节
  • GBK: 一个汉字=2字节, 英文=1字节

1.1.52. explain

• 使用 explain select * from users
• 返回
  • id 是 select 的序列编号, 有几个 select 就会有几个 id, 并且 id 的顺序是按 select 出现的顺序增加的. id 越大执行优先级就越高, id相同则从上往下执行, id 为 null 最后执行.
  • select_type 表示对应行是简单还是复杂查询
    • simple: 简单查询, 查询不包含子查询和 union
    • primary: 复杂查询中最外层的 select
    • subquery: 包含在 select 中的子查询(不在 from 子句中)
    • derived(派生表): 包含在 form 子句中的子查询. MySQL 会将结果存放在一个临时表中
    • union: 在 union 中的第二个和随后的 select
  • table: 表示 explain 的这一行正在访问哪个表
  • type: 表示关联类型或者访问类型
    • const, system: 对查询的某部分进行优化并将其转化为一个常量.
    • eq_ref: primary key 或 unique key 索引的所有部分被连接使用. 最多只会返回一条符合条件的记录.
    • ref: 相比于 eq_ref, 不适用唯一索引, 而是使用普通索引或唯一性索引的部分前缀, 索引要和某个值相比较, 可能会找到多个符合条件的行.
      • 简单 select 查询, name 是普通索引(非唯一索引)
      • 关联表查询, 连用了联合索引的左半部分
    • range: 范围扫描, 常见的有 in(), between, >, < >= 等操作中
    • index: 扫描全表索引, 通常比 all 快一些
    • all: 全表扫描, 意味着 MySQL 需要从头到尾去查找所需要的行. 通常情况下这需要增加索引来优化了.
  • possible_keys: 这一列显示查询可能使用哪些索引来查找.
  • key 列: 这一列显示 MySQL 实际采用哪个索引来优化对该表的访问. 如果没有使用索引, 则为 NULL
  • key_len: 显示了 MySQL 咋索引里使用的字节数. 通过这个值可以算出具体使用了索引中的哪些列.
  • ref: 显示了在 key 列记录的所有中, 表查找值所用到的列或者常量, 常见的有: const(常量), 字段名(user.id)
  • rows: 这是 MySQL 估计要读取并检测的行数, 这个并不是结果集里的行数.
  • extra: 展示额外信息
    • Using index: 使用覆盖索引
    • Using where: 使用 where 语句来处理结果, 查询的列未被索引覆盖.
    • Using index condition: 查询的列不完全被索引覆盖, where 条件中是一个前导列的范围.
    • Using temporary: 需要创建一张临时表来处理查询. 出现这种情况一般要进行优化的, 首先想到的是用索引来优化.
    • Using filesort: 将使用外部排序而不是索引排序, 数据较小时在内存排序, 否则需要在磁盘完成排序. 这种情况一般也需要索引来优化.
    • Select tables optimized away: 使用某些聚合函数 (比如 max, min) 来访问存在索引的某个字段.

1.1.53. 分布式 ID

1.1.54. [2] 索引慢分析

1.1.55. 主键的特性以及原因

1.1.56. SQL 防注入

1.2. 行锁 什么情况变 表锁

1.2.1. IO 过高优化