Beschleunigen Sie die Analyse und Optimierung dynamischer PHP-Websites mit MySQL-Indizes

In diesem Artikel geht es hauptsächlich darum, wie man die MySQL-Indexanalyse und die Optimierung dynamischer Websites beschleunigt.

1. Was ist ein Index?

Indizes werden verwendet, um Datensätze mit bestimmten Werten schnell zu finden. Alle MySQL-Indizes werden in Form von B-Bäumen gespeichert. Wenn kein Index vorhanden ist, muss MySQL beim Ausführen einer Abfrage alle Datensätze in der gesamten Tabelle beginnend mit dem ersten Datensatz scannen, bis ein Datensatz gefunden wird, der die Anforderungen erfüllt. Je größer die Anzahl der Datensätze in der Tabelle ist, desto höher sind die Kosten für diesen Vorgang. Wenn ein Index für die als Suchbedingung verwendete Spalte erstellt wurde, kann MySQL schnell den Speicherort des Zieldatensatzes ermitteln, ohne Datensätze scannen zu müssen. Wenn die Tabelle 1000 Datensätze enthält, ist das Auffinden der Datensätze über den Index mindestens 100-mal schneller als das sequentielle Durchsuchen der Datensätze.

Angenommen, wir erstellen eine Tabelle mit dem Namen „Personen“:

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

Dann fügen wir völlig zufällig 1000 verschiedene Namenswerte in die Personentabelle ein. Die Namensspalten haben in der Datendatei keine explizite Reihenfolge. Wenn wir einen Index für die Namensspalte erstellen, sortiert MySQL die Namensspalte im Index. Für jedes Element im Index speichert MySQL intern einen „Zeiger“ auf den tatsächlichen Speicherort des Datensatzes in der Datendatei. Wenn wir also die Personen-ID eines Datensatzes finden möchten, dessen Name „Mike“ entspricht (der SQL-Befehl lautet „SELECT peopleid FROM people WHERE name='Mike';“), kann MySQL nach „Mike“ suchen. Wert im Index für Name und gehen Sie dann direkt zu Die entsprechende Zeile in der Datendatei gibt genau die Personen-ID (999) dieser Zeile zurück. Während dieses Vorgangs muss MySQL nur eine Zeile verarbeiten, um das Ergebnis zurückzugeben. Wenn in der Spalte „Name“ kein Index vorhanden ist, scannt MySQL alle Datensätze in der Datendatei, also 1.000 Datensätze! Offensichtlich kann die Aufgabe umso schneller erledigt werden, je kleiner die Anzahl der Datensätze ist, die MySQL verarbeiten muss.

2. Arten von Indizes

MySQL bietet eine Vielzahl von Indextypen zur Auswahl:

Gewöhnlicher Index:

Dies ist der grundlegendste Indextyp , und es gibt keine Einschränkungen wie Einzigartigkeit. Gewöhnliche Indizes können auf folgende Weise erstellt werden:

Erstellen Sie einen Index, z. B. CREATE INDEX ON Tabellenname (Liste der Spalten);

Ändern Sie die Tabelle, z. B ALTER TABLE Tabellenname ADD INDEX [Name des Index] (Liste der Spalten);

Geben Sie den Index beim Erstellen der Tabelle an, zum Beispiel CREATE TABLE Tabellenname ([...], INDEX [Name des Index] ( Liste der Spalten));

Eindeutiger Index:

Dieser Index ist im Grunde derselbe wie der vorherige „normale Index“, es gibt jedoch einen Unterschied: alle Werte im Index Die Spalte darf nur einmal vorkommen, d. h. sie muss eindeutig sein. Eindeutige Indizes können auf folgende Weise erstellt werden:

Erstellen Sie einen Index, z. B. CREATE UNIQUE INDEX ON Tabellenname (Liste der Spalten);

Ändern Sie die Tabelle, z. B als ALTER TABLE Tabellenname ADD UNIQUE [Name des Index] (Liste der Spalten);

Geben Sie den Index beim Erstellen der Tabelle an, zum Beispiel CREATE TABLE Tabellenname ( [...], UNIQUE [Name des Index] (Liste der Spalten) );

Primärschlüssel:

Der Primärschlüssel ist ein eindeutiger Index, er muss jedoch als „PRIMARY KEY“ angegeben werden. Wenn Sie jemals Spalten vom Typ AUTO_INCREMENT verwendet haben, sind Sie möglicherweise bereits mit Konzepten wie Primärschlüsseln vertraut. Der Primärschlüssel wird im Allgemeinen beim Erstellen der Tabelle angegeben, z. B. „CREATE TABLE Tabellenname ([...], PRIMARY KEY (Liste der Spalten));“. Wir können jedoch auch Primärschlüssel hinzufügen, indem wir die Tabelle ändern, z. B. „ALTER TABLE Tabellenname ADD PRIMARY KEY (Liste der Spalten);“. Jede Tabelle kann nur einen Primärschlüssel haben.

Volltextindex:

MySQL unterstützt Volltextindizierung und Volltextsuche ab Version 3.23.23. In MySQL ist der Indextyp des Volltextindex FULLTEXT. Volltextindizes können für Spalten vom Typ VARCHAR oder TEXT erstellt werden. Es kann mit dem Befehl CREATE TABLE oder mit dem Befehl ALTER TABLE oder CREATE INDEX erstellt werden. Bei großen Datensätzen ist das Erstellen eines Volltextindexes mit dem Befehl ALTER TABLE (oder CREATE INDEX) schneller als das Einfügen von Datensätzen in eine leere Tabelle mit einem Volltextindex. Die folgende Diskussion in diesem Artikel befasst sich nicht mehr mit Volltextindizes. Weitere Informationen finden Sie in der MySQL-Dokumentation.

3. Einspaltiger Index und mehrspaltiger Index

Der Index kann ein einspaltiger Index oder ein mehrspaltiger Index sein. Im Folgenden veranschaulichen wir anhand konkreter Beispiele die Unterschiede zwischen diesen beiden Indizes. Angenommen, es gibt eine solche Personentabelle:

CREATE TABLE people ( peopleid 
SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, 
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );

Das Folgende sind die Daten, die wir in die Personentabelle einfügen:

In diesem Datenfragment gibt es vier Personen mit dem Namen „Mikes“ (zwei von sie (ein Sullivans, zwei McConnells), zwei 17-Jährige und einer mit dem ungewöhnlichen Namen Joe Smith.

Der Hauptzweck dieser Tabelle besteht darin, die entsprechende Personen-ID basierend auf dem Nachnamen, Vornamen und Alter des angegebenen Benutzers zurückzugeben. Beispielsweise müssen wir möglicherweise die Personen-ID eines Benutzers finden, dessen Name Mike Sullivan ist und der 17 Jahre alt ist (der SQL-Befehl lautet SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17). ;). Da MySQL nicht bei jeder Ausführung einer Abfrage die gesamte Tabelle scannen soll, müssen hier Indizes berücksichtigt werden.

首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname)；），MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。

由于建立了firstname列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。

为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)；

由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！

那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

四、最左前缀

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

firstname，lastname，age
firstname，lastname
firstname

从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引：

SELECT peopleid FROM people 
WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; 
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; 
SELECT peopleid FROM people WHERE firstname='Mike'; 
The following queries cannot use the index at all: 
SELECT peopleid FROM people WHERE lastname='Sullivan'; 
SELECT peopleid FROM people WHERE age='17'; 
SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';

五、选择索引列

在性能优化过程中，选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列，在join子句中出现的列。请看下面这个查询：

SELECT age ## 不使用索引
FROM people WHERE firstname='Mike' ## 考虑使用索引
AND lastname='Sullivan' ## 考虑使用索引

这个查询与前面的查询略有不同，但仍属于简单查询。由于age是在SELECT部分被引用，MySQL不会用它来限制列选择操作。因此，对于这个查询来说，创建age列的索引没有什么必要。下面是一个更复杂的例子：

SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引
WHERE firstname='Mike' ##考虑使用索引
AND lastname='Sullivan' ##考虑使用索引

与前面的例子一样，由于firstname和lastname出现在WHERE子句中，因此这两个列仍旧有创建索引的必要。除此之外，由于town表的townid列出现在join子句中，因此我们需要考虑创建该列的索引。那么，我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢？差不多如此，但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

以上内容就是加速PHP动态网站 MySQL索引分析和优化，希望可以帮助到大家，想要更多相关内容请关注PHP中文网（www.php.cn）！