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

Abierto
  • ¿Algo no está bien?
#1,538 1 comentario 2 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 10 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
35/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Estancado
Stack tecnológico
go, mysql
Área
backend, databases

Línea de trabajo

Busca BuildUniqueKeyRangeEndPreparedQueryViaOffset e inspecciona cómo se construye esta consulta de MySQL. Reproduce el comportamiento informado de EXPLAIN y SELECT con y sin USE INDEX (PRIMARY), y determina después si es seguro forzar el índice. Se considera terminado cuando el problema del optimizador está resuelto con cobertura para la ruta de consulta afectada, si el enfoque es apropiado.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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?

Lenguaje dominante
Go
Estrellas
13.6k
Forks
1.4k
Merge medio
9 d 19 h
PR fusionados (30 d)
1

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de github/gh-ost

Todos los issues de github/gh-ost

Issues similares

Más issues de Go

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.