单位有一个很复杂的统计SQL,关联了5张表
并且排序跨越了两个表，
并且一个升序，一个降序...
这个SQL先查到一个大分类下所有的小分类，然后查询小分类下所有的歌曲，然后使用歌曲连接到排行榜的数据，
根据优先级和排行榜的点击率排序，最后取若干条数据。
优先级 升序 点击率 降序。
表设计严格遵守三范式,没有一个冗余字段
这个SQL的功能是提供大分类下按照点击率排行的歌曲列表，所以使用率很高，虽然有缓存，但是缓存失效，平均查询时间需要10s。
由于这个SQL对于点击率的实时性要求不高，所以采用类似Oracle物化视图，定期全量刷新的方式优化。
新建一个表，MV_Test
每天先truncate这个表，然后使用5个表的关联查询初始化这个表，
然后程序只使用MV_Test这个表。
这个优化的方式很常见。
但是遇到两个问题
1.排序字段一个升序，一个降序。
因为MySQL没有实现降序的索引,所以在初始化MV的时候，需要整理数据。
假设原来的优先级为priority字段，则在插入MV的时候，需要插入（0-priority）
这样优先级和点击率就都可以使用降序了。
2.初始化MV的数据需要30s
先truncate，然后初始化数据。问题是初始化数据需要30s。
在这30s内，用户访问不到任何的数据。
业务人员也要求最好每个小时刷新一次。
难道每小时都要给用户看30s的白板吗?
后来采用的方案
初始化的数据写入mv_test_pre表(新建一个"准备"表)
初始化完成之后，rename表。这个rename的过程不到1毫秒。
RENAME TABLE mv_test TO tmp_mv_test,
mv_test_pre TO mv_test,
tmp_mv_test TO mv_test_pre;
参考:
http://imysql.com/node/89
并且排序跨越了两个表，
并且一个升序，一个降序...
这个SQL先查到一个大分类下所有的小分类，然后查询小分类下所有的歌曲，然后使用歌曲连接到排行榜的数据，
根据优先级和排行榜的点击率排序，最后取若干条数据。
优先级 升序 点击率 降序。
表设计严格遵守三范式,没有一个冗余字段
这个SQL的功能是提供大分类下按照点击率排行的歌曲列表，所以使用率很高，虽然有缓存，但是缓存失效，平均查询时间需要10s。
由于这个SQL对于点击率的实时性要求不高，所以采用类似Oracle物化视图，定期全量刷新的方式优化。
新建一个表，MV_Test
每天先truncate这个表，然后使用5个表的关联查询初始化这个表，
然后程序只使用MV_Test这个表。
这个优化的方式很常见。
但是遇到两个问题
1.排序字段一个升序，一个降序。
因为MySQL没有实现降序的索引,所以在初始化MV的时候，需要整理数据。
假设原来的优先级为priority字段，则在插入MV的时候，需要插入（0-priority）
这样优先级和点击率就都可以使用降序了。
2.初始化MV的数据需要30s
先truncate，然后初始化数据。问题是初始化数据需要30s。
在这30s内，用户访问不到任何的数据。
业务人员也要求最好每个小时刷新一次。
难道每小时都要给用户看30s的白板吗?
后来采用的方案
初始化的数据写入mv_test_pre表(新建一个"准备"表)
初始化完成之后，rename表。这个rename的过程不到1毫秒。
RENAME TABLE mv_test TO tmp_mv_test,
mv_test_pre TO mv_test,
tmp_mv_test TO mv_test_pre;
参考:
http://imysql.com/node/89
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1242710/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
