perl – 选择distinct比group by更快

schema->resultset('Entity')->search({ 
        -or => { "me.user_id" => $user_id,'set_to_user.user_id' => $user_id } 
    },{
        'distinct' => 1,'join' => {'entity_to_set' => {'entity_set' => 'set_to_user'}},'order_by' => {'-desc' => 'modified'},'page' => 1,'rows' => 100
    });

在包含表格的数据库中,如下所示.

CREATE TABLE entity (
  id varchar(500) NOT NULL,user_id varchar(100) NOT NULL,modified timestamp NOT NULL,PRIMARY KEY (id,user_id),FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE entity_to_set (
  set_id varchar(100) NOT NULL,entity_id varchar(500) NOT NULL,PRIMARY KEY (set_id,user_id,entity_id),FOREIGN KEY (entity_id,user_id) REFERENCES entity(id,user_id) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (set_id) REFERENCES entity_set(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE entity_set (
  id varchar(100) NOT NULL,PRIMARY KEY (id)
);

CREATE TABLE set_to_user (
  set_id varchar(100) NOT NULL,FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (set_id) REFERENCES entity_set(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE user (
  id varchar(100) NOT NULL,PRIMARY KEY (id)
);

我有大约6000个实体,6000个entity_to_set,10个entity_set和50个set_to_user.

现在,这个查询需要一些时间,(一两秒钟),这是不幸的.仅对实体表进行查询(包括ORDER BY)时,结果几乎是即时的.作为调试的第一步,我找到了DBIC代码变为的实际SQL查询：

SELECT me.id,me.user_id,me.modified FROM entity me
LEFT JOIN entity_to_set entity_to_set ON ( entity_to_set.entity_id = me.id AND entity_to_set.user_id = me.user_id ) 
LEFT JOIN entity_set entity_set ON entity_set.id = entity_to_set.set_id 
LEFT JOIN set_to_user set_to_user ON set_to_user.set_id = entity_set.id 
WHERE ( ( set_to_user.user_id = 'Craigy' OR me.user_id = 'Craigy' ) ) 
GROUP BY me.id,me.modified ORDER BY modified DESC LIMIT 100;

这是EXPLAIN QUERY PLAN的结果

0|0|0|SCAN TABLE entity AS me USING INDEX sqlite_autoindex_entity_1 (~1000000 rows)
0|1|1|SEARCH TABLE entity_to_set AS entity_to_set USING COVERING INDEX entity_to_set_idx_cover (entity_id=? AND user_id=?) (~9 rows)
0|2|2|SEARCH TABLE entity_set AS entity_set USING COVERING INDEX sqlite_autoindex_entity_set_1 (id=?) (~1 rows)
0|3|3|SEARCH TABLE set_to_user AS set_to_user USING COVERING INDEX sqlite_autoindex_set_to_user_1 (set_id=?) (~5 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY

其中entity_to_set_idx_cover是

CREATE INDEX entity_to_set_idx_cover ON entity_to_set (entity_id,set_id);

现在,问题是用于排序的b树,而不是在我不进行连接时使用的索引.

我注意到DBIx :: Class转换’distinct’=> 1进入GROUP BY语句(I believe the documentation says they are equivalent here).我删除了GROUP BY语句并改为使用SELECT DISTINCT,并使用以下查询

SELECT DISTINCT me.id,me.modified FROM entity me
LEFT JOIN entity_to_set entity_to_set ON ( entity_to_set.entity_id = me.id AND entity_to_set.user_id = me.user_id ) 
LEFT JOIN entity_set entity_set ON entity_set.id = entity_to_set.set_id 
LEFT JOIN set_to_user set_to_user ON set_to_user.set_id = entity_set.id 
WHERE ( ( set_to_user.user_id = 'Craigy' OR me.user_id = 'Craigy' ) ) 
ORDER BY modified DESC LIMIT 100;

我认为它给出了相同的结果.此查询的EXPLAIN QUERY PLAN是

0|0|0|SCAN TABLE entity AS me USING COVERING INDEX entity_sort_modified_user_id (~1000000 rows)
0|1|1|SEARCH TABLE entity_to_set AS entity_to_set USING COVERING INDEX entity_to_set_idx_cover (entity_id=? AND user_id=?) (~9 rows)
0|2|2|SEARCH TABLE entity_set AS entity_set USING COVERING INDEX sqlite_autoindex_entity_set_1 (id=?) (~1 rows)
0|3|3|SEARCH TABLE set_to_user AS set_to_user USING COVERING INDEX sqlite_autoindex_set_to_user_1 (set_id=?) (~5 rows)

其中entity_sort_modified_user_id是使用创建的索引

CREATE INDEX entity_sort_modified_user_id ON entity (modified,id);

这几乎是瞬间运行(没有b树).

编辑：为了证明当ORDER BY按升序排列时问题仍然存在,以及索引对这些查询的影响,这里是对相同表的类似查询.前两个查询分别使用SELECT DISTINCT和GROUP BY没有索引,后两个查询具有相同的查询和索引.

sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT me.id,me.modified FROM entity me LEFT JOIN entity_to_set entity_to_set ON ( entity_to_set.entity_id = me.id AND entity_to_set.user_id = me.user_id ) LEFT JOIN entity_set entity_set ON entity_set.id = entity_to_set.set_id WHERE ( me.user_id = 'Craigy' AND entity_set.id = 'SetID' ) ORDER BY modified LIMIT 100;
0|0|0|SCAN TABLE entity AS me (~100000 rows)
0|1|1|SEARCH TABLE entity_to_set AS entity_to_set USING AUTOMATIC COVERING INDEX (entity_id=? AND user_id=?) (~7 rows)
0|2|2|SEARCH TABLE entity_set AS entity_set USING COVERING INDEX sqlite_autoindex_entity_set_1 (id=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR DISTINCT
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> EXPLAIN QUERY PLAN SELECT me.id,me.modified FROM entity me LEFT JOIN entity_to_set entity_to_set ON ( entity_to_set.entity_id = me.id AND entity_to_set.user_id = me.user_id ) LEFT JOIN entity_set entity_set ON entity_set.id = entity_to_set.set_id WHERE ( me.user_id = 'Craigy' AND entity_set.id = 'SetID' ) GROUP BY me.id,me.modified ORDER BY modified LIMIT 100;
0|0|0|SCAN TABLE entity AS me USING INDEX sqlite_autoindex_entity_1 (~100000 rows)
0|1|1|SEARCH TABLE entity_to_set AS entity_to_set USING AUTOMATIC COVERING INDEX (entity_id=? AND user_id=?) (~7 rows)
0|2|2|SEARCH TABLE entity_set AS entity_set USING COVERING INDEX sqlite_autoindex_entity_set_1 (id=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> CREATE INDEX entity_idx_user_id_modified_id ON entity (user_id,modified,id);
sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT me.id,me.modified FROM entity me LEFT JOIN entity_to_set entity_to_set ON ( entity_to_set.entity_id = me.id AND entity_to_set.user_id = me.user_id ) LEFT JOIN entity_set entity_set ON entity_set.id = entity_to_set.set_id WHERE ( me.user_id = 'Craigy' AND entity_set.id = 'SetID' ) ORDER BY modified LIMIT 100;
0|0|0|SEARCH TABLE entity AS me USING COVERING INDEX entity_idx_user_id_modified_id (user_id=?) (~10 rows)
0|1|1|SEARCH TABLE entity_to_set AS entity_to_set USING AUTOMATIC COVERING INDEX (entity_id=? AND user_id=?) (~7 rows)
0|2|2|SEARCH TABLE entity_set AS entity_set USING COVERING INDEX sqlite_autoindex_entity_set_1 (id=?) (~1 rows)
sqlite> EXPLAIN QUERY PLAN SELECT me.id,me.modified ORDER BY modified LIMIT 100;
0|0|0|SEARCH TABLE entity AS me USING COVERING INDEX entity_idx_user_id_modified_id (user_id=?) (~10 rows)
0|1|1|SEARCH TABLE entity_to_set AS entity_to_set USING AUTOMATIC COVERING INDEX (entity_id=? AND user_id=?) (~7 rows)
0|2|2|SEARCH TABLE entity_set AS entity_set USING COVERING INDEX sqlite_autoindex_entity_set_1 (id=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

我的问题是：如何修复我的DBIx :: Class代码,使其执行与SELECT DISTINCT查询一样好.或者我如何添加索引以使其工作正常？还是需要一些其他类型的修复？