Frage Ist eine tabellarische Indexierung möglich?


Stellen Sie sich eine Struktur vor, bei der Sie eine Viele-zu-Eins-Beziehung (oder eine Eins-zu-Viele-Beziehung) zu einer Bedingung (wo, Reihenfolge nach usw.) für beide Tabellen haben. Beispielsweise:

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;

und für eine Beispielabfrage:

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

Nehmen wir an, Sie indexieren tableOne.objectId und tableTwo.eventTime. Wenn Sie dann auf die obige Abfrage erklären, wird es "Using filesort" zeigen. Im Wesentlichen gilt es zuerst die tableOne.objectId Index, aber es kann nicht angewendet werden tableTwo.eventTime Index, da dieser Index für die gesamte Tabelle (nicht die begrenzte Ergebnismenge) gilt und daher manuell sortiert werden muss.

So, Gibt es eine Möglichkeit, einen Kreuztabellenindex zu erstellen, so dass er nicht jedes Mal, wenn die Ergebnisse abgerufen werden, archivieren muss?  Etwas wie:

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

Außerdem habe ich eine Ansicht erstellt und diese indexiert, aber die Indizierung wird für Ansichten nicht unterstützt.

Die Lösung, auf die ich mich stütze, wenn die tabellarische Indizierung nicht möglich ist, besteht darin, die bedingten Daten in einer Tabelle zu replizieren. In diesem Fall bedeutet das eventTime würde repliziert werden tableOne und ein mehrspaltiger Index würde eingerichtet werden tableOne.objectId und tableOne.eventTime (im Wesentlichen manuell den Index erstellen). Ich dachte jedoch, ich würde zuerst die Erfahrung anderer Leute aufsuchen, um zu sehen, ob das der beste Weg ist.

Vielen Dank!

Aktualisieren:

Hier sind einige Verfahren zum Laden von Testdaten und zum Vergleichen von Ergebnissen:

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 ;

Sie können diese wie folgt verwenden, um 10.000 Zeilen in zu füllen tableTwo und 20.000 Zeilen in tableOne (mit zufälligen Verweisen auf tableOne und zufällig objectIds zwischen 1 und 5), die 26,2 bzw. 70,77 Sekunden benötigten, um für mich zu laufen:

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

Update 2 (Tested Triggering SQL):

Im Folgenden finden Sie das bewährte SQL, das auf der Triggermethode von daniHp basiert. Dies hält die dateTime synchron auf tableOne wann tableOne hinzugefügt oder tableTwo ist aktualisiert. Diese Methode sollte auch für Viele-zu-Viele-Beziehungen funktionieren, wenn die Bedingungsspalten in die Verbindungstabelle kopiert werden. In meiner Prüfung von 300.000 Zeilen in tableOne und 200.000 Zeilen in tableTwo, die Geschwindigkeit der alten Abfrage mit ähnlichen Grenzen war 0,12 Sekunden und die Geschwindigkeit der neuen Abfrage zeigt immer noch als 0,00 Sekunden. Es gibt also eine deutliche Verbesserung, und diese Methode sollte gut in Millionen von Reihen und weiter reichen.

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 ;

Und die aktualisierte Abfrage:

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;

18
2017-12-14 17:34


Ursprung


Antworten:


Wie Sie wissen, erreicht SQL Server dies mit indizierte Ansichten:

Indexierte Ansichten bieten zusätzliche Leistungsvorteile, die nicht erreicht werden können   erreicht mit Standard-Indizes. Indizierte Sichten können die Abfrage erhöhen   Leistung auf folgende Weise:

Aggregationen können vorberechnet und zur Minimierung im Index gespeichert werden   teure Berechnungen während der Abfrageausführung.

Tabellen können vorgemischt und der resultierende Datensatz gespeichert werden.

Kombinationen von Joins oder Aggregationen können gespeichert werden.

Um diese Technik zu nutzen, müssen Sie in SQLServer die Ansicht abfragen und nicht über die Tabellen. Das bedeutet, dass Sie über die Ansicht und die Indizes Bescheid wissen sollten.

MySQL hat keine indizierten Sichten, aber Sie können Simulieren Sie das Verhalten mit Tabelle + Trigger + Indizes.

Anstatt eine Sicht zu erstellen, müssen Sie eine indizierte Tabelle erstellen, einen Auslöser, um die Datentabelle auf dem neuesten Stand zu halten, und dann müssen Sie Ihre neue Tabelle anstelle Ihrer normalisierten Tabellen abfragen.

Sie müssen bewerten, ob der Overhead von Schreiboperationen die Verbesserung von Leseoperationen ausgleicht.

Bearbeitet:

Beachten Sie, dass es nicht immer notwendig ist, eine neue Tabelle zu erstellen. Beispielsweise können Sie in einem 1: N-Beziehung (Master-Detail) -Trigger eine Kopie eines Felds aus der Tabelle "Master" in der Tabelle "Detail" aufbewahren. In Ihrem Fall:

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;

Beachten Sie, dass dies ein Trigger vor dem Einfügen ist.

Jetzt wird die Abfrage wie folgt umgeschrieben:

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

Haftungsausschluss: nicht getestet.


5
2017-12-14 21:51



Eine tabellarische Indexierung ist in MySQL nicht möglich, außer über die jetzt nicht mehr existierende Akiban (?) Engine.

Ich habe eine Regel: "Normalisieren Sie keine kontinuierlichen Werte wie INTs, FLOATs, DATETIMEs usw." Die Kosten der JOIN Wenn Sie den kontinuierlichen Wert sortieren oder einen Bereichstest durchführen müssen, wird die Leistung beeinträchtigt.

DATETIME dauert 5 Bytes; INT dauert 4. Also ist jedes 'Raum'-Argument zur Normalisierung einer Datetime ziemlich schlecht. Es ist selten, dass Sie eine Datetime "normalisieren" müssten, wenn sich alle Verwendungen eines bestimmten Werts ändern.


1
2018-04-09 14:48



Kann ich falsch liegen, aber wenn dies meine Anwendung ist, werde ich die Daten nicht duplizieren, es sei denn, ich muss um 2 Spalten in 2 verschiedenen Tabellen bestellen, und dies ist eine heiße Abfrage (es ist oft erforderlich). Aber da es keine klare Lösung gibt, um das zu vermeiden filesortWas ist mit diesem kleinen Trick? (Erzwinge, dass der Optimierer den Index für die Spalte in der order by-Klausel verwendet eventTime)

select * from tableOne t1 
inner join tableTwo t2 use index (eventTime)  on t1.tableTwoId = t2.id and t2.eventTime > 0
where t1.objectId = 1
order by t2.eventTime desc limit 0,10;

beachten use index (eventTime) und t2.eventTime > 0

Es zeigt an, dass der Optimizer den Index für eventTime anstelle von verwendet hat filesort

1   SIMPLE  t2  range   eventTime   eventTime   5       5000    Using where; Using index
1   SIMPLE  t1  ref objectId,tableTwoId tableTwoId  4   tests.t2.id 1   Using where

0
2018-04-05 23:52