吴耿龙

Web开发技术分享

首页 归档 标签 关于

实现微信朋友圈可见不可见的数据库设计及ORM语句

2017-11-30 5 min read # 微信朋友圈 # laravel # 数据库设计

说明

发朋友圈的时候，可以公开、对自己可见、对部分人可见、对部分人不可见，微信的数据库设计是怎样的我不太清楚，下边的方案是根据我们自己的业务设计的，也能实现这种需求。

设计方案

方案一

在时间线表增加三个字段：权限类型、用户列表。当选择对谁可见或不可见时，把用户的id列表存到用户列表列表，用下面的查询语句查出某个用户关注的所有孩子的最新100条数据。

select 
  * 
from 
  `time_line`.`tb_time_line` 
where 
  `child_id` in (
    '62a1464fcaeb00e3', 'f2bd17a138a2cabf'
  ) 
  and (
    `visible_type` = '1' 
    or (
      `visible_type` = '3' 
      and `user_list` like 'a129366c3a06f7d5'
    ) 
    or (
      `visible_type` = '4' 
      and `user_list` not like 'a129366c3a06f7d5'
    )
  ) 
  and `time_line`.`tb_time_line`.`deleted_at` is null 
order by 
  `create_time` desc 
limit 
  100 offset 0

方案二

时间线表增加一个权限类型字段，新建一个表存放某条时间线对某人可见或不可见，这个表有两个字段：user_id、time_line_id，联合主键。下面的语句查询同样的数据。

select 
  * 
from 
  `time_line`.`tb_time_line` 
where 
  `child_id` in (
    '62a1464fcaeb00e3', 'f2bd17a138a2cabf'
  ) 
  and (
    `visible_type` = '1' 
    or (
      `visible_type` = '3' 
      and exists (
        select 
          * 
        from 
          `tb_visible` 
        where 
          `tb_visible`.`user_id` = 'a129366c3a06f7d5' 
          and tb_visible.time_line_id = time_line_id
      )
    ) 
    or (
      `visible_type` = '4' 
      and not exists (
        select 
          * 
        from 
          `tb_visible` 
        where 
          `tb_visible`.`user_id` = 'a129366c3a06f7d5' 
          and tb_visible.time_line_id = time_line_id
      )
    )
  ) 
  and `time_line`.`tb_time_line`.`deleted_at` is null 
order by 
  `create_time` desc 
limit 
  100 offset 0

方案对比

对比可以通过查询、插入的效率，这里主要对比查询效率。

环境：

电脑：Macbook Pro 2015 256G
数据库：MySQL 5.7.17

time_line 表创建了近500万条数据。
visible 表创建了1千多万条数据。

查询速度统计，取10次查询的平均值。

第1次 第2次 第3次 第4次 第5次 第6次 第7次 第8次 第9次 第10次 平均(ms)
方案1 14.6 9.1 13.7 12.3 15.6 11.2 11.8 14.6 20.0 12.6 13.5
方案2 19.2 18.1 15.8 10.0 10.4 7.7 14.3 13.1 7.3 12.0 12.7

计算总数统计，取10次查询的平均值。

因为需要做分页，所以需要先查出符合条件的总记录数。查询语句如下：

select 
  count(*) as aggregate 
from 
  `time_line`.`tb_time_line` 
where 
  `child_id` in (
    '62a1464fcaeb00e3', 'f2bd17a138a2cabf'
  ) 
  and (
    `visible_type` = '1' 
    or (
      `visible_type` = '3' 
      and `user_list` like 'a129366c3a06f7d5'
    ) 
    or (
      `visible_type` = '4' 
      and `user_list` not like 'a129366c3a06f7d5'
    )
  ) 
  and `time_line`.`tb_time_line`.`deleted_at` is null

方案二

select 
  count(*) as aggregate 
from 
  `time_line`.`tb_time_line` 
where 
  `child_id` in (
    '62a1464fcaeb00e3', 'f2bd17a138a2cabf'
  ) 
  and (
    `visible_type` = '1' 
    or (
      `visible_type` = '3' 
      and exists (
        select 
          * 
        from 
          `tb_visible` 
        where 
          `tb_visible`.`user_id` = 'a129366c3a06f7d5' 
          and tb_visible.time_line_id = time_line_id
      )
    ) 
    or (
      `visible_type` = '4' 
      and not exists (
        select 
          * 
        from 
          `tb_visible` 
        where 
          `tb_visible`.`user_id` = 'a129366c3a06f7d5' 
          and tb_visible.time_line_id = time_line_id
      )
    )
  ) 
  and `time_line`.`tb_time_line`.`deleted_at` is null
第1次 第2次 第3次 第4次 第5次 第6次 第7次 第8次 第9次 第10次 平均(ms)
方案1 2410.0 2860.0 2620.0 2050.0 2040.0 2030.0 2040.0 2050.0 2090.0 2040.0 2223.0
方案2 126.0 123.0 125.0 127.0 127.0 124.0 124.0 123.0 124.0 123.0 124.6

对比结果，方案一总时间 13.5ms+2223ms=22.365s ，方案二 12.7ms+124.6ms=1.37s。

综上，选择方案二。

用laravel的ORM实现如下：

public function circleList($userId, array $childIds, $currentPage, $perPage, $orderByHappenTime = false)
    {
        $condition = self::whereIn('child_id', $childIds)
            ->where(function ($query) use ($userId) {
                $query->where('visible_type', Common::VISIBLE_TYPE_ALL)
                    ->orWhere(function ($query) use ($userId) {
                        $query->where('visible_type', Common::VISIBLE_TYPE_WHO_CAN_SEE)
                            ->whereExists(function ($query) use ($userId) {
                                $query->from('journal_time_line.tb_visible')
                                    ->where('tb_visible.user_id', $userId)
                                    ->whereRaw('tb_visible.time_line_id = time_line_id');
                            });
                    })
                    ->orWhere(function ($query) use ($userId) {
                        $query->where('visible_type', Common::VISIBLE_TYPE_WHO_CANNOT_SEE)
                            ->whereNotExists(function ($query) use ($userId) {
                                $query->from('journal_time_line.tb_visible')
                                    ->where('tb_visible.user_id', $userId)
                                    ->whereRaw('tb_visible.time_line_id = time_line_id');
                            });
                    });
            });

        if ($orderByHappenTime) {
            $condition = $condition->orderBy('happen_time', 'desc');
        }

        $model = $condition->orderBy('create_time', 'desc')
            ->paginate($perPage, ['*'], 'page', $currentPage);

        return $model;
    }
RSS