mysql
2021-04-23 10:22:33 19 举报AI智能生成
mysql学习详解
作者其他创作
大纲/内容
数据库三大范式
每个列不可再拆分
非主键完全依赖主键，不能依赖主键的一部分
非主键只依赖于主键，不依赖于其他非主键
什么是索引
表中一列或多列的值进行排序的一种数据结构
索引有几种类型
聚簇索引
聚集索引，也叫主键索引，建在主键上，叶子节点中有行数据记录
辅助索引，也叫二级索引，建立在普通列上，叶节点存放列值和对应的主键值
非聚簇索引
非聚簇索引，就是叶子节点上存放的是索引和索引对应数据的地址，主键索引和辅助索引都是这样
myISAM引擎和Innodb引擎的区别
事务，innodb支持事务，myIsam不支持
innodb是聚簇索引，myisam是非聚簇索引
myisam只支持表锁，innodb支持更细粒度的行锁
myisam更多用于报表需求，所以在业务功能上支持更少
聚簇索引和非聚簇索引的区别
聚簇索引主键索引查找时更快，因为叶节点有行数据，少一次寻址IO
只查主键时，可以利用覆盖索引，直接拿到主键的值
聚簇索引插入时要判断主键不重复，插入开销更大
聚簇索引在行移动和页分裂时，因为存的是主键值，不用修改辅助索引，减少了维护成本
二级索引查找时，非聚簇索引会更快，因为聚簇索引的二级索引查找一般要回表
什么是覆盖索引
辅助索引中能查找的内容，不需要回表查找
对于某些统计问题，优先走辅助索引，辅助索引比较小，减少IO
什么是最左匹配
建联合索引时，要把最常使用的列放到最左边，是最左前缀规则
最左匹配是指在联合索引中，会按索引顺序找到能匹配到查询语句中的最左前缀
最左匹配对like查询同样适用，在索引排序的过程中左边字符处于高位
关于组合索引遇到范围查询以后，后面的字段就用不到了，如（a,b,c,d）遇到a = x and b= x and c > x and d= x，d这个字段不生效，不过也可以理解，到c找出的内容已经没法用到d这个索引了，只能遍历了
事务的ACID特性
A原子性
要么都做，要么都不做，带回滚
C一致性
数据库从一种状态变成下一种一致的状态
事务的开始之前和结束之后，完整性约束没有被破坏
I隔离性
每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见
D持久性
事务一旦提交，其结果就是永久性的
什么是脏读、幻读、不可重复读
不可重复读
一个事务内多次访问同一数据集合，由于其它事务进行了DML操作，会出现两次读到的数据不一致的情况
脏读
读到事务中未提交的数据修改，这个修改不一定被提交，所以叫脏读
幻读
幻读，是指在同一事务下，连续两次执行同样的sql，返回不同的结果，第二次会返回之前不存在的行
隔离级别和锁的关系
数据库的乐观锁和悲观锁怎么实现
超大分页怎么处理
固定长度的字段用什么字段处理
主从复制的作用
什么是索引覆盖
指定sql运行索引的两种方式
索引覆盖减少回表
长字符串、前缀字符串区分度低情况下加索引
索引函数操作，破坏索引值有序性问题
mysql binlog的三种录入方式
分布式事务的实现
什么是MVCC
Multiversion Concurrency Control多版本并发控制
每行记录可能有多个版本
MVCC 的实现方式为：每一行记录都有两个隐藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果没有主键，则还会多一个隐藏的主键列）
DATA_TRX_ID 最近更新这条行记录的事务 ID
DATA_ROLL_PTR 表示指向该行回滚段（rollback segment）的指针
InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织
开启事务后，对行进行update的操作，该行原本的值拷贝到 undo log，新行的ptr指向undo log里面刚copy的记录
insert 就是生成一条新的undo log记录
readView生成的时机
RR模式下，每个事务第一个select语句执行开始，会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView
RC模式下，每个事务的每个select语句执行开始，会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView
只有不加锁的读操作才开启并走一致性视图，所以可重复读模式下，另一个事务插入数据时，select读不到新插入数据，但是update操作却可以操作新插入数据，update操作后，这条行记录的事务id会变成当前事务id,重新变得可读
记录可见性判断
开启readView，会记录下当前活跃事务最小的事务id（只有当前事务活跃时，这个和最大事务id一样）,当前未分配的事务id(即最大事务id)
事务id集合里会记录下当前活跃的事务id,不包括自身
记录的trx_id小于最小事务id，说明在开启readView前提交，可见
记录的trx_id大于等于最大事务id,说明开启readView后才提交，不可见
记录的trx_id在最大和最小之间，如果不在活跃事务id集合内，可见，在活跃事务id集合内，不可见
事务id的生成时机
通过表information_schema.INNODB_TRX，可以观察到，begin,不会生成事务id,对数据表的操作才会
不开启事务情况下的DML操作
这一块网上没找到资料，都是写开启事务之后
自己实践，一个会话开启事务后，对所有行加共享锁，另一个会话不开启事务，直接写入数据，会发现这个会话在对将要插入的记录行加排它锁
这个说明不开启事务的情况下，修改记录一样会加锁，这个锁会自己加锁执行完解锁，不用用户干扰
事务实现回滚的具体过程
什么是mysql
关系型数据库管理系统
mysql有哪些数据类型
整数类型
小数类型
字符串类型
字符超过指定长度，是会出现插入错误的
枚举类型
日期和时间类型
timstamp效率高于dateTime
mysql有哪些存储引擎
InnoDB
MyIsam
其它
索引的数据结构有哪3种
B+树
适合范围查找和顺序查找
叶节点双向链表连接
hash
判等查找很快，hash结构，一般用redis更多
全文索引
全文倒排索引，分词后，建立词语和文章id、出现位置的对应关系，一般都用es存了
索引为什么使用B+树而不是B树
平衡二叉树
左右子树高度相差不超过1的二叉查找树，左子树小于根节点，右子树大于根节点
缺点在于二叉树的树高过高，单次查询磁盘IO次数很多
B树
也叫平衡多路查找树，每个节点可以有多个分叉，利用磁盘每次可以读出4KB的特点，在一个节点内尽可能多存放数据
通过单个节点存放多个key数据，生产环境树高一般不超过3层
B+树
和B树相比，非叶子节点不再保存行记录的指针，增加每个叶子节点能保存的关键字数
叶子节点双向链表连接，便于进行遍历，并且天然排序
建了索引，sql查询一定会走吗
全表扫描执行更快时，不会走索引，一般用于范围查找和联表查询
查询优化器
计算全表扫描代价
计算使用能使用不同索引的代价
找出其中成本最低的一个
查询量小时，一般会选择走辅助索引，回表开销没那么大，查询范围很大时，利用聚集索引的顺序读特性，速度更快
联合索引中的顺序问题
多列上建立的索引
将最常使用的列放到左边，联合索引遵守最左前缀匹配规则
联合索引除了利用联合有序性加快查找外，还可以利用有序进行order by
什么是数据库事务
把数据库从一种一致性状态修改到另一种一致性状态，要么都保存，要么都不保存
数据库的4种隔离级别和默认级别
read uncommited 允许读未提交的数据变更 会导致脏读、不可重复读、幻读
read commited 允许读取并发事务已提交的数据，可以避免脏读，还会有幻读和不可重复读
repeatable read 可重复读，mysql默认隔离级别
对同一个字段的读取，多次结果是一致的，除非是本事务进行的修改， 可以避免脏读、不可重复读，避免不了幻读
一个会话开启事务后，外部数据的update对查询没影响，这个是在读快照的感觉
外部数据的insert事务内不可见，但是事务内进行全量数据修改以后，就可以看到新增的记录，这就是幻读
两个会话都开启事务的情况下，指定主键去插入或者更新全量数据，都会被锁住
serializable可串行化
所有事务串行执行，满足ACID所有特性，可以防止幻读
一个会话开启事务的情况下，另一个会话没办法写入数据或修改数据，会卡到超时，所以不会有幻读
两个会话都开启事务的情况下，同时写入数据会有死锁出现，不过第一个写入的还是能成功
按粒度划分数据库的锁有哪几种
行锁
开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
mysql行锁要依赖索引，索引不生效的情况下，会锁住整张表
页锁
开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
表锁
开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
按锁定程度区分锁
共享锁 S锁
允许事务读一行数据，共享锁之间不互斥
手动加锁方式 select..lock in share mode
排它锁 X锁
允许事务删除或更新一行数据，排它锁和其它锁都互斥
手动加锁方式select....for update
意向共享锁 IS锁
事务想要获取某几行数据的共享锁
意向排它锁 IX锁
事务想要获得表中几行的排它锁
一致性非锁定读
如果读取的行正在执行update或delete操作，读取不会等待锁释放，而是读取行的快照数据
read commited级别下，读最新版快照
read repeat级别下，读事务开始时的行数据
主键自增长锁
lock mode 0
锁定单行，锁定索引，没索引的情况锁定主键
表锁的自增长锁方式，通过max(id) for update加锁，插入完数据就释放
lock mode 1
对于确定插入行数的语句，使用互斥量对计数器进行累加，不确定行数，使用自增长锁 默认形式
lock_mode 2
都采用互斥量的方式对计数器进行累加
操作锁时定位问题的3张表
innodb_trx
事务表
innodb_locks
当前存在的锁
innodb_lock_waits
锁等待情况
innodb引擎行锁的3种算法
record lock 单个行记录上的锁
gap lock 间隙锁
锁定一个范围，不包括行本身
使用age > 32 and age < 36这种条件，加的是间隙锁，不会锁定32和36
next-key lock 间隙锁+行锁
锁定一个范围，并锁定记录本身，在两个索引间左开右闭的方式进行锁定
查询索引有唯一索引时，锁降级为行锁，只锁行
对age=6加锁后，不仅会锁定（5,6]这个区间，还会再加一个gap锁，锁定（6,9）
使用age<36这种条件，加的是next-key锁，36会被锁定
innodb解决幻读
在查找的时候加上for update（或者lock in share mode），就会对查询范围加上next-key lock,这种情况下，范围内的插入都是不允许的，这样也不会有幻读了。
当然这个查找是要命中索引的，不然行锁就升级成表锁了
什么是死锁，怎么避免和解决
死锁是指两个及以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象
超时是最简单的方法，利用FIFO顺序对超时事务进行回滚
等待图进行死锁检测，事务之间互相等待资源的图
T1指向T2的边代表T1等待T2占用的资源
判断图中是否存在回路，存在回路说明存在死锁
存在死锁的情况下，回滚undo量最小的事务
检测算法为深度优先遍历
死锁的概率
概率很低，具体公式不贴了，就是事务数n乘以操作数r除以数据行数R的感觉
事务越多，每个事务操作的数量越多，数据行数越小，发生死锁的概率越小
死锁的示例
经典AB锁-会话A 锁住a资源，会话B锁住b资源，会话a请求对b资源加锁，会等待，会话b请求对资源a加锁，形成闭环死锁，会话b会进行回滚，会话a拿到b资源
当前事务持有待插入记录的X锁，等待队列里有一个S锁请求，这时在当前事务内插入在s锁请求范围的数据，由于S锁要重新获取新插入记录的锁，不太合理，会选择死锁，回滚undo log大的事务
主键使用自增id还是uuid
自增id
优点：存储小、查找效率高，插入有序，不会频繁改变磁盘写入位置
缺点：分布式存储的表合并操作复杂，容易被猜到下一个key，大量插入时，自增id的生成加锁影响并发
uuid
优点：适合大量数据的插入和更新操作,尤其是在高并发和分布式环境下
缺点：占用空间大，作为聚簇索引时，无序性会降低写入效率，引起之前的页分裂
生产方法：时间戳+机器id(本机ip)+序列号
锁升级
锁升级是指将当前锁粒度降低，即行锁变成页锁或表锁
innodb不存在，microsoft sql 存在
innodb根据页进行加锁，且采用位图的方式，占用内存资源很小
字段为什么定义为not null
主从复制的实现方法有几种
什么是索引下推
sql查询缓存问题
索引下推减少回表
内存数据flush到磁盘的时机
redo log
redo log buffer 重做日志缓冲
每次将重做日志缓冲写入重做日志文件，需要一次fsync操作
重做日志缓冲先写入文件系统缓存，为了确保重做日志写入磁盘，必须进行一次fsync操作
innodb_flush_log_trx_commit
0 事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread 每1秒会进行一次重做日志的fsync操作
1 事务提交时必须调用一次fsync操作，保持事务持久性 效率最低
2 事务提交时，只将重做日志写入文件系统缓存，不进行fsync操作，这种情况下，数据库宕机不会丢数据，操作系统宕机会丢数据
redo log file 重做日志文件
保证事务的持久性
物理格式日志，记录的是对每个页的修改
与bin log的区别为，binlog是逻辑日志，记录是对应的sql日志
undo log
事务回滚需要，当需要回滚时，可以利用这些undo信息将数据回滚到修改之前的样子
undo存放在数据库内部的特殊段中，被称为undo段，位于共享表空间内
逻辑日志，做和之前相反的工作，对于每个insert,都有一个delete
mvcc通过undo log完成，当前事务可以通过undo读取之前的行版本信息，已实现非锁定读
undo log 也会产生redo log ，借此完成持久化
explain命令参数含义
id，查询序号，不重要
selectType，查询类型
simple,简单查询
primary,嵌套查询时外层查询
union,联合查询
.........
table 查找表
type连接类型
const 匹配唯一行，主键或唯一键
eq_ref 它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY
ref 和上面那个类似，能命中索引值，是多行的情况
ref_or_null 和上面那个类似，可以处理包含null值的行
index_merge 索引合并优化，说明现有索引不能很好满足需求
unique_subquery
index_subquery
range 给定范围内的检索，使用一个索引来检查行
index 扫描索引树，比ALL来得更快
all 扫描磁盘
possible_keys 哪些索引能在表中找到行
keys 使用到的索引
key_len 使用到的索引长度
ref ref列显示使用哪个列或常数与key一起从表中选择行
rows 执行sql查询的行数
extra 查询详细信息
using index 是否使用索引
using filesort 进行了内存排序操作，一般出现在where查找出顺序和order by字段不一致的情况
using temporary 使用临时表进行查询，一般用于联表查询时，查询非驱动表上的字段导致
Using Index Condition 索引下推，用了where条件中的其它字段对辅助索引查找结果进行了过滤，减少了回表次数
......
0 条评论
下一页
