Optimierungsbeispiel für die gespeicherte MySQL-Prozedur

Vorwort

Im Prozess der Datenbankentwicklung treten häufig komplexe Geschäftslogik- und Datenbankoperationen auf. Derzeit werden gespeicherte Prozeduren zum Kapseln von Datenbankoperationen verwendet. Wenn das Projekt über viele gespeicherte Prozeduren verfügt und die Schreibweise nicht standardisiert ist, wird es schwierig, das System in Zukunft zu warten und die Logik der großen gespeicherten Prozeduren zu verstehen Wenn die Datenbank groß ist oder das Projekt hohe Leistungsanforderungen an gespeicherte Prozeduren stellt, wird es in Zukunft schwierig sein, das System zu warten. Andernfalls kann die Geschwindigkeit einer optimierten gespeicherten Prozedur sehr langsam sein sogar hundertmal effizienter als eine gespeicherte Prozedur mit schlechter Leistung. Im Folgenden wird der gesamte Prozess der Optimierung einer bestimmten gespeicherten MySQL-Prozedur vorgestellt.

In diesem Artikel sind die gespeicherten Prozeduren , die optimiert werden müssen, wie folgt:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
  in  p_boxnumber  varchar(30)
)
pr_dealtestnum_label:begin

    insert into tb_testnum select boxnumber,usertype from tb_testnum_tmp where boxnumber= p_boxnumber;

    leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnumok';

Nach dem Login kopieren

Die in der gespeicherten Prozedur verwendete Tabelle tb_testnum hat die folgende Struktur:

drop table if exists tb_testnum;

create table tb_testnum
(
  boxnumber varchar(30) not null,
  usertype  int     not null                                         
);
create unique index idx1_tb_testnum ontb_testnum(boxnumber);

Nach dem Login kopieren

Eine weitere Tabelle tb_testnum_tmp, die in der gespeicherten Prozedur verwendet wird, hat die folgende Struktur:

drop table if exists tb_testnum_tmp;

create table tb_testnum_tmp
(
  boxnumber varchar(30) not null,
  usertype  int     not null  
);
create unique index idx1_tb_testnum_tmp ontb_testnum_tmp(boxnumber);

Nach dem Login kopieren

Aus der Struktur der beiden Tabellen ist ersichtlich, dass tb_testnum und tb_testnum_tmp genau die gleichen Felder enthalten. Die Funktion der gespeicherten Prozedur pr_dealtestnum ist basierend auf den Eingabeparametern. Fügen Sie Daten aus der Tabelle tb_testnum_tmp in die Tabelle tb_testnum ein.

Obwohl die erwartete Funktion erreicht werden kann, ist der Code der gespeicherten Prozedur pr_dealtestnum offensichtlich noch verbesserungswürdig.

Im Folgenden optimieren wir es Schritt für Schritt.

Optimierung 1

Der Hauptteil der

gespeicherten Prozedur pr_dealtestnum ist eine Einfügeanweisung, aber diese Einfügeanweisung enthält auch eine Auswahlanweisung. Eine solche Schreibweise ist unregelmäßig. Daher müssen wir diese Einfügeanweisung in zwei Anweisungen aufteilen, d. h. zuerst die Daten aus der Tabelle tb_testnum_tmp suchen und sie dann in die Tabelle tb_testnum einfügen. Die geänderte gespeicherte Prozedur lautet wie folgt:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
  in  p_boxnumber  varchar(30)
)
pr_dealtestnum_label:begin
    declare p_usertype  int;

    select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;

    insert into tb_testnum values(p_boxnumber,p_usertype);

    leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

Nach dem Login kopieren

Optimierung 2

Vor dem Einfügen von Daten in die Tabelle tb_testnum muss festgestellt werden, ob die Daten bereits in der Tabelle vorhanden sind. Wenn sie vorhanden sind, werden keine weiteren Daten eingefügt. Auf die gleiche Weise müssen Sie vor dem Abfragen von Daten aus der Tabelle tb_testnum_tmp zunächst feststellen, ob die Daten in der Tabelle vorhanden sind. Wenn sie vorhanden sind, können Sie in der Tabelle nach Daten suchen. Die geänderte gespeicherte Prozedur lautet wie folgt:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
  in  p_boxnumber  varchar(30)
)
pr_dealtestnum_label:begin
    declare p_usertype  int;
    declare p_datacount int;

    select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
    if p_datacount > 0 then
    begin
      select usertype into p_usertype fromtb_testnum_tmp where boxnumber=p_boxnumber;
    end;
    else
    begin
      leave pr_dealtestnum_label;
    end;
    end if;

    select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
    if p_datacount = 0 then
    begin
      insert into tb_testnum values(p_boxnumber,p_usertype);
      leave pr_dealtestnum_label;
    end;
    else
    begin
      leave pr_dealtestnum_label;
    end;
    end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

Nach dem Login kopieren

Optimierung 3

Unabhängig davon, ob der Vorgang des Einfügens von Daten in die Tabelle tb_testnum erfolgreich ausgeführt wird oder nicht, sollte es einen Identifikationswert geben, der das Ergebnis der Ausführung darstellt, was Entwicklern auch das Verfolgen und Debuggen erleichtert Programmablauf. Mit anderen Worten: Vor jeder Leave-Anweisung sollte ein Rückgabewert stehen, für den wir einen Ausgabeparameter definieren. Die geänderte gespeicherte Prozedur lautet wie folgt:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
  in  p_boxnumber varchar(30),
  out  p_result   int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
    declare p_usertype  int;
    declare p_datacount int;

    select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
    if p_datacount > 0 then
    begin
      select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
    end;
    else
    begin
      set p_result = 1;
      leave pr_dealtestnum_label;
    end;
    end if;

    select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
    if p_datacount = 0 then
    begin
      insert into tb_testnum values(p_boxnumber,p_usertype);
      set p_result = 0;
      leave pr_dealtestnum_label;
    end;
    else
    begin
      set p_result = 2;
      leave pr_dealtestnum_label;
    end;
    end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

Nach dem Login kopieren

Optimierung 4

Wir haben festgestellt, dass in der Anweisung „insert into tb_testnum values(p_boxnumber,p_usertype);” nach der Tabelle tb_testnum kein spezifischer Feldname aufgeführt ist, was ebenfalls unregelmäßig ist. In zukünftigen Softwareversionen gilt: tb_testnum Wenn der Tabelle ein neues Feld hinzugefügt wird, meldet diese Einfügeanweisung höchstwahrscheinlich einen Fehler. Daher lautet die Standardschreibweise, dass die Details unabhängig davon sind, wie viele Felder in der Tabelle tb_testnum vorhanden sind Der Feldname muss beim Ausführen des Einfügevorgangs wie folgt aufgeführt werden:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
  in  p_boxnumber varchar(30),
  out  p_result   int  -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
    declare p_usertype  int;
    declare p_datacount int;

    select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
    if p_datacount > 0 then
    begin
      select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
    end;
    else
    begin
      set p_result = 1;
      leave pr_dealtestnum_label;
    end;
    end if;

    select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
    if p_datacount = 0 then
    begin
      insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
      set p_result = 0;
      leave pr_dealtestnum_label;
    end;
    else
    begin
      set p_result = 2;
      leave pr_dealtestnum_label;
    end;
    end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

Nach dem Login kopieren

Optimierung 5

Verwenden Sie nach dem Ausführen der Einfügeanweisung den mit MySQL gelieferten @error_count-Parameter, um zu bestimmen, ob die eingefügten Daten erfolgreich sind, damit Entwickler die Ausführungsergebnisse verfolgen können. Wenn der Wert dieses Parameters nicht 0 ist, bedeutet dies, dass das Einfügen fehlgeschlagen ist. Anschließend verwenden wir einen Rückgabeparameterwert, um anzugeben, dass der Vorgang fehlgeschlagen ist. Die geänderte gespeicherte Prozedur lautet wie folgt:

drop procedure if exists pr_dealtestnum;
delimiter //

create procedure pr_dealtestnum
(
  in  p_boxnumber varchar(30),
  out  p_result  int  -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
    declare p_usertype  int;
    declare p_datacount int;

    select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
    if p_datacount> 0 then
    begin
      select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
    end;
    else
    begin
      set p_result = 1;
      leave pr_dealtestnum_label;
    end;
    end if;

    select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
    if p_datacount = 0then
    begin
      insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
      if @error_count<>0 then
      begin
        set p_result= 3;
      end;
      else
      begin
        set p_result= 0;
      end;
      end if;
    end;
    else
    begin
      set p_result = 2;
    end;
    end if;

    leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';

Nach dem Login kopieren

Zusammenfassung

Wie aus dem Obigen hervorgeht, gibt es in einer kurzen gespeicherten Prozedur so viele Bereiche, die optimiert werden müssen. Es scheint, dass das Schreiben einer gespeicherten Prozedur keine einfache Angelegenheit ist. Wenn wir Code schreiben (nicht nur gespeicherte Prozeduren), müssen wir die Funktion, Lesbarkeit, Leistung und andere Aspekte des Codes berücksichtigen, damit wir schönen Code mit einem langen Lebenszyklus schreiben und dann hochwertige Softwareprodukte entwickeln können . Ich hoffe, dass dieser Artikel allen beim Erlernen gespeicherter MySQL-Prozeduren hilfreich sein kann, und danke Ihnen für Ihre Unterstützung.

Optimierungsbeispiel für die gespeicherte MySQL-Prozedur_MySQL