MySQL锁及MVCC的原理分析

MySQL中的数据用不同的技术存储在文件或内存中。每种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术称之为存储引擎。通过选择不同的存储引擎，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

引擎有InnoDB,MyIsam,Memory,Mrg_Myisam,Blackhole....(SHOW ENGINES-查看改数据库支持的搜索引擎)，下文中只对两种常用的存储引擎InnoDB,MyIsam作比较。

# 查询该数据库的所有搜索引擎
SHOW ENGINES

开始测试插入

# myisam引擎建立表
create table testMyIsam(  
id int unsigned primary key auto_increment,  
name varchar(20) not null  
)engine=myisam; 

# innodb引擎建立表
create table testInnoDB( 
id int unsigned primary key auto_increment, 
name varchar(20) not null 
)engine=innodb;  


# 我们使用存储过程来插入更新删除记录，以此来比较插入效率，更新效率，删除效率，查询效率
# 创建存储过程
delimiter $$
create procedure ptestmyisam()
begin
declare pid int ;
set pid = 30000;
while pid>0 
do
insert into testMyIsam(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$

# 删除存储过程
drop procedure if exists ptestmyisam;

#使用存储过程：
call ptestmyisam();

# 在插入3W条记录时myisam速度是innodb三倍左右 

通过测试，MyIsam的增删改查速度比InnoDB非常快，这是因为MyIsam不支持事务而InnoDB支持事务。但是普通的增删改是必须要考虑事务安全的，所以我们还是选择速度比较慢的InnoDB。

使用 InnoDB：

1.可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。

2.表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

使用 MyISAM：

1.做很多count的计算的。如一些日志，调查的业务表。

2.插入修改不频繁，查询非常频繁的。

引擎原理

https://blog.csdn.net/qq_21993785/article/details/80582373

锁原理

是否开启事务，何时加锁(增删改查)，加什么锁(行锁表锁共享锁排它锁....) 数据库会自动决定的，所以我们不懂锁机制也可以开开心心写代码

# 在执行下面查询前，存储引擎会自动加一个共享锁(读锁)。
select * from tablea;

# 在执行下面查询前，存储引擎会自动加一个排它锁(写锁)。
update * from tablea;
update table set column1='hello' where id=1
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');

事务

# 查看当前所选库的提交模式
show variables like 'autocommit';
# 关闭当前库自动提交(仅仅临时有效，切换库后会自动恢复至默认开启状态)
set autocommit = 'OFF';
# 更新操作
update testInnoDB set name = 'www' where id = 1
# 回退
rollback
# 手动提交
COMMIT;

#-----------------------------------------------------------------------------

# 不用关心当前提交模式，sql直接开启事务
start transaction
# 更新操作
update testInnoDB set name = 'www' where id = 1
# 回退
rollback
# 手动提交
COMMIT;

# 当然啦，存储引擎InnoDB是支持事务的，所以上面测试有效，而存储引擎MyIsam不支持事务，所以无论开启事务与否，都无效的

锁

MyISAM存储引擎支持的是表级锁，不支持行锁。默认是表锁。

InnoDB存储引擎既支持表级锁，又支持行级锁，默认是行锁。(行锁通过给索引上的索引项加锁来实现行锁)

更新(update)或删除(delete)SQL语句是会加锁的，查询(select)或插入(insert)SQL语句不会加锁，如果更新或删除的时候使用到了索引那么这个锁就是行锁，反之就是表锁。查询或插入的时候无论是否使用了索引都不会加锁。

手动加锁的SQL是 ........FOR UPDATE。因为更新和删除是默认加锁的，我们手动加不加锁没有意义也无法对比，所以我们可以对查询和插入进行手动加锁，这样可以对比。select * from testtable where id = 1 for Update;（这个语句使用到了索引，我们对这个语句加锁，所以这个锁为行锁）

注意：select * from testtable where id = 1 for Update或update  testtable set name = 'wenjian' where id = 5 当我们执行这个SQL的时候，引擎在瞬间完成(加锁,更新,解锁)三个步骤，我们根本来不及进行测试，这时候我们可以开启下事务(这样我们就有足够时间去测试啦)

注意：因为行锁这个特性，所以数据库支持高并发，但是如果InnoDB更新数据的时候不是行锁而是表锁的话，那么其并发性会大打折扣，而且也可能导致你的程序出错。

注意：判断sql是行锁还是表锁是依靠sql是否使用了索引来判断的，我们可以借助explain sql语句，当type=index或range 时候，可以判断这sql使用了索引，进而来确定他使用的是行级锁

注意：行级锁分为两种S(共享锁)和X(排它锁),表级锁也分为两种S(共享锁)和X(排它锁)

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

 

# 产看数据库版本
select version();

# varchar(20) char(20) 
mysql5.0 版本前 代表20个字节(假设编码utf8,可以存储20个字母或6个汉字,一个汉字三个字节)
mysql5.0 版本后 代表20个字符 如果存字母就是20个字节，如果是汉字 就是60个字节

# 查看该字段的字节
select *, length(char4) from user;

# 查看mysql中支持的字符集,Maxlen 是该字符集中一个字符所占的最大字节数
SHOW CHARSET