Time to Read

10分

なぜ

MySQLは、WITH RECURSIVE文をサポートしていないため、たとえば以下のような自己参照テーブル “comments”

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_id  | int(11)      | YES  |     | NULL    |                |
| body       | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
| updated_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

において、あるレコードを起点とした、その子/孫レコードすべてを取ってくる、ということがSQL一回ではしづらい。

Ruby on Railsなら acts_as_tree 等を用いることができそうだが、何度もSQLを投げるため、よりパフォーマンスを上げたいのであれば、例えばストアドプロシージャに頼る等が考えられる。

実コード

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

delimiter //
 
CREATE PROCEDURE find_root_and_children(IN root_id INT)
BEGIN
    -- 空の一時テーブルを二つ用意
    CREATE TEMPORARY TABLE comments_stack (
        `id` INT(11) NULL,
        `parent_id` INT(11) NULL,
        `body` VARCHAR(255) COLLATE utf8_unicode_ci NULL,
        `created_at` datetime NULL,
        `updated_at` datetime NULL
    ) ENGINE = MEMORY;
    CREATE TEMPORARY TABLE comments_old_stack (
        `id` INT(11) NULL,
        `parent_id` INT(11) NULL,
        `body` VARCHAR(255) COLLATE utf8_unicode_ci NULL,
        `created_at` datetime NULL,
        `updated_at` datetime NULL
    ) ENGINE = MEMORY;
 
    -- スタックに、Rootコメントを挿入
    INSERT INTO comments_stack
        SELECT * FROM comments WHERE id = root_id;
 
    -- 新旧のDISTINCTのテーブル件数が一致する（増えなくなる）まで繰り返し
    WHILE (SELECT COUNT(DISTINCT id) FROM comments_stack) <> \
              (SELECT COUNT(DISTINCT id) FROM comments_old_stack) DO
        -- 旧テーブルに前回ループの件数をコピー
        TRUNCATE TABLE comments_old_stack;
        INSERT INTO comments_old_stack
            SELECT * FROM comments_stack;
        -- 親IDが、それまでに取得した集合中に存在するレコードを挿入
        INSERT INTO comments_stack
            SELECT * FROM comments c1
            WHERE c1.parent_id IN (SELECT c2.id FROM comments_old_stack c2);
    END WHILE;
 
    -- 結果表示
    SELECT DISTINCT * FROM comments_stack;
    -- 後処理
    DROP TEMPORARY TABLE comments_stack;
    DROP TEMPORARY TABLE comments_old_stack;
END
//
 
delimiter ;

カーソルを使ってないし、DISTINCTとか冗長に見えると思うんデスが、この書き方だと、SELECT文の回数が要素ツリーの「件数」ではなく「深さ」に比例するんで、割と高速なはず。と思う。

mysql> call find_root_and_children(1);
+------+-----------+------+---------------------+---------------------+
| id   | parent_id | body | created_at          | updated_at          |
+------+-----------+------+---------------------+---------------------+
|    1 |      NULL | root | 2010-08-20 07:07:02 | 2010-08-20 07:07:02 |
|    2 |         1 | c1   | 2010-08-20 07:07:26 | 2010-08-20 07:07:26 |
|    3 |         1 | c2   | 2010-08-20 07:07:32 | 2010-08-20 07:07:32 |
|    4 |         2 | c1-1 | 2010-08-20 07:07:38 | 2010-08-20 07:07:38 |
|    5 |         2 | c1-2 | 2010-08-20 07:07:40 | 2010-08-20 07:07:40 |
|    6 |         2 | c1-3 | 2010-08-20 07:07:43 | 2010-08-20 07:07:43 |
|    7 |         3 | c2-1 | 2010-08-20 07:07:49 | 2010-08-20 07:07:49 |
+------+-----------+------+---------------------+---------------------+
7 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call find_root_and_children(2);
+------+-----------+------+---------------------+---------------------+
| id   | parent_id | body | created_at          | updated_at          |
+------+-----------+------+---------------------+---------------------+
|    2 |         1 | c1   | 2010-08-20 07:07:26 | 2010-08-20 07:07:26 |
|    4 |         2 | c1-1 | 2010-08-20 07:07:38 | 2010-08-20 07:07:38 |
|    5 |         2 | c1-2 | 2010-08-20 07:07:40 | 2010-08-20 07:07:40 |
|    6 |         2 | c1-3 | 2010-08-20 07:07:43 | 2010-08-20 07:07:43 |
+------+-----------+------+---------------------+---------------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

たとえば、Railsと統合するなら

上記のストプロを定義/DROPするSQLを直に投げる感じのMigrationを作成、db:migrateしとく。

Commentモデルに以下のようなクラスメソッドを追加。

1
2
3
4
5
6
7
8
9

class Comment < ActiveRecord::Base
  class << self
    def find_root_with_children_by_id(rid)
      ret = find_by_sql(["call find_root_and_children(?)", rid])
      connection.reconnect!
      ret
    end
  end
end

reconnect! してるのは、バグっつーか、そもそもストプロ使うな！って話かもしれんけどね：

It looks like CALLing a stored procedure will drop a ActiveRecord
connection. Maybe a “reconnect! if !active?” can be a quick patch…

ということなので、クイックに追加。でも、最新の2.3系ではパッチが当てられてるの？ 未検証（gem経由で入れた2.3.8では全然ダメです）。。。

所感

  • ストプロは、まあ、何度もSQL投げるより全然速いよね。
  • ストプロって読み辛いしテストもろくに書けないので、追加するなら慎重に管理したほうがいいと思いますよ。