《关于MySQL的一些骚操作》

《关于MySQL的一些骚操作》

概要

回顾以前写的项目,发现在规范的时候,还是可以做点骚操作的。

假使以后还有新的项目用到了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;
ALTER TABLE `student`
 ADD COLUMN `cls_no` smallint(6) unsigned NOT NULL DEFAULT 0 AFTER `no`;

伪造一些数据,假设有4个班级,4班没有相对应的学生。使用如下的查询语句就能发现不同之处:

select c.*, s.`name` from class c left join student s on c.no = s.cls_no and c.no < 4 order by c.no asc;

查询结果如下图所示:

《关于MySQL的一些骚操作》

需要注意的是,此处我再查询条件中设置了 c.no < 4 这一JOIN条件,但是明显的没有起到作用,查询结果中仍然显示了no=4的结果,这是因为此次查询使用的JOIN是LEFT JOIN,class作为左表,在匹配条件无法完全满足的情况下,亦会将左表的所有数据显示出来,引入了NULL值。

换成使用WHERE呢,参照下句:

select c.*, s.`name` from class c left join student s on c.no = s.cls_no where c.no < 4 order by c.no asc;

查询结果如下图所示:

《关于MySQL的一些骚操作》

为什么同样是使用LEFT JOIN,查询结果就不同了呢?这是因为可以认为SQL是分成两部分进行执行的(伪SQL,意思到位):

(1) select c.

, s. name from class c left join student s on c.no = s.cls_no as tmn;

(2)select c.

, s. name from tmp where c.no < 4 order by c.no asc;

需要注意的是,此处首先执行JOIN部分查询,再对查询结果执行WHERE。在执行INNER JOIN时,以上问题还可以忽略,但是如果使用的是LEFT JOIN或者RIGHT JOIN,则需要加倍小心查询条件了。

获取资料:

最后给大家分享一份学习资料,里面包括:(BATJ面试资料、高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)和Java进阶学习路线图。

免费领取加q群:478052716  即可!

分页查询优化

查询的优化,最初是在研究MongoDB的分页查询时学到的,只能说大多数的数据库都是差不多的(当然现在存在时序数据库,分页查询那是更加骚气的)。大多数的分页查询都是类似如下的写法:

SELECT * FROM student WHERE cls_no > 1 LIMIT 1000, 10 ORDER BY id;

这样的写法存在性能损耗,数据库会将所有符合条件的数据查询出来,挨个数到第1000条记录,最后选取前10条记录进行交差。前面的1000条数据,就会显得很浪费,在LIMIT数值很大的情况下,这个性能损耗就是无法忍受的了(百度就会默认禁止查询76页以后的数据)。

因为分页一般是逐页翻下去的(如果是跳页进行查询,那就只能用上面的查询语句慢慢查询搜寻结果了),那么每次分页完都能获取当前的最大ID,我们可以基于ID确定我们的搜索起始点,基于此点向后查询10条满足要求的结果,改动如下(让前端多传一个当前页的最大ID,这个小小的要求当然是可以满足的):

SELECT * FROM student WHERE id > 1000 AND cls_no > 1 LIMIT 10 ORDER BY id;

以上是基于当前的ID是连续ID(其中若干记录没有被物理删除掉),如果是非连续ID,那么基于ID确定起始查询点是不恰当的,此时我们就可以使用JOIN:

SELECT * FROM student WHERE id > 1000 AND cls_no > 1 LIMIT 10 ORDER BY id;

其实,此处我们是id的索引表,从而快速的确定ID,因此查询简化成根据索引表查询的ID确定数据记录(不过需要注意,此处的索引表是无法添加WHERE子句的),因此这种写法在实际环境中几乎是个鸡肋。

UPDATE/DELETE改动多个表记录

工作中,经常需要修改多个表中的关联记录。一般的做法是将相关表中的记录查询出来,再挨个进行修改。如果修改的逻辑较为复杂,那么这样做是没有问题的,但是若是只是简单的修改(比如修改boolean变量),那么可以通过一条SQL语句完成此任务。

SQL中只要提及多个表,那么大致上就会出现JOIN的身影。我们有个需求,就是将3班的学生转移到5班(原有的3班更改为5班),使用JOIN语句的话就可以按照如下方式完成任务。

SELECT s.* FROM student s JOIN (SELECT id FROM student LIMIT 1000, 10) t ON s.id = t.id;

通过JOIN既可以完成此任务,可以拓展到修改多个表中数据内容,也可以扩展至DELETE语句中。

SELECT COUNT(*)/COUNT(1)/COUNT(列名)掉书袋

此处,就简单的总结一下:

  • SELECT COUNT(*):是SQL 92中定义的标准统计行数的语法(所以肯定是做了很多优化的);
  • SELECT COUNT(1): 查询符合条件的行数;
  • SLECT COUNT(列名): 查询符合条件的,且指定的列名所对应值非NULL行数。

对于SELECT COUNT(*)/COUNT(1),在MySQL的官方文档中,其实现思路是一样的,不存在性能差异,那么自然是推荐更加标准的写法了。

Image placeholder
hclxx
未设置
  49人点赞

没有讨论,发表一下自己的看法吧

推荐文章
mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

一通骚操作,我把SQL执行效率提高了10000000倍!

场景我用的数据库是mysql5.6,下面简单的介绍下场景课程表:create table Course(c_id int PRIMARY KEY,name varchar(10))数据100条学生表:

【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 数据库操作:删除数据库

使用MySQL的DROPDATABASE命令可以很容易的删除一个数据库。数据库删除的同时,所属的数据表将一起被删除。如果删除的数据库不存在,则会引发错误。为了避免错误的发生,可以在DROPDATABA

如何实现Laravel的容器

PHP反射 今天我要给大家讲解的是PHP当中使用非常普遍的高级操作:反射。反射在当今几乎所有的PHP框架或者工具中都占用非常重要的角色,就比如Laravel的容器,容器对于Laravel架构来说极其重

VMware斥资27亿美元完成对Pivotal的收购

昨日,VMware公司正式宣布,已经完成对Pivotal公司的收购,这笔交易斥资27亿美元。收购完成后,Pivotal的股权结构将从纽约证券交易所退市,以VMware全资子公司的形式运营。其实,VMw

jquery获取url的参数乱码

jquery获取url的参数乱码//扩展方法获取url参数 $.getUrlParam=function(name){ varreg=newRegExp("(^|&)"+name+"=([^&]*)(

kernel的结构与命令行参数

kernel包结构在RHEL中rpm包是一种cpio格式的压缩文件,它由源文件和元数据(metadata)组成。而在rpm包中kernelrpm比较特殊,是一个只有元数据的包,在元数据中约束了以下的包

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My

基础信息:什么是 MySQL?

MySQL是一个开源的深受欢迎的关系型数据库管理系统(简称RDBMS)。目前排名第二,仅次于Oracle数据库。 MySQL可以免费下载,但是,还提供了几个付费版本,这些版本提供了附加功能。 顾名思义

MySQL 表结构生成 Markdown 文档 | 工具篇

背景 在实施软件工程的时候,当要将某一版本归档时,需要汇总的文档要求还是比较高的、各类文档齐全,包括项目架构、项目安装、接口等文档,而数据库表结构说明文档亦属于其一。记得很早之前想找一个可以导出MyS

MySQL 定时备份

1设置好crontab定时任务备份 #每天备份三次数据库 05,15,22***sh/data/script/alldatabase_back.sh&>/dev/null #每天2点备份 02***s

Laravel-Binlog 扩展(用于实时监听 MySQL 数据变更、数据同步等场景)

Laravel-Binlogv0.2.1 (该扩展当前用于我司测试环境实时同步Mysql数据变更到ElasticSearch,稳定性待测试!!哈哈哈)我司正式环境走的阿里云DTS数据订阅 基于Sw

MySQL 读后总结

一条SQL查询语句是如何执行的? Mysql基本架构示意图Mysql分为Server层和存储引擎层两部分 Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

pymysql fetchone () , fetchall () , fetchmany ()

最近在用python操作mysql数据库时,碰到了下面这两个函数,标记一下: 1.定义 1.1fetchone(): 返回单个的元组,也就是一条记录(row),如果没有结果则返回None 1.2fet

MySQL 安装和配置

MySQL安装和配置 MySQL安装 Mysql安装官网:http://www.mysql.com/ 官网下载:http://dev.mysql.com/downloads/mysql/ 官网5.5下

2019年8月数据库流行度排行:双星闪耀 MySQL 成月度最大赢家

炎炎夏日,DB-Engines的8月榜单已经发布,本月积分MySQL获得了最显著的增长,较上月增加了24分,Oracle获得了18分的增长,Oracle公司的两个王牌产品,闪耀8月。以下是前10名的榜