说明

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

设计方案

方案一

在时间线表增加三个字段：权限类型、用户列表。当选择对谁可见或不可见时，把用户的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次查询的平均值。

计算总数统计，取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

对比结果，方案一总时间 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;
    }

原文：https://www.wugenglong.com/database/realizing_the_design_of_the_wechat_friend_circle_database.html