mysql知识点整理思维导图模板_ProcessOn思维导图、流程图

慢sql优化

避免不必要的列

索引优化

尽量使用覆盖索引

尽量使用联合索引

不在索引上、列做函数计算

尽量不使用!=和<>

join优化

小表驱动大表

优化子查询

适当做冗余

不用join太多表

排序优化

利用索引字段扫描的顺序排序

union优化

条件下推

每个union都加上查询条件

日志

分类

错误日志

对mysql启动、关闭、运行过程做了记录，帮助mysql定位问题

慢查询日志

用来记录超过定义时长的查询语句的

通过慢查询，可以查找出哪些查询语句的执行效率很低

一般查询日志

记录了所有数据库请求信息

binlog二进制文件

记录DDL、DML语句，除了select和show之外

以事件形式记录，并保存在二进制文件

使用场景

主从复制

恢复数据

innodb引擎特有

redo log重做日志

mysql数据以页为单位，redo log记录在某个数据页上做的修改

让mysql拥有崩溃恢复能力

比如mysql实例挂了或宕机，重启时，innodb存储引擎使用redo log 恢复数据

保证数据的持久性与完整性

undo log回滚日志

作用：对数据进行回滚

当事务对数据库进行修改，innodb引擎不仅会记录redo log,还会生成对应的undo log,

如果事务执行失败会调用rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前

bin log和redo log的区别

存储引擎

binlog记录所有与数据库有关的日志记录，包括innodb和myisam等存储引擎

redo log只记录innodb引擎的日志

内容不同

binlog记录关于一个事务的具体操作内容，是逻辑日志

redo log记录的是关于每个页（page)的更改的物理情况

写入时间不同

bin log是事务提交前写入

redo log在事务执行过程中，会不断写入

写入方式不同

binlog是追加写入的，不会覆盖

redo log是循环写入和擦除的，会覆盖

redo log如何写入磁盘

redo log缓冲区

redo log不是直接落入到磁盘的，而是先写到缓冲区

刷盘时机

缓冲区空间不足时

写入容量占到总容量大约一半时

事务提交时

后台线程输入时

正常关闭服务器

磁盘满时

命令执行顺序

from

on

join

where

group by

having

select

distinct

order by

limit

最左前缀原则

MVCC

多版本并发控制

维护数据历史版本

关键点

隐式字段

事务id

回滚日志指针

undo log

版本链

快照读&当前读

Read View

生成时机

读已提交

每次读取数据前都生成一个read View

不可重复读

第一次读取数据时生成一个read View

内容

m_ids：活跃事务id列表

min_trx_id：最小的活跃事务id

max_trx_id：下一个事务id

cretor_trx_id:生成readView的事务

判断逻辑

作用

读已提交、可重复读都只能读取已经提交的事务所修改的记录

版本链中某个版本的修改未提交，则这个版本不能被读取

需要确定版本链中哪个版本能被当前事务读取

流程

每次变动都把undo日志记录下来

并用回滚指针指向undo log

对该条记录的修改日志串联起来，就是版本链

读写分离

主从架构

一主一从

一主多从

基本实现

数据库服务器搭建主从服务器

数据库主机负责读写操作，从机负责读操作

主机通过复制将数据同步到从机

业务服务器将写操作发给主机，读操作发给从机

分配服务器

程序代码封装

抽象一个数据访问层

实现读写操作分离和数据库服务器连接的管理

中间件封装

主从复制

主机数据写入，更新bin log

主机dump线程把bin log推给从机

从机IO线程接收主机binlog，记录到relay log

从机sql线程读取并执行relay log，并记录自己的bin log

复制延时

原因

大并发更新操作，从机读取的binlog线程仅一个

某个sql在从服务器上执行时间稍长，或者某个sql要进行锁表，导致主机的sql大量积压，未被同步到从机

解决

根据业务指定主从服务器读取数据

实时性高、重要业务指定在主服务器读取

实时性不高，非关键业务指定在从服务器读取

连接

内连接

外连接

左连接

右连接

交叉连接

笛卡尔积

三范式

数据表不可再分

非主键需完全依赖于主键，不能不分依赖

比如订单表里存了商品信息（商品价格、商品类型），那就需要把订单id和商品id作为联合主键，才能符合第二范式

非主键只能依赖于主键，而不依赖非主键

比如订单表存用户地址信息

作用

控制数据库冗余，节省空间

一般会反范式，冗余数据，提供性能。空间换时间

区别

varchar和char的区间

varchar

可变长字符串

最多存放字符个数，65532

存取慢，因为长度不固定

不占据多余空间

char

定长字符串，固定长度

最多存放字符个数，255

存取快

占据空间

blob和text

blob

存储二进制数据

没有字符集

text

存储大字符串

有字符集，且根据字符集的校对规则进行排序和比较

datetime和timestamp

相同

存储时间的表现格式一致

YYYY-MM-DD HH:MM:SS

都包含日期和时间

都可以存储微秒

不同

日期范围

datetime是1000-01-01 ~ 9999-12-31

timestamp是1970-01-01 ~ 2038-01-09

时区

datetime与时区无关

timestamp存储和显示都依赖于时区

存储空间

datetime是8字节

timestamp是4字节

默认值

datetime默认是null

timestamp是当前时间

in和exists

逻辑不同

in是外表和内表哈希连接

exists是对外表loop查询，每次loop再对内表查询

性能

两表数据量差不多，in和exists性能差不多

内表大，用exists

内表小，用in

not in 和not exists

not in全表扫描，没用索引

not exists走索引

union和union All

union all不会合并重复的记录行

union效率比union all高

count

count(1）

忽略所有行，1代表代码行，不忽略null

count(*)

包括所有列，不忽略null

count(列名)

只包括列名那一列，统计结果忽略null

执行速度

有主键时，用count(主键)

没有主键，用count(1)

表中只有1列，用count(*)

myIsam和innodb

存储结构

每个myIsam在磁盘中存储为3个文件

innodb 所有表存储在一个数据文件（可能是多个，或者是独立的表空间文件）

innodb受限于操作系统文件大小。一般是2GB

索引

myIsam是非聚簇索引，结构是B树

Innodb是聚簇索引，结构是B+树

效率

外键支持

myIsam不支持外键

innodb支持外键

事务支持

myIsam不支持事务

innodb支持事务，具有事务、回滚和崩溃修复能力的事务安全特效

表行数

myisam保存了表的总行数，如果select count(*) from 会直接取这个值。但是加条件的话，会遍历整张表

innodb会遍历表得到表行数

主键必须

myisam允许表没有任何索引和主键

innodb如果没有设定主键或者非空唯一索引，就会自动生成一个主键，数据是主索引的一部分，附加索引保存的是主索引的值

最小粒度锁

myIsam仅支持表锁，更新时会锁住整张表，导致其它查询和更新都阻塞

innodb支持行锁

索引

分类

基本使用

主键索引

innodb主键是默认索引

数据列不允许重复

不允许null

一个表只能有一个主键

唯一索引

数据列不允许重复

允许null

一个表允许多个列创建唯一索引

普通索引

无唯一性限制

允许NULL值

组合索引

多列值组成一个索引

组合搜索

效率大于索引合并

覆盖索引

避免回表

数据结构

哈希索引

B树索引

B+树索引

物理存储

聚簇索引

一张表只能有1个聚簇索引

数据和键值在一棵树上，叶子节点就是数据

物理顺序按照索引排序

非聚簇索引

一张表可以有多个非聚簇索引

数据和键值不在一颗树上

物理顺序不按照索引排序

为什么能加快查询

mysql通过BTREE算法生成索引文件，查找数据库时，找到索引文件进行遍历

在较小的索引数据里查找，然后映射到对应的数据，能大幅度提升查找效率

B+树的结构

一个父节点可以有多个子节点，树的高度最高为3层，降低IO

非叶子节点不存储真实数据，存储指引搜索方向的数据项

扫表能力强，如果要全表扫描，只用扫描叶子结点，不需要遍历整颗树

效率、IO稳定，每次都是在叶子结点拿到数据

磁盘读写能力强，因为根节点、枝节点不保存数据，所以一个节点能够保存更多关键字，一次磁盘加载的关键字更多，IO次数更少

叶子结点之间使用双向链表

支持范围查找

排序能力更强，因为叶子结点有下一个数据区的指针，数据形成链表

二叉树

二叉树会退化为链表，不稳定

平衡二叉树

每个节点只能存储一个键值和数据，磁盘IO次数多

B+树可以存放更多节点，树高度降低，减少磁盘IO

hash索引

B+树可以范围查询，hash不支持

B+树可以排序，hash不支持

B+树支持联合索引最左侧原则，hash不支持

hash的等值查询效率高于B+树

B+树能模糊查询，hash不适合模糊查询

锁

加锁机制

乐观锁

悲观锁

锁粒度

行锁

开销大

加锁速度慢

锁冲突发生概率小

锁定粒度小

并发度高

页锁

介于行锁和表锁之间

表锁

开销小

加锁快

锁冲突概率大

锁粒度大

并发低

锁模式

记录锁

使用唯一性索引（唯一索引和聚簇索引）等值查询且精准匹配到一条记录时

间隙锁

范围查询

等值查询

且没有命中记录时

解决幻读问题

next-key临键锁

记录锁和间隙锁的结合

范围查询且命中部分

唯一性索引等值查询，会退化成记录锁

没有匹配到任何记录时，退化成间隙锁

解决幻读问题

意向锁

为了支持innodb的多粒度锁

解决表锁和行锁共存问题

表上有意向排它锁之后，其它事务去加锁就会阻塞

插入意向锁

兼容性

共享锁

排它锁

事务

特性

A原子性

redo log保证

C一致性

undo log

I 隔离性

MVCC实现

D持久性

redo log保证

隔离级别

读未提交

问题

脏读

幻读

不可重复读

实现

读不加锁

读已提交

问题

不可重复读

实现

MVCC

ReadView

每次读取数据都生成一个readView

每个事务只能读取它能看到的版本

可重复读

问题

幻读

实现

MVCC

Read View

第一次读数据生成

每个事务只能读取它能看到的版本

串行化

实现

读写都加锁

分库分表

水平分表

路由方式

范围路由

难点

分段大小选取上

分段太小，导致切分后子表数量过多

分段太大可能导致单表依然存在性能问题

一般建议分段大小在100万到2000万之间

优点

随着数据的增加平滑扩充新表

缺点

分布不均匀

hash路由

难点

初始表数量的选取

表数量太多维护麻烦

表数量太少可能导致单表性能存在问题

用了hash路由后，很难增加子表，所有数据需要重分布

优点

表分布较均匀

缺点

扩充表麻烦

配置路由

路由表

用一张独立的表记录路由信息

垂直分表

问题

跨库join

在业务代码中进行关联数据

适当冗余字段

数据异构，需要跨库join的数据异构到ES

事务

需要分布式事务解决

跨节点的count，order by,group by以及聚合函数问题

数据迁移、容量规划、扩容等问题

ID问题

数据库表被切分，不能再依赖数据库自身的主键生成机制

需要一些手段保证全局主键唯一

设置自增步数，三张表步长是3

UUID，但是不连续的主键插入会导致严重的页分裂，性能差

分布式ID，雪花算法