Java岗大厂面试百日冲刺【Day17】—— 数据库4

转载自: Java岗大厂面试百日冲刺【Day17】—— 数据库4

本文已获得原作者 _陈哈哈 授权并经过重新整理规划后发布。

本栏目Java开发岗高频面试题主要出自以下各技术栈：Java基础知识、集合容器、并发编程、JVM、Spring全家桶、MyBatis等ORMapping框架、MySQL数据库、Redis缓存、RabbitMQ消息队列、Linux操作技巧等。

面试题1：MySQL中你用过的INSERT插入方式都有哪几种？

我们常见的插入方法一般有这几种，普通插入语句、插入或更新、插入或替换、插入或忽略，应用在不同的场景中，在功能方面呢也会有所不同。下面我们分别过一下；

1、普通插入语句

INSERT INTO table (`a`, `b`, `c`, ……) VALUES ('a', 'b', 'c', ……);

这里不再赘述，注意顺序即可，不建议小伙伴们去掉前面括号的内容，别问为什么，容易被同事骂。

2、插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"语句：

情景示例：这张表存了用户历史充值金额，如果第一次充值就新增一条数据，如果该用户充值过就累加历史充值金额，需要保证单个用户数据不重复录入。

这时可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"语句。

注意事项："INSERT INTO … ON DUPLICATE KEY UPDATE …"语句是基于唯一索引或主键来判断唯一（是否存在）的。如下SQL所示，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

-- 用户陈哈哈充值了30元买会员
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员') 
   ON DUPLICATE KEY UPDATE  total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员';
 
-- 用户陈哈哈又充值了100元买瞎子至高之拳皮肤
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
   VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '购买盲僧至高之拳皮肤') 
   ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='购买盲僧至高之拳皮肤';

• 若username='chenhaha’的记录不存在，INSERT语句将插入新记录，否则，当前username='chenhaha’的记录将被更新，更新的字段由UPDATE指定。

对了，ON DUPLICATE KEY UPDATE为MySQL特有语法，比如在MySQL迁移Oracle或其他DB时，类似的语句要改为MERGE INTO语法，兼容性让人想骂街。但没办法，就像用WPS写的xlsx用Office无法打开一样。

3、插入或替换

如果我们想插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。

情景示例：这张表存的每个客户最近一次交易订单信息，要求保证单个用户数据不重复录入，且执行效率最高，与数据库交互最少，支撑数据库的高可用。

此时，可以使用"REPLACE INTO"语句，这样就不必先查询，再决定是否先删除再插入。

"REPLACE INTO"语句是基于唯一索引或主键来判断唯一（是否存在）的。

注意事项：如下SQL所示，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

-- 20点充值
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值');
 
-- 21点买皮肤
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
   VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');

• 若username='chenhaha’的记录不存在，REPLACE语句将插入新记录（首次充值），否则，当前username='chenhaha’的记录将被删除，然后再插入新记录。

id不要给具体值，不然会影响SQL执行，业务有特殊需求除外。

小tips：
ON DUPLICATE KEY UPDATE：如果插入行出现唯一索引或者主键重复时，则执行旧的update；如果不会导致唯一索引或者主键重复时，就直接添加新行。

REPLACE INTO：如果插入行出现唯一索引或者主键重复时，则delete老记录，而录入新的记录；如果不会导致唯一索引或者主键重复时，就直接添加新行。

replace into 与 insert on deplicate udpate 比较：

1. 在没有主键或者唯一索引重复时，replace into 与 insert on deplicate udpate 相同。
2. 在主键或者唯一索引重复时，replace是delete老记录，而录入新的记录，所以原有的所有记录会被清除，这个时候，如果replace语句的字段不全的话，有些原有的比如c字段的值会被自动填充为默认值（如Null）。
3. 细心地朋友们会发现，insert on deplicate udpate只是影响一行，而REPLACE INTO可能影响多行，为什么呢？

上面REPLACE影响了多行记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引，会怎么样呢？我们早一些数据测试一下。

-- 测试表创建，a,b,c三个字段均有唯一索引
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
-- 插入三条测试数据
INSERT into table1 VALUES(1,1,1);
INSERT into table1 VALUES(2,2,2);
INSERT into table1 VALUES(3,3,3);

此时table1中已经有了3条记录，a,b,c三个字段都是唯一（UNIQUE）索引

mysql> select * from table1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+---+---+---+
3 rows in set (0.00 sec)

下面我们使用REPLACE语句向table1中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

mysql> REPLACE INTO table1(a, b, c) VALUES(1,2,3);
Query OK, 4 rows affected (0.04 sec)

此时查询table1中的记录如下，只剩一条数据了

mysql> select * from table1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set (0.00 sec)

REPLACE INTO 语法回顾：如果插入行出现唯一索引或者主键重复时，则delete老记录，而录入新的记录；如果不会导致唯一索引或者主键重复时，就直接添加新行。

我们可以看到，在用REPLACE INTO时每个唯一索引都会有影响的，可能会造成误删数据的情况，因此建议不要在多唯一索引的表中使用REPLACE INTO；

4、插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO …语句：情景很多，不再举例赘述。

注意事项：同上，"INSERT IGNORE INTO …"语句是基于唯一索引或主键来判断唯一（是否存在）的，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

-- 用户首次添加
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
   VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 20:00:20');
 
-- 二次添加，直接忽略
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
   VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 21:00:20');

面试题2：见过大量数据同时插入的场景么？有哪些处理方式？给你会怎么设计？

针对高并发插入场景，一般有以下三种处理方式，我们分别来看一下：

1、单条循环插入

我们取10w条数据进行了一些测试，如果插入方式为程序遍历循环逐条插入。在mysql上检测插入一条的速度在0.01s到0.03s之间。逐条插入的平均速度是0.02*100000，也就是33分钟左右。

下面代码是测试例子：

普通循环插入100000条数据的时间测试

@Test
public void insertUsers1() {
    
    User user = new User();
    
    user.setUserName("提莫队长");
    user.setPassword("正在送命");
    user.setPrice(3150);
    user.setHobby("种蘑菇");
    
    for (int i = 0; i < 100000; i++) {
        user.setUserName("提莫队长" + i);
        // 调用插入方法
        userMapper.insertUser(user);
    }
}

执行速度是30分钟也就是0.018*100000的速度。可以说是很慢了

发现逐条插入优化成本太高。然后去查询优化方式。发现用批量插入的方法可以显著提高速度。

将100000条数据的插入速度提升到1-2分钟左右↓

修改SQL语句批量插入

insert into user_info （user_id,username,password,price,hobby）
    values （null,'提莫队长1','123456',3150,'种蘑菇'）,(null,'盖伦','123456',450,'踩蘑菇');

用批量插入插入100000条数据，测试代码如下：

@Test
public void insertUsers2() {
     
    List<User> list= new ArrayList<User>();
	
    User user = new User();
    user.setPassword("正在送命");
    user.setPrice(3150);
    user.setHobby("种蘑菇");
	
    for (int i = 0; i < 100000; i++) {
        user.setUserName("提莫队长" + i);
        // 将单个对象放入参数list中
        list.add(user);
        
    }
    userMapper.insertListUser(list);
}

批量插入使用了0.046s 这相当于插入一两条数据的速度，所以用批量插入会大大提升数据插入速度，当有较大数据插入操作是用批量插入优化

批量插入的写法：

dao定义层方法:

Integer insertListUser(List<User> user);

mybatis Mapper中的sql写法：

<insert id="insertListUser" parameterType="java.util.List">
    INSERT INTO `db`.`user_info`
        ( `id`,
          `username`,
          `password`,
          `price`,
          `hobby`) 
     values
    <foreach collection="list" item="item" separator="," index="index">
        (null,
        #{item.userName},
        #{item.password},
        #{item.price},
        #{item.hobby})
    </foreach>

</insert>

这样就能进行批量插入操作：

注意：但是当批量操作数据量很大的时候。例如我插入10w条数据的SQL语句要操作的数据包超过了1M，MySQL会报如下错:

报错信息：

Mysql You can change this value on the server by setting the max_allowed_packet' variable. Packet for query is too large (6832997 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

解释：

用于查询的数据包太大（6832997> 1048576）。 您可以通过设置max_allowed_packet的变量来更改服务器上的这个值。

通过解释可以看到用于操作的包太大。这里要插入的SQL内容数据大小为6M 所以报错。

解决方法：

数据库是MySQL57，查了一下资料是MySQL的一个系统参数问题:

max_allowed_packet，其默认值为1048576(1M)；

查询：

show VARIABLES like '%max_allowed_packet%';

修改此变量的值：MySQL安装目录下的my.ini(windows)或/etc/mysql.cnf(linux) 文件中的[mysqld]段中的

将max_allowed_packet = 1M，更改为20M(或更大，如果没有这行内容，增加这一行)，如下图

保存，重启MySQL服务。现在可以执行size大于1M小于20M的SQL语句了。

但是如果20M也不够呢？

3、分批量多次循环插入

如果不方便修改数据库配置或需要插入的内容太多时，也可以通过后端代码控制，比如插入10w条数据，分100批次每次插入1000条即可，也就是几秒钟而已；当然，如果每条的内容很多的话，另说。

追问1：如果插入速度依旧很慢，还有没有其他的优化手段？

方案A：通过show processlist;命令，查询是否有其他长进程或大量短进程抢占线程池资源 ？看能否通过把部分进程分配到备库从而减轻主库压力；或者，先把没用的进程kill掉一些？（手动挠头o_O）

方案B：大批量导数据，也可以先关闭索引，数据导入完后再打开索引

• 关闭表索引：ALTER TABLE user_info DISABLE KEYS;
• 开启表索引：ALTER TABLE user_info ENABLE KEYS;

面试题3：你对建表字段是否该使用not null这个问题怎么看?

之前我收到过一条短信，内容是“尊敬的 null 你好，XXX”，当时我就笑了。真是外行看热闹，内行看门道，这是程序员都能 Get 的笑点，说明程序没有正确从数据库获取到我的姓名，然后把空值格式化为了 null。

出现这种情况的原因一般是数据库的数据问题造成的，我大胆猜测有以下几种场景：

1. 首次名称入库时出错，把我的名称字段填写失败，MySQL默认成 null 值，业务层查询返回时格式化成了’null’字符串；
2. 用户注册时故意在名称中加了\n、\r等下流的数据，导致查询时返回了空字符串’’，正则校验时又出现空指针；
3. 用户名设置为’null’

在 MySQL 中，NULL 表示未知的数据，我们在设计表时，常常有老司机告诉我们：

字段尽可能用NOT NULL，而不是NULL，除非有特殊情况！

但却都只给结论也不说明原因，就像喝鸡汤不给勺子一样，有点膈应，让不少同学对这些结论只知其一，不明其二。坦白说，老司机也不一定清楚为啥，可能就是他领导让他这么干而已~~

就像我领导，记得我刚来公司时，他语重心长的叮嘱我：MySQL 建表字段记得用 not null，不然就滚蛋！🙃🙃🙃

先看看 MySQL 官网文档提到 NULL 的地方：

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

翻译：

NULL列在行中需要额外的空间以记录其值是否为NULL。 对于MyISAM表，每个NULL列都多花一位，四舍五入到最接近的字节。

其实这是官方在委婉的告诉你，别用NULL就完了~~

下面我们来看看NULL值有多少坑，这里我会结合 NULL 字段，和你着重说明 sum 函数、count 函数，以及查询条件为 NULL 值时可能踩的坑。

先给出我们的测试表：

mysql> select * from demo0527;
+----+------------+-------+------+
| id | name       | money | age  |
+----+------------+-------+------+
|  1 | 陈哈哈1    |   100 | NULL |
|  2 | 陈哈哈2    |  NULL | NULL |
|  3 | NULL       |   100 | NULL |
+----+------------+-------+------+
3 rows in set (0.00 sec)

我们通过下面三个用例，结合数据库中表 demo0527 的 null 值来看看：

• 示例一：通过 sum 函数统计一个只有 NULL 值的列的总和，比如 SUM(age)；
• 示例二：select 记录数量，count 使用一个允许 NULL 的字段，比如 COUNT(name)；
• 示例三：使用 =NULL 条件查询字段值为 NULL 的记录，比如 money=null 条件。

以上三个示例对应的测试SQL如下：

SELECT SUM(age) from demo0527;
SELECT count(name) from demo0527;
SELECT * FROM demo0527 WHERE money=null;

查询结果：

mysql> SELECT SUM(age) from demo0527;
+----------+
| SUM(age) |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(name) from demo0527;
+-------------+
| count(name) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM demo0527 WHERE money=null;
Empty set (0.00 sec)

得到的结果，分别是 NULL、2、空List；显然，这三条 SQL 语句的执行结果和我们的期望不同：

• 虽然表中的 age 都是 NULL，但 SUM(age) 的结果应该是 0 才对；
• 虽然第三行记录的 name 是 NULL，但查记录总行数应该是 3 才对；
• 使用 money=NULL 并没有查询到 id=2 的记录，查询条件失效。

三个示例的原因分别是：

1. MySQL 中 sum 函数没统计到任何记录时，会返回 null 而不是 0，可以使用 IFNULL(null,0) 函数把 null 转换为 0；
2. 在MySQL中使用count（字段），不会统计 null 值，COUNT(*) 才能统计所有行；
3. MySQL 中使用诸如 =、<、> 这样的算数比较操作符比较 NULL 的结果总是 NULL，这种比较就显得没有任何意义，需要使用 IS NULL、IS NOT NULL 或 ISNULL() 函数来比较。有兴趣的同学可以看一下《有意思，原来SQL中的NULL是这么回事儿》

让我们根据上述原因来相应修改一下 SQL：

SELECT IFNULL(SUM(age),0) FROM demo0527;
SELECT COUNT(*) FROM demo0527;
SELECT * FROM demo0527 WHERE age IS NULL;

修改后我们查询的结果就是我们想要的了：

mysql> SELECT IFNULL(SUM(age),0) FROM demo0527;
+--------------------+
| IFNULL(SUM(age),0) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM demo0527;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM demo0527 WHERE age IS NULL;
+----+------------+-------+------+
| id | name       | money | age  |
+----+------------+-------+------+
|  1 | 陈哈哈1    |   100 | NULL |
|  2 | 陈哈哈2    |  NULL | NULL |
|  3 | NULL       |   100 | NULL |
+----+------------+-------+------+
3 rows in set (0.00 sec)

另外值得注意的是，不仅money=NULL 条件查不到字段值为 NULL 的记录，当我们使用 SELECT * FROM demo0527 WHERE money <>100; 来查询id=2这行时，也是查不到任何数据的。我们在工作中往往会在这里栽跟头，导致统计不准确，给大家Mark一下。

mysql> SELECT * FROM demo0527 WHERE money <>100;
Empty set (0.02 sec)

可见MySQL库中的NULL值很容易导致我们在统计、查询表数据时出错，这里有些同学可能会问有没有性能上的提升，算不算SQL优化，其实把NULL列改为NOT NULL带来的性能提升可以忽略，除非确定它带来了问题，否则不需要把它当成优先的优化措施。