高级JAVA面试题详解(四)——数据库MySQL（MySQL优劣、存储引擎、事务、索引、锁、删除重复数据）

MySQL详解 上篇

主要写一些除分库分表、主从之外的其它问题

MySQL和MongoDB的区别

根据各自优劣如何选择使用MySQL还是MongoDB

MongoDB：

1. 侧重高数据写入的性能，而非事务安全，适合业务系统中有大量“低价值”数据的场景。例如做日志系统。
2. 因JSON格式存储，不像MySQL建表之后需要把字段提前设计好，对数据格式不明确的需求支持较好。像第三方数据获取，或授权信息返回数据存储可以选用MongoDB

MySQL：

1. MongoDB不支持事务操作，需要用到事务的应用选择MySQL例如订单状态流转、支付、优惠券使用等等。
2. MongoDB目前不支持join操作，需要复杂查询的选用MySQL
3. MySQL使用广泛，SQL语句成熟，正常业务考虑到开发同事会选用MySQL

MySQL 存储引擎

在mysql5之后，支持的存储引擎有十几个，但是常用的就那么几种，这里简单列举三种说明InnoDB、MyISAM、MEMORY

InnoDB

1. 5.5版本之后默认为引擎
2. 支持事务，默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
3. 支持自增长列
4. 支持外键
5. 使用的锁粒度为行级锁，可以支持更高的并发
6. 存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
7. 支持两种存储方式，分别为：共享表空间存储、独立表空间存储。
   7.1) 共享表空间存储：一个数据库的所有表数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制，官方文档上写的是64TB。对于经常删除操作的这类应用不适合用共享表空间，因为大量删除操作后表空间中将会有大量的空隙。
   7.2)独立表空间存储：每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动，空间可以回收。但受文件大小影响，当单表占用空间过大时，存储空间不足。

缓冲池简介：
       InnoDB存储引擎是基于磁盘存储的，并将其记录按照页的方式进行管理。在数据库系统中由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲技术来提高数据的整体性能。
       缓冲池简单来说就是一块内存区域.通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。
       读取操作首先判断该页是不是在缓冲池中，未命中则读取磁盘上的页。
       修改操作首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘。
       缓冲池配置可以通过INNODB_BUFFER_POOL_SZIE来设置，官方文档建议，缓冲池的大小最多应设置为物理内存的80%，正常使用可以设置为（50%~80%）之间。

MyISAM

1. 5.5版本前的默认引擎
2. 不支持事务，也不支持外键
3. 锁粒度为表级锁 ，并发支持差
4. 存储格式：静态表（Fixed）、动态表（Dynamic）、压缩表
   3.1) 静态表：静态表中的字段都是非变长字段，每个记录都是固定的长度，当表不包含变量长度列(VARCHAR, BLOB, 或TEXT)时,使用这个格式。存储迅速，出现故障容易恢复,占用空间比动态表大，静态表在进行数据存储时会按照事先定义的列宽度补足空格，但在访问的时候会去掉这些空格;
   3.2)动态表：如果表包含任何可变长度的字段（varchar、blob、text），或者该表创建时用row_format=dynamic指定，则该表使用动态格式存储。占用空间小，频繁的更新和删除操作会产生碎片。
   3.3)压缩表：由myisampack工具创建，占据非常小的磁盘空间，因为每个记录都是被单独压缩的

MEMORY

1. 数据存储在内存中、访问速度快，但是在服务器重启之后，所有数据都会丢失
2. 支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
3. 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈

MySQL 事务隔离级别与传播方式

事务的特性（ACID）

• 原子性（Atomicity）：组成一个事务的多个数据库操作是一个不可分割的原子单元；只有所有操作执行成功，整个事务才提交，其中一个操作失败，都必须回滚到初始状态。
• 一致性（Consistency）：事务执行的前后，数据完整性保持一致。
• 隔离性（Isolation）：事务执行不应该受到其他事务的干扰。（根据数据库的隔离级别，会产生不同程度的干扰）。
• 持久性（Durubility）：事务一旦结束，数据就持久化到数据库中。

隔离级别（TRANSACTION）

脏读： 一个事务读取到另一个事务未提交的数据。
不可重复读：一个事务读取到另一个事务提交的更新的数据，导致多次查询结果不一致。
幻读：一个事务读取到另一个事务提交的新增的数据，导致多次查询的结果不一致。

MySQL默认隔离级别为：可重复度

事务的传播方式（PROPAGATION）

• PROPAGATION_REQUIRED：支持当前事务，如果当前没有事务，就新建一个事务。（默认传播属性）
• PROPAGATION_SUPPORTS：支持当前事务，如果当前没有事务，就以非事务方式执行。
• PROPAGATION_MANDATORY：支持当前事务，如果当前没有事务，就抛出异常。
• PROPAGATION_REQUIRESNEW：新建事务，如果当前存在事务，把当前事务挂起。
• PROPAGATION_NOT_SUPPORTED：以非事务方式执行，如果当前存在事务，就把当前事务挂起。
• PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
• PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则进行与PROPAGATION_REQUIRED类似的操作。

索引

mysql有哪些索引，分别是什么数据结构

Hash索引

• 数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。
• hash索引存储的是计算得到的hash值和行指针，而不存储具体的行值，所以通过hash索引查询数据需要进行两次查询(首先查询行的位置，然后找到具体的数据)

B+Tree索引

• 是最常用的mysql数据库索引算法，采用B+树的结构。
• 叶子节点存储了整行数据的是聚簇索引（主键索引）
• 叶子节点存储了主键的值的是非聚簇索引（非主键索引），非主键索引的叶子节点是主键的值，需要回表查询

韭菜课堂开课啦：

非主键索引一定会查询多次吗（发生回表查询）？
覆盖索引 指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。
例如我建了一个联合索引 A、B字段 然后通过where条件A字段去查询B字段。
那假如我没有建主键咋办？
Innodb表中在没有默认主键的情况下会生成一个6byte空间的自动增长主键，可以用select _rowid from table来查询

B+Tree索引与Hash索引对比优劣

• Hash索引，其检索效率非常高，索引的检索可以一次定位，但是只支持“=”和“in”，如存在大量Hash值相等的情况后性能并不一定就会比B+Tree索引高。
• B+Tree索引支持范围查询，Hash索引不支持
• Hash索引不支持多列联合索引的最左匹配规则
• Hash索引不支持索引排序，索引值和计算出来的hash值大小并不一定一致。

MySQL为什么要选择B+tree来做索引的结构

• 普通的二叉树可能因为插入的数据最后变成一个很长的链表，哪怕采用红黑树保证平衡，数据量较大情况下依然会出现节点长度过长的问题。（特别是访问深层数据时，需要索引多次，数据结构设计的更为‘矮胖’一点就可以减少访问的层数）
• B-树每个节点 key 和 data 在一起，无法区间查找。（范围查询在数据库中是很常用的）
• B+树只需要去遍历叶子节点就可以实现整棵树的遍历
• B+树的内部节点并没有指向关键字具体信息的指针，内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了

MySQL锁

锁的机制

共享锁与排他锁
共享锁（读锁）：其他事务可以读，但不能写。
排他锁（写锁） ：止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。：
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁的粒度

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。可避免死锁。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
  • 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。
  • InnoDB 行锁是通过给索引上的索引项加锁来实现的，所以只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

InnoDB的间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
InnoDB使用间隙锁的目的：

• 防止幻读，以满足相关隔离级别的要求；
• 满足恢复和复制的需要：

死锁

死锁的产生

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
  死锁后InnoDB如何处理
  死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁。
  如何避免死锁
• 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
• 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
• 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

乐观锁、悲观锁

乐观锁
乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。如果发现版本号被修改则从新查询后再更新，但是为了避免极端情况下一直循环查询会去限制次数，当到达次数上限后，采用悲观锁。
悲观锁
悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想修改这个数据就会等待直到它拿到锁。

课外习题：
表没有主键和唯一索引的情况下，怎么删除重复的数据？
在有主键的情况下删除重复数据太简单了，这里就不做描述了，咱们来点升级版本的。

方案1：（表中存在大量重复数据）

-- 新建临时表将目标表的不重复的数据插入保存起来
create table tmp as (select distinct * from table_name);
-- 清空目标表的数据
delete from table_name;
-- 将临时表tmp的数据插入
insert into table_nameselect * from tmp;
-- 删除临时表
Drop table tmp;

方案2：（表中数据量大，但重复数据少）

-- 假设表中只有两个字段 name 和 value,取出所有重复的数据放入临时表
create table tmp as (select * from table_name GROUP BY demo_table.`name`,demo_table.`value` HAVING COUNT(1) > 1);
-- 根据临时表的数据删除现有表中的重复数据， 注意delete的语法
DELETE demo_table from demo_table,tmp WHERE demo_table.`value` = tmp.`value` AND demo_table.name = tmp.name
-- 将临时表的数据插入原表
INSERT into demo_table select * from tmp;
-- 删除临时表
Drop table tmp;

方案3：存储过程通过 limit删除
这里就不贴代码了，给你们一个思路就好，循环遍历查询出的所有重复数据结果，将每行数据当做查询条件查询，判断结果是否大于1，如果大于1执行一次delete操作，delete语句末尾加上 limit 1（只删除1行数据），然后继续循环遍历。

最后

以上就是自觉红牛为你收集整理的高级JAVA面试题详解(四)——数据库MySQL（MySQL优劣、存储引擎、事务、索引、锁、删除重复数据）的全部内容，希望文章能够帮你解决高级JAVA面试题详解(四)——数据库MySQL（MySQL优劣、存储引擎、事务、索引、锁、删除重复数据）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。