mysql 思维导图模板_ProcessOn思维导图、流程图

sql是如何执行的

流程图片

连接器

管理连接，权限验证

查询缓存

查询缓存以key-value的形式存在内存中,key是查询语句

查询缓存失效频繁，只要有对表的更新，则该表所有的缓存都会失效

mysql提供按需使用方式,通过设置query_cache_type为DEMAND并在查询时显式指定使用缓存select SQL_CACHE * from T where ID=10

查询缓存失效频繁，只要有对表的更新，则该表所有的缓存都会失效

MySQL 8.0 版本直接将查询缓存的整块功能删掉了

分析器

词法分析、语法分析

优化器

执行计划生成，选择索引

执行器

操作引擎，返回结果

mysql引擎

引擎种类

InnoDB 和 MySIAM 区别 

考虑事务、锁、外键、索引

InnoDB 与 MySIAM 性能比较

考虑聚集索引、非聚集索引的区别

为什么InnoDB支持事务

考虑redo log 和 undo log、锁的粒度

innodb 特性

mysql事务

事务的特性 ACID

A：原子性  C：一致性 I：隔离性  D：持久性

事务的实现

考虑undo log、redo log 、锁

并发事务带来的问题

脏读、不可重复读、幻读问题

事务的隔离级别

读未提交 Read Uncomitted

什么问题都可能发生

读已提交 Read Committed

解决了脏读问题，可能有不可重复读、幻读问题

可重复读 Repeable Read（默认）

解决了脏读、不可重复读问题，但是可能有幻读问题

Innodb 引擎下采用MVCC解决了幻读问题

串行化 Serializable

什么问题都不会发生

分布式事务

概念

解决方案

2PC、3PC、TCC补偿...

锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL）

使用场景：全库逻辑备份

表级锁

表锁

表锁的语法是 lock tables … read/write

元数据锁(meta data lock ，MDL)

MDL 不需要显式使用，在访问一个表的时候会被自动加上

MDL 的作用是，保证读写的正确性

行锁

行锁 Rocord Lock

锁住一行

间隙锁 Gap lock

锁住一个范围，前开后开

Next-Key Lock（行锁 + 间隙锁）

锁定一行+一个范围

行锁分类

读写锁

读锁（共享锁 S锁）

select... lock in share mode

写锁（排他锁 X锁）

select... for update

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。

意向锁(意向共享锁、意向排他锁)

InnoDB的表级锁，其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型

加锁规则

1、原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

2、原则 2：查找过程中访问到的对象才会加锁。

3、优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁

4、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，nextkey lock 退化为间隙锁

5、一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

锁的思想

悲观锁

悲观锁：当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改， 最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前先锁定， 再修改的方式被称之为悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）

实现：X锁、S锁

乐观锁

乐观锁（ Optimistic Locking ）: 是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突， 所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息， 让用户决定如何去做。相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。 一般的实现乐观锁的方式就是记录数据版本

实现：MVCC

死锁

产生条件

互斥条件

请求和保持条件

不剥夺条件

循环等待条件

如何防范

设置获得锁的超时时间

按同一顺序访问对象

避免事务中的用户交互

保持事务简短并在一个批处理中

使用低隔离级别

mysql 日志

redo log 重做日志

redo log又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值

保存：在页修改的时候，先写到 redo log buffer 里面，然后写到 redo log 的文件系统缓存里面(fwrite)， 然后再同步到磁盘文件（fsync）

undo log 回滚日志

Undo回滚日志记录某数据被修改前的值

bin log

mysql-binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（(除了数据查询语句）信息

redo log 和 bin log 差别

一致性锁定读/一致性非锁定读

一致性锁定读

一致性锁定读指的是一个事务可以通过SELECT语句给某条记录加X锁或者X锁

一致性非锁定读

要读取的行被加了X锁（排它锁），这时候读取操作不会等待行上锁的释放， 而是会读取行的一个快照数据

快照读/当前读

快照读 snapshot read

读取历史数据的方式

就是slect。例如 select ... from .... where ...

当前读 current read

读取数据库当前版本数据的方式

select * from table where ? lock in share mode;

select * from table where ? for update;

insert

update

delete

索引

索引结构分类

Hash

优点：

1、查询效率高

缺点：

1、只能用于等值查询，不能用于范围查询

2、hash碰撞问题，可能也会导致查询效率低（数据库解决方法为拉链法：可以参照HashMap的解决方式）

B+树

优点：

1、单次请求涉及的磁盘IO次数少（出度d大，且非叶子节点不包含表数据，树的高度小）

2、查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）；

缺点：

随机IO导致的问题

1、主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；

2、即使主键是有序递增的，大量写请求的分布仍是随机的；

为什么选择B+树，而不是B树或者红黑树之类的...

AVL树、红黑树

不选择的原因：因为都是二叉树，节点数量很多时，树太高，会增加磁盘IO次数

B-树(B树)

3、类似于二分查询，只要找到一个节点data节点符合条件，就可以结束

1、多路二叉树

2、每个节点既保存索引，又保存数据

B+树

1、多路二叉树

2、只有叶子节点保存数据

3、搜索时相当于二分查找

4、增加了相邻接点的指向指针

B-树和 B+ 树区别

1、B+tree的查询效率更加稳定，B+树查询时间复杂度固定是logn，B树查询复杂度最好是 O(1)

2、B+树相邻接点的指针可以大大增加区间访问性，可使用在范围查询等，而B树每个节点 key 和 data 在一起，则无法区间查找

3、B+树的磁盘读写代价更低，B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多

为什么Mongodb用的B-树，而Mysql用的B+树？

考虑Nosql数据库和关系型数据库应用场景

RTree

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找

FULLTEXT

它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题

最初只有MySIAM支持全文索引。mysql 5.6开始，innodb 也开始支持全文索引

索引种类

主键索引(唯一、不可以为null)

唯一索引(不可以为null)

普通索引

前缀索引

组合索引

最左前缀原则

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

全文索引

“覆盖索引”

索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据

什么时候索引会失效

1、列类型是字符串，查询条件未加引号

2、使用like查询时，通配符 % 之前

3、隐式类型转换导致索引失败

例如：列时字符串，查询时使用的数字，则Mysql会隐式转换，讲表中数据转换为数字比较

4、隐式编码转换导致的索引失败

例如：原列字符集是utf8，要关联查询的列是utf8mb4，则mysql会调用convent函数进行转换

5、联合索引，where中索引列违背最左匹配原则

6、使用!= 、<> 、 NOT、NOT IN、!< 、!> 等

7、使用or时，or两边的列都需要加上索引，不然也会失效

8、对索引列进行计算  + - * /

9、Mysql 优化器的最终选择，不走索引

优化器查询使用索引查出来的数据是否超过整表的30%，超过则不用索引

性能优化

explain 如何分析

explain可以得到什么信息？

索引使用情况、查询的条数....

那些字段比较重要

type

查询使用了何种类型

system > const > eq_ref > ref > range > index > all

possible_keys

显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引

key

实际使用的索引。如果为NULL，则没有使用索引

可以在sql中使用 force index（列）强制使用索引

也可以在sql使用 ignore index（列）强制不使用索引

key_len

使用的索引的长度，在不影响精度的情况下，长度越短越好

rows

使用索引检索的条数

Extra

关于MySQL如何解析查询的额外信息

分库分表带来的问题

什么是分表分库

数据分片，指按照某个维度将存放在单一数据库中的数据分散地存放至 多个数据库或表中以达到提升性能瓶颈以及可用性的效果

垂直拆分(纵向拆分)

优点

库表职责单一，复杂度降低，易于维护。

单库或单表压力降低。相互之间的影响也会降低。

缺点

部分表关联无法在数据库级别完成，需要在程序中完成

单表大数据量仍然存在性能瓶颈。

单表或单库高热点访问依旧对 DB 压力非常大。

事务处理相对更为复杂，需要分布式事务的介入。

拆分达到一定程度之后，扩展性会遇到限制。

水平拆分(横向拆分)

优点

解决单表单库大数据量和高热点访问性能遇到瓶颈的问题。

应用程序端整体架构改动相对较少。

事务处理相对简单。

只要切分规则能够定义好，基本上较难遇到扩展性限制

缺点

拆分规则相对更复杂，很难抽象出一个能够满足整个数据库的切分规则。

后期数据的维护难度有所增加，人为手工定位数据更困难。

产品逻辑将变复杂。比如按年来进行历史数据归档拆分，这个时候在页面设计上就需要约束用户 必须要先选择年，然后才能进行查询

拆分原则

例如：id 取模(扩展性差)、

好处

单表大数据被拆分,解决了单表大数据访问问题

带来的问题

事务问题

跨数据库查询

全局序列号

分库策略

路由透明

分表分库中间件

client

TDDL、Sharding-JDBC

proxy

MyCAT

迁移的方案

停机迁移

不停机迁移

思路

大致思路为写一个主动迁移的程序、然后代码里判断是否迁移，没有迁移则被动迁移

被动迁移时可订阅binlog

读写分离带来的问题

主从复制的原理

1、主库把数据更改记录到二进制(bin log)日志中

2、从库把主库上的日志复制到自己的中继日志(rely log) 中

3、从库读取中继日志中的事件，将其重现在从库中

主从同步延迟的原因

主从同步延迟解决方案

增加数据缓存层，判断从库数据是否最新等

sql如何优化

count

count(主键id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。 server 层拿到 id 后，判断是不可能为空的，就按行累加

count(1) ：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去， 判断是不可能为空的，按行累加

count(字段)： 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加； 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加

count(*) ：count（*）并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加

效率：count(字段)<count(主键 id)<count(1)≈count(*)

...

MVCC 多版本并发控制

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号

INSERT时，保存当前事务版本号为行的创建版本号

DELETE时，保存当前事务版本号为行的删除版本号

UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行 通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用， 大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行