ejemplos - mysql hint force use index

¿Cómo hacer que JOIN query use index?

Question

Tengo dos mesas:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(1000) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `last_updated` (`last_updated`),
) ENGINE=InnoDB AUTO_INCREMENT=799681 DEFAULT CHARSET=utf8 

CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT ''0'',
  `category_id` int(11) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Esta es mi consulta:

SELECT a.*
FROM
    articles AS a,
    article_categories AS c
WHERE
    a.id = c.article_id
    AND c.category_id = 78
    AND a.comment_cnt > 0
    AND a.deleted = 0
ORDER BY a.last_updated
LIMIT 100, 20

Y una EXPLAIN para ello:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: index
possible_keys: PRIMARY
          key: last_updated
      key_len: 9
          ref: NULL
         rows: 2040
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY,fandom_id
          key: PRIMARY
      key_len: 8
          ref: db.a.id,const
         rows: 1
        Extra: Using index

Utiliza una exploración de índice completa de last_updated en la primera tabla para ordenar, pero no usa un índice y para join ( type: index in explain). Esto es muy malo para el rendimiento y mata todo el servidor de la base de datos, ya que esta es una consulta muy frecuente.

Intenté invertir el orden de las tablas con STRAIGHT_JOIN , pero esto da filesort, using_temporary , que es aún peor.

¿Hay alguna manera de hacer que mysql use el índice para unirse y para ordenar al mismo tiempo?

=== actualización ===

Estoy realmente desmejorado en esto. Tal vez algún tipo de desnormalización puede ayudar aquí?

Answer 1

Antes de llegar a su consulta específica, es importante entender cómo funciona un índice.

Con estadísticas apropiadas, esta consulta:

select * from foo where bar = ''bar''

... usará un índice en foo(bar) si es selectivo. Eso significa que si bar = ''bar'' equivale a seleccionar la mayoría de las filas de la tabla, irá más rápido para leer la tabla y eliminar las filas que no se apliquen. Por el contrario, si bar = ''bar'' significa solo seleccionar un puñado de filas, leer el índice tiene sentido.

Supongamos que ahora lanzamos una cláusula de orden y que tiene índices en cada uno de foo(bar) y foo(baz) :

select * from foo where bar = ''bar'' order by baz

Si bar = ''bar'' es muy selectivo, es barato tomar todas las filas que cumplan y ordenarlas en la memoria. Si no es para nada selectivo, el índice en foo(baz) tiene poco sentido porque de todos modos obtendrá toda la tabla: usarlo significa ir y venir en las páginas del disco para leer las filas en orden, lo cual es muy costoso.

Mezcle en una cláusula de límite, sin embargo, y foo(baz) repente podría tener sentido:

select * from foo where bar = ''bar'' order by baz limit 10

Si bar = ''bar'' es muy selectivo, sigue siendo una buena opción. Si no es para nada selectivo, rápidamente encontrará 10 filas coincidentes escaneando el índice en foo(baz) ; puede leer 10 filas, o 50, pero encontrará 10 buenas pronto.

Supongamos que la última consulta con índices en foo(bar, baz) y foo(baz, bar) lugar. Los índices se leen de izquierda a derecha. Uno tiene muy buen sentido para esta consulta potencial, el otro podría no tener ninguno. Piensa en ellos así:

bar   baz    baz   bar
---------    ---------
bad   aaa    aaa   bad
bad   bbb    aaa   bar
bar   aaa    bbb   bad
bar   bbb    bbb   bar

Como puede ver, el índice en foo(bar, baz) permite comenzar a leer en (''bar'', ''aaa'') y buscar las filas en orden desde ese punto en adelante.

El índice en foo(baz, bar) , por el contrario, produce filas ordenadas por baz independientemente de qué bar pueda contener. Si bar = ''bar'' no es para nada selectivo como un criterio, rápidamente se encontrará con filas coincidentes para su consulta, en cuyo caso tiene sentido usarlo. Si es muy selectivo, puede terminar iterando gazillones de filas antes de encontrar lo suficiente para que coincida con bar = ''bar'' - aún podría ser una buena opción, pero es tan óptimo.

Con eso siendo abordado, volvamos a su consulta original ...

Necesita unir artículos con categorías, filtrar artículos que están en una categoría particular, con más de un comentario, que no se eliminen, y luego ordenarlos por fecha y luego tomar un puñado de ellos.

Supongo que la mayoría de los artículos no se eliminan, por lo que un índice sobre ese criterio no será de mucha utilidad, solo ralentizará las escrituras y la planificación de consultas.

Supongo que la mayoría de los artículos tienen un comentario o más, por lo que tampoco será selectivo. Es decir, hay poca necesidad de indexarlo tampoco.

Sin su filtro de categoría, las opciones de índice son razonablemente obvias: articles(last_updated) ; posiblemente con la columna de recuento de comentarios a la derecha y la bandera eliminada a la izquierda.

Con tu filtro de categoría, todo depende ...

Si su filtro de categoría es muy selectivo, tiene mucho sentido seleccionar todas las filas que están dentro de esa categoría, ordenarlas en la memoria y seleccionar las filas que coincidan más arriba.

Si su filtro de categoría no es para nada selectivo y produce casi artículo, el índice de articles(last_update) tiene sentido: las filas válidas están por todos lados, así que lea las filas en orden hasta que encuentre suficiente coincidencia y voilà .

En el caso más general, es vagamente selectivo. A mi leal saber y entender, las estadísticas recopiladas no analizan mucho las correlaciones. Por lo tanto, el planificador no tiene una buena manera de estimar si encontrará artículos con la categoría correcta lo suficientemente rápido como para valer la pena leer el último índice. Unir y clasificar en la memoria generalmente será más barato, por lo que el planificador se va con eso.

De todos modos, tienes dos opciones para forzar el uso de un índice.

Una de ellas es reconocer que el planificador de consultas no es perfecto y usar una pista:

http://dev.mysql.com/doc/refman/5.5/en/index-hints.html

Tenga cuidado, porque a veces el planificador es correcto al no querer usar el índice que le gustaría o la versión del vicio. Además, puede volverse correcto en una versión futura de MySQL, así que tenlo en cuenta mientras mantienes tu código a lo largo de los años.

Editar: STRAIGHT_JOIN , como señala DRAP también funciona, con advertencias similares.

El otro es mantener una columna adicional para etiquetar artículos seleccionados con frecuencia (por ejemplo, un campo de minúscula, que se establece en 1 cuando pertenecen a su categoría específica), y luego agregar un índice en, por ejemplo, articles(cat_78, last_updated) . Manténlo usando un disparador y lo harás bien.

Answer 2

Antes que nada, recomendaría leer el artículo 3 formas en que MySQL usa los índices .

Y ahora, cuando conoces los conceptos básicos, puedes optimizar esta consulta en particular.

MySQL no puede usar el índice para ordenar, solo puede generar datos en un orden de índice. Dado que MySQL utiliza bucles anidados para unirse, el campo que desea ordenar debe estar en la primera tabla de la combinación (verá el orden de unirse en los resultados de EXPLAIN, y puede afectarlo al crear índices específicos y (si no ayuda) ) al forzar los índices requeridos).

Otra cosa importante es que antes de realizar el pedido, busque todas las columnas para todas las filas filtradas de a tabla y luego omita probablemente la mayoría de ellas. Es mucho más eficaz obtener una lista de identificadores de fila requeridos y obtener solo esas filas.

Para que esto funcione, necesitará un índice de cobertura (deleted, comment_cnt, last_updated) en la tabla a , y ahora puede reescribir la consulta de la siguiente manera:

SELECT *
FROM (
  SELECT a.id
  FROM articles AS a,
  JOIN article_categories AS c
    ON a.id = c.article_id AND c.category_id = 78
  WHERE a.comment_cnt > 0 AND a.deleted = 0
  ORDER BY a.last_updated
  LIMIT 100, 20
) as ids
JOIN articles USING (id);

PD La definición de la tabla para la tabla a no contiene la columna comment_cnt ;)

Answer 3

El uso de un índice que no cubre es costoso. Para cada fila, todas las columnas descubiertas deben recuperarse de la tabla base, utilizando la clave principal. Entonces, primero trataría de hacer que el índice cubra los articles . Eso podría ayudar a convencer al optimizador de consultas MySQL de que el índice es útil. Por ejemplo:

KEY IX_Articles_last_updated (last_updated, id, title, comment_cnt, deleted),

Si eso no ayuda, podría jugar con FORCE INDEX :

SELECT  a.*
FROM    article_categories AS c FORCE INDEX (IX_Articles_last_updated)
JOIN    articles AS a FORCE INDEX (PRIMARY)
ON      a.id = c.article_id
WHERE   c.category_id = 78
        AND a.comment_cnt > 0
        AND a.deleted = 0
ORDER BY 
        a.last_updated
LIMIT   100, 20

El nombre del índice que aplica la clave primaria siempre es "primario".

Answer 4

Puede usar influence MySQL para usar las LLAVES o ÍNDICES

por

• Ordenando, o
• Agrupando, o
• Unirse

Para obtener información adicional, siga este enlace . Tenía la intención de usar esto para unirme (es decir, USE INDEX FOR JOIN (My_Index) pero no funcionó como esperaba. Eliminar la parte FOR JOIN aceleró mi consulta significativamente, de más de 3.5 horas a 1-2 segundos. Simplemente porque MySQL se vio obligado a usar el índice correcto.

Answer 5

Si tiene muchas categorías, esta consulta no puede ser eficiente. Ningún índice individual puede cubrir dos tablas a la vez en MySQL .

Tienes que hacer desnormalización: agregar last_updated , has_comments y deleted en article_categories :

CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT ''0'',
  `category_id` int(11) NOT NULL DEFAULT ''0'',
  `last_updated` timestamp NOT NULL,
  `has_comments` boolean NOT NULL,
  `deleted` boolean NOT NULL,
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `ix_articlecategories_category_comments_deleted_updated` (category_id, has_comments, deleted, last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

y ejecuta esta consulta:

SELECT  *
FROM    (
        SELECT  article_id
        FROM    article_categories
        WHERE   (category_id, has_comments, deleted) = (78, 1, 0)
        ORDER BY
                last_updated DESC
        LIMIT   100, 20
        ) q
JOIN    articles a
ON      a.id = q.article_id

Por supuesto, debe actualizar article_categories cada vez que actualice columnas relevantes en el article . Esto se puede hacer en un desencadenador.

Tenga en cuenta que la columna has_comments es booleana: esto permitirá usar un predicado de igualdad para hacer un escaneo de rango único sobre el índice.

También tenga en cuenta que el LIMIT entra en la subconsulta. Esto hace que MySQL use las últimas búsquedas de filas que no utiliza por defecto. Vea este artículo en mi blog sobre por qué aumentan el rendimiento:

• Rendimiento de ORDER BY / LIMIT de MySQL: búsquedas de última fila

Si estuvieras en SQL Server, podrías hacer una vista indexable sobre tu consulta, lo que esencialmente haría una copia indexada y no normalizada de article_categories con los campos adicionales, mainained automáticamente por el servidor.

Desafortunadamente, MySQL no es compatible con esto y tendrá que crear dicha tabla manualmente y escribir código adicional para mantenerlo sincronizado con las tablas base.

Answer 6

Tendría los siguientes índices disponibles

tabla de artículos - ÍNDICE (eliminado, last_updated, comment_cnt)

tabla article_categories - INDEX (article_id, category_id) - ya tienes este índice

luego, agregue Straight_Join para forzar la consulta como se indica en lugar de intentar usar la tabla article_categories a través de las estadísticas que pueda tener para ayudar a la consulta.

SELECT STRAIGHT_JOIN
      a.*
   FROM
      articles AS a
         JOIN article_categories AS c
            ON a.id = c.article_id
            AND c.category_id = 78
   WHERE
          a.deleted = 0
      AND a.comment_cnt > 0
   ORDER BY 
      a.last_updated
   LIMIT 
      100, 20

Según comentarios / comentarios, consideraría invertir según el conjunto si los registros de categorías son mucho más pequeños ... como

SELECT STRAIGHT_JOIN
      a.*
   FROM
      article_categories AS c
         JOIN articles as a
            ON c.article_id = a.id
           AND a.deleted = 0
           AND a.Comment_cnt > 0
   WHERE
      c.category_id = 78
   ORDER BY 
      a.last_updated
   LIMIT 
      100, 20

En este caso, garantizaría un índice en la tabla de artículos por

índice - (id, eliminado, last_updated)