mysql
2017-12-24 09:46:22 269 举报AI智能生成
mysql 基础
作者其他创作
大纲/内容
事务的实现
相关概念
redo log
binlog
主要用于主从复制
undo log 存在表空间中，会有purge线程清理
rollback segment
锁
隔离级别
事务执行过程
用排他锁锁定该行
记录 undo/redo log，确保日志刷到磁盘上持久存储。
更新数据记录，缓存操作并异步刷盘。
将事务日志持久化到 binlog。
回滚事务：根据当前回滚指针从undo log中找出事务修改前的版本，并恢复
提交事务，在 redo log 中写入commit记录。
释放锁资源
异步清理undo段信息
清理保存点列表
恢复
通过 redo log 来重做事务或通过 undo log 来回滚
以binlog 来确定是提交还是回滚
行锁实现
快照读
snapshot read
定义：读取记录的可见版本（可能是历史版本），不加锁
表现
普通 select
当前读
current read
定义：读取记录最新版本，会加锁保证不并发修改
表现
特殊查询 select .. where ？ for update 加S锁
select .. where ？ lock in share mode 加x锁
insert，update，delete等 DML操作 加x锁
特点
针对当前读，innoDB交互是一条一条的进行，加锁也是一条一条
过程
发送一个update语句
使用当前读取出第一条并加锁
mysql 收到会再发一个update请求，更新这条记录
完成后再读下一条重复上述过程
两阶段锁实现
2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁
过程
锁机制
锁信息查看
show status like 'innodb_row_lock%';
无锁
普通select 不加任何锁，因此行锁无阻塞，但page锁和表锁会阻塞
共享锁（读写锁，S锁）
共享读，ix，x锁会阻塞
select * from table where ？ lock in share mode
当前读加排他锁（写锁，x锁）
排他锁即其他任何锁都会阻塞
select * from table where ？ for update
insert，update，delete
gap间隙锁
范围条件的行锁会有
或=条件但数据不存在
意向共享锁
意向排他锁
范围
表锁
没有用到索引查询时
行级锁
通过索引+mvcc 实现
replace into
导致主从数据不一致
丢失更新问题
隔离级别
丢失更新，脏读，可重复读（数据变化），幻读（行数变化）
读未提交（read uncommited） yes,yes,yes
读提交（read commited) no,yes,yes
大部分数据库的默认值
一个事务内，数据会变，数据数量也会变
针对当前读：加记录锁，可幻读
可重复读（repeatable read）no,no,yes
mysql默认值，含义是限制其他事务写（排他锁，写锁）
一个事务内数据，数据不变但数量会变
解决幻读，要加范围锁或表锁
当前读：加记录锁，范围锁，不能插入（间隙锁），无幻读
串行（serializable）no,no,no
从mvcc 退化到基于锁的并发控制，全是当前读，读+读锁，写+写锁
基本知识
支持分区
默认innoDB引擎
常用引擎
myisam
my-z[ei]m
my-z[ei]m
innoDB
in-no-db
数据类型
整数，tiny int 1位 127/255，small int 2位，medium int 3位，int 4位，big int 8位
定点数
decimal(m,d) m 1~65,D 0~30 默认0
numeric(m,d)
浮点数
M 0~255，D 0~30
double(m,d) 只保证16位有效数字，超出=decimal
float(m,d) =double REAL_AS_FLOAT
real(m,d) =double
二进制
bit(m) m 1~64
BINARY
VARBINARY
Blob
字符串
char(m) M 0~255 固定长度，不够右边空格填充，取出时截掉
varchar(m) m 0~65535 ,与字符集有关系
Text
ENUM
单选字符串数据类型
最多 65535
set
多选字符串数据类型
最多64个选项
时间日期类型
Date，time，dataTime,TIMESTAMP
datatime
范围 全范围
8个字节存储
与时区无关
TIMESTAMP
范围 1970 ~ 2037
4位存储
与时区有关，不同时区数据不一样
毫秒，微秒问题
mysql 没有毫秒，微秒数据，需要程序自行处理
mysql5.6.4 后支持，DateTime(3/6)或者Timestamp(3/6)
约束
主键 ，联合主键
会生成唯一索引
加no null 约束
产生聚集索引
外键
会生成索引
约束
唯一约束
会生成唯一索引
可以为空
产生非聚集索引
区别：目的不同，唯一约束约束数据，唯一索引优化查询
NO null
高可用方式
sql优化
索引
普通索引
主键索引
唯一索引
组合索引
多个列共同组成，从最左边开始组合创建，只有第一个有单独索引
like 查询时，xxx%会走索引
全文索引（仅MyISAM )
索引数据/存储结构
非聚集索引
节点中只保存行数据地址，而不保持行数据
myisam 引擎使用
聚集索引
innoDB only
所有的数据行存在叶子页中，聚集索引就是表
表中要有一自增的主键，因为聚集索引中叶子节点存的是主键值
btree 索引
b树
二叉树，最多2个子节点
左边比自己小，右边比自己大
极端情况变成链表
所有节点都存数据-关键字
b-树
平衡二叉树，AVL树,多路搜索树
根节点左右子树，深度最多差1，且都是平衡二叉树
所有节点都存数据-关键字
b+树
mysql myisam，innodb都使用
与b-树结构基本相同
作为索引只有达到叶子节点才命中，b-树可以是内节点
数据存在叶子节点中，内节点相当于索引
叶子节点横向有连接指针（顺序指针）
所有数据（关键字）只在叶子节点
b*树
b+树的变体
除了根节点，其他节点都有相邻兄弟节点的指针
将结点的最低利用率从1/2提高到2/3
常见查询算法
二分法
要求数据必须有序
o（n）
二叉树法
需要先建索引
o（logn/2)
主索引，辅助索引
注意事项
索引不包含NULL值
使用短索引
不使用NOT IN和<>操作
MVCC
多版本一致性控制
隐含字段
DB_TRX_ID 6字节
事务id，随事务增加
DB_ROLL_PTR 7字节
回滚指针
DB_ROW_ID
隐藏的id，随记录自然增长
过程
select
当前session 事务版本号>=DB_TRX_ID
delete_id is null or 当前事务号
insert
新插入的行，DB_TRX_ID=当前事务号
update
更新行的时候，InnoDB会把原来行复制一份，DB_TRX_ID=当前事务号
delete
设置删除版本号为当前事务id (DB_TRX_ID=当前事务号??) delete_id = 当前事务号
相当于标记为删除，而不是物理的删除。真是的删除是在InnoDB的purge线程去做的。
commit
修改事务状态为commit,在 redo log 中写入 commit 记录
rollback
根据当前回滚指针从undo log中找出事务修改前的版本，并恢复
职业：暂无
0 条评论
下一页
图形选择
思维导图
主题
补充说明
AI生成
提示
关闭后当前内容将不会保存，是否继续？
取消
确定