您的位置：首页 > 数据库 > mysql  >  MySQL 重写查询语句的三种策略

MySQL 重写查询语句的三种策略

 更新时间：2021年5月10日 15:00  点击：2304

在优化存在问题的查询时，我们需要改变方式去获取查询结果——但这并不意味着从 MySQL获取同样的结果集。有些时候我们可以将查询转换为获取相同结果，但更好性能的查询形式。然而，我们也需要考虑重写查询去获取不同的结果，因为这样可以提高开发效率。也可以通过修改应用程序代码来取得相同的效果。本篇文章将介绍如何重写查询的技巧。

复杂查询与分步查询

一个重要的查询设计课题是将复杂查询分解为多个简单查询是否会更好。在传统的数据库设计中强调尽可能地用更少的查询解决大量工作。在过往，这种方式会更好。这是因为以前的网络通讯成本更高以及考虑查询解析器和优化器的负荷。

然而，这种建议并不怎么适用于 MySQL，这是由于 MySQL 处理建立连接和断开连接的方式十分高效，并且对简单查询的响应很快。当今的网络速度相比以前也有了大幅度的提升。根据不同的服务端版本，MySQL 可以在普通机器上一秒内运行超过10万次的简单查询，并且在千兆网络上完成每秒2000次的查询通讯。因此，进行分布查询并不是过往说的那么糟糕。

相比于每秒遍历的数据行数，连接响应依旧是比较慢的。在内存数据中，这个时间达到了毫秒级。当然，使用尽可能的查询次数依旧是一个不错的选择。但是，有时我们可以通过拆分复杂查询为几个简单的查询来提高性能。接下来我们将展示一些示例。

在程序设计中，使用过多的查询是一个常犯的错误。例如，有些应用执行了10个单独的查询来获取10行数据（使用循环一条条获取），而这本可以通过一条查询10行数据的查询来完成。因此，这并不是倡导每次都做查询的拆分，而是根据实际情况来。

切分查询语句

另一个方式是拆分查询后重新再组合。通过在大数据量的查询拆分为更小范围的查询以减少每次影响的行数。

清洗旧数据就是一个典型的例子。周期性的清洗数据工作需要移除大量数据，进行这样的操作会长时间锁定大量数据行。这种操作还会产生事务日志、消耗大量资源并且会阻塞那些本不应该被打断的小数据量的查询。将DELETE语句切分后，使用中等规模的查询可以显著改善性能，并且在查询是重复的时候可以减少重复查询产生的额外延迟。例如下面的删除语句：

DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

应用的伪代码的形式如下：

rows_affected = 0
do {
  rows_affected = do_query (
  "DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH)
  LIMIT 10000")
  } while rows_affected > 0

一次删除10000行对于提高每次查询的效率来说已经是一个足够大的任务了。一个足够短的任务会减少对服务端的影响（事务存储引擎会从中受益）。在 DELETE 语句中插入一些休眠时间也是一个不错的主意，这样可以在时间上分散负荷并且缩短持有锁的持续时间。

拆解联合查询

很多高性能的应用会拆解联合查询。可以通过将联合查询拆分为多个单表查询，然后在应用中再将结果组合起来。例如：

SELECT * FROM tag
	JOIN tag_post ON tag_post.tag_id=tag.id
  JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

可以将这个联合查询拆分如下是哪个部分。

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123, 456, 567, 9098, 8904);

注：这里的 tag_id=1234和post.id IN (123, 456, 567, 9098, 8904)都是基于前面查询的结果得到的值。为什么要这么做？第一眼看过去好像是毫无必要的——增加了查询的次数而已。然而，这种重建查询可以带来如下优势：

  • 缓存机制会更有效。很多应用直接使用 ORM 映射数据表。在这个例子中，如果 tag 为 mysql 的对象已经被缓存了，第一条查询就会跳过。如果 posts 中 id 为123，567或9908在缓存中，则可以从 IN 列表中移除这几个。通过这种策略，查询缓存会得到相应的受益。如果只有其中的一个表经常变化，拆解联合查询可以减少缓存失效的次数。
  • 单独执行这些查询有时候可以减少锁表的机会。
  • 通过这种方式很容易扩展数据库，并把数据表放到不同的机器上。
  • 查询自身可以进行优化。这个例子中，使用 IN 查询替代联合查询后，MySQL 对行 ID 进行排序和获取数据行有可能会更优。
  • 可以减少冗余的行访问。使用这种方式意味着只做一次数据行获取，而在联合查询中有可能重复获取相同的数据。基于这种原因，这种拆解方式也可能会减少整个网络负荷和内存占用。
  • 扩展一下，也可以通过人为进行哈希联合查询来替代MySQL联合查询的嵌套循环，哈希联合查询也可能会更有效。

最终可以看到，通过拆解联合查询可以使得缓存复用性更高，多服务器分布式数据方案更简单，并可以在大的数据表中使用 IN 查询替代联合查询或同一张表的多次重复查询。

以上就是MySQL 重写查询语句的三种策略的详细内容，更多关于MySQL 重写查询语句的资料请关注猪先飞其它相关文章！

[!--infotagslink--]

相关文章

  • Mybatis Plus select 实现只查询部分字段

    这篇文章主要介绍了Mybatis Plus select 实现只查询部分字段的操作，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-09-01

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14

  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下...2016-08-23

  • mysql中获取一天、一周、一月时间数据的各种sql语句写法

    创建表：复制代码 代码如下:create table if not exists t( id int, addTime datetime default '0000-00-00 00:00:00′)添加两条初始数据：insert t values(1, '2012-07-12 21:00:00′);insert t values(2, '2012-07...2014-05-31

  • MyBatisPlus-QueryWrapper多条件查询及修改方式

    这篇文章主要介绍了MyBatisPlus-QueryWrapper多条件查询及修改方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2022-06-27

  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢？commen sence指的是那些大家都应该知道的事情，但往往大家又会会略这些东西，或者对这些东西一知半解，今天我总结下自己在mysql中遇到的一些commen sense类型的问题。　...2015-03-15

  • Oracle使用like查询时对下划线的处理方法

    这篇文章主要介绍了Oracle使用like查询时对下划线的处理方法，本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-16

  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取)，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22

  • 解决mybatis-plus 查询耗时慢的问题

    这篇文章主要介绍了解决mybatis-plus 查询耗时慢的问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-07-04

  • mysql的3种分表方案

    一、先说一下为什么要分表：当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。根据个人经验，mysql执行一个sql的过程如下：1...2014-05-31

  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时，总免不了会遇到这个问题：插入中文字符出现乱码，虽然这是运维先给配好的环境，但是在自己机子上玩的时候咧，总得知道个一二吧，不然以后如何优雅的吹牛B。...2015-03-15

  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql，这里记录一下安装的过程，方便以后查阅Mysql5.5.37安装需要cmake，5.6版本开始都需要cmake来编译，5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15

  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候，客户机在Bridged Adapter模式下，使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候，客户机在Bridged Adapter模式下，使用可选项里唯一一个WIFI选项，Microsoft Virtual Wifi Miniport Adapter也无法上网，故弃之。...2013-09-19

  • C#条件语句、循环语句(if、while)

    这篇文章主要介绍了C#条件语句、循环语句(if、while)的用法，学习c#的朋友可以参考下...2020-06-25

  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点，大部分情况下，修改MySQL密码是需要有mysql里的root权限的...2013-09-11

  • MySQL数据库备份还原方法

    MySQL命令行导出数据库： 1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录 如我输入的命令行：cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26

  • MySQL中在查询结果集中得到记录行号的方法

    如果需要在查询语句返回的列中包含一列表示该条记录在整个结果集中的行号， ISO SQL:2003 标准提出的方法是提供 ROW_NUMBER() / RANK() 函数。 Oracle 中可以使用标准方法（8i版本以上），也可以使用非标准的 ROWNUM ； MS SQL...2015-03-15

  • Node实现搜索框进行模糊查询

    这篇文章主要为大家详细介绍了Node实现搜索框进行模糊查询，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2021-06-28

  • Mysql命令大全(详细篇)

    一、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql/bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密...2015-11-08

  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件，只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23