代码之家 › 专栏 › 技术社区 › Jon M

mysql搜索用户及其角色

join search mysql

Jon M · 技术社区 · 15 年前

我正在重新编写SQL,它允许用户搜索我们站点上的任何其他用户,并显示他们的角色。

例如,角色可以是“编写者”、“编辑者”、“发布者”。

每个角色都将用户链接到发布。

用户可以在多个发布中扮演多个角色。

表设置示例:

"users" : user_id, firstname, lastname
"publications" : publication_id, name  
"link_writers" : user_id, publication_id  
"link_editors" : user_id, publication_id

当前psuedo sql:

SELECT * FROM (
  (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') 
  UNION 
  (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%')
) AS dt
JOIN (ROLES STATEMENT) AS roles ON roles.user_id = dt.user_id

目前,我的角色声明是:

SELECT  dt2.user_id, dt2.publication_id, dt.role FROM (
  (SELECT 'writer' AS role, link_writers.user_id, link_writers.publication_id
  FROM link_writers)
  UNION
  (SELECT 'editor' AS role, link_editors.user_id, link_editors.publication_id
  FROM link_editors)
) AS dt2

将roles语句包装在union子句中的原因是,有些角色更复杂,需要表联接来查找发布的标识和用户标识。

例如,“发布者”可以跨两个表链接

"link_publishers": user_id, publisher_group_id
"link_publisher_groups": publisher_group_id, publication_id

因此,在这种情况下,构成我联盟一部分的查询将是:

SELECT 'publisher' AS role, link_publishers.user_id, link_publisher_groups.publication_id
FROM link_publishers
JOIN link_publisher_groups ON lpg.group_id = lp.group_id

我非常确信我的表设置是好的(在研究布局时,我被警告不要为所有系统使用一个表)。我的问题是用户表中现在有100000行,每个链接表中最多有70000行。

用户表中的初始查找速度很快,但连接确实会减慢速度。

我如何才能加入相关角色?

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————-- explain http://img155.imageshack.us/img155/4758/stackusersearchjoins.gif

解释以上内容(在新窗口中打开以查看完全分辨率)。

红色底端是“where firstname like“%jenkz%”,第三行搜索concat(firstname,”,lastname)like“%jenkz%”。因此行数很大,但我认为这是不可避免的,除非有一种方法可以将索引交叉连接的字段放入其中?

顶部的绿色位仅显示从roles语句扫描的行总数。

然后,您可以看到每个单独的联合子句(6-12),它们都显示大量行。有些索引是正常的,有些是唯一的。

似乎mysql没有优化使用dt.user_id作为union语句内部的比较。有没有办法强迫这种行为?

请注意,我真正的设置不是出版物和作家,而是“网站管理员”、“玩家”、“团队”等。

3 回复 | 直到 15 年前

Community CDub 8 年前

检查了OMG小马的答案 SO - Use Of Correlated Subquery ,我想到了:

SELECT * FROM (
  (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') 
  UNION 
  (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%')
) AS dt
JOIN ( SELECT 'writer' AS role, link_writers.user_id, link_writers.publication_id
       FROM link_writers
       UNION
       SELECT 'editor' AS role, link_editors.user_id, link_editors.publication_id
       FROM link_editors
       UNION
       SELECT 'publisher' AS role, lp.user_id, lpg.publication_id
       FROM link_publishers lp
       JOIN link_publisher_groups lpg ON lpg.publisher_group_id = lp.publisher_group_id
     ) roles on roles.user_id = dt.user_id

这个解释在我的小数据集上看起来很合理。它在现实中是什么样子的?

Martin 15 年前

我最初的想法是创建一个临时表来保存(和索引)匹配名称的用户ID,并使用它来针对每个链接表进行连接。不幸的是,在MySQL中,一个临时表在查询中只能联接一次。

棘手的解决方法是创建一个永久表,将连接ID添加到主键,这样单独的会话就不会被混淆。

create table tt ( connection_id int not null,
                  user_id int not null, 
                  firstname varchar(10) not null, 
                  lastname varchar(10) not null,
                  primary key( connection_id, user_id ) );

每次需要答案时,将重复以下顺序:

delete from tt where connection_id = connection_id();

insert into tt 
  SELECT connection_id(), user_id, firstname, lastname FROM users 
  WHERE firstname LIKE '%Jenkz%' 
  UNION 
  SELECT connection_id(), user_id, firstname, lastname FROM users 
  WHERE lastname LIKE '%Jenkz%';

接下来,扩展现有的联合,以便只拉出相关的用户标识:

SELECT 'writer' AS role, link_writers.user_id, link_writers.publication_id
FROM link_writers
JOIN tt ON tt.connection_id = connection_id() and tt.user_id = link_writers.user_id

UNION

SELECT 'editor' AS role, link_editors.user_id, link_editors.publication_id
FROM link_editors
JOIN tt ON tt.connection_id = connection_id() and tt.user_id = link_editors.user_id

UNION

SELECT 'publisher' AS role, link_publishers.user_id, link_publisher_groups.publication_id
FROM link_publishers
JOIN link_publisher_groups 
   ON link_publisher_groups.publisher_group_id = link_publishers.publisher_group_id
JOIN tt ON tt.connection_id = connection_id() and tt.user_id = link_publishers.user_id

也许这将是一个改进,因为并非所有链接表的每一行都被拉入联合中。

这个解释有点奇怪,因为在TT上只使用了4个字节的索引——在这里我可以预期所有8个字节。也许这是因为我在TT中的数据太少了。

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: tt
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: link_writers
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.tt.user_id
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: UNION
        table: tt
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 2
  select_type: UNION
        table: link_editors
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.tt.user_id
         rows: 1
        Extra: Using index
*************************** 5. row ***************************
           id: 3
  select_type: UNION
        table: tt
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
*************************** 6. row ***************************
           id: 3
  select_type: UNION
        table: link_publishers
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.tt.user_id
         rows: 1
        Extra: Using index
*************************** 7. row ***************************
           id: 3
  select_type: UNION
        table: link_publisher_groups
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.link_publishers.publisher_group_id
         rows: 2
        Extra: Using index
*************************** 8. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
8 rows in set (0.00 sec)

Martin 15 年前

另一种方法是稍微取消设计的规范化,以更好地支持查询。

为此,请创建一个新表“role”:

create table role (
     user_id int not null,
     role enum ('writer', 'editor', 'publisher' ) not null,
     primary key (user_id, role )
);

只要将新行添加到包含用户标识的链接表中,就会更新此信息:

insert ignore into role values( $user_id, $role );

一段时间后,角色条目可能已经存在,因此“忽略”修饰符。

可以从现有表中引导该表:

insert ignore into role select distinct user_id, 'writer' from link_writers;
insert ignore into role select distinct user_id, 'editor' from link_editors;
insert ignore into role select distinct user_id, 'publisher' from link_publishers;

然后,您的搜索查询就变成了一组简单的连接,MySQL在优化这些连接时应该没有问题:

SELECT 
   r.user_id, 
   r.role,
   case r.role 
        when 'writer' then w.publication_id
        when 'editor' then e.publication_id
        when 'publisher' then pg.publication_id
        end as publication_id
FROM (
  (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') 
  UNION 
  (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%')
) AS dt
JOIN role r on r.user_id = dt.user_id
LEFT JOIN link_writers w on r.user_id = w.user_id and r.role = 'writer'
LEFT JOIN link_editors e on r.user_id = e.user_id and r.role = 'editor'
LEFT JOIN link_publishers p on r.user_id = p.user_id and r.role = 'publisher'
LEFT JOIN link_publisher_groups pg on p.publisher_group_id = pg.publisher_group_id;

这将给出一个非常“广泛”的答案。