一、mysql的锁有哪几种
按颗粒度划分
1.行级锁:行级锁是mysql中粒度最小的一种锁,只针对当前操作的行进行加锁。行级锁能够大大减少数据操作时发生的冲突,但实现起来的开销也最大。行级锁分为共享锁和排他锁
2.表级锁:表级锁是mysql中粒度最大的一种锁,在操作数据库时,会将整张表锁住。表级锁实现起来就比较简单,开销也比较小
3.页级锁:页级锁是mysql中比较折中的一种锁,行级锁开销大,冲突少,表级锁开销小,冲突大,页级锁就是比较折中的一种锁,他能够对数据行相邻的一组数据进行加锁
按级别划分
4.共享锁:共享锁又称读锁,其他事务可以并发读取,但是不能修改。加了共享锁的数据,其他事务也只能加共享锁,不能加排他锁。使用select … lock in share mode,MySQL就会对查询出来的每一行数据加共享锁,如果其中一行数据被加了排他锁,则会被阻塞
5.排他锁:排他锁又称写锁或独占锁。加了排他锁的数据,其他事务不能再对其加任何锁,获取排他锁的事务可以读取数据和修改数据。使用select … for update,MySQL就会对查询出来的每一行数据加排他锁,当没有其他事务干预这些数据时,就可以加锁成功,否则被阻塞
6.意向锁:意向锁是表级锁,是MySQL自动添加的,无需用户自己操作,MySQL中的意向锁又分为两种,意向共享锁和意向排他锁。innodb会自动对insert、update、delete加意向排他锁,对于一般的select语句,innodb不会对其加任何锁,可以显式添加锁
二、mysql数据库的MyISAM和InnoDB的区别
最大的区别在于对于事务的支持,MyISAM不支持事务,而InnoDB支持事务
行级锁,MyISAM不支持行级锁,InnoDB支持
外键, MyISAM不支持外键,InnoDB支持
mysql5.5版本之后,默认的引擎是InnoDB
三、MySQL在并发中事务会产生的问题
脏读:读到了其他事务未提交的数据,第一次读取时,事务还未提交,第二次读取,事务已被回滚。比如你在吃烧烤,不小心掉了一块肉,蚂蚁看到了,赶紧回去叫兄弟们过来吃,这时,你把肉捡起来丢到了垃圾桶,这个动作也就是事务回滚,然后蚂蚁回来时却已没有了肉
不可重复读:一个事务读取了两次数据,两次读到的数据不一致,发生的过程是事务1去读数据,读到的是A,然后事务2把那条数据改成了B,事务1再去读一次,结果发现变成了B,就造成了两次读取数据不一致的问题。
幻读:在一个事务的操作里面发现了未被操作的数据,发生的过程是,事务1将所有符合条件的数据(如:select * from xxx where )都做了修改,然后事务2又插入了一批符合条件的数据,事务1就发现还有符合条件的数据没有被修改成功,感觉像是出现了幻觉一样。
四、MySQL的InnoDB引擎支持的事务隔离级别和各自的区别
DEFAULT,默认的隔离级别,每个数据库的默认隔离级别都不一样,MySQL的默认隔离级别是可重复读,Oracle的默认隔离级别是读已提交
READ-UNCOMMITTED,读未提交,其他事务可以读取当前事务还未提交的数据。这个级别的事务隔离无法解决脏读、幻读和不可重复读问题,因此一般不使用
READ-COMMITED,读已提交,其他事务只能读到当前事务已经提交的数据
REPEATABLE-READ,可重复读,事务在查询到了数据之后会加锁,其他事务无法修改,这样就解决了脏读和不可重复读的问题,但幻读问题依然存在
SERIAZIABLE,串行化,最高的事务隔离级别,所有事务只能挨个执行,要等到上一个事务完全执行结束,下一个事务才能执行,这样就完全解决了脏读、幻读和不可重复读的问题,但同样带来的是效率的降低
隔离级别设置越高,隔离效果越好,但同时效率也越低,因此在开发中要根据自己的实际场景来进行选择,在一般的场景中,READ-COMMITED(读已提交)就能够满足开发需求
五、MySQL的索引相关面试题
索引类型
普通索引:最基本的索引,没有任何限制
唯一索引:索引列的值只能唯一,但允许为空,如果是组合索引,则组合的值只能唯一
主键索引:是特殊的唯一索引,值不允许为空,且只能有一个主键索引
组合索引:在多个字段上创建索引,只有在查询条件中使用了创建索引的第一个字段索引才会生效。使用组合索引时,需遵循最左前缀匹配原则
全文索引: 主要用来查询文本中的关键字,而不是直接与索引中的值进行对比
B+树与B树的区别:B+树只有叶子节点存储数据,B树所有节点都存储数据
创建索引的原则:最频繁使用,用以缩小查询范围的字段;频繁使用,需要排序的字段
索引失效问题:
组合索引中,查询条件使用的字段不包含创建组合索引使用的第一个字段
like条件中,%在前
对索引列使用计算表达式或者函数
字段定义类型为varchar,查询时条件字段类型用number,比如No='111111’写成No=111111
使用not in和not exist关键字
条件中使用or关键字,应该使用in
将字段与null值比较,比如使用is null或者is not null
其他见索引面试题还原现场
六、MySQL优化
对频繁使用的查询字段建立索引
防止索引失效,避免全表扫描,可以参考上述会导致索引失效的条件
避免使用select *,返回无用字段会降低查询效率
七、悲观锁和乐观锁
悲观锁:对事物采取一种悲观的态度,每次操作数据时都认为其他事务会修改数据,于是在操作之前将数据加锁,一般是使用数据库提供的锁机制,如果是读操作,则使用共享锁,其他事务只能读不能改,如果是写操作,则使用排他锁,其他事务不能读也不能写。悲观锁大多数情况下需要依靠数据库本身提供的锁机制,以达到操作时最大程度的独占性,但随之而来的就是造成数据库的大量开销,特别是对于长事务,这种开销往往无法承受,则需要使用下面的乐观锁。
乐观锁:对事物采取一种乐观的态度,不使用数据库提供的锁机制,一般使用数据版本机制,即给数据表添加一个版本(version)标识,每次更新时将版本+1,如果更新时的版本号不大于数据库里面的版本号,则认为是过期数据
八、分库分表
分库:按照功能将不同的数据库放在不同的服务器上,比如用户功能模块的数据表和商品功能模块的数据表分别存储,一般这种在微服务架构中已经实现
分表:
垂直分表:顾名思义,垂直是按照列来分,即字段,按照功能将表中的部分字段放到重新创建的数据表中,比如用户的基本信息和其他信息,解决的是列过多的问题
水平分表:水平是按照行来分的,即数据行,数据量超过设置的阈值时,就重新创建一张结构一样的表进行存储,或者根据日期来分表,将多个或者一个自然日的数据放在同一张表中,解决的是数据量过大的问题