Acelerar el conteo de filas en MySQL

Supongamos, para fines ilustrativos, que está ejecutando una biblioteca utilizando una tabla simple de “libros” de MySQL con tres columnas:

(id, título, estado)

  • id es la clave principal
  • título es el título del libro
  • El estado podría ser una enumeración que describa el estado actual del libro (por ejemplo, DISPONIBLE, REVISADO, PROCESADO, FALTA)

Una consulta simple para informar cuántos libros caen en cada estado es:

SELECT status, COUNT(*) FROM books GROUP BY status 

o para encontrar específicamente cuántos libros están disponibles:

 SELECT COUNT(*) FROM books WHERE status = "AVAILABLE" 

Sin embargo, una vez que la tabla crece a millones de filas, estas consultas tardan varios segundos en completarse. Agregar un índice a la columna “estado” no parece marcar una diferencia en mi experiencia.

Además de almacenar en caché periódicamente los resultados o actualizar explícitamente la información resumida en una tabla separada cada vez que un libro cambia de estado (mediante activadores o algún otro mecanismo), ¿hay alguna técnica para acelerar este tipo de consultas? Parece que las consultas COUNT terminan mirando cada fila y (sin saber más detalles) estoy un poco sorprendido de que esta información no pueda determinarse de alguna manera a partir del índice.

ACTUALIZAR

Usando la tabla de muestra (con una columna indexada de “estado”) con 2 millones de filas, comparé la consulta GROUP BY. Al usar el motor de almacenamiento InnoDB, la consulta tarda 3.0 – 3.2 segundos en mi máquina. Usando MyISAM, la consulta tarda de 0.9 a 1.1 segundos. No hubo diferencias significativas entre el recuento (*), el recuento (estado) o el recuento (1) en ninguno de los casos.

Es cierto que MyISAM es un poco más rápido, pero tenía curiosidad por ver si había una manera de hacer que una consulta equivalente se ejecutara mucho más rápido (por ejemplo, 10-50 ms, lo suficientemente rápido para ser invocado en cada solicitud de página web para un sitio de poco tráfico). sin la sobrecarga mental de almacenamiento en caché y desencadenadores. Parece que la respuesta es “no hay forma de ejecutar la consulta directa rápidamente”, que es lo que esperaba, solo quería asegurarme de no perderme una alternativa fácil.

Entonces la pregunta es

¿Hay alguna técnica para acelerar este tipo de consultas?

Bueno en realidad no. Un motor de almacenamiento basado en columnas probablemente sería más rápido con esas consultas SELECT COUNT (*) pero sería menos eficiente para casi cualquier otra consulta.

Su mejor opción es mantener una tabla de resumen mediante activadores. No tiene mucha sobrecarga y la parte SELECT será instantánea sin importar qué tan grande sea la mesa. Aquí hay un código repetitivo:

 DELIMITER // CREATE TRIGGER ai_books AFTER INSERT ON books FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status // CREATE TRIGGER ad_books AFTER DELETE ON books FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status; // CREATE TRIGGER au_books AFTER UPDATE ON books FOR EACH ROW BEGIN IF (OLD.status <> NEW.status) THEN UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status); END IF; END // 

MyISAM es bastante rápido con count (*). El inconveniente es que el almacenamiento de MyISAM no es tan confiable y es mejor evitarlo cuando la integridad de los datos es crítica.

InnoDB puede ser muy lento para realizar consultas de conteo (*), ya que está diseñado para permitir múltiples vistas simultáneas de los mismos datos. Entonces, en cualquier momento, no es suficiente ir al índice para obtener el conteo.

Desde: http://www.mail-archive.com/mysql@lists.mysql.com/msg120320.html

La base de datos comienza con 1000 registros en ella. Comienzo una transacción. Usted inicia una transacción. Borro 50 registros. Agrega 50 registros. Yo hago un COUNT ( ) y veo 950 registros. Hace un COUNT ( ) y ve 1050 registros. Confirmo mi transacción: la base de datos ahora tiene 950 registros para todos menos usted. Usted confirma su transacción: la base de datos tiene 1000 registros nuevamente.

La forma en que InnoDB se mantiene al día con qué registros son “visibles” o “modificables” con respecto a cualquier transacción es mediante el locking a nivel de fila, niveles de aislamiento de transacción y versiones múltiples. http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html

Eso es lo que hace que contar cuántos registros puede ver cada persona no sea tan directo.

Por lo tanto, lo más importante es que tendrá que ver el almacenamiento en caché de los conteos de alguna manera en lugar de ir a la mesa si necesita acceder a esta información con frecuencia y rapidez.

desde: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

InnoDB no mantiene un recuento interno de filas en una tabla. (En la práctica, esto sería algo complicado debido a las múltiples versiones.) Para procesar una instrucción SELECT COUNT (*) FROM t, InnoDB debe escanear un índice de la tabla, lo que lleva algún tiempo si el índice no está completamente en el búfer piscina.

La solución sugerida es:

Para obtener un recuento rápido, debe usar una tabla contraria que cree usted mismo y dejar que la aplicación la actualice de acuerdo con los insertos y la elimine. SHOW TABLE STATUS también se puede usar si un recuento aproximado de filas es suficiente.

En resumen: count (*) (en innoDB) tomará mucho tiempo para las tablas que contienen una gran cantidad de filas. Esto es por diseño y no puede ser ayudado.

Escribe tu propia solución.

Muchas respuestas aquí dicen que un índice no ayudaría, pero en mi caso sí …

Mi tabla usó MyISAM, y solo tenía unas 100k filas. La consulta:

 select count(*) from mytable where foreign_key_id=n 

tomó 7-8 segundos para completar.

foreign_key_id un índice en foreign_key_id :

 create index myindex on mytable (foreign_key_id) using btree; 

Después de crear el índice, la instrucción de selección anterior reportó un tiempo de ejecución de 0.00 segundos.

No hubo diferencias significativas entre el recuento (*), el recuento (estado) o el recuento (1)

recuento (columna) devuelve el número de líneas donde la columna NO ES NULA. Como 1 es NOT NULL, y el estado también es, presumiblemente, NOT NULL, la base de datos optimizará la prueba y los convertirá a todos en count (*). Lo cual, irónicamente, no significa “recuento de líneas donde todas las columnas no son nulas” (o cualquier otra combinación), solo significa “recuento de líneas” …

Ahora, volviendo a tu pregunta, no puedes tener tu pastel y comértelo …

  • Si quiere que haya un conteo “exacto” disponible en todo momento, entonces tiene que incrementar y disminuir en tiempo real, a través de disparadores, lo que ralentiza sus escrituras.

  • O puede usar conteo (*), pero esto será lento

  • O puede conformarse con una estimación aproximada, o un valor desactualizado, y usar el almacenamiento en caché u otros enfoques probabilísticos.

En general, en valores por encima de “unos pocos”, NADIE está interesado en un recuento exacto en tiempo real. De todos modos, es una pista falsa, ya que en el momento en que la lees, es muy probable que el valor haya cambiado.