MySQL-Indextypen sowie Vor- und Nachteile

1 Wählen Sie den Datentyp des Index

MySQL unterstützt viele Datentypen zum Speichern von Daten, die einen großen Einfluss auf die Leistung haben. Im Allgemeinen können einige Richtlinien befolgt werden:

(1) Kleinere Datentypen sind normalerweise besser: Kleinere Datentypen erfordern normalerweise weniger Speicherplatz auf der Festplatte, im Speicher und im CPU-Cache.

(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.

(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.

2
Indextyp

Indizes werden in der Speicher-Engine implementiert, nicht in der Serverschicht. Daher sind die Indizes der einzelnen Speicher-Engines nicht unbedingt identisch und nicht alle Speicher-Engines unterstützen alle Indextypen

(1) Gewöhnlicher Index

Dies ist der grundlegendste Index, den es gibt keine Einschränkungen. Es gibt die folgenden Erstellungsmethoden:

Index erstellen

CREATE INDEX indexName ON mytable(username(length)); Wenn es sich um den Typ CHAR, VARCHAR handelt, kann die Länge kleiner sein als die tatsächliche Länge von das Feld; wenn es sich um BLOB- und TEXT-Typen handelt, muss die Länge angegeben werden, wie unten angegeben.

Ändern Sie die Tabellenstruktur

ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );  删除索引的语法：
DROP INDEX [indexName] ON mytable;

(2) Eindeutiger Index

Es ähnelt dem vorherigen normalen Index, außer dass der Wert der Indexspalte eindeutig sein muss , aber es ist erlaubt. Es gibt einen Nullwert. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. Es verfügt über die folgenden Erstellungsmethoden:

Index erstellen

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   );

(3) Primärschlüsselindex

Es handelt sich um einen speziellen eindeutigen Index, der keine Nullwerte zulässt. Im Allgemeinen wird der Primärschlüsselindex gleichzeitig mit der Erstellung der Tabelle erstellt:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

(4) Kombinierter Index

Um einspaltige Indizes und anschaulich zu vergleichen Kombinierte Indizes, fügen Sie der Tabelle mehrere Indizes hinzu. Feld:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); Um die Effizienz von MySQL weiter zu steigern,

Erwägen Sie einfach die Erstellung eines kombinierten Index. Bauen Sie einfach Name, Stadt und Alter in einen Index ein:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); Beim Erstellen der Tabelle beträgt die Länge des Benutzernamens 16, und hier wird 10 verwendet . Dies liegt daran, dass die Namenslänge im Allgemeinen 10 nicht überschreitet, was die Indexabfrage beschleunigt, die Größe der Indexdatei verringert und die Aktualisierungsgeschwindigkeit von INSERT verbessert.

Wenn Sie jeweils einspaltige Indizes für Benutzername, Stadt und Alter erstellen, sodass die Tabelle über drei einspaltige Indizes verfügt, unterscheidet sich die Abfrageeffizienz stark vom oben genannten kombinierten Index, der weitaus geringer ist als unser kombinierter Index. Obwohl es derzeit drei Indizes gibt, kann MySQL nur den einspaltigen Index verwenden, der seiner Meinung nach am effizientesten ist.

Das Erstellen eines solchen kombinierten Index entspricht tatsächlich dem Erstellen der folgenden drei Sätze kombinierter Indizes:

Benutzername, Stadt, Alter Benutzername, Stadt Benutzername Warum gibt es keinen kombinierten Index wie Stadt, Alter? ? Dies ist auf das „ganz linke Präfix“ des MySQL-Verbundindex zurückzuführen. Das einfache Verständnis besteht darin, die Kombination nur ganz links zu beginnen. (Dies ist eine der Interviewfragen, ich hätte sie damals richtig beantworten sollen) Nicht nur Abfragen, die diese drei Spalten enthalten, verwenden diesen kombinierten Index, sondern auch die folgende SQL verwendet diesen kombinierten Index:

SELECT FROM mytable WHREE username="admin" AND city="郑州"  SELECT FROM mytable WHREE username="admin" 而下面几个则不会用到：
SELECT FROM mytable WHREE age=20 AND city="郑州"  SELECT FROM mytable WHREE city="郑州"

3
Zeit, einen Index zu erstellen

An diesem Punkt haben wir gelernt, wie man einen Index erstellt. Unter welchen Umständen müssen wir also einen Index erstellen? Im Allgemeinen müssen Spalten, die in WHERE und JOIN erscheinen, indiziert werden, aber das ist nicht ganz richtig, da MySQL nur die Indizes 23735d90c0339e974c9b8bbfdf9ee4cb, >=, BETWEEN, IN und manchmal auch LIKE verwendet der Index. Zum Beispiel:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    
ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

Gerade erwähnt, dass nur bestimmte LIKEs indiziert werden müssen. Weil MySQL den Index nicht verwendet, wenn Abfragen mit den Platzhalterzeichen % und _ beginnen. Im folgenden Satz werden beispielsweise Indizes verwendet:

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

4
Die Mängel von Indizes

Das Obige Die erwähnte Verwendung von Indizes hat Vorteile, eine übermäßige Verwendung von Indizes kann jedoch zu Missbrauch führen. Daher weist der Index auch seine Mängel auf:

Obwohl der Index die Abfragegeschwindigkeit erheblich verbessert, verringert er auch die Geschwindigkeit der Aktualisierung der Tabelle, z. B. INSERT, UPDATE und DELETE in der Tabelle. Denn beim Aktualisieren der Tabelle muss MySQL nicht nur die Daten, sondern auch die Indexdatei speichern.

Das Erstellen von Indexdateien belegt Speicherplatz. Im Allgemeinen ist dieses Problem nicht schwerwiegend. Wenn Sie jedoch mehrere kombinierte Indizes für eine große Tabelle erstellen, wird die Indexdatei schnell erweitert.

Indizes sind nur ein Faktor zur Verbesserung der Effizienz. Wenn Ihr MySQL über eine große Menge an Datentabellen verfügt, müssen Sie Zeit damit verbringen, die besten Indizes zu recherchieren und zu erstellen oder Abfrageanweisungen zu optimieren

5
Hinweise zur Verwendung von Indizes

Bei der Verwendung von Indizes gibt es einige Tipps und Vorsichtsmaßnahmen wie folgt:

Der Index enthält keine Spalten mit NULL-Werte

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

不要在列上进行运算

select * from users where YEAR(adddate)<2015; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2015-01-01’;

不使用NOT IN和a8093152e673feb7aba1828c43532094操作

以上就是MySQL索引类型与优缺点的内容，更多相关内容请关注PHP中文网（www.php.cn）！