Heim > Artikel > Datenbank > Detaillierte Einführung in Schlüssel und Index in MySQL

Detaillierte Einführung in Schlüssel und Index in MySQL

PHP中文网Original: 2017-06-20 14:10:3410582Durchsuche

1. Überblick

1. Grundkonzepte

(1) Der Schlüssel ist die physische Struktur der Datenbank. Die erste Ebene ist die Einschränkung Die Einzigartigkeit und Vollständigkeit der Daten ist die Indexfunktion, die zum Erstellen von Indizes und zur Optimierung der Abfragegeschwindigkeit verwendet wird und dieselbe Funktion wie der Index hat.

(2) Gewöhnlicher Schlüssel: Es gibt keine Einschränkung, aber für diesen Schlüssel wird ein Index erstellt.

(3) Primärschlüssel: ein Primärschlüssel; eine Tabelle kann einen Primärschlüssel haben, der in einen einzelnen Primärschlüssel (der nur eine Spalte enthält) und einen zusammengesetzten Primärschlüssel (auch gemeinsamer Primärschlüssel genannt) unterteilt ist , der mehrere Spalten enthalten kann); ein Speicher-Primärschlüssel kann angegeben werden, und gleichzeitig wird ein Index für diesen Schlüssel erstellt; Der Primärschlüssel ist nicht erforderlich, wird jedoch dringend empfohlen. [Einige gute Gewohnheiten für die Verwendung von Primärschlüsseln: keine Änderungen, keine Wiederverwendung]

(4) eindeutiger Schlüssel: Standardisiert die Eindeutigkeit von Daten Gleichzeitig wird dieser Schlüssel verwendet, um einen Index zu erstellen.

(5) Fremdschlüssel: Fremdschlüssel; standardisiert die referenzielle Integrität von Daten, gleichzeitig wird ein Index für diesen Schlüssel erstellt.

(6) Index: eine Dimension des Schlüssels, die manchmal das Schlüsselwort Schlüssel ersetzen kann.

2. Primärschlüssel und eindeutiger Schlüssel

(1) Ähnlichkeiten: Eindeutigkeitsbeschränkung

(2) Unterschiede

1) Der Ausgangspunkt /function ist anders: Ersteres ist die eindeutige Kennung einer Datenzeile, während letzteres nur zur Vermeidung von Datenduplizierungen verwendet wird.
2) Eine oder mehrere Spalten des ersteren dürfen alle nicht null sein. Wenn eine der Spalten null ist, wird sie beim Hinzufügen des Primärschlüssels nicht null wird sich wieder ändern. Die letzte Spalte kann null sein.
3) Eine Tabelle kann nur einen Primärschlüssel und mehrere eindeutige Schlüssel haben. [Kann eine Tabelle keinen Primärschlüssel haben? ? ? 】
4) Für die Spalte, die dem eindeutigen Schlüssel entspricht, kann null mehrmals eingefügt werden (obwohl dies auch eine Art Wiederholung ist); .

2. Syntax

1. Feldebene hinzufügen

(1) beim Erstellen Gewöhnlicher Schlüssel: Tabelle t erstellen (ID int nicht Null-Schlüssel); (2) Primärschlüssel: Tabelle t erstellen (ID int nicht Null-Primärschlüssel); key gibt auch den Primärschlüssel an und kann nur einmal in einer Tabelle angegeben werden (kann nicht mehrmals als gemeinsamer Primärschlüssel angegeben werden)

(3) eindeutiger Schlüssel: Tabelle erstellen t (id int nicht null eindeutiger Schlüssel );

(4) Fremdschlüssel: Es sollte nicht funktionieren

(5) Index: Alle Schlüssel können nicht transponiert werden Index

2. Hinzufügen - Tabellenebene beim Erstellen

(1) Gewöhnlicher Schlüssel: Anders als bei der Angabe auf Feldebene ist der gewöhnliche Schlüssel hier nicht mehr derselbe wie der Primärschlüssel. Auch wenn der Primärschlüssel nicht angegeben ist, wird dies bei MySQL nicht der Fall sein Verwenden Sie den Schlüssel als Primärschlüssel.

create table t(id int, key (id)); Wenn es andere Schlüssel gibt, die die ID verwenden (z. B. einen Fremdschlüssel), verwenden Sie andere Schlüssel, um sie zu benennen werden keine benannt, wird die ID verwendet; wenn mehrere Spalten gleichzeitig als Schlüssel angegeben werden, wird der erste Spaltenname als Schlüsselname verwendet.

Tabelle erstellen t(id int, key kismet(id)); Geben Sie den Namen des Schlüssels an

Einschränkung: kann nicht verwendet werden, Schließlich ist es normal. Der Schlüssel hat keine Einschränkungswirkung

(2) Primärschlüssel

Tabelle erstellen t(id int, Primärschlüssel (id));

Tabelle erstellen t(id int, Primärschlüssel kismet(id )); kann ausgeführt werden, aber der Name funktioniert nicht , aber der Name funktioniert nicht

(3) eindeutiger Schlüssel

create table t(id int, unique key (id)); Die Benennungsregeln unterscheiden sich von key. Nur die erste Spalte wird als Schlüsselname verwendet

create table t(id int, unique key kismet( id)); Geben Sie den Namen des Schlüssels an

create table t(id int, Einschränkung Kismet Unique Key(id)); Geben Sie den Namen des Schlüssels an

(4) Fremdschlüssel [Ich persönlich denke das Die sogenannte Erstellung von zwei Schlüsseln besteht aus zwei Ebenen der Logik, nämlich Datenintegritätseinschränkungen und Indexoptimierung key (dage_id) verweist auf dage(id)); kann ausgeführt werden, und das Ausführungsergebnis ist ein automatisch benannter Fremdschlüssel und ein automatisch benannter gewöhnlicher Schlüssel.

create table t(id int, Foreign Key Kismet(dage_id) references dage(id)); kann ausgeführt werden, und das Ausführungsergebnis ist ein automatisch benannter Fremdschlüssel und einen normalen Schlüssel namens Kismet.

Tabelle erstellen t(id int, Einschränkung Kismet Fremdschlüssel(dage_id) Referenzen dage(id)); kann ausgeführt werden, und das Ausführungsergebnis ist zu erstellen eine Tabelle mit dem Namen „Der Fremdschlüssel von Kismet“ und ein normaler Schlüssel mit dem Namen „Kismet“.

(5) Index: Der Schlüssel im Schlüssel und der eindeutige Schlüssel (Tabellenebene) können in den Index übertragen werden, und der Effekt ist das gleiche.

3. Nach der Erstellung

(1) Schlüssel hinzufügen: hinzufügen, zum Beispiel: Tabelle ändern, um Primärschlüssel hinzuzufügen

(2) Schlüssel löschen, löschen, Primärschlüssel Verwenden Sie alter table, um den Primärschlüssel zu löschen. Beachten Sie den Unterschied zwischen dem Löschen von Schlüsseln und dem Löschen von Spalten.

4. Informationen anzeigen: Tabellenname anzeigen; Sie können verschiedene Attribute der Tabelle anzeigen, einschließlich Schlüsselattribute, Speicher-Engine, Zeichensatz, Partitionsstatus usw.

3. Fremdschlüssel

1. Es kann zwei Tabellen zuordnen, um die Datenkonsistenz sicherzustellen und einige zu implementieren Kaskadenoperationen;

2. Speicher-Engine, die Fremdschlüssel unterstützt: InnoDB, Speicherüberprüfungsunterstützung, andere wurden nicht überprüft.

3. Vollständige Syntax

(1) [CONSTRAINT-Symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...)

[Beim Löschen {RESTRICT |. Keine Aktion festlegen}]

[Beim Aktualisieren {RESTRICT | >
(2) Verwendung: Diese Syntax kann beim Erstellen einer Tabelle und einer Änderungstabelle verwendet werden. (3) CONSTRAINT-Symbol gibt den Namen des Schlüssels an. Wenn nicht angegeben, wird
verwendet werden beim Löschen und beim Aktualisieren automatisch generiert (4) und stellen ereignisauslösende Einstellungen dar. Parameter können eingestellt werden:

RESTRICT (Fremdschlüsseländerungen im Erscheinungsbild einschränken, Standard). )

CASCADE (Fremdschlüsseländerungen folgen)

SET NULL (Nullwert festlegen )

STANDARD EINSTELLEN

KEINE AKTION

4. Beispiel
(1) Erstellen Sie eine Tabelle, legen Sie Fremdschlüssel fest und fügen Sie Daten ein

CREATE TABLE `dage` (

 `id` int(11) NOT NULL auto_increment,

 `name` varchar(32) default '',

 PRIMARY KEY (`id`)

);

CREATE TABLE `xiaodi` (

 `id` int(11) NOT NULL auto_increment,

 `dage_id` int(11) default NULL,

 `name` varchar(32) default '',

 CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)

);

insert into dage(name) values('铜锣湾');

insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');

(2) Wenn in Wenn es einen jüngeren Bruder gibt, löschen Sie den älteren Bruder. Das Ergebnis ist wie folgt:
(3) Wenn Sie den jüngeren Bruder erzwingen möchten, ohne den älteren Bruder festzulegen, ist das Ergebnis wie folgt.

[SQL] delete from dage where id=1;

[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

(4) Ändern Sie die Ereignisauslösereinstellungen

[SQL] insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');

alter table xiaodi drop foreign key xiaodi_ibfk_1;

alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;

(5) Wenn Sie den ältesten Bruder löschen, obwohl noch ein jüngerer Bruder vorhanden ist: der älteste Bruder und der jüngere Der dem älteren Bruder entsprechende Bruder wird zusammen gelöscht. Wenn Sie den jüngeren Bruder zwangsweise einfügen möchten, ohne den älteren Bruder festzulegen, ändert sich das Ergebnis nicht, das heißt, es schlägt fehl.

4. Index [Referenz:]

1 >
(1) Rolle: Indizes haben einen entscheidenden Einfluss auf die Abfragegeschwindigkeit. Wenn kein Index vorhanden ist, durchsucht die Abfrage die gesamte Tabelle. Wenn ein Index vorhanden ist, durchsucht die Abfrage nur den Index. Da sich die Daten der Datenbank nicht im Speicher befinden, muss jede Abfrage die Daten von der Festplatte in den Speicher übertragen, und E/A verschwendet viel Zeit. Da Indizes viel kleiner sind als Daten, kann die Verwendung von Indizes die Abfragegeschwindigkeit erheblich verbessern, insbesondere wenn die Datenmenge groß ist.

(2) Der Index wird in der Speicher-Engine implementiert, nicht in der Serverschicht. Daher sind die Indizes der einzelnen Speicher-Engines nicht unbedingt genau gleich und nicht alle Speicher-Engines unterstützen alle Indextypen. Die derzeit am häufigsten verwendete Speicher-Engine ist InnoDB.

2. Wählen Sie den Datentyp des Index aus: MySQL unterstützt viele Datentypen, und die Auswahl des geeigneten Datentyps zum Speichern von Daten hat große Auswirkungen auf die Leistung. Im Allgemeinen können einige Richtlinien befolgt werden [(1)(2) gilt nicht für Hash-Indizes]:
(1) Kleinere Datentypen sind normalerweise besser: Kleinere Datentypen sind normalerweise besser. Benötigt weniger Speicherplatz auf der Festplatte , Arbeitsspeicher und CPU-Cache und Prozesse schneller.

(2) Einfache Datentypen sind besser: Ganzzahlige Daten haben weniger Verarbeitungsaufwand als Zeichen, da der Vergleich von Zeichenfolgen komplexer ist. In MySQL sollten Sie zum Speichern der Zeit die integrierten Datums- und Uhrzeitdatentypen anstelle von Zeichenfolgen verwenden und zum Speichern von IP-Adressen ganzzahlige Datentypen verwenden.
Beachten Sie, dass Sie für Indizes keine Zeichenfolgen verwenden sollten, wenn Sie Ganzzahlen verwenden können, insbesondere wenn die Datenmenge groß ist. Ein Nachteil von Ganzzahlen besteht darin, dass die Zusammenarbeit mit dem Client möglicherweise etwas mehr Arbeit erfordert (insbesondere bei großen Ganzzahlen). , hat aber kaum Auswirkungen auf die Effizienz.

(3) Versuchen Sie NULL zu vermeiden: Spalten sollten als NOT NULL angegeben werden, es sei denn, Sie möchten NULL speichern. In MySQL ist es schwierig, Abfragen für Spalten mit Nullwerten zu optimieren, da sie Indizes, Indexstatistiken und Vergleichsvorgänge erschweren. Sie sollten Nullwerte durch 0, einen Sonderwert oder eine leere Zeichenfolge ersetzen.

3. B-Baum-Index: Das Ergebnis ist B-Baum (ausgeglichener Binärbaum)

(1) Übersicht: Der gespeicherte Wert im Index ist wie folgt Die Reihenfolge in der Indexspalte. Sie können den B-Tree-Index verwenden, um vollständige Schlüsselwort-, Schlüsselwortbereichs- und Schlüsselwortpräfixabfragen durchzuführen.
Wenn mehrere Spalten indiziert werden (kombinierter Index), ist die Reihenfolge der Spalten sehr wichtig
, MySQL kann nur eine effektive Suche für das Präfix ganz links im Index durchführen.

(2) Beispiel: Sein Index enthält die Spalten last_name, first_name und dob jeder Zeile in der Tabelle.

TABELLE ERSTELLEN Personen (<div> <div><div class="linenums"> <div class="L0"><code class="language-sql">CREATE TABLE People (
 last_name varchar(50) not null,<div class="L2"><code class="language-sql"> last_name varchar(50) not null,

 first_name varchar(50) not null,

 dob date not null,

 gender enum('m', 'f') not null,vorname varchar(50) nicht null, 
<span-klasse> Geburtsdatum nicht null,</span-klasse>geschlecht enum('m', 'f') nicht null,
 key(last_name, first_name, dob)

);

(3) Matching-Methode: Sie können entweder suchen oder sortieren nach [Die Ergebnisse werden sortiert, also ist die Suche einfach Schnell]

1) Alle Werte abgleichen: Geben Sie bestimmte Werte für alle Spalten im Index an.

2) Passen Sie das Präfix ganz links an: Sie können den Index verwenden, um die Person zu finden, deren Nachname Allen ist. Verwenden Sie einfach die erste Spalte im Index.

3) Spaltenpräfix abgleichen: Sie können den Index beispielsweise verwenden, um Personen zu finden, deren Nachname mit J beginnt, wodurch nur Spalte 1 im Index verwendet wird .

4) Bereichsabfrage übereinstimmender Werte: Sie können den Index verwenden, um Personen zu finden, deren Nachnamen zwischen Allen und Barrymore liegen, indem Sie nur die erste Spalte in verwenden Index.

5) Der Matching-Teil ist präzise und der andere Teil ist Bereichsmatching: Sie können den Index verwenden, um Personen zu finden, deren Nachname Allen und deren Vorname ist beginnt mit dem Buchstaben K.

6) Nur den Index abfragen: Wenn sich die abgefragten Spalten alle im Index befinden, besteht keine Notwendigkeit, den Wert des Tupels zu lesen.

7) Wenn das Indexfeld A+B ist, wird der A-Index bei der Abfrage von A+C verwendet -> 🎜>

(4) Einschränkungen

1) Die Abfrage muss in der Spalte ganz links im Index beginnen.

2) Eine Indexspalte kann nicht übersprungen werden. Beispielsweise können Sie einen Index nicht verwenden, um eine Person zu finden, deren Nachname Smith war und die an einem bestimmten Tag geboren wurde.

3) Die Speicher-Engine kann die Spalten auf der rechten Seite der Bereichsbedingung im Index nicht verwenden. Wenn Ihre Abfrageanweisung beispielsweise WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23' lautet, verwendet die Abfrage nur die ersten beiden Spalten im Index, da es sich bei LIKE um eine Bereichsabfrage handelt .

4. Hash-Index

(1) Übersicht

1) Der Hash-Index berechnet den Hash-Wert über die Hash-Funktion zum Abrufen und kann den Zeilenzeiger der zu durchsuchenden Daten finden und so die Daten lokalisieren.

2) Der Hash-Wert hängt nicht vom Datentyp der Spalte ab. Der Index einer TINYINT-Spalte ist so groß wie der Index einer langen Zeichenfolge Spalte.

3) Die Speicherspeicher-Engine unterstützt nicht eindeutige Hash-Indizes. Wenn mehrere Werte denselben Hash-Code haben, speichert der Index ihre Zeilenzeiger in einem verknüpfte Liste an derselben Stelle in einem Hash-Tabelleneintrag.

(2) Einschränkungen

1) Da der Index nur Hash-Code und Datensatzzeiger enthält, kann MySQL nicht vermeiden Lesen von Datensätzen mithilfe von Indizes. Der Zugriff auf Datensätze im Speicher erfolgt jedoch sehr schnell und hat keine großen Auswirkungen auf die Leistung.

2) Die Hash-Index-Sortierung kann nicht verwendet werden.

3) Der Hash-Index unterstützt keine teilweise Übereinstimmung von Schlüsseln, da der Hash-Wert über den gesamten Indexwert berechnet wird.

4) Der Hash-Index unterstützt nur Gleichheitsvergleiche, z. B. die Verwendung von =, IN() und <=>. Bei WHERE-Preis>100 wird die Abfrage nicht beschleunigt.

(3) Beispiel

<span-klasse>CREATE TABLE testhash <span class="pun">(<div class="L0"><code class="language-sql"><span class="pln">CREATE TABLE testhash <span class="pun">(</span></span>

<span class="pln"> fname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,<code class="language-sql"><span class="pln">   fname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,</span></span></span></span></span></span>

<span class="pln">   lname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,</span></span></span></span></span></span>

<span class="pln">   <em><span style="color: #000000">KEY USING HASH</span></em><em><span style="color: #000000"><span class="pun">(<span class="pln">fname<span class="pun">)</span></span></span></span></em></span>

<span class="pln"> lname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,<div class="L4"><code class="language-sql"><span class="pun">)<span class="pln">ENGINE<span class="pun">=<span class="pln">MEMORY<span class="pun">;</span></span></span></span></span>

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in Schlüssel und Index in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!

Detaillierte Einführung in Schlüssel und Index in MySQL

In Verbindung stehende Artikel