Mysql
2020-07-07 13:54:51 18 举报AI智能生成
Mysql知识点总结
作者其他创作
大纲/内容
隔离级别
RAED UNCOMMITED
使用查询语句不会加锁，可能会读到未提交的行（Dirty Read）
READ COMMITED
只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，
所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）；
所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）；
REPEATABLE READ
多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）；
通过行级锁+MVCC实现
SERIALIZABLE 串行化
实现
锁
加锁是加在索引上的
类型
共享锁
允许其他事务再加共享锁，不允许其他事务再加排他锁；
select…lock in share mode
排它锁
其它事务无法再对该数据加共享或者排他锁。
select…for update
种类
表级锁
是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，
被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁分为表共享读锁与表独占写锁。
被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁分为表共享读锁与表独占写锁。
lock talbe name read
lock table name write
unlock table
行级锁
页级锁
GAP锁（间隙锁）
意向锁
死锁
事物A持久资源1的锁，事务B持有资源2的锁
事务A等待资源2的锁，事务B等待资源1的锁
事务A等待资源2的锁，事务B等待资源1的锁
insert加锁
意向排它锁+排它锁
时间戳
MVCC 多版本控制
（Multiversion Concurrency Control）
（Multiversion Concurrency Control）
来源-根据源码佐证
https://blog.csdn.net/Waves___/article/details/105295060
最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，
MVCC只在 Read Committed 和 Repeatable Read两个隔离级别下工作
MVCC只在 Read Committed 和 Repeatable Read两个隔离级别下工作
实现
隐藏字段
DB_TRX_ID
表示最近一次对本记录行作修改（insert | update）的事务ID。至于delete操作，
InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。
InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。
DB_ROLL_PTR
回滚指针，指向当前记录行的undo log信息
DB_ROW_ID
随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时，
innodb就会使用这个行ID自动产生聚簇索引。这个DB_ROW_ID跟MVCC关系不大。
innodb就会使用这个行ID自动产生聚簇索引。这个DB_ROW_ID跟MVCC关系不大。
Read View
Read View主要是用来做可见性判断的, 里面保存了“对本事务不可见的其他活跃事务”。
包含如下几个变量
包含如下几个变量
low_limit_id
目前出现过的最大的事务ID+1，即下一个将被分配的事务ID
up_limit_id
活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id
trx_ids
Read View创建时其他未提交的活跃事务ID列表，创建Read View时，
将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的
将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的
creator_trx_id
当前创建事务的ID，是一个递增的编号
Undo log
存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。
增删改查
select
读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的
insert
将当前事务的版本号保存至行的创建版本号
update
新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
delete
将当前事务的版本号保存至行的删除版本号
常用
show engine innodb status;
查看innoDB状态，包含死锁日志
查看innoDB状态，包含死锁日志
查看隔离级别
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
设置隔离级别
查看表状态
show table status like ‘plan_branch’\G;
show table status from test like ‘plan_branch’\G;
show table status from test like ‘plan_branch’\G;
查看锁概况
show status like 'innodb_row_lock%';
数据库版本
select version();
引擎
MyISAM
结构
tableName.frm
frame表结构
tableName.myd
myISAM data数据文件
tableName.myi
myISAM index索引文件
搜索
在index文件中查找到文件对应的指针地址，再去data文件取数据
查看正在锁和等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
InnoDB
结构
tableName.frm
frame结构文件
tableName.ibd
索引、数据集合文件
搜索
ibd本身就是b+树结构文件，data存放的就是完整数据列
ACID
Atomicity 原子性
事务一起成功或一起失败
回滚日志 undo log实现
Consistency 一致性
表示事务完成后，符合逻辑运算
Isolation 隔离性
排除其他事务对本事务的影响
Durability 持久性
事务结束后的数据不随着外界原因导致数据丢失
加锁实例分析
select * from t1 where id = 10 for update;加了几把锁
场景1：ID是主键索引
行锁，不需要gap锁
场景2：ID非主键，但属于唯一索引
两把锁
场景3：没有索引
全表加锁，并加gap锁
场景3：普通非唯一索引
id=10的行加锁，并加gap锁
所有gap锁场景都在RepeatableRead级别下
索引
概念
排好序的数据结构
结构
b+tree
hash
在=查找时很快，范围查找时很慢
二叉树
从左到右递增
缺点：单边增长
红黑树-二叉平衡树
二叉平衡树
左旋右旋
缺点：高度太高
插入
新节点除了根总为红色
B树-多叉平衡树
叶子节点具有相同的深度，叶节点的指针为空
节点元素从左到右递增
缺点
每个元素都是k-v结构，data挤占节点空间，造成高度过高
范围查询时因为data分布在所有节点，需要多次从根遍历
B+树-mysql使用
mysql默认节点大小-16KB
非叶子节点不存储data，只存储索引，会造成冗余索引
叶子节点用指针连接，提高区间访问性能（如范围查找）
叶子节点包含data和所有的元素
MyISAM中data是文件地址指针
InnoDB中data是完整数据列
分类
聚集索引
就是叶子节点包含完整的数据列
稀疏索引
如MyISAM，叶子节点只包含数据的指针
索引优化
全值匹配
用等于查询
最左前缀
从索引的最左前列开始并且不跳过索引中的列
不要在索引上做任何操作(运算、函数、类型转换等)
不能使用索引中范围条件右边的列
WHERE name= 'LiLei' AND age = 22 AND position ='manager';
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
mysql在使用不等于（！=或者<>）的时候无法使用索引
.is null,is not null 也无法使用索引
ike以通配符开头（'$abc...'）mysql索引失效
少用or,用它连接时很多情况下索引会失效
explain
type列
MySQL决定如何查找表中的行
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
面试
为什么InnoDB表必须有主键，最好是整型自增的主键
0 条评论
下一页
