简聊Mysql

Dong先生
64 阅读13分钟

1.sql查询过程

  • 分为三层的交互：
    • 客户端
    • 数据库service层
    • 存储引擎层
  1. 客户端：程序通过druid的数据链接，一般设置5个链接；还有其他配置：
spring:
  datasource:
    druid:
      # 指定数据源类型为DruidDataSource
      type: com.alibaba.druid.pool.DruidDataSource
      # 数据库连接URL
      url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
      # 数据库用户名
      username: xxxx
      # 数据库密码
      password: "xxxxx"
      # 连接池初始化时创建的连接数
      initialSize: 5
      # 连接池中最大连接数
      maxActive: 50
      # 连接池中最小空闲连接数
      minIdle: 5
      # 连接池中最大空闲连接数
      maxIdle: 10
      # 获取连接时的最大等待时间（毫秒）
      maxWait: 60000
      # 间隔多久检测一次空闲连接（毫秒）
      timeBetweenEvictionRunsMillis: 60000
      # 连接池中连接最小空闲时间（毫秒）
      minEvictableIdleTimeMillis: 300000
      # 用于检测连接是否有效的SQL语句
      validationQuery: SELECT 1
      # 是否开启空闲连接的检测
      testWhileIdle: true
      # 是否开启连接的检测功能，在获取连接时检测连接是否有效
      testOnBorrow: false
      # 是否开启连接的检测功能，在归还连接时检测连接是否有效
      testOnReturn: false
      # 是否缓存PreparedStatement对象
      poolPreparedStatements: true
      # 缓存PreparedStatement对象的最大数量
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计用的filter，允许监控统计
      filters: stat
      # 配置扩展属性，用于监控统计分析SQL性能等
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

  1. 数据库service层

    • 连接器:长连接,但长连接可能导致典型的8小时问题或是浪费资源
    • 缓存查询：mysql8.0之前还有缓存查询，之后就没有了
    • 解析器：词法分析,语法分析
    • 优化器:explan查看执行计划；
    • 执行器：调取存储引擎层；

  2. 存储引擎层：

    • 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持MyISA、MInnoDB等多个存储引擎；

2.sql修改过程

  • 先从buffer pool中获取数据 如果没有查询磁盘中获取到包含这条数据的数据页，返回给service的执行器
  • Service修改这条数据页的这一行数据的值
  • 记录改变前的值到undo log
  • 记录改变后的值到redo log
  • 调用存储引擎接口，记录数据页到buffer pool
  • 提交事物

image.png

3.MVCC机制

  • 主要解决什么:多版本并发控制

    主要解决并发时的读写问题,并且解决脏读，不可重复读，和部分幻读问题；

  • 如何实现的

    1. 每行数据的3个隐藏字段

    a. row_id:没有主键的时候，会自动生成一个6字节的row_id b. trx_id:最近创建或者修改事务的ID； c. roll_point:会滚指针，指向旧版本的undo日志；

    1. undolog版本链

    undolog 就是回滚日志，是保证事务原子性的机制，日志中只是记录：insert/delete/update语句；

    1. ReadView读视图

    视图中有四个重要的因素：trx_list:当前活跃的事务ID，min_trx_id:trx_list中最小的id，max_trx_id:记录下一次事务最大的id（不再trx_list）,creator_trx_id:生成ReadView视图的Id； 判断规则：

    1. 拿着隐藏字段的trx_id和视图进行比较；
    2. trx_id==creator_trx_id 说明是自己的事务，该版本可以被当前事务访问；
    3. trx_id<min_trx_id 说明生成该版本的事务在当前生成ReadView已经提交；
    4. trx_id>=max_trx_id 说明生成好版本的事务雜当前事务生成的 Read、View以前已经提交；
    5. min_trx_id<trx_id<max_trx_id,要看list活跃集合中有没有trx_id,存在：不可访问，不存在可以访问；

  • 分为哪几种情况 image.png

  • 隔离级别的不同生成ReadView的时间也是不同的：

  1. 读未提交：没有视图，直接返回记录值的最新数据
  2. 读已提交：每次的查询都会生成一个全新的视图；
  3. 可重复读：在事务开启的时候就会创建一个ReadView视图，在这个事务里不会再次更新查询；
  4. 串行读： 直接加锁的方式解决并行的访问；
  • MVCC如何解决脏读，不可重复读，幻读
  1. 解决脏读：由于版本控制的可见性规则。使的当前事务只能看到提交的数据;
  2. 解决不可重复读：在可重复读的级别通过使用从启动到结束都使用一个ReadView视图，解决提交两次查询读取不一致的现象 3.MVCC在幻读上值解决了快照度，但是不能解决“当前读”

MVCC可以解决快照读:如不加锁的select，原理就是MVCC使用快照来控制数据的读取范围，从而在RR级别避免了幻读，在select快照读时候，没有发现新的数据，但是新插入的同样的数据却报错，说明MVCC无法测地解决幻读； MVCC无法解决当前读，在select上加锁，使用当前读，幻读会出现； 所以要想真正解决幻读是需要加锁的；

  • 快照读 和 当前读
  1. 快照读（实际上为读相关的操作）：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。可以理解为将当前数据库的数据复制一份副本。在当前事务中，之后不管进行多少次 SELECT 查询，都是在副本中去取数据，所以不管数据库中后来是否又对数据进行了改变，都不会影响当前已经 BEGIN 的事务数据的读取。 简单的 SELECT 操作，属于快照读，不加锁。 SELECT * FROM user WHERE ?
  1. 当前读（实际上为写相关的操作）：在事务中操作数据前，还要去MySQL中 重新读取一遍该数据对应最新版本的记录，并且 当前读 返回的记录都会加上锁，保证其他事务不会再并发修改这条记录。以下两种方式都属于当前读，需要加锁： 特殊读 (加锁读): SELECT * FROM user WHERE id = xxx LOCK IN SHARE MODE; INSERT / UPDATE / DELETE / SELECT ... FOR UPDATE 等写操作。

4.LBCC机制

  • 主要解决什么:基于锁的并发控制

    LBCC是RR级别下特有的

  • 锁的模式：

    • 共享锁
    • 排它锁
    • 意向共享锁/读锁（IS，表锁类型，无法手动创建）：当需要对数据加行级读锁时，MySQL 会先向整个表加意向读锁。 MySQL 中语法： lock in share mode
    • 意向排它锁/写锁（IX，表锁类型，无法手动创建）：当需要对数据加行级写锁时，MySQL 会先向整个表加意向写锁。MySQL 中语法： for update

  • 锁的算法：

    1. 记录锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）：
    2. 介绍：临键锁 = 记录锁 + 间隙锁，是 RR-可重复读 隔离级别下独有的
    3. 目的：间隙锁的出现就是为了解决可重复读隔离级别下的幻读问题

5.集群模式，主从复制过程

  • 过程

    1. 首先在master上开启二进制日志
    2. master上数据发生变化，进行DML操作的时候，会产生二进制日志
    3. master上的dump线程会通知slave上的IO线程来拿二进制日志，IO线程拿到二进制日志后会写入到slave上的中继日志，然后SQL线程会去读取新产生的中继日志relary.log，重演二进制日志里的操作，从而达到slave和master上的数据一模一样，实现数据的一致性。

  • 复制模式:

    1. 异步复制
    2. 半同步复制（一个从返回ACK就可以）
    3. 同步复制，提交一条确认一条

  • 注意点

    1. 主服务器通常会主动推送二进制日志事件给从服务器，而不是从服务器主动来取；

image.png

6.树结构分类 （1）. 二分查找树

  • 规则：

    • 左子树的节点值<父节点
    • 右子树的节点>父节点

  • 缺点：

    • 不能平衡 会造成斜树 形成了链表 造成循环查询

（2）. 平衡二叉查找树 AVL树

  • 规则
    • 左子树的节点值<父节点
    • 右子树的节点>父节点
    • 左右子树深度绝对值不能超过1
    • 左旋和右旋来实现 image.png

（3）. 多路平衡查找树 B树

  • 规则：

    • 父节点可放N个 子节点分支为N+1个 通过分裂 和 合并保持平衡
    • 具有平衡二叉树的特点

  • 缺点：

    • 查找数据需要加载到innodb的内存空间中，磁盘块过少只放了一个磁盘块，对比的时候IO次数过多；
    • 平衡二叉树数深度很深，而多路平衡查找树深度减少，减少IO的次数，将索引的结构尽可能多的加载到innodb的buffer pool中；

  • 为什么用自增主键或有顺序的当作索引

    • 防止频繁的分裂合并 导致页page分裂 磁盘空间碎片 和 page的重新分配；所以索引必须是有序的

image.png

（4）. B+树结构索引 + 存储索引数据

  • 规则

    • mysql用到的索引数据结构
    • 父节点和子节点的关系是n:n的关系
    • 只有叶子节点存数据
    • 叶子节点有双向指针，有序的双向链表

  • 优点

    • B树能解决的 B+数据都能解决 （树的深降低）
    • 扫库，扫表的能力更强（叶子节点就可以扫描全表）
    • 磁盘读写能力更强 （树比较低，IO的深度较低）
    • 排序能力够强 （范围查询更强）
    • 效率更稳定 （最少3次是为了平衡随机查询 中和所有的查询）

image.png

  • 扩展点

    • page=16kb
    • 假设一个叶子节点可以存入10条记录（每条记录是1600bytes）
    • 假设内节点可以存入10000个键值+子节点指针的单元（一个单元16bytes） image.png

  • 为什么不用hash结构索引

    • hash索引不是innodb的索引类型
    • 在使用其他存储引擎使用，比如：memory：容易发生hash冲突
    • 查询比较快 但是是个鸡肋 虽然索引是有序的 但是hash值是无序的 这样不利于比较和范围的查找
    • 自适应hash索引 是innodb自动创建的与buffer pool中适应 用户不能直接创建

（5）.红黑树

  • 规则

    • 红黑树的约束：最大长度不能超过最小长度的两倍
    • 节点分为红色和黑色
    • 根节点必须为黑色
    • 叶子节点都是黑色的unll节点
    • 红色节点的两个字节点都是黑色（不充许两个相邻的红色节点）
    • 从任意节点出发，到其每个字节点的路径中包含相同数量的黑色节点

  • 为什不用红黑树做mysql的索引

    • 不够平衡；
    • 最大长度不能超过最小长度的两倍
    • 一般放在内存使用treeMap为红黑树
    • 范围查询慢

7.索引优化方案策略

image.png

image.png

8.四大特征

  • 原子性
  • 隔离性
  • 持久性
  • 一致性

9.隔离级别 image.png

10.脏读 不可重复度 幻读

  • 脏读：并发情况下，一方事务读到了另一方事务 “已 update 但未 commit” 的数据，破坏了事务隔离性。
  • 不可重复读：并发情况下，一方事务读到了另一方事务 “已 update 或 delete ，并 commit ” 的数据，破坏了事务隔离性。
  • 幻读：并发情况下，一方事务读到了另一方事务" insert/delete 并 commit "的数据，导致前后读取记录数不一致（行记录数多了或少了都算是幻读）

11.innodb的缓存区buffer pool

  • show variables like "%innodb_buffer_pool%"
  • 大小 固定128M 开发中要是服务器内存的80%
  • 内存写满 淘汰策略 LRU算法 从中优化 如下图：

image.png

  • 提供读写的效率-为什么效率会高 与IO有关

image.png

  • change buffer 是buffer pool一部分 不需要将磁盘唯一索引页加载，不见查唯一性；大小调整：show variables like "innodb_change_buffer_max_size" 作用：提供增删改的效率
  • log buffer 数据redo log 的缓存区 会定时刷盘到磁盘中；刷盘策略：0:1秒钟执行一次 1.commit一次执行一次 2.先写入操作系统中 操作系统进行1秒刷一次
  • show variables like "innodb_flush_log_at_trx_commit"
  • buffer pool 中放入的是指针 不是数据页的内容
  • 解决部分写失效-dubbowrite buffer

image.png

12.数据最大能放多少量的数据

  • 没有明确说明，大约每张表在2000w之内，具体根据业务来定；

13.mysql有哪些函数可使用

  1. SELECT ABS(-1)
  2. SELECT AVG(score) FROM sc;
  3. SELECT CEIL(1.5);
  4. SELECT CEILING(1.5);
  5. SELECT SUM(score) FROM sc;-SUM(expression)返回指定字段的总和
  6. SELECT FLOOR(1.5);-FLOOR(x) 返回小于或等于 x 的最大整数
  7. SELECT GREATEST(3, 12, 34, 8, 25);-GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值
  8. SELECT LEAST(3, 12, 34, 8, 25);=LEAST(expr1, expr2, expr3, …) 返回列表中的最小值 1.SELECT MAX(score) FROM sc;=MAX(expression)返回字段 expression 中的最大值
  9. SELECT MIN(score) FROM sc;
  10. SELECT RAND();-RAND()返回 0 到 1 的随机数
  11. SELECT ROUND(1.23456);-ROUND(x)返回离 x 最近的整数
  12. SELECT TRUNCATE(1.23456,3);-TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）
  13. SELECT LENGTH('1234');-LENGTH/CHAR_LENGTH(s)/CHARACTER_LENGTH(s)返回字符串 s 的字符数
  14. SELECT CONCAT('hel','llo');-CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串
  15. LTRIM(s)去掉字符串 s 开始处的空格-SELECT LTRIM(' RUNOOB ');
  16. SELECT SUBSTR("RUNOOB", 2, 3);
  17. SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
  18. SELECT ADDTIME('2011-11-11 11:11:11', 5);

13.其他解释

  • 回表：从非聚集索引查询到结果后，要去根据id查询聚集索引树，从叶子结点拿结果；回表多次按查询结果定；
  • 索引覆盖：在非聚集索引树中有name age sex 三个值，这三个值是联合索引，这时候根据name查询 age时候就用到了索引覆盖；
  • 索引条件下推：当联合索引name，age，sex 查询条件是为where name=“xxx” and sex=“男” age<15时候，就会在存储引擎层过滤age<15这个条件
  • 聚集索引： 主键索引，也叫一级索引，一个表中只有一个聚集索引；只有叶子结点存储数据；
  • 非聚集索引：一个表可以有多个非聚集索引；
  • 联合索引：a b c 三个字段一起做索引，遵循最左匹配原则；
  • 索引失效：%在前面会失效 类型转换会失效；
  • 离散度：离散度过低会影响优化器选取索引
avatar
文章
阅读
粉丝