Heim > Datenbank > MySQL-Tutorial > MySQL Advanced 12 – Index

MySQL Advanced 12 – Index

黄舟
Freigeben: 2016-12-29 16:54:01
Original
1150 Leute haben es durchsucht

Indizierung ist der Schlüssel zur schnellen Suche. Die Einrichtung eines MySQL-Index ist für den effizienten Betrieb von MySQL sehr wichtig. Im Folgenden werden einige gängige MySQL-Indextypen vorgestellt

In Datenbanktabellen kann die Indizierung von Feldern die Abfragegeschwindigkeit erheblich verbessern. Angenommen, wir erstellen eine Mytable-Tabelle:

Der Code lautet wie folgt:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );
Nach dem Login kopieren

Wir fügen zufällig 10.000 Datensätze hinein, darunter einen: 5555, admin.

Bei der Suche nach dem Datensatz „username="admin"

SELECT * FROM mytable WHERE username='admin';
Nach dem Login kopieren

kann MySQL den Datensatz ohne Scan genau finden, wenn ein Index für den Benutzernamen erstellt wurde. Im Gegenteil, MySQL scannt alle Datensätze, dh es werden 10.000 Datensätze abgefragt.

Indizes werden in einspaltige Indizes und kombinierte Indizes unterteilt. Ein einspaltiger Index bedeutet, dass ein Index nur eine einzige Spalte enthält. Eine Tabelle kann mehrere einspaltige Indizes haben, es handelt sich jedoch nicht um einen kombinierten Index. Kombinierter Index, das heißt, ein Index enthält mehrere Spalten.

Zu den MySQL-Indextypen gehören:

1. Gewöhnlicher Index

Dies ist der einfachste Index, er unterliegt keinen Einschränkungen. Es gibt die folgenden Erstellungsmethoden:

1. Erstellen Sie einen Index

Der Code lautet wie folgt:

CREATE INDEX indexName ON mytable(username(length));
Nach dem Login kopieren

Wenn es sich um den Typ CHAR oder VARCHAR handelt, kann die Länge angegeben werden kleiner als die tatsächliche Länge des Feldes; wenn es sich um BLOB- und TEXT-Typen handelt, muss die Länge angegeben werden, wie unten angegeben.

2. Ändern Sie die Tabellenstruktur

Der Code lautet wie folgt:

ALTER mytable ADD INDEX [indexName] ON (username(length)) CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length)));
Nach dem Login kopieren

Index löschen:

DROP INDEX [indexName] ON mytable;
Nach dem Login kopieren

2. Eindeutiger Index

Es ähnelt dem vorherigen gewöhnlichen Index, außer dass der Wert der Indexspalte eindeutig sein muss, aber Nullwerte zulässig sind. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. Es verfügt über die folgenden Erstellungsmethoden:

Der Code lautet wie folgt:

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;
Nach dem Login kopieren

Ändern Sie die Tabellenstruktur

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;
Nach dem Login kopieren

Direkt beim Erstellen der Tabelle angeben

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));
Nach dem Login kopieren

3. Primärschlüsselindex

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

Der Code lautet wie folgt:

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));
Nach dem Login kopieren

Natürlich können Sie auch den ALTER-Befehl verwenden. Denken Sie daran: Eine Tabelle kann nur einen Primärschlüssel haben.

4. Kombinierter Index

Um den Einzelspaltenindex und den kombinierten Index anschaulich zu vergleichen, fügen Sie der Tabelle mehrere Felder hinzu:

Der Code lautet wie folgt:

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,   age INT NOT NULL);
Nach dem Login kopieren

Um die Effizienz von MySQL weiter zu steigern, muss über die Einrichtung eines kombinierten Index nachgedacht werden. Das heißt, Name, Stadt und Alter in einen Index einzubauen:

Der Code lautet wie folgt:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
Nach dem Login kopieren

Beim Erstellen der Tabelle beträgt die Länge des Benutzernamens 16, 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 und 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. Nicht nur Abfragen, die diese drei Spalten enthalten, verwenden diesen kombinierten Index, auch die folgende SQL verwendet diesen kombinierten Index:

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin";
Nach dem Login kopieren

Die folgenden werden nicht verwendet:

Der Code lautet wie folgt :

SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州";
Nach dem Login kopieren

5. Zeitpunkt zum Erstellen eines Index

Bisher haben wir gelernt, einen Index zu erstellen. 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 <, <=, =, >, >=, BETWEEN, IN indiziert und manchmal LIKE verwendet Index. Beispiel:

Der Code lautet wie folgt:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city=&#39;郑州&#39;;
Nach dem Login kopieren

Zu diesem Zeitpunkt müssen Sie Stadt und Alter indizieren, da der Benutzername der Mytable-Tabelle auch in der JOIN-Klausel erscheint. es ist auch notwendig.

Ich habe gerade erwähnt, dass LIKE nur zu bestimmten Zeiten indiziert werden muss. Weil MySQL den Index nicht verwendet, wenn Abfragen mit den Platzhalterzeichen % und _ beginnen. Beispielsweise verwendet der folgende Satz den Index:

Der Code lautet wie folgt:

SELECT * FROM mytable WHERE username like&#39;admin%&#39;;
Nach dem Login kopieren

Der nächste Satz verwendet nicht den Index:

Der Code lautet wie folgt:

SELECT * FROM mytable WHEREt Name like&#39;%admin&#39;;
Nach dem Login kopieren

因此,在使用LIKE时应注意以上的区别。

六、索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

七、使用索引的注意事项

使用索引时,有以下一些技巧和注意事项:

1.索引不会包含有NULL值的列

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

2.使用短索引

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

3.索引列排序

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

4.like语句操作

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

5.不要在列上进行运算

代码如下:

select * from users where YEAR(adddate)<2007;
Nach dem Login kopieren

将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:

代码如下:

select * from users where adddate<‘2007-01-01&#39;;
Nach dem Login kopieren

6.不使用NOT IN和<>操作

 以上就是MySQL高级十二——索引的内容,更多相关内容请关注PHP中文网(m.sbmmt.com)!


Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage