MySQL(事务&锁)
引擎
InnoDB
-
索引
-
默认聚簇索引
-
主键索引-叶子节点包含整行数据、回滚指针、更新版本号、删除版本号（后两个用于MVCC）
-
普通索引-叶子节点包含主键值，通过查询主键值，再使用主键索引查询整行数据
- 不带行指针，减小数据移动和页分裂的开销
-
-
锁
-
行锁
- 使用索引查询数据时，锁加在索引上（oracle加在数据行上），当多个数据对应同一个索引时，会产生阻塞
-
表锁(其实不是)
- 未使用索引查询数据时，将全表搜索，给每一行加锁，大大降低并发性
-
SQL1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10; 加锁总结-
在前面八种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采用的是快照读 下面的讨论中就忽略，主要讨论SQL2：delete操作的加锁
-
一，id列是主键，RC隔离级别
id是主键时，此SQL只需要在id=10这条记录上加X锁即可
-
二，id列是二级唯一索引，RC隔离级别
若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录
-
三，id列是二级非唯一索引，RC隔离级别
若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁
-
四，id列上没有索引，RC隔离级别
若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL_Server层面进行的。 因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化， 对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁， 但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束
-
五，id列是主键，RR隔离级别
- 与组合一相同
-
六，id列是二级唯一索引，RR隔离级别
- 与组合二相同
-
七，id列是二级非唯一索引，RR隔离级别
Repeatable_Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete_from_t1_where_id_=10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁， 然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。 直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束
-
八，id列上没有索引，RR隔离级别
在Repeatable_Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP 杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent_read，来缓解加锁开销与并发影响 但是semi-consistent_read本身也会带来其他问题，不建议使用
READ COMMITTED隔离级别和关闭nodb_locks_unsafe_for_binlog还有另外一个负作用：MySQL会释放掉不匹配Where条件的记录锁。例如，对于UPDATE语句，InnoDB只能进行“半一致性(semi_consistent)读”，所以，它会返回最新提交事务所做改变，从而产生不可重复读和幻像问题。 -
九，Serializable隔离级别
在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读
-
lock in share mode\for update
锁住返回的记录，通过索引查询，锁住索引相关记录，全表扫描时，锁住所有记录。
-
insert 只对插入记录加锁
-
-
当前读时，根据where条件过滤，确定存储引擎返回给server的记录，该记录需加锁
-
当两个insert操作，对索引的同一间隙范围插入数据时，并不会造成阻塞，称为插入意向间隙锁
-
-
事务隔离级别
-
读未提交
- 总是读取最新版本，可能脏读、不可重复读、幻读
-
读提交
-
使用MVCC
-
增删改操作提交后，对其它事务可见，可能不可重复读、幻读 若一事务未提交前另一事务便查询，数据来自回滚段
RR隔离级别下，一致性读读取记录的最新版本，若最新版本被锁（有事务在操作未提交），则读取回滚段最新版本，回滚段数据不存在有事务在操作
-
-
可重复读（默认级别）
-
MVCC
-
每个事务开始时InnoDB分配事务ID，ID自增 读快照不加锁 读当前版本加锁 insert、update、delete均为当前读 读（select）不加锁，读写不冲突，并发性提升
-
增
- 更新版本号 = 当前事务ID
-
删
- 删除版本号 = 当前事务ID
-
改
- 更新版本号 = 当前事务ID
- 将更改前数据复制到回滚段，版本同样 = 当前事务ID X？
-
查
- 更新版本号 <= 事务版本号 && 删除版本号未定义 或> 事务版本号
- 若无法返回最新记录，可能返回回滚段数据
- 一致性读，同一事物多次读，读取的是第一次读的快照版本
-
-
插入操作提交后，对其它事务可见，可能幻读
-
间隙锁
-
锁住满足查询条件存在和不存在的记录（防止增删），避免幻读
MVCC在select（读快照）的时候，可以通过版本筛选，避免幻读（大于当前事务ID不返回数据），为什么需要间隙锁？因为在类似当前读如select...for update的时候，读取当前版本，会产生幻读。
-
但如当另一个事务插入一个记录，本事务也要插入一个相同主键的记录时，会报错，可见较串行化而言幻读避免并不完全
-
-
-
串行化
- 总是读取最新版本，所有操作加锁代替MVCC，读写冲突，并发性降低
-
-
where 条件过滤
-
Index Key，决定间隙锁
-
Index First Key
- 确定索引查询的起始范围
-
Index Last Key
- 确定索引查询的终止范围
-
存储引擎使用索引过滤
-
-
Index Filter
- Index Key 范围中需满足的条件
- 5.6之前，在MySQL_Server过滤 5.6之后，支持Index_Condition_PushDown，存储引擎使用索引过滤
-
Tab Filter
- 不属于索引列的查询条件
- 在MySQL_Server过滤
-
MyISAM
-
索引
- 不支持聚簇索引
- 主键索引-叶子节点保存行号（指针）
- 普通索引-叶子节点同样保存行号
- 区别：主键索引仅是在普通索引上要求唯一非空
-
锁
- 仅支持表锁
索引
特点
- 加快select速度，降低写改删速度
类别
-
聚簇
-
特点
索引项的顺序与表中记录的物理顺序一致。叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。 聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。
-
插入数据
插入操作根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。 如果数据页已满，则需要拆分数据页，调整索引指针（且如果表还有非聚集索引，还需要更新这些索引指向新的数据页）。 而类似于自增列为聚集索引的，数据库系统可能并不拆分数据页，而只是简单的新添数据页。
-
删除数据
-
-
非聚簇
-
特点
- 表数据存储顺序与索引顺序无关。叶结点包含索引字段值及指向数据页数据行的逻辑指针。 非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。
-
插入数据
- 数据行没有特定的顺序，所有的新行将被添加到表的末尾位置。
-
删除数据
-
-
B-Tree
-
Hash
-
组合索引
- 假设存在组合索引index_i1 c1c2(c1,c2)， select * from t1 where c1=1 and c2=2 能够使用该索引。 select * from t1 where c1=1 也能够使用该索引。但是，查询语句 select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。
- 实质就是根据前面的where条件过滤提取
-
覆盖索引
- 当需要查询的数据列全部包含在一个组合索引中时，MySQL仅使用索引就可查出数据，无需通过索引二次查询
索引排序
1、ORDER BY的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
2、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。
注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化...
集群
读写分离
master-slave
分区
范围分区
哈希分区
count(*)
innodb遍历全表得出长度，采用行锁，若维护一个需要同步修改的rows，行锁就无意义
myisam采用表锁，修改rows同步无压力
命令
不可重复读VS幻读
不可重复读
- 同一事务中，多次查询结果，行数相同，值不同
幻读
- 同一事务中，多次查询结果行数不同
网友评论