¿Cómo obtengo MySQL para usar un ÍNDICE para consulta de vista?

Estoy trabajando en un proyecto web con base de datos MySql en Java EE. Necesitábamos una vista para resumir los datos de 3 tablas con más de 3 millones de filas en general. Cada tabla fue creada con índice. Pero no he encontrado una manera de aprovechar las ventajas de los índices en la recuperación de sentencias de selección condicional de la vista que creamos con [group by].

Recibí sugerencias de personas que utilizar vistas en MySql no es una buena idea . Porque no puede crear un índice para vistas en mysql como en oracle. Pero en alguna prueba que tomé, los índices se pueden usar en la vista Seleccionar statement. Tal vez he creado esos puntos de vista de una manera incorrecta.

Usaré un ejemplo para describir mi problema.

Tenemos una tabla que registra los datos de los puntajes más altos en los juegos de la NBA, con índice en la columna [happend_in]

CREATE TABLE `highscores` ( `tbl_id` int(11) NOT NULL auto_increment, `happened_in` int(4) default NULL, `player` int(3) default NULL, `score` int(3) default NULL, PRIMARY KEY (`tbl_id`), KEY `index_happened_in` (`happened_in`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

insertar datos (8 filas)

 INSERT INTO highscores(happened_in, player, score) VALUES (2006, 24, 61),(2006, 24, 44),(2006, 24, 81), (1998, 23, 51),(1997, 23, 46),(2006, 3, 55),(2007, 24, 34), (2008, 24, 37); 

luego creo una vista para ver el puntaje más alto que obtuvo Kobe Bryant cada año

 CREATE OR REPLACE VIEW v_kobe_highScores AS SELECT player, max(score) AS highest_score, happened_in FROM highscores WHERE player = 24 GROUP BY happened_in; 

Escribí un enunciado condicional para ver el puntaje más alto que obtuvo Kobe en 2006 ;

 select * from v_kobe_highscores where happened_in = 2006; 

Cuando lo explico en sapo para mysql, descubrí que mysql ha escaneado todas las filas para formar la vista, luego busca datos con la condición en él, sin usar el índice en [happened_in].

 explain select * from v_kobe_highscores where happened_in = 2006; 

explicar el resultado

La vista que usamos en nuestro proyecto está construida entre tablas con millones de filas. Escanear todas las filas de la tabla en todas las vistas de recuperación de datos es inaceptable. ¡Por favor ayuda! ¡Gracias!

@zerkms Aquí está el resultado que probé en la vida real. No veo muchas diferencias entre. Creo que @ spencer7593 tiene el punto correcto. El optimizador de MySQL no “empuja” ese predicado hacia abajo en la consulta de vista. prueba de la vida real

¿Cómo consigues que MySQL use un índice para una consulta de vista? La respuesta corta, proporciona un índice que MySQL puede usar.

En este caso, el índice óptimo es probablemente un índice de “cobertura”:

 ... ON highscores (player, happened_in, score) 

Es probable que MySQL use ese índice, y EXPLAIN mostrará: "Using index" debido al WHERE player = 24 (un predicado de igualdad en la columna principal del índice. GROUP BY happened_id (la segunda columna en el índice) , puede permitir que MySQL optimice el uso del índice para evitar una operación de clasificación. Incluir la columna de score en el índice permitirá que la consulta esté completamente satisfecha con el índice, sin tener que visitar (consultar) las páginas de datos a las que hace referencia el índice.

Esa es la respuesta rápida. La respuesta más larga es que es muy poco probable que MySQL use un índice con la columna principal de happened_id para la consulta de vista.


Por qué la vista causa un problema de rendimiento

Uno de los problemas que tiene con la vista de MySQL es que MySQL no “empuja” el predicado desde la consulta externa hacia la consulta de vista.

Su consulta externa especifica WHERE happened_in = 2006 . El optimizador de MySQL no tiene en cuenta el predicado cuando ejecuta la “consulta de vista” interna. Esa consulta para la vista se ejecuta por separado, antes de la consulta externa. El conjunto de resultados de la ejecución de esa consulta se “materializa”; es decir, los resultados se almacenan como una tabla intermedia MyISAM. (MySQL lo llama una “tabla derivada”, y el nombre que usan tiene sentido, cuando comprendes las operaciones que realiza MysQL).

La conclusión es que MySQL no está utilizando el índice que ha definido en happen_in cuando inicia la consulta que forma la definición de la vista.

Después de que se crea la “tabla derivada” intermedia, ENTONCES se ejecuta la consulta externa, usando esa “tabla derivada” como una fuente de filas. Es cuando se ejecuta esa consulta externa que se evalúa el predicado happened_in = 2006 .

Tenga en cuenta que todas las filas de la consulta de vista se almacenan, lo que (en su caso) es una fila para CADA valor de happened_in , no solo el que especifica un predicado de igualdad en la consulta externa.

La forma en que se procesan las consultas de vista puede ser “inesperada” para algunos, y esta es una razón por la que el uso de “vistas” en MySQL puede provocar problemas de rendimiento, en comparación con la forma en que otras bases de datos relacionales procesan las consultas.


Mejora del rendimiento de la consulta de vista con un índice de cobertura adecuado

Dada su definición de vista y su consulta, lo mejor que obtendrá será un método de acceso “Uso de índice” para la consulta de vista. Para obtener eso, necesitaría un índice de cobertura, por ejemplo

 ... ON highscores (player, happened_in, score). 

Es probable que sea el índice más beneficioso (rendimiento) para su definición de vista existente y su consulta existente. La columna de player es la columna principal porque tiene un predicado de igualdad en esa columna en la consulta de vista. La siguiente columna es siguiente, porque tiene una operación GROUP BY en esa columna, y MySQL podrá usar este índice para optimizar la operación GROUP BY. También incluimos la columna de score porque esa es la única columna a la que se hace referencia en su consulta. Eso hace que el índice sea un índice de “cobertura”, porque MySQL puede satisfacer esa consulta directamente desde páginas de índice, sin necesidad de visitar ninguna página en la tabla subyacente. Y eso es lo mejor que vamos a salir de ese plan de consulta: “Usar índice” sin “Usar filesort”.


Compare el rendimiento con la consulta independiente sin tabla derivada

Puede comparar el plan de ejecución de su consulta con la vista en comparación con una consulta independiente equivalente:

 SELECT player , MAX(score) AS highest_score , happened_in FROM highscores WHERE player = 24 AND happened_in = 2006 GROUP BY player , happened_in 

La consulta independiente también puede hacer uso de un índice de cobertura, por ejemplo

 ... ON highscores (player, happened_in, score) 

pero sin necesidad de materializar una tabla MyISAM intermedia.


No estoy seguro de que ninguna de las respuestas anteriores brinde una respuesta directa a la pregunta que estaba haciendo.

P: ¿Cómo obtengo MySQL para usar un INDICE para consultar consultas?

A: defina un ÍNDICE adecuado que la consulta de vista puede usar.

La respuesta corta es proporcionar un “índice de cobertura” (el índice incluye todas las columnas a las que se hace referencia en la consulta de vista). Las columnas principales en ese índice deben ser las columnas a las que se hace referencia con predicados de igualdad (en su caso, el player columna sería una columna principal porque tiene un predicador player = 24 en la consulta. Además, las columnas a las que se hace referencia en GROUP BY deberían ser columnas principales en el índice, lo que permite a MySQL optimizar la operación GROUP BY , haciendo uso del índice en lugar de usar una operación de clasificación.

El punto clave aquí es que la consulta de vista es básicamente una consulta independiente; los resultados de esa consulta se almacenan en una tabla intermedia “derivada” (una tabla MyISAM que se crea cuando se ejecuta una consulta en la vista).

El uso de vistas en MySQL no es necesariamente una “mala idea”, pero yo les advierto encarecidamente a aquellos que decidan usar vistas dentro de MySQL que tengan en cuenta cómo MySQL procesa las consultas que hacen referencia a esas vistas. Y la forma en que MySQL procesa las consultas de vista difiere (significativamente) de la forma en que otras bases de datos manejan las consultas de visualización (por ejemplo, Oracle, SQL Server).

Crear el índice compuesto con las columnas player + happened_in (en este orden particular) es lo mejor que puede hacer en este caso.

PD: no pruebe el comportamiento del optimizador de MySQL en una cantidad tan pequeña de filas, porque es probable que prefiera el escaneo completo de los índices. Si desea ver lo que sucederá en la vida real, llénelo con una cantidad de datos reales.

Esto no responde directamente a la pregunta, pero es una solución directamente relacionada para otros que se encuentran con este problema. Esto logra los mismos beneficios de usar una vista, mientras minimiza las desventajas.

Configuré una función de PHP a la que puedo enviar parámetros, elementos para insertar en el interior para maximizar el uso del índice, en lugar de usarlos en una cláusula join o where fuera de una vista. En la función, puede formular la syntax SQL para una tabla derivada y devolver esa syntax. Luego, en el progtwig de llamadas, puede hacer algo como esto:

 $table = tablesyntax(parameters); select field1, field2 from {$table} as x... + other SQL 

De este modo, obtiene los beneficios de encapsulación de la vista, la capacidad de llamarlo como si fuera una vista, pero no las limitaciones del índice.