MySQL
2021-03-16 10:19:20 130 举报AI智能生成
MySQL内容梳理
作者其他创作
大纲/内容
数据结构
hash算法
hash函数,散列分布
复杂度O(1)
精确查找快, 不支持范围查找
二叉树查找树
定义
1. 左子树所有节点的值均小于他的根节点的值
2. 右子树所有节点的值均大于他的根节点的值
3. 子树也符合以上规则
缺点
数据不平衡
平衡二叉树
定义
1. 二叉查找树的定义
2. 左右两个子树的高度差的绝对值不超过1(子高平衡)
缺点
几乎每次插入/删除节点都会影响二叉树的平衡
红黑树
定义
1. 每个结点要么是红的，要么是黑的
2. 根结点是黑的
3. 每个叶结点，即空结点（NIL）是黑的
4. 如果一个结点是红的，那么它的俩个儿子都是黑的
5. 对每个结点，从该结点到其子孙结点的所有路径上包含相同数目的黑结点(黑高平衡)
B树(平衡多路查找树)
m阶的B树定义
1. 根节点至少有2个节点
2. 每个中间节点都包含k-1个元素和k个孩子, 其中 m/2 <= k <= m
3. 每个叶子节点都包含k-1个元素, 其中 m/2 <= k <= m
4. 所有叶子节点在同一层上
5. 每个节点的元素从小到大排列
优缺点
横向扩展, 不会增加深度
特点
1. m阶B树节点最多有m个子树, m-1个元素
2. m阶B树节点最少有m/2个子树, m/2 - 1 个元素
3. 数据即存在叶子节点, 也存在中间节点
为了磁盘或其它存储设备而设计的一种多叉平衡查找树, 多用于做文件系统的索引
因为文件系统和数据库一般都是存在电脑硬盘上的，
如果数据量太大的话不一定能一次性加载到内存中。
但是B树可以多路存储, 刚好可以对应数据存储的页.
如果数据量太大的话不一定能一次性加载到内存中。
但是B树可以多路存储, 刚好可以对应数据存储的页.
B+树
m阶的B+树定义
1. 根节点至少有2个子女
2. 每个中间节点都至少包含ceil(m / 2)个孩子，最多有m个孩子
3. 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
4. 所有的叶子结点都位于同一层
5. 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划
特点
1. m阶B树最多有m个子树, m-1个元素
2. 每个中间节点至少包含ceil(m/2)个子节点
3. 每个叶子节点都有左右2个指针，指向左右的下一个数据数据，形成一个有序的双向链表
4. 只有叶子节点才会有data，其他都是主键索引
优缺点
横向扩展, 不会增加深度
存储引擎
Innodb
数据格式
.frm存放表结构
idb存放数据和索引
聚簇索引
锁的粒度
InnoDB采用MVCC(多版本并发控制)来支持高并发，
InnoDB实现了四个隔离级别，默认级别是REPETABLE READ，
并通过间隙锁策略防止幻读的出现。它的锁粒度是行锁
InnoDB实现了四个隔离级别，默认级别是REPETABLE READ，
并通过间隙锁策略防止幻读的出现。它的锁粒度是行锁
事务
InnoDB是典型的事务型存储引擎，并且通过一些机制和工具，支持真正的热备份。
逻辑备份使用mysqldump
物理备份方法首选xtrabackup开源工具
页合并与分裂
MyISAM
存储形式
frm 描述表结构
MYD保存表数据
MYI存储表索引
基于非聚簇索引进行存储的
锁的粒度
MyISAM不支持行锁，所以读取时对表加上共享锁，
在写入是对表加上排他锁。由于是对整张表加锁，
相比InnoDB，在并发写入时效率很低。
在写入是对表加上排他锁。由于是对整张表加锁，
相比InnoDB，在并发写入时效率很低。
事务（不支持）
InnoDB和MyISAM的对比
1、由于锁粒度的不同，InnoDB比MyISAM支持更高的并发；
2、InnoDB为行级锁，MyISAM为表级锁，所以InnoDB相对于MyISAM来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁；
3、在备份容灾上，InnoDB支持在线热备，有很成熟的在线热备解决方案；
4、查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据；
5、SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要朱行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；
6、MyISAM的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而InnoDB的表数据文件为:.ibd和.frm(表结构定义)；
数据库设计
逻辑设计
范式设计
第一范式
数据表的每一列都要保持它的原子特性，也就是列不能再被分割。
第二范式(2NF)
属性必须完全依赖于主键
第三范式(3NF)
所有的非主属性不依赖于其他的非主属性
反范式设计
优点：查询时可以减少表的关联；可以更好的进行索引优化；
缺点：存在数据冗余以及数据维护异常；对数据的修改需要更多的成本
物理设计
命名规范
1、库名、表名、字段名必须使用小写字母，并采用下划线分割。
2、库名、表名、字段名禁止超过32个字符。
3、库名、表名、字段名禁止使用MySQL保留字。
存储引擎选择
1、使用场景是否需要事务支持；
2、是否需要支持高并发，InnoDB的并发度远高于MyISAM；
3、是否需要支持外键；
4、是否需要支持在线热备
5、高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；
6、索引，不同存储引擎的索引并不太一样
数据类型选择
INT
TINYINT
SMALLINT
MEDIUMINT
BIGINT
FLOAT(M,D)
DOUBLE(M,D)
DECIMAL(M,D)
DATETIME
TIMESTAMP
CHAR(M)
VARCHAR(M)
BLOB 或 TEXT
ENUM
MySql架构与存储引擎
Mysql逻辑架构介绍
1.连接层
2.服务层
Management Serveices & Utilities： 系统管理和控制工具
SQL Interface: SQL接口
Parser: 解析器
Optimizer: 查询优化器。
Cache和Buffer： 查询缓存。
3.引擎层
4.存储层
存储引擎
Innodb
MyISAM
Archive
Memory
Federated
BLACKHOLE
mysql的查询流程
1. mysql客户端通过协议与mysql服务器建连接
2. 发送查询语句，先检查查询缓存，如果命中(一模一样的sql才能命中)，直接返回结果，否则进行语句解析
3. 首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。
4. mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
5. 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。
6. 存储引擎执行计划
锁
什么是锁
协调多个进程或线程并发访问某一资源的机制
锁的种类
粒度划分
全局锁
表级锁
表锁
元数据锁（meta data lock，MDL）
1. MySQL5.5版本引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；
当要对表做结构变更操作的时候，加MDL写锁
当要对表做结构变更操作的时候，加MDL写锁
2. 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
3.读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，
如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
4. 事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会
等到整个事务提交后再释放
等到整个事务提交后再释放
行锁
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。
由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而
提高一些需要高并发应用系统的整体性能。
由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而
提高一些需要高并发应用系统的整体性能。
使用功能
共享锁
共享锁的代号是S，是Share的缩写，共享锁的锁粒度是行或者元组（多个行）。一个事务获取了共享锁之后，可以对锁定范围内的数据执行读操作。
排它锁
排它锁的代号是X，是eXclusive的缩写，排它锁的粒度与共享锁相同，也是行或者元组。一个事务获取了排它锁之后，可以对锁定范围内的数据执行写操作。
思想划分
乐观锁
1. 查询出商品信息
select (status,status,version) from t_goods where id=#{id}
select (status,status,version) from t_goods where id=#{id}
2. 修改商品status为2
update t_goods set status=2,version=version+1
where id=#{id} and version=#{version};
update t_goods set status=2,version=version+1
where id=#{id} and version=#{version};
悲观锁
共享锁
共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁
排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
事务
ACID属性
原子性(Atomicity)
事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
一致性(Consistent)
在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,
以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的
隔离性(Isolation)
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。
这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
持久性(Durable)
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持
并发事务处理带来的问题
更新丢失（Lost Update）
两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，
就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
脏读（Dirty Reads）
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。
此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
不可重读（Non-Repeatable Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，
却发现其读出的数据已经发生了改变、或某些记录已经被删除了！
这种现象就叫做“不可重复读”。一句话：事务A读取到了事务B已
经提交的修改数据，不符合隔离性
却发现其读出的数据已经发生了改变、或某些记录已经被删除了！
这种现象就叫做“不可重复读”。一句话：事务A读取到了事务B已
经提交的修改数据，不符合隔离性
幻读（Phantom Reads）
个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，
这种现象就称为“幻读”
脏读是事务B里面修改了数据,幻读是事务B里面新增了数据
事务隔离级别
分类
读未提交(Read uncommitted)
脏读/不可重复读/幻读
读已提交(Read committed)
不可重复读/幻读
可串行化(Serializable)
脏读/不可重复读/幻读都不可能
mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，
这种隔离级别并发性极低，开发中很少会用到
可重复读(Repeatable read)
幻读
可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）
insert、update和delete会更新版本号，是当前读（当前版本）
要避免幻读可以用间隙锁在Session _1下面执行update account set name ='zhuge'
where id> 10 and id<= 20;，则其他Session没法插入这个范围内的数据
where id> 10 and id<= 20;，则其他Session没法插入这个范围内的数据
查看隔离级别
show variables like 'transaction_isolation';
select @@transaction_isolation;
默认的事务隔离级别(Repeatable read)
MVCC(多版本并发控制)
假设同一份数据，既有读事务访问，又有写事务操作，实际上，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本，直到写事务提交，读事务才会访问到这个新的数据版本
实现方式
数据记录的多个版本同时保存在数据库
使用undo_log动态构造(mysql的innodb使用该实现)
实现原理
在每一行有隐藏列，当前行的创建事务id，删除事务id, 上一个版本指针(undo_log, 同一条记录可能会存在多个版本, 呈链表结构)
读取每一行的时候
判断记录是否被修改
当前行的创建事务id > 当前事务id, 说明记录在事务开启前已被修改了, 需要从undo_log回滚
判断记录是否被删除
当前行的删除事务id < 当前事务id, 说明记录在事务开启前已被删除, 则过滤
日志
错误日志
mysql执行过程中的错误信息
mysql执行过程中的告警信息
event scheduler运行时所产生信息
mysq启动和停止过程中产生的信息
主从复制结构中，重从服务器IO复杂线程的启动信息
show variables like 'log_error';
查询日志
查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。
在MySQL配置文件中设置
long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中
log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file
在MySQL配置文件中设置
long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中
log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file
一般查询
默认是关闭的
般查询日志查询的不止是select语句，几乎所有的语句都会记录
慢查询
询获取锁(包括锁等待)的时间不计入查询时间内
慢查询日志的工具mysqldumpslow
慢查询分析
开启慢查询日志，捕获慢SQL
SHOW VARIABLES LIKE '%slow_query_log%';
启用慢查询日志
set @@global.slow_query_log=on;
set @@global.slow_query_log=on;
explain+慢SQL分析
缺点
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
show profile查询SQL语句在服务器中的执行细节和生命周期
Show Profile是MySQL提供可以用来分析当前会话
中语句执行的资源消耗情况，可以用于SQL的调优测量
默认关闭，并保存最近15次的运行结果
中语句执行的资源消耗情况，可以用于SQL的调优测量
默认关闭，并保存最近15次的运行结果
SQL数据库服务器参数调优
当order by 和 group by无法使用索引时，增大max_length_for_sort_data
参数设置和增大sort_buffer_size参数的设置
参数设置和增大sort_buffer_size参数的设置
二进制日志
二进制日志是一个二进制文件，记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、
操作数据等其他额外信息，但是他不记录SELECT、SHOW等那些不改变数据库的SQL语句。二进制日志主要用于数据
库恢复和主从复制，以及审计操作。
操作数据等其他额外信息，但是他不记录SELECT、SHOW等那些不改变数据库的SQL语句。二进制日志主要用于数据
库恢复和主从复制，以及审计操作。
三种记录方式
STATEMENT
ROW
MIXED
0 条评论
下一页
