MySQL Order By-Indexoptimierungsmethode

Auch wenn ORDER BY nicht genau mit der Reihenfolge des Index übereinstimmt, kann der Index dennoch verwendet werden, solange der nicht verwendete Indexteil und alle zusätzlichen ORDER BY-Felder in der WHERE-Klausel enthalten sind.

MySQL Order By using index
Die folgenden Abfragen verwenden Indizes, um ORDER BY or zu lösen Abschnitt „GROUP BY“:

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; 
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; 
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2; 
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; 
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

MySQL ohne Index Order By
In anderen Fällen kann MySQL keine Indizes verwenden, um ORDER zu erfüllen BY, obwohl es einen Index verwendet, um Datensätze zu finden, die der WHERE-Klausel entsprechen. Diese Situationen sind wie folgt:
* Führen Sie ORDER BY für verschiedene Indexschlüssel aus:
SELECT * FROM t1 ORDER BY key1, key2;
* Führen Sie ORDER BY für nicht aufeinanderfolgende Indexschlüsselteile aus:
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* Mit ASC und DESC:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* Der für die Suche nach Datensätzen verwendete Indexschlüssel ist nicht derselbe wie der für ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1; Es werden viele Tabellen zusammengefügt, und die Felder in ORDER BY in den gelesenen Datensätzen stammen nicht alle aus der ersten nicht konstanten Tabelle (d. h. in EXPLAIN). Der Join-Typ der ersten Tabelle in den analysierten Ergebnissen ist nicht const).
* Verwenden Sie unterschiedliche ORDER BY- und GROUP BY-Ausdrücke.
* Datensätze in Tabellenindizes werden nicht sequentiell gespeichert. Dies ist beispielsweise bei HASH- und HEAP-Tabellen der Fall.

Durch Ausführen von EXPLAIN SELECT ... ORDER BY erfahren Sie, ob MySQL den Index in der Abfrage verwendet. Wenn der Wert des Felds „Extra“ Using filesort lautet, kann MySQL den Index nicht verwenden. Einzelheiten finden Sie unter „7.2.1 EXPLAIN-Syntax (Informationen zu einem SELECT abrufen). Wenn Ergebnisse sortiert werden mussten, wurde vor MySQL 4.1 Folgendes verwendet Dateisortierungsalgorithmus:

1. 根据索引键读取记录，或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。 
2. 在缓冲中每条记录都用一个‘对'存储了2个值（索引键及记录指针）。缓冲的大小依据系统变量 sort_buffer_size 的值而定。 
3. 当缓冲慢了时，就运行 qsort（快速排序）并将结果存储在临时文件中。将存储的块指针保存起来（如果所有的‘对'值都能保存在缓冲中，就无需创建临时文件了）。 
4. 执行上面的操作，直到所有的记录都读取出来了。 
5. 做一次多重合并，将多达 MERGEBUFF（7）个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。 
6. 重复以上操作，直到剩余的块数量小于 MERGEBUFF2 (15)。 
7. 在最后一次多重合并时，只有记录的指针（排序索引键的最后部分）写到结果文件中去。 
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。
缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。

Ein Problem bei diesem Näherungsalgorithmus besteht darin, dass die Datenbank Datensätze zweimal liest: einmal, um WO zu schätzen Beim Teilen von Sätzen erfolgt das zweite Mal beim Sortieren. Obwohl die Datensätze beim ersten Mal erfolgreich gelesen wurden (z. B. wurde ein vollständiger Tabellenscan durchgeführt), wurde das zweite Mal zufällig gelesen (die Indexschlüssel wurden sortiert, die Datensätze jedoch nicht). In MySQL 4.1 In neueren Versionen wird der Filesort-Optimierungsalgorithmus verwendet, um nicht nur den Indexschlüsselwert und die Datensatzposition aufzuzeichnen, sondern auch die in der Abfrage erforderlichen Felder. Dadurch wird vermieden, dass der Datensatz zweimal gelesen werden muss. verbesserte Dateisortierung Der Algorithmus sieht ungefähr wie folgt aus:

1. Lesen Sie wie zuvor die Datensätze, die der WHERE-Klausel entsprechen.
2. Für jeden Datensatz wird ein entsprechendes „Tupel“ von Informationen aufgezeichnet, einschließlich des Indexschlüsselwerts, des Datensatzspeicherorts und aller in der Abfrage erforderlichen Felder.
3. Sortieren Sie die „Tupel“-Informationen nach dem Indexschlüssel.
4. Beim Lesen der Datensätze in der richtigen Reihenfolge werden lediglich Datensätze aus der sortierten Liste der „Tupel“ gelesen, anstatt sie erneut aus der Datentabelle zu lesen.

Verwenden Sie eine verbesserte Dateisortierung Im Vergleich zum ursprünglichen Algorithmus beanspruchen „Tupel“ mehr Platz als „Paare“ und passen selten genau in den Sortierpuffer (die Größe des Puffers wird durch sort_buffer_size bestimmt). bestimmt durch den Wert). Daher sind möglicherweise mehr E/A-Vorgänge erforderlich, wodurch der verbesserte Algorithmus langsamer wird. Um eine Verlangsamung zu vermeiden, wird diese Optimierung nur zum Sortieren von „Tupeln“ verwendet, wenn die Summe der Größen der zusätzlichen Felder die Systemvariable überschreitet Die Situation von max_length_for_sort_data (ein Symptom dafür, dass der Wert dieser Variablen zu hoch eingestellt ist, ist eine hohe Festplattenlast und eine niedrige CPU-Last). Möchten Sie ORDER BY verbessern? Die Geschwindigkeit hängt zunächst davon ab, ob MySQL Indizes anstelle zusätzlicher Sortierprozesse verwenden kann. Wenn Sie keine Indizes verwenden können, können Sie die folgenden Strategien ausprobieren:
* Erhöhen Sie den Wert von sort_buffer_size.
* Erhöhen Sie den Wert von read_rnd_buffer_size.
* Ändern Sie tmpdir so, dass es auf ein dediziertes Dateisystem mit viel freiem Speicherplatz verweist.
Bei Verwendung von MySQL 4.1 oder neuer ermöglicht diese Option mehrere Pfade in einem Schleifenformat. Unter Unix wird jeder Pfad durch einen Doppelpunkt (':') getrennt. Windows, NetWare und OS/2 Verwenden Sie ein Semikolon (';'). Mit dieser Funktion können Sie die Last gleichmäßig auf mehrere Verzeichnisse verteilen. Hinweis: Bei diesen Pfaden muss es sich um Verzeichnisse handeln, die auf verschiedenen physischen Datenträgern verteilt sind, und nicht um verschiedene Verzeichnisse auf demselben physischen Datenträger.