MySQL查询性能优化-重构查询方式

MySQL |6个月前 |0

laravel MySQL 优化


在优化有问题的查询时,目标应该是找到一个更优的方法获得实际需要的结果——而不是一定总是需要从 MySQL 获取一模一样的结果集。有时候,可以将查询转换一种写法让其返回一样的结果,但性能更好。但也可以通过修改应用代码,用另一种方式完成查询,最终达到一样的目的。

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

设计查询的时候一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。在传统实现中,总是强调需要数据库层完成尽可能多的工作,这样的逻辑在于以前总是认为网络通信,查询解析和优化是一件代价很高的事情。

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

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

切分查询

有时候对于一个大查询我们需要“分而治之”,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,没辞只返回小部分查询结果。

删除旧的数据就是一个很好的例子。定期地清理大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据,占满整个事物日志、耗尽系统资源、阻塞很多小的但重要的查询。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地,可以对没一个表进行一次单表查询,然后将结果在应用程序中进行关联。例如:

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 (1,2,3,4);

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

  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如,上面查询中的 tag 已经被缓存了,那么应用就可以跳过第一个查询。再例如,应用中已经缓存了 ID 为 1、2 的内容,那么第三个查询的 IN() 中就可以少几个 ID。另外,对 MySQL 查询缓存来说,如果关联表中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。
  • 将查询分解后,智行单个查询可以减少锁的竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  • 查询本身效率也可能有所提升。在这个例子中,使用 IN() 代替关联查询,可以让 MySQL 安装 ID 顺序进行查询,这可能比随机的关联要更高效。
  • 可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络与内存的消耗。
  • 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。

在很多场景下,通过重构查询将关联放到应用程序中将会更加高效,这样的场景有很多,如:当应用能够方便地缓存单个查询的结果时、当可以将数据分布到不同的 MySQL 服务器上时、当能够使用 IN() 的方式代替关联查询时、当查询中使用同一个数据表时。

——以上内容摘抄自《高性能MySQL》

在查看 laravel Eloquent 模型关联产生的 SQL 语句可以看出,laravel 框架就很好的利用了上述方法。

MeiWJ
A PHP Web Artisan

完善源自用心 細節決定成敗