Lösen Sie das Problem, dass IN-Unterabfragen in MySQL dazu führen, dass der Index unbrauchbar wird

Heute habe ich eine Fallstudie zur MySQL IN-Unterabfrageoptimierung gesehen.

Ich war zunächst etwas skeptisch (das wäre in SQL Server unmöglich, aber ich werde später einen einfachen Test durchführen).

Dann habe ich eine Tabelle erstellt, um sie entsprechend seinen Angaben zu testen und zu überprüfen, und festgestellt, dass die IN-Unterabfrage von MySQL nicht gut ausgeführt wurde, was tatsächlich dazu führen würde, dass der Index nicht verwendet werden konnte (die IN-Unterabfrage konnte nicht verwendet werden, daher war das Szenario MySQL und die Endversion war 5.7.18).

MySQL-Testumgebung

Die Testtabelle sieht wie folgt aus

Tabelle test_table2 erstellen
(
  id int auto_increment Primärschlüssel,
  pay_id int,
  Zahlungszeitpunkt Datum/Uhrzeit,
  andere_Spalte varchar(100)
)

Erstellen Sie eine gespeicherte Prozedur zum Einfügen von Testdaten. Das Merkmal der Testdaten ist, dass pay_id wiederholbar ist. Hier wird in der gespeicherten Prozedur während der Schleifeneinfügung von 3 Millionen Datenelementen alle 100 Datenelemente eine wiederholte pay_id eingefügt. Das Zeitfeld ist innerhalb eines bestimmten Bereichs zufällig.

DEFINER ERSTELLEN=`root`@`%` VERFAHREN `test_insert`(IN `loopcount` INT)
  SPRACHE SQL
  NICHT DETERMINISTISCH
  ENTHÄLT SQL
  SQL-SICHERHEITSDEFINIERER
  KOMMENTAR ''
BEGINNEN
  Deklariere cnt int;
  setze cnt = 0;
  während cnt< Schleifenanzahl do
    in test_table2 einfügen (pay_id,pay_time,other_col) Werte (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    wenn (cnt mod 100 = 0) dann
      in test_table2 einfügen (pay_id,pay_time,other_col) Werte (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    Ende wenn;
    setze cnt = cnt + 1;  
  Ende während;
ENDE

Führen Sie den Aufruf test_insert(3000000) aus und fügen Sie 303000 Datenzeilen ein.

Zwei Möglichkeiten zum Schreiben von Unterabfragen

Die Abfrage bedeutet im Wesentlichen, die Daten mit einer Geschäfts-ID größer als 1 innerhalb eines bestimmten Zeitraums abzufragen. Es gibt daher zwei Schreibweisen.

Die erste Schreibweise ist wie folgt: Die IN-Unterabfrage enthält die Geschäfts-ID, deren Geschäftsstatistiken innerhalb eines bestimmten Zeitraums größer als 1 sind. Die äußere Schicht fragt entsprechend dem Ergebnis der IN-Unterabfrage ab. Die Spalte pay_id der Geschäfts-ID hat einen Index und die Logik ist relativ einfach. Diese Schreibweise ist tatsächlich ineffizient, wenn die Datenmenge groß ist und kein Index benötigt wird.

wähle * aus test_table2, erzwinge Index(idx_pay_id)
wobei pay_id in (
  Wählen Sie Pay_ID aus Test_Tabelle2 
  wobei pay_time>="01.06.2016 00:00:00" 
    UND pay_time<="2017-07-03 12:59:59" 
  Gruppieren nach Pay_ID 
  mit count(pay_id) > 1
);

Ausführungsergebnis: 2,23 Sekunden

Die zweite Schreibweise ist die Verknüpfung mit der Unterabfrage. Diese Schreibweise entspricht der obigen IN-Unterabfrage. Der folgende Test ergab, dass die Effizienz tatsächlich erheblich verbessert wurde.

wähle tpp1.* aus test_table2 tpp1, 
(
   Wählen Sie die Zahlungs-ID aus 
   von test_table2 
   WO pay_time>="2016-07-01 00:00:00" 
   UND pay_time<="2017-07-03 12:59:59" 
   Gruppieren nach Pay_ID 
   mit count(pay_id) > 1
) tpp2 
wobei tpp1.pay_id=tpp2.pay_id

Ausführungsergebnis: 0,48 Sekunden

Im Ausführungsplan der Unterabfrage wird festgestellt, dass es sich bei der äußeren Abfrage um einen vollständigen Tabellenscan handelt und der Index auf pay_id nicht verwendet wird.

Im Ausführungsplan des Join-Self-Checks verwendet die äußere Schicht (Abfrage des tpp1-Alias) den Index auf pay_id.

Später wollte ich für die erste Abfragemethode einen erzwungenen Index verwenden. Obwohl kein Fehler auftrat, stellte ich fest, dass es nutzlos war.

Wenn die Unterabfrage ein direkter Wert ist, kann der Index normal verwendet werden.

Es ist ersichtlich, dass die MySQL-Unterstützung für IN-Unterabfragen wirklich nicht sehr gut ist.

Außerdem: Wenn wir einen Fall hinzufügen, in dem eine temporäre Tabelle verwendet wird, ist dies zwar effizienter als viele Join-Abfragen, aber auch effizienter als die direkte Verwendung der IN-Unterabfrage. In diesem Fall kann auch der Index verwendet werden, aber in diesem einfachen Fall ist die Verwendung einer temporären Tabelle nicht erforderlich.

Nachfolgend sehen Sie einen ähnlichen Testfall in SQL Server 2014. Die Struktur und Anzahl der Testtabellen sind genau gleich. Es ist ersichtlich, dass in diesem Fall die beiden Schreibmethoden in SQL Server als genau gleich angesehen werden können (Ausführungsplan + Effizienz). In dieser Hinsicht ist SQL Server viel besser als MySQL.

Unten finden Sie das Testumgebungsskript im SQLServer.

Tabelle test_table2 erstellen
(
  id int identity(1,1) Primärschlüssel,
  pay_id int,
  Zahlungszeitpunkt Datum/Uhrzeit,
  andere_Spalte varchar(100)
)
beginne mit der Übertragung
deklarieren Sie @i int = 0
während @i<300000
beginnen
  in test_table2 Werte einfügen (@i,getdate()-rand()*300,newid());
  wenn(@i%1000=0)
  beginnen
    in test_table2 Werte einfügen (@i,getdate()-rand()*300,newid());
  Ende
  setze @i = @i + 1
Ende
BEGEHEN
GEHEN
Erstellen Sie den Index idx_pay_id auf test_table2(pay_id);
Erstellen Sie den Index idx_time auf test_table2(pay_time);
GEHEN
Wählen Sie * aus Testtabelle2 
wobei pay_id in (
          Wählen Sie Pay_ID aus Test_Tabelle2 
          wobei pay_time>='2017-01-21 00:00:00' 
          UND pay_time<='2017-07-03 12:59:59' 
          Gruppieren nach Pay_ID 
          mit count(pay_id) > 1
        );
wähle tpp1.* aus test_table2 tpp1, 
(
   Wählen Sie die Zahlungs-ID aus. 
   von test_table2 
   WO Zahlzeit>='2017-01-21 00:00:00'
   UND pay_time<='2017-07-30 12:59:59' 
   Gruppieren nach Pay_ID mit 
   Anzahl(Zahlungs-ID) > 1
) tpp2 
wobei tpp1.pay_id=tpp2.pay_id

Zusammenfassung: In MySQL-Daten sollten IN-Unterabfragen ab Version 5.7.18 immer noch mit Vorsicht verwendet werden