So schreiben Sie zwei Unterabfragen in MySQL

MySQL-Testumgebung

Die Testtabelle lautet wie folgt

create table test_table2
(
    id int auto_increment primary key,
    pay_id int,
    pay_time datetime,
    other_col varchar(100)
)

Build Eine gespeicherte Prozedur fügt Testdaten ein. Das Merkmal der Testdaten ist, dass pay_id wiederholbar ist. Wenn die gespeicherte Prozedur verarbeitet wird und 3 Millionen Daten in eine Schleife eingefügt werden, wird alle 100 Teile eine wiederholte pay_id eingefügt Daten. Das Zeitfeld ist innerhalb eines bestimmten Bereichs zufällig.

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
    LANGUAGE SQLNOT DETERMINISTICCONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''BEGINdeclare cnt int;set  cnt = 0;while cnt< loopcount doinsert into test_table2 (pay_id,pay_time,other_col) values  (cnt,date_add(now(), interval floor(300*rand()) day),uuid());if (cnt mod 100 = 0) theninsert into test_table2 (pay_id,pay_time,other_col) values  (cnt,date_add(now(), interval floor(300*rand()) day),uuid());end if;set cnt = cnt + 1;    end while;END

Führen Sie den Aufruf test_insert(3000000) aus >

Zwei Arten, Unterabfragen zu schreiben

Die allgemeine Bedeutung von Abfrage besteht darin, die Daten mit einer Geschäfts-ID größer als 1 innerhalb eines bestimmten Zeitraums abzufragen, daher gibt es zwei Schreibweisen.

Der erste Weg, es zu schreiben, ist wie folgt: Die IN-Unterabfrage ist die Geschäfts-ID, deren Anzahl an Geschäftsstatistikzeilen in einem bestimmten Zeitraum größer als 1 ist. Die äußere Ebene wird entsprechend den Ergebnissen abgefragt Es gibt einen Index für die Spalte pay_id der Geschäfts-ID.

Diese Schreibmethode ist in der Tat weniger effizient, wenn die Datenmenge groß ist und kein Index erforderlich ist

Ausführungsergebnis: 2,23 Sekunden

select * from test_table2 force index(idx_pay_id)where pay_id in (　　select pay_id from test_table2 
　　where pay_time>="2016-06-01 00:00:00" 
　　　　AND pay_time<="2017-07-03 12:59:59" 
　　group by pay_id 
　　having count(pay_id) > 1);

Die zweite Schreibweise besteht darin, sich mit der Unterabfrage zu verbinden. Diese Schreibweise entspricht der oben genannten Methode zum Schreiben von IN-Unterabfragen. Der folgende Test ergab, dass die Effizienz tatsächlich gut ist. Viele Verbesserungen

Ausführungsergebnis: 0,48 Sekunden

select tpp1.* from test_table2 tpp1,
(     select pay_id 
     from test_table2 
      WHERE pay_time>="2016-07-01 00:00:00" 
     AND pay_time<="2017-07-03 12:59:59" 
     group by pay_id 
     having count(pay_id) > 1) tpp2 
where tpp1.pay_id=tpp2.pay_id

Suchen Sie im Ausführungsplan der Unterabfrage nach der vollständigen Tabellenscanmethode. Der Index für pay_id wird nicht verwendet.

　

　

Später wollte ich die erzwungene Indizierung für die erste Abfragemethode verwenden, obwohl kein Fehler gemeldet wurde, habe ich festgestellt, dass dies der Fall ist war überhaupt nutzlos.

　

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

　

　

　 Es ist ersichtlich, dass die Unterstützung von MySQL für IN-Unterabfragen tatsächlich nicht sehr gut ist.

Darüber hinaus ist das Hinzufügen einer temporären Tabelle zwar effizienter als viele Join-Methoden, aber auch effizienter als die direkte Verwendung von IN-Unterabfragen. In diesem Fall ist dies auch möglich werden verwendet, aber in diesem einfachen Fall besteht keine Notwendigkeit, eine temporäre Tabelle zu verwenden.

　

　

Das Folgende ist ein Test eines ähnlichen Falls in SQL Server 2014, Es gibt Zehntausende identischer Testtabellenstrukturen und -mengen. In diesem Fall können die beiden Schreibmethoden in SQL Server als genau gleich angesehen werden (Ausführungsplan + Effizienz). SQL Server ist viel besser als MySQL

Das Folgende ist das Testumgebungsskript in SQL Server.

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

Das obige ist der detaillierte Inhalt vonSo schreiben Sie zwei Unterabfragen in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!