Java岗大厂面试百日冲刺 - 日积月累，每日三题【Day17】—— 数据库4

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

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

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

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

"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)

（老板：插入前10w数据，插入5w数据后还剩8w数据？？，咱们家数据让你喂狗了吗！！）

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分钟左右。

下面代码是测试例子：

1普通循环插入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分钟左右↓

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 list= new ArrayList();

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);

mybatis Mapper中的sql写法：

INSERT INTO db.user_info

( id,

username,

password,

price,

hobby)

values

(null,

#{item.userName},

#{item.password},

#{item.price},

#{item.hobby})

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

注意：但是当批量操作数据量很大的时候。例如我插入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;

结尾

这不止是一份面试清单，更是一种”被期望的责任“，因为有无数个待面试者，希望从这篇文章中，找出通往期望公司的”钥匙“，所以上面每道选题都是结合我自身的经验于千万个面试题中经过艰辛的两周，一个题一个题筛选出来再次对好答案和格式做出来的，面试的答案也是再三斟酌，深怕误人子弟是小，影响他人仕途才是大过，也希望您能把这篇文章分享给更多的朋友，让他帮助更多的人，帮助他人，快乐自己，最后，感谢您的阅读。

由于细节内容实在太多啦，在这里我花了两周的时间把这些答案整理成一份文档了，在这里只把部分知识点截图出来粗略的介绍，每个小节点里面都有更细化的内容！
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
(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;

结尾

[外链图片转存中…(img-GgieFGau-1714684901427)]

这不止是一份面试清单，更是一种”被期望的责任“，因为有无数个待面试者，希望从这篇文章中，找出通往期望公司的”钥匙“，所以上面每道选题都是结合我自身的经验于千万个面试题中经过艰辛的两周，一个题一个题筛选出来再次对好答案和格式做出来的，面试的答案也是再三斟酌，深怕误人子弟是小，影响他人仕途才是大过，也希望您能把这篇文章分享给更多的朋友，让他帮助更多的人，帮助他人，快乐自己，最后，感谢您的阅读。

由于细节内容实在太多啦，在这里我花了两周的时间把这些答案整理成一份文档了，在这里只把部分知识点截图出来粗略的介绍，每个小节点里面都有更细化的内容！
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！