Java MySQL数据库专题

MySQL是一种关系型数据库，它开源免费(GPL)，扩展方便，稳定性也有保障，因此在企业级开发中常常使用。MySQL默认端口号是3306。

查看MySQL提供的所有存储引擎： mysql> show engines

在这里插入图片描述

MySQL的默认存储引擎是InnoDB，并且在5.7版本的所有存储引擎中只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事务。

MyISAM和InnoDB的区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型。两者各有优劣，视具体应用而定。两者最主要的差别是InnoDB支持事务处理、外键、行级锁，而MyISAM不支持。因此多数开发者认为MyISAM只适合于小项目。

MyISAM存储：如果表对事务要求不高，同时以查询和添加为主，我们主要考虑使用MyISAM存储引擎。例如BBS中的发帖表、回复表。批量添加时MyISAM效率更高。

InnoDB存储：对事务要求高，保存的数据都是重要数据，建议使用InnoDB。例如：订单号、账号表。

【面试】 MyISAM和InnoDB的区别？

1. InnoDB支持事务、回滚、和崩溃修复能力，MyISAM强调的是性能，其每次查询都具有原子性，其执行速度比InnoDB更快，但不支持事务。
2. MyISAM不支持外键，InnoDB支持外键。
3. 锁机制(MyISAM表级锁，InnoDB支持行级锁)。
4. 查询和添加速度(MyISAM批量插入速度快)。
5. 全文索引(MyISAM支持全文索引，InnoDB不支持全文索引)。
6. 内存空间使用率(MyISAM内存空间使用率低于InnoDB，也就是占空间更小)

大多数情况下我们使用InnoDB引擎，但是在某些情况下使用MyISAM也是合适的比如读密集的情况下(如果你不介意MyISAM的崩溃问题的话)。

MySQL索引

MySQL索引使用的数据结构主要有BTree索引和哈希索引。哈希索引底层数据结构就是哈希表，因此绝大多数需求为单条记录查询时，可以选择哈希索引，查询性能最快(O(1))；其余大部分场景，建议使用BTree索引。

MySQL的BTree索引具体指B+Tree，但在主要的两种存储引擎的实现方式是不同的。

• MyISAM：B+树叶子节点的data域存放的是数据记录的地址。在索引检查的时候，首先按照B+树搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后根据data域的地址值读取相应的数据记录。这被称为“非聚簇索引”。MyISAM索引文件和数据文件是分离的。
• InnoDB：InnoDB存储引擎的B+树索引可以分为聚簇索引和辅助索引(非聚簇索引或者二级索引)。InnoDB索引表数据文件本身就是按照B+树组织的一个索引结构，树的叶子结点data域保存了完整的数据记录。索引和数据是存储在一起的。

聚簇索引就是按每张表的主键构造一棵B+树，同时叶子节点中存放的是行记录数据，因此也将聚簇索引的叶子节点也称为数据页，这个特性决定了索引组织表中的数据也是索引的一部分。

在这里插入图片描述

日常工作中，根据实际情况自行添加的索引都是辅助索引(非聚簇索引)。辅助索引是为了查找主键索引而建立的二级索引，先在辅助索引中找到主键索引，再使用主键索引在主索引中查找数据。也由于辅助索引的二次查询过程，在设计表的时候，不建议使用过长的字作为字段，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

事务

事务就是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四大特性(ACID)

1. 原子性(Atomicity)
2. 一致性(Consistency)
3. 隔离性(Isolation)
4. 持久性(Durability)

原子性

原子性如何保证：基于日志的UNDO/REDO机制实现。

一致性

一致性是最基本的属性，保证一致性需要首先满足其他三个属性。 多个事务并行进行的情况下，即使保证了每一个事务的原子性，也不能保证其一致性。

引用知乎@大宽宽的回答如下： ACID之间的关系并不正交，C其实代表“正确性”，他们之间的关系可以理解为代码利用数据库提供的AID特性和自身的正确逻辑，保证数据是正确的。

在这里插入图片描述

并发事务会带来哪些问题

• 脏读
• 丢失修改
• 不可重复读
• 幻读

不可重复度和幻读的区别： 不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或者减少了。

事务的隔离级别

SQL标准定义了四个隔离级别：

• READ-UNCOMMITED(读取未提交)：最低的隔离级别，允许尚未提交的数据变更，可能会导致脏读、幻读和不可重复读。
• READ-COMMITED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读和不可重复读还是会发生。
• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果是一致的，除非数据是被本身事务自己修改，可以阻止脏读和不可重复读，但幻读还是有可能发生。
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有事务依次逐个执行。这样事务之间完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ。 注意：虽然InnoDB存储引擎采取REPEATABLE-READ隔离级别，但是它使用Next-key Lock算法，因此也可以避免幻读的产生，这与其他数据库(SQL Server)是不同的。InnoDB已经可以完全保证事务的隔离性要求，即达到了SQL标准的SERIALIZABLE(可串行化)隔离级别。因为隔离级别越低，事务需要请求的锁越少，所以大部分数据库的隔离级别都是READ-COMMIT，但是InnoDB默认使用REPEATABLE-READ，并且不会有任何性能损失。

Read-Commited、Repeatable-Read两种隔离级别是如何实现的？

基于MVCC实现的，在Read Committed、Repeatable Read这两种事务隔离级别下执行普通的select操作时访问记录的版本链的过程，使不同的事务的读-写，写-读操作并发执行。

Read-Commited：每一次进行普通的select操作都生成一个ReadView。 Repeatable-Read：只在第一次执行普通的select操作前生成一个ReadView，之后的查询操作都重复使用该ReadView。

锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

表级锁和行级锁对比：

• 表级锁：MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，并发冲突的概率最高，并发度最低。
• 行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，不包括记录本身
• Next-Key Lock：Record+Gap锁定一个范围，包括记录本身

大表优化

当MySQL单表记录过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围
• 读/写分离
• 垂直分区
• 水平分区

池化思想和数据库连接池

池化思想在很多的地方都可以见到，比如java线程池，jdbc连接池，redis连接池等。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。除了初始化资源，池化设计还包括如下这些特征：池子的初始化、池子的活跃值、池子的最大值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

数据库连接本质上就是一个socket连接。数据库服务端还要维护一些缓存和用户权限信息之类的所以占用了一些内存。我们可以把数据库连接池看作是维护数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。 在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。

参考文献

JavaGuide面试突击版，百度可得最新版，有删减和小部分修正以及扩充。