Mysql
Mysql
InnoDb存储引擎
在InnoDB存储引擎中，数据会存储在硬盘上，在处理数据时需要先将数据加载到内存中，读取表中的记录时，Innod存储引擎不需要一条一条的将记录从硬盘文件中读取出来，InnoDB采取的方式是: 将数据划分成若干个页，以页作为磁盘和内存之间交互的单位，InnoDB中页的大小一般为16KB，也就是说: 当需要从磁盘中读取数据时，每次都会读取16KB大小的数据到内存中，每一次也会将内存中的16KB数据写道磁盘上
InnoDB数据页结构
页是InnoDB管理存储空间的基本单位,一页的默大小为16KB
InnoDB行结构
一页可能有一行数据或者多行数据, 一行最大容量为65535 Byte
一行记录的数据除了我们自己定义的列的数据之外, 还会有3个隐藏的列
row_id: 行id, 当一张表中,如果没有手动的指定主键字段, 那么InnoDB会选取一个unique约束的字段为 主键, 如果连unique字段都没有,那么则会为表默认的添加一个名为row_id的隐藏列作为主键，当有主键时则不会添加
trx_id: 事务id，表示当前行的版本记录是由哪个事务所修改的
roll_pointer: 指向旧版本的id
一行记录可能有多个数据版本, 当事务开启时, 会创建一个read-view(事务快照), 并且为其分配一个全局唯一递增的事务id, 每个事务来修改一行记录时, 都会产生一个数据版本, 并且会将当前事务的id赋值给 trx_id, 同时会保留旧版本的数据日志(undo_log), 并将新版本的行记录的roll_pointer指向旧版本id, 也就是说, 一行记录可能有多个版本, 每个版本都有其trx_id, roll_pointer
InnoDB 索引
索引: 索引是帮助Mysql快速检索数据的排好序的数据结构
Innodb 的索引默认采用的数据结构为 b+ tree
聚簇索引
按照主键值的大小排序并且索引与数据是在一起的, 叶子节点存储数据(一行记录), 非叶子节点存储主键值
所有表中的数据都存放在聚簇索引上的叶子节点上, 聚簇索引并不需要我们手动创建, 在创建表时, InnoDB存储引擎会自动根据主键创建聚簇索引, 在InnoDB存储引擎中, 聚簇索引就是数据的存储方式, 也就是数据即索引, 索引即数据
二级索引(辅助索引)
二级索引(辅助索引): 在InnoDB中除了聚簇索引都是二级索引, 也可以称之为辅助索引, 非聚簇索引
聚簇索引只有当查询条件为主键值的时候才会生效, 因为聚簇索引的B+数中的数据都是按主键值进行排序的, 当我们想以别的字段作为查询条件时, 我们可以多创建几颗B+树, 也就是索引, 不同的B+树中的数据采用不同的规则进行排序
辅助索引(二级索引)特点:
按照指定字段的值进行排序
叶子节点存储的是主键的值, 非叶子节点存储索引值
在对二级索引(辅助索引)进行查找数据时, 先查询二级索引找到对应的主键值, 再根据主键值到聚簇索引中再获取完整的数据 --- 这个过程称之为 回表查询
联合索引
以多个字段的大小为排序规则所建立的索引(B+ Tree)称之为联合索引, 也是辅助索引
-- 创建索引
alter table 表名 add index 索引名称(字段1, 字段2);
-- 删除索引
alter table 表明 drop index 索引名;
-- 查询索引
show index from 表名
索引的代价
① 空间上的代价 ② 时间上的代价
空间上的代价: 每一个索引都对应着一颗B+ Tree,随着数据量的增加,索引所占用的磁盘空间也会增加
时间上的代价: 索引是对数据的排序, 那么当对数据进行增删改的操作时,都需要去维护所涉及到的B+Tree索引
B+ Tree 的优点
B+ Tree 属于多路平衡搜素树, 其特点就是高度低, 一个节点能够存储多个数据, 降低IO消耗
B+ Tree进行全表扫描的效率高, B+ Tree全表扫描只需要对叶子节点进行遍历即可(叶子节点之间互相引用)
B+ Tree的节点天然有序, 其左子节点小于父节点, 右子节点大于父节点, 所以对于范围查询不需要全表扫描
Hash 数据结构索引的缺点
Hash索引适合于等值查询, 无法进行范围查询,因为经过hash后索引并不是有序的, 所以hash索引无法利用索引进行排序, 不支持联合索引的最左前缀匹配规则, 如果出现hash碰撞则会影响效率
B+ Tree 索引总结
每个索引都对应着一棵B+ Tree
索引是帮助Mysql提高查询效率快速, 检索数据的一种数据结构
InnoDB存储引擎会自动为主键建立聚簇索引, 索引的叶子节点存储着表中的完整记录, 非叶子节点存储主键的值
可以为指定的列建立二级索引(辅助索引), 二级索引的叶子节点由索引列值和主键值构成, 非叶子节点存储索引列的值, 如果需要通过二级索引来查找完整的用户记录, 则需要先通过二级索引找到主键值, 再通过主键值到聚簇索引中获取完整的用户记录 ---- 这个过程称之为 回表查询
MyISAM 存储引擎
MyISAM 存储引擎采用非聚簇索引, 也就是说: 索引和数据是分开的
数据文件:
将表中的记录按照记录的插入顺序单独的存放在一个文件中, 称之为数据文件, 这个文件将存储表中所有 的记录, 可以通过行号快速的访问一条记录
索引文件:
MyISAM 存储引擎会将索引信息存储在一个单独的文件中, 称之为索引文件, MyISAM会为主键自动创建索引, 非叶子节点存储主键值, 叶子节点由主键和行号构成
主键索引: 叶子节点存储主键值 + 行号, 非叶子节点存储主键值
二级索引: 叶子节点存储索引列值 + 行号, 非叶子节点存储索引列值
在MyISAM中, 所有的索引都是二级索引, 因为都需要先从索引中获取对应的行号,在通过行号到数据文件中获取记录
执行计划 Explain
EXPLAIN select * from test;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
属性解析:
| 属性 | 解释 |
|---|---|
| id | 每一个select 关键字都对应着一个id, id越大执行的优先级越高,相同则按照顺序执行 |
| select_type | 查询类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 访问表的方法 |
| possiable_keys | 可能使用到的索引 |
| key | 实际使用到的索引 |
| key_len | 实际使用到的索引长度 null 占两个1个字节 可变长度占2个字节 |
| ref | 所引用的对象信息 |
| rows | 预估所需要读取的记录条数 |
| filtered | 经过过滤条件过滤后剩余记录条数的百分比 |
| Extra | 额外信息 |
type 属性常见值
system: 表中只有一条数据, 并且该表使用的存储引擎的统计数据是准确的(MyISAM Memory)
const: 根据主键或者唯一索引列与 常数进行等值匹配
eq_ref: 在连接查询时, 只会匹配到唯一的一条记录 (通常常见于 主键索引列, 唯一索引列)
ref: 在查询时, 可能会返回0条或多条数据 (常见于 普通索引)
range: 根据索引进行范围查询
index: 查询需要遍历整棵索引树
all: 全表扫描
Extra 常见值
impossible where: 查询语句的where 条件永远不可能成立
using index: 使用覆盖索引, 查询的字段可以通过索引获取而不需要回表查询
using index condition: 索引下推, 在存储引擎中进行过滤条件的判断, 减少回表次数
using where: 使用了过滤条件查询
using join buffer: 在连接查询时, 使用了join buffer的内存块加快查询速度
using filesort: 排序的字段使用了文件排序, 在内存(记录少时)中或者磁上(记录多时)对字段的值进行排序(group by 也会用到排序)
using temporary: 使用到了临时表
性能排序
type:
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
Extra:
using index > using index condition > using where > using join buffer > using filesort > using temporary
索引优化
索引最左前缀匹配规则:
Mysql索引查询时会遵循最左前缀匹配规则, 也就是最左优先, 在检索数据时, 从联合索引的最左边开始匹配, 所以当我们创建一个联合索引时如key1, key2, key3, 相当于创建了key1 ; key1 key2 ; key1 key2 key3三个索引
Mysql事务
事务是由一组操作所组成的一个执行单位, 这个执行单位具有ACID4种特性
原子性:
一个事务中的所有操作,要么全部成功,要么全部失败,不会结束在中间的某个环节
一致性:
在事务开始之前和事务结束之后, 数据库的数据完整性没有被破坏
隔离性:
数据库允许多个并发事务同时对数据进行读取或者修改, 隔离性可以防止多个事务并发执行时由于交叉执 行导致的数据不一致
持久性:
事务执行结束后, 对数据的修改时永久的,即便发生故障也不会丢失
Mysql的事务默认为自动提交 可以通过 set autocommit=0 来关闭
隔离级别
未提交读(READ UNCOMMITED):
一个事务可以读取到其他事务没有提交的数据
读已提交(READ COMMITED):
一个事务只能读取到其他事务已经提交的数据
可重复读(REPEATABLE READ):
一个事务第一次读取到某条记录后,即使其他事务修改了该记录的值并且已提交, 该事务再次读取时, 仍然是一样的数据, 不会每次都能读到不同的数据
串行化(SERIALIZABLE):
事务只能一个一个的执行, 不能够并发执行
并发事务所带来的问题
脏读: 一个事务读取到另一个事务还没有提交的数据
不可重复读: 一个事务对同一行数据读取了两次,却得到了不同的结果
幻读: 一个事务在在操作过程中进行了两次查询, 第二次查询的结果包含了第一次查询的结果中未出现的数据或者是缺少了第一次查询中出现的数据
| 隔离级别 | 问题 |
|---|---|
| 未提交读(READ UNCOMMITED) | 脏读 不可重复读 幻读 |
| 读已提交(READ COMMITED) | 不可重复读 幻读 |
| 可重复读(REPEATABLE READ) | 幻读(mysql中不会出现) |
| 串行化(SERIALIZABLE) | null |
ReadView MVCC
在一个事务中,(隔离级别为读已提交: )每次查询都会创建一个ReadView, 称之为事务快照, 通过ReadView就可以判断一条记录的某个版本对当前事务是否可见
每一行记录都有两个隐藏列:
trx_id: 事务id，表示当前行的版本记录是由哪个事务所修改的
roll_pointer: 指向旧版本的id
一行记录可能有多个数据版本, 当事务开启时, 会创建一个read-view(事务快照), 并且为其分配一个全局唯一递增的事务id, 每个事务来修改一行记录时, 都会产生一个数据版本, 并且会将当前事务的id赋值给 trx_id, 同时会保留旧版本的数据日志(undo_log), 并将新版本的行记录的roll_pointer指向旧版本id, 也就是说, 一行记录可能有多个版本, 每个版本都有其trx_id, roll_pointer
ReadView中4个重要的属性
m_ids: 表示在生成ReadView时当前系统活跃的事务id列表
max_trx_id; m_ids列表中最大值, 也就是当前活跃事务id列表中值最大的事务id
min_trx_id: m_ids列表中的最小值, 也就是当前活跃事务id列表中值最小的事务id
creator_trx_id: 表示当前事务id
作用:
通过ReadView中的4个属性, 只需要特定的步骤就可以判断某个版本的记录对当前事务是否可见
1) 如果被访问版本的trx_id等于ReadView中的creator_trx_id属性值, 那么就表示当前事务正在访问自己修改过的记录, 所以该版本的记录对当前事务是可见的
2) 如果被访问版本的trx_id小于ReadView中的creator_trx_id属性值时, 那么就表示生成该版本的事务在当前事务生成ReadView之前就已经提交了, 所以该版本的记录对当前事务是可见的
3) 如果被访问版本的trx_id大于ReadView中的creator_trx_id属性值时, 那么就表示生成该版本的事务是在当前事务生成ReadView之后对这条记录进行修改的, 所以该版本的记录对当前事务是不可见的
4) 如果被访问版本的trx_id值在ReadView的max_trx_id和min_trx_id属性值之间时,那么就需要判断trx_id值是否在m_ids事务id列表中, 如果在: 那么就表示在当前事务生成ReadView时, 生成该版本的事务还是活跃的,也就是未提交的, 所以该版本对当前事务是不可见的; 如果不在: 那么就表示: 在生成ReadView时,生成此版本的事务已经提交, 那么此版本的记录对当前事务是可见的
MVCC
概述: MVCC (Multi-Version Concurrency Control 多版本并发控制) 指的就是在READ COMMITED(读已提交)和REPEATABLE READ(可重复读)这两种隔离级别的事务在执行普通的select 操作访问记录的版本链的过程, 可以让不同的事务的读-写, 写-读操作并发执行, 从而提升系统性能。 读已提交和可重复读最大的区别就是生成ReadView的时机不同, 读已提交在每一次进行普通的select操作都会生成一个ReadView, 而可重复读只会在第一次进行普通的select操作生成ReadView, 之后的普通的select操作都是重复使用这个ReadView
加锁会破坏MVCC机制
Mysq|的锁机制
读锁(共享锁, Shared Locks锁, S锁):
将查到的数据加上读锁,其他事务可以获取这些记录的读锁，但是不能够获取写锁
写锁(排他锁, Exclusive Locks, X锁):
将查到的数据加上写锁,其他事务不可以获取这些记录的读写锁
加读锁: select * from user where id >= 3 lock in share mode;
将查到的数据都会加上读锁,允许其他事务能够获取到这些记录的读锁,但是不能够获取写锁
加写锁: select * from uesr whereid = 1 for update;
将查到的数据加上写锁,其他事务不允许获取这些记录的读写锁
读操作:
对于普通的select操作,不会加任何锁
写操作:
insert: 插入一条记录时，先加隐式锁来保护这条新插入的记录在本事务没有提交前,不会被其他事务访问到
(隐式锁:一个事务插入一条记录后，还未提交,，这条记录会保存本次事务id,如果其他事务想要来读取该记录时会发现事务id不一致, 不会允许访问)
update: 如果被更新的列，修改前后不会导致存储空间的变化,那么会先给记录加上写锁,再对记录进行修改如果修改前后会导致存储空间发生变化,那么回先给记录加上写锁然后删除该记录再insert一条新纪录
delete: 删除条记录时，会先给记录加 上写锁，再将记录删除
行锁与表锁:
LOCK_ REC _NOT. GAP: 单个行记录上的锁
LOCK_ GAP: 间隙锁，锁定一个范围,但是不包括记录本身。 GAP锁的目的:是为了防止同- -事物的两次当前读,出现幻读的情况
next-key lock: 锁定-个范围,并且锁定记录本身。 对于行的查询,都是采用该锁,主要目的用于解决幻读的问题
READ COMMITED情况下:
不管有没有索引,只会对查询到的记录进行加锁
REPEATABLE READ级别下:
主键索引和唯一索引都只会对满足条件的记录进行加锁
普通索引会对满足条件的记录和间隙进行加锁
没有索引那么会对表中所有的记录和间隙进行加锁
MysqI的默认隔离级别为REPEATABLE READ, REPEATABLE READ隔离级别因为自动会加GAP锁,所以会解决幻读的情况
MySQL/InnoDB定义的4种隔离级别:
Read Uncommited
可以读取未提交记录
Read Committed (RC);
针对当前读, RC隔离级别保证对读取到的记录加锁(记录锁),存在幻读现象。
Repeatable Read (RR):
针对当前读，RR隔离级别保证对读取到的记录加锁(记录锁),同时保证对读取的范围加锁,新的
满足查询条件的记录不能够插入(间隙锁),不存在幻读现象。
Serializable:
从MVCC并发控制退化为基于锁的并发控制。区别快照读与当前读,所有的读操作均为当前读,读加读锁(S锁),写加写锁(X锁。
Serializable隔离级别下，读写冲突,因此并发度急剧下降，在MySQL/InnoDB 下不建议使用。
图解
mysql事务以及readview
Mysql锁
