MySQL知识体系思维导图模板_ProcessOn思维导图、流程图

MySQL架构

MySQL内部架构

连接器

长链接

短链接

它负责和客户端建立连接，获得权限，维持和管理连接。我们查询时，第一步都是链接， 这个时候就是连接器在起作用。链接的时候会经过TCP握手，然后身份验证，然后我们输入用户名密码就好了 验证ok后，我们就连上了这个MySQL服务了，但是这个时候我们处于空闲状态

查询缓存

key(查询语句)：value(查询结果)

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。 因为：查询失效非常频繁

分析器

在缓存没有命中的情况下，就开始执行语句了。 会先做词法分析，你的语句有这么多单词、空格，MySQL就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等。 然后就开始语法分析，根据词法分析的结果，语法分析会判断你sql的对错，错了会提醒你的，并且会提示你哪里错了

优化器

索引选择

它负责查询的优化。优化就比较简单了，因为我们建立表可能会建立很多索引，优化有一步就是要确认使用哪个索引， 比如使用你的主键索引，联合索引还是什么索引更好。还有就是对执行顺序进行优化，条件那么多，先查哪个表， 还是先关联，会出现很多方案，最后由优化器决定选用哪种方案

执行器

执行权限判读

它负责执行语句，执行器会调用执行引擎去执行。执行的时候，就一行一行的去判断是否满足条件， 有索引的执行起来可能就好点，一行行的判断就像是接口都提前在引擎定义好了，所以他比较快

存储引擎

Inondb

InnoDB架构

Redolog+binlog执行过程

关键特性

插入缓冲

插入的时候会先到缓冲池查找，如果有，则加入；如果不在，先放入insert buffer对象中，然后以一定频率进行insert buffer和辅助索引页叶子结点的合并操作

条件

非聚集索引

非唯一索引

非聚集索引的插入与更新，

why

非聚集索引的插入无序，需要离散访问，插入性能变低

双写

目的

解决部分写失败问题

原理

1.缓冲池刷脏页的时候，先写入到内存的double write buffer

2. 在写入到磁盘的共享表空间（2M），每次写入1Mb

3. 同步到本地磁盘

相关名词

double write

共享表空间中连续的128个页，即两个区，大小同样为2M（物理磁盘）

double write buffer内存

脏页

当内存数据页与磁盘数据文件不一致，这个内存也叫做脏页

自适应哈希

目的

innodb不支持哈希索引

某些情况下，哈希查找效率很高

原理

innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引

特性

不能人工干预，系统自动创建

优化二级索引查找速度

刷新临近页

刷新一个脏页的时候，innodb会检查该页所在区的所有页，如果判断为脏页，就一起进行刷新

Myisam

表锁

不支持外键

不支持事务

不支持mvcc

支持全文索引；innodb不支持但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

是非聚集索引，innodb是聚集索引

innodb支持crash-safe

主从复制架构

MySQL底层执行原理

执行流程图

数据写入过程

MySQL存储结构

基本单元

行

行数据图解

变长字段长度列表

字符集会影响到CHAR类型是否加入到该头字段中

NULL值列表

记录头信息

图解

delete_mask

记录的真实数据

隐藏列

完整的示例图解

行格式

compact行格式

Redundant行格式

图解

page

在数据库中，不论读一行，还是读多 行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。

innodb中页的大小默认是16kb

类型

数据页

系统页

Undo页

事务数据页

页结构

页中的数据记录是已单向链表的形式

多页采用双向链表组织

区

在innoDB一个区会分配64个连续的页

一个区的大小是1mb

段

表段

索引段

一个段包含64个连续页

表空间

表空间（Tablespace）是一个逻辑容器

表空间存储的对象是段

分类

系统表空间

用户表空间

撤销表空间

临时表空间

show variables like 'innodb_file_per_table';

on

多张表公用一个ibd文件

off

一个表一个ibd文件

B+树

结构

每个新建B+树的节点会申请一个页

同层节点通过页的结构形成一个双向链表

数据存取原理

数据存取流程

innodb缓冲池

结构

数据页加载的形式

内存读取

随机读取

如果数据不再内存，就需要在磁盘上对该页进行查找

整体时间10ms

顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的， 顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页 面单独进行磁盘 I/O 操作了。

MySQL功能分类

基本操作

DDL：数据定义语言

修改表结构

添加字段

ALTER TABLE player ADD (age int(11));

修改字段名，将age字段改成player_age

ALTER TABLE player RENAME COLUMN age to player_age

修改字段的数据类型

ALTER TABLE player MODIFY (player_age float(3,1));

删除字段

ALTER TABLE student DROP COLUMN player_age;

DML

DCL

DQL

事务

特性:ACID

原子性（Atomicity）

一致性（Consistency）

隔离性（Isolation）

持久性（Durability）

控制语句

START TRANSACTION或者 BEGIN

作用是显式开启一个事务

COMMIT

提交事务。当提交事务后，对数据库的修改是永久性的。

ROLLBACK

意为回滚事务。

SAVEPOINT

在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。

RELEASE SAVEPOINT

删除某个保存点。

SET TRANSACTION

设置事务的隔离级别

自动提交

mysql> set autocommit =0; //关闭⾃动提交

并发处理

三种异常

脏读（Dirty Read）

读到了其他事务还没有提交的数据

不可重复读（Nnrepeatable Read）

对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。 这是因为有其他事务对这个数据同时进行了修改或删除

幻读（Phantom Read）

事务A根据条件查询得到了N条数据，但此时事务B更改或者增加了M条符合事务A查询条件的数据， 这样当事务A再次进行查询的时候发现会有N+M条数据，产生了幻读

隔离级别

深入浅出索引

什么是索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样

InnoDB 的索引模型

B+ 树索引模型

InnoDB 的数据是按数据页为单位来读写的

InnoDB 中，每个数据页的大小默认是 16KB

索引类型

逻辑功能划分

主键索引

普通索引

唯一索引

物理实现方式

聚簇索引

非聚簇索引：二级索引

按照字段个数划分

单一索引

联合索引

MySQL索引结构

Hash索引

B+树

自适应hash

innodb中会将满足一定条件的热点数据页的地址放到hash表中， 并且这些hash放到缓冲池，提升热点数据的查询效率

原理

数据结构

Btree索引

以B/B+树的数据结构存储，应用范围广

MySQL 使用 B+ 树是因为数据的遍历在关系型数据库中非常常见， 它经常需要处理各个表之间的关系并通过范围查询一些数据； 但是 MongoDB 作为面向文档的数据库，与数据之间的关系相比， 它更看重以文档为中心的组织方式，所以选择了查询单个文档性能较好的 B 树， 这个选择对遍历数据的查询也可以保证可以接受的时延

mongodb用的是B树，因为它追求的是读写单个记录的性能，而不是范围查找

LSM树存储引擎的代表数据库就是HBase

LSM树和B+树相比，LSM树牺牲了部分读性能，用来大幅提高写性能

LSM树是一种专门用来优化写入的数据结构，它将随机写变成了顺序写显著地提高了写入性能， 但是却牺牲了读的效率，这与大多数场景需要的特点是不匹配的， 所以 MongoDB 最终还是选择读取性能更好的 B 树作为默认的数据结构；

Hash索引

mysql只有MEMORY存储引擎显示支持哈希索引。

通过Hash算法计算索引位置，效率高，一次定位。

哈希表结构在等值查询场景比较快

但需要解决Hash冲突。不支持范围检索

innodb为什么选择B+树

哈希表，不支持范围查询

有序数组

有序数组索引只适用于静态存储引擎

更新插入成本太高

红黑树/二叉搜索树

树太高，增加io次数

子主题

b树

每个节点都存储数据

比b+树高，会增加io次数

B+的叶子节点中有双向链表关联，范围查找更快

b+树

只有叶子节点存储数据

每个叶子节点之间通过指针关联

提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率

聚集索引叶子节点存储整行数据

非聚集索引叶子节点存储主键值

节点的大小为1页（16k）倍数最好

Mysql的基本存储结构是页

各个数据页可以组成一个双向链表

而每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

跳表、LSM 树（todo）

如何选择索引

什么时候不需要创建索引

当数据量非常小，比如不到1000行

数据重复度，高于10%

频繁更新字段不要创建索引

创建索引有那些规律

字段的数值有唯一性的限制，比如用户名

频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下

 需要经常 GROUP BY 和 ORDER BY 的列

UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

更新索引字段的值比更新普通字段的值的效率要高，因为更新索引字段还需要维护索引树

DISTINCT 字段需要创建索引

做多表 JOIN 连接操作时，创建索引需要注意以下的原则

链接表尽量不要超过3张

对where条件创建索引

什么情况下索引会失效

如果索引进行了表达式计算，则会失效

如果对索引使用函数，也会造成失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引， 而在 OR 后的条件列没有进行 索引，那么索引会失效。

当我们使用 LIKE 进行模糊查询的时候，前面不能是 %

索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。 索引不会储层空值，所以最好将索引设置为not null

我们在使用联合索引的时候要注意最左原则

三星索引

在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列

将 GROUP BY 和 ORDER BY 中的列加入到索引中

将 SELECT 字段中剩余的列加入到索引片中

为什么很难存在理想的索引设计

缺点

三星索引会让索引变宽

一张表的索引个数不宜过多，否则一条记录的增加和修改，会因为过多的索引造成额外 的负担

索引使用原则

覆盖索引

当查询语句中的字段就是索引中的某个字段时，可以达到索引覆盖的效果

索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

联合索引：最左原则

前缀索引对覆盖索引的影响

会导致回表

前缀索引区分度不高的问题

第一种方式是使用倒序存储

mysql> select field_list from t where id_card = reverse('input_id_card_string');

第二种方式是使用 hash 字段

你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

回表

查询主键索引的B+树

为什么没有理想的索引？

窄索引

包含索引列数为1或2

会通过主键查找相应数据,称为回表

宽索引

包含的索引列数⼤于2

可以防止回表

过滤因子

where后的条件

针对SQL查询的理想索引设计：三星索引

1. 在WHERE条件语句中，找到所有等值谓词中的条件列，将它们作为索引⽚中的开始列；

2. 将 GROUP BY和ORDER BY中的列加⼊到索引中；

3. 将SELECT字段中剩余的列加⼊到索引中。

为什么很难存在理想的的索引设计

1. 如果数据量很⼤,过多索引所需要的磁盘空间可能会成为⼀个问题，对缓冲池所需空间的压⼒也会增加。

2. 增加了索引维护的成本。

你能看到针对⼀条SQL查询来说，三星索引是个理想的⽅式，但实际运⾏起来我们要考虑更多维护的成本，在索引效率和索引维护之间进⾏权衡。

设置合理索引

一张表的索引个数不宜过多

在索引表中，我们也需要控制索引列的数量

单列索引和复合索引的⻓度也需要控制

没有理想的索引, 只有适合的索引设计. 需要在索引效率和维护成本中进行平衡

索引使用分析

主键索引

等值查询

范围查询

模糊查询

排序

索引失效

二级索引

联合索引

缓冲池

数据库缓冲池

如何读取数据

执行SQL语句的时候更新了缓存池中的数据，那么这些数据会同步到磁盘上吗？

做checkpoint的机制将数据回写到磁盘

脏页（dirty page）

指的是缓冲池中被修改过的页，与磁盘上的数据不一致。

查看缓冲池的大小

show variables like 'innodb_buffer_pool_size'

设置缓冲池大小 128MB

set global innodb_buffer_pool_size = 134217729

查看缓冲池个数

mysql > show variables like 'innodb_buffer_pool_instances'

开启多个缓冲池，你先需要将innodb_buffer_pool_size参数设置为小于等于1GB

然后再针对innodb_buffer_pool_instances参数进⾏修改

数据加载的三种⽅式

1. 内存读取

<ol><li>如果该数据存在于内存中，基本上执行时间在1ms左右，效率还是很高的。 </li></ol>

2.随机读取

如果数据没有在内存中，就需要在磁盘上对该⻚进⾏查找，整体时间预估在10ms左右

3. 顺序读取

通过last_query_cost统计SQL语句的查询成本

锁

按照粒度划分

全局锁

Flush tables with read lock (FTWRL)

全局锁的典型使用场景是，做全库逻辑备份

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟

既然要全库只读，为什么不使用 set global readonly=true 的方式呢

readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库

表级锁

另一类表级的锁是 MDL（metadata lock)

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

行锁

MyISAM不支撑行锁

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

页锁

加锁开销

低于行锁

锁死概率

高于行锁

并发性能

低于行锁

数据库管理的角度

共享锁

读锁或 S 锁

排它锁

排它锁也叫独占锁、写锁或 X 锁

意向锁

程序员编程角度

乐观锁

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件， 不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序 上，我们可以采用版本号机制或者时间戳机制实现。

适用于读多写少

悲观锁

对数据被其他事务的修改持保守态度，会 通过数据库自身的锁机制来实现，从而保证数据操作的排它性

适用于写操作多的场景

不同数据库或引擎支持情况

如何避免锁死

使用过程中避免锁死的方法

如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步 来获取，这样可以减少死锁发生的概率；

如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方 式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；

不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生 的概率

使用MySQL自身提供的机制

死锁检测

将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

直接进入等待，直到超时

innodb_lock_wait_timeout 来设置

MVCC：Multiversion Concurrency Control

什么是MVCC

MVCC是通过数据⾏的多个版本管理来实现数据库的并发控制

使用MVCC的好处

读写互相不阻塞

降低了死锁概率

解决了一致性读写问题

快照读

读取的是快照数据(历史版本)

当前读

读取最先的数据. 加锁的SELECT, 或者插入, 删除, 更新都会进行当前读

InnoDB

MV

Undolog

CC

Read View

来决定数据是否显示

针对不同的隔离级别，read view的生成策略不同。

InnoDB中的MVCC

事务版本号

当我们每次开启一个事务, 都会从数据库中获得一个事务ID, 这个事务ID是自增长的, 所以通过ID大小可以判断出来事务的时间顺序

行记录的隐藏列

db_row_id, 隐藏的行ID

db_trx_id, 操作这个数据的事务ID

db_roll_ptr, 回滚指针

Undo Log

InnoDB将我们的行记录快照保存在了Undo Log里

Read View, 帮助我们解决了行的可见性问题

Read View是如何工作的

Read View的结构

1. trx_ids，系统当前正在活跃的事务ID集合。

2. low_limit_id，活跃的事务中最⼤的事务ID。

3. up_limit_id，活跃的事务中最⼩的事务ID。

4. creator_trx_id，创建这个Read View的事务ID。

Read View的原则

trx_id < 活跃的最⼩事务ID（up_limit_id）

这个⾏记录在这些活跃的事务创建之前就已经提交了，那么这个⾏记录对该事务是可⻅的

trx_id > 活跃的最⼤事务ID（low_limit_id）

该⾏记录在这些活跃的事务创建之后才创建，那么这个⾏记录对当前事务不可⻅

up_limit_id < trx_id < low_limit_id

查询数据的步骤

1. ⾸先获取事务⾃⼰的版本号，也就是事务ID；

2. 获取Read View；

3. 查询得到的数据，然后与Read View中的事务版本号进⾏⽐较；

4. 如果不符合ReadView规则，就需要从Undo Log中获取历史快照；

5. 最后返回符合规则的数据

读已提交时 , 一个事务中的每一次SELECT查询都会获取一次Read View

可重复读时 , 一个事务只在第一次SELECT时会获取Read View , 后面所有的SELECT 都会复用这个Read View

MVCC下各种隔离级别的实现流程

读未提交

采用读取undolog的快照

读已提交

读取undolog的当前读

可重复读

在一个事务的select时只会获取一次read view

可重复读+ next-key

InnoDB是如何解决幻读的

在可重复读的情况下，InnoDB可以通过Next-Key锁+MVCC来解决幻读问题。

在读已提交的情况下，即使采⽤了MVCC⽅式也会出现幻读。

innodb锁三种类

记录锁：针对单个行记录添加锁

间隙锁（Gap Locking）：可以帮我们锁住一个范围（索引之间的空隙），但不包括记 录本身。采用间隙锁的方式可以防止幻读情况的产生。

Next-Key 锁：帮我们锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可 以解决幻读的问题。

日志

redolog

innodb引擎

当前页做了什么改动

保持持久化

WAL(write ahead logging)

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了

执行事务的时候，写完内存和日志，事务就算完成

先写日志再写磁盘。

文件大小固定,循环写,写满后会覆盖

crash safe 保证及时数据库异常重启，之前提交的记录不会丢失

恢复redolog上已经提交的数据

物理日志，记录在某数据页上作了什么修改

innodb_flush_log_at_trx_commit 设置为1时，每次事务的redo log都直接持久化到磁盘

binlog

sever层

逻辑日志，记录语句的原始逻辑

大小不固定，可追加写入，写到一定大小后切换文件

sync_binlog设置为1时，每次事务的binglog都持久化到磁盘

主从复制时，slave去拉取master的binlog在本地执行；伪装成slave，来接收binlog，然后解析数据，做业务处理，比如监听数据改动，做业务监控，缓存清理等。

三种格式

statement

基于SQL语句

row

基于内容

mix

混合

undolog

innodb引擎

逻辑日志

主要目的是记录一个事物的改动，这样如果这个事物rollback的话，可以根据undo log来恢复到上一个状态，实现事物的一致性

实现MVCC

更新一行数据的流程

执行器通过引擎获取要执行的行

执行器拿到这一行数据，对数据更新，再调用引擎接口写入这行数据

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

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

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

两阶段提交，保证redolog与binlog的数据一致性

查询优化器

SQL语句执行

存储过程与视图

视图

创建视图:CREATE VIEW

视图或者虚拟表

结构

视图作为一张虚拟表，帮我们封装了底层与数据表的接口

视图本身是虚拟表，本身不存储数据，如果想通过视图对底层数据表进行修改会受到限制

视图一般只用于查询

存储过程

如何实现

存储过程优缺点

优点

一次编译可多次使用，提升SQL的执行效率

减少开发工作量

安全性强

减少网络传输量

缺点

可移植性差

存储过程在MySQL ，oracle，SQLserver不能完全通用

调试困难

很多数据库没有调试过程

版本管理困难

当数据库变更时，存储过程无法使用

不适合高并发场景

高并发场景下，数据库表存在分库分表，存储过程可能不适用了

MySQL核心概念

Buffer Pool

数据库的一个内存组件

Buffer Pool默认情况下是128MB，有一点偏小，实际生产环境下完全可以对Buffer Pool进行调整

通过命令（SHOW ENGINE INNODB STATUS）查看innodb具体情况

buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数

磁盘随机读的性能是比较差的，所以不可能每次更新数据都进行磁盘随机读，必须是读取一个数据页之后放到Buffer Pool的缓存里去，下次要更新的时候直接更新Buffer Pool里的缓存页。

Change Buffer

写缓存

将数据页从磁盘读入内存中涉及随机 IO 访问，这也是数据库里面成本最高的操作之一

利用写缓存（Change Buffer）可以减少 IO 操作，从而提升数据库性能

通过参数innodb_change_buffer_max_size来动态设置

唯一索引的更新不能使用change buffer

change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

change buffer只限于用在普通索引的场景下，而不适用于唯一索引

适合写多读少的业务，因为查询会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价

redo log

本质是保证事务提交之后，修改的数据绝对不会丢失的。

redo log这个机制存在的意义

日志大致格式

因为你MySQL重启之后，把你之前事务更新过做的修改根据redo log在Buffer Pool里重做一遍就可以了，就可以恢复出来当时你事务对缓存页做的修改，然后找时机再把缓存页刷入磁盘文件里去。

为什么不在修改缓存页的同时刷入磁盘？

需要记录的东西

表空间号+数据页号+偏移量+修改几个字节的值+具体的值

结构

日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据

redo log buffer

用来缓冲redo log 写入的

通过设置mysql的innodb_log_buffer_size可以指定这个redo log buffer的大小，默认的值就是16MB，其实已经够大了，毕竟一个redo log block才512字节而已，每一条redo log其实也就几个字节到几十个字节罢了。

undo log

回滚日志

如果要回滚事务，那么就基于undo log来回滚就可以了，把之前对缓存页做的修改都给回滚了就可以了。

表空间

平时将数据一行一行插入表中，这个表是个逻辑概念，在物理层面，表对应的是表空间这个概念。

表空间的磁盘文件里，有很多的数据页的。

数据区

一组数据区包含256个数据区

一个数据区对应着连续的64个数据页，一个数据区1MB

一个数据页16KB

数据页

磁盘文件里的数据页进行的磁盘随机读写

从表空间的磁盘文件里读取数据页出来，这个过程是磁盘随机读操作

MySQL数据读写机制

一种是对redo log、binlog这种日志进行的磁盘顺序读写

所谓顺序写，就是说在一个磁盘日志文件里，一直在末尾追加日志

磁盘顺序写的性能其实是很高的

一种是对表空间的磁盘文件里的数据页进行的磁盘随机读写

IOPS和响应延迟

IOPS：每秒I/O数的简称，表示一秒中输入输出操作（比如读和写）的次数

可以用IOPS数值来描述一个数据库的IO操作量

拓展

磁盘的顺序读写，随机读写

顺序读写主要时间花费在了传输时间

随机读写需要多次寻道和旋转延迟

页分裂

分裂过程

比如在第一个数据页里有一条数据的主键是10，第二个数据页里居然有一条数据的主键值是8，那此时肯定有问题了。 所以此时就会出现一个过程，叫做页分裂

核心目标就是保证下一个数据页里的主键值都比上一个数据页里的主键值要大

自增主键不会出现，如果你的主键不是自增的，他可能会有一个数据行的挪动过程，保证你下一个数据页的主键值都大于上一个数据页的主键值

索引页

表的实际数据是存放在数据页里的，然后你表的索引其实也是存放在页里的，此时索引放在页里之后，就会有索引页， 假设你有很多很多的数据页，那么此时你就可以有很多的索引页。

索引页里存放的就是下一层的页号和最小索引字段值

数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据大小有序排列的

数据页

页里面的记录都是组成一个单向链表的，而且是按照数据大小有序排列的

下一个数据页的所有主键值大于上一个数据页的所有主键值

回表

MYSQL

MySQL数据库调优

有那些方式可以知道数据库的性能问题

用户反馈

日志分析

我们可以通过查看数据库日志和操作系统日志等方式找出异常情况， 通过它们来定位遇到的问题

服务器资源使用监控

通过监控服务器的 CPU、内存、I/O 等使用情况，可以实时了解服务器的性能使用，与历 史情况进行对比。

数据库内部状况监控

数据库调优有那些维度

选择合适的数据存储

MySQL

oracle

SQLserver

数据库性能分析

加缓存，或者缓存优化

慢SQL分析

执行计划分析

执行时间长

1、索引设计优化 2、join表过多，需要优化 3、数据表设计优化

等待时间长

服务器参数调优

数据量大

1、读写分类 2、分库分表

优化层次

服务器硬件优化

连接

增加服务端可用连接数

减少服务端占用连接数

使用连接池

配置优化

操作系统配置

Centos系统的内核参数，磁盘调度策略等优化

数据库配置

架构优化

缓存

主从复制

异步复制

半同步复制

全同步复制

多库并行复制

GTID复制

读写分离

分库分表

垂直分库

水平分库

高可用

Haproxy+keepallived

集群（NDB Cluster）

Galera Cluster for mysql

MHA(美团高可用架构)

MGR

库级优化

控制一个库中的数据表数量

采用主从架构优化我们的读写策略

除此以外，我们还可以对数据库分库分表

业务分库

表的字段过多，将一张表拆分为多张表

单表行数过多采用数据分配到不同表

优化表设计

表结构要尽量遵循第三范式的原则

如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优 化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率

表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。

在满足业务要求的情况下，数值型>字符型（CHAR>VARCHAR）

优化逻辑查询

逻辑查询优化

通过改变 SQL 语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查询进行 重写

子查询优化

物理查询优化

创建索引应该选择数据重复低，重复度不超过10%

要注意联合索引对索引使用的影响

要注意多个索引对索引使用的影响

查询路径优化

单表查询

尽量减少扫描数据的行数

两张表的连接

多张表的连接

使用 Redis 或 Memcached 作为缓存

explain

执行计划

所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描， 拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序

id

id越大就先执行，相同id的从上到下执行

select_type

PRIMARY，查询类型是主查询的意思

SUBQUERY，也就是子查询

SIMPLE，简单的查询

union_result

MATERIALIZED

物化方式存储子查询的临时结果

derived

type

consts

通过主键或者唯一索引的访问，速度超高

ref

使用了普通二级索引，不是唯一索引，此时这种查询速度也是很快的

使用了主键或者唯一索引并且还使用了is null 语法

ref_or_null

针对一个二级索引同时比较了一个值还有限定了IS NULL， 类似于select * from table where name=x and name IS NULL， 那么此时在执行计划里就叫做ref_or_null

range

利用索引做了范围筛选

index

直接扫描二级索引的叶子节点，也就是扫描二级索引里的每条数据

all

全表扫描，直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里全部数据

possible_keys

跟type结合起来的，意思就是说你type确定访问方式了， 那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里

ref

就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息

rows

预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据

filtered

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

例子

key

就是在possible_keys里实际选择的那个索引

key_len

索引的长度

Extra

Nested Loop（嵌套循环的访问方式）

using index(使用了覆盖索引，仅仅涉及到了一个二级索引，不需要回表)

using temporary

对全表数据放到临时表里做大量的磁盘文件操作

相当耗时，性能也是极低

tmp_table_size

Using index condition

Using where

例子

Using join buffer

使用内存技术来提升关联的性能

Using filesort

把表全数据放磁盘文件排序

性能极差

sort_buffer_size

多表关联

inner join

就是要求两个表里的数据必须是完全能关联上的，才能返回回来，这就是内连接

连接条件可以放在where语句里

outer join

左外连接

在左侧的表里的某条数据，如果在右侧的表里关联不到任何数据，也得把左侧表这个数据给返回出来

右外连接

在右侧的表里如果关联不到左侧表里的任何数据，得把右侧表的数据返回出来

外连接一般是把连接条件放在ON字句里

驱动表、被驱动表

先从驱动表里根据WHERE条件去筛选一波数据

从驱动表里扫出来一波数据，接着又来一个for循环一条一条去被驱动表里根据ON连接条件和WHERE筛选条件去查

针对多表查询的语句，我们要尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找， 接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！

根据成本优化选择执行计划

IO成本

CPU成本

优化思路

分析基本流程

子主题

慢SQL分析思路

1.定位到执行慢的sql语句

执行show VARIABLES LIKE '%quer%'

第一个表示慢日志是否开启，默认关闭，第二表示慢日志的文件的位置。 一般执行时间超过10秒的sql语句就会被放进这个文件里面（10秒是默认值，可以修改），这样就可以定位到执行慢的sql语句。

2.用explain分析执行慢的语句

ID：表示查询中执行select子句或操作表的顺序，当id相同时，按从上到下的顺序执行，id不同时，id数值大的先执行。

select_type：select_type代表的是查询的类型,主要是用于区别普通查询,联合查询,子查询。 SIMPLE ：简单的select查询，查询中不包含子查询或者UNION。 primary：查询中若包含任何的子查询,最外层查询则被标记为primary。 subquery：表示子查询。 dervied:zai from列表中包含的子查询标记为derived(衍生) mysql会递归执行浙西子查询,把结果放在临时表里。 UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVE union result :从union表获取结果的select。

table：表示执行的表

type：type所显示的是查询使用了哪种类型… 这个字段很重要，从好到坏的顺序是：system > const > eq_ref > ref > range > index > all 一般来说，至少要保证能达到range级别。 system:该类型表示表中只有一行记录 const:常量匹配,表示通过索引一次就可以找到数据 eq_ref:唯一性索引扫描,对于每个索引建,表中只有一条记录和它匹配 ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 range:只查找范围内的数据,使用索引来匹配行,查询的范围缩小了,自己比前面的类型性能更好 index:(full index scan)全索引文件扫描比all要好不少,将直接从索引数据结构中找寻数据,比全表扫描理论上快不少 all: Full Table Scan 将遍历全表以找到匹配的行

possible_keys: 显示可能应用在这张表中的索引，一个或多个,但不一定会使用到

keys：显示查询语句中实际使用的索引,如果没有使用则为null

key_len: 显示索引中使用的字节数,通过key_len计算查询中使用的索引长度,在不损失精确性的情况下索引长度越短越好,key_len显示的值为索引字段最可能的长度,并非是实际使用长度

ref:显示索引的那一列被使用了，如果可能的话，最好是一个常数。

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

extra:包含十分重要的额外信息 Usiing filesort:说明mysql读数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql无法利用索引完成的排序称为"文件排序". Using temporary:使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 Using index:表示相应的select操作中使用了覆盖索引,. Using where : 表明使用了where过滤 Using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些 impossible where:where子句的值总是false，不能用来获取任何元组

3.对对应的sql语句进行优化

通过一些提高性能的经验来修改sql语句，或者让sql走索引。

优化工具

MySQL日志与备份

mysqldump

MySQL数据库自带的一个很好用的备份命令。是逻辑备份，导出的是SQL语句。也就是把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程,是全量备份

备份例子： mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --databases zsythink -p > zsythink.sql

属性

--flush-logs mysqldump备份时，立刻滚动二进制Binlog文件，也就是生成新的binlog文件开始记录日志，那么我们在恢复的时候，就不用从一个binlog文件的中间开始恢复，高效！

--master-data 指定mysqldump备份时，记录对应二进制日志Binlog文件的位置 =0 不记录位置，与不使用此命令没有区别，没用 =1 默认值，也就是不指定值就是1，记录具体的Binlog文件的位置，会在备份文件中生成“CHANGE MASTER TO”语句，标明备份开始二进制文件的前缀名，和对应的position位置 =2 与1不同的时，它会注释掉备份文件中生成的“CHANGE MASTER TO”语句，如果只是单纯的记录position的位置，建议使用值2

--routines 表示备份时，存储过程和存储函数也会被备份

--triggers 表示备份时，触发器会被备份

--events 表示备份时，事件表会被备份

--single-transaction 在备份的过程中使用--single-transaction选项，它会让备份基于独立的事务进行

恢复： 执行生成的sql文件就可以了

mysql binlog

基本概念： 二进制日志，记录对数据发生或潜在发生更改的sql语句，并以二进制的形式保存在磁盘中

作用

复制:Mysql的master-slave协议，让slave可以通过监听binlog文件实现数据复制，达到数据一致的目的 一般是在master端开启binlog，然后再slave端通过读取master端的binlog文件来实现主从数据一致性 恢复：通过mysqlbinlog工具恢复数据 一般我们使用mysqldump备份是全量备份，它备份某一个时间点的所有数据。如果在下一次备份前数据库故障，那么上次备份时间点到数据库故障时间点之间的数据库变化就会丢失，这就需要我们的binlog记录这些增量数据了。

binlog日志格式

1.ROW 仅保存记录被修改细节，不记录sql语句上下文相关信息 2.STATEMENT 每一个会修改数据的sql语句都会记录在binlog中 3.MIXED 以上两种level的混合使用 执行show variables like 'binlog_format'查看

管理binlog

show variables like ‘log_bin’查看是否开启binlog

管理binlog的sql语句

show master logs；查看所有Binlog的日志列表 show master status；查看最后一个Binlog日志的编号名称，以及最后一个事件结束的位置 flush logs；刷新Binlog 此刻开始产生一个新编号的Binlog日志文件 reset master；清空所有的Binlog日志（慎用）

查看Binlog相关的sql语句

show binlog events in ‘L1WWITAFW5IVTNG-bin.000001’ from 500 limit 1，2；从指定的位置，带有偏移，开始查看binlog日志，限制查询的条件

恢复

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码数据库名

常用选项： --start-position=953 起始pos点 --stop-position=1437 结束pos点 --start-datetime="2013-11-29 13:18:54" 起始时间点 --stop-datetime="2013-11-29 13:21:53" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

redo log

redolog记录着物理变化，即什么页修改了什么数据。每当我们在内存中修改数据时，会在内存中写redo log，假如修改未落到磁盘，可以通过redolog恢复这部分修改

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

日志的记录过程

这能解释mysql如何保证redolog和binlog日志一致 1.一条更新语句请求 2.在内存中更新 3.将修改结果更新到内存 4.在n数据页的m处修改了x，并把这行记录记为preprea 5.修改好了，可以提交事务 6.写入binlog 7.commit 提交事务 8.将redolog的这条记录置为commit

redo log的记录方式

redolog的大小是固定的，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小，redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。

write pos表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。

解决的问题

有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失。

它和binlog的区别

1、redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。 2、redo log是innodb引擎层实现的，并不是所有引擎都有。 3、binlog是server层实现的，意味着所有引擎都可以使用binlog日志 4、binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

undo log

undolog是回滚日志，主要有两个作用：回滚和多版本控制(MVCC)

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

undo log和redo log的区别

Undo 记录某数据被修改前的值，可以用来在事务失败时进行 rollback； Redo 记录某数据块被修改后的值，可以用来恢复未写入 data file 的已成功事务更新的数据。 Redo Log 保证事务的持久性 Undo Log 保证事务的原子性

参考资料

https://www.processon.com/view/5f1998bbe0b34d54dac69f4a?fromnew=1

https://www.processon.com/view/5f3cdb807d9c0806d4216ad0?fromnew=1

未分类

排序

order by rand() 使用了内存临时表，内存临时表排序的时候 使用了 rowid 排序方法

count