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

基本概念

SQL语句分类

DDL

数据定义语句（create drop alter）

DML

数据操作语句（insert update delete）

子查询操作符

all

any

合并查询

union

union all

intersect

minus

DQL

数据查询语句（select）

DCL

数据控制语句（grant revoke commit rollback）

基本操作语句

显示数据库创建语句：SHOW CREATE DATABASE db_name

辅助操作

约束

not null

unique

primarykey

foregin key

check

触发器

函数

存过程

数据备份为sql文件及数据恢复

备份

恢复

函数

时间日期函数

字符串函数

数字函数

流程相关函数

其他函数

mysql用到字符编码的地方

client：客户端字符集

connection：连接数据库的字符集，不指定则使用服务端默认字符集

database：指定数据库使用的字符集，若建库时不指定则使用服务器字符集

result：数据库给客户端返回的字符集，若不指定则使用服务器默认字符集

server：服务器安装时使用的默认字符集

system：数据库系统使用的字符集

事务和锁

事物

原子性
持久性
一致性

redo log

用户崩溃时恢复数据（WAL预写日志的方式，在数据提交前记录日志（日志文件顺序存储速度快））；
Force Log at Commit（1.设定参数每隔一段时间落盘；2.事物提交时落盘）保证该日志落盘（从redo log buffer 到 redo log），以此来达到奔溃时数据可能恢复;。
总的有两个日志空间交替使用，当空间满的时候出发数据文件落盘(Buffer Poll 到数据文件)，数据文件落盘之后及可清除redo log日志文件

force log at commit

保证事物提交之后redo log已经持久化

Double write

用于提高数据库的可靠性，用于解决脏页落盘时部分写失效问题

操作系统磁盘最小单位4KB；
mysql最小操作单位16KB；
duble write 保证mysql的数据页要么全部成功，要么全部失败

undo log

用于对数据回滚时进行撤销，也会用于隔离性的多版本控制

控制台操作

0) start transaction //开始一个事务
1) savepoint 保存点名 //设置保存点
2) rollback to 保存点名 //取消部分事务
3) rollback //取消全部事务
4) commit //提交事务.

隔离性

读未提交（脏读）

当一个事务读取另一个事务尚未提交的修改时，产生脏读

读已提交（不可重复读）

同一查询在同一事务中多次进行，由于其他提交事务所做的修改，每次返回不同的结果集，此时发生非重复读

重复读（幻读）

同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

导致一条数据查询不存在，但是插入也不能成功（因为别的事物插入了该数据，而当前session不可见）

序列化

相关操作

show engines 查看当前提供的存储引擎

show variables like '%storage_engine%' 查看默认搜索引擎

MVCC
（多版本并发控制）

基本概念

当前读

像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读

像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；

数据库并发场景

读-读

读-写

MVCC解决该问题

为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

写-写

维持一个数据的多个版本，使得读写操作没有冲突

快照读就是 MySQL 实现 MVCC 理想模型的其中一个非阻塞读功能

MVCC 模型在 MySQL 中的具体实现则是由
3 个隐式字段，undo 日志，Read View 等去完成的

三个隐式字段

DB_TRX_ID

DB_ROLL_PTR

DB_ROW_ID

删除字段

undo日志

insert undo log

update undo log

purge

Read View
(读试图)

RR级别

某个事务的对某条记录的第一次快照读会创建一个快照及 Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，所以对之后的修改不可见

RC级别

每次快照读都会新生成一个快照和 Read View , 这就是我们在 RC 级别下的事务中可以看到别的事务提交的更新的原因

间隙锁

磁盘文件

日志文件

错误日志文件（error log）

二进制日志文件(bin log)
【mysql服务】

需要手动开启

通用查询日志(general query log)

慢查询日志(show query log)

中继日志(relay log)

主从复制时，从库从主库获取过来的bin log日志在本地保存为中继日志（relay log）

重做日志文件(redo log)

用于奔溃时回复数据，循环使用；
根据checkpoint脏页落盘成功之后就可以清除该记录（当redolog空间不足也会触发脏页落盘）。

回滚日志文件(undo log)
【innodb存储引擎】

事物回滚

隔离性，多版本控制

ibdata

*.fmt

磁盘

随机写文件

数据（数据增删很频繁）

顺序写文件

日志（写得快，都是增量）

keepalived/HAProxy

keepalived

检查被代理服务器的可用性

任意时间只有一个服务（获得与keepalived连接的服务）对外提供服务

被代理服务通过抢占的方式去争夺与keepalived的链接，一旦有连接成功，别的需等待该连接失效

HAProxy

动态代理服务器，更多的类似nginx

执行流程

查询流程

整体架构

sql执行流程

1.先尝试从~~查询缓存~~(8.0以后不再有查询缓存)中查询该sql是否已经有缓存的结果了；
2.分析器拿到sql之后会尝试对sql语句进行词法分析和语法分析，校验语法的正确性；
3.优化器拿到分析器的sql之后，开始继续解析sql，判断需要走什么索引，根据实际情况重写sql，最终生成执行计划；
4.执行器根据执行计划执行sql，执行之前会先进行操作权限校验；
然后根据表存储引擎调用对应接口进行查询数据，这里的扫描行数就是指的接口返回的记录数，执行器拿到返回记录之后进一步加工；
5.执行器根据sql条件依次筛选数据；
从磁盘数据中读取数据到内存Buffer Pool（数据页，索引页）中；

CUD流程

1.数据写入Buffer Pool的同时，先写入数据到redo log buffer中；
2.redo log buffer 依据 Force log at Commit(按时或事物提交的时候)写入 redo log日志中；
3.Buffer pool 依据check point 择时脏页数据落盘；（redo log日志满了也会触发该操作，成功之后会清理redo log日志）;
4.Double write保证脏页数据不会出现部分落盘失败的情况。

in与exists效率

1. 如果查询的两个表大小相当，使用in和exists差别不大；
2. 如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询小的使用in；
3. not in和 not exists ：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而notexists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in快。

操作命令

show processlist

查看连接池中建立好的连接

Time：表示这个连接多久没有动静了；
默认地，如果超过8个小时还没有动静，连接器就会自动断开连接，可以通过wait_timeout参数进行控制

限制

任何标准表最多可以创建16个索引列

集群架构

三种方式

MySQL Cluster

复杂

drbd 磁盘镜像网络raid

维护成本高

MySQL复制

缺点

存在延时问题

操作步骤

1.主库记录bin log日志；
2.主库推送bin log日志给从库；
3.从库接受日志保存为中继日志(relay log)；
4.从库线程从中继日志(relay log)还原数据。

bin log日志记录三种方式

statement

记录语句

Row

记录影响的数据行

mix

混合statement 和 row

常用复制架构

一主多从

缺点

从库多了之后，主库推送bin log日志压力会增大

多级复制

从库复制给从库，一级一级传递

双主复制

两个数据库互为主从

需要的技术

浮动IP-keepalived

双主多级复制

复制方式

异步复制

主库dump线程异步负责推送bin log日志给从库

缺点

主从数据不一致

在dump线程还未推送日志或推送过程中挂了

从库性能比主库差等原因，导致在使用数据的时候从库还没有从中继日志（relay log）中恢复数据

异步复制

半同步复制

主库bin log日志要推送在从库中并记录中继日志（relay log）之后才返回客户端成功

搭建时候需要插件支持

半同步复制，主从库都需要的插件

读写分离实战

核心问题

SQL路由问题

Mysql+keepalived实现双主集群

两个服务都需要设置logs-slave-updates ,将从另一个主库收到的日志也保存bin-log日志（主从复制情况下，从库收到的中继日志不需要保存一份bin-log日志，但主从从的情况也需要设置该参数）

spring提供的解决方案

spring提供的多数据源解决方式

分库分表实现

方案

客户端分片

应用层直接实现

定制JDBC协议

定制ORM框架

代理分片

Mycat

常见概念

逻辑库

逻辑表

分片表

非分片表

数据量不大的表，没必要分片

ER表

解决跨库查询的问题（子表和父表存在同一个库，主从明细表等）

全局表（数据冗余）

每个库都保存一个数据（例如字典表一些变更少，数据少的数据）

分片节点

节点主机

分片规则

哈希分片

时间分片

全局序列号
（分布式ID生成算法）

基于数据库实现

采用一台服务器一次性申请多个ＩＤ放入本地缓存（保证效率）

雪花算法

64位（1位符号（0）+41位时间戳+10位机器标识+12位序列号）

zk实现

持久顺序节点，异步删除不再使用的节点

redis

Cobar

ShardingSphere

TDDL

Mysql-proxy

Oneproxy

Atlas

Vitess

优化

一般操作步骤

1.通过show status了解各种SQL执行频率；

show [session|global] status [like '???']

session来表示当前的连接的统计结果(默认)，
global来表示自数据库上次启动至今的统计结果

show status like ‘Com_%’;

Connections：试图连接MySQL服务器的次数
Uptime：服务器工作的时间（单位秒）
Slow_queries：慢查询的次数 (默认是慢查询时间10s)

2.定位执行效率较低的SQL；

默认不记录慢查询日志，需要在服务启动时手动开启；
通过慢查询日志定位执行效率较低的SQL语句。
慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句

慢查询

常用配置

slow_query_log 启动停止记录慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

记录语句

记录CRUD所有符合条件的语句

分析工具

mysqldumpslow

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)

c:总次数
t:总时间
l:锁的时间
r:总数据行
at,al,ar :t,l,r平均数【例如：at = 总时间/总次数】

-t top 指定取前面几天作为结果输出

pt_query_digest

pt-query-digest --explain h=127.0.0.1, u=root,p=password slow-mysql.log

3.通过explain分析低效率SQL的执行情况；

Explain + SQL语句

id

表示查询中执行select子句或操作表的顺序

三种情况

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

select_type
（查询类型）

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

SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY：若查询中包含任何复杂的子查询，则外层标记为此类型
SUBQUERY:在select或where中使用的查询使用该类型
DERIVED/MATERIALIZED:在from列表中使用的子查询被标记为DERIVED（衍生），mysql会递归这些子查询，把结果放入临时表中
UNION:若第二个select出现在union之后，则被标记为UNNION；若UNION被包含在from子句的查询中，则外层被标记为DERIVED
UNION RESULT:从union表获取结果的select

table

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

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了；const用于比较primary key或者unique索引
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询
index：使用了整个索引文件（全表扫描的一种）
all：Full Table Scan，将遍历全表以找到匹配的行

possible_key

key

key_len

表示索引使用的字节数
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

key_len计算总结

ref

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

rows

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

extra

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

4.确定问题，并做相应处理。

索引

查看索引的使用情况 show status like ‘Handler_read%’;
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。

常用优化方式

选择合适的存储引擎

合适的数据类型

对表进行水平划分

对表进行垂直划分

数据库参数配置

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

选择合适的编码

四个方面

1.数据库设计；

2.sql优化

大批量插入数据

MyISAM：
alter table table_name disable keys;
loading data//insert语句;
alter table table_name enable keys;

Innodb：
1，将要导入的数据按照主键排序
2，set unique_checks=0,关闭唯一性校验。
3，set autocommit=0,关闭自动提交。

优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。
如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

原则

优化需要优化的Query
定位优化对象性能瓶颈
从Explain入手
永远小结果集驱动大结果集#
尽可能在索引中完成排序
只取自己需要的Column
仅仅使用最有效的过滤条件
尽可能避免复杂的join和子查询

3.数据库参数设置；

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

4.恰当的硬件资源和操作系统

避免索引失效

尽量全值匹配

联合索引，尽量吧联合字段都使用起来

最左匹配原则

如果索引了多列，查询从索引的最左列开始并且不能跳过部分索引

不在索引上做任何操作

不要在索引字段上做操（函数，类型转换，计算）作，会失效

范围条件放最后

范围条件之后的索引字段会索引失效

尽量使用覆盖索引(查询列是索引列)，减少使用select *

少用不等于

使用不等于的时候会索引失效导致全表扫描

Null、Not null可能有影响

字段not null(所以字段都有值，此时进行null值判断没有任何意义，如一定要用可使用覆盖索引方式)

条件为is null 索引失效

条件is not null 索引失效

字段为is null

条件为is null 使用索引

条件为is not null 索引失效

like查询使用‘%具体字段’的方式会索引失效，全表扫描

字符串不加单引号会索引失效

类型不匹配会调用函数转类型，从而导致失效

union比or的效率更高