MySQL 行锁、表锁、悲观锁、乐观锁的特点与应用
表级锁: 开销小,加锁快;不会出现死锁(因为 MyISAM 会一次性获得 SQL 所需的全部锁);
锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁: 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度
也最高。
页锁:开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,
并发度一般
行锁 和 表锁
1.主要是针对锁粒度划分的,一般分为:行锁、表锁、库锁
(1)行锁:访问数据库的时候,锁定整个行数据,防止并发错误。
(2)表锁:访问数据库的时候,锁定整个表数据,防止并发错误。
2.行锁 和 表锁 的区别:
表锁: 开销小,加锁快,不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低
行锁: 开销大,加锁慢,会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高
悲观锁 和 乐观锁
(1)悲观锁:顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次
在拿数据的时候都会上锁,这样别人想拿这个数据就会 block 直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都
是在做操作之前先上锁。
(2)乐观锁: 顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不
会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本
号等机制。
乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于
write_condition 机制的其实都是提供的乐观锁。
(3)悲观锁 和 乐观锁的区别:
两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下,即冲突
真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。但如果经常产
生冲突,上层应用会不断的进行 retry,这样反倒是降低了性能,所以这种情况下用悲观锁
就比较合适。
共享锁
共享锁指的就是对于多个不同的事务,对同一个资源共享同一个锁。相当于对于同一把门,
它拥有多个钥匙一样。就像这样,你家有一个大门,大门的钥匙有好几把,你有一把,你女
朋友有一把,你们都可能通过这把钥匙进入你们家,这个就是所谓的共享锁。
刚刚说了,对于悲观锁,一般数据库已经实现了,共享锁也属于悲观锁的一种,那么共享锁
在 mysql 中是通过什么命令来调用呢。通过查询资料,了解到通过在执行语句后面加上 lock
in share mode 就代表对某些资源加上共享锁了。
什么时候使用表锁
对于 InnoDB 表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以
选择 InnoDB 表的理由。但在个别特殊事务中,也可以考虑使用表级锁。
第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不
仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考
虑使用表锁来提高该事务的执行速度。
第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种
情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开
销。
当然,应用中这两种事务不能太多,否则,就应该考虑使用 MyISAM 表了。
表锁和行锁应用场景:
表级锁使用与并发性不高,以查询为主,少量更新的应用,比如小型的 web 应用;
而行级锁适用于高并发环境下,对事务完整性要求较高的系统,如在线事务处理系统。
Mysql InnoDB、MyISAM的特点?
1、InnoDB类型支持事务处理,MyISAM类型不支持
2、InnoDB不支持FULLTEXT类型的索引,MyISAM支持。
3、InnoDB提供了行锁,MyISAM写入时锁表。
InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
4、InnoDB 中不保存表的具体行数,也就是说,执行select count() from table时,InnoDB要扫描一遍整个表来计算有多少行;但是MyISAM只要简单的读出保存好的行数即可。
当count()语句包含 where条件时,两种表的操作是一样的。
5、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除,MyISAM重建表。
6、InnoDB不支持LOAD TABLE FROM MASTER操作(表的拷贝从主服务器转移到从属服务器);MyISAM表支持。
解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
7、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引;但是在MyISAM表中,可以和其他字段一起建立联合索引。
数据库隔离级别是什么?有什么作用?
隔离性是数据库事务的一个特性,sql标准将隔离级别从高到底分四种:可序列化(Serializable)、可重复读(Repeatable reads)、提交读(Read committed)、未提交读(Read uncommitted)
未提交读
事务在读数据的时候并未对数据加锁。
务在修改数据的时候只对数据增加行级共享锁。
会出现脏读,因为会读取到其他事务未提交的数据
提交读
事务对当前被读取的数据加 行级共享锁(当读到时才加锁),一旦读完该行,立即释放该行级共享锁;
事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加 行级排他锁,直到事务结束才释放。
提交读可以解决脏读的现象,但是会出现不可重复读的现象
可重复读
事务在读取某数据的瞬间(就是开始读取的瞬间),必须先对其加 行级共享锁,直到事务结束才释放;
事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加 行级排他锁,直到事务结束才释放。
可重复读能够解决不可重复读和脏读的现象,但是还会出现幻读的现象
Mysql主备同步的基本原理?
在master机器上,主从同步事件会被写到特殊的log文件中(binary-log);
在slave机器上,slave读取主从同步事件,并根据读取的事件变化,在slave库上做相应的更改。
主从同步事件有3种形式:statement、row、mixed。
statement:会将对数据库操作的sql语句写入到binlog中。
row:会将每一条数据的变化写入到binlog中。
mixed:statement与row的混合。Mysql决定什么时候写statement格式的,什么时候写row格式的binlog。
master上的操作
当master上的数据发生改变的时候,该事件(insert、update、delete)变化会按照顺序写入到binlog中。
binlog dump线程
当slave连接到master的时候,master机器会为slave开启binlog dump线程。
当master 的 binlog发生变化的时候,binlog dump线程会通知slave,并将相应的binlog内容发送给slave。
slave机器上的操作
当主从同步开启的时候,slave上会创建2个线程。
I/O线程。该线程连接到master机器,master机器上的binlog dump线程会将binlog的内容发送给该I/O线程。该I/O线程接收到binlog内容后,再将内容写入到本地的relay log。
SQL线程。该线程读取I/O线程写入的relay log。并且根据relay log的内容对slave数据库做相应的操作。
select * from table t where size > 10 group by size order by size 语句执行顺序
如何优化数据库性能
升级硬盘,提高读写速度
业务优化,对业务逻辑重新梳理,优化流程,批量更新减少数据库交互次数
数据库使用主从部署,提高服务能力
建立索引,对于常使用的字段创建索引
分库分表,当数据量很大的时候就需要采用分库分表的方式来解决
(索引、分库分表、批量操作、分页算法、升级硬盘SSD、业务优化、主从部署)
SQL什么情况不会使用索引
不包含、不等于、函数
一般在什么字段上建立索引
使用比较频繁的字段,经常作为where条件的字段、作为表关联的字段
小字段
如何从一张表中查出name字段不包含”XYZ”的所有行?
Mysql,B+索引实现,行锁实现,SQL优化
如何解决高并发减库存问题?
采用redis的incr/decr 自增自减命令可以用来作为减库存的方案,因为redis是单线程运行,没有并发问题