I have the following query. The idea is that it allows me to know what groups, and subsequently users, have access to each component_instance. I'm wondering if there is a better way to do this as the query is quite slow, but it's really handy to have these extra columns every time I deal with this table:
我有以下查询。其思想是，它允许我知道哪些组以及随后的用户可以访问每个component_instance。我想知道是否有更好的方法来实现这一点，因为查询速度非常慢，但是每次处理这个表时，拥有这些额外的列是非常方便的:
SELECT component_instances.*,
GROUP_CONCAT(DISTINCT IF(permissions.view, groups.id, NULL)) AS view_group_ids,
GROUP_CONCAT(DISTINCT IF(permissions.edit, groups.id, NULL)) AS edit_group_ids,
GROUP_CONCAT(DISTINCT IF(permissions.view, users.id, NULL)) AS view_user_ids,
GROUP_CONCAT(DISTINCT IF(permissions.edit, users.id, NULL)) AS edit_user_ids
FROM `component_instances`
LEFT OUTER JOIN permissions ON permissions.component_instance_id = component_instances.id
LEFT OUTER JOIN groups ON groups.id = permissions.group_id
LEFT OUTER JOIN groups_users ON groups_users.group_id = groups.id
LEFT OUTER JOIN users ON users.id = groups_users.user_id
GROUP BY component_instances.id
ORDER BY (case when component_instances.ancestry is null then 0 else 1 end), component_instances.ancestry, position
The permissions table is like so (excuse the Rails!):
权限表是这样的(请原谅Rails!)
create_table "permissions", :force => true do |t|
t.integer "component_instance_id"
t.integer "group_id"
t.boolean "view", :default => false
t.boolean "edit", :default => false
end
The types of permissions are edit, and view. A group can be assigned either or both. Permissions are also recursive in that if there are no group permissions on a component_instance, we'd have to check its ancestors to find the first where permissions are set (if any). This makes having the one query quite important because I can then combine this query with the selection logic that the ancestry gem provides (materialised path tree).
权限的类型是编辑和查看。一个组可以被分配为一个或两个。权限也是递归的，因为如果component_instance上没有组权限，我们必须检查它的祖先，以找到第一个设置权限的地方(如果有的话)。这使得拥有一个查询变得非常重要，因为我可以将这个查询与ancestry gem提供的选择逻辑(物化路径树)结合起来。
Update
更新
I've since found this query benchmarks faster:
我发现这个查询基准更快:
SELECT component_instances.*,
GROUP_CONCAT(DISTINCT view_groups.id) AS view_group_ids,
GROUP_CONCAT(DISTINCT edit_groups.id) AS edit_group_ids,
GROUP_CONCAT(DISTINCT view_users.id) AS view_user_ids,
GROUP_CONCAT(DISTINCT edit_users.id) AS edit_user_ids
FROM `component_instances`
LEFT OUTER JOIN permissions ON permissions.component_instance_id = component_instances.id
LEFT OUTER JOIN groups view_groups ON view_groups.id = permissions.group_id AND permissions.view = 1
LEFT OUTER JOIN groups edit_groups ON edit_groups.id = permissions.group_id AND permissions.edit = 1
LEFT OUTER JOIN groups_users view_groups_users ON view_groups_users.group_id = view_groups.id
LEFT OUTER JOIN groups_users edit_groups_users ON edit_groups_users.group_id = edit_groups.id
LEFT OUTER JOIN users view_users ON view_users.id = view_groups_users.user_id
LEFT OUTER JOIN users edit_users ON edit_users.id = edit_groups_users.user_id
GROUP BY component_instances.id
ORDER BY (case when component_instances.ancestry is null then 0 else 1 end), component_instances.ancestry, position
Here is an EXPLAIN for the query above and the table CREATE statements:
下面是对上述查询和表CREATE语句的解释:
+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+
| 1 | SIMPLE | component_instances | ALL | PRIMARY,index_component_instances_on_ancestry | NULL | NULL | NULL | 119 | "Using temporary; Using filesort" |
| 1 | SIMPLE | permissions | ALL | NULL | NULL | NULL | NULL | 6 | "Using where; Using join buffer (Block Nested Loop)" |
| 1 | SIMPLE | view_groups | eq_ref | PRIMARY | PRIMARY | 4 | 05707d890df9347c.permissions.group_id | 1 | "Using where; Using index" |
| 1 | SIMPLE | edit_groups | eq_ref | PRIMARY | PRIMARY | 4 | 05707d890df9347c.permissions.group_id | 1 | "Using where; Using index" |
| 1 | SIMPLE | view_groups_users | ref | index_groups_users_on_group_id_and_user_id | index_groups_users_on_group_id_and_user_id | 5 | 05707d890df9347c.view_groups.id | 1 | "Using index" |
| 1 | SIMPLE | edit_groups_users | ref | index_groups_users_on_group_id_and_user_id | index_groups_users_on_group_id_and_user_id | 5 | 05707d890df9347c.edit_groups.id | 1 | "Using index" |
| 1 | SIMPLE | view_users | eq_ref | PRIMARY | PRIMARY | 4 | 05707d890df9347c.view_groups_users.user_id | 1 | "Using index" |
| 1 | SIMPLE | edit_users | eq_ref | PRIMARY | PRIMARY | 4 | 05707d890df9347c.edit_groups_users.user_id | 1 | "Using index" |
+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+
CREATE TABLE `component_instances` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`visible` int(11) DEFAULT '1',
`instance_id` int(11) DEFAULT NULL,
`deleted_on` date DEFAULT NULL,
`instance_type` varchar(255) DEFAULT NULL,
`component_id` int(11) DEFAULT NULL,
`deleted_root_item` int(11) DEFAULT NULL,
`locked_until` datetime DEFAULT NULL,
`theme_id` int(11) DEFAULT NULL,
`position` int(11) DEFAULT NULL,
`ancestry` varchar(255) DEFAULT NULL,
`ancestry_depth` int(11) DEFAULT '0',
`cached_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_component_instances_on_ancestry` (`ancestry`)
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8
CREATE TABLE `groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
CREATE TABLE `groups_users` (
`group_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
KEY `index_groups_users_on_group_id_and_user_id` (`group_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`component_instance_id` int(11) DEFAULT NULL,
`group_id` int(11) DEFAULT NULL,
`view` tinyint(1) DEFAULT '0',
`edit` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `edit_permissions_index` (`edit`,`group_id`,`component_instance_id`),
KEY `view_permissions_index` (`view`,`group_id`,`component_instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`real_name` varchar(255) DEFAULT NULL,
`username` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`crypted_password` varchar(255) DEFAULT NULL,
`administrator` int(11) NOT NULL DEFAULT '0',
`password_salt` varchar(255) DEFAULT NULL,
`remember_token_expires` datetime DEFAULT NULL,
`persistence_token` varchar(255) DEFAULT NULL,
`disabled` tinyint(1) DEFAULT NULL,
`time_zone` varchar(255) DEFAULT NULL,
`login_count` int(11) DEFAULT NULL,
`failed_login_count` int(11) DEFAULT NULL,
`last_request_at` datetime DEFAULT NULL,
`current_login_at` datetime DEFAULT NULL,
`last_login_at` datetime DEFAULT NULL,
`current_login_ip` varchar(255) DEFAULT NULL,
`last_login_ip` varchar(255) DEFAULT NULL,
`perishable_token` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `index_users_on_username` (`username`),
KEY `index_users_on_perishable_token` (`perishable_token`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
The ORDER BY comes from the ancestry gem but if there's a better way to do this I'd be happy to submit that as a pull request to them.
来自祖先宝石的订单，但是如果有更好的方法的话，我很乐意作为拉取请求提交给他们。
2 个解决方案
#1
1
NULL is placed first (could use COALESCE to replace NULL with something else too instead of using an additional sort column). The second thing is reducing the joins, because the last two were on the id on which we concat.
首先放置NULL(可以使用合并来替换NULL，而不是使用额外的sort列)。第二件事是减少连接，因为最后两个是在我们连接的id上。
SELECT
component_instances.*,
GROUP_CONCAT(DISTINCT view_groups.id) AS view_group_ids,
GROUP_CONCAT(DISTINCT edit_groups.id) AS edit_group_ids,
GROUP_CONCAT(DISTINCT view_groups_users.user_id) AS view_user_ids,
GROUP_CONCAT(DISTINCT edit_groups_users.user_id) AS edit_user_ids
FROM
`component_instances`
LEFT OUTER JOIN permissions
ON permissions.component_instance_id = component_instances.id
LEFT OUTER JOIN groups view_groups
ON view_groups.id = permissions.group_id AND permissions.view = 1
LEFT OUTER JOIN groups edit_groups
ON edit_groups.id = permissions.group_id AND permissions.edit = 1
LEFT OUTER JOIN groups_users view_groups_users
ON view_groups_users.group_id = view_groups.id
LEFT OUTER JOIN groups_users edit_groups_users
ON edit_groups_users.group_id = edit_groups.id
GROUP BY
component_instances.id
ORDER BY
component_instances.ancestry, -- MySQL was sorting the NULL values already correctly
position
;
#2
2
It's almost impossible to optimize your query if we don't have your table structure and indices. Using a EXPLAIN statement is the necessary part of query optimizations.
如果我们没有表结构和索引，几乎不可能优化查询。使用EXPLAIN语句是查询优化的必要部分。
Without the mentioned info, all I can comment on your question is that your ORDER BY part can benefit from some optimization for sure. Using any functions or statements in a condition will always result in a disaster. Also using a nullable field in an ORDER BY will also lead to problems. Perhaps the easiest way would be adding a new field to your table holding 0s and 1s instead of the current CASE statement.
没有上述的信息，我只能评论你的问题，你的订单可以部分受益于一些优化肯定。在条件中使用任何函数或语句都会导致灾难。同时，使用可空字段也会导致问题。也许最简单的方法是在包含0和1的表中添加一个新字段，而不是使用当前的CASE语句。
Don't forget that having index on any field within a condition / order by / group by is always necessary if the number of records is considerable.
不要忘记，如果记录的数量很大，那么在条件/ order by / group by中的任何字段上都有索引总是必要的。
[UPDATE]
(更新)
Your query is rather simple. The EXPLAIN's result shows that the only parts suitable as a candidate to be indexed are:
您的查询相当简单。EXPLAIN的结果表明，唯一适合作为候选索引的部分是:
CREATE INDEX inx4 ON permissions (`component_instance_id`, `group_id`, `edit`, `view`);
The EXPLAIN's second line shows that there's no index of table permissions used in your query. That's because MySQL has a couple of rules when it will use indices:
解释的第二行显示，查询中没有使用表权限的索引。这是因为MySQL在使用索引时有一些规则:
- Only one index of each table can be used in each one (sub-)query.
- 每个表的一个索引只能用于每个(子)查询。
- Any index could be used only if all its fields are mentioned in the query (as in conditions / order by / group by).
- 只有在查询中提到了所有字段(如在条件/ order by / group by)中，才可以使用任何索引。
Considering your query, and the fact that all four fields of table permissions are mentioned, you'll need an index on all four of them or it's useless.
考虑到您的查询，并且提到了表权限的所有四个字段，您将需要对所有四个字段都使用索引，否则就没有用处了。
Yet the ORDER BY can benefit from the amendment I mentioned before.
然而，《BY号命令》可以从我前面提到的修正案中受益。
#1
1
NULL is placed first (could use COALESCE to replace NULL with something else too instead of using an additional sort column). The second thing is reducing the joins, because the last two were on the id on which we concat.
首先放置NULL(可以使用合并来替换NULL，而不是使用额外的sort列)。第二件事是减少连接，因为最后两个是在我们连接的id上。
SELECT
component_instances.*,
GROUP_CONCAT(DISTINCT view_groups.id) AS view_group_ids,
GROUP_CONCAT(DISTINCT edit_groups.id) AS edit_group_ids,
GROUP_CONCAT(DISTINCT view_groups_users.user_id) AS view_user_ids,
GROUP_CONCAT(DISTINCT edit_groups_users.user_id) AS edit_user_ids
FROM
`component_instances`
LEFT OUTER JOIN permissions
ON permissions.component_instance_id = component_instances.id
LEFT OUTER JOIN groups view_groups
ON view_groups.id = permissions.group_id AND permissions.view = 1
LEFT OUTER JOIN groups edit_groups
ON edit_groups.id = permissions.group_id AND permissions.edit = 1
LEFT OUTER JOIN groups_users view_groups_users
ON view_groups_users.group_id = view_groups.id
LEFT OUTER JOIN groups_users edit_groups_users
ON edit_groups_users.group_id = edit_groups.id
GROUP BY
component_instances.id
ORDER BY
component_instances.ancestry, -- MySQL was sorting the NULL values already correctly
position
;
#2
2
It's almost impossible to optimize your query if we don't have your table structure and indices. Using a EXPLAIN statement is the necessary part of query optimizations.
如果我们没有表结构和索引，几乎不可能优化查询。使用EXPLAIN语句是查询优化的必要部分。
Without the mentioned info, all I can comment on your question is that your ORDER BY part can benefit from some optimization for sure. Using any functions or statements in a condition will always result in a disaster. Also using a nullable field in an ORDER BY will also lead to problems. Perhaps the easiest way would be adding a new field to your table holding 0s and 1s instead of the current CASE statement.
没有上述的信息，我只能评论你的问题，你的订单可以部分受益于一些优化肯定。在条件中使用任何函数或语句都会导致灾难。同时，使用可空字段也会导致问题。也许最简单的方法是在包含0和1的表中添加一个新字段，而不是使用当前的CASE语句。
Don't forget that having index on any field within a condition / order by / group by is always necessary if the number of records is considerable.
不要忘记，如果记录的数量很大，那么在条件/ order by / group by中的任何字段上都有索引总是必要的。
[UPDATE]
(更新)
Your query is rather simple. The EXPLAIN's result shows that the only parts suitable as a candidate to be indexed are:
您的查询相当简单。EXPLAIN的结果表明，唯一适合作为候选索引的部分是:
CREATE INDEX inx4 ON permissions (`component_instance_id`, `group_id`, `edit`, `view`);
The EXPLAIN's second line shows that there's no index of table permissions used in your query. That's because MySQL has a couple of rules when it will use indices:
解释的第二行显示，查询中没有使用表权限的索引。这是因为MySQL在使用索引时有一些规则:
- Only one index of each table can be used in each one (sub-)query.
- 每个表的一个索引只能用于每个(子)查询。
- Any index could be used only if all its fields are mentioned in the query (as in conditions / order by / group by).
- 只有在查询中提到了所有字段(如在条件/ order by / group by)中，才可以使用任何索引。
Considering your query, and the fact that all four fields of table permissions are mentioned, you'll need an index on all four of them or it's useless.
考虑到您的查询，并且提到了表权限的所有四个字段，您将需要对所有四个字段都使用索引，否则就没有用处了。
Yet the ORDER BY can benefit from the amendment I mentioned before.
然而，《BY号命令》可以从我前面提到的修正案中受益。