Mysql数据库知识图谱思维导图模板_ProcessOn思维导图、流程图

索引

索引类型

主键索引

数据列不允许重复，不允许为NULL，一个表只能有一个主键

子节点存储了整行数据，非主键索引存储的是主键

唯一索引

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

普通索引

基本的索引类型，没有唯一性的限制，允许为NULL值

全文索引

是目前搜索引擎使用的一种关键技术

数据结构

Hash

Memory引擎支持

B/B+树

操作

B+树的插入

叶节点未满，子节点未满

直接把记录插入叶子节点中

叶节点满了，子节点未满

发生叶节点拆分

将中间节点放在子节点中

小于中间节点的记录放在左边

大于等于中间节点记录放在右边

叶节点满了，子节点满了

拆分叶节点

小于中间节点的记录放在左边

大于等于中间节点记录放在右边

拆分子节点

小于中间节点的记录放在左边

大于等于中间节点记录放在右边

中间节点放在上一层叶节点

B+树的删除

填充因子=50%

叶节点和中间节点都大于填充因子

直接将记录从叶子节点删除，如果该节点是子节点，则用右节点替换

叶节点小于填充因子

合并叶节点和兄弟节点，同时更新子节点

叶节点和中间节点都小于填充因子

合并叶子节点和兄弟节点

更新子节点

合并子节点和兄弟节点

特点

每个中间节点不保存数据，只用来索引，具体数据保存在子节点中

叶子节点本身存在顺序，每个page之间用双向链表存储

所有中间节点元素都存在子节点中

通常有两个指针：1个指向根节点，1个指向最小叶子节点，针对这两种查找运算是随机的

区别

B+树

只有叶子结点会带有指向具体记录的指针

所有的叶子结点通过指针连接在一起

一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定

非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率

叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易

B-树

所有节点都会带有指向具体记录的指针

不同的叶子之间没有连在一起

可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定

索引原理

索引的原理很简单，就是把无序的数据变成有序的

创建了索引的列的内容进行排序

对排序结果生成倒排表

在倒排表内容上拼上数据地址链

在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

数据按照页来分开的，一页大约是16K

索引设计

出现在where子句中的列，或者连接子句中指定的列

散列效果较好的列

短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

最左前缀匹配原则

更新频繁字段不适合创建索引

定义有外键的数据列一定要建立索引

索引失效（模型数空运最快）

模–代表模糊查询

型–代表数据类型

数–代表函数

空–代表NULL

运–代表数值运算

最–代表最左原则

快–代表全表扫描最快

索引优缺点

优点

提高数据检索的效率，降低数据库的IO成本

创建唯一索引，可以保证数据库表中每一行数据的唯一性

有依赖关系的子表和父表联合查询时，可以提高查询速度

使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗

缺点

创建索引和维护索引要耗费时间

索引需要占磁盘空间

降低更新表的速度

回表

从非主键索引树搜索回到主键索引树搜索的过程称为：回表

覆盖索引

从非主键索引中就能查到的记录

索引下推

对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数

日志

重写日志：redolog

特征

基于磁盘的数据结构

记录的是数据库中每个页的修改

用来恢复提交后的数据页，并且只能恢复至最后一次提交的位置

用于碰撞恢复(crash recovery),保证mysql宕机不会影响持久性

使用WAL日志追加技术，先写日志再写磁盘

重做日志过程

客户端尝试修改数据时，Innodb会把记录下写在redo log中

再修改缓存池中数据，当事务提交时，调用fsync把redo log刷入磁盘

缓存池的数据何时刷入磁盘由后台线程异步处理

redo log的事务状态是prepare,也就是未真正提交

等bin log日志写入磁盘完成才变成commit,事务才算真正完成

当Mysql宕机后，只要重试解析redo log的更改记录进行重放、刷盘即可

重放(刷盘)过程

固定大小，循环写入

redo log写满后从头开始写，形成一个环状

write pos表示redo log当前记录的日志序列号(LSN)，写入还未刷盘的记录，循环往后递增

check point表示redo log中的修改记录已经刷新到磁盘后的LSN,这个LSN之前的数据已经全部落盘

write pos和check point之间的绿色部分表示空余部分，用来记录写的日志

check point到write pos之间是还未来得及刷盘的部分

擦除：write pos追上check point时，就需要推动check point前移，即进行刷盘空出位置进行记录新的日志

写入机制

通过innodb_flush_log_at_trx_commit进行设置写入策略

0：每秒提交 redo buffer -> os cache ->disk 可能丢失一秒内的事务数据

1(默认值):每次事务提交执行 redo buffer ->os cache ->disk 性能较差

每次事务提交 redo buffer->os cache ,然后由后台Master线程每隔1s进行刷盘

回滚日志：undolog

用来回滚到某一个版本，是一种逻辑日志

undo log记录的是修改之前的数据

作用

实现mvcc机制:undo log中保存了未提交之前版本数据，作为旧版本数据的快照以便其他事务进行读取

实现事务原子性，利用Undo log进行回滚

二进制日志：binlog

记录MySQL中增删改时的记录日志

show VARIABLES like '%log_bin%'查看是否开启binlog

二进制形式存储在磁盘中的逻辑日志

bin log采用追加的方式写入

默认情况下是关闭的

bin log和redo log 相辅相成，共同保证事务持久性

作用

进行主从复制

基于时间点恢复数据

通过订阅bin log可以做很多事情，比如同步上游数据、和redis配合实现延时双删等

relay 中继日志

用于主从复制

Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入

同步日志

sync_relay_log设置如何同步中继日志到中继日志文件

0时，则MySQL服务不会对中继日志文件进行同步操作，依赖于操作系统来定期进行同步

sync_relay_log = N(N>0)，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区
，每N个sync_relay_log事件后对中继日志文件执行一次同步(调用fdatasync())

错误日志：error log

记录MySQL在启动、关闭或者运行过程中的错误信息

show variables like "%log_error%" 查看错误日志的地址

默认开启

慢查询日志：slow query log

记录执行时间超过指定阈值的SQL语句

show variables like "%slow_query%"：查看慢查询日志是否开启以及日志的位置

默认情况下时关闭的，默认时间时10s

一般查询日志：general log

客户端连接信息以及执行的SQL语句信息

普通查询日志会记录增删改查的信息，因此一般是关闭的

锁机制

悲观锁

屏蔽一切可能违反数据完整性的操作

查询完数据的时候就把事务锁起来，直到提交事务

使用数据库中的锁机制

适用场景

多写的情况，冲突概率大

乐观锁

只在提交操作时检查是否违反数据完整性

乐观锁一般会使用版本号机制或CAS算法实现

适用场景

修改少读多场景，冲突发生概率小，节省锁的开销，增加系统吞吐量

表级锁

锁定粒度最大的一种锁，表示对当前操作的整张表加锁

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低

意向锁：表明事务稍后要进行那种类型的锁定

表共享读锁（共享锁）IS：打算在某行上设置共享锁

表独占写锁（排他锁）IX：打算在某行上设置排他锁

Insert 意向锁：Insert操作设置的间隙锁

LOCK_AUTO_INC：自增锁

为一个AUTO_INCREMENT列生成自增值前，必须先为该表加 AUTO_INC 表锁

为了提高并发插入的性能，自增锁不遵循二阶段锁协议，加锁释放锁不跟事务而跟语句走，insert开始时获取，结束时释放

自增值只要分配了就会+1，不管事务是否提交了都不会撤销，所以可能出现空洞

行级锁

最细的一种锁，表示只针对当前操作的行进行加锁

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高

共享锁

允许事务对一条行数据进行读取

排它锁

允许事务对一条行数据进行删除或更新

记录锁Record：始终锁定索引记录

间隙锁Gap：锁定一个范围

临键锁Next-key：记录锁+间隙锁的组合，可锁定表中不存在的记录

谓词锁Predicat：空间索引

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁

一次锁定相邻的一组记录

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

死锁

指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象

产生死锁的原因

系统资源不足

进程运行推进的顺序不合适

资源分配不当

四个必要条件

互斥条件：一个资源每次只能被一个进程使用

请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放

不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺

循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系

如何避免

破坏互斥条件：这个条件我们没有办法破坏，因为我们⽤锁本来就是想让他们互斥的（临界资源需要互斥访问）

破坏请求与保持条件：⼀次性申请所有的资源

破坏不剥夺条件：占⽤部分资源的线程进⼀步申请其他资源时，如果申请不到，可以主动释放它占有的资源

破坏循环等待条件：靠按序申请资源来预防。按某⼀顺序申请资源，释放资源则反序释放

MySQL避免死锁的方法

一次性锁定所有需要的资源

按照一致的顺序进行加锁

缩小锁冲突的范围

show engine innodb status:查看当前库中有哪些事务或者锁

数据库基础

范式

第一范式：不可拆分列

第二范式，每个表只有一个主键

第三范式：消灭传递依赖，每列和主键有关

BC范式：3NF基础上消除主键对于码的部分与传递函数依赖

适当的增加冗余字段，提高查询效率

常见数据库

关系型

mysql、oracle、pgSQl、sqlService

非关系型

redis、mongoDB、VoltDB、Hbase

sql语言

DQL：查询sql

DML：新增、修改删除的sql

DDL：对数据库表进行操作

查询相关命令

show full processlist 查看所有进程

show OPEN TABLES where In_use > 0 查看是否锁表

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;查看正在锁的事务

select * from performance.data_lock 查询所有的锁情况

存储引擎

MyISAM

存储格式

静态（固定长度）表

动态表

压缩表

特点

不支持事务，也不支持外键约束，只支持全文索引

访问速度快，对事务完整性没有要求

MyISAM 适合查询、插入为主的应用场景

表级锁定形式，数据在更新时锁定整个表

适用场景

不需要事务的支持

单方面读取或写入数据比较多的业务

使用读写并发访问相对较低的业务

数据修改相对较少的业务

对数据业务一致性要求不是非常高的业务

服务器硬件资源相对比较差

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

InnoDB

特点

支持事务，支持 4 个事务隔离级别

读写阻塞与事务隔离级别相关

能非常高效的缓存索引和数据

表与主键以簇的方式存储

支持分区、表空间，类似 oracle 数据库

支持外键约束，5.5 前不支持全文索引，5.5 后支持全文索引

行级锁定，但是全表扫描仍然会是表级锁定

适用场景

业务需要事务的支持

行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引来完成

业务数据更新较为频繁的场景

业务数据一致性要求较高

InnoDB 较好的缓存能力来提高内存利用率，减少磁盘 IO 的压

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

show engines;查看当前数据库引擎

修改存储引擎

alter table 修改

修改 /etc/my.cnf 配置文件

创建表时指定存储引擎

mysql优化

执行计划explain

id

SQL执行的顺序的标识

select_type

查询中每个select子句的类型

table

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

type

常用的类型有：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

possible_keys

MySQL能使用哪个索引在表中找到记录

Key：key

key列显示MySQL实际决定使用的索引

key_len

索引中使用的字节数

ref

上述表的连接匹配条件

rows

扫描行数

Extra

解决查询的详细信息

优化方向

设计上：字段类型，存储引擎，范式

功能上：索引，缓存，分库分表

架构上：集群，主从复制，负载均衡，读写分离

参数配置优化

连接请求

max_connections:最大连接数

set global.max_connections=1000修改全局最大连接数

wait_timeout：等待超时时间

interative_timeout：连接状态的超时时间

缓冲区变量

key_buffer_size:缓冲的key的大小

query_cache_size:查询结果缓存的大小（8.0去除了）

max_connect_error:连接允许的最大错误数量

sort_buffer_size:排序的缓冲区大小，默认1M

innodb变量

innondb_buffer_pool_size:内存的缓存大小，默认128M

sql常见问题

隐式转换

where条件注意数据类型，避免类型转换导致不走索引

合适的索引

涉及范围的查询的索引不一定生效：优化器优化

联合索引建立需要根据实际查询情况，避免最左前缀原则导致联合索引不生效（可能生效最前的索引）

避免冗余索引

写入优化

大批量的写入，prepared statement减少sql解析

multiple values /add batch 减少交互

load data 直接导入

索引和约束问题

数据更新

数据的范围更新

注意gap lock问题

导致锁范围扩大

查询优化

子查询优化

semi-join Materialization 是用于semi-join的一种特殊的子查询物化技术

Materialization/lookup

Materialization/scan

模糊查询

like的问题，后%可走索引

全文检索，solr/es 实现组合查询

连接查询

驱动表的选择问题

避免笛卡尔积

命中索引

查询where条件

避免null、not、not in 函数等

减少使用or ，使用union

force index 强行走索引

事务

事务特点

原子性（Atomicity）

要么都发生，要么都不发生

一致性（Consistency）

事务前后数据库的完整性约束没有被破坏

隔离性（Isolation）

每个事务都有各自的完整数据空间

持久性（Durability）

事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

事务问题

脏读

一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的

不可重复读

一个事务内两个相同的查询却返回了不同数据

幻读

A事务修改全表，B事务新增数据，A事务会出现幻读

丢失更新

A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

事务隔离级别

read uncommitted : 读取尚未提交的数据：不解决脏读

很少使用、不能保证一致性

read committed：读取已经提交的数据：可以解决脏读

每次查询设置和读取自己的新快照

只支持基于行的binlog

update 优化：半一致读

repeatable read：重读读取：可以解决脏读和不可重复读 —mysql默认的

使用事务第一次读取时创建快照

使用了MVVC技术

serializable：串行化：可以解决脏读不可重复读和虚读—相当于锁表

资源损耗最大

分库分表

原来存储在一个库表中的数据拆分到不同的库/表中。

分库分表原因

业务的不断发展和数据的不断递增，未来场景下可能出现瓶颈

分布式部署、单机服务性能瓶颈、数据处理能力瓶颈、数据存储瓶颈等

实施策略

垂直拆分——主要是字段的拆分

按照功能模块、关系密切程度将不同的业务表划分出来，部署到不同的库上

水平拆分——表结构不变，数据分表;

按照某种规则把一张表的数据拆分成多个结构相同的表

拆分规则

范围法

切分简单，根据uid，按照范围，很快能够定位到数据在哪个库上

扩容简单，如果容量不够，只要增加user-db2即可

uid必须要满足递增的特性

数据量不均，新增的user-db2，在初期的数据会比较少

请求量不均，一般来说，新注册的用户活跃度会比较高，故user-db1往往会比user-db0负载要高，导致服务器利用率不平衡

哈希法

切分策略简单，根据uid取模，根据取模结果很快能够定位到数据在哪个库上

数据量均衡，只要uid是均匀的，数据在各个库上的分布一定是均衡的

请求量均衡，只要uid是均匀的，负载在各个库上的分布一定是均衡的

扩容麻烦，如果容量不够，要增加一个库，重新hash可能会导致数据迁移，如何平滑的进行数据迁移，是一个需要解决的问题

城市/租户-有明显业务特征的分表

根据不同的业务特征进行拆分，按照不同的租户直接分离数据，数据独立

单个租户数据量快速增大后依旧是瓶颈

拆分带来的问题

垂直分库

跨库join的问题：全局表、字段冗余、数据同步、系统层组装

跨库事务（分布式事务）的问题：分布式事务

水平分库

分布式全局唯一ID：id生成规则不再依赖于数据库

分片字段该如何选择：片键，表中最频繁被使用，或者最重要的字段来作为分片字段

数据迁移，容量规划，扩容等问题：历史数据、扩容

跨分片的排序分页：业务处理

跨分片的函数处理：业务处理

跨分片join ：全局表、ER分片、内存计算

分库组件

Sharding-sphere

开源的分布式数据库中间件解决方案组成的生态圈

提供标准化的数据分片、读写分离、柔性事务和数据治理功能

Sharding-JDBC

定位为轻量级Java框架，在Java的JDBC层提供的额外服务

适用于任何基于JDBC的ORM框架

支持任何第三方的数据库连接池

支持任意实现JDBC规范的数据库

性能很好、支持跨数据库jdbc

增加了开发难度、不支持跨语言（java）

Sharding-Proxy

透明化的数据库代理端

向应用程序完全透明，可直接当做MySQL使用

适用于任何兼容MySQL协议的的客户端

sharding-ui，直接的管理sharding-proxy，在上面动态修改配置等信息

性能问题：作为数据库的代理层，使用java与阻塞式的驱动

mycat

开源的，面向企业应用开发的大数据库集群

拦截了SQL语句，做分片分析、路由分析、读写分离分析、缓存分析等执行真实数
据库，并将返回的结果做适当的处理，最终再返回给用户

建议线上使用双机热备环境

应用场景

单纯的读写分离，此时配置最为简单，支持读写分离，主从切换

分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片

多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化

报表系统，借助于Mycat的分表能力，处理大规模报表的统计

替代Hbase，分析大数据

海量数据实时查询的一种简单有效方案

Mycat的默认端口是:8066

主从同步

主从过程

主库执行DDL和DML操作，按照修改顺序以此写入bin log

从库的IO线程连接上主库并请求读取指定位置position的日志内容

主库收到请求后，将制定位置position之后的内容日志、主库bin log文件名称以及在日志中的位置推送给从库

从库IO线程收到数据后，将日志内容以此写入relay log文件最末端，并将bin log文件名和位置position记录到master-info文件中，以遍下次使用。

从库的sql线程检测到relay log中内容更新后，读取日志并解析成可执行的sql语句进行主从同步

为什么要主从同步

读写分离

数据备份

高用性

同步机制

半同步复制：解决主库数据丢失问题

主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库

从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库

主库接收到至少一个从库的 ack 之后才会认为写操作完成了,返回客户端

并行复制：解决主从同步延时问题

从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行

异步模式:默认模式

节点执行完客户端提交的事务后立即提交事务并返回给客户端

不关心 log dump 线程是否成功地将将此次事务写进 binglog 并且发送给从库