MySql相关知识点

Mysql

创建时间:2021-08-30 16:26

字数:1.7k 阅读:

0.SQL语句执行顺序
1.SQL语句在MySQL中的执行顺序
2.sql的日志系统
3.事务隔离
4.索引
5.全局锁、表级锁和行级锁
详解MVCC
一些注意点

0.SQL语句执行顺序

(7) SELECT
(8) 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) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

from ：先确定查询范围
ON：确定多表联合查询的条件
JOIN：指定联合哪些数据表
WHERE ：全表查询的筛选条件，生成第一个结果集
GROUP BY：分组条件，对第一个结果集进行分组，得到第二个结果集
HAVING ：过滤条件，与group by进行连用，对第二个结果集中的每组数据，进行筛选过滤，得到第三个结果集
SELECT：指定获取的列项，得到第四个结果集
DISTINCT ：对指定列进行去重操作
ORDER BY：对结果集按照指定字段进行排序整理
LIMIT：对最终结果集进行截取，一般和offset连用，可用于分页

1.SQL语句在MySQL中的执行顺序

MySQL组成
- Server层
  - 连接器
    - 尽量使用长连接，但是执行sql的临时内存是管理在长连接对象中的，解决方案有两个
      - 定期断开长连接
      - Mysql 5.7及之后版本，执行完较大的查询操作后，使用mysql_reset_connection重新初始化连接
  - 缓存（8.0版本已删除该功能）
    - 保存查询结果，以<查询语句, 查询结果>的键值对形式存储
  - 分析器
  - 优化器
  - 执行器
- 存储引擎层
执行过程（基础架构上来说）
- 1）先查询缓存，若缓存中有相同的命令，则直接返回执行结果
- 2）在分析器中，进行词法分析、语法分析
- 3）在优化器中，优化sql语句
- 4）在执行器中，先检查权限，再检查

2.sql的日志系统

组成
- redo log
  - 特点：InnoDB特有的日志
  - 用处：当MySQL更新一条语句时，先更新到redo log并刷新内存，带到服务器空闲的时候将更改更新到磁盘。
  - 大小：4 * 1GB，结构类似循环链表。包含 write position和check position两个指针。
  - 内容：记录的是物理日志，即该数据页做了什么改动
- binlog
  - 特点：server层日志
  - 内容：记录的是逻辑日志。有两种格式：statement格式记录sql语句，row格式记录行的内容，记录两条—更新前后的行内容。
- redo log和 binlog的不同
  - redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
  - redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻，比如“给 ID=2 这一行的 c 字段加 1 ”。
WAL（Write Ahead Log）技术
- 先写日志，再刷磁盘
执行过程（日志系统上来说）
- 1）执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
- 2）执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
- 3）引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
- 4）执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
- 5）执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
两阶段提交：
- 作用对象：redo log
- 目的：为了保持两个两个日志的一致性

3.事务隔离

并发事务可能出现的问题
- 脏读
- 不可重复读
- 幻读
事务隔离级别
- 读取未提交
- 读取已提交
- 可重复读
  - 实现机制：
    - 多版本并发控制：MVCC
    - undo log：相当于含有多个read-view（版本），当没有事务在使用read-view时，删除该版本视图。
  - 建议：
    - 不要使用长事务
      - 会导致undo log变长
      - 占用锁资源
- 串行化

4.索引

作用：提高查询效率
分类：（针对InnoDB引擎）
- 主键索引（聚簇索引）
  - 叶子节点：存储整行的值
  - 自增主键：
    - B+树插入新节点会造成页分裂，使用自增主键可以避免这种情况
- 非主键索引
  - 叶子节点：存储主键的值
  - 回表：需要到主键索引再次查询
  - 索引覆盖：不需要回表即可以得到查询数据
  - 前缀索引：最左前缀原则
  - 索引下推

5.全局锁、表级锁和行级锁

全局（读）锁
- 使用场景：数据库做逻辑备份
- FTWRL(Flush Table With Read Lock)
  - 当数据库中所有表的存储引擎为InnoDB时，不必使用FTWRL，因为InnoDB支持一致性读，即可重复读。（sqldump开启single-transaction参数，在备份中开启事务，确保拿到一致性视图）
表级锁
- 表锁
  - 显式使用：lock tables t1 read/write unlock tables
- 元数据锁（MDL Metadata Lock）
  - 非显式使用：在访问一个表的时候自动加上。
  - 作用：保证读写的正确性。
  - 触发机制：对表的增删改查机制都会先申请MDL
行级锁
- 行级锁是涉及该行的操作时才加锁，但是需要事务结束后才能释放（两阶段锁协议）
- 死锁
  - 解决办法
    - 设置超时时间
    - 主动死锁检测（常用）
      - 会占用较多的cpu资源
  - 热点行更新问题
    - 确保该业务不会出现死锁，关闭死锁检测（较少使用，风险比较大）
    - 控制并发度
- 开发经验
  - 在同一个事务中，如果要对多个行加上行锁，把最可能引起死锁、影响并发度的锁放到最后申请。

详解MVCC

什么是MVCC
- 多版本并发控制
先了解一下事务和锁的关系
- 使用start transaction命令后，该命令并不是事务的起点。第一个操作才是事务的开始；使用start transaction with consistent snapshot后，立即开启事务
- 当事务对数据进行进行增、删、改时，增加相应的next-key lock、行锁等
MVCC的表现
- 当一个

一些注意点

start transaction与start transaction with consistent snapshot的区别
- 使用start transaction命令后，该命令并不是事务的起点。第一个操作才是事务的开始
- 使用start transaction with consistent snapshot后，立即开启事务

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 525244039@qq.com