代码之家 › 专栏 › 技术社区 › instanceof me

mysql两索引查询性能+排序

indexing performance mysql

instanceof me · 技术社区 · 15 年前

我正在实施以下访问策略:a User 可以访问 Resource 如果他创造了它,属于 资源 的组成员或资源是否公开。

这是我的数据库结构(表是myisam):

User (1K-10K Users)
  id
  nickame
  â¦
  index user_name(id, nickname)
Group (1K)
  id
  â¦
Resource (10K-100K)
  id
  user_id
  access_type (int)
  group_id
  created (int/timestamp)
  â¦
  indexes by_user(user_id, created), by_group(access_type, group_id, created), public(access_type, created)
User_Group (5K-20K)
  user_id
  group_id
  membership_type (int)
  index user_group(user_id, membership_type, group_id)

授权访问的条件是这样实现的: * 用户 是的创建者 资源 ( user_id = <his id> ,无论访问类型是什么) *或者资源是公开的: Resource.access_id = 3 *或者资源共享在一个组中,用户被接受为该组的“成员”: ** Resource.access_id = 2 (在组中共享) ** Ressource.group_id 匹配A User_Group 的 group_id **此 用户组 的 user_id 匹配用户ID **此 用户群 的 membership_type 是1、2或3(接受的成员或组主持人/管理员)

我的问题是:列出 Resources (和 资源 创建者 nickname )当我们拥有用户的ID并按资源的 created 时间戳。

到目前为止,我最好的尝试是使用 UNION 允许同时使用 by_user 和 by_group 索引,但在随后的排序过程中可能会遇到困难:

SELECT SQL_NO_CACHE
  a.*, user.nickname
FROM (
  ( SELECT resource.* FROM resource WHERE user_id = '000000-0000-0000-0000-000000000000' )
UNION
  ( SELECT resource.* FROM resource WHERE access_type = 3 )
UNION
  ( SELECT resource.* FROM resource
   INNER JOIN user_group ON (access_type = 2 AND user_group.group_id = resource.group_id)
   WHERE user_group.user_id = '000000-0000-0000-0000-000000000000'
    AND user_group.type IN (1, 2, 3)
  )
) a
LEFT JOIN user ON (user.id = a.user_id)
ORDER BY created DESC;

这个 EXPLAIN 输出告诉我它使用两个索引 SELECT S和饰面 type: ALL / Using filesort 对于 ORDER BY 在 联合 ED行。但是如果我想订购和限制的话,我想这可能会变得很重,因为上面没有索引 联合 ED元组。

另一种可能性是使用 Groups 这个 用户 在:

SELECT SQL_NO_CACHE
  resource.*, user.nickname
FROM resource
LEFT JOIN user ON (user.id = resource.user_id) 
WHERE user_id = '000000-0000-0000-0000-000000000000'
  OR access_type = 3
  OR (access_type = 2 AND group_id IN
   ( SELECT group_id FROM user_group USE INDEX (`user_group`)
   WHERE user_id = '000000-0000-0000-0000-000000000000' AND type IN (1, 2, 3)
   )
  )
ORDER BY created DESC;

但是这里 解释 告诉我它不会使用索引并执行选择 type: ALL / Using where; using filesort 在 资源 桌子,巫婆是我想要避免的。如果我尝试 FORCE INDEX(by_user, by_group) ,首先合并两个索引 type: index_merge / Using sort_union(by_user,by_group); Using where; Using filesort 也可能很贵。

有更好的主意吗?这个请求可能经常被称为_

1 回复 | 直到 15 年前

Rob Van Dam 15 年前

我认为与您建议的子查询相比,第二个联接更好地利用这里的索引(只需要添加一个distinct以避免重复)。

  SELECT SQL_NO_CACHE DISTINCT
         resource.*, user.nickname
    FROM resource
LEFT JOIN user ON user.id = resource.user_id
LEFT JOIN user_group ON user_group.user_id = user.id AND user_group.type IN (1, 2, 3)
    WHERE user_id = '000000-0000-0000-0000-000000000000'
       OR access_type = 3
      OR (access_type = 2 AND group_id IS NOT NULL)
ORDER BY created DESC;

每当主WHERE子句是或的序列时,理想的解释结果将是某种索引合并。但是,你是对的,那种联合是最慢的。如果你继续读下去 index_merge 尤其是关于每种类型合并的子部分,很明显,where子句的or部分需要引用键的所有部分,否则将得到排序联合。因此,如果有资源索引,您应该得到更好的解释结果:

Resource (10K-100K)
id
user_id
access_type (int)
group_id
created (int/timestamp)
â¦
indexes 
    just_user(user_id),
    by_user(user_id, created),
    just_access(access_type),
    just_access_group(access_type, group_id),
    by_group(access_type, group_id, created),
    public(access_type, created)

我认识到这似乎与当你做多部分键时,你可以免费得到左前缀作为索引相矛盾,但是看起来索引合并算法不能(还没有?)使用那些部分前缀索引。