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

逻辑架构

Server 层

连接器

数据库连接

长连接

指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接

指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

长连接存在的问题及解决方案

查询缓存

分析器

优化器

执行器

存储引擎层

基本操作

select

1.连接器收到客户端请求建立连接，并检查是否有执行权限

2.分析器对语法分析

3.优化器根据语法树进行优化

4.执行器根据优化器结果，调用引擎接口，获取第一条引擎认为符合条件的，执行器判断，继续取下一行

update

查询步骤同上，执行器拿到符合条件的行后，进行更新，调用引擎写接口

order

如果要求对当前使用的索引列进行排序，从引擎拿到的就是有序的

可以直接返回给客户端（一种优化手段：合理建立索引）

如果引擎返回到执行器的数据无序

数据量小的情况下，直接用sort buffer进行排序即可

数据量大的情况下，需要用到临时文件，进行归并排序

如果有limit，并且数量比较小，mysql会采用堆排序，取前几个直接返回

可以适当调大sort buffer，优化排序性能

group

如果从引擎拿到的有序，遍历一遍结果即可得到结果

所以可建立适当索引进行优化

如果从引擎拿到的无序，需要用到临时表（小则内存表，大则磁盘表），将group by的列当作唯一键，
并对结果进行排序返回，如果不需要排序，可以order by null 免于排序直接返回

count

由于MVCC机制，同时存在的每个session可能返回不同的结果，所以不能像Myisam那样直接记录整表数量直接返回

count(*)

innodb对此进行了优化，不取值，肯定不为null，按行累加

count(1)

不取对应的数据行，在每一行返回1，肯定不为null，按行累加，性能比count(*)稍微差点

count(主键)

需要取出主键列，肯定不为null，按行累加

count(字段)

取出字段列

如果不允许为null，直接按行累加

如果允许为null，需要先判断是否为null，对非null行累加

性能

count(*) > count(1) > count(主键) > count(字段)

join

join字段有索引

Index Nested-Loop Join(NLJ)

从驱动表取出一行数据，直接利用索引树到被驱动表查出数据，进行连接

Batched Key Access(BKA)

依赖于Multi Range Read(MRR)，回表的时候先做排序，通过将随机读，转换为尽量顺序读

对NLJ方案进行的优化，默认开启，从驱动表取出一批数据，放到join buffer，到被驱动表进行MRR连接

join字段无索引

Block Nested-Loop Join(BNL)

从驱动表取出符合条件的行，放到join buffer，取出join列，到被驱动表进行全表匹配，如果buffer放不下，需要分多次进行（Block名字由来，分块）

优化

构建适当索引，尽量走BKA方案

如果为冷查询，并且可以过滤掉很多数据，建索引有点浪费，可以考虑使用临时表，将数据插入到临时表中，建立索引进行连接

原则

尽量选取小表作驱动表

可以适当调节join buffer大小优化速度

事务

隔离级别

读未提交

一个事务还没提交时，它做的变更就能被别的事务看到。

读提交（建议使用）

一个事务提交之后，它做的变更才会被其他事务看到。

可重复读（默认隔离级别）

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化

对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

实现

启动方式

显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

问题：如何避免长事务对业务的影响？

MVCC（多版本并发控制）

在写的同时，允许读操作，提高并发度

read view

当前未提交事务id列表

未提交事务中最小的事务id

下一个未使用的事务id

生成时机

读已提交

每次快照读前生成

可重复读

在开启事务的第一个读写语句生成

实现原理

在每一行有隐藏列，当前行的事务id，上一个版本指针

旧版本存在undo log中，当行事务id小于当前所有read view中的最小事务id时，说明可以删除了

读取每一行的时候，判断当前行的事务id

小于read view最小事务id时，正常返回

大于read view下一个未使用的事务id时，说明事务在生成快照后生成，利用版本指针回滚

大于read view最小事务id，如果在未提交事务列表则回滚，不在则正常返回

视图

普通view

一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。

一致性读视图consistent read view

InnoDB 在实现 MVCC 时用到的一致性读视图，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

数据版本可见性规则

小结

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据

对于读提交，查询只承认在语句启动前就已经提交完成的数据

对于当前读，总是读取已经提交完成的最新版本

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构

数据结构

为什么不用平衡二叉树或者红黑树

树的分支太少，每次内存读取磁盘一页（页是内存的管理单位）是16K数据，但是只有2个子树有数据，读取浪费太多

树的高度太高，读取磁盘次数过多

为什么不用B树

范围查询效率太低

每个节点都包含数据，如果数据比较大，那么一页中存储的节点个数就会变少

哈希表

只适用于等值查询，不适合范围查询

B+树

除叶子节点外的其他节点都不包含数据，因此一页中能够存放更多的节点

只有叶子节点存储数据，所有的查询都要查找到叶子节点，因此查询性能是稳定的

所有的叶子节点形成了一个有序链表，更加便于范围查找

更适合用自增主键，连续性好，如果用UUID这种作为主键，那么维护所以的代价比较高，因为从中间插入会导致树分裂

与MyISAM区别

MyISAM索引也使用B+树，但其叶子节点上存放数据的地址，普通索引同样存放数据指针

InnoDB主键索引叶子节点存放具体的数据，普通索引叶子节点存放对应的主键

InnoDB 的索引模型

每一个索引在 InnoDB 里面对应一棵 B+ 树

索引类型分为主键索引和非主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）

非主键索引的叶子节点存的是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）

问题：基于主键索引和普通索引的查询有什么区别？

问题：自增主键和业务主键的选取？

问题：如何避免回表操作？

索引维护

页分裂

页合并

索引重建

重建主键索引

alter table T engine=InnoDB

重建非主键索引

alter table T drop index k; alter table T add index(k);

唯一索引和普通索引

查询

唯一索引：在做查找操作时，查找到第一个满足条件的记录后，就会停止继续检索。

普通索引：在做查找操作时，查找到满足条件的第一个记录后，还会继续查找下一个记录，直到碰到第一个不满足条件的记录。

两者查询性能差距很小的原因

增删改

唯一索引：直接在内存中操作

普通索引：使用change buffer

若更新的目标页在内存中，两者性能相差不大；若更新的目标页不在内存中，唯一索引性能较差，普通索引性能较好。

change buffer的使用场景

写多读少

问题：change buffer重启之后是否会丢失？

change buffer的merge过程

从磁盘读入数据页到内存（老版本的数据页）

从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页

写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更

分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，但允许有空值

复合索引

即一个索引包含多个列

基本语法

创建

CREATE [UNIQUE ] INDEX indexName ON mytable(col01,col02,col03......);

ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name\G

使用ALTER命令

建立索引规则

每个表不超过5个索引

索引太多，会导致插入更新操作变慢

每个联合索引不超过5个字段

联合索引字段太多，区分度就不是很高了，反而会浪费空间

修改频繁的列不要建立索引

更新数据会更新索引，会导致数据库性能下降

区分度低的列不要建立索引

建立联合索引时

经常用的优先

区分度高的优先

长度短的优先

主键索引最好使用递增整数

整数：由于innodb辅助索引叶子节点存放的是主键，主键长度越小，辅助索引查找越快

递增：由于innodb主键索引上数据按主键递增排序，如果插入顺序不是递增，会导致页分裂，降低插入数据效率

字符串上建立索引

可以使用前缀索引，减小索引使用空间

可以考虑倒序建立索引，比如身份证号

可以考虑使用hash索引，建立单独一列crc32，快速定位

索引优化规则

范围查询右边的列将不能使用索引

查询的时候，对列的那一边使用函数，会导致不走索引
（mysql认为使用函数会改变索引顺序）

隐式转换

比如查询条件为字符串列=数值，a=1，mysql会将字符串
转换为数值进行比较，导致索引列使用了函数

字符集转换

比如a、b两列分别使用utf8和utf8mb4字符集，当查询条件是a=b
的时候，会将utf8强制转换为utf8mb4，导致索引列使用了函数

覆盖索引优化

当索引中包含了查询条件的所有列，将直接返回，不用回表

前缀索引优化

前缀索引可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

优点

占用空间小

缺点

可能会增加额外的记录扫描次数，因为不满足条件的也需要回表操作

使用前缀索引就用不上覆盖索引对查询性能的优化

使用方法

使用倒序存储

使用 hash 字段

两者区别

索引下推优化

explain执行计划

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况

id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询

SIMPLE

简单的SELECT查询，查询中不包含子查询或者UNION

PRIMARY

查询中若包含任何复杂的子查询，最外层查询则被标记为PRIMARY

SUBQUERY

在SELECT或者WHERE列表中包含了子查询

DERIVED

在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里

UNION

若第二个SELECT出现在UNION之后，则被标记为UNION;
若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT

从UNION表获取结果的SELECT

table

显示这一行的数据是关于哪张表的

type

system

表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计

const

表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键常量置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref

唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

ref

非唯一索引，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引

index

index只遍历索引树，这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

all

全表扫描，将遍历全表以找到匹配的行

从上到下性能逐渐下降。一般来说，保证查询达到range级别，最好达到ref

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

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

查询中若使用了覆盖索引，则索引和查询的select字段重叠

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序”

Using temporary

使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by

Using index

表示相应的select操作中使用了覆盖索引，避免访问了表的数据行；
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

Using where

使用了where过滤

using join buffer

使用了连接缓存

impossible where

where子句的值总是false，不能用来获取任何元组

select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

distinct

优化distinct，在找到第一匹配的元组后即停止找同样值的工作

锁

全局锁

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

语法是Flush tables with read lock (FTWRL)

使用场景：全库逻辑备份

问题：备份为什么要加锁呢？

表级锁

表锁

语法是lock tables … read/write

元数据锁MDL（metadata lock)

问题：如何安全地给小表加字段？

行锁

行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁

等待超时

主动死锁检测

问题：如果所有事务都要更新同一行会出现什么情况？

尽量避免长事务

尽量使用同一顺序访问需要加锁的资源

尽量一次申请所有需要加锁资源

如果业务允许，可以降低隔离级别减少死锁

buffer pool

sort buffer

join buffer

redo buffer

undo buffer

change buffer

在不影响数据一致性的前提下，非唯一索引（唯一索引需要读出数据判断是否违反唯一约束）

当要修改的磁盘页不在内存中时，innodb会先将数据页改动记录到change buffer中，并记入redo log中（所以崩溃后可以依靠redo log进行恢复）

当下次查询的时候，再将磁盘中的数据读入内存，并应用change buffer的修改

通过减少随机读，提高增删改的效率（正常改的时候需要先将数据页读入内存，修改后再写入磁盘）

刷脏页

1. buffer pool满了之后，有新的数据页需要读入内存，会触发刷脏页

2. redo log写指针追到了头指针，会触发刷脏页

3. 在系统空闲的时候，定时任务刷脏页

4. 在正常关闭数据库的时候，会将脏页刷到磁盘

后两种情况不影响查询效率，尽量减少前两种刷脏页情况

适当调大redo log大小

适当调节磁盘io能力告诉mysql，innodb_io_capacity

淘汰策略

改进版LRU

将buffer pool分为3/8和5/8，分别为old和new

新加入内存的数据页先进入old，超过1s再访问，再进入new，old和new也都使用LRU进行淘汰

日志

redo log（重做日志）

WAL预写日志，先写日志，再写磁盘

将随机写优化为顺序写，提高修改效率

redo log存放的是数据页的改动

redo log为环状的，保证了最大使用空间

建议使用4个1G文件作为redo log

使用两阶段提交保证redo log和binlog的一致性

在客户端执行commit命令的时候，会先写redo log为prepare状态，再写binlog，最后将redo log置为commit状态

崩溃恢复的时候，redo log为prepare状态，只要binlog存在就将其置为commit状态，然后将redo log中的改动应用到内存

binlog中会有xid，用来连接binlog和redo log对用的内容

innodb_flush_at_trx_commit参数控制刷磁盘

0 表示每次只刷到os_cache中就不管了

1 表示每次提交事务都需要flush os_cache

2 表示每次提交事务刷到os_cache上，每个1s触发一次flush

0基本不使用，2有可能会丢失1s的事务，1可以保证数据的完整性，保证提交后的事务不会丢失

使用组提交优化flush次数，将redo log的flush放到写完bin log之后

binlog（归档日志）

格式

statement

直接记录执行的语句，有可能造成主从的数据不一致，比如delete limit 1，主从如果选择了不同的索引，将删除不同的数据

row

记录中有执行前的值和执行后的值，对于数据恢复提供了极大的便利，但是会急剧增大binlog大小，比如delete 10w行数据，就需要存10w语句

mixed

前两种模式的混合，对于mysql认为不会导致数据不一致的语句，使用statement，否则使用row格式

每个线程都有一个binlog buffer，因为事务是不能被打断的

使用sync_binlog参数控制flush

0表示每次只写到os_cache中就返回

1表示每次都执行flush

2-N表示集齐N个事务一起flush

使用组提交优化flush次数，将binlog的flush放到flush redo log之后

undo log（回滚日志）

用来存放数据的历史版本，用于MVCC回退版本时使用

redo log和binlog区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

问题：执行update T set c=c+1 where ID=2; 这条语句的流程？

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

高可用

结构

主从

双主

冷备

延迟备份

防止误操作，可以设置为延迟1小时，这样可以保证1小时内发现的误操作及时修正

判活机制

select 1

缺点：当增删改查堵住的时候，select 1也会正常返回

select 表

缺点：当磁盘空间满的时候，select表可以正常返回

update 表

缺点：响应慢的情况，不容易检测到

开启innodb performance

缺点：开启会损失一部分性能

可以用update表+部分innodb performance

读写分离遇到问题

主从延迟导致刚写入的直接查从库查不到

直接查主库

sleep

先判断主从延迟，如果有延迟走主库