MySQL 系列教程（笔记）

# 重构查询的方式

在优化有问题的查询时，目标应该找到一个更优的方法获得实际需要的结果，而不是一定总是需要从 MySQL 获取一模一样的结果集。

有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。

也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

本节将介绍如何通过这种方式来重构查询，并展示何时需要使用这样的技巧。

# 一个复杂查询还是多个简单查询

设计查询的时候一个需要考虑的重要问题是：是否需要将一个复杂的查询分成多个简单的查询。

在传统的实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总认为网络通信、查询解析和优化是一件代价很高的事情。

这种想法对于 MySQL 并不适用，从设计上让链接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前快很多，无论是带宽还是延迟。在某些版本的 MySQL 上，即使一个通用服务器上，也 能够运行每秒超过 10 万的查询，即使是一个千兆网卡也能轻松满足每秒超过 2000 次的查询。所以 运行多个小查询已经不是大问题了

MySQL 内部每秒能够扫描内存中上百万行数据，相比之下，MySQL 响应数据给客户端就慢得多了。在 其他条件都相同的时候，使用尽可能少的查询当然是更好 的。但是有时候将一个大查询分解为多个小查询是很有必要的。

不过，在应用设计的时候，如果一个查询能够胜任时还写多个独立查询是不明智的。例如：对一个数据表做 10 次独立的查询来返回 10 行数据，每个查询返回一条结果，查询 10 次。

# 切分查询

有时候对于 一个大查询我们需要「分而治之」，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。这不就是分页查询的思想吗？

删除旧的数据就是一个好的例子。定期删除大量数据时，如果用一个大的语句一次性完成的话，则

  • 可能需要一次锁住很多数据
  • 占满整个事务日志
  • 耗尽资源
  • 阻塞很多小的但重要的查询。

将一个大的 delete 语句切分成多个较小的查询可以：

  • 尽可能小的影响 MySQL 性能
  • 还可以减少 MySQL 复制的延迟

例如，需要每个月运行一次下面的查询：

delete from messages where created < date_sub(now(), interval 3 month)
1

可以用类似下面的办法来完成同样的工作

rows_affected = 0
do {
	rows_affected = do_query(
  	"delete from messages where created < date_sub(now(), interval 3 month)"
  )
} while rows_affected > 0
1
2
3
4
5
6

一次性删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务性引擎，很多时候小事务能更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时所的持有时间。

# 分解关联查询

很多高性能的应用都会对关联查询进行分解。简单说：对单表查询，在应用程序中进行关联。

例如下面这个查询：

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';
1
2
3
4

可以分解成下面的这些查询来代替

select * from tag_where tag = 'mysql'
select * from tag_post where tag_id = 1234;
select * from post where post.id in(123,456,9098);
1
2
3

乍一看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一样的。事实上，用分解关联查询的方式重构查询有如下的优势：

  • 让缓存的效率更高

    许多应用程序可以方便的缓存单标查询对应的结果对象。

    例如：上面查询中 tag 已经被缓存了，那么应用就可以跳过第一个查询。

    再如：应用中已经缓存了 ID 为 123，456 的内容，那么低三个查询的 in() 中就可以少几个 ID.

    另外对 MySQL 的查询缓存来说，如果关联中的某个表发生了变化，那么久无法使用查询缓存了，而拆分之后，如果某个表现很少改变，那么急于该表的查询就可以重复利用资源缓存结果了。

  • 将查询分解后，执行单个查询可以减少锁的竞争

  • 在应用层做关联，可以容易对数据库进行拆分，更容易做到高性能和扩展。

  • 查询本身效率也会有所提升，这个可比随机的关联要更好高效

  • 可以减少冗余记录的查询

    在应用层做关联查询，意味着对于某条记录应用，从这里看，这样的重构还可能会减少网络和内存的消耗。

  • 更近一步，这样做相当于在应用中实现了 哈希关联，而不是使用 MySQl 的嵌套循环关联。某些场景的哈希关联效率要高很多。

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多。比如：

  • 当应用能够方便的缓存单个查询的结果的时候
  • 当可以将数据分布到不同的 MySQL 服务器上的时候
  • 当能使用 in() 方式替代关联查询的时候
  • 当查询中使用同一个数据表的时候

← 查询执行的基础 →