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

组件

客户端

cli、jdbc、navicat、sqlyog等

服务端

连接器

控制用户的连接

分析器

词法分析、语法分析  AST

Calcite

优化器

优化sql语句，规定执行流程

CBO基于成本优化 RBO基于规则优化

执行器

sql语句的实际执行组件

缓存

mysql8已经移除该模块

存储引擎

查看

show engines

常用分类

InnoDB

存储位置

磁盘

存储格式

.frm .ibd

支持事务、行级锁、主键

MyISAM

存储位置

磁盘

存储格式

.frm .MYD .MYI

不支持事务

Memory

存储位置

内存 hash

事务（ACID）

原子性（Atomicity）

Undo log

描述

逻辑日志，每行操作会记录一条日志，存在于数据库的undo log segment（段）中， undo log也会产生redo log，因为undo log也要实现持久性保护 分为： insert undo log（insert只对本身事务可见，对其他事务无影响） update undo log（update/delete）

作用

回滚

回滚行记录到某个版本

MVCC

Multiple version concurrent control多版本并发控制， 实现非锁定一致性读取：读取一行记录时，若已被其他事务占据， 则通过undo读取之前的版本

InnoDB向数据库中存储的每一行数据添加三个字段 DB_TRX_ID：表示插入或更新行的最后一个事务的事务标识符，删除也是更新 DB_ROLL_PTR：回滚指针指向写入回滚段的撤消日志（Undo Log） DB_ROW_ID：随着新行插入而单调增加的行ID

一致性（Consistency）

隔离性（Isolation）

隔离级别

读未提交（read uncommitted）

脏读、不可重复读、幻读

读已提交（read committed）

不可重复读、幻读

可重复读（repeatable read）

幻读

串行化（serializable）

脏读

在两个事务中，第一个事务中更新了某条数据还未提交，在另一个事务中能读取到新的值

不可重复读

在两个事务中，第一个事务中更新了某条数据并以提交，在另一个事务中读取到两种状态， 如果第一个事务还未提交，则为老数据 如果第一个事务已提交，则为新数据 应该需要保证在同一个事务中读到的数据都是一样的

幻读

在两个事务中，第一个事务插入了一条新的数据并以提交 在另一个事务中是无法查询到该条数据，但是在进行insert插入 时候，会出现主键重复的现象

命令

设置事务级别： set session transaction isolaction level read uncommitted; 开启事务： begin; commit;

持久性（Durability）

Redo log

描述

物理格式的日志，记录数据库中每个页的修改 包含两部分： 1、内存中的日志缓冲（redo log buffer） 2、磁盘上的重做日志文件(redo log file) 在一个事务中每一次sql操作都会写一个redo log至内存的buffer中 事务提交时必须先将该事务所有的redo log调用fsync至redo log file中

innodb默认页大小为16kb show variables like '%innodb_page_size%'

innodb_flush_log_at_trx_commit控制redo log的写入策略 show variables like '%innodb_flush_log_at_trx_commit%' 0：redo log写入内存buffer中，由master thread每秒调用一次fsync()刷写至磁盘 1：默认值，每次提交都会调用fsync()刷写至磁盘 2：每次提交写到os buffer中，每秒一次调用fsync()刷写至磁盘

作用

当系统崩溃时，虽然数据没有持久化，但是redo log持久化了， 系统可以根据redo log的内容，将所有数据恢复至最新的状态

索引

数据结构

hash

优点： 速度快，memory存储引擎使用的该索引

缺点： 必须要将所有的数据加载到内存中，比较耗费内存空间 只能做等值查询，如果是范围查询则不太适合

b+树

非叶子节点不存储data，只有叶子节点存储data

索引类型

聚簇索引/非聚簇索引

聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值存储在一起 非聚簇索引：数据和索引分开存储，叶子节点只有索引，索引指向数据行通过辅助键检索无需访问主键的索引树

主键索引

描述

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引 Innodb默认将创建表定义的主键设置为主键索引，如果没有主键会选择一个唯一的非空索引代替 如果没有这样的索引，InnoDB 会隐式定义一个主键6个字节row_id来作为聚簇索引

创建语句

1、ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2、创建表的时候指定主键即可

辅助索引 二级索引 普通索引

创建语句

1、ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 2、CREATE INDEX index_name ON table_name (column_name)

回表

辅助索引的叶子节点中不是存放的实际数据，而是存放的主键， 再通过主键索引查询对应的记录称为回表 例：表tbl中字段id、name，id为主键、name设置了辅助索引 select * from tbl where name='zhangsan';

通过explain查看sql执行计划： using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表

索引覆盖

根据辅助索引查询的时候，如果查询字段刚好是辅助索引叶子节点的数据 例：表tbl中字段id、name，id为主键、name设置了辅助索引 select id from tbl where name='zhangsan';

组合索引

描述

当where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引

创建语句

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

最左匹配原则

创建了一个组合索引（a，b，c），相当于创建了三个索引（a），（a，b），（a，b，c） select * from tbl where a=1 and b=2 and c=2; 使用索引（a，b，c） select * from tbl where a=1 and b=2; 使用索引（a，b） select * from tbl where a=1 and c=2; 使用索引（a） select * from tbl where b=2 and c=2; 不使用索引 select * from tbl where c=2; 不使用索引 like关键词：like '%aaa%' 不使用索引，like 'aaa%'使用索引

索引下推

当查询字段不是或不全是组合索引字段，查询条件是多条件且where/order by字段全是组合索引字段 index_condition_pushdown（ISP）（mysql 5.6+包括5.6 提供该功能）

默认开启的，通过下面配置控制索引下推开关 SET optimizer_switch = 'index_condition_pushdown=off';

唯一索引

描述

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一

创建语句

1、ALTER TABLE `table_name` ADD UNIQUE [indexName] (`column`) 2、CREATE UNIQUE INDEX index_name ON table_name (column_name)

全文索引

描述

FULLTEXT索引用于全文搜索。 只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列 5.6以前只有MyISAM支持，5.6及5.6+两者都支持

创建语句

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

使用

select * from table_name where match(col1,col2) against('xxx xxx'); col1、col2是创建全文索引基于的列，这里match必须和创建的全文索引保持一致

锁

作用

保证数据并发访问的一致性、有效性

锁分类

表级锁

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

MyISAM

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

在查询时，会对查询语句中所有的表自动加上读锁； 在更新时，会对更新语句中所有的表自动加上写锁

show status like 'table%'; 如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况

行级锁

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

Innodb

通过在索引上加锁实现的，与Oracle不同，Oracle是通过在数据块中对相应数据行加锁来实现的 所以在Innodb中，只有通过索引检索数据时，才会用到行锁，否则只会用到表锁

show status like 'innodb_row_lock%'; 如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

共享锁（读锁）

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁， 则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。 这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改

排他锁（写锁）

允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。 若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁

update,delete,insert都会自动给涉及到的数据加上排他锁 select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句 所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据， 但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

间隙锁

定义

根据检索条件向左寻找最靠近检索条件的记录值A作为左区间， 向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）

使用

session 1: start transaction ; select * from news where number=4 for update ; session 2: start transaction ; insert into news value(2,4);#（阻塞） insert into news value(2,2);#（阻塞） insert into news value(4,4);#（阻塞） insert into news value(4,5);#（阻塞） insert into news value(7,5);#（执行成功） insert into news value(9,5);#（执行成功） insert into news value(11,5);#（执行成功）

next-key锁

定义

记录锁和间隙锁

分区表

应用场景

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据

分区表的数据更容易维护

批量删除大量数据可以使用清除整个分区的方式

对一个独立分区进行优化、检查、修复等操作

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

可以使用分区表来避免某些特殊的瓶颈

innodb的单个索引的互斥访问

ext3文件系统的inode锁竞争

可以备份和恢复独立的分区

分区表限制

一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区

在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表无法使用外键约束

分区表类型

范围分区（Range）

只能使用列名，不支持表达式，可以使用一个或者多个列

列表分区（List）

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

列分区（Column）

mysql从5.5开始支持column分区，可以认为i是range和list的升级版， 在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

Hash分区（Hash）

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。 这个函数可以包含myql中有效的、产生非负整数值的任何表达式

Key分区

类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

子分区

在分区的基础之上，再进行分区后存储

主从复制

描述

数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点， 默认采用异步复制方式

原理

master将对数据的改变记录成二进制binlog日志，slave定期对master的二进制日志进行探测是否变化，如果变化了则拉区日志文件

工作方式

slave库会创建两个线程：I/O线程，SQL线程

I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中

主库会生成一个log dump线程,用来给从库I/O线程传binlog

SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行

复制延迟

由于mysql的主从复制时单线程操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高， slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多， 另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度， 或者当slave中有大型query语句产生了锁等待，那么延时就产生了

mysql 5.7 支持multi-threaded slave（MTS）并行复制功能

binlog

模式

statement

描述

基于SQL语句的复制(statement-based replication, SBR)， 每一条会修改数据的sql语句会记录到binlog中

优点

不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少， 减少了磁盘IO，提高性能

缺点

在某些情况下会导致master-slave中的数据不一致 (如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

row

描述

基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息， 仅需记录哪条数据被修改了，修改成了什么样子了

优点

不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题

缺点

会产生大量的日志，尤其是alter table的时候会让日志暴涨

mixed

描述

混合模式复制(mixed-based replication, MBR) 一般的复制使用STATEMENT模式保存binlog， 对于STATEMENT模式无法复制的操作使用ROW模式保存binlog， MySQL会根据执行的SQL语句选择日志保存方式

Master-Slave

1、在Master节点上配置 server_id=1 binlog-do-db=test binlog_format=mixed log-bin=/var/log/mysql/mysql-bin

2、创建用于复制的用户 CREATE USER 'repl'@'%' GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '123';

4、在Slave节点配置 server_id=2 binlog-do-db=test binlog_format=mixed log-bin=/var/log/mysql/mysql-bin

5、执行命令 change master to \ master_host="node01", \ master_user="repl",  \ master_password="123", \ master_log_file="mysql-bin.000002",  \ master_log_pos=120;

6、启动start slave      停止stop slave

问题

Could not execute Delete_rows event on table test.t_user; Can't find record in 't_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 761 stop slave; set global sql_slave_skip_counter=1; start slave;