Was ist ein MySQL-Index? Einführung in verwandte Kenntnisse des MySQL-Index

Der Inhalt dieses Artikels befasst sich mit der Frage, was ein MySQL-Index ist. Die Einführung relevanter Kenntnisse über den MySQL-Index hat einen gewissen Referenzwert. Freunde in Not können sich darauf beziehen.

Was ist ein Index?

Ein Index ist wie das Inhaltsverzeichnis eines Buches

Um eine Zeile mit einem bestimmten Wert in einer Spalte zu finden, muss MySQL die gesamte Tabelle beginnend mit dem ersten Datensatz lesen, bis die entsprechende Zeile gefunden wird Zeit, die zum Abfragen der Daten benötigt wird. Wenn die abgefragte Spalte in der Tabelle über einen Index verfügt, kann MySQL schnell zu einem Speicherort gelangen, an dem die Datendatei durchsucht werden muss, was viel Zeit spart.

Vorteile und Nachteile

Vorteile

1. Beschleunigt die Abfrage erheblich

2. Alle Feldtypen können indiziert werden

Nachteile

1. Je mehr Daten, desto zeitaufwändiger.

2 Außerdem gibt es eine maximale Online-Einstellung. Wenn wir eine große Anzahl von Indizes haben, erreicht die Indexdatei möglicherweise schneller den Online-Wert als die Datendatei.

3. Wenn Sie Daten in der Tabelle hinzufügen, löschen oder ändern, Der Index benötigt auch eine dynamische Wartung, die die Geschwindigkeit der Datenpflege verringert

Nutzungsprinzipien und -szenarien

1 Je mehr Indizes, desto besser, es kommt auf die Situation an

2. Tabellen, die häufig aktualisiert werden, sollten so wenige Indizes wie möglich haben

3. Erstellen Sie Indizes für Felder, die häufig für Abfragen verwendet werden

4 mit kleinen Datenmengen. Der Zeitaufwand für Daten ist kürzer als für das Durchlaufen der Indexdaten, und der Index hat keinen Optimierungseffekt

5. Versuchen Sie, keine Indizes für Felder mit wenigen unterschiedlichen Werten zu verwenden , wie zum Beispiel das Geschlechtsfeld, das nur zwei unterschiedliche Werte für Männer und Frauen hat.

Indexklassifizierung

Hinweis: Die Indizierung ist in der Speicher-Engine implementiert, was bedeutet, dass verschiedene Speicher-Engines unterschiedliche Indizes verwenden

MyISAM- und InnoDB-Speicher Engines: unterstützen nur BTREE-Indizes, was bedeutet, dass BTREE standardmäßig verwendet wird und nicht ersetzt werden kann

MEMORY/HEAP-Speicher-Engines: unterstützt HASH- und BTREE-Indizes

1 🎜>

Ein Index enthält nur eine einzelne Spalte, aber es kann mehrere einspaltige Indizes in einer Tabelle geben

1.1 Gewöhnlicher Index

Grundlegender Indextyp in MySQL, Nein Welche Einschränkungen sind zulässig, um doppelte Werte und Nullwerte in die Spalten einzufügen, in denen der Index definiert ist, nur um die Daten schneller abzufragen?

1.2. Eindeutiger Index

Der Wert in der Indexspalte muss eindeutig sein, aber Nullwerte sind zulässig

1.3 Primärschlüsselindex

ist ein spezieller eindeutiger Index, es sind keine Nullwerte zulässig

2 Kombinierter Index

Ein Index, der nur für eine Kombination mehrerer Felder in der Tabelle erstellt wurde Die Abfragebedingungen Der Index wird nur verwendet, wenn das linke Feld dieser Felder verwendet wird. Befolgen Sie bei Verwendung des kombinierten Indexes die beste linke Präfixregel

3 🎜>Volltextindex. Er kann nur auf der MyISAM-Engine verwendet werden, und Volltextindizes können nur für Felder vom Typ CHAR, VARCHAR und TEXT verwendet werden. Volltextindex bedeutet, dass Sie in einem Textstapel die Datensatzzeile, zu der das Feld gehört, über ein bestimmtes Schlüsselwort finden können, z. B. „Sie sind ein großer schlechter Stift, eine zweitklassige Person …“ Durch das großer schlechter Stift, es könnte möglich sein, den Datensatz zu finden

4. Räumlicher Index

Ein räumlicher Index ist ein Index, der auf Feldern räumlicher Datentypen basiert. Es gibt vier räumliche Datentypen in MySQL, GEOMETRY, POINT, LINESTRING, POLYGON. Verwenden Sie beim Erstellen eines räumlichen Index das Schlüsselwort SPATIAL. Anforderungen: Die Engine ist MyISAM. Die zum Erstellen eines räumlichen Index verwendete Spalte muss als NOT NULL deklariert werden.

Indexmodus

Verwendungsprinzip: Wenn die Werte unterschiedlich sind ​​ist groß und basiert hauptsächlich auf der Suche nach gleichen Werten (=, <=>, in). Der Hash-Index ist eine effizientere Wahl und weist eine Suchkomplexität von O(1) auf, wenn die Wertedifferenz relativ ist schlecht und die Bereichssuche ist hauptsächlich B-Tree die bessere Wahl, da er die Bereichssuche unterstützt.

B-Tree-Index

Der B-Tree-Index verfügt über die Funktionen der Bereichssuche und Präfixsuche. Für einen B-Baum mit N Knoten beträgt die Komplexität des Abrufens eines Datensatzes O(LogN). . Entspricht der binären Suche.

Hash-Index

Hash-Index kann nur eine gleiche Suche durchführen, aber egal wie groß die Hash-Tabelle ist, die Suchkomplexität ist O(1).

Indexerstellung und -löschung

Erstellen

Erstellen beim Erstellen einer Tabelle

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]

Beispiel:

CREATE TABLE `NewTable` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (255) NOT NULL,
    `name` VARCHAR (255) NOT NULL,
    `sex` TINYINT NOT NULL DEFAULT 0,
    `address` VARCHAR (255) NULL,
    PRIMARY KEY (`id`), # 主键索引
    INDEX `name` (`name`) USING BTREE, # 普通索引
    UNIQUE INDEX `username` (`username`) USING BTREE # 唯一索引
    INDEX `u_n_a` (`username`,    `name`,`address`) USING BTREE # 组合索引
);

Vorhandene Tabellenerstellung

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

Beispiel:

ALTER TABLE `test`
ADD PRIMARY KEY (`id`),  # 主键索引
ADD INDEX `name` (`name`) USING BTREE , # 普通索引
ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 唯一索引
ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 组合索引

Index löschen

ALTER TABLE 表名 DROP INDEX 索引名。

Beispiel:

ALTER TABLE `test`
DROP PRIMARY KEY,
DROP INDEX `username`,
DROP INDEX `name`,
DROP INDEX `u_n_a`;

Index aktualisieren

Zuerst löschen und dann erstellen

ALTER TABLE `test`
DROP INDEX `username` ,
ADD UNIQUE INDEX `username1` (`username`) USING BTREE ,
DROP INDEX `name` ,
ADD INDEX `name2` (`name`) USING BTREE ,
DROP INDEX `u_n_a` ,
ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

Indexfehlerbedingungen

1. Kombinierte Felder folgen nicht dem optimalen linken Präfix Regel

2. Fuzzy-Abfrage, z. B. „%test“

# 索引生效
select * from `test` where `name` like "123";
# 索引生效
select * from `test` where `name` like "123%";
# 索引失效
select * from `test` where `name` like "%123";
# 索引失效
select * from `test` where `name` like "%123%";

3. 在索引列上做如下任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

如 sex 字段上添加索引

# 索引失效
select * from `test` where `sex`*0.5  = 1

4. 范围索引(>,<,between and)后，无法命中组合索引右边的列

构建索引

ALTER TABLE `test`
ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;

示例：

# 命中全部
select * from `test` where `sex` = 1 and `name` = 'a';
# 命中部分，sex命中，name失效
select * from `test` where `sex` > 1 and `name` = 'a';</p>
<p style="white-space: normal;">5. !=, is null, is not null 无法使用索引</p>
<p style="white-space: normal;">6. 字符串字段的值不加单引号（数字不报错，英文报错）索引失效</p>
<p>构建索引</p>
<pre class="brush:php;toolbar:false">ALTER TABLE `test`
ADD INDEX `name` (`name`) USING BTREE ;

示例

# 索引失效
select * from `test` where `name`  =  123;
# 索引生效
select * from `test` where `name`  =  '123';

7. or 条件导致索引失效

构建索引

ALTER TABLE `test`
ADD INDEX `sex` (`sex`) USING BTREE ;
ADD INDEX `n_u` (`name`, `username`) USING BTREE ;

示例：

# 索引不生效
select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 
# 索引sex生效
select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )

附录

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询要从索引的最左前列开始并且不跳过索引中的列

如下构建索引

ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

如下查询情况

# 命中部分
select * from `test` where `username` = 'aaa';
# 命中部分
select * from `test` where `username` = 'aa' and `address` = 'aaa';
# 全命中
select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a';
# 不命中，第一条件字段不是username
select * from `test` where  `address` = 'aaa';

Das obige ist der detaillierte Inhalt vonWas ist ein MySQL-Index? Einführung in verwandte Kenntnisse des MySQL-Index. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!