对于MySQL的理解

福贵儿

已于 2023-06-06 09:31:27 修改

阅读量524

点赞数

分类专栏：数据库文章标签： mysql 底层

于 2023-02-19 12:55:53 首次发布

本文链接：https://blog.csdn.net/Yushuzii/article/details/129109577

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一.理论

一条SQL执行过程
- 连接器
  负责跟客户端建立连接、获取权限、维持和管理连接。
- 分析器
  - 词法
    - MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
  - 语法
    - 根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
  - 语义
    - 判断语句是否正确，表是否存在，列是否存在
- 优化器
  - 决定使用什么索引，和join表的顺序
- 执行器
  - 判断是否有权限，然后执行语句
  - 执行的过程
索引
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样
- 常见索引数据结构(每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景)
  - 哈希表
    - 适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎
  - 有序数组
    - 适用于等值查询和范围查询场景中，只适用于静态存储引擎
  - 搜索树
  - 跳表
  - LSM 树
- 常见索引
  基于非主键索引的查询需要多扫描一棵索引树，应用中应该尽量使用主键查询
  - 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
  - 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）配合change buffer使用更佳
  - 唯一索引：唯一索引可以创建多个且能为空(只能一次)，主键索引不能为空
- 索引优化
  - 覆盖索引(会受到前缀索引影响)
    由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
  - 最左前缀原则
    第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  - 索引下推
- 优化器选错索引(纠正)
  - 由于索引统计信息不准确，导致判断扫描行数不准确，此种情况可用 analyze table 来解决
  - 由于临时表，排序字段，导致优化器误判，此种情况可用force index来强行指定索引，也可以通过修改语句引导优化器，还可以通过增加或者删除索引来绕过这个问题
- 索引失效
  总结：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。(下面三种都是需要函数转换)
  - 条件字段函数操作
    例如 where month(t_modified)=7
  - 隐式类型转换
    例如 select “10” > 9
    如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
    如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。
  - 隐式字符编码转换
    mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /语句Q1/
    tradeid不是同一个字符集，需要转换CONVERT(traideid USING utf8mb4)，导致索引失效
事务(ACID)
- 特性
  - 原子性(Atomicity)
  - 一致性(Consistency)
  - 隔离性(Isolation)
    这4种隔离级别，并行性能依次降低，安全性依次提高
    - 读未提交（read uncommitted）
      一个事务还没提交时，它做的变更就能被别的事务看到
    - 读提交（read committed）
      一个事务提交之后，它做的变更才会被其他事务看到
    - 可重复读（repeatable read）
      一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
    - 串行化（serializable ）
      对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。
  - 持久性(Durability)
- 多事务问题
  - 脏读
  - 不可重复读
  - 幻读
- 事务启动方式
  - 显式启动事务语句，begin或者start transaction,提交commit，回滚rollback
  - set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。
- MVVC(Multi-Version Concurrency Control 多版本并发)
  - 视图是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
锁
- 全局锁
  - 命令：
    - Flush tables with read lock (FTWRL)
    - set global readonly=true (不建议使用)
  - 场景：做全库逻辑备份
- 表锁
  - 一种是表锁，一种是元数据锁（metadata lock)
  - 命令
    - lock tables … read/write(不建议使用 lock tables锁住整个表的影响面太大)
    - MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 行锁
  - 死锁
    - 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
    - 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
日志
- binlog(归档)
  - Server层特有的日志
- redo log(重做)
  - InnoDB 引擎特有的日志
  - 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，空闲时写入磁盘。
- undo log(回滚)

二.实战

Join
- 什么使用时候Join
  - 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  - 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
- 如果要使用 join，应该选择大表做驱动表还是小表？
  - 如果是 Index Nested-Loop Join 算法(BNL)，应该选择小表做驱动表；
  - 如果是 Block Nested-Loop Join 算法(ILJ)：
    - 在 join_buffer_size 足够大的时候，是一样的；
    - 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
      （小表：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。）
- 优化
  - Multi-Range Read 优化
    意为多范围读，表示在回表之前先把需要回表的主键排序，再读取对应的数据（当然，有的并不需要回表），就能够将无序读转化为有序读，性能会大幅提升
  - Batched Key Access(BKA对NLJ 算法的优化,但依赖MRR)
    set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;
临时表
- 特点
  （1）只对当前session可见
  （2）可以与普通表重名
  （3）增删改查用的是临时表
  （4）show tables不显示普通表