Mysql思维导图
2020-05-07 19:21:38 0 举报AI智能生成
mysql思维导图
作者其他创作
大纲/内容
Mysql
组件
客户端
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默认页大小为16kbshow 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查询存在多个条件查询的时候，我们需要对查询的列创建组合索引
最左匹配原则
创建了一个组合索引（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` )
使用
锁
保证数据并发访问的一致性、有效性
锁分类
表级锁
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
表共享读锁（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上的锁
分区表
应用场景
表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
分区表的数据更容易维护
批量删除大量数据可以使用清除整个分区的方式
对一个独立分区进行优化、检查、修复等操作
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
可以使用分区表来避免某些特殊的瓶颈
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线程
复制延迟
由于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）并行复制功能
0 条评论
回复 删除
下一页
