Effektive Optimierung von MySQL-SQL-Anweisungen – Index

1 Union-Kombination zweier Indizes

ALTER TABLE album ADD INDEX name_release (name,first_released);
EXPLAIN SELECT a.name, ar.name,
a.first_released
  FROM album a
 INNER JOIN artist ar USING (artist_id)
 WHERE a.name = 'Greatest Hits'
 ORDER BY a.first_released;
mysql> EXPLAIN SELECT a.name, ar.name,
    -> a.first_released
    ->   FROM album a
    ->  INNER JOIN artist ar USING (artist_id)
    ->  WHERE a.name = 'Greatest Hits'
    ->  ORDER BY a.first_released;
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys                  | key          | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | a     | ref    | name_release,name_2,name_part2 | name_release | 257     | const             |  659 | Using where |
|  1 | SIMPLE      | ar    | eq_ref | PRIMARY                        | PRIMARY      | 4       | union.a.artist_id |    1 |             |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)

ALTER TABLE album ADD INDEX name_release (name,first_released);

MySQL kann Indizes in den Spalten WHERE, ORDER BY und GROUP BY verwenden MySQL wählt nur einen Index für eine Tabelle aus.
Ab MySQL 5.0 verwendet der Optimierer möglicherweise mehr als einen Index in einzelnen Ausnahmen. In früheren Versionen würde dies jedoch dazu führen, dass Abfragen langsamer ausgeführt werden.

2 Vereinigung zweier Indizes
Erster Typ: Die häufigste Indexzusammenführungsoperation ist die Vereinigung zweier Indizes, wenn der Benutzer diese Art der Indexzusammenführung durchführt Die Operation tritt auf, wenn zwei Indizes mit sehr
hoher Kardinalität eine ODER-Operation durchführen. Bitte
schauen Sie sich das folgende Beispiel an:

 SET @@session.optimizer_switch='index_merge_intersection=on';
 
 EXPLAIN SELECT artist_id, name
 FROM artist
 WHERE name = 'Queen'
 OR founded = 1942\G
 
mysql>  EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->  WHERE name = 'Queen'
    ->  OR founded = 1942;
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.01 sec)

Extra: Using union(name,founded); Nehmen Sie die Sammlung.

Hinweis
Die Systemvariable „optimierer_switch“ wurde erstmals in MySQL 5.1 eingeführt. Sie können diese zusätzlichen Optionen steuern, indem Sie diese Variable aktivieren oder deaktivieren. Weitere Informationen
finden Sie unter folgendem Link: http://www.php.cn/.

2 Die zweite Art der Indexzusammenführung besteht darin, zwei Indizes mit einer kleinen Anzahl eindeutiger Werte zu überschneiden, wie unten gezeigt:

SET @@session.optimizer_switch='index_merge_intersection=on';
EXPLAIN SELECT artist_id, name
 FROM artist
  WHERE type = 'Band'
 AND founded = 1942;
 
 mysql> SET @@session.optimizer_switch='index_merge_intersection=on';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->   WHERE type = 'Band'
    ->  AND founded = 1942;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       | const |  498 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Extra: Verwenden von intersect(founded,type); Verwenden von where Da es sich um AND handelt, müssen Sie nur den effizientesten Index unter den beiden Indizes verwenden, um den Wert zu durchlaufen.

3 Die dritte Art der Indexzusammenführungsoperation ähnelt der Vereinigung zweier Indizes, muss jedoch zuerst sortiert werden:

EXPLAIN SELECT artist_id, name
 FROM artist
 WHERE name = 'Queen'
  OR (founded BETWEEN 1942 AND 1950);
  mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->  WHERE name = 'Queen'
    ->   OR (founded BETWEEN 1942 AND 1950);
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                       |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL | 5900 | Using sort_union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
1 row in set (0.00 sec)

Mehr über die Indexzusammenführung erfahren Sie über den folgenden Link: http://www.php.cn/.

4 Fälle der Zusammenführung mehrerer Indizes
Während des Erstellungsprozesses dieser Beispiele habe ich auch eine Methode entdeckt, die zuvor in keiner Client-Abfrage enthalten war. neue Situation. Hier ist ein Beispiel für das Zusammenführen von drei Indizes:

mysql> EXPLAIN SELECT artist_id, name
  FROM artist
  WHERE name = 'Queen'
 OR (type = 'Band' AND founded = '1942');
 .....
mysql> EXPLAIN SELECT artist_id, name
    ->   FROM artist
    ->   WHERE name = 'Queen'
    ->  OR (type = 'Band' AND founded = '1942');
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)

Tipps
Mehrspaltige Indizes sollten immer zur Anzeige ausgewertet werden Wenn sie besser als optimiert sind, führt der Prozessor die Indizes effizienter zusammen. Was hat mehr Vorteile: mehrere einspaltige Indizes oder mehrere mehrspaltige Indizes? Diese Frage kann nur in Verbindung mit der Abfrageart und Abfragekapazität der konkreten Anwendung beantwortet werden. Unter verschiedenen Abfragebedingungen kann die Indexzusammenführung dieser Einzelspaltenindizes für einige Spalten mit hoher Kardinalität eine hohe Flexibilität bringen. Die Leistungsreferenzfaktoren von Datenbankschreibvorgängen wirken sich auch auf den optimalen Datenzugriffspfad zum Abrufen von Daten aus.

5 Erstellen Sie bessere MySQL-Indizes
Verwenden Sie hauptsächlich 2 spezielle Indizes
Durch die Verwendung von Indizes kann die Ausführungszeit von Abfragen verkürzt werden Solche Leistungsverbesserungen können einen Leistungssprung in der Größenordnung von Sekunden bis hin zu Millisekunden bewirken.

Die richtige Abstimmung Ihrer Indizes ist für die Optimierung sehr wichtig, insbesondere für Anwendungen mit hohem Durchsatz. Auch wenn die Verbesserung der Ausführungszeit nur wenige Millisekunden beträgt, ist dies eine sehr bedeutende Leistungsverbesserung für

eine Abfrage, die 1000 Mal pro Sekunde ausgeführt wird. Beispielsweise ist die Verkürzung der
-Ausführungszeit um 4 Millisekunden für eine Abfrage, die ursprünglich 20 Millisekunden für die 1.000-malige Ausführung pro Sekunde benötigt, von entscheidender Bedeutung für die Optimierung von SQL-Anweisungen. Wir werden die in Kapitel 4 eingeführten Methoden verwenden, um mehrspaltige Indizes zu erstellen und auf dieser Basis aufzubauen, um besser abdeckende Indizes zu erstellen.


● Abdeckindex erstellen

ALTER TABLE artistDROP INDEX gegründet,
ADD INDEXfound_name (founded,name);In InnoDB der Hauptcode Der Wert von wird an jeden entsprechenden Datensatz im Nicht-Primärschlüsselindex angehängt, sodass es nicht erforderlich ist, den Primärschlüssel im Nicht-Primärschlüsselindex anzugeben.
Diese wichtige Funktion bedeutet, dass alle Nicht-Primärschlüsselindizes in der InnoDB-Engine Primärschlüsselspalten implizieren. Und für Tabellen, die von der MyISAM-Speicher-Engine konvertiert werden, wird der Primärschlüssel normalerweise als letztes Element in ihre InnoDB-Tabellenindizes eingefügt. Wenn QEP „Index verwenden“ in der Spalte „Extra“ anzeigt, bedeutet dies nicht, dass der Index beim Zugriff auf die zugrunde liegenden Tabellendaten verwendet wird, sondern dass nur dieser Index alle Anforderungen der Abfrage erfüllt. Diese Art von Index kann bei großen oder häufig ausgeführten Abfragen zu erheblichen Leistungsverbesserungen führen. Er wird als abdeckender Index bezeichnet. Ein abdeckender Index verdankt seinen Namen der Tatsache, dass er alle in einer bestimmten Tabelle in einer Abfrage verwendeten Spalten abdeckt. Um
einen abdeckenden Index zu erstellen, muss dieser Index alle Spalten in der angegebenen Tabelle enthalten, einschließlich der WHERE-Anweisung, der ORDER BY-Anweisung, der GROUP BY-Anweisung (falls vorhanden) und der
SELECT-Anweisung.

[Kommentar]: Wenn die Datenkapazität zunimmt, insbesondere wenn sie die maximale Speicher- und Festplattenkapazität überschreitet, kann das Erstellen eines Index für eine große Spalte
Auswirkungen auf die Gesamtsystemleistung haben. Abdeckindizes sind eine ideale Optimierung für große normalisierte Schemata, die viele Primärschlüssel- und Fremdschlüsseleinschränkungen geringer Länge verwenden.


● Erstellen Sie einen Index für eine lokale Spalte

ALTER TABLE artist
 DROP INDEX name,
  ADD INDEX name_part(name(20));

  这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O 开销，而这又意味着能更快地访问到需
要访问的行，尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低
基数的索引带来的影响。局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时，你可以看到记录一个短小版本的name 列不会对执行过
的SQL 语句有任何好处。最大的益处只有当你在被索引的列上添加限制条件时才能体现出来。

EXPLAIN SELECT artist_id,name,founded
 FROM artist
 WHERE name LIKE 'Queen%';
 mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name          | name | 257     | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

在这个示例中，Extra后面没有出现Using Index，所以在索引中记录全名并没有带来额外的益处。
而所提供的局部列索引满足了WHERE 条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索
引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较
是必不可少的。

count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93条记录，而SELECT count(*) FROM artist;有577983条记录，按照普遍的情况，可以走索引，难道是name(20)的20定义的太长了？

ALTER TABLE artist
 DROP INDEX name_part,
  ADD INDEX name_part2(name(10));

  mysql> ALTER TABLE artist
    ->  DROP INDEX name_part,
    ->   ADD INDEX name_part2(name(10));
Query OK, 0 rows affected (3.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name_part2    | name_part2 | 12      | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看结果，再用name(5) 试试看。
mysql> ALTER TABLE artist
    ->  DROP INDEX name_part2,
    ->   ADD INDEX name_part3(name(5));
Query OK, 0 rows affected (3.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name_part3    | name_part3 | 7       | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看来局部索引对like的效果不是很明显的，可能跟数据分布范围有关，也许这93条数据全部打散在各个数据库块中，
所以导致解析器认为不能简单地通过数次index就能遍历出数据，故而Extra栏里面就没有出现Using Index的提示。

 
总结：在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来
巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中，将1 000 000 条查询减少几毫秒
将会显著改善性能，并且获得更大的容量和扩展性。为SQL 查询创建最优索引可以认为是一项艺术。

 

 以上就是Effective MySQL之SQL语句最优化--索引 的内容，更多相关内容请关注PHP中文网（www.php.cn）！