database - una - view lenta mysql

¿Pueden varios índices trabajar juntos?

Question

Supongamos que tengo una tabla de base de datos con dos campos, "foo" y "bar". Ninguno de ellos es único, pero cada uno de ellos está indexado. Sin embargo, en lugar de indexarse ​​juntos, cada uno tiene un índice separado.

Ahora supongamos que realizo una consulta como SELECT * FROM sometable WHERE foo=''hello'' AND bar=''world''; Mi mesa tiene un gran número de filas para las que foo es ''hola'' y una pequeña cantidad de filas para las que la barra es ''mundo''.

Por lo tanto, lo más eficiente para el servidor de base de datos es usar el índice de barras para encontrar todos los campos donde la barra es ''mundo'', y luego devolver solo aquellas filas para las cuales foo es ''hola''. Este es O(n) donde n es el número de filas donde la barra es ''mundo''.

Sin embargo, me imagino que es posible que el proceso ocurra al revés, donde se utilizó el índice fo y se buscaron los resultados. Este sería O(m) donde m es el número de filas donde foo es ''hola''.

Entonces, ¿es Oracle lo suficientemente inteligente como para buscar eficientemente aquí? ¿Qué pasa con otras bases de datos? ¿O hay alguna forma de que pueda decirlo en mi consulta para buscar en el orden correcto? ¿Quizás poniendo bar=''world'' primero en la cláusula WHERE ?

Answer 1

Entonces, ¿Oracle es lo suficientemente inteligente como para buscar de manera eficiente aquí?

La respuesta simple es "probablemente". Hay muchas personas muy brillantes en cada uno de los proveedores de bases de datos que trabajan en la optimización del optimizador de consultas, por lo que probablemente esté haciendo cosas que ni siquiera había pensado. Y si actualiza las estadísticas, probablemente lo haga aún más.

Answer 2

El mejor enfoque sería agregar foo al índice de la barra, o agregar barra al índice de foo (o ambos). Si el índice de foo también contiene un índice en la barra, ese nivel de indexación adicional no afectará la utilidad del índice de foo en ninguno de los usos actuales de ese índice, ni afectará de manera apreciable el rendimiento de mantener ese índice, pero le dará a la base de datos adicional información para trabajar en la optimización de consultas como en el ejemplo.

Answer 3

Eli,

En un comentario, usted escribió:

Lamentablemente, tengo una tabla con muchas columnas, cada una con su propio índice. Los usuarios pueden consultar cualquier combinación de campos, por lo que no puedo crear índices de manera eficiente en cada combinación de campos. Pero si solo tuviera dos campos que necesitaran índices, estaría completamente de acuerdo con su sugerencia de usar dos índices. - Eli Courtwright (29 de septiembre a las 15:51)

Esta es en realidad información bastante crucial. A veces los programadores se burlan de sí mismos al hacer preguntas. Intentan destilar la pregunta hasta los puntos más importantes, pero a menudo simplifican y extrañan obtener la mejor respuesta.

Este escenario es precisamente el motivo por el que se inventaron los índices de mapas de bits: para manejar los tiempos en que se usarían grupos desconocidos de columnas en una cláusula where.

En caso de que alguien diga que los IMC son solo para columnas de baja cardinalidad, es posible que no se apliquen a su caso. Bajo probablemente no sea tan pequeño como piensas. El único problema real es la concurrencia de DML a la tabla. Debe ser de un solo hilo o raro para que esto funcione.

Answer 4

En primer lugar, asumiré que estás hablando de buenos, normales, índices de árbol b * estándar. La respuesta para los índices de mapa de bits es radicalmente diferente. Y hay muchas opciones para varios tipos de índices en Oracle que pueden o no cambiar la respuesta.

Como mínimo, si el optimizador puede determinar la selectividad de una condición particular, usará el índice más selectivo (es decir, el índice en la barra). Pero si tiene datos asimétricos (hay N valores en la barra de columnas pero la selectividad de cualquier valor particular es sustancialmente mayor o menor que 1 / N de los datos), necesitaría tener un histograma en la columna para contar el optimizador cuyos valores son más o menos probables. Y si está utilizando variables de vinculación (como deberían hacerlo todos los buenos desarrolladores de OLTP), dependiendo de la versión de Oracle, puede tener problemas con el examen de variable de vinculación.

Potencialmente, Oracle podría incluso hacer una conversión sobre la marcha de los dos índices b * -árbol a mapas de bits y combinar los mapas de bits para usar ambos índices para encontrar las filas que necesita recuperar. Pero este es un plan de consulta bastante inusual, particularmente si solo hay dos columnas donde una columna es altamente selectiva.

Answer 5

Es casi seguro que Oracle use el índice más selectivo para dirigir la consulta, y puede verificarlo con el plan de explicación.

Además, Oracle puede combinar el uso de ambos índices de varias maneras: puede convertir btree indexes en bitmaps y realizar una operación de mapa de bits y ANd en ellos, o puede realizar un hash join en los rowid devueltos por los dos índices.

Una consideración importante aquí podría ser cualquier correlación entre los valores que se consultan. Si foo = ''hello'' representa el 80% de los valores en la tabla y bar = ''world'' representa el 10%, Oracle estimará que la consulta arrojará 0.8 * 0.1 = 8% de las filas de la tabla. Sin embargo, esto puede no ser correcto: la consulta puede devolver el 10% de los rwos o incluso el 0% de las filas, dependiendo de qué tan correlacionados estén los valores. Ahora, dependiendo de la distribución de esas filas en toda la tabla, puede que no sea eficiente usar un índice para encontrarlas. Es posible que aún necesite acceder (digamos) al 70% o los bloques de tabla para recuperar las filas requeridas (google para "factor de agrupamiento"), en cuyo caso Oracle realizará una exploración completa de tabla si obtiene la estimación correcta.

En 11g, puede recopilar estadísticas de varias columnas para ayudar con esta situación, creo. En 9i y 10g puede usar el muestreo dinámico para obtener una muy buena estimación del número de filas que se recuperarán.

Para obtener el plan de ejecución, haga esto:

explain plan for
SELECT *
FROM   sometable
WHERE  foo=''hello'' AND bar=''world''
/
select * from table(dbms_xplan.display)
/

Contraste eso con:

explain plan for
SELECT /*+ dynamic_sampling(4) */
       *
FROM   sometable
WHERE  foo=''hello'' AND bar=''world''
/
select * from table(dbms_xplan.display)
/

Answer 6

Es mejor que eso.

Las búsquedas de índice siempre son más rápidas que las exploraciones de tablas completas. Así que detrás de las escenas, Oracle (y el servidor SQL para el caso) primero localizarán el rango de filas en ambos índices. Luego verá qué rango es más corto (ya que es una unión interna), y repetirá el rango más corto para encontrar las coincidencias con el mayor de los dos.

Answer 7

Estoy seguro de que también puede hacer que Oracle muestre un plan de consulta para que pueda ver exactamente qué índice se usa primero.

Answer 8

Puede proporcionar pistas sobre qué índice usar. No estoy familiarizado con Oracle, pero en Mysql puedes usar USE | IGNORE | FORCE_INDEX (mira aquí para más detalles). Para un mejor rendimiento, debe usar un índice combinado.

Answer 9

Sí, puede dar "pistas" con la consulta a Oracle. Estas sugerencias se disfrazan de comentarios ("/ * HINT * /") a la base de datos y son principalmente específicos del vendedor. Entonces, una sugerencia para una base de datos no funcionará en otra base de datos.

Utilizaría indicios de índice aquí, la primera pista para la pequeña mesa. Mira aquí .

Por otro lado, si a menudo busca estos dos campos, ¿por qué no crea un índice en estos dos? No tengo la sintaxis correcta, pero sería algo así como

CREATE INDEX IX_BAR_AND_FOO on sometable(bar,foo);

De esta manera, la recuperación de datos debería ser bastante rápida. Y en caso de que la concatenación sea única, simplemente crea un índice único que debería ser muy rápido.