相关文章推荐
笑点低的消防车  ·  mysql解决唯一索引重复导致的插入失败问题 ...·  2 月前    · 
直爽的红烧肉  ·  mysql查询字段中带空格的值的sql语句, ...·  2 周前    · 
可爱的牙膏  ·  刻晴大战史莱姆,,_手机游戏热门视频·  1 年前    · 
从未表白的胡萝卜  ·  我的房客是妖怪3:父亲死了,儿子却站在头上撒 ...·  2 年前    · 
内向的花卷  ·  封神演义故事:五仙议定封神榜,共抗天劫,少年 ...·  2 年前    · 
强健的回锅肉  ·  kindle退出中国,我电子书怎么办?_Ki ...·  2 年前    · 
冷静的口罩  ·  海马汽车为保壳拟出售研发公司,背后是销量崩塌 ...·  2 年前    · 
Code  ›  关于MySQL的一些骚操作——提升正确性,抠点性能开发者社区
mysql 社区功能 sql优化 mysql update语句
https://cloud.tencent.com/developer/article/1547000
想旅行的凉面
2 年前
作者头像
Java_老男孩
0 篇文章

关于MySQL的一些骚操作——提升正确性,抠点性能

前往专栏
腾讯云
开发者社区
文档 意见反馈 控制台
首页
学习
活动
专区
工具
TVP
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP
返回腾讯云官网
社区首页 > 专栏 > 老男孩成长之路 > 正文

关于MySQL的一些骚操作——提升正确性,抠点性能

发布 于 2019-12-02 22:23:56
1.2K 0
举报

概要

回顾以前写的项目,发现在规范的时候,还是可以做点骚操作的。 假使以后还有新的项目用到了 MySQL ,那么肯定是要实践一番的。 为了准备,创建测试数据表(建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci,感兴趣的读者可以自行搜索这两个配置):

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '编号',
  `name` varchar(30) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入冲突时更新数据

SQL执行插入时,可能因为种种原因插入失败,比如UNIQUE索引冲突导致插入失败。比如某个不晓得DBA插入了一条错误的学生记录("3", "小明"),悲剧的是小明的编号是1。常规做法就是判断当前的 数据库 记录中是否存在小明的记录,如果有则更新其对应其编号,否则就插入小明的记录。当然存在更好的做法:

INSERT INTO student(`no`, `name`) VALUES (3, "xiaoming");
INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming"), (2,"xiaohong")
ON DUPLICATE KEY UPDATE `no` = VALUES(`no`);

那就是使用ON DUPLICATE KEY UPDATE,这是mysql独特的语法(语句后面可以放置多个更新条件,每个条件使用逗号隔开即可)。需要注意,这里的VALUES(no)是将冲突的no数值更新为用户插入数据中的no,这样每条冲突的数据就可以动态的设置新的数值。

忽略批量插入失败中的错误

批量插入比单条数据挨个插入,普遍会提高性能以及减少总的网络开销。但是,假如批量插入的数据中心存在一个臭虫,在默认的情况下,这就会导致批量插入失败(没有一条数据插入成功)。当然,我们可以选择忽略, MongoDB 都能够做到的事情,MySQL自然是可以做到。

INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming");
INSERT IGNORE INTO student(`no`, `name`) VALUES (1, "xiaoming"), (2,"xiaohong"),(3, "xiaowang");

只需要在批量插入的语句中,插入IGNORE,那么某几条数据的插入失败就会被忽略掉,正确的数据依然可以插入库中。但是,我建议这个功能谨慎使用,使用mysql数据库本身就是看中数据的正确性,没必要为了批量插入的性能而自动放弃数据的正确性,如果真心觉得这个数据不重要,那么为什么不将此数据存入NoSQL中呢,MongoDB就是不错的选择。

IGNORE还有些副作用,感兴趣的可以自行查询。

使用JOIN替换子查询

MySQL的子查询优化不是太好,它的运行有点反我们的直觉(我们写的代码终究会在某些时候和我们的直觉相悖,这大概就是优化产生的根源之一吧)。其中最糟糕的一类是WHERE子句中包含IN的子查询语句(详情可见《高性能MySQL》一书的6.5章节,标题名字起得就很nice,为MySQL查询优化器的局限性)。概括下就是在部分情况下,在部分情况下MySQL可能会在挨个执行外部记录时执行子查询,如果外部记录数量较大,那么性能就会堪忧。

SELECT * FROM student WHERE no > (SELECT no FROM student WHERE `name`='xiaoming');
SELECT s.* FROM student s JOIN (SELECT no FROM student WHERE `name`='xiaoming') t ON s.no > t.no;

看上述代码,可以知道使用JOIN还是比较容易替换子循环,代码虽然会稍显晦涩,但是也许可以避免在并发量大的某个晚上你被叫起来检讨自己的错误。MySQL一直在优化子查询,在部分条件下子查询可能会比JOIN具有更高的效率,因此在有时间进行验证的情况下选择最佳的SQL语句。

JOIN中的WHERE和AND坑

为了更好的说明坑,我这里需要创建一个新的表,并在原来的学生表中添加字段:

CREATE TABLE `class` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(10) unsigned NOT NULL COMMENT '编号',
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
推荐文章
笑点低的消防车  ·  mysql解决唯一索引重复导致的插入失败问题_java mysql 因为联合唯一索引插入失败 抛错
2 月前
直爽的红烧肉  ·  mysql查询字段中带空格的值的sql语句,并替换开发者社区
2 周前
可爱的牙膏  ·  刻晴大战史莱姆,,_手机游戏热门视频
1 年前
从未表白的胡萝卜  ·  我的房客是妖怪3:父亲死了,儿子却站在头上撒尿_哔哩哔哩_bilibili
2 年前
内向的花卷  ·  封神演义故事:五仙议定封神榜,共抗天劫,少年杨戬奉命下山_鸿钧_天庭_元始天尊
2 年前
强健的回锅肉  ·  kindle退出中国,我电子书怎么办?_Kindle_用户_软件
2 年前
冷静的口罩  ·  海马汽车为保壳拟出售研发公司,背后是销量崩塌难解_36氪
2 年前
今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号