情景示例:这张表存的每个客户最近一次交易订单信息,要求保证单个用户数据不重复录入,且执行效率最高,与数据库交互最少,支撑数据库的高可用。
此时,可以使用"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条即可,也就是几秒钟而已;当然,如果每条的内容很多的话,另说。。
-
方案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。
出现这种情况的原因一般是数据库的数据问题造成的,我大胆猜测有以下几种场景:
-
首次名称入库时出错,把我的名称字段填写失败,MySQL默认成 null 值,业务层查询返回时格式化成了’null’字符串;
-
用户注册时故意在名称中加了
\n、\r
等下流的数据,导致查询时返回了空字符串’’,正则校验时又出现空指针; -
用户名设置为’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;
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
![img](https://i-blog.csdnimg.cn/blog_migrate/ab42f6c38266f51fa3d8528c4ba8ab7b.jpeg)
总结
我们总是喜欢瞻仰大厂的大神们,但实际上大神也不过凡人,与菜鸟程序员相比,也就多花了几分心思,如果你再不努力,差距也只会越来越大。
面试题多多少少对于你接下来所要做的事肯定有点帮助,但我更希望你能透过面试题去总结自己的不足,以提高自己核心技术竞争力。每一次面试经历都是对你技术的扫盲,面试后的复盘总结效果是极好的!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
303)]
[外链图片转存中…(img-4NyTWC6w-1713441997306)]
[外链图片转存中…(img-TR4pVw9E-1713441997308)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
![img](https://i-blog.csdnimg.cn/blog_migrate/ab42f6c38266f51fa3d8528c4ba8ab7b.jpeg)
总结
我们总是喜欢瞻仰大厂的大神们,但实际上大神也不过凡人,与菜鸟程序员相比,也就多花了几分心思,如果你再不努力,差距也只会越来越大。
面试题多多少少对于你接下来所要做的事肯定有点帮助,但我更希望你能透过面试题去总结自己的不足,以提高自己核心技术竞争力。每一次面试经历都是对你技术的扫盲,面试后的复盘总结效果是极好的!
[外链图片转存中…(img-Z3wVHH0e-1713441997311)]
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!