MySQL

一、索引

1 联合索引的最左前缀匹配原则

• 定义：按照/最左优先的方式/进行索引的匹配。
• 示例：比如对a，b，c三列做索引，a、ab、abc可以使用索引，b、c、bc不能使用索引。
• 结构：
  先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。
  所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

2 索引失效条件：

• 不满足最左前缀原则
• 在列上做操作：计算、函数、类型转换（比如说字符串和数字比较，或者隐式类型转换用到了CAST函数）
• 使用不等于、大于、小于，后边的会失效（比如a>100且b=1，这时b的索引失效，但a用到了索引）
• 使用LIKE的时候，以%开头会导致索引失效
• 字符串不加引号
• 条件用了OR，OR里有某一列没有索引，不使用索引（所以尽量用IN）

3 建立索引的规范

字段：

• 建议建立索引的字段：
  • 对被频繁查找的，SELECT的列、WHERE的列、ORDER BY的列、GROUP BY的列，建议建立索引
• 不建议建立索引的字段：
  • 值可能为NULL的字段尽量不建立索引。如果必须要建索引，NULL值可以用0、1或者true、false代替。
  • 被频繁更新的字段应该慎重建立索引（维护索引成本较大）
  • 大量重复数据不建索引（比如性别）
  • 字符串类型的字段，尽量考虑用前缀索引代替普通索引

质量：

• 限制索引数量（一张表最好不要超过五个）
• 尽可能建立联合索引而不是单列索引
• 避免冗余索引，比如(a,b)的联合索引和a的单列索引

4 B树和B+树

B树（B-树）特点

B树和B+树是平衡多路查找树（b是balance（平衡））

1. 每个根结点有多个叶子节点
2. 高度平衡，每个根节点高度一致
3. 高度小，查找速度比较快
4. 所有节点，按主键顺序遵循左小右大

B+树和B树的区别

1. B+树所有数据只存储在叶子节点，所有根节点不存储数据。
2. B+树每个叶子节点都有指向下一个节点的指针。

B+树特点：

1. 查询速度稳定，数据都存储在叶子节点，查找次数相同
2. 遍历更快（因为叶子节点有指向下一个叶子节点的指针）
3. 通过叶子节点存储指针，能满足空间局部性原理，如果存储器上某个位置被访问，那么它附近的位置大概率也会被访问

5 索引

5.0 innodb怎样选择哪一列作索引

• 有主键：默认使用主键作为聚簇索引的索引键
• 没有主键：选择第一个不包含NULL值的唯一列作为聚簇索引的索引键
• 以上两个都没有的情况下，自动生成一个隐式自增id作为聚簇索引的索引键

5.1 按底层物理存储方式分类的索引（聚集索引/主键索引、非聚集索引/二级索引/辅助索引）

1. 聚簇索引（聚集索引）
   索引结构和数据一起存放。InnoDB的主键索引就属于聚簇索引（字典里的拼音）
   • 优点：
     • 查询速度快。相当于直接定位到了数据
   • 缺点：
     • 更新代价大。更新数据，需要更新索引，需要更新索引里的数据。
2. 非聚簇索引（非聚集索引）
   索引顺序和物理存储顺序不同（字典里的偏旁），叶子节点存放的是主键值，而不是实际数据
   • 优点：
     • 更新代价较小。叶子节点不存放数据
   • 缺点：
     • 可能会需要二次查询，回表。（满足覆盖索引的条件：查的内容都在索引里就不需要回表）

5.2 按字段特性分类的索引

1. 主键索引
   通常在创建表的时候一起创建，列值唯一（且不可以为NULL），一张表只有一个主键
2. 唯一索引
   在unique字段上的索引，列值唯一（可以有NULL），一张表可以有多个
3. 普通索引
   只能加速查询，允许值重复和有NULL，一张表可以有多个
4. 前缀索引
   只适用于字符串类型，对文本的前几个字符创建索引，比普通索引建立的数据更小

5.3 覆盖索引和联合索引

覆盖索引

• 索引覆盖了查询内容
• 比如：对列a、b做索引，只查a或者ab不需要回表。

联合索引（组合索引、复合索引）

• 使用表中的多个字段创建索引

5.4 什么时候回表

没有覆盖索引的查询，比如用到了三列，索引里只包含两列

5.5 执行update后索引怎么变的

• 更新的列不在索引列
  • 只会修改数据页，不会影响索引
• 更新的列是主键
  • 主键的更新会导致在索引中位置的变化，会删除原来的记录并插入一条新记录，可能会导致页分裂或重新分配
  • 其他索引存储的对应的主键值会指向新的主键值
• 更新的列在辅助索引或者联合索引里
  • 辅助索引或者联合索引会删除旧的索引记录，然后插入新的索引记录

6 内连接和外连接

• 内连接是inner join：只返回两个表都存在且匹配的数据
• 外连接
  • left join：会返回左表中所有的记录，即使在右表中没有匹配的记录。对于没有匹配的右表记录，会用NULL来填充。
  • 右外连接：右边驱动表的数据全部显示，左边匹配表的没有匹配到就不会显示。

二、InnoDB特性

1 InnoDB

InnoDB的默认级别是可重复读。
InnoDB的MVCC和next-key lock 可以避免幻读产生，已经可以完全保证事务的隔离性要求，达到可串行化的效果，并且不会有可串行化的更多的锁的性能损失。

• 原子性：是通过undo log（回滚日志）来保证。
• 隔离性：是通过读写锁 + MVCC机制来实现的。
• 持久性：是通过redo log（重做日志）来实现的。
• 一致性：是通过原子性+隔离性+持久性来保证的

2 MVCC

MVCC是多版本并发控制，是InnoDB在可重复读隔离级别下事务的实现方式。
一般情况下读读不需要锁，读写、写写都需要锁。用了MVCC后，在读写时不需要加锁，但可能读到历史数据。

0. 每行数据的版本号：每行数据都包含创建版本号和删除版本号。（创建版本号表示数据何时被插入或最近修改，删除版本号表示数据何时被删除或标记为不可见。）
1. 事务ID：当一个事务开始时，InnoDB 会分配一个唯一的事务 ID
2. 创建Read-View：然后，会创建一个Read-View。它是通过创建快照来实现的。存储了一个时间点的数据库状态，包含了当前所有表的版本号。
3. 查询时：当一个事务执行查询时，它只能看到对它可见的数据版本。可见性是通过比较数据行的版本号和当前事务的 Read-View 来确定的。
   • 如果某一行的创建版本号小于或等于当前事务的事务 ID，并且删除版本号大于当前事务的事务 ID，那么该行的数据对当前事务可见。
   • 如果某一行的创建版本号大于当前事务的事务 ID，那么该行的数据对当前事务不可见，因为该行的数据是在当前事务之后创建的。
   • 如果某一行的删除版本号小于或等于当前事务的事务 ID，那么该行的数据对当前事务可见，即使该行已被删除。
4. 修改时：当一个事务对数据进行修改时，InnoDB 不会直接修改原始数据行。会创建一个新版本的数据行，并更新该行的创建版本号。这样可以保持数据的历史版本。
5. 事务提交：当事务完成并提交时，事务 ID 将被标记为已提交。其他事务可以看到这个事务所做的更改。
6. 事务回滚：如果事务回滚，那么它所做的更改将被撤销，不会影响其他事务。
7. 数据的清理：定期或根据需要，InnoDB 会执行数据清理操作，删除已经不再需要的旧版本数据。

3 undo log：事物回滚、MVCC

• 生成时间
  • 是在事务开始之前
• 作用及内容
  • 保存的是当前事物上一版本的数据，用于事物回滚数据、MVCC
• 使用的条件
  • 事务执行过程中可能遇到各种错误，比如服务器本身的错误等。
  • 程序在执行过程中通过ROLLBACK取消当前事务的执行。
  • 可能已经执行一半就结束，但已经修改了很多数据，为了事务的原子性，需要把修改的数据给还原回来。
• insert undo log
  • 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log
  • 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被统一清除。
• 对同一行加锁的时候，undolog是链表，新的会放在旧的前边。

4 redo log：数据恢复，事务的持久性

javaguide.cn/database/my…

• 生成时间
  • 事物开始之后（由于事物两阶段提交的原因，redolog会在事物执行过程中产生）
• 作用及内容
  • 保存的是内存中修改的数据，用在数据库宕机后数据的恢复
• 刷盘策略
  • 先写入redo log buffer 中，然后再按照一定频率刷新到redo log file

5 binlog（和redo log比较）

• 作用：
  • Binlog主要用于数据复制和备份
  • Redo Log主要用于保证事务的持久性和数据库的崩溃恢复。
• 格式和内容（对比redo log）
  • binlog记录执行的SQL语句。
  • redolog记录了数据库的底层物理修改，不易阅读
• 写入时机
  • binlog在事务提交之后才被写入，（记录的是已经成功提交的操作）
  • redo log在事务执行过程中可以不断写入
• redo log只能在InnoDB生成

6 两阶段提交协议

• 准备阶段
  • 协调者向参与者发起指令、参与者评估自己的状态。如果参与者评估指令可以完成，就会写undolog。然后锁定资源，执行操作，但是并不提交。
• 提交阶段
  • 如果每个参与者/明确返回/准备成功，那么协调者发起提交指令，参与者提交资源变更的事务，释放锁定的资源。
  • 如果任何一个参与者/准备失败，那么协调者向参与者发起终止命令，参与者取消已变更的事务，执行undolog，释放锁定的资源

三、架构、引擎

1 基础架构

MySQL如何执行一条SQL

1. 客户端发起请求
2. 连接器（验证用户身份）
3. 查询缓存（存在缓存就直接返回，不存在才执行后续操作）
4. 分析器（对SQL进行词法分析和语法分析）
5. 优化器（主要对执行的sql优化选择最优的执行方案）
6. 执行器
7. 去存储引擎获取数据返回

2 存储引擎

存储引擎基于表，而不是数据库（比如同一个库下，a表是InnoDB，b表是MyISAM）。
默认InnoDB。

2.1 MyISAM和InnoDB的区别

1. MyISAM只支持表级锁，InnoDB还支持行级锁，默认为行级锁
2. MyISAM不支持事物，InnoDB支持事物，默认可重复读。这个级别下解决幻读，是基于MVCC和Next-Key LOCK实现的。
3. MyISAM不支持外键，InnoDB支持使用外键。但是一般情况下使用外键概念必须在应用层解决。
4. InnoDB的redo log支持崩溃后的恢复，MyISAM不支持
5. InnoDB支持MVCC，减少加锁操作，可以提高性能。

四、基本原理

1 事物的特性（ACID）

• 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有被执行过一样。
• 一致性：执行事务前后，数据库的完整性没有被破坏，写入的内容必须完全符合所有的预设规则。例如转账业务中，无论事务是否成功，转账者和收款者的总额应该是不变的。
• 隔离性： 并发访问数据库，一个用户的事务不被其他事务所干扰，各个并发事务之间数据库是独立的。
• 持久性： 一个事务被提交之后，对数据的修改就是永久的，即便系统故障也不会丢失。

2 脏读、幻读、不可重复读

内容：

• 脏读：读取到了未提交的事务数据。
• 不可重复读：在同一事务中，两次查询同一个记录得到的结果不一致。
• 幻读：在同一事务中，两次查询同一范围，后一次查询看到了前一次查询没有看到的行。

示例：

• 脏读
  例如：变量为50，事物A要修改为100，A还未提交，事物B已经读取到了100。但此时发生回滚，数据库里的变量还是50而不是100，事物B读取到的和数据库真实的不一致。
• 丢失修改
  例如：事物A和事物B都对变量修改，期望将结果+1的修改。t1时刻事物A获取变量值是50，t2时刻事物B也获取变量值是50。但事物A还未执行修改完毕，数据库最后的结果是51而不是52，事物A的修改丢失了。
• 不可重复读
  例如：事物A对变量只读，事物B对变量修改。t1时刻事物A读取到变量结果是50，t2时刻事物B将结果修改为100，t3时刻事物A发现变量的结果发生改变。
• 幻读
  1. select 某记录是否存在——不存在。
  2. 准备插入此记录
  3. 但执行 insert 时发现此记录已存在，无法插入 此时就发生了幻读

不可重复读和幻读的区别：

• 幻读是查询到的个数的区别，不可重复读是内容的区别。两者解决方案不一致，加的锁不一样。
• 不可重复读：UPDATE和DELETE，幻读：INSERT。

3 事物隔离级别

• 未提交读：事务中发生了修改，即使没有提交，其他事务也是可见的。
  • 可能会导致脏读、幻读或不可重复读。
• 提交读：可以避免未提交读发生的情况，只有提交后的才能被看到。
  • 可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• 可重复读：对一个记录读取多次的结果是相同的，除非数据是被本身事务自己所修改。
  • 可以阻止脏读和不可重复读，但幻读仍有可能发生。
• 串行化：最高的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。
  • 这个级别可以防止脏读、不可重复读以及幻读。

InnoDB的默认级别是可重复读。
InnoDB的MVCC和next-key lock 可以避免幻读产生，已经可以完全保证事务的隔离性要求，达到可串行化的效果，并且不会有可串行化的更多的锁的性能损失。

五、锁

• 行级锁、表级锁：行级锁开销大，冲突少，会死锁。表级锁开销小，冲突大，不会死锁
• gap lock间隙锁
  • 锁定一个范围
• next-key lock
  • 间隙锁+行锁，能解决幻读的问题
• 共享锁、排他锁
  • 共享锁S
  • 排他锁X
• 乐观锁、悲观锁

六、使用

1 SQL执行的慢的原因和解决方法

• 某条SQL偶尔执行慢
  1. 在刷新脏页，redo log写满了需要直接写入磁盘
  2. 执行的时候遇到了锁
• 某条SQL一直执行慢
  1. 没有用上索引，加索引、查看是否是对字段进行运算、函数，导致未用上索引。
  2. 用explain排查，看索引是否失效，修复之后，可以用index某列强制走索引。

2 字段设计规范

1. 不要使用null，可以default一个空值（在判断=null，或者非等值查找（<>）null时，无法用到索引）
2. 控制单表数据量的大小：对于日志数据，采用归档的方法。对于业务数据，采用分库分表的方法
3. 不要在数据库中存储文件（例如图片）等大的二进制文件，应该存储在文件服务器，数据库只存储文件地址信息。
4. 金额相关不能使用float或者double，使用decimal（精准浮点数）

3 开发规范

1. 充分利用索引
2. 查看慢查询日志，优化SQL语句
3. 禁止使用SELECT *
4. golang中要判断查询是否中断
5. 尽量用in代替or（in能更有效的用到索引）
6. 使用自增主键（新增数据会顺序向后追加，非自增主键需要添加到中间）
7. 尽量避免join查询

4 解决数据库高并发的方法

1. 用redis做缓存（需要注意一致性）
2. 使用索引，提高查询速度
3. 使用主从读写分离，主库写，从库读
4. 数据库进行拆分
5. 使用分布式架构，分散计算压力

七、其他

1 碎片

• 碎片产生原因
  数据被删除会留空，插入时会使用空白空间，某个空间一直没有大小合适的数据占用，就形成了碎片。
• 碎片如何优化
  使用OPTIMIZE TABLE

2 mysql多个表同时读写，要保持一致性，又要保证性能高，如何做

• 开启事务：将所有写操作放在同一个事务中，可以保证acid数据一致性
• 批量插入：将多个插入合并成一条语句，减少与数据库的交互次数，提升性能