MySQL

https://www.cnblogs.com/better-farther-world2099/articles/14768929.html

MySQL 数据是存放在磁盘中的，如果每次操作都是对磁盘进行，那么肯定很影响性能。所以一定是先把数据从磁盘中取出来，放在内存中，再在内存中操作。 这一块区域就是 buffer pool。它由许多的数据缓存页和控制块组成。默认大小时 128M，每页大小是 16k，控制块为数据页的 5%。实际应用中，对数据的增删改 都是在 buffer pool 中操作，然后再由 IO 线程写入磁盘。同时为了避免数据库崩溃等异常情况，导致未成功写入磁盘，引入了 redo 日志，记录了之前做过的增删改操作， 重启后，读取文件内操作，重新执行一遍，即可恢复。当然，对于数据更新过程，还有一套严格的步骤，涉及到 undolog、binlog、提交事务、buffer pool 占数据刷回磁盘等。 通过这种方式，保证在内存中操作，然后顺序写回磁盘，提高性能。（内存速度高于磁盘，顺序写高于随机读写）

工作流程为：在查询的时候，先在 buffer pool 中查询对应的数据页，如果没有从磁盘读取，存在 buffer pool 中，下次查询直接从 buffer pool 中获取。

不同于查询缓存，查询缓存是以 key-value 的形式，存储相同查询 sql 的结果。buffer pool 是属于引擎层的。

buffer pool 是以页为单位进行存储的，每个缓存页都对应一个控制块，里面包含数据页所属的表空间、编号以及在 buffer pool 中的地址。

buffer pool 中页分三种， 空闲页、干净页、脏页，空闲页是未被使用的页，干净页是指内存的数据和磁盘数据一致，脏页是指内存中的数据已被修改，还没有刷回磁盘，当刷回磁盘后变为干净页。

基于链表 + LRU + 冷热分离来管理的。

LRU 是指最近最少使用算法，原理就是将使用的元素放在队列头部，这样尾部即为最少使用的元素，当队列满了，淘汰最末尾的即可。 但是在 MySQL 使用中会出现 2 个比较常见的问题:

MySQL 因此改进了 LRU 算法，将其分为2个区域，热数据区和冷数据区，当加载页时，先放在冷数据区，1s 内被再次访问就移动至热数据区，时间可设置。 为何设置成1s，是因为 1s 内如果数据不在被访问，那么再次被访问的几率就不大了。时间可以调整。

视情况而定，有好处也有坏处。

优点： - 提高检索效率，降低数据库IO成本 - 通过对索引列进行排序，降低数据排序的成本，降低cpu的消耗 缺点： - 创建和维护索引需要耗费时间，随数据量增加而增加 - 需要占用物理空间 - 数据进行增加、修改、 删除，索引也需要维护 创建索引的原则： - 在经常需要搜索的列创建索引 - 主键、外键 - 范围搜索 - 排序 不建或少建索引： - 用不到的字段查询 - 表记录比较少 - 经常需要修改的字段 - 参与列计算的 - 区分度不高的字段

聚簇索引，索引和数据在一起，非聚簇索引，索引和数据分开存储，索引的叶子节点指向了数据对应的位置

主键采用聚簇索引，采用B+数结构，叶子节点就是行记录。

聚簇索引的优点 - 范围查找 - 查找目标数据，不需要再次io回表查询 - 覆盖索引

缺点 - 插入速度严重依赖插入顺序 - 更新主键的代价很高，因为会导致行移动 - 二级索引访问，需要回表查询

根据联合索引最左边的字段进行排序，然后再按照第二个排序，依次…​…​…​

没有索引下推时，会根据索引来查找记录，然后根据 where 进行数据过滤，会造成多次回表， 使用索引下推后，在取出索引的同时，判断是否可以根据where条件进行数据过滤，减少回表次数，提高性能。

InnoDB 引擎根据查询统计发现某一查询满足hash索引的数据结构特点并且达到一定次数，就建立一个hash索引，底层是散列表，存在于缓存中，只适合搜索等值的查询，范围查询是不能使用的。 其目的减少b+树路径搜索，可以根据索引快速定位到叶子节点。

索引的创建是有序的，如果首字母无法确认，也即%在左侧，这意味着所有的索引都要进行判断，如果在右侧，则可以根据首字母从左到右依次比较，可以过滤一部分数据，是可以用到索引的。 当然还有一种特殊情况，如果查询的字段中都在组合索引中，那么也会走索引，也是全部扫描，但是组合索引由于不会存储完整的数据行，那么检索效率还是高于全表扫描的。

UUID - 不会冲突，数据拆分合并，都能保证主键全局唯一性 - 可以在应用层生成，提高数据库吞吐能力 - 影响插入速度，与自增相比，最大缺陷就是随机io，新增的uuid，值不一定比以前的大，无法按顺序放在数据后，涉及到插入旧的数据之间，造成数据移动，影响性能。 - 会消耗更多的存储空间，操作比整型慢

推荐还是使用自增。

B树 - 每个节点包含索引和数据，如果数据量过多，会导致树的高度增加，增加磁盘io

B+树 - 一个节点有多个元素 - 叶子节点都冗余了非叶子节点数，有数据冗余 - 叶子节点有单项指针

B+ 树在提高IO性能的同时还能解决 B 树遍历效率低的问题。B+ 树只要遍历叶子节点即可实现整颗树的遍历。

MySQL 以页为单位进行读取存储。页包含双向指针、页目录以及用户数据区域。页目录分组缩小查询范围、提高查询效率，以空间换取时间。

show global status like 'Innodb_page_size' - 16384 , 16kb

MySQL设计者将 B+ Tree 的节点大小设置为一个页，这样只需要一次 io 就能全载入，每页大小16k，所以节点也是 16k，B+Tree的根节点保存在内存中，子节点才是存储在磁盘中。

计算公式： 根节点指针数 * 单个叶子节点的记录行数

id：操作顺序 select_type: 查询类型 table：查询的表 partitions：分区 type: 表示连接类型，以什么样的方式来获取数据，简化后的从好到坏 system const eq_ref ref rang index all possible_keys: 可能使用到的索引 key：使用的索引 key_len： ref: rows: 读取行数 filtered：过滤行数 extra： 额外信息

type: - system 不进行磁盘id，查询系统表，仅返回一条数据 - const 查询主键索引，返回 1 条或 0 条数据，属于精确查找 - eq_ref 查找唯一性索引，返回数据最多一条，属于精确查找 - ref 查询非唯一性索引，返回多条数据，属于精确查找 - range 查找某个索引的部分索引，只检索给定范围的行 - index 查找所有索引树，比all快 - all 不使用任何索引，全表扫描

extra:

一般性分页，使用 limit offset,rows, 第一个是偏移量，0 开始，rows 表示返回的行数

开启慢查询日志： - slow_query_log 开启还是关闭 默认关闭 - slow-query-log-file 日志储存路径 - long-query-time 慢查询阈值，大于此时间就是慢查询

性能下降原因： - 等待时间长，锁表导致 - 执行时间长，查询语句烂，索引失效，join太多，服务器参数配置

慢查询优化思路 - 优先选择高并发的sql - 定位性能优化的性能瓶颈， io数据访问，看是否用到了索引，cpu，看数据预算是否花费了太多时间，网络带宽 - 明确优化目标，确定优化程度用户体验好 - 通过 explain 入手，明确 sql 的执行状态 - 永远用小的结果集驱动大的结果集，获取连接次数少，减少资源消耗 - 尽可能在索引中完成排序 - 只获取自己需要的列 - 只使用最有效的过滤条件，最短路径获取数据 - 避免复杂的 join 和子查询，建议不超过 3 张表，将复杂的 sql 拆分成小 sql，通过代码层进行封装 - 合理设计索引并利用

Buffer Pool， 增大值，可减少对磁盘的 io 操作，在有大量事务进行更新、删除、插入时，可有效提高性能。

增加依据： 根据命中率，低于90，则可以考虑增加缓冲池大小。缓存查询次数 / (缓存查询次数 + 磁盘查询次数) * 100

数据库一页 16k，操作系统一页 4k，需要分 4 次写入，如果只写了一部分，那么就会造成数据丢失。

获取的数据都在索引上，不需要回表

事务的持久化是为了应对系统崩溃造成的数据丢失，只有保证了事务的一致性，才能保证执行结果的正确性。

MVCC 多版本控制器，核心就是 Undo log 多版本链 加 Read view, 通过 undo log 来保存数据的历史版本，实现多版本管理。CC 是通过 read view 来实现管理的，通过read view 原则来觉得数据是否显示。针对不同的隔离级别，read view 的生成策略不同，也就实现了不同的隔离级别。

Undo log 多版本链，每条数据都有两个隐藏字段 - trx_id: 事务id，记录最近一次更新这条数据的事务id - roll_pointer: 回滚指针，指向之前生成的 undo log

Read view: 将某个时刻的数据记录下来，查询时从 undo log 最新一条记录进行对比，如果不符合比较规则，则回滚到上一条记录进行对比，直到得到符合比较条件的查询结果

当一个事务查询sql时，会生成一致性视图 read-view, 按照规则来查找合适的数据

规则 - trx_id < min_id: 事务已提交，可读 - trx_id > max_id: 将来事务生成的，不可读 - 中间情况，分，当前在未提交的事务中，那么不可读，否则可读。

普通的查询时快照读，是不会看到别的事务插入的数据的， 只有在当前读时才会出现，例如 select …​ for update 语句。 当前读读取的是数据库最新的数据。

按操作类型：分读锁和写锁 按操作粒度分：表级锁、行级锁、页面锁 开销、速度、粒度、死锁、冲突概率、并发读 按操作性能： 乐观锁（数据版本比较，提交时检查）、悲观锁（提前锁定，避免其他人操作）

行级锁包含共享锁和排他锁，必须在使用 InnoDB 引擎以及开始事务隔离级别为可重复读。

共享锁，当事务对数据加上共享锁后，其他用户可以并发读取数据，但不能进行修改，直到释放 排他锁，当事务加排他锁后，其他事务不能加任何类型的锁，获取锁，既可以读，也可以写

一般情况，insert update delete 都是默认加排他锁，select 默认不加锁，但是可以显示的镜像加共享锁和排他锁，lock in share mode, for update

共享锁兼容共享锁，其他组合都不兼容。

通过对索引数据页上的记录加锁的，主要算法有3种： record lock/ gap lock / next-key lock

在可重复读的隔离级别下，先采用 next-key lock，当操作含有唯一所有时，会进行降级，recordlock，仅锁住索引本身而非范围

数据库为了高并发的数据访问，对数据进行了多版本处理，并通过事务的可见性来保证事务看到自己应该看到的数据版本，只在 Read Commited，Repeatable Read 下工作

行记录有三个隐藏字段，db_row_id/db_trx_id/db_roll_ptr - 没有显示定义主键，也没有定义唯一索引，会自动添加一个 row_id 隐藏列作为主键 - 事务进行增删改时，会将这个事务 id 插入到 trx_id 中 - roll_ptr： 回滚指针，指向 undo log

undo log 多版本链 read view

读已提交，每次都会生成一个 readview，导致每次都读到不同的数据 可重复读，一个事务中，只有第一次生成，可以解决不可重复读问题。

表锁，一个事务锁住了a表，然后访问b表，此时b被另一个事务锁住，同时需要访问a表，互相等待释放，造成死锁，调整程序操作逻辑

行级锁 - 全表扫描，行级锁上升为表锁，多个事务执行后，很容易产生死锁和阻塞，解决办法就是 explain 分析，处理全表扫描 sql，建立索引优化 - 两个事务想拿到对方持有的锁，互相等待，产生死锁。对索引加锁的顺序不一致导致，如果可以，尽量以相同的属性来访问索引记录，在程序批量处理数据时，如果对数据进行排序，以固定的顺序来处理记录，可降低死锁可能

undo log 用于回滚的日志，更新之前会记录在这个里面，回滚时通过这个来回滚，在事务开始前产生，事务提交后，不会立即删除，放入待删除列表，交给后台线程处理。 - 提供回滚操作，记录回滚时需要操作的sql记录 - 提供多版本控制 mvcc

redo log：用于数据库持久性，事务提交成功，那么数据库修改就永久保存下来。 如果每执行成功一个事务，就将数据写入磁盘，会有严重的性能问题。因此设计了 redo log，来记录事务对那些数据页做了修改，就能解决性能问题，文件更小而且是顺序IO。 先将记录写入 redo log buffer，后续将多个操作写入redo log file。当发生故障、崩溃后，重启服务，可以通过此文件进行恢复。

bin log 用于记录数据库执行的写入性操作，不包含查询，以二进制的形式保持在磁盘中。 - 主从复制 - 数据恢复

将同一个数据库中的数据分散存放到多个数据库上，以达到分散单台设备负载效果。主要解决因数据量过大导致数据库性能降低。

是通过将主服务器的 binlog 负责到从服务器，然后执行。从服务器会启动一个 io 线程读取日志，以及一个执行线程，执行 binlog

客户端发送sql请求 -》查询缓存 -》 解析器-》解析树-》预处理器-》新解析树-》查询优化器-》执行计划-》查询执行引擎-》返回结果

5.7 支持，8.0 之后废弃

可在应用层进行缓存，例如 redis encached 等等。

A: 原子性由 undo log 保证，他记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql C：一致性由其他三大特性保证，程序代码要保证业务上的一致性 I: 隔离性由 MVCC 来保证 D：持久性由内存 + redo log 来保证，宕机可以从 redo log 恢复。

Master 一个线程，binlog dump thread, 当 binlog 日志有变动时，线程读取内容发送给从节点。 从节点 IO 线程接收 binlog 内容，写入 relay log 文件中，从节点SQL线程读取 relay log 来进行重放。 默认采用异步负责，主库发送日志，不关心从库是否处理。存在丢失问题、但是性能高。可以选择全同步复制，必须等待从库响应才结束。 还有一种折中方式半同步复制，一个节点确认没问题，即可视为成功，其他节点就不考虑了。