java面试题及解答—MySQL

一、mysql的锁有哪几种

• 按颗粒度划分
  • 行级锁：行级锁是mysql中粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能够大大减少数据操作时发生的冲突，但实现起来的开销也最大。行级锁分为共享锁和排他锁
  • 表级锁：表级锁是mysql中粒度最大的一种锁，在操作数据库时，会将整张表锁住。表级锁实现起来就比较简单，开销也比较小
  • 页级锁：页级锁是mysql中比较折中的一种锁，行级锁开销大，冲突少，表级锁开销小，冲突大，页级锁就是比较折中的一种锁，他能够对数据行相邻的一组数据进行加锁
• 按级别划分
  • 共享锁：共享锁又称读锁，其他事务可以并发读取，但是不能修改。加了共享锁的数据，其他事务也只能加共享锁，不能加排他锁。使用select ... lock in share mode，MySQL就会对查询出来的每一行数据加共享锁，如果其中一行数据被加了排他锁，则会被阻塞
  • 排他锁：排他锁又称写锁或独占锁。加了排他锁的数据，其他事务不能再对其加任何锁，获取排他锁的事务可以读取数据和修改数据。使用select ... for update，MySQL就会对查询出来的每一行数据加排他锁，当没有其他事务干预这些数据时，就可以加锁成功，否则被阻塞
  • 意向锁：意向锁是表级锁，是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关键字，如not null, not exist
  • 全表扫描比索引快时，会放弃索引使用全表扫描
  • 部分场景使用or关键字，相同字段使用or会使用range，带了索引的多个字段使用or会使用index_merge，其中任意字段未建立索引则会导致索引失效

  select * from table1 where name = '张三' or name = '李四';
  

  name字段建立了索引，则会使用type=range的索引

  select * from table1 where name = '张三' or No = '1001';
  

  name和No字段都建立了索引，则会使用type=index_merge的索引
  name有索引，而No没有索引，则会导致索引失效，如果把or改成and，则会使用type=index_merge的索引

• 其他见索引面试题还原现场

六、MySQL优化

• 对频繁使用的查询字段建立索引
• 防止索引失效，避免全表扫描，可以参考上述会导致索引失效的条件
• 避免使用select *，返回无用字段会降低查询效率

七、悲观锁和乐观锁

• 悲观锁：对事物采取一种悲观的态度，每次操作数据时都认为其他事务会修改数据，于是在操作之前将数据加锁，一般是使用数据库提供的锁机制，如果是读操作，则使用共享锁，其他事务只能读不能改，如果是写操作，则使用排他锁，其他事务不能读也不能写。悲观锁大多数情况下需要依靠数据库本身提供的锁机制，以达到操作时最大程度的独占性，但随之而来的就是造成数据库的大量开销，特别是对于长事务，这种开销往往无法承受，则需要使用下面的乐观锁。
• 乐观锁：对事物采取一种乐观的态度，不使用数据库提供的锁机制，一般使用数据版本机制，即给数据表添加一个版本（version）标识，每次更新时将版本+1，如果更新时的版本号不大于数据库里面的版本号，则认为是过期数据

八、分库分表

• 分库：按照功能将不同的数据库放在不同的服务器上，比如用户功能模块的数据表和商品功能模块的数据表分别存储，一般这种在微服务架构中已经实现
• 分表：
  • 垂直分表：顾名思义，垂直是按照列来分，即字段，按照功能将表中的部分字段放到重新创建的数据表中，比如用户的基本信息和其他信息，解决的是列过多的问题
  • 水平分表：水平是按照行来分的，即数据行，数据量超过设置的阈值时，就重新创建一张结构一样的表进行存储，或者根据日期来分表，将多个或者一个自然日的数据放在同一张表中，解决的是数据量过大的问题