反补面试官MySQL

小汤圆真可爱
132 阅读13分钟

索引

  • 哪些地方加索引

    • where、聚合函数、order group、避免回表、关联查询

  • 普通索引与唯一索引

    • 字面意思
    • 唯一索引不能使用 change buffer , 频繁改动时不好
    • 查找时唯一索引好，但是普通索引都链接着性能损耗几乎不计

  • 定位分析

    • slow-log
    • explain
    • trace
    • show processlist

  • 索引失效

    • 函数操作 2020-07-01 2020-01-01 00:00:00
    • 隐式转换
    • 模糊查询(ES替换方案)
    • 计算操作，操作符号推荐写在后面
    • 状态字段散列值太低不适合，需要回表

  • 索引原理

    • hash
      • 不支持范围查询、hash碰撞后链表链接
    • B树+
      • 首先二分法，查找快，修改时效率不高
      • 二叉树，但是会失去平衡
      • 平衡二叉树，瘦高，磁盘查找次数太多
      • B树，加粗，每个节点都有key和data，每一个页的节点数比较少
      • B+树
        • 叶子节点包含全部
        • 叶子节点用指针链接
        • 非叶子节点只存key

  • 索引使用

    • 前缀索引：取前几位、拆开存(邮箱)、反转(身份证)，函数加工也有开销

  • order group

    • 加index、file_sort_buffer、单路排序双路排序
    • 联合索引、覆盖索引
    • 去掉不必要的返回字段
    • 不排序的时候order by null
    • 排序失效：联合索引两个字段范围查询、两个字段asc desc

  • 联表

    • 小表n驱动大表m，被驱动表有索引，n次
    • 被驱动表无索引，驱动表加到内存中 n + m 次，内存中 n * m 次
    • 还可以创建一张临时表进行优化

  • 分页

    • 按id来、联表取出ID

  • count

    • redis替换、专门建立一张统计表事务中统计且 update = update + 1

锁

  • 全局锁
    • 库备份
    • 主库用业务不同意，从库用主从延迟 -- single-transaction
  • 表级锁
    • 表索
    • 元数据锁
      • no slow log、commit及时、避免大事务、避免业务高峰时DDL
  • Innodb表：是根据索引来的
    • 两阶段锁
    • 共享锁 LOCK IN SHARE MODE
    • 排他锁 FOR UPDATE
    • GAP锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）
      • 防止幻读，以满足相关隔离级别的要求
    • 死锁
      • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
      • 应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
      • SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施

事务

  • 分布式事务

    • 消息中间件做媒婆，并发低、单机故障、脑裂

  • 原子性

    • 一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态
    • 原理：InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子

  • 持久性

    • 事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响
    • 原理：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求

  • 隔离性 -问题 - 脏读 - 不可重复读：数据变了 - 幻读：行数变了

    • 事务隔离级别

      • 读未提交：脏读、不可重复度、幻读
      • 读已提交：不可重复度、幻读
      • 可重复度：幻读（MVCC登场）
      • 可串行化：无

    • MVCC多版本并发控制 (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

      • 最大的优点是读不加锁，因此读写不冲突，并发性能好
      • InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列(也可以称之为标记位)和undo log
      • 其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等
      • 当读取数据时，MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC

    • (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性

      • InnoDB实现的RR通过next-key lock机制避免了幻读现象
      • next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)
      • 此时的next-key lock并不是真的加锁，只是为读取的数据增加了标记（标记内容包括数据的版本号等）；准确起见姑且称之为类next-key lock机制，根据版本号回退找结果

    • 总结

      • InnoDB实现的RR，通过锁机制、数据的隐藏列、undo log和类next-key lock，实现了一定程度的隔离性，可以满足大多数场景的需要
      • 不过需要说明的是，RR虽然避免了幻读问题，但是毕竟不是Serializable，不能保证完全的隔离

    • www.cnblogs.com/kismetv/p/1…

  • 一致性

    • 事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态

日志

  • 重做日志（redo log）
    • 确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性
    • 物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的
    • 事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中
    • 当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）
  • 回滚日志（undo log）
    • 保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
    • 逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的
    • 事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性
    • 当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间
  • 二进制日志（binlog）
    • 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步，用于数据库的基于时间点的还原。
    • 逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句
  • 错误日志（errorlog）
    • 错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息
  • 慢查询日志（slow query log）
    • 慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句
  • 一般查询日志（general log）
    • 记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。 因此，Mysql默认是把General log关闭的
  • 中继日志
    • 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中
    • 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中
    • sql执行线程——执行relay log中的语句；

其他

  • 三范式

    • 数据表的每一列都要保持它的原子特性，也就是列不能再被分割
    • 对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖
    • 对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖

  • Innodb四大特性

    • insert buffer
      • 对于为非唯一索引，辅助索引的修改操作并非实时更新索引的叶子页,而是把若干对同一页面的更新缓存起来做合并为一次性更新操作,转化随机IO 为顺序IO,这样可以避免随机IO带来性能损耗，提高数据库的写性能
    • Double write
      • Double write 是InnoDB在 tablespace上的128个页（2个区）是2MB
      • 当mysql将脏数据flush到data file的时候, 先使用memcopy 将脏数据复制到内存中的double write buffer ，之后通过double write buffer再分2次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题,如果发生了极端情况（断电），InnoDB再次启动后，发现了一个Page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了
    • 自适应哈希
      • InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引
    • read-ahead
      • InnoDB 提供了两种预读的方式，一种是 Linear read ahead，由参数innodb_read_ahead_threshold控制，当你连续读取一个 extent 的 threshold 个 page 的时候，会触发下一个 extent 64个page的预读。另外一种是Random read-ahead，由参数innodb_random_read_ahead控制，当你连续读取设定的数量的page后，会触发读取这个extent的剩余page

  • 秒杀

    • 单独服务，坏了也不影响其他业务
    • 秒杀链接加盐
    • redis集群
    • nginx负载均衡 控制恶意请求
    • 资源静态化 cdn
    • 按钮控制
    • 限流：前端多点几下、后端redis+异步
    • 削峰

  • MyISAM 和 InnoDB

    • InnoDB支持事物，而MyISAM不支持事物
    • InnoDB支持行级锁，而MyISAM支持表级锁
    • InnoDB支持MVCC, 而MyISAM不支持
    • InnoDB支持外键，而MyISAM不支持
    • InnoDB不支持全文索引，而MyISAM支持

  • varchar与char的区别以及varchar(50)中的50代表的涵义

    • char是一种固定长度的类型，varchar则是一种可变长度的类型
    • 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

  • in 和exists 区别

    • mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的
      • 如果查询的两个表大小相当，那么用in和exists差别不大
      • 外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个
      • not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快

SQL生命周期及关键字顺序

  • 应用服务器与数据库服务器建立一个连接

  • 数据库进程拿到请求sql

  • 解析并生成执行计划，执行

  • 读取数据到内存并进行逻辑处理

  • 通过步骤一的连接，发送结果到客户端

  • 关掉连接，释放资源

  • FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1

  • 对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2

  • OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留表中未找到 匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的 结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置

  • WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4

  • GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5

  • CUTE|ROLLUP：把超组插入 VT5，生成 VT6

  • HAVING：对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7

  • SELECT：处理 SELECT 列表，产生 VT8

  • DISTINCT：将重复的行从 VT8 中删除，产品 VT9

  • ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)

  • TOP：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者

  • CAP

    • 指的是在一个分布式系统中，一致性（Consistency）、可用性（Availability）、分区容错性（Partition tolerance）。CAP 原则指的是，这三个要素最多只能同时实现两点，不可能三者兼顾
avatar
文章
阅读
粉丝