image.png

1. SQL执行计划

image.png
重要字段：

  1. key中存在值，表示使用到了对应索引字段；
  2. type：访问类型（The join type 联接类型）
    1. system -> const -> eq-ref -> ref -> fulltext -> ref or null -> index merge -> unique -> subquery -> index subquery -> range -> index -> All；
    2. 尽可能少出现all，尽量在range之上；
  3. id
  4. extra

    2. 存储

    2.1

    image.png

    2.2

    image.png
  • 数据读取都是分块读取，以datapage的整数倍进行读取；
  • 读取时，以读取一个块数据后通过指针指向的下一块进行选择性读取，并非是按照物理连续空间的来读；

image.png

  • 思考：应当使用怎样的数据结构存储数据？

    2.3

    image.png

  • Memory存储引擎使用哈希索引

  • 弊端很明显

    • 补充：4. 存在扩容操作（开辟空间，数据迁移，消耗巨大，严重影响效率）

      2.4

      image.png

      2.5

      image.png

  • B树中每个节点中存放3种类型的多个数据

      1. key
      1. data
      1. pointer
  • image.png
    • 可以查到innodb的每个磁盘块是16KB
  • 思考：3层的B树能够存储多少条数据？（假设一个data为1kb，且不考虑其他类型数据的内存占用）
    • 约为16 16 16 = 4096
  • 存储的量很少，如果存储更多的数据需要加深B树的深度，但是又需要考虑到IO次数的问题，所以考虑是否可以将树“变胖”？

image.png

  • B+树
  • 树的最底层存放key和data，其他层只存放pointer和key
  • 初略计算：如果pointer+key组合约为10字节，一个data约为1kb大小，默认情况innodb的单个磁盘块为16kb，能够存放的数据为1600 1600 16 = 40960000条数据。
    • 如果key较大，那么直接影响存储的数据量，所以要求：key要尽可能少的占用空间；一般使用int类型
  • 问题：mysql树是几层？
    • 答：一般情况下，mysql数据库B+树是3层~4层，已支持千万级别数据的存储；
  • 问题：为什么要使用主键自增？
    • 答：一个已经排好序的索引结构，如果要往已存在的记录间隙中插入数据，就需要找到对应的key所在的位置，然后插入，但是如果对应的磁盘块满了，问题就会很麻烦，需要进行页分裂操作，扩增一个磁盘块，把原有的数据分别置于两个块中，多出一个磁盘块，必然导致树的上一层中对应区块的指针要多一个，如果恰好该磁盘块数据满了，那么就要求再进行页分裂，进而又会影响到更上一层，所以非主键自增的数据插入，将会严重影响索引的结构。
    • 但是如果使用主键自增，就可以直接在最后一层的最后一个节点后面添加数据，如果满则紧挨着最后一个节点分配一个新的磁盘块装数据。（上次一层的最后一个节点的最后一个指针指向最后一层之前的最后一个节点，遍历完该节点后立即就可以遍历新分配的节点获取数据）
  • 问题：为什么要进行逻辑删除？
    • 保留数据做业务分析；
    • 防止频繁的页合并；
  • 问题：页分裂，页合并为何影响性能？
    • 因为进行这些数据的操作，为保数据安全需要加锁处理，那么必然影响其他请求对数据的访问，并且以上操作都需要占用cpu资源，导致其他线程的工作效率降低，影响系统吞吐量，所以频繁的页分裂、页合并就会对服务器造成影响；
  • B+树结构的索引查找方式分为两种：
    • 从根节点进行二分查找锁定对应的叶子节点，遍历其中链表；
    • 直接在叶子节点中进行对索引值的范围查找或者分页查找；
  • 之所以存在这两种方式是由于B+树树最底层的特殊的双向链表结构；
  • 到底选择哪种方式则取决于MySQL优化器，通过优化器判断能得出最高效的策略；

    2.6

    image.png
    image.png
  1. mysql一个表可以创建多少个索引？
  • 多个索引
  1. 索引与B+树的对应关系？
  • 一个索引对应一棵B+树
  1. 多个索引的存在，数据会存储多少份？
  • 一份，表中数据不会冗余存储
  1. 索引的叶子节点中存放什么？
  • innodb存储引擎会有聚簇索引的概念，即以某一个字段建立一棵树特定的B+树，该树中索引值与对应行的数据绑定存放在一起，因此叶子节点中存放的是行数据；该字段选择方式：主键->唯一键（第一个）->rowid（6字节）
  • 其他索引叶子节点存放的是聚簇索引的索引值（一般情况通过回表去聚簇索引的B+树上获取数据）

回表：

  • 通过索引查询时，所查询的所有列不被索引列包含，在当前的B+树上没有获取到结果值，最后通过检索聚簇索引的B+树获取到行数据；

覆盖索引：

  • 所查询的所有列被索引列覆盖或者包含，在当前树结构中直接获取到数据，无需通过回表读取实际的数据行；

like以%开头导致索引失效：

  • %开头，对开头直接使用任意字符，mysql并不知道从哪个值开始匹配，因此只能全盘扫描，导致索引失效；
  • 使用覆盖索引，可以解决这一问题；

最左匹配：涉及联合索引

  • 存储时，排序先比较第一个字段，以某个值为基准，小于第一个字段的直接放到左边，如果相同，则比较第二个字段，如果小于放左边，如果大于放右边；
  • 自然，查找时也是这个道理，先根据第一个字段找，再根据第二个字段找，一直到最后一个字段；如果sql语句字段的顺序不会，优化器会自动进行调整以符合联合索引的索引字段顺序，如果中间缺失某个字段，则从该字段开始后面的字段不起到索引作用；

索引下推：ICP

  • 用户访问MySQL服务器的三层架构：客户端 -> 服务端 -> 存储引擎
  • select * form age = … and name = …
  • 该sql语句，如果不使用索引下推：
    • 则先通过age在存储引擎层面查找到数据，然后引入到server层，通过name字段对其中数据进行过滤，得到最终结果；
  • 如果使用索引下推：
    • 则直接根据age和name去存储引擎中获取到数据；

MRR：multi-range read 适用于range查询，5.6默认开启

  • 检索数据是通过非聚簇索引找到聚簇索引值，可能是多个值，原本得到的值不会进行排序，拿到这些值的一个列表后，直接去聚簇索引树上通过二分查找或者叫做随机查找得到数据；
  • 使用mrr后，则通过对非聚簇索引树上得到的多个值进行排序，那么就可以锁定这些值所在的范围区间，在聚簇索引树上就可以通过范围查找获取到对应的多个数据行；

2.6

Innodb：

  • innodb的索引与数据存放在一起：通过mysql目录下Data文件夹下可以看到会存在多组同名不同格式的文件image.png
  • .frm存放表结构相关数据
  • .ibd存放行记录和索引

MylSAM：

  • image.png
  • .frm表结构
  • .MYD数据
  • .MYI索引

是否使用主键自增：根据业务场景决定

  • 单机数据库推荐使用；// 非主键自增频繁页分裂页合并，主键自增能够减少页分裂

  • 分布式服务不使用；做分布式id

    2.7

    or：不确定是否走索引

  • 如果所有列都是索引列，使用or会走索引；（索引覆盖了）

  • 如果查询列并不都是索引列，则不会走索引；
  • 单纯使用主键使用or去查询会走索引；

总结：不发生回表，则走索引…？

3. 主从复制与优化

3.1

image.png

  • 分布式服务，必然会用到mysql集群，必然涉及：主从复制

    3.2

    image.png

  • 需要保证客户端在操作时，后端的各个服务器的数据一致，逻辑上等同于在操作一个数据库；

    3.3

    image.png

  • 关于优化，并不仅仅是考虑索引是否使用和失效而已；

3.4

image.png

  1. redis主从复制使用到了快照和日志两种方式实现数据一致，mysql则使用日志来实现；
  2. 日志分为多种，其中binlog是公用的，隶属于mysql服务器，各种存储引擎都会使用到；mysql通过binlog来实现主从复制的数据同步；
  3. binlog（Binary Log）：需要手动开启；

顺序IO效率远大于随机IO（需要考虑寻址时间）
image.png
随机寻址：

  • 小文件在写入的时候，为防止磁盘空间的浪费以及碎片化，会搜索可能存在的空白页，然后进行写入，这个过程就叫做随机写入；

数据同步的流程：
image.png
image.png

  • 存在线程概念：
    • IO Thread：用于读取主节点中的binlog，并写入从节点的日志中成为relaylog；
    • SQL Thread：用于读取relaylog并执行（重放）日志中的信息；

3.5

单个1G的文件与多个小的总和为1G的文件在读取

  • 速度有差异；
  • 多个文件的读取涉及随机读取的寻址问题；

延迟：复制延迟
image.png

  1. 主节点写入binlog是一个顺序写入的过程，此过程时延低；
  2. IO Thread读取binlog过程为顺序读，低时延；

  3. IO Thread写入RelayLog过程为顺序写，低时延；

    3.6 复制延迟

    延迟的产生：
    image.png
    重点是一下三个：3，4，6

  4. SQL Thread在Replay时，随机寻址问题造成时延；

  5. TPS并发量高，多个用户线程执行，对主节点写数据，从节点中IO Thread与SQL Thread处理能力有限，远远超过了单线程的处理能力，造成时延；（类似于生产者-消费者模式，生产消息速度大于消息消费速度）
  6. 大事务：无解，尽量避免大事务；

解决延迟：
MTS：multi-thread slave

  • 但是多线程处理log环境下，必须就要制订一种规则去保证各种冲突问题，比如：大事务，锁冲突，…

操作粒度与版本：
image.png

  • mysql目前使用最广的三大版本：5.6，5.7，8.0X
  • 其中5.6只支持库级别，5.7开始支持行的操作；
    • 5.6之前单线程，5.6之后多线程
  • 操作粒度小，那么执行操作时锁定的资源更少，多线程环境下的并发能力也就更强；

外部轮廓层面解决延时问题：
image.png
根本上解决延时问题：

  • MySQL5.6.3开始支持基于schema（库）的并行复制，由于大多情况为一库多表，所以不太适合生产；
  • 5.6 coordinator线程两个作用：
    • 判断是否可以并行执行，如果可以交给workder线程执行事务的二进制日志；
    • 若不可并行执行，如操作为DDL，或者操作需要跨schema，则必须等待所有worker线程执行完成后，然后开始执行coordinator中的当前日志；
  • MySQL5.7引入完全的并行复制功能，官方称之为enhanced multi-thread slave （MTS），即5.7之后基本不存在复制延迟问题。

image.png
image.png
并行操作中，可能出现并行事务问题，是不可以以轮询的方式将任务分发给各个worker线程的：

  • 因为各个线程之间存在争用cpu时间的情况，多个事务操作同一资源，会导致时间点颠倒或错位，造成最终数据不一致；

  • 查看work线程的个数（一般生产环境为8~16个，与CPU的核心数保持一致）：show variables like ‘%workder%’;

  • 查看数据库服务器的进程：show preocesslist;

所以使用并行复制，必须遵守一定的规则：

  1. 操作同一资源的多个事务必须分配给同一个worker线程；
  2. 同一个事务的多个sql语句必须分配给同一个workder线程；

协调器的分发规则：
image.png

  • 问题：如果涉及到表关联？
    • 将一整个SQL语句交给一个workder处理；

image.png
worker上添加标识，表示正在操作某一资源

GTID:
image.png
image.png

  • 不指定GTID，会自动生成匿名的GTID；

  • 没有GTID，就无法保证组信息，就无法保证组提交，因此并行复制也不存在；

3.7

image.png
image.png

  • undolog： 回滚日志
  • redolog： 前滚日志

crash safe

  • 两阶段提交：保证安全，只要进入两阶段提交过程，就能保证数据持久安全；
    • parepare：写完了redolog
    • commit： 写完了binlog

MySQL的数据安全主要体现两个方面：

  1. 能够恢复到任何一个时刻的数据状态；
  2. crash-safe：不管什么时候发生断电或突然崩溃，重启后数据都不会丢失；
  • 第一点通过binlog实现，通过replay binlog，一步一步的执行，产生对应时刻的数据；
  • 第二点通过二阶段提交的特性来实现，使用到了redolog；所谓crash-safe就是指innodb执行引擎，未提交的数据会回滚，已提交或者已开始提交的数据会能够进行恢复；只要开始进行事务提交，在事务提交的任何阶段出现问题，都能恢复到原来的数据情况；

两阶段提交的详细过程：

  1. redo log prepare：write
  2. binlog：wirte
  3. redo log prepare：fsync
  4. binlog：fsync
  5. redo log commit：write

write：写入到内存
fsync：写入磁盘，涉及到IO（此时需要保证一定量的写入，进而提高效率）
在保证足够量的写入的前提下，将binlog的write-fsync两个过程的中间时间差记作一个组；

  • redo log容量不会特别大，因为使用循环写入的机制；

https://zhuanlan.zhihu.com/p/142491549

3.x

日志的两阶段提交保证crash safe：
image.png
image.png

mysql 8.0X开始没有mysql缓存，原因是缓存命中率过低；

4. 锁

4.1

image.png

  • 锁与事务中隔离性关联；

    4.2

    image.png

  • 当前读与快照读

    4.3

    image.png
    MVCC：

  • 多版本并发控制；

  • 一行数据可能存在多个不同的版本数据；

隐藏属性：

  1. 隐藏id——6字节的row_id；
  2. 事务id
    1. 每行记录被操作后会被最后一次操作的事务id标记；
  3. 回滚指针
    1. 指向上一个版本的数据，当需要回滚时，则获取该地址的数据进行覆盖；

多版本之间的关系以及隐藏属性的实际作用：
image.png

  • 历史版本数据使用undolog来保存；
  • undolog实现了MVCC以及事务的原子性；

多个概念：read view，trx_list，up_limit_id，low_limit_id
image.png
可见性算法的比较规则：
image.png
image.png
image.png

  • 当前事务id：对应资源的最后被修改的事务的id

RC与RR：

  • 比较规则相同；

  • 之所以不同的RC与RR在读取并发读取时结果不同，是由于read view的生成时机不同；

    4.4

    image.png
    image.png
    image.png
    image.png
    当前隔离级别为RR：

  • 事务A进行一次快照读后，事务B开启并新增了一条数据；

  • 事务A再次进行快照读发现仍然只存在两条记录，但是进行更新（当前读）发现能够更新三条记录，出现幻读；

原因：

  • RR级别read view只会在快照读后生成一次，导致后面进行快照读时拿到的read view的版本一直是老版本，新的事务新增数据，通过与老版本read view进行可见性算法判断后，发现为不可见；

脏读：

  • 读到其他事务修改但未提交的数据；

5. 聚簇索引与非聚簇索引

image.png

优化

image.png

MVCC（multi version conccurency control）

不加锁能够保证安全；
多版本并发控制
MVCC
MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。保证事务的隔离性；
MVCC是一种多版本并发控制机制。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
image.png
image.png
image.png
image.png
image.png
image.png
image.png
事务4去快照读时，读到的是哪个版本的历史记录？

可能的关键点：

  • 事务是否提交
  • 各个事务的开启时机

image.png

  • 每个事务对应一个readview；不同隔离级别生成readview的机制不同
    • RC：每次快照读都生成一个；
    • RR：一个事务中，第一次快照读生成一个，后续快照读时，会延用第一次生成的；

image.png
image.png

  • 经过可见性算法，得出结论，可以是可以看到刚刚修改的记录的； ```latex 第一次测试：事务1，2同时开启，事务2修改name为lisi并提交，事务1快照读能够看到最新的记录； trx_list：1 up_limit_id：1 low_limit_id：3

DB_TRX_ID 2 可见性算法分析…. :可见

第二次测试：事务3，4同时开启，事务3先进行一个快照读，看到name是zhangsan，事务4修改name为lisi并提交，事务3无法快照读无法读到被修改的数据 第二次测试里面的第一次快照读： trx_list: 3,4 up_limit_id: 3 low_limit_id: 5

DB_TRX_ID: 2 可见性算法分析…. :能够看到事务2的记录

第二次测试里面的第二次快照读： trx_list: 3,4 up_limit_id: 3 low_limit_id: 5

DB_TRX_ID: 4 事务4提交后，仍然延用这个readview 可见性算法分析…. :DB_TRX_ID在属于列表中的活跃id，所以事务4的提交记录不可见 ```

存在的结论：（不一定准确需要推导）

  • RC：可以看到
  • RR：不能看到

问题：MVCC各种设计和可见性算法比较规则都相同，上述两种隔离级别产生不同的结果原因是？

  • 必然就是上述用来比较的值不同，而这些值的来源是readview，所以可以得出结论，两种隔离级别之所以产生不同的结果是因为产生的readview不同或者说产生的readview的时机不同；

image.png

  • RR在某一个事务中，一直进行快照读，可能读的都是历史版本，但是进行一个当前读后，再去快照读那么就是新版本的数据了；
  • 间隙锁解决RR中出现幻读https://www.jianshu.com/p/32904ee07e56
    • select … for update开启
    • 使用范围条件检索数据，并且使用的非聚簇索引和非唯一索引字段，并请求共享锁或者排他锁时，innodb会给符合条件的记录和符合条件不存在的记录都加锁，不存在的记录被称为间隙，加锁，即间隙锁；进而防止了其他事务往这些记录上插入或者修改记录到这些位置；新的记录无法插入，保证了不会产生幻读；
    • 触发条件：
      • RR
      • 当前读
      • 查询条件能够走索引；

不可重复读：同一事务，两次快照读取不一致，第二次读到了修改了的数据；

  • 原因：因为RC级别，readview每次会在快照读后重新生成，经过可见性算法分析后，每次都能读到最新的数据；如果在两次之间，有其他事务进行修改并提交，那么两次读取的数据就会不一致；
  • RR通过修改readview的生成机制，避免了不可重复读的问题；RR:多次快照读只生成一次readview，其他时候都是延用第一次的readview，只有当当前事务进行一次当前读后，下次进行快照读时才会重新生成（这也是造成幻读的原因）；

幻读：同一事务，两次快照读取不一致，第二次读到了新增的数据；

  • RR级别无法解决该问题，实例：事务A，先进行快照读，当前表中命中3行记录；另一个事务B此时完成了数据的插入，并且提交；事务A再完成一次当前读后，再进行快照读，发现mad怎么有4行记录了，幻读了；
  • RR如果都是快照读，不会产生幻读，只有快照读当前读都有的情况下，才会出现幻读；

查看锁信息(在事务执行的时候查)
show engine innodb status\G;
set global innodb_status_ouput_locks=1;

事务：

image.png
image.png
image.png

image.png
为什么不直接写入磁盘，而是要先写日志？

  • 日志是顺序写，直接追加（append）就可以了，效率远远远远高于磁盘中去找数据块中的对应数据的随机写；所以优先完成日志，更加能够保证当前数据的不丢失或者说持久化；

两阶段提交：

binlog 属于mysql服务，实现备份库或者临时库的信息恢复；
redo log 属于innodb执行引擎这种插件，实现对当前所在的innodb中数据磁盘块信息的恢复；

过程：

  • 获取数据；
  • 更改数据，写入内存；
  • 写redo log，完成后redo log状态为prepare；
  • 写binlog；
  • redo log状态改为commit，进行事务提交；

如何保证崩溃后的数据恢复？

  • 通过日志来实现，binlog可以实现从库的数据恢复，通过读binlog写入从库relay log，然后repaly；主库可以直接通过redo log实现对应磁盘块数据的恢复；
  • 但是需要保证redo log与binlog信息的一致性，才能保证数据恢复的一致性；
  • 如何保证？ 两阶段提交！
  • 通过两阶段提交，实现了，只要进入事务提交阶段，就能保证数据恢复的一致性；
    • 如果binlog没写完挂了，那么考虑redo log在prepare状态，而binlog与redo log信息不一致，那么撤销redo log中此次事务，保证binlog 与 redo log一致；
    • 如果binlog写完了挂了，那么考虑redo log在prepare状态，而binlog与redo log信息一直，那么修改prepare为commit，保证binlog 与 redo log的一致；

锁：

image.png
image.png

  • 间隙锁解决RR中出现幻读https://www.jianshu.com/p/32904ee07e56

    • select … for update开启
    • 使用范围条件检索数据，并且使用的非聚簇索引和非唯一索引字段，并请求共享锁或者排他锁时，innodb会给符合条件的记录和符合条件不存在的记录都加锁，不存在的记录被称为间隙，加锁，即间隙锁；进而防止了其他事务往这些记录上插入或者修改记录到这些位置；新的记录无法插入，保证了不会产生幻读；
    • 触发条件：
      • RR
      • 当前读
      • 查询条件能够走索引
      • 所选范围条件覆盖不存在的记录

  • 记录锁：对某个特定的单行记录进行上锁；

  • 临键锁：为记录锁与间隙锁的组合，使用时会锁定索引区间也锁定索引值对应记录；

image.png