MySQL v2.0笔记思维导图模板_ProcessOn思维导图、流程图

分支主题

安装

索引结构

数据结构

二叉树

单链表

AVL树

数据量大的时候高度不可控，磁盘IO不可控

维持平衡代价大

红黑树

数据量大的时候高度不可控，磁盘IO不可控

hash表

不支持范围，只有=和in

冲突问题

hash函数要求高

不能利用索引来排序

也没有最左匹配等等查询优化

Btree

B+树

与B树的区别

数据分块存储，一块就是一页

所有的值是按照顺序存储的，每一个叶子到根的距离是一致的

非页节点存储数据的边界。叶子节点存储这下宁数据行的指针（MyISAM）

MySQL实现

表数据文件本身就是按B+Tree组织的一个索引结构文件

存储引擎

针对表而不是库的

MyISAM

结构

frm

myi

索引，存行的指针

myd

数据

非聚集

innodb

磁盘存储结构

frm

ibd

聚集索引

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

有了主键或者唯一键才可以用来在B+树中建立索引

相当于是追加数据，而不是从中间插入数据，减少磁盘的随机IO

为什么非主键索引结构叶子节点存储的是主键值？

一致性和节省存储空间

联合索引的底层存储结构长什么样？

按照从左到右的顺序来排序，只有第一个索引一致的情况下，第二个索引才是有序的

InnoDB支持事务（TRANSACTION）

InnoDB支持行级锁

explain工具

id

越大优先级越高，相等的时候从上往下执行

一个select一个ID，一个select涉及多个表那么ID是一样的

就是一个复杂的SQL里可能会有很多个 SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个唯一的id

select_type

simple

简单查询。不包含子查询和union

primary

复杂查询最外面的select

subquery

select中的子查询

derived

from中的子查询

结果放在一个派生表里面，子查询执行后的结果集会物化为一个内部临时表，外层查询针对这个临时的物化表

union

union中首先执行的查询，也就是后面的查询

在使用union语句的时候，会有第三条执行计划，这个第三条执行计划意思是针对两个查询的结果
依托一个临时表进行去重，这个第三条执行计划的select_type就是union_result。

table

普通查询

正在查询的表

子查询

<deriveN>，当前查询依赖id= N的查询，所以先执行id=N 的查询

union

result列的值是<union 1,2>，1和2表示参与select的行ID

type

MySQL如何查表里面的行数据

NULL

优化器就可以得到结果，不用查索引

system

表里只有一条元素且匹配

const,

只需要匹配表里的一条唯一键的元组，几次磁盘IO就可以定位到了。

eq_ref

使用主键或unique键的连接查询，被驱动表就是这个

ref

使用非唯一索引查询

range

基于二级索引进行范围查询

index

二级索引全索引扫描不回表（覆盖索引）

ALL

聚簇索引全索引扫描

针对单表查询可能会基于多个索引提取数据后进行合并，此时查询方式会是index_merge这种。

possible_keys列

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

字符串

n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

char(n)

存汉字长度就是 3n 字节

varchar(n)

存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节　

时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

ref

查询方式是索引等值匹配的时候，比如const、ref、 eq_ref、ref_or_null这些方式的时候，此时执行计划的ref字段告诉你的就是：你跟索引列等值匹配的是什么？是等值匹配一个常量值？还是等值匹配另外一个字段的值？

rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

ﬁltered

就是经过搜索条件过滤之后的剩余数据的百分比。

Extra

Using index

使用覆盖索引（查找方式），不回表，索引树都能找到就是覆盖索引

Using index condition

查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

也可能是覆盖了但是除了前导列的范围加了其他的范围

Using where

使用 where 语句来处理结果，并且查询的列未被索引覆盖，或者使用了索引但是还有其他where条件

Using filesort

将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

Using temporary

没有索引，需要创建一个临时表去重

用group by、union、distinct之类的语法的时候，万一你要是没法直接利用索引来进行分组聚合，
他会直接基于临时表来完成，也会有大量的磁盘操作

有索引的话，一边扫描一边去重，就不会创建临时表，就是Using index

Select tables optimized away

使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

索引及SQL优化

索引结构B+树

主键索引（聚簇索引）

主键目录

主键目录也放在数据页里

基于主键的索引查找过程

二级索引

为什么索引页也要维护主键

联合索引

插入数据时候的索引维护

聚簇索引维护

二级索引维护

索引缺点

耗费空间

维护耗费时间

联合索引查询原理

为什么不使用单值索引

案例

索引设计原则

根据使用的SQL设计索引，代码先行

联合索引+最左：针对你的SQL语句里的where条件、order by条件以及 group by条件去设计联合索引

基数：不要在小基数字段上建立索引，还不如全表扫描了，

前缀索引：字段类型比较小的设计索引，或者取前缀做索引

where与order by冲突时优先where

读多写多，尽量少一点，需要维护索引，频繁增删改的字段不要建立索引

主键自增

两三个联合索引覆盖百分之八十，加两三个特殊索引覆盖剩下的，复杂联表交给java。计算不能让MySQL来。再复杂的SQL交给大数据来处理。报表交给java比SQL高效，报表也难以优化，一般是大数据计算好落地MySQL，不用计算功能

SQL执行计划

MySQL内部基于
成本计算实现执行计划优化

trace工具

possible keys

计算全表扫描的成本

索引的成本计算方法

总结

MySQL内部基于
规则的执行计划优化

删除括号

常量替换

没有意义的直接删

子查询

物化表

半连接semi join

更新语句

索引数量限制

锁等待和死锁

MySQL连接池以及redo log文件

查询语句

count(*)查询优化

MyISAM

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算

InnoBD

MVCC机制不适合维护，不同事务的值不一样

可以有一个大致的维护好的值 show table status 结果中 rows

对于大数据总数维护到Redis原子计数器 incr 和 decr，有数据库和Redis的一致性问题

CUD的时候，增加数据库计数表，让他们在同一个事务里面

EXPLAIN select count(1) from employees;

EXPLAIN select count(id) from employees;

EXPLAIN select count(name) from employees;

EXPLAIN select count(*) from employees;

in 和 exists关联查询优化

小表驱动大表

被驱动表：大表。驱动表：小表

当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)

当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

join关联查询优化

小表驱动大表

被驱动表：大表。驱动表：小表

嵌套循环连接（有索引）
Nested-Loop Join 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

驱动表200行全表扫描，然后被驱动表使用索引查询

当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

一般 join 语句中，如果执行计划 Extra 中没有出现 Using join buffer 则表示使用的 join 算法是 NLJ。

基于块的嵌套循环（没有索引）
Block Nested-Loop Join 算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

join_buffer 是无序的

适合没有索引，没有索引意味着全盘扫描，使用块嵌套就是内存扫描很快而不是磁盘

分段放

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下驱动表的所有数据话，策略很简单，就是分段放。

比对结束拿出来放新的再比对

对于关联sql的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间

straight_join只适用于inner join，并不适用于left join，right join。因为left join，right join已经代表指
定了表的执行顺序

分页查询优化

先根据排序字段从索引里面查出来需要的ID，之后和聚簇索引join查询使用eq_ref查询

根据自增且连续的主键排序的分页查询

可以直接使用有主键索引+where实现索引查询，而不是ALL全表扫描

如果不是连续的话使用where查询可能会出现结果不是自己想要的

根据非主键字段排序的分页查询

先使用主键和排序字段筛选出特定的区间，之后再使用这个结果回表取出所有的记录

基于慢sql查询做优化

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

like查询索引下推

（Index Condition Pushdown，ICP）

like KK%相当于=常量，%KK和%KK% 相当于范围

like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

is null,is not null 一般情况下也无法使用索引

不在索引做任何操作

覆盖索引优化

回表的危害

在where里面使用联合索引

等值匹配

最左侧列匹配

最左前缀匹配原则（like）

范围查找规则

等值匹配+范围匹配规则

排序使用索引

可以使用

order by语句使用索引最左前列。

使用where子句与order by子句条件列组合满足索引最左前列。

默认全部升序可以使用

全部指定降序也可以，Mysql8以上版本有降序索引

不可以使用

有的升序有的降序不可以使用索引

order by的字段使用了复杂函数不可以使用索引

如果order by的条件有不在索引列上的，就会产生Using filesort。

MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

Using filesort文件排序

单路排序

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

双路排序

需要回表，参与排序只有ID和排序字段

首先根据相应的条件取出相应的排序字段和可以直接定位行
数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

sort buffer

优化SQL让使用的buffer尽可能少

大小可以改，内存够的话可以大一点就不需要回表了

对比

单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

分组使用索引

借助索引的最左侧字段已经分好组

group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
的限定条件就不要去having限定了。

一条SQL太慢怎么办

最左匹配

建立索引

schema优化

底层查询优化

explain查询计划成本计算

自动调换位置

条件下推

分区优化

索引使用

聚簇索引

非聚簇索引

主键索引

覆盖索引

最左前缀匹配原则

索引下推

一条SQL执行流程

MySQL驱动

mysql-connector-java就是面向Java语言的 MySQL驱动

MySQL驱动，他会在底层跟数据库建立网络连接，有网络连接，接着才能去发送请求给数据库服务器

数据库连接池应对多个连接并发，避免连接创建之后就销毁

连接器

服务端也是需要连接池的

session 建立，还会将mysql.user表的用户的权限放在session内存空间，每次请求到来都要看客户端权限

默认8小时长连接，连接完成后，如果你没有后续的动作，这个连接就处于空闲状态

可以不断开连接清空缓存的内容

注意重建连接，权限的修改才会生效，否则一直使用session里面的内存信息

管理连接和权限校验

缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

在静态表里使用查询缓存

mysql8.0已经移除了查询缓存功能

分析器

“词法分析”

“语法分析”

语法树

构造执行树

优化器

执行计划生成，选择合适索引

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

调用执行引擎接口，返回查询结果

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎

buffer pool 首先会判断hash表的数据页有没有加载进来，有热点数据的缓存，以及LRU的淘汰，

子主题

日志

binlog（server）

binlog 归档记录CUD操作

server层记录SQL语句的执行原始逻辑

binlog‐format=statement

sql本身

资源消耗小

可能主从不一致，可能在不同库使用的索引优化之后不一致

binlog‐format=ROW（推荐）

记录执行后的结果

数据量大，安全性好

canal默认就是使用这个

binlog‐format=mixed

上面两个

刷盘策略--sync_binlog参数

redo重做日志（存储引擎）

innodb_flush_log_at_trx_commit

通常设置为1
可以严格的保证提交事务之后，数据是绝对不会丢失的

两阶段提交

在redo里面写入更新的物理记录之后，变为prepare阶段

在binlog里面记录当前执行的SQL语句

本次更新对应的binlog文件名称和这次更新的binlog日志在文件里的位置，
都写入到redo log日志文件里去。在redo log日志文件里写入一个commit标记。

恢复机制

一句 log 的结构

redo log block

12字节的header头又分为了4个部分

子主题

redo log buffer

innodb_log_buffer_size

流程

redo log group 事务

buffer中的block写入磁盘的时机

文件个数设置

undo回滚日志（存储引擎）

日志版本链

开启事务之后只有执行更新操作行记录才会有新的事务ID

readview 机制

视图 = 所有未提交的事务组成的事务ID数组 + 以及一个已提交的最大的事务ID

视图不是开启事务的时候生成的，而是第一次执行select时候生成的

基于readview生成一个区间，有三部分

版本链比对规则

读已提交

可重复读

INSERT语句的undo log

子主题

意义

缓存页一个就是16kb，数据比较大，缓存页刷入磁盘是随机写磁盘，性能是很差的

如果是写redo log，第一个一行redo log可能就占据几十个字节，就包含表空间好、数据页号、磁盘文件偏移量、更新值，这个写入磁盘速度很快。

数据类型选择

数值类型

确定合适的大类型：数字、字符串、时间、二进制；

确定具体的类型：有无符号、取值范围、变长定长等。

尽量把字段定义为NOT NULL，避免使用NULL。

日期和时间

DATE

datetime

timeStamp

2038年

字符串

CHAR

VARCHAR

BLOB

TEXT

多事务并发问题

innodb事务（并发）

属性

A原子性

操作：全执行全不执行

使用undolog实现：灾备恢复的时候，redo保证持久性，但是事务可能在宕机的时候还没有执行结束，就需要undo实现事务的回滚，将redo恢复的数据但是事务没有执行完的回滚了

C一致性

数据：完整性

事务实现

I隔离性

不受其他事务影响

使用隔离级别实现MVCC

D持久性

修改后的数据不能丢失

使用redolog实现

事务并发问题

脏写

脏读

不可重复读

幻读

注意第一个是写的问题，后三个是读的问题，读的问题是使用MVCC解决，写的问题是使用锁来实现的

SQL标准的事务隔离级别

读未提交

读已提交

可重复读

序列化

加锁

增加行级锁之前，InnoDB会自动给表加意向锁

执行DML语句，会自动给记录加独占锁

执行DQL语句

共享锁S

select from where lock in share mode

排它锁X

select from where for update

间隙锁NK

sql采用范围条件时，InnoDB对不存在的记录自动加间隙锁，二级索引即使是等值update也会加间隙锁

临键锁

where条件边界正好是存在的数据，就会从间隙变为临键锁

意向锁，排它锁，共享锁

1.提高了是否可以加表锁的判断效率。有没有行锁一下就知道了不需要遍历

2.实现了行锁和表锁的多粒度锁机制，使得表锁和行锁可以共存。

RR隔离级别聚簇索引，等值查询是记录锁；非聚簇索引，等值查询且存在是间隙锁非聚簇索引，左闭区间范围查询，边界命中，边界是记录锁，覆盖的范围是临键锁非聚簇索引，区间范围查询无数据命中，边界后面的下一个数据是临键锁

MYSQL的事务隔离级别

读未提交

读已提交

MVCC实现

每次select都会生成最新的read view

可重复读（默认）

可以解决幻读

只是在select的时候不会查出来，但是在update的时候会使用最新的视图，就会幻读，指的是其他的事务已经提交了CIA会幻读，其他事务没有提交此时是有临键锁或者间隙锁的是不会幻读的

MVCC实现（读）

读取是事务开启时的数据

更新是实时的数据

第一次select会生成read view

间隙锁GAP（写或者范围条件查询的时候）
范围内不存在的记录加锁
防止幻读，满足恢复和复制的需要

间隙锁在某些情况下可以解决幻读问题。

间隙指的是当前已经有的行记录之间的间隙，锁定要更新区间所在的间隙

间隙锁是在可重复读隔离级别下才会生效。

临键锁Next Key（写）

行锁+间隙锁

上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

可序列化

锁实现

读写都会加锁

MVCC多版本并发控制
只是针对读

undo版本链

read-view机制

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

实现RC

实现RR

解决重复读：只在事务的第一次查询生成readview

解决幻读：根据事务ID的条件范围查询的时候，每次读到的数据都是一样的，不会读到人家插入进去的数据

对一行数据的读写是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

锁
针对写和序列化读

多个事务更新同一行数据时，是如何加锁避免脏写的？

操作

读锁（悲观）（共享锁）

当前session和其他session都可以读该表。当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

写锁（悲观）（排它锁，独占锁）

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

当有人在更新数据的时候，其他的事务可以读取这行数据吗（基于MVCC）

当有人在更新数据的时候，其他的事务可以读取这行数据吗（基于共享锁）

查询操作还能加互斥锁，他的方法是：select * from table for update。

乐观悲观锁

悲观锁（数据库都是）

乐观锁（自定义）

版本号机制

update ... set , version=#{version+1} where .. AND version = ${version}

CAS算法

无锁的方式实现有锁的感觉

涉及内存值，旧值，新值，采用自旋操作，

问题

ABA

误以为没有更新过

循环时间太长开销会变大

只能一个共享变量的原子操作

总结

默认：对同一行数据的更新操作加的行级独占锁是互斥，跟读操作都是不互斥的，读操作默认都是走mvcc机制读快照版本的！

独占锁和独占锁是互斥的，此时别人不能更新；
但是此时你要查询，默认是不加锁的，走mvcc机制读快照版本，
但是你查询是可以手动加共享锁的，共享锁和独占锁是互斥的，但是共享锁和共享锁是不互斥的

不是太建议在数据库粒度去通过行锁实现复杂的业务锁机制，而更加建议通过redis、zookeeper来用分布式锁实现复杂业务下的锁机制，分布式系统里的复杂业务的一些锁机制依托数据库查询的时候，在SQL语句里加共享锁或者独占锁，会导致这个加锁逻辑隐藏在SQL语句里，在你的Java业务系统层面其实是非常的不好维护的

粒度

行锁

开销大（查找），加锁慢（）

粒度小，冲突少，并发度高

其他事务会阻塞

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，
例如：select * from test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。

当系统并发量高的时候，Innodb 的整体性能和MYISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

表锁

整表迁移场景

开销小，加锁快

unlock tables;

DDL语句和增删改操作，确实是互斥的，但是不是表级锁，是MySQL通用的元数据锁，这里的表锁是存储引擎自己的锁

表级的意向锁(常见)

表上面手动加的独占锁和共享锁，以及更新数据和查询数据默认自动加的意向独占锁和意向共享锁，他们互相之间的互斥关系

你如果手动加了表级的共享锁或者独占锁，此时是会阻塞掉其他事务的一些正常的读写操作的，因为跟他们自动加的意向锁都是互斥的。

性能

乐观

版本对比来实现

悲观

优化

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，因为有MVCC不会加锁。但是update、insert、delete操作会加行锁。

死锁

场景

两个事务，A先修改1再修改2 ，B先修改2再修改1

解决

InnoDB会自动检测死锁使得一个回滚，另一个继续

设置超时等待参数 innodb_lock_wait_timeout;

避免

不同业务并发访问多个表的时候，应该约定以相同的顺序访问这些表

以批量的方式处理数据，事先对数据排序，保证线程按固定的顺序处理数据

在事务中，如果要更新记录，应直接申请足够级别的锁，也就是排它锁

总结

间隙锁锁定间隔，防止间隔中被其他事务插入；

临键锁锁定索引记录+间隔，防止幻读；

面试题

1.InnoDB存储引擎什么时候会锁住整张表（什么时候使用行级锁），什么时候或只锁住一行呢（使用行锁）？

只有通过索引条件查询数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！记住：一定要记住为匹配条件字段加索引。

2.什么时候使用行级锁？什么时候使用表级锁？

（1）在增删改查时匹配的条件字段不带有索引时，innodb使用的是表级锁，

3.行级锁锁的是什么？行级锁怎么实现加锁？

（1）行级锁是针对索引加的锁；

（2） InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。

4.mysql读锁和写锁？

（1）因为只有触发了读写锁，我们才会谈是进行行级锁定还是进行表级锁定；

（2）用select 命令时触发读锁，当使用update,delete,insert时触发写锁，并且使用rollback或commit后解除本次锁定。

5.常见的锁算法：

next KeyLocks锁，同时锁住记录(数据)，并且锁住记录前面的Gap

Gap锁，不锁记录，仅仅记录前面的Gap

Recordlock锁（锁数据，不锁Gap）

所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

6.什么时候会释放锁？

提交事务或回滚事务就会释放锁。

为什么不直接更新磁盘上的数据

因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，
同时还能保证各种异常情况下的数据一致性。
更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据，虽然技术上是可以做到的，但是那必然导致执行请求的性能极差。

缓冲池Buffer Pool--加载
数据页为缓存页
（innoDB存储引擎）

更新数据的流程

SQL语句会对应修改buffer pool的缓存数据，写undo 日志，写redo log buffer ，写binlog

事务提交的时候回将redo 写入磁盘，binlog写入磁盘，之后设置redo中的事务标记为commit

最后后台的IO线程会随机将buffer pool里的脏数据写到磁盘里面

意义：增删改主要是针对内存缓存的磁盘数据，因为磁盘太慢了

配置大小

系统启动就会申请一块内存区域

缓存页

缓存页的描述信息

空间分配

free 链

缓存页hash表

flush链

LRU链

MySQL预读机制

缺点

为什么使用

全表扫描

解决：冷热数据

冷数据区域的数据是什么

何时到热数据区

淘汰策略

刷盘时机

Redis的缓存预热可以联想这里

热数据的移动策略

结合CRUD场景描述缓存以及几个链表的运作

多个buffer pool

加锁访问

加锁之后的性能

设置多个Buffer Pool来优化他的并发

基于chunk机制动态调整buffer pool大小

直接全部复制不可取

什么是chunk

行记录

设置方法

拆解

引入变长字段的长度列表，解决一行数据的读取问题

二进制bit位来存储多个NULL字段值

40个bit位的数据头

实际数据

数据读取方法

行溢出

数据页

双向链表

页目录

槽

页分裂

自增主键

非自增主键

维护上层索引条目

索引页

表空间以及划分多个数据页的数据区

表空间

数据区extent

组

性能抖动

buffer pool满了

redo log 文件写满了

解决

电池放电

主从复制架构

流程

读写分离

半同步复制

从库返回成功再提交事务（5.7默认）

发出从库同步的时候就提交事务，从库返回之后直接返回客户端

主从延迟

要求不高：异步+从库多线程复制数据

要求高：半同步+从库多线程复制数据

高可用

MHA

分库分表

数据大，索引大，高度增加，查询变慢，而且缓存可以放的数据页比较小

单表一百万，数据库建好索引，用上索引

用户系统

订单系统

跨库/表分页

分库分表扩容

数据库领域建模

数据库设计

数据库

数据库范式

设计数据表

数据索引的缺点

主键索引和唯一索引的区别？

如何验证mysql的索引是否满足需求

为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql独独喜欢B+树？

数据库事务特性？

事务级别

读未提交

读已提交

可重复读

可序列化

InnoDb的事务级别是可重复读，会有幻读的情况，如果解决？

InnoDB 锁类型有哪些？

记录锁(Record-Lock)

间隙锁

next-key 锁

优化经验

limit取靠后的数据，耗时的问题。

数据库线上问题排查

死锁

查询事务隔离级别 select @@tx_isolation

查询死锁日志 show engine innodb status

根据死锁日志的SQL语句分析代码

锁时序分析

表锁

页锁

行锁

记录锁

间隙锁

临键锁

意向锁

慢SQL

利用explain执行计划优化SQL

利用索引提速

小表驱动大表

利用show processlist，然后kill 慢SQL

连接过多

set global max_connections 增大连接数

show processlist，然后kill慢查询

关系型数据库和非关系数据库的特点