Beeinträchtigung der Verwendung des Hash-Joins durch den MySQL-Optimierer

Dieser Artikel bringt Ihnen relevantes Wissen über MySQL, das hauptsächlich die Verwendung von Hash-Joins durch den MySQL-Optimierer vorstellt wird Ihnen hilfreich sein.

Empfohlenes Lernen: MySQL-Video-Tutorial

Der Originalinhalt der GreatSQL-Community darf nicht ohne Genehmigung verwendet werden und gibt die Quelle für den Nachdruck an. GreatSQL ist die inländische Zweigversion von MySQL und seine Verwendung stimmt mit MySQL überein.

Vorwort

Der Datenbankoptimierer entspricht dem menschlichen Gehirn. Meistens kann er richtige Entscheidungen treffen, korrekte Ausführungspläne formulieren und einen effizienten Weg finden, aber schließlich basiert er auf bestimmten festen Regeln und Algorithmen Manchmal sind die Urteile des Optimierers nicht so flexibel wie unser menschliches Gehirn. Was sollten wir tun, wenn wir feststellen, dass der Optimierer den falschen Ausführungsplan auswählt? welchen Weg man wählen soll.

Wir wissen, dass Oracle flexiblere Hinweise bereitstellt, um dem Optimierer mitzuteilen, welche Tabellenverbindungsmethode beim Verbinden mehrerer Tabellen ausgewählt werden soll, z. B. use_nl, no_use_nl, um zu steuern, ob Nest verwendet werden soll Loop Join, use_hash, no_use_hash steuert, ob Hash-Join verwendet werden soll. use_nl，no_use_nl控制是否使用Nest Loop Join，use_hash,no_use_hash控制是否使用hash join。

但是MySQL长期以来只有一种表连接方式，那就是Nest Loop Join，直到MySQL8.0.18版本才出现了hash join， 所以MySQL在控制表连接方式上没有提供那么多丰富的hint给我们使用，hash_join与no_hash_join的hint只是惊鸿一瞥，只在8.0.18版本存在，8.0.19及后面的版本又将这个hint给废弃了，那如果我们想让两个表做hash join该怎么办呢？

实验

我们来以MySQL8.0.25的单机环境做一个实验。建两个表，分别插入10000行数据，使用主键做这两个表的关联查询。

create table t1(id int primary key,c1 int,c2 int);
create table t2(id int primary key,c1 int,c2 int);
delimiter //
CREATE PROCEDURE p_test()
BEGIN
declare i int;
set i=1;
while i<10001 do
insert into t1 values(i,i,i);
insert into t2 values(i,i,i);
SET i = i + 1;
end while;
END;
//
delimiter ;

查询一下两表使用主键字段关联查询时实际的执行计划，如下图所示：

查询一下两表使用非索引字段关联查询时实际的执行计划，如下图所示：

从执行计划可以看出，被驱动表的关联字段上有索引，优化器在选择表连接方式时会倾向于选择Nest Loop Join，当没有可用索引时倾向于选择hash join。

基于这一点那我们可以使用no_index提示来禁止语句使用关联字段的索引。

从上面的执行计划可以看出使用no_index提示后，优化器选择了使用hash join。

当索引的选择性不好时，优化器选择使用索引做Nest Loop Join是效率是很低的。

我们将实验的两个表中c1列的数据做一下更改,使其选择性变差，并在c1列上建普通索引。

update t1 set c1=1 where id<5000;
update t2 set c1=1 where id<5000;
create index idx_t1 on t1(c1);
create index idx_t2 on t2(c1);

当我们执行sql ：

select t1.*,t2.* from t1 join t2 on t1.c1=t2.c1;

这个查询结果会返回大量数据，被驱动表的关联字段c1列的索引选择性差，此时选择hash join是更明智的选择，但是优化器会选择走Nest Loop Join。我们可以通过实验验证一下hash join 与 Nest Loop Join的性能差异。

可以看出使用hash join的耗时是使用Nest Loop Join的1/6，但是优化器根据成本估算时，使用Nest Loop Join的成本要比使用hash join的成本低很多，所以会去选择Nest Loop Join，这个时候就需要加上hint 提示禁止使用关联字段的索引，被驱动表上每次都全表扫描的代价是很高的，这样优化器估算后就会选择走hash join。

MySQL官方文档里提到用BNL，NO_BNL

Aber MySQL verfügt seit langem nur über eine Tabellenverbindungsmethode, und zwar Nest Loop Join, die erst in MySQL-Version 8.0.18 erschien, sodass MySQL nicht so viel Umfang bietet in der Kontrolltabellen-Verbindungsmethode. Die Hinweise von hash_join sind nur ein kleiner Einblick. Sie sind nur in Version 8.0.18 und späteren Versionen vorhanden Hinweis wird uns gegeben: Was sollen wir tun, wenn wir einen Hash-Join zwischen zwei Tabellen durchführen möchten?

Experiment🎜🎜Lassen Sie uns ein Experiment in der eigenständigen Umgebung von MySQL8.0.25 durchführen. Erstellen Sie zwei Tabellen, fügen Sie jeweils 10.000 Datenzeilen ein und verwenden Sie den Primärschlüssel, um verwandte Abfragen zwischen den beiden Tabellen durchzuführen. 🎜rrreee🎜Fragen Sie den tatsächlichen Ausführungsplan ab, wenn die beiden Tabellen das Primärschlüsselfeld verwenden, um die Abfrage zuzuordnen, wie in der folgenden Abbildung dargestellt: 🎜

🎜🎜Fragen Sie den tatsächlichen Ausführungsplan ab, wenn zwei Tabellen Nicht-Indexfelder verwenden, um Abfragen zuzuordnen, wie gezeigt in der Abbildung unten: 🎜

🎜🎜Ausführen von Aus dem Plan geht hervor, dass es Indizes für die zugehörigen Felder der gesteuerten Tabelle gibt. Der Optimierer tendiert dazu, Nest Loop Join zu wählen, wenn er die Tabellenverbindungsmethode auswählt. Wenn keine verfügbar ist Index wird tendenziell ein Hash-Join gewählt. 🎜🎜Auf dieser Grundlage können wir die Eingabeaufforderung no_index verwenden, um zu verhindern, dass die Anweisung den Index des zugehörigen Felds verwendet. 🎜

🎜🎜Aus dem obigen Ausführungsplan ist ersichtlich, dass sich der Optimierer nach Verwendung der no_index-Eingabeaufforderung für die Verwendung von Hash-Join entschieden hat. 🎜🎜Wenn die Selektivität des Index nicht gut ist, entscheidet sich der Optimierer dafür, den Index für den Nest Loop Join zu verwenden, was sehr ineffizient ist. 🎜🎜Wir werden die Daten in Spalte c1 in den beiden Tabellen im Experiment ändern, um sie weniger selektiv zu machen, und einen normalen Index für Spalte c1 erstellen. 🎜rrreee🎜Wenn wir SQL ausführen: 🎜rrreee🎜Dieses Abfrageergebnis gibt eine große Datenmenge zurück. Die Indexselektivität der c1-Spalte des zugehörigen Felds der gesteuerten Tabelle ist zu diesem Zeitpunkt klüger Auswahl, aber der Optimierer wählt Nest Loop Join. Wir können den Leistungsunterschied zwischen Hash-Join und Nest Loop Join durch Experimente überprüfen. 🎜

🎜🎜Es ist ersichtlich, dass der Zeitaufwand für die Verwendung von Hash-Join 1/6 des Zeitaufwands für die Verwendung von Nest Loop Join beträgt. Wenn der Optimierer jedoch anhand der Kosten schätzt, sind die Kosten für die Verwendung von Nest Loop Join viel niedriger als die Kosten für die Verwendung von Hash Beitreten, daher wird Nest Loop Join ausgewählt. Zu diesem Zeitpunkt müssen Sie Hinweise hinzufügen, um die Verwendung von Indizes für verwandte Felder zu verhindern. Die Kosten für das Scannen der gesamten Tabelle jedes Mal auf der gesteuerten Tabelle sind sehr hoch, daher wird der Optimierer dies tun Wählen Sie nach der Schätzung einen Hash-Join. 🎜🎜Die offizielle MySQL-Dokumentation erwähnt die Verwendung von Hinweisen auf BNL und NO_BNL, um die Optimierung des Hash-Joins zu beeinflussen. Experimente haben jedoch gezeigt, dass es keinen verfügbaren Index für das zugehörige Feld gibt Wenn der Optimierer die Kosten schätzt, verwendet er nicht den vollständigen BNL-Tabellenscan für Nested-Loop-Joins in der gesteuerten Tabelle, sondern entscheidet sich für die Verwendung von Hash-Joins, sodass NO_BNL in diesem Szenario nutzlos ist. 🎜🎜Können wir diesen Index nicht einfach entfernen, da wir ihn nicht verwenden? Warum müssen wir den Hinweis von no_index verwenden? Es gibt so viele Geschäftsnutzungsszenarien. Wenn dieser Index an anderer Stelle verwendet wird, kann dies der Fall sein Gleichzeitig werden die Vorteile von Hinweisen hervorgehoben. Sie müssen nur diese Anweisung verwenden. 🎜

Zusammenfassung

Nest Loop Join hat seine Vorteile Es ist die schnellste Verbindungsmethode für Antworten und eignet sich für Szenarien, in denen die Menge der zurückgegebenen Daten gering ist. Wenn zwei große Tabellen verbunden sind und eine große Datenmenge zurückgegeben wird und der Index des zugehörigen Felds relativ ineffizient ist, ist die Verwendung eines Hash-Joins effizienter. Wir können den Hinweis „no_index“ verwenden, um den ineffizienten Index des zugehörigen Felds zu deaktivieren Feld, das den Optimierer auffordert, Hash-Join auszuwählen.

Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonBeeinträchtigung der Verwendung des Hash-Joins durch den MySQL-Optimierer. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!