☰

クロステーブルインデックスは可能ですか? 質問する

tag-icon • tag-icon mysql join indexing
クロステーブルインデックスは可能ですか? 質問する

両方のテーブルに条件 (where、order by など) を持つ多対 1 (または 1 対多) の関係がある構造を考えてみましょう。例:

CREATE TABLE tableTwo (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    eventTime DATETIME NOT NULL,
    INDEX (eventTime)
) ENGINE=InnoDB;

CREATE TABLE tableOne (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tableTwoId INT UNSIGNED NOT NULL,
    objectId INT UNSIGNED NOT NULL,
    INDEX (objectID),
    FOREIGN KEY (tableTwoId) REFERENCES tableTwo (id)
) ENGINE=InnoDB;

クエリの例:

select * from tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id
  where objectId = '..'
  order by eventTime;

tableOne.objectIdと にインデックスを付けるとしますtableTwo.eventTime。上記のクエリを説明すると、「Using filesort」と表示されます。基本的に、最初にインデックスを適用しますtableOne.objectIdが、そのインデックスは tableTwo 全体 (限定された結果セットではない) を対象としているため、インデックスを適用できずtableTwo.eventTime、手動でソートする必要があります。

したがって、結果が取得されるたびにファイルをソートする必要がないように、テーブル間のインデックスを作成する方法はありますか?何かのようなもの：

create index ind_t1oi_t2et on tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id 
  (t1.objectId, t2.eventTime);

また、ビューを作成してインデックスを作成することも検討しましたが、ビューではインデックス作成はサポートされていません。

テーブル間のインデックス作成が不可能な場合に私が検討している解決策は、条件付きデータを 1 つのテーブルに複製することです。この場合、eventTime複製されtableOne、複数列のインデックスが設定されますtableOne.objectId(tableOne.eventTime基本的に手動でインデックスを作成します)。ただし、まず他の人の経験を調べて、それが最善の方法であるかどうかを確認しようと思いました。

どうもありがとう！

アップデート：

テスト データをロードして結果を比較する手順を次に示します。

drop procedure if exists populate_table_two;
delimiter #
create procedure populate_table_two(IN numRows int)
begin
declare v_counter int unsigned default 0;
  while v_counter < numRows do
    insert into tableTwo (eventTime) 
    values (CURRENT_TIMESTAMP - interval 0 + floor(0 + rand()*1000) minute);
    set v_counter=v_counter+1;
  end while;
end #
delimiter ;

drop procedure if exists populate_table_one;
delimiter #
create procedure populate_table_one
   (IN numRows int, IN maxTableTwoId int, IN maxObjectId int)
begin
declare v_counter int unsigned default 0;
  while v_counter < numRows do
    insert into tableOne (tableTwoId, objectId) 
      values (floor(1 +(rand() * maxTableTwoId)), 
              floor(1 +(rand() * maxObjectId)));
    set v_counter=v_counter+1;
  end while;
end #
delimiter ;

これらを次のように使用して、 に 10,000 行tableTwo、 に 20,000 行tableOne( へのランダムな参照と1 から 5 までのtableOneランダムなobjectIds を使用) を入力できます。私の場合、実行にそれぞれ 26.2 秒と 70.77 秒かかりました。

call populate_table_two(10000);
call populate_table_one(20000, 10000, 5);

更新 2 (トリガー SQL のテスト):

以下は、daniHp のトリガー メソッドに基づいて試行され、テストされた SQL です。これにより、が追加または更新されたときdateTimeに が同期されます。また、条件列が結合テーブルにコピーされている場合、このメソッドは多対多の関係でも機能します。 の 300,000 行と の200,000 行のテストでは、同様の制限を持つ古いクエリの速度は 0.12 秒でしたが、新しいクエリの速度は依然として 0.00 秒と表示されています。したがって、明らかな改善があり、このメソッドは数百万行以上でも十分に機能するはずです。tableOnetableOnetableTwotableOnetableTwo

alter table tableOne add column tableTwo_eventTime datetime;

create index ind_t1_oid_t2et on tableOne (objectId, tableTwo_eventTime);

drop TRIGGER if exists t1_copy_t2_eventTime;
delimiter #
CREATE TRIGGER t1_copy_t2_eventTime
   BEFORE INSERT ON tableOne
for each row
begin
  set NEW.tableTwo_eventTime = (select eventTime 
       from tableTwo t2
       where t2.id = NEW.tableTwoId);
end #
delimiter ;

drop TRIGGER if exists upd_t1_copy_t2_eventTime;
delimiter #
CREATE TRIGGER upd_t1_copy_t2_eventTime
   BEFORE UPDATE ON tableTwo
for each row
begin
  update tableOne 
    set tableTwo_eventTime = NEW.eventTime 
    where tableTwoId = NEW.id;
end #
delimiter ;

更新されたクエリ:

select * from tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id
  where t1.objectId = 1
  order by t1.tableTwo_eventTime desc limit 0,10;

ベストアンサー1

ご存知のとおり、SQLServerはこれを実現します。インデックス付きビュー:

インデックス付きビューは、標準のインデックスでは実現できない追加のパフォーマンス上の利点を提供します。インデックス付きビューは、次の方法でクエリ パフォーマンスを向上できます。

集計を事前に計算してインデックスに保存することで、クエリ実行中のコストのかかる計算を最小限に抑えることができます。

テーブルを事前結合し、結果のデータ セットを保存できます。

結合または集計の組み合わせを保存できます。

SQLServer でこの手法を利用するには、テーブルではなくビューに対してクエリを実行する必要があります。つまり、ビューとインデックスについて知っておく必要があります。

MySQLにはインデックスビューはありませんが、テーブル + トリガー + インデックスを使用して動作をシミュレートする。

ビューを作成する代わりに、インデックス付きテーブルと、データ テーブルを最新の状態に保つトリガーを作成し、正規化されたテーブルではなく新しいテーブルをクエリする必要があります。

書き込み操作のオーバーヘッドが読み取り操作の改善を相殺するかどうかを評価する必要があります。

編集済み:

必ずしも新しいテーブルを作成する必要はありません。たとえば、1:N 関係 (マスター詳細) トリガーでは、フィールドのコピーを「マスター」テーブルから「詳細」テーブルに保持できます。この場合、次のようになります。

CREATE TABLE tableOne (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tableTwoId INT UNSIGNED NOT NULL,
    objectId INT UNSIGNED NOT NULL,
    desnormalized_eventTime DATETIME NOT NULL,
    INDEX (objectID),
    FOREIGN KEY (tableTwoId) REFERENCES tableTwo (id)
) ENGINE=InnoDB;

CREATE TRIGGER tableOne_desnormalized_eventTime
   BEFORE INSERT ON tableOne
for each row
begin
  DECLARE eventTime DATETIME;
  SET eventTime = 
      (select eventTime 
       from tableOne
       where tableOne.id = NEW.tableTwoId);
  NEW.desnormalized_eventTime = eventTime;
end;

これは挿入前のトリガーであることに注意してください。

クエリは次のように書き換えられます。

select * from tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id
  where t1.objectId = '..'
  order by t1.desnormalized_eventTime;

免責事項: テストされていません。

おすすめ記事