6.3 重构查询方式

有时候,转换一下查询写法可以在获得相同结果集的前提上,使查询变得更加的高效.

6.3.1 一个复杂查询还是多个简单查询.

当某个SQL使用的是一个复杂的查询的时候,可以考虑将其拆分成多个简单的查询,即使这样的查询会返回一些冗余数据,但是有时候返回冗余数据也要比复杂查询的效率要高,不过这里需要开发者自己去衡量一下两种方式的代价.

6.3.2 切分查询

有时候,对于一个大的查询我们需要"分而治之",将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果.
这样操作的前提是,大查询查询的数据量非常大,一般在一万行以上,当然,也不一定是查询,可能是删除/更新等操作,这个时候将查询分为多个每次查询一万行的子查询即可.
一个示例是,如果要每个月执行一次如下语句:
delete from my_order where create_time <DATE_SUB(NOW(),INTERVAL 3 MONTH);
若该操作涉及的数据量很大,则可以考虑将其分成多次删除操作完成,例如:

k=True
while k:
  k = conn.execute('delete from my_order where create_time <DATE_SUB(NOW(),INTERVAL 3 MONTH) limit 10000;')

6.3.3 分解关联查询

有的时候将一个较为复杂的关联查询拆解为多个简单查询会使得查询效率增大.
例如有一个如下的查询语句对tag post tag_post进行关联查询

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

上述语句可以修改为如下三个语句:
select * from tag where tag='mysql'; -- tag_id:1234
select * from tag_post where tag_id=1234; --post_id:1,2,3,4,5
select * from post where post_id in (1,2,3,4,5);

将复杂的语句拆分后有如下几个好处:

• 1. 让缓存效率提高,查询颗粒度越低,能命中缓存的概率也就越高.
• 2. 拆解查询后,可以减少锁的竞争.由于复杂查询在查询时会同时锁住多个表的行,而拆分后只锁住待查询表的行.
• 3. 在应用层做关联,可以更容易对数据库做拆分,是数据库也更容易扩展
• 4. 查询本身效率也会有提升.在这个例子中使用IN()代替关联查询,可以让mysql按照ID顺序进行查询,比随机关联查询了要更高效.
     *5. 可以减少冗余记录的查询,在应用层做关联,意味着对某条记录应用只需要查询一次,而在数据库中做关联,这可能需要重复地访问一部分数据.
     *6. 这样做相当于在应用层实现了哈希关联,而不是mysql的嵌套循环关联.

一般来说,在如下情况下分解关联查询会对效率有提升:
*1. 当应用能够方便地缓存单个查询结果的时候.
*2. 当数据是分布到不同的mysql服务器上的时候.
*3. 当能够使用IN()的方式代替关联查询的时候
*4. 当查询中使用同一个数据表的时候.