MySQL query optimizer doesn't necessarily use unique key in BuildUniqueKeyRangeEndPreparedQueryViaOffset's query

Offen
  • Stimmt etwas nicht?
#1,538 1 Kommentar 2 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 10 Tagen

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Anfängerfreundlichkeit
35/100
Issue-Typ
Bug
Klarheit
Größtenteils klar
Aktivitätsstatus
Veraltet
Tech-Stack
go, mysql
Bereich
backend, databases

Rechercherichtung

Suche nach BuildUniqueKeyRangeEndPreparedQueryViaOffset und untersuche, wie diese MySQL-Abfrage erstellt wird. Reproduziere das gemeldete EXPLAIN- und SELECT-Verhalten mit und ohne USE INDEX (PRIMARY) und ermittle anschließend, ob das Erzwingen des Index sicher ist. Als erledigt gilt die Aufgabe, wenn das Optimizer-Problem mit einer Abdeckung für den betroffenen Abfragepfad behoben ist, sofern dieser Ansatz geeignet ist.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

bug performance

We've run into an issue where the gh-ost migration starts off fine, but at some point, the MySQL query optimizer behavior changes for this query, and this query starts taking ~15m.

The explain query shows that the MySQL query optimizer is doing an index scan

mysql> explain select `applicationId`, `id` from `_workflowActionExecutions` where ((`applicationId` > _binary'appAAAA') or (((`applicationId` = _binary'appAAAA')) AND (`id` > _binary'waxAAAA'))) and ((`applicationId` < _binary'appZZZZ') or (((`applicationId` =
_binary'appZZZZ')) AND (`id` < _binary'waxZZZZ')) or ((`applicationId` = _binary'appZZZZ') and (`id` = _binary'waxZZZZ'))) order by `applicationId` asc, `id` asc limit 1 offset 999;
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                     | partitions | type  | possible_keys                                                                                                                                                                                       | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | _workflowActionExecutions | NULL       | index | PRIMARY,IDX_applicationId_workflowGraphExecutionId,IDX_applicationId_workflowExecutionId_status_branchId,IDX_applicationId_status_workflowId,IDX_applicationId_workflowId_status_processTime,IDX_id | PRIMARY | 34      | NULL | 1000 |   100.00 | Using where; Using index |
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.47 sec)

(ids fuzzed)

This is because MySQL thinks that if we USE INDEX (PRIMARY), it will end up doing a massive range scan

mysql> explain select `applicationId`, `id`
    -> from `_workflowActionExecutions` USE INDEX (PRIMARY)
    -> where ((`applicationId` > _binary'appAAAA') or (((`applicationId` = _binary'appAAAA')) AND (`id` > _binary'waxAAAA'))) and ((`applicationId` < _binary'appZZZZ') or (((`applicationId` = _binary'appZZZZ')) AND (`id` < _binary'waxZZZZ')) or ((`applicationId` = _binary'appZZZZ') and (`id` = _binary'waxZZZZ')))
    -> order by `applicationId` asc, `id` asc
    -> limit 1
    -> offset 999;
+----+-------------+---------------------------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+
| id | select_type | table                     | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra                    |
+----+-------------+---------------------------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+
|  1 | SIMPLE      | _workflowActionExecutions | NULL       | range | PRIMARY       | PRIMARY | 34      | NULL | 95809902 |   100.00 | Using where; Using index |
+----+-------------+---------------------------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+
1 row in set, 1 warning (0.46 sec)

(note the massive amount of rows)

But if I run the query with USE INDEX (PRIMARY), it's actually really fast, instead of taking ~15m

mysql> select `applicationId`, `id`
    -> from `_workflowActionExecutions` USE INDEX (PRIMARY)
    -> where ((`applicationId` > _binary'appAAAA') or (((`applicationId` = _binary'appAAAA')) AND (`id` > _binary'waxAAAA'))) and ((`applicationId` < _binary'appZZZZ') or (((`applicationId` = _binary'appZZZZ')) AND (`id` < _binary'waxZZZZ')) or ((`applicationId` = _binary'appZZZZ') and (`id` = _binary'waxZZZZ')))
    -> order by `applicationId` asc, `id` asc
    -> limit 1
    -> offset 999;
+-------------------+-------------------+
| applicationId     | id                |
+-------------------+-------------------+
...
+-------------------+-------------------+
1 row in set (0.53 sec)

Is it possible to force the use of the index when constructing the query? Is that an appropriate solution?

Vorherrschende Sprache
Go
Sterne
13.6k
Forks
1.4k
Ø Merge
9 T. 19 Std.
Gemergte PRs (30 T.)
1

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus github/gh-ost

Alle Issues in github/gh-ost

Ähnliche Issues

Weitere Issues zu Go

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.