JAVA面试汇总-7.数据库

可以扫描下面二维码访问我的小程序来打开，随时随地通过微信访问。

1.请简洁描述Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

（1）Read Uncommited，读到未提交数据，所有事务都可以看到其他未提交事务的执行结果。很少用于实际应用，因为他的性能也不比其他级别好多少。读取到未提交的数据，也被称之为脏读，一旦实际最终数据回滚了，还会导致一些列问题。
（2）Read Commited，读到提交内容，这是主流数据库系统的默认隔离级别（但不是Mysql 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读 （ Nonrepeatable Read ）, 因为同一事物的其他实例在该实例处理期间可能会有新的Commit ,所以同一 select 可以返回不同结果。
（3）Repeatable Read(可重读)：这是Mysql的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题，幻读（Phantom Read），简单的说，幻读指当用户读取某一范围的数据行时，另一事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。
（4）Serializable(可串行化)：这是最高的隔离级别，它通过强制事务排序，使之不可能互相冲突从而解决幻读问题。简而言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。实际上，不同的事务同时开启同一个表的查询是没有问题，但是一旦开启查询，则其他事务均不可对该表进行修改插入操作，直到其他事务commit后，才可以进行插入修改操作。

2.在Mysql中ENUM的用法是什么？

直接上代码

//建表语句，enum类型，可以指定后边的具体枚举值，类似与java的枚举值
CREATE TABLE `test_enum` (
  `ID` bigint NOT NULL COMMENT 'PRIMARY KEY BATCH ID',
  `STATUS` enum('NAME','SEX') NOT NULL,
  PRIMARY KEY (`ID`)
);
//插入时候同样可以直接使用'NAME'或者'SEX',1(等同于'NAME'),2(等同于'SEX')
insert into test_enum values(1,'NAME');
insert into test_enum values(2,'SEX');
insert into test_enum values(3,1);
insert into test_enum values(4,2);
//查询
select * from test_enum;
//输出
1    NAME
2    SEX
3    NAME
4    SEX
//修改增加Enum类型
ALTER TABLE test_enum MODIFY COLUMN STATUS enum('NAME','SEX','TTTT')

3.CHAR和VARCHAR的区别？

（1）char类型时定长的类型，即当定义的是char(10)，输入的是"abc"这三个字符时，它们占的空间一样是10个字节，包括7个空字节。当输入的字符长度超过指定的数时，char会截取超出的字符。而且，当存储char值时，MySQL是自动删除输入字符串末尾的空格。char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。
（2）varchar(n)类型用于存储可变长的，长度为n个字节的可变长度且非Unicode的字符数据。n必须是介于1和8000之间的数值，存储大小为输入数据的字节的实际长度+1或2，比如varchar(10), 然后输入abc三个字符，那么实际存储大小为3个字节。除此之外，varchar还需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示。
（3）从占用空间上考虑，varcahr较合适；从效率上考虑，用char合适。二者之间需要权衡。

4.drop,delete与truncate的区别

（1）delete和truncate都会清除表数据，但不会将整个表干掉，而drop可以将整个表干掉，也可以适用一些索引，约束，触发器等，都是直接干掉操作。
（2）TRUNCATE会清理这个表和索引所占用的空间，这些空间都会恢复到初始大小，DELETE操作不会减少表或索引所占用的空间，之前数据即使被删掉也占用空间。

5.局部性原理与磁盘预读

（1）局部性原理，当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
（2）磁盘的读取速度要比内存慢很多，为了提高效率，要尽量减少磁盘I/O，所以就是每次读取都多读一些，也就是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。因为磁盘是顺序读取的，因此预读只是在原有的基础上再多继续读一些，返回到内存中，速度也是非常快的。
（3）实际上这个是为了引入B+树以及B-树的存储索引结构，提高索引查询的效率。但是这个我是不太懂，后边抽时间输出一下，树，B+树、B-树，红黑树，写一篇。

6.数据库范式

（1）第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。例如，地址字段，实际上可以拆解成为省市区街道地址的，拆分完成后，无法再次拆分才是满足第一范式的。
（2）第二范式，首先要满足第一范式，每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。可以看下面的例子
订单信息表，其中会出现重复的两条订单数据，后边的客户，所属单位，联系方式都是重复的，需要拆解成子表

拆解后的表，可以看到订单信息表只有一条数据，而且没有重复的信息

（3）第三范式同样要先满足第一第二范式，另外需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。例如，学生表（学生编号，学生姓名，班级名称，学校名称，学校地址，学校电话），其中后边两个地址和电话和学生不是直接关联的，需要单独拆成学校表。拆解后学生表（学生编号，学生姓名，班级名称，学校编号），学校表（学校编号，学校地址，学校电话）。

7.存储过程与触发器的区别

（1）存储过程，是一段可以重复执行的SQL逻辑代码，通过指定的输入参数（可不输入），按照SQL语句执行具体操作，最终返回指定的输出结果，通过其他存储过程或者EXECUTE方式调用（Java代码中可以使用该方式调用）
（2）触发器，也是一段SQL逻辑代码，但是是由指定的表在特定的操作下触发（例如update，insert等操作时）的执行语句。比较常见的是多个表关联紧密，当删除了某个表的数据，需要同时删除其他关联表的数据，可以使用触发器来操作。

8.什么情况下设置了索引但无法使用

（1）表中数据较少，建立索引无意义，没有全表扫描速度快。
（2）该列数据重复度高，大量的重复数据，因此在该列上建立索引查询时，仍然需要通过全表扫描。
（3）like '%dfdfd'模糊匹配时，注意是前面模糊匹配，后边匹配dfdfd，需要匹配大量数据，无法走索引。当修改为like 'dfdfd%'，有可能走索引也可能不走索引，主要看匹配的数据量超过30%则不走索引。
（4）使用OR时候，前后两部分中存在某一部分没有建立索引。
（5）字段存在索引，但条件中使用函数时，例如SUBSTR(COLUMN,2,5) = 'abcde',这种情况不走索引。如果需要走索引要单独建立函数索引。
（6）数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）。

9.什么情况下不宜建立索引？

（1）字典表这种或者数据很少的，几十条数据，建立索引反而慢，因为要多一道手续。
（2）字段重复度极高，往往超过三分之一的重复数据的字段，即使建立索引，也是全表查询。
（3）没有查询意义的字段，不应该建立索引。
（4）另外一个表索引建议在三个以及以下，否则如果查询语句比较多的时候会造成索引冲突，反而不走最佳的索引。

10.解释MySQL外连接、内连接与自连接的区别

（1）内连接，最常用的，关联表直接通过where条件写出的

//第一种写法
select * from taba a INNER JOIN tabb b on a.key1 = b.key1;
//第二种写法，省去了INNER JOIN
select * from taba a,tabb b where a.key1 = b.key1;

（2）外连接，分为左连接（以左表数据为准，例如班级和学生，班级左表，学生右表，有的班级还没有分学生，这样能够把所有的班级的学生查询出来，有的班级即使没有学生也会展示，只是学生部分为null），右连接（以右表数据为准，和左连接反着，实际就是把班级表放右边，学生左表，结果和左连接类似，只是这次以右表为准），全外连接（这个特殊，班级左表，学生右表，存在没有学生的班级，也存在没有分班的学生，查询的结果展示有学生的班级，展示班级和学生；没有学生的班级，学生为null，只展示班级；没有学生的班级，班级为null，只展示学生，这样把所有的数据都展示出来）

//左连接
SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key;
//右连接
SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key;
//全外连接
SELECT * FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key;

（3）自连接，不太常用，就是有时候数据自己与自己关联，比如全班考试，想知道比张三分数高的学生，可以写成一条SQL

//复杂写两次
select a.score from student a where a.name='张三';
//上面SQL得到15分的结果
select a.* from student a where a.socre>15;
//直接捅过自连接写成一条SQL
select b.* from student a,student b 
where a.name = '张三' and b.socre>a.score;

11.如何进行SQL优化

（1）所有的查询SQL建议按照索引查询，查询非常频繁的如果不走索引，查询速度慢，会导致服务响应慢。客户取消后再次请求操作，进而会导致更多的查询甚至导致服务宕机。
（2）update时候，尽量按照primary key作为条件来更新，效率更高，退而求其次也要按照索引更新。
（3）尽量不要频繁更新索引字段，会导致频繁更改索引存储，更新效率也低。
（4）近期发现的一个问题，由于要把全表一百万的数据更新，另外每次查询都是全表查询，自己限制在了100条数据，也就是sql的limit 100，这样每查询一次几秒，我循环20000次，这样损耗了我8个小时才循环完毕，因此如果是全表查询，记得limit要修改的高一些，limit 10000，实际上和limit 100的时间差不多，即使几秒钟，但是循环次数少了，反而减少了很多时间。
（5）大字段上不能建立索引，因为为了这种字段建立索引，会占用大量空间，反而效率不高。
（6）索引字段上使用函数后，是无法走索引的。

12.完整性约束包括哪些？

（1）实体完整性：规定表的每一行在表中是惟一的实体。例如，商品表，那么每一条数据代表唯一一件商品，不能另外再存在一条数据也是这一件商品了。
（2）域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。例如，性别只能是男女未知，不能再有其他了。
（3）参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。例如，表A的外键字段COLUMN_A，是B表的主键，表A的COLUMN_A的数据必须是表B中的数据，不能出现表B中没有的数据。
（4） 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。例如，一些非空约束、唯一约束、检查约束、主键约束、外键约束。

13.Mysql 的存储引擎,myisam和innodb的区别。

（1）存储：Myisam，每个MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开头，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。Innodb，基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。这个说的实际不对，当表空间不够时候可以在增加新的表空间，innodb表大小是可以超过2GB的。
（2）事务：MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持；InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能。
（3）增删改查：如果执行大量的SELECT，MyISAM更快。大量修改插入操作时候，InnoDB效率更高。DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，这块不懂了，那么MyISAM每次删除完成数据把其他的数据是重新建立表文件，删除旧文件？
（4）AUTO_INCREMENT：MyISAM，每表一个AUTO_INCREMEN列的内部处理，MyISAM为INSERT和UPDATE操作自动更新这一列，效率更高，AUTO_INCREMENT类型的字段可以和其他字段一起建立联合索引；InnoDB，AUTO_INCREMEN字段有自动增长计数器，存储在主内存中，AUTO_INCREMENT类型的字段必须有该字段的独立索引。
（5）行数：MyISAM存储了行数，InnoDB没有存储行数，因此select count(*)时候，MyISAM查询行数记录就可以返回，但是InnoDB需要全表查询一遍。
（6）锁，MyISAM的增删改查都是表锁；InnoDB提供行锁，不加锁读取，也有表锁。

14.MYSQL的主从延迟怎么解决

（1）网络延迟导致的主从延迟，优化网络，增加带宽，满足网络同步的速度要求。
（2）从数据库配置低，当主数据库大量并发下，从数据库无法及时完成数据库操作，因此需要增加从数据库配置，已达到大量并发的同步操作。
（3）Slave调整参数，关闭binlog，能够不将binlog同步到磁盘，修改innodb_flush_log_at_trx_commit参数值为0，不把日志flush到磁盘，这两项修改能使从数据库的性能提升，但是数据安全性有所下降。

15.MVCC的含义，如何实现的

（1）多版本并发控制，读不加锁，读写不冲突。
（2）表中有一个隐藏列，trx_id操作的事务id，每次记录操作增删改查，都会对这个值修改，修改为当前操作的事务id。
（3）另外MVCC记录了几个事务ID，分别是m_ids：在当前事务开始时，当前系统中活跃的事务id列表；min_trx_id：在当前事务开始时，当前系统中活跃的最小的事务id，也就是m_ids中的最小值；max_trx_id：在当前事务开始时，系统应该分配给下一个事务的事务id值；creator_trx_id：当前事务id。
（4）上面四个id记住后，就可以来继续解释了，如果被访问的数据的trx_id=creator_trx_id，表示这条数据是自己当前事务修改的，当然可以读取了；如果trx_id<min_trx_id，表示这条数据比当前事务开始时活跃的最早的事务还要早，也就是在当前事务之前这条数据已经修改完了，同样是可以读取的；trx_id>=max_trx_id，表示当前事务开始时，最大的活跃事务比这条数据的trx_id要早，说明时当前事务开始后，这条数据才修改的，说明无法读取这条数据，需要找到之前的事务版本（最接近当前事务id的一条记录）来展示，如果没有早于当前事务的任何一条记录，则无数据。

16.事务是如何通过日志来实现的，说得越深入越好

（1）先来了解undo log和redo log
（2）undo Log是实现事务的原子性的关键，也就是修改数据时的备份记录，如果出现问题，方便回滚数据到原始状态，例如，事务开始，数据A的COLUMN_A当前值为10，update操作+10，首先将数据A的状态记录到undo Log内存中，COLUMN_A的值为10，然后修改内存中的数据A，COLUMN_A修改为20，下一步将undo log写入到磁盘中，最后一步将内存中的修改记录写入到磁盘，也就是COLUMN_A为20的记录存到磁盘中，事务提交，事务最终结束。在最终的事务提交前失败，都会进行回滚操作，将数据以及内存的记录恢复到之前的状态。
（3）redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。Redo Log记录的是新数据的备份。它是在事务提交之前，将新数据状态记录到redo Log中，记录的数据的最新状态。例如上面的+10操作，实际上是在修改记录写入到磁盘后，记录redo Log，之后再提交事务。

17.锁的优化策略

（1）所有SQL语句尽量都通过索引执行，避免行级锁升级成为页级锁甚至表级锁。
（2）合理设计索引，尽量缩小行锁的锁定范围，范围过大可能导致其他查询条件的执行。
（3）避免使用通过一定范围的查询条件，因为会产生页级锁甚至表锁，影响效率。
（4）使用事务时，事务执行时间不要太长，时间过长可能影响其他的查询或者增删改查的执行。
（5）避免产生死锁

18.乐观锁和悲观锁是什么，INNODB的标准行级锁有哪2种，解释其含义。

（1）乐观锁，用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，更新失败。
（2）悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现的。
（3）行级锁标准有共享锁和排他锁，这两个都是悲观锁。
（4）共享锁，允许多个事务读取数据，但是禁止排他锁来抢占。
（5）排他锁，抢占成功的排他锁事务可以更新数据，其他事务均不可执行，禁止共享锁或者其他排他锁抢占，直到当前事务完成后才可以。

19. db 里面 user 和 schema的区别

（1）user是指具体登陆到数据库的用户，需要有账号密码
（2）schema指的是具体的数据库，同一个数据库实例可以建立多个数据库schema
（3）具体user可以操作哪些schema，需要dba用户为该user赋权，才可以在schema中建表删表（DDL操作）

20. 主键和外键的区别

（1）主键是表数据的唯一标识，每条数据都是唯一的标识对应的字段，比如班级表，班级ID就是唯一的标识。
（2）外键是当前表中对应其他表数据关联的字段，表示当前数据与其他表数据的关联关系，比如学生表中的班级ID，这个就可以是外键，对应关联班级表中的信息。
（3）主键在表中数据不能重复；外键字段在当前表可以重复，例如几个学生都在班级1中。

21. MySQL的自增ID用完了，会怎么样？

（1）先提供答案，新插入数据，会报唯一索引重复数据错误

//建表语句
CREATE TABLE `t_test` (
 `ID` int NOT NULL AUTO_INCREMENT,
 `name` varchar(10),
 PRIMARY KEY (`ID`)
);
//首先插入倒数第二大的id值
insert into t_test values (2147483646, 'test');
//再次插入，可以成功
insert into t_test (name) values ('test1');
//再次插入test2，报错
insert into t_test (name) values ('test2');
//错误信息
Duplicate entry '2147483647' for key 't_test.PRIMARY'

（2）因此可以得出结论，超过int的最大值后会报错
（3）可以修改为bigint类型，但是仍然有最大值大约2^64，达到最大值仍然会报错，但是要问一个问题，你的数据会达到这么大么？如果这么大数据量存储到MySQL数据库中，那么查询都是一个很大的问题，设计就不是很合理，不太适合MySQL

22. Mysql count(*)，count(字段)，count(1)的区别

（1）count(1)和count(*)的作用都是检索表中所有记录行的数目，不论其是否包含null值。
（2）count(1)比count(*)效率更高，这个先打个问号，后边第4点第5点有解答
（3）count(字段)的作用是检索表中的这个字段的非空行数，不统计这个字段值为null的记录
（4）那么问题来了，为啥count(1)效率高呢？先看下MySQL的官方文档中的一句话：InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.翻译过来就是这俩我做了同样的处理，没有谁效率更高，都一样。
（5）那么MySQL另一大引擎，MyISAM中呢？表会直接存储数据总行数，因此如果不带条件，count(*)会直接从这个总行数中查询，速度很快。但是如果带条件呢？同样是一行一行的读取，那么这个时候呢？我需要找数据跑一下，等我跑完再更新吧。

23. MySql中数据类型datetime和timestamp区别

（1）存储内容（存储到数据库的格式），datetime为YYYY-MM-DD HH:MM:SS，固定占用8字节。timestamp为1970-01-01 00:00:00开始到现在的毫秒数，固定占用4或7字节。
（2）均可以支持毫秒，datetime(n)表示可以支持n位的毫秒，timestamp不带毫秒是4字节，带毫秒数时就变成了7字节。因此当只有4字节时，其最大只能记录到'2038-01-19 00:00:00'。
（3）均可以设置默认值，可设置初始化值为当前时间，以及数据更新时自动更新修改时间。
例如建表语句汇总：default current_timestamp表示默认当前时间，on update current_timestamp表示修改数据时自动更改为当前时间。
create_time datetime not null default current_timestamp on update current_timestamp
update_time timestamp not null default current_timestamp on update current_timestamp
（4）timestamp的由于记录的是毫秒数，因此可以有时区属性。在任何时区不同的地方，都可以转化为当地的时间。缺点是存储的时间范围有局限性（1970-01-01 00:00:00至2038-01-19 00:00:00）。
（5）什么时候使用timestamp合适呢？当系统记录时间范围不会超过（1970-01-01 00:00:00至2038-01-19 00:00:00）范围，服务涉及跨时区的业务时，推荐使用timestamp类型。
（6）什么时候使用datetime合适呢？当系统记录时间范围会超过（1970-01-01 00:00:00至2038-01-19 00:00:00）范围，例如生日，服务不涉及跨时区的业务时，推荐使用datetime类型。

24. 什么是数据库索引？

（1）索引（index）是帮助MySQL高效获取数据的数据结构（有效），在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
（2）说人话就是，帮助MySQL高效的查询出数据的数据结构叫做索引，这个索引是单独存储在数据表数据之外的位置。

25. MySQL的索引类型有哪些？

MySQL中的索引类型有以下几种，普通索引，唯一索引，主键索引，组合索引，全文索引
（1）普通索引，最基本的索引，它没有任何限制。

CREATE INDEX index_name ON table(column(length))

（2）唯一索引，索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON table(column(length))

（3）主键索引，特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) NOT NULL,
    PRIMARY KEY (`id`)
);

（4）组合索引，指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，但是如果数据重复过多不一定触发，建议组合索引中的多个字段共同使用。使用组合索引时遵循最左前缀集合（最左前缀：
顾名思义，就是最左优先，如下例子，当创建了lname_fname_age组合索引，相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引，也就是优先走lname单列索引）。

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

（5）全文索引，主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,    PRIMARY KEY (`id`),
    FULLTEXT (content)
);
CREATE FULLTEXT INDEX index_content ON article(content);