Wie kann die Leistung von Datenbankabfragen optimiert werden? (ausführliche Erklärung)

Wie optimiert man die Leistung von Datenbankabfragen? (Ausführliche Erklärung)

Abfragen sind die am häufigsten verwendete Operation in der Datenbanktechnologie. Der Vorgang des Abfragevorgangs ist relativ einfach. Nach dem Empfang der vom Client gesendeten SQL-Anweisung führt der Datenbankserver die SQL-Anweisung aus und gibt dann die Abfrageergebnisse an den Client zurück. Obwohl der Prozess sehr einfach ist, haben unterschiedliche Abfragemethoden und Datenbankeinstellungen einen großen Einfluss auf die Abfrageleistung.

Daher werden in diesem Artikel Techniken zur Abfrageoptimierung erläutert, die häufig in MySQL verwendet werden. Zu den besprochenen Inhalten gehören: Verbesserung der Abfragegeschwindigkeit durch Abfragepufferung; die indexbasierte Sortierung von Abfragen und die Verwendung verschiedener Abfrageoptionen zur Verbesserung der Leistung.

1. Verbessern Sie die Abfragegeschwindigkeit durch Abfragepufferung.
Wenn wir SQL-Anweisungen zum Abfragen verwenden, führt der Datenbankserver diese Anweisung im Allgemeinen jedes Mal aus, wenn er SQL-Anweisungen empfängt . Wenn jedoch innerhalb eines bestimmten Intervalls (z. B. innerhalb einer Minute) genau dieselbe SQL-Anweisung empfangen wird, wird sie auf die gleiche Weise ausgeführt. Obwohl dies die Echtzeitnatur der Daten gewährleisten kann, ist für die Daten in den meisten Fällen keine vollständige Echtzeit erforderlich, was bedeutet, dass es zu einer gewissen Verzögerung kommen kann. Wenn dies der Fall ist, lohnt es sich nicht, in kurzer Zeit genau das gleiche SQL auszuführen.
Glücklicherweise stellt uns MySQL die Abfragepufferungsfunktion zur Verfügung (Abfragepufferung kann nur in MySQL 4.0.1 und höher verwendet werden). Durch Abfragepufferung können wir die Abfrageleistung bis zu einem gewissen Grad verbessern.

Wir können den Abfragepuffer über die Datei my.ini im MySQL-Installationsverzeichnis festlegen. Die Einstellung ist auch sehr einfach, setzen Sie einfach query_cache_type auf 1. Nach dem Festlegen dieses Attributs prüft MySQL, bevor es eine SELECT-Anweisung ausführt, in seinem Puffer, ob dieselbe SELECT-Anweisung ausgeführt wurde. Wenn dies der Fall ist und das Ausführungsergebnis nicht abgelaufen ist, wird das Abfrageergebnis direkt an den Client zurückgegeben. Beachten Sie jedoch beim Schreiben von SQL-Anweisungen, dass der Abfragepuffer von MySQL die Groß-/Kleinschreibung beachtet. Die folgenden beiden SELECT-Anweisungen lauten wie folgt:

SELECT * from TABLE1
SELECT * FROM TABLE1

Die beiden oben genannten SQL-Anweisungen sind völlig unterschiedliche SELECTs für die Abfragepufferung. Darüber hinaus verarbeitet der Abfragecache Leerzeichen nicht automatisch. Daher sollten Sie beim Schreiben von SQL-Anweisungen versuchen, die Verwendung von Leerzeichen zu reduzieren, insbesondere die Leerzeichen am Anfang und Ende von SQL (da der Abfragecache die Leerzeichen nicht automatisch abfängt). Anfang und Ende).

Obwohl es manchmal zu Leistungseinbußen führen kann, wenn kein Abfragepuffer eingerichtet wird, gibt es einige SQL-Anweisungen, die Daten in Echtzeit abfragen müssen oder nicht häufig verwendet werden (vielleicht ein- oder zweimal am Tag ausgeführt). Dies erfordert das Ausschalten der Pufferung. Natürlich ist es möglich, den Abfragecache durch Festlegen des Werts von query_cache_type zu deaktivieren, aber dadurch wird der Abfragecache dauerhaft deaktiviert. MySQL 5.0 bietet eine Methode zum vorübergehenden Deaktivieren des Abfragepuffers:

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

Die obige SQL-Anweisung verwendet SQL_NO_CACHE, sodass der Server unabhängig davon, ob diese SQL-Anweisung ausgeführt wurde, nicht im Puffer nachschaut und führen Sie es jedes Mal aus.
Wir können query_cache_type in my.ini auch auf 2 setzen, sodass der Abfragecache erst verwendet wird, nachdem SQL_CACHE verwendet wurde.

SELECT SQL_CALHE * FROM TABLE1

2. MySQLs automatische Optimierung von Abfragen

Indizes sind sehr wichtig für die Datenbank. Indizes können verwendet werden, um die Leistung bei Abfragen zu verbessern. Aber manchmal kann die Verwendung von Indizes die Leistung beeinträchtigen. Wir können uns die folgende SALES-Tabelle ansehen:

CREATE TABLE SALES
(
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
PRICE FLOAT NOT NULL,
SALE_COUNT INT NOT NULL,
SALE_DATE DATE NOT NULL,
PRIMARY KEY(ID)，
INDEX (NAME)，
INDEX (SALE_DATE)
)

Angenommen, in dieser Tabelle sind Millionen von Daten gespeichert, und wir möchten den Durchschnittspreis des Produkts mit der Produktnummer 1000 in den Jahren 2004 und 2005 abfragen. Wir können die folgende SQL-Anweisung schreiben:

SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

Wenn die Menge dieses Produkts sehr groß ist, macht es fast 50 % oder mehr der Datensätze in der Tabelle SALES aus. Dann ist die Verwendung des Index für das Feld SALE_DATE zur Berechnung des Durchschnitts etwas langsam. Denn wenn Sie einen Index verwenden, müssen Sie den Index sortieren. Wenn sehr viele Datensätze vorhanden sind, die die Bedingungen erfüllen (z. B. 50 % oder mehr der Datensätze in der gesamten Tabelle), verringert sich die Geschwindigkeit, sodass es besser ist, die gesamte Tabelle zu scannen. Daher entscheidet MySQL automatisch, ob der Index für die Abfrage verwendet wird, basierend auf dem Datenanteil, der die Bedingungen in der gesamten Tabelle erfüllt.

Für MySQL wird der Index nicht verwendet, wenn der Anteil der oben genannten Abfrageergebnisse an den Datensätzen der gesamten Tabelle etwa 30 % beträgt. Dieser Anteil wird von MySQL-Entwicklern aufgrund ihrer Erfahrung abgeleitet. Der tatsächliche Verhältniswert variiert jedoch je nach verwendeter Datenbank-Engine

3. Indexbasierte Sortierung

Eine der Schwächen von MySQL ist seine Sortierung. Obwohl MySQL etwa 15.000 Datensätze in einer Sekunde abfragen kann, kann MySQL bei der Abfrage höchstens einen Index verwenden. Wenn daher die WHERE-Bedingung den Index bereits belegt, wird der Index nicht zum Sortieren verwendet, was die Geschwindigkeit der Abfrage erheblich verringert. Wir können uns die folgende SQL-Anweisung ansehen:

SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;

Der Index für das NAME-Feld wurde in der WHERE-Klausel der obigen SQL verwendet. Daher wird der Index beim Sortieren von SALE_DATE nicht mehr verwendet. . Um dieses Problem zu lösen, können wir einen zusammengesetzten Index für die SALES-Tabelle erstellen:

ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)

这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询又会慢下来。

四、 不可达查询的检测

在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value  200。我们永远无法找到一个既小于100又大于200的数。
如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:

SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显，这是一个不可达的查询，WHERE条件一定是假。MySQL在执行SQL语句之前，会先分析WHERE条件是否是不可达的查询，如果是，就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用EXPLAIN进行测试:

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”

上面的查询是一个正常的查询，我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

我们可以看到，table项是空，这说明MySQL并没有对SALES表进行操作。

五、 使用各种查询选择来提高性能

SELECT语句除了正常的使用外，MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分，我将介绍几个常用的查询选项。

1. STRAIGHT_JOIN:强制连接顺序

当我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如下列的SQL语句中，TABLE1和TABLE2并不一定是谁连接谁:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …

如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT_JOIN关键字，如下列的SQL语句:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

2. 干预索引使用，提高性能

在上面已经提到了索引的使用。一般情况下，在查询时MySQL将自己决定是否使用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。

限制使用索引的范围：

有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置。

SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …

从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。

限制不使用索引的范围

如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

强迫使用某一个索引

上面的两个例子都是给MySQL提供一个选择，也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

3. 使用临时表提供查询性能

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

六、 结论
在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。 

推荐教程：《MySQL教程》

Das obige ist der detaillierte Inhalt vonWie kann die Leistung von Datenbankabfragen optimiert werden? (ausführliche Erklärung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!