mysql知识点

慢查询

定位

• 借助第三方检测工具

  • SkyWalking
  • 自研监控系统

• mysql开启慢查询

  开启慢查询可能会影响mysql服务器的性能，如果硬盘IO已经是瓶颈的话则影响更为明显。

  建议做好以下设置：

  • 控制日志最大大小
  • 定时清理日志
  • 使用其他监控工具

• 使用性能监控工具

  • arthas

分析

• 使用 explain or desc
  • 使用key和ken_length来判断是否命中索引
  • 使用type来判断是否走了全索引树扫描和全盘扫描，来判断索引是否需要优化。
  • 使用extra来判断是否需要回表查询来判断
    • 增加索引
    • 调整返回的字段
    • 改变条件字段位置（最左原则）

索引

• 定义
  以特定的有序数据结构存储指定列数据达到高效检索数据的方式。

数据结构

• hash

• 红黑树

  为什么不用它，虽然它动态平衡树，但也是二叉查找树，所以树的深度就会很深，如果数据量较大，并不能很明显的提升检索性能。

• B树

  多阶多叉路平衡查找树

  • 阶：代表节点上最多存储的数据个数-1。
  • 数据：所有节点都存储数据及指针。

• B+树

  • 阶数更多。
  • 只有叶子节点存储数据，并且叶子结点是双向链表结构，更加便于范围查找，不用再回到根节点再向下遍历。
  • 非叶子节点存储范围数据以及指针。

分类

• 聚集索引

  • 索引和数据在一起，叶子节点保存了行数据，每个表必须有，有且仅有一个。

  • 要么是主键要么是唯一索引，没有则表会生成rowid组建聚簇索引。

• 非聚集索引

  • 索引和数据分开，叶子节点存储的是主键，可以有多个。
  • 不需要具备唯一性。

  

• 覆盖索引

  返回的数据字段可以在检索条件的索引字段的索引文件中全部找到，则称之为覆盖索引查找，不需要回表。

回表查询

使用带有索引的条件语句检索的结果，并不能满足select需要的字段，需要在聚集索引中二次查找，这种操作称之为回表。

超大分页

使用覆盖索引+子查询优化超大分页查询慢,将部分数据存储在内存中，减少磁盘IO操作，提高查询性能。

以下是一个示例的SQL查询，使用回表策略优化大表查询students表.

# 优化前
SELECT * FROM `students` where name = "zhangsan" LIMIT 10000000,1;

#  优化后
SELECT *
FROM students s1
JOIN (
SELECT id  FROM `students` where name = "zhangsan" LIMIT 10000000,1
) AS s2
ON s1.id = s2.id;

创建原则

• 数据量够大

  • 数据查询体验很差时就要考虑加了
  • 数据本来预估就很大。

• 字段选择

  • 条件查询
  • group by
  • order by。

• 尽量选择区分度高的列建立索引，唯一索引最好。

• 如果字符串长度较长，建立前缀索引。

  CREATE INDEX idx_email_prefix ON users (email(5));
  

• 二级索引尽量使用联合索引，减少单列索引

  • 节省储存空间。
  • 更好地利用覆盖索引查询。

• 如果确定列数据不能存储null，使用not null来约束，尽量也遵守这种约定给到缺省值也好，这样可以使优化器更好地选择更优索引字段。

• 并不是多多益善，越多增删改代价越高。

索引失效

• 组合索引
  • 要符合最左前缀原则。
  • 中间有间隔，需要回表。
  • 范围查询右边的列使用不到索引，需要回表。
• 普通索引
  • 在索引字段使用运算符。
  • 字符串索引字段，字段类型使用错误没有加单引号。（类型转换）
  • 模糊查询，前面有%。

Tips: 最左前缀法则、不能使用函数和运算符、类型使用正确。

优化

表的设计

• 表的数值类型 tinyint int bigint
• 合适的字符串类型 char varchar 可变长度效率稍低。
• 范式设计

语句优化

• select后面尽量指明字段。
• 避免使用索引失效的写法。
• where后面避免对字段使用表达式操作。
  
• 使用union all 替代 union （union会多一次过滤，去除重复数据，性能较差）
• 尽量是使用inner join，同时尽量把小表放在左边，以小表驱动查询。（inner join会自动化优化小表放在左边作为驱动表，而left join和right join则不会。）

主从复制、读写分离

• Binlog
  • canal
  • 触发器

分库分表

事务

定义

一组操作的集合，不可分割的工作单元，事务会将这些操作向系统统一提交或者统一撤销，要么同时成功，要么同时失败。

• 原子性
  不可分割的工作单元，要么一起成功，要么一起失败。
• 一致性
  事务完成前和完成后，都需要保证系统的一致性，遵循业务系统的约定和规则。
• 隔离性
  事务之间是隔离独立的，相互之间不受影响。
• 持久性
  事务完成持久化的数据是持久化落盘的，物理化存储。

并发事务

• 脏读
  一个事务读到了另外一个事务还没有commit的数据。（读未提交产生）
• 不可重复读
  同一个事务两次读取的同一条记录数据不一样。（读已提交产生）
• 幻读
  一个事务在读取时数据记录一直提示不存在，但是当插入这条数据时提示数据已经存在，产生“幻觉”。

以下是解决方案

间隙锁是一种锁定索引范围而非实际数据的锁，它可以锁定一个范围，防止其他事务在这个范围内插入数据，从而保证了范围内的数据的唯一性

事务隔离

• 未提交读

• 读已提交

• 可重复读

• 串行化

  

• 多版本并发控制（MVCC）

  在MVCC中，每个事务都可以看到一个特定时间点的数据库快照。通过给每个事务分配唯一的事务标识符，并维护每个数据项的多个版本，可以实现并发执行而避免争用和数据不一致性。

redo&&undo

缓冲池

Buffer Pool：当数据库客户端进行增删改查时，为了提升效率都会和Buffer Pool，进行交互，当数据在Buffer Pool中不存在时，缓冲池会将数据行所在的数据页加载到缓冲池供与客户端交互。

数据页：Innodb引擎磁盘管理的最小单元，每一页大小默认为16KB，数据页中存储的是数据行。

脏页：当缓冲池提交到磁盘时，发生错误，在缓冲池存留的数据页就是脏页。

​

redo log

记录事务提交时数据页物理修改的记录，用于产生脏页时的补救方案。（WAL）

• 用来二次保证来实现数据事务的持久性。
• 有两份循环写。
• 当无脏页时，数据会被定时清理。
• 服务宕机时，可以用来恢复数据。

undo log

回滚日志，用来记录修改前的数据记录，作用：提供回滚、MVCC，保证事务的原子性、一致性。

• delete时，记录一条insert语句。
• update时，记录一条相反的update语句。
• insert时产生的日志，commit后就会删除；delete、update则不会删除，MVCC中有用。

排它锁

数据行的排它锁，实现了事务之间的隔离性。

MVCC

在聚集索引中有三个隐藏字段

• 插入事务id或者最后一次修改的事务id(DB_TRX_ID)
• 回滚指针（DB_ROOL_PTR）
  用来指向回滚段中的 undo 日志记录。
• DB_ROW_ID
  如果没有配置主键，表自动生成用于充当主键，聚簇索引。

undo log 版本链

多个事务对同一条记录的修改，在undo log文件中生成一条记录的版本链表，表头记录的是最新的旧记录，表尾记录的是最早的旧记录。

readview

• 当前读
  加行级锁

• 快照读

  怎么判断快照读读的是那条记录？

  • 当记录中的DB_TRX_ID等于自己事务ID（同一个事务）

  • 小于最小活跃事务ID（其他事务已提交）

  • 同时不再活跃事务列表中，也不可以大于最大事务id。（事务隔离）

  

  

  • 读已提交：每次读都会生成一个新的快照读。
  • 可重复读：复用第一次读生成的快照。

主从同步

• 主库
  • binglog：记录DDL以及DML所有操作记录。
• 从库
  • 通过IOThread同步binglog，写入Relay log
  • 使用SQLThread读取Relaylog，重放Relay log 实现数据同步。

分库分表

• 垂直分库
  根据业务将表分散到另一个集群的不同的库中。
  

• 垂直分表

  • 根据字段属性将不通字段分配到另一个集群的不通的表中。
    • 冷热分离
      • 根据更新频率区分
      • 是否常用
    • 字段数据大小
      大的描述类字段拆分出来（text、blob）

  

• 水平分库

  将数据拆分到不通集群的多个库中。
  

  • 根据id与集群个数取模
  • 预分区划分（range_file）
  • 其他分区算法

• 水平分表
  将一张大表的数据拆分成多个表（可以在同一个库中）。

知识点

WAL

WAL（Write-Ahead Logging）： WAL是一种日志记录技术，它在将数据写入磁盘之前先写入一个称为日志的文件。具体步骤如下：

1. 在执行事务修改之前，先将修改操作记录到WAL中。
2. 然后将修改操作应用到内存中的数据结构，使得修改对应的数据项在内存中更新。
3. 一旦事务所有的修改都已经应用到内存中，将事务标记为已提交。
4. 最后，将WAL中的日志写入磁盘。

使用WAL的好处是可以提高数据库的性能，因为数据的持久化是通过顺序写入日志文件来完成的，而不是直接写入磁盘的随机写操作。此外，WAL还可以帮助恢复数据库状态，在数据库故障时，可以使用WAL日志文件回放来恢复未提交的事务或丢失的数据。

AOF

AOF（Append-Only File）： AOF也是一种日志记录技术，它将每个写操作追加到文件末尾，以日志的形式保存所有修改的指令。具体步骤如下：

1. 将每个写操作（如SET、DEL等）追加到AOF文件的末尾。
2. 定期或基于配置选项，对AOF文件进行重写（rewrite），以减小文件大小并移除不必要的指令。
3. 在数据库重启时，通过重新执行AOF文件中的指令来恢复数据状态。

使用AOF的好处是可以提供更高的持久性和数据安全性，因为AOF记录了每个写操作，所以在数据库故障发生时，可以通过重新执行AOF文件来完整地恢复所有修改。此外，AOF还允许通过将指令追加到AOF文件末尾的方式，实现数据的增量备份。

WAL和AOF都是常见的持久化机制，选择哪种取决于具体的需求和使用场景。一些数据库系统可能支持同时使用WAL和AOF，以提供更强大的数据保护和恢复能力。