Detaillierte Erklärung der Verwendung und Unterschiede zwischen Indizes und Ansichten in MySQL

Vorwort

Dieser Artikel stellt hauptsächlich die Verwendung und Unterschiede von Indizes und Ansichten in MySQL vor und gibt sie zu Ihrer Information und zum Lernen weiter. Schauen wir uns ohne weiteres die ausführliche Einführung an.

Index

1. Übersicht

Alle MySQL-Spaltentypen können indiziert werden.

MySQL unterstützt BTREE-Index, HASH-Index, Präfixindex, Volltextindex (FULLTEXT) [nur von der MyISAM-Engine unterstützt und auf Char-, Varchar- und Textspalten beschränkt], räumlichen Spaltenindex [nur von der MyISAM-Engine unterstützt und das indizierte Feld darf nicht leer sein], unterstützt jedoch keinen Funktionsindex.

Die Tabellen der Speicher-Engines MyISAM und InnoDB erstellen standardmäßig BTREE-Indizes.
Standardmäßig wird mithilfe der Speicher-Engine MEMORY ein HASH-Index für eine Tabelle erstellt.

2. Erstellen Sie einen Index

Die Syntax zum Erstellen eines Indexes lautet:

erstelle [eindeutigen|Volltext|räumlichen] Index Indexname
 [mit Indextyp]
auf tbl_name(index_col_name, ...);

Indexspaltenname:
 col_name [(Länge)] [aufsteigend/absteigend]

Sie können auch alter table verwenden, um einen Index hinzuzufügen. Die Syntax lautet:

ALTER [IGNORE] TABLE Tabellenname
 alter_specification [, alter_specification] …

alter_specification:
 ...
 ADD INDEX [Indexname] [Indextyp] (Indexspaltenname, …)
 ...

Beispiel: Erstellen Sie einen 10-Byte-Präfixindex für die Stadttabelle

mysql> erstelle Index cityName für city(Name(10));
mysql> Tabelle ändern, Stadt hinzufügen, Index Stadtname(Name(10));

3. Index anzeigen

Sie können show index from table; um alle aktuellen Indizes der Tabelle anzuzeigen.

4. Index löschen

Lösche den Index Indexname auf Tabellenname.

5. BTREE-Index und HASH-Index

Tabellen mit der Speicher-Engine MEMORY können wahlweise BTREE-Indizes und HASH-Indizes verwenden

BTREE-Index:

• Bei Verwendung des Operators >, <, =, >=, <=, between, !=, <> oder like xxx (xxx beginnt nicht mit einem Platzhalter) kann der BTREE-Index der relevanten Spalten verwendet werden.

Hinweise zur Verwendung von HASH-Indizes: (im Zusammenhang mit den Einschränkungen von HASH-Tabellen)

• Kann nur für Gleichheitsvergleiche mit den Operatoren = oder <=> verwendet werden.
• Der Optimierer kann keine HASH-Indizes verwenden, um die Order-by-Operationen zu beschleunigen.
• MySQL kann nicht ungefähr bestimmen, wie viele Zeilen sich vor den beiden Werten befinden, was die Effizienz der Abfrage bis zu einem gewissen Grad beeinträchtigt.
• Es kann nur in einer Zeile nach dem gesamten Schlüsselwort gesucht werden.

6. Grundsätze der Indexgestaltung

Die zu durchsuchenden Indexspalten sind nicht notwendigerweise die auszuwählenden Spalten. Für die Indizierung sind die Spalten am besten geeignet, die in der Where-Klausel vorkommen, nicht die in der Select-Anweisung.

Verwenden Sie einen eindeutigen Index. Wählen Sie für die Indizierung Spalten aus, deren Werte leicht zu unterscheiden sind. Beispielsweise ist ein Index für den Geburtstag besser als ein Index für das Geschlecht, da die Spalte „Geburtstag“ andere Werte hat und leichter zu unterscheiden ist, während die Spalte „Geschlecht“ nur „M“ und „F“ hat. In diesem Fall ist der Index nicht sehr nützlich und jeder Index gibt ungefähr die Hälfte der Zeilen zurück.

Verwenden Sie kurze Indizes. Eine Präfixlänge wird üblicherweise für den Präfixindex einer Zeichenfolge angegeben. Wenn die meisten Werte innerhalb der ersten 10 bis 20 Zeichen eindeutig sind, müssen Sie nicht die gesamte Spalte indizieren, sondern können stattdessen die ersten 10 bis 20 Zeichen indizieren. Dadurch können Sie Indexplatz sparen, die E/A-Zeit verkürzen und die Abfrageeffizienz verbessern.

Überindizieren Sie nicht. Jeder zusätzliche Index nimmt zusätzlichen Speicherplatz ein und verringert die Leistung von Schreibvorgängen. Wenn die Tabelle geändert wird, muss der Index aktualisiert und möglicherweise sogar neu erstellt werden. Je mehr Indizes vorhanden sind, desto länger dauert es. Darüber hinaus berücksichtigt MySQL bei der Generierung eines Ausführungsplans verschiedene Indizes. Redundante Indizes erschweren die Abfrageoptimierung.

Sicht

1. Übersicht

MySQL bietet seit Version 5.0.1 eine Anzeigefunktion.

Eine Ansicht ist eine virtuelle Tabelle, die in der Datenbank nicht tatsächlich vorhanden ist. Die Zeilen- und Spaltendaten stammen aus der Tabelle, die in der Abfrage der benutzerdefinierten Ansicht verwendet wird, und werden bei Verwendung der Ansicht dynamisch generiert.

2. Erstellen oder Ändern einer Ansicht <br /> Zum Erstellen einer Ansicht benötigen Sie die Berechtigung create view und die Berechtigung „Auswählen“ für die Tabellen und Spalten, die in der Abfrage enthalten sind.

Wenn Sie zum Bearbeiten einer Ansicht die Berechtigung create or replace oder „Ändern“ verwenden, benötigen Sie für die Ansicht auch die Berechtigung „Löschen“.

Die Syntax zum Erstellen einer Ansicht lautet:

erstellen [oder ersetzen][algorithmus = {undefiniert|zusammenführen|versuchbar}]
 Ansicht Ansichtsname [(Spaltenliste)]
 als Select-Anweisung
 [mit Prüfoption [cascade|local]]

Ändern Sie die Ansichtssyntax wie folgt:

ändern [Algorithmus = {undefiniert|zusammenführen|versuchbar}]
 Ansicht Ansichtsname [(Spaltenliste)]
 als Select-Anweisung
 [mit Prüfoption [cascade|local]]

MySQL weist einige Einschränkungen bei Ansichtsdefinitionen auf. Das Schlüsselwort „from“ darf beispielsweise keine Unterabfrage enthalten, was sich von anderen Datenbanken unterscheidet.

3. Aktualisierbarkeit von Ansichten

Die Aktualisierbarkeit einer Ansicht hängt von der Definition der Abfrage in der Ansicht ab. Die folgenden Ansichtstypen sind nicht aktualisierbar.

• Enthält Aggregatfunktionen (Summe, Minimum, Maximum, Anzahl usw.), Distinct, Group By, Having, Union oder Union All.
• Ständiger Blick.
• Die Auswahl enthält eine Unterabfrage.
• verbinden.
• aus einer Ansicht, die nicht aktualisiert werden kann.
• Die Unterabfrage in der Where-Klausel verweist auf die Tabelle in der From-Klausel.

Beispiel: Die folgenden Ansichten können nicht aktualisiert werden

-- Enthält Aggregatfunktionenmysql > erstellen oder ersetzen view payment_sum als 
 -> wähle staff_id,sum(amount) 
 -> ab Zahlung
 -> Gruppieren nach Personal-ID;

-- Konstante Ansicht MySQL> Ansicht PI erstellen oder ersetzen als 
 -> wähle 3,1415926 als Pi;

-- Wählen Sie „enthält“ Subquerymysql > erstellen Sie die Ansicht „city_view“ als
 -> auswählen (Stadt aus Stadt auswählen, wo city_id = 1);

Die with[cascaded|local] check option bestimmt, ob Aktualisierungen von Daten zugelassen werden, die dazu führen, dass Datensätze die Anzeigebedingungen nicht mehr erfüllen. Die Standardeinstellung ist kaskadiert. Diese Option ähnelt der Option in der Oracle-Datenbank.

• lokal: kann aktualisiert werden, solange die Bedingungen dieser Ansicht erfüllt sind
• kaskadiert: Alle Bedingungen für alle Ansichten unter dieser Ansicht müssen erfüllt sein, bevor die Aktualisierung durchgeführt werden kann.

4. Eine Ansicht löschen

Sie können eine oder mehrere Ansichten gleichzeitig löschen, müssen dafür aber über die Löschberechtigung für die Ansicht verfügen.

Ansicht löschen [falls vorhanden] Ansichtsname [,Ansichtsname] ... [einschränken|kaskadieren]

Löschen Sie beispielsweise die Ansicht pay_view

mysql> Ansicht „pay_view1,pay_view2“ löschen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

5. Ansicht anzeigen

Ab MySQL Version 5.1 zeigt der Befehl „show tables“ nicht nur den Namen der Tabelle, sondern auch den Namen der Ansicht an. Es gibt keinen Befehl „show views“, der nur die Ansicht anzeigt.

Alternativ können Sie es auch mit dem folgenden Befehl anzeigen:

Tabellenstatus anzeigen [von db_name] [wie 'Muster']

Beispiel

mysql> zeige Tabellenstatus wie 'pay_view' \G
*************************** 1. Reihe ***************************
  Name: pay_view
  Motor: NULL
 Version: NULL
 Row_format: NULL
  Zeilen: NULL
 Durchschnittliche Zeilenlänge: NULL
 Datenlänge: NULL
Max_data_length: NULL
 Index_length: NULL
 Data_free: NULL
 Auto_increment: NULL
 Erstellungszeit: NULL
 Update_time: NULL
 Check_time: NULL
 Sortierung: NULL
 Prüfsumme: NULL
 Create_options: NULL
 Kommentar: ANZEIGEN
1 Zeile im Satz (0,00 Sek.)

Wenn Sie die Definition einer Ansicht anzeigen möchten, können Sie show create view verwenden.

Beispiel

mysql> anzeigen, Ansicht erstellen, pay_view \G
*************************** 1. Reihe ***************************
  Ansicht: pay_view
  Ansicht erstellen: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `pay_view` AS select `pay`.`pid` AS `pid`,`pay`.`amount` AS `amount` from `pay` where (`pay`.`amount` < 10) WITH CASCADED CHECK OPTION
Zeichensatzclient: gbk
Sortierungsverbindung: gbk_chinese_ci
1 Zeile im Satz (0,00 Sek.)

Schließlich können Sie auch relevante Informationen zur Ansicht anzeigen, indem Sie die Systemtabelle information_schema.views anzeigen.

Beispiel

mysql> wähle * aus information_schema.views, wobei table_name = 'pay_view' \G
*************************** 1. Reihe ***************************
 TABLE_CATALOG: def
 TABLE_SCHEMA: mysqldemo
  TABELLENAME: pay_view
 VIEW_DEFINITION: Wählen Sie `mysqldemo`.`pay`.`pid` als `pid`, `mysqldemo`.`pay`.`amount` als `amount` aus `mysqldemo`.`pay`, wobei (`mysqldemo`.`pay`.`amount` < 10)
 CHECK_OPTION: CASCADED
 IS_UPDATEABLE: JA
  DEFINIERER: root@localhost
 SICHERHEITSTYP: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 Zeile im Satz (0,03 Sek.)

Fragen und Antworten:

Können MySQL-Ansichten Indizes verwenden?

Ich denke, die Antwort ist ja, der Index wird auf der echten Tabelle hinter der Ansicht erstellt, nicht auf der Ansicht selbst.

Ein Index ist ein Datenbankobjekt, das in einem Schema gespeichert ist. Die Funktion eines Index besteht darin, die Geschwindigkeit von Tabellenabrufabfragen zu erhöhen. Ein Index ist eine schnelle Zugriffsmethode, um Daten schnell zu finden und so die Anzahl der Lese- und Schreibvorgänge auf der Festplatte zu reduzieren. Ein Index ist ein Objekt in der Datenbank. Er kann nicht unabhängig existieren und muss von einem Tabellenobjekt abhängen.

Eine Ansicht ist das Abfrageergebnis einer oder mehrerer Tabellen. Es handelt sich um eine virtuelle Tabelle, da sie keine Daten speichern kann.

Verweise

Tang Hanming usw., „MySQL in einfachen Worten“, Posts and Telecommunications Press, 2014

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.