tipos - Usar el índice en la tabla de unión interna en MySQL

union mysql dos tablas

Question

Tengo mesa Foo con 200 millones de registros y mesa Bar con 1000 registros, están conectados muchos a uno. Hay índices para las columnas Foo.someTime y Bar.someField. También en la barra 900 los registros tienen algún campo de 1, 100 tienen algún campo de 2.

(1) Esta consulta se ejecuta inmediatamente:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime between ''2008-08-14'' and ''2018-08-14'' and b.someField = 1 limit 20;
...
20 rows in set (0.00 sec)

(2) Este toma solo para siempre (el único cambio es b.someField = 2):

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime between ''2008-08-14'' and ''2018-08-14'' and b.someField = 2 limit 20;

(3) Pero si abandono la cláusula where en algún momento que también se ejecuta inmediatamente:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where b.someField = 2 limit 20;
...
20 rows in set (0.00 sec)

(4) También puedo acelerarlo al forzar el uso del índice:

mysql> select * from Foo f inner join Bar b force index(someField) on f.table_id = b.table_id where f.someTime between ''2008-08-14'' and ''2018-08-14'' and b.someField = 2 limit 20;
...
20 rows in set (0.00 sec)

Aquí está la explicación en la consulta (2) (que lleva una eternidad)

+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| 1 | SIMPLE | g | range | bar_id,bar_id_2,someTime | someTime | 4 | NULL | 95022220 | Using where |
| 1 | SIMPLE | t | eq_ref | PRIMARY,someField,bar_id | PRIMARY | 4 | db.f.bar_id | 1 | Using where |
+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

Aquí está la explicación en (4) (que tiene índice de fuerza)

+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| 1 | SIMPLE | t | ref | someField | someField | 1 | const | 92 | |
| 1 | SIMPLE | g | ref | bar_id,bar_id_2,someTime | bar_id | 4 | db.f.foo_id | 10558024 | Using where |
+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

¿Entonces la pregunta es cómo enseñar a MySQL a usar el índice correcto? La consulta es generada por ORM y no está limitada solo a estos dos campos. Y también sería bueno evitar mucho cambiar la consulta (aunque no estoy seguro de que la combinación interna encaje aquí).

ACTUALIZAR:

mysql> create index index_name on Foo (bar_id, someTime);

Después de eso, la consulta (2) se ejecuta en 0.00 segundos.

Answer 1

Si crea un índice compuesto para foo(table_id, sometime) , debería ayudar mucho. Esto se debe a que el servidor podrá limitar el resultado establecido por table_id primero, y luego en sometime .

Tenga en cuenta que cuando se utiliza LIMIT , el servidor no garantiza qué filas se recuperarán si muchos califican para su restricción WHERE. Técnicamente, cada ejecución puede darte resultados ligeramente diferentes. Si desea evitar la ambigüedad, siempre debe usar ORDER BY cuando usa LIMIT . Sin embargo, eso también significa que debe tener más cuidado al crear índices apropiados.