MySQL InnoDB|MYISAM对比,读写锁,乐观锁对比

 

 

1.聚簇索引和非聚簇索引区别

聚簇索引数据格式:        [PK|TID|DATA] 存放了主键事务ID,数据

非聚簇索引数据格式:    [PK|ADDRESS]存放了主键事务ID,数据地址

 

2.InnoDB 和 MYISAM对比

InnoDB: 

        1. 默认创建主键聚簇

        2.支持事务

        3.CRUD加了事务

        4.支持,表锁,行锁,共享锁

MYISAM: 

        1. 默认创建非聚簇索引

        2.不支持事务

        3.CUD开启表锁

        4.支持,表锁

 1. 大量插入操作： MYISAM只有表锁，因此在单请求大批量操作数据时是最优选择，InnoDB涉及到事务

 2.大量查询（count和groupBY）:MYISAM引擎的表锁里存储了行数，count比innodb去检索索引树.全表统计MYISAM要快些

 3.大量查询,走索引,inndb比MYISAM快

 4.批量更新：InnoDB根据索引查找数据更新比MYISAM先检索到主键在更新数据要快一些

 5. 删除：MYISAM引擎数据紧密存储，删除和更新会导致空洞问题，致使MYISAM追加功能受阻，只有在补完洞之后MYISAM性能才得以发挥

 

3.for update 排它锁（行锁|写锁|X锁）

SET AUTOCOMMIT = 0; 
SELECT * from user WHERE id = 1 for update;
COMMIT;

注意： where id 如果非索引则是对整张表上锁，否则是走innodb的行锁

for upate锁定查询命中的行,自己能写,别人不能写,不能加锁,可读

未commit前:

         1.其他请求尝试加排他锁,阻塞,直到上一个commit

         2.其他请求尝试修改数据,阻塞,直到上一个commit

         3.其他请求查询开启事务,正常查询结果

 

 

4.共享锁(读锁|S锁)

自己加锁,别人可加锁,未提交前update操作阻塞

SELECT * from user WHERE id = 1 lock in share mode;

 

5.乐观锁

乐观锁 需要增加version字段，每次修改时version++,如果存在同时修改那么，会有一个成功，其他的失败,对用户不友好需要在代码中自旋操作,缺点高并发时对数据请求过多,适用于一个账户多端操作防止并发问题

UPDATE user set `name`='李四' WHERE id =1 AND vserion=1001

 

6.行锁

{记录锁，间隙锁}

记录锁：索引结构上加锁

间隙所：当记录锁未命中，自动实现间隙锁

临间锁：

6.表锁

LOCK TABLE user READ; 用读锁锁表，会阻塞其他事务修改表数据。
LOCK TABLE user WRITE; 用写锁锁表，会阻塞其他事务读和写。

表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般