前言
在第五期中,我只是一笔带过了 复杂查询的优化处理,就是尽量的可以拆分成几个简单的查询语句。我觉得那可能不够严谨,今天我就来好好带大家分析分析。
单个 OR 多个查询
网络通信
连接:轻量级,连接与断开损耗低。
网络带宽:随着发展现代的网络速度也是越来越快的。
主从复制时,小查询可以减少MySQL复制的延迟。
数据返回
MySQL内部每秒能够扫描内存中上百万行数据,相比之下,MySQL响应数据给客户端就慢的多了。在相同条件的情况下,使用尽可能少的查询会比较好。但是有时候,将一个大查询分解为多个小查询是很有必要的,
注意
如果一个查询能够胜任时还写多个独立查询时不明智的。比如从用户表返回十条用户数据,却使用了10条sql来查是不应该。这样就多了10条查询。
切分查询
切分查询的宗旨就是减少锁的粒度。我们在业务场景中可能会遇到需要每个月清理一些数据,比如日志,操作记录等等的文件,这时的数据会比较多。如果我们直接通过一条sql语句去执行,就会导致锁的粒度非常大,可能会导致相关涉及到表操作的业务可能会受到影响,这个时候我们就可以采用分批的去处理。比如以下示例:
# 删除一个月的数据
mysql> DELETE FROM `logs` WHERE created_at < DATE_SUB(NOW(),INTERVAL 1 MONTH)
# 上面这种删除我们就可以尝试使用分页去处理数据。伪代码:
rows_affected = 0
do {
rows_affected = do_query (
"DELETE FROM `logs` WHERE created_at < DATE_SUB(NOW(),INTERVAL 1 MONTH) LIMIT 1000"
)
} while rows_affected > 0
一次删除一千行,这样对于数据库来说压力也小,事务粒度也小。所以理所应到操作起来也会快很多的。
分解关联查询
接下来就是重中之重了,会经常在业务中遇到复杂的业务查询,比如关联查询 多个join语句。在后期的章节中,我会详细讲解join的底层查询逻辑,现在我们先来看看下面这条语句:
# 查询用户的地址及它的地址详细数据
mysql> SELECT * FROM User as u
-> join address as a on u.id = a.user_id
-> join region as r on a.province_id = r.id
-> where u.username = 'gio';
# 这个时候我们就可以拆分三条语句去处理
mysql> select * from user where username = 'gio';
mysql> select * from address where user_id = 1;
mysql> select * from region wherer id in (110000,120000,410000,430000)
我们可以看到上面拆解了三条sql语句,虽然返回的数据可能都一样,但是里面处理的逻辑是不一样的。大家可关注下期的 join 底层分析。先来讲讲这样分解的具体优势:
- 让缓存效率更高,许多应用程序可以方便地缓存单表查询对应的结果对象。换句话说就是你这三条单表语句命中缓存的几率比较大,在变更不频繁的情况下,大几率是从缓存中获取数据的。
- 将查询分解后,执行单个查询可以减少锁的竞争。
- 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
- 查询本身效率也可能会有所提升。在上面例子中:in的查询按顺序要比随机要好很多
- 可以减少冗余记录的查询,在应用层做关联,某条记录只需要查询一次,但是如果在mysql中做关联,则可能重复查询访问某一部分数据。如果重复访问了势必会增加内存与网络的消耗。
- 可能使用mysql的不同线程去处理,减少语句出现长时间等待的可能。
总结
在很多场景下,重构关联查询将它们的处理放在应用层会更加高效。而且在很多情况下,我们的数据可能分布在不同的数据库中,我们知道跨库查询的成本肯定要比单库要高的,所以如果可以使用类似 **In()**这种单表的查询是非常不错的一种选择
参考文献。1
高性MYSQL优化 第三版 ↩︎