4 Möglichkeiten zur Optimierung von MySQL-Abfragen für Millionen von Daten

Wählen Sie * aus t_name, wobei c_name1 = "xxx" ist, sortiert nach c_name2, Limit 2000000,25;

/*Abteilungstabelle, löschen, falls vorhanden*/
Tabelle löschen, wenn EXISTIERT dep;
Tabelle erstellen dep(
    id int vorzeichenloser Primärschlüssel auto_increment,
    depno mediumint unsigned nicht null Standard 0,
    depname varchar(20) ungleich null Standard "",
    Memo varchar (200) nicht null Standard ""
);

/*Mitarbeitertabelle, löschen, falls vorhanden*/
Tabelle löschen, wenn emp EXISTIERT;
Tabelle emp erstellen(
    id int vorzeichenloser Primärschlüssel auto_increment,
    empno mediumint unsigned nicht null Standard 0,
    empname varchar(20) nicht null Standard "",
    Job varchar(9) nicht null Standard "",
    mgr mediumint unsigniert nicht null Standard 0,
    Einstellungsdatum, Datum und Uhrzeit sind nicht null,
    sal decimal(7,2) nicht null,
    comn decimal(7,2) nicht null,
    depno mediumint unsigniert nicht null Standard 0
);
/* Funktion zum Generieren eines zufälligen Strings */
TRENNUNGSZEICHEN $
FUNKTION löschen, wenn Rand_String EXISTIERT;
CREATE FUNCTION rand_string(n INT) gibt VARCHAR(255) zurück
BEGINNEN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WÄHREND ich < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    Setze i = i+1;
    ENDE WÄHREND;
    RETURN return_str;
ENDE $
TRENNUNGSZEICHEN;


/*Funktion zum Generieren einer zufälligen Abteilungsnummer*/
TRENNUNGSZEICHEN $
Funktion löschen, wenn rand_num EXISTIERT;
Funktion erstellen rand_num() gibt INT(5) zurück
BEGINNEN
    DECLARE i INT DEFAULT 0;
    SETZE i = FLOOR(100+RAND()*10);
    ZURÜCK i;
ENDE $
TRENNUNGSZEICHEN;
/*Erstellen Sie eine gespeicherte Prozedur: Fügen Sie Daten in die Tabelle emp ein*/
TRENNUNGSZEICHEN $
VERFAHREN löschen, wenn EXISTIERT insert_emp;
PROZEDUR ERSTELLEN insert_emp(IN START INT(10),IN max_num INT(10))
BEGINNEN
    DECLARE i INT DEFAULT 0;
    /*set autocommit =0 Setze Autocommit auf 0 und schalte das Standard-Commit aus*/
    SETZEN Sie Autocommit = 0;
    WIEDERHOLEN
    Setze i = i + 1;
    INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'VERKÄUFER',0001,now(),2000,400,rand_num());
    BIS i = max_num
    ENDE WIEDERHOLUNG;
    BEGEHEN;
ENDE $
TRENNUNGSZEICHEN;

/*Erstellen Sie eine gespeicherte Prozedur: Fügen Sie Daten in die Dep-Tabelle ein*/
TRENNUNGSZEICHEN $
VERFAHREN löschen, falls EXISTIERT: insert_dept;
PROZEDUR ERSTELLEN insert_dept(IN START INT(10), IN max_num INT(10))
BEGINNEN
    DECLARE i INT DEFAULT 0;
    SETZEN Sie Autocommit = 0;
    WIEDERHOLEN
    Setze i = i+1;
    INSERT INTO dep( depno, depname, memo) VALUES((START+i), Randzeichenfolge(10), Randzeichenfolge(8));
    BIS i = max_num
    ENDE WIEDERHOLUNG;
    BEGEHEN;
ENDE $
TRENNUNGSZEICHEN;

/*120 Datensätze einfügen*/
rufen Sie insert_dept(1,120) auf;
/*Fügen Sie 5 Millionen Daten ein*/
rufen Sie insert_emp(0,5000000) auf;

/*Offset ist 100, nimm 25*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 100,25;
/*Offset ist 4800000, nimm 25*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 4800000,25;

[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 100,25;
Betroffene Zeilen: 0
Zeit: 0,001 s
[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a nach links verbinden dep b auf a.depno = b.depno, sortiert nach a.id, desc-Limit 4800000,25;
Betroffene Zeilen: 0
Zeit: 12.275s

/*Die Unterabfrage ruft die ID der um 100 verschobenen Position ab und erhält 25 nach dieser Position*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus emp auswählen, sortiert nach ID-Limit 100,1)
Sortieren nach A.ID-Limit 25;

/*Die Unterabfrage ruft die ID der Position mit dem Offset 4800000 ab und erhält 25 nach dieser Position*/
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus Emp auswählen, sortiert nach ID-Limit 4800000,1)
Sortieren nach A.ID-Limit 25;

[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus emp auswählen, sortiert nach ID-Limit 100,1)
Sortieren nach A.ID-Limit 25;
Betroffene Zeilen: 0
Zeit: 0,106 s

[SQL]
Wählen Sie a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id >= (ID aus Emp auswählen, sortiert nach ID-Limit 4800000,1)
Sortieren nach A.ID-Limit 25;
Betroffene Zeilen: 0
Zeit: 1.541s

/*Denken Sie daran, dass die ID der letzten Daten bei der vorherigen Paginierung 100 ist. Daher überspringen wir 100 und scannen die Tabelle ab 101*/
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 100, sortiert nach a.id-Limit 25;

/*Denken Sie daran, dass die ID der letzten Daten bei der vorherigen Paginierung 4800000 ist. Daher überspringen wir 4800000 und scannen die Tabelle ab 4800001*/
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 4800000
Sortieren nach A.ID-Limit 25;

[SQL]
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 100, sortiert nach a.id-Limit 25;
Betroffene Zeilen: 0
Zeit: 0,001 s

[SQL]
Wählen Sie a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
von emp a links verbinden dep b auf a.depno = b.depno
wobei a.id > 4800000
Sortieren nach A.ID-Limit 25;
Betroffene Zeilen: 0
Zeit: 0.000s