script - optimize query mysql
Mysql cuenta el rendimiento en mesas muy grandes (6)
Finalmente, lo más rápido fue consultar las primeras X filas usando C # y contando el número de filas.
Mi aplicación está tratando los datos en lotes. La cantidad de tiempo entre dos lotes depende de la cantidad de filas que deben tratarse
SELECT pk FROM table WHERE fk = 1 LIMIT X
Obtuve el resultado en 0.9 segundos.
Gracias a todos por tus ideas!
Tengo una tabla con más de 100 millones de filas en Innodb.
Tengo que saber si hay más de 5000 filas donde la clave externa = 1. No necesito el número exacto.
Hice algunas pruebas:
SELECT COUNT(*) FROM table WHERE fk = 1 => 16 segundos
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 => 16 segundos
SELECT primary FROM table WHERE fk = 1 => 0.6 segundos
Tendré una mayor red y tiempo de tratamiento, ¡pero puede ser una sobrecarga de 15.4 segundos!
Tienes una mejor idea ?
Gracias
Edición: [Se agregaron los comentarios relevantes de OP]
Intenté SELECT SQL_NO_CACHE COUNT (fk) FROM tabla WHERE fk = 1 pero me llevó 25 segundos
Mysql fue sintonizado para Innodb con Mysql Tuner.
CREATE TABLE table ( pk bigint(20) NOT NULL AUTO_INCREMENT,
fk tinyint(3) unsigned DEFAULT ''0'',
PRIMARY KEY (pk), KEY idx_fk (fk) USING BTREE )
ENGINE=InnoDB AUTO_INCREMENT=100380914 DEFAULT CHARSET=latin1
Cosas de DB:
''have_innodb'', ''YES'' ''ignore_builtin_innodb'', ''OFF'' ''innodb_adaptive_hash_index'', ''ON''
''innodb_additional_mem_pool_size'', ''20971520'' ''innodb_autoextend_increment'', ''8''
''innodb_autoinc_lock_mode'', ''1'' ''innodb_buffer_pool_size'', ''25769803776''
''innodb_checksums'', ''ON'' ''innodb_commit_concurrency'', ''0'',
''innodb_concurrency_tickets'', ''500'' ''innodb_data_file_path'',
''ibdata1:10M:autoextend'' ''innodb_data_home_dir'', '''', ''innodb_doublewrite'', ''ON''
''innodb_fast_shutdown'', ''1'' ''innodb_file_io_threads'', ''4''
''innodb_file_per_table'', ''OFF'', ''innodb_flush_log_at_trx_commit'', ''1''
''innodb_flush_method'', '''' ''innodb_force_recovery'', ''0'' ''innodb_lock_wait_timeout'', ''50''
''innodb_locks_unsafe_for_binlog'', ''OFF'' ''innodb_log_buffer_size'', ''8388608''
''innodb_log_file_size'', ''26214400'' ''innodb_log_files_in_group'', ''2''
''innodb_log_group_home_dir'', ''./'' ''innodb_max_dirty_pages_pct'', ''90''
''innodb_max_purge_lag'', ''0'' ''innodb_mirrored_log_groups'', ''1'' ''innodb_open_files'',
''300'' ''innodb_rollback_on_timeout'', ''OFF'' ''innodb_stats_on_metadata'', ''ON''
''innodb_support_xa'', ''ON'' ''innodb_sync_spin_loops'', ''20'' ''innodb_table_locks'', ''ON''
''innodb_thread_concurrency'', ''8'' ''innodb_thread_sleep_delay'', ''10000''
''innodb_use_legacy_cardinality_algorithm'', ''ON''
Actualización ''15: utilicé el mismo método hasta ahora con 600 millones de filas y 640 000 nuevas filas por día. Sigue funcionando bien.
Las tablas de contador u otro mecanismo de almacenamiento en caché es la solución:
InnoDB no mantiene un recuento interno de filas en una tabla porque las transacciones simultáneas pueden "ver" diferentes números de filas al mismo tiempo. Para procesar una instrucción SELECT COUNT (*) FROM t, InnoDB escanea un índice de la tabla, lo cual toma algún tiempo si el índice no está completamente en el grupo de búferes. Si su tabla no cambia con frecuencia, usar la memoria caché de consultas de MySQL es una buena solución. Para obtener un conteo rápido, debe usar una tabla de contador que usted mismo cree y dejar que su aplicación la actualice de acuerdo con las inserciones y eliminaciones que haga. Si un recuento aproximado de filas es suficiente, se puede utilizar MOSTRAR ESTADO DE LA TABLA. Consulte la Sección 14.3.14.1, “Consejos de ajuste de rendimiento de InnoDB” .
No parece estar interesado en el conteo real, así que inténtalo:
SELECT 1 FROM table WHERE fk = 1 LIMIT 5000, 1
Si se devuelve una fila, tienes 5000 y más registros. Supongo que la columna fk está indexada.
Si está utilizando PHP, podría hacer mysql_num_rows en el resultado que obtuvo de SELECT primary FROM table WHERE fk = 1 => 0.6 seconds , creo que será eficiente.
Pero depende del idioma del servidor que estés usando
Si no está interesado en saber el número de filas y solo desea probar el COUNT con algún valor, puede usar el siguiente script estándar:
SELECT ''X''
FROM mytable
WHERE myfield=''A''
HAVING COUNT(*) >5
Esto devolverá una sola fila o ninguna fila, dependiendo de si se cumple la condición.
Este script cumple con ANSI y se puede ejecutar completamente sin evaluar el valor completo de COUNT (*). Si MySQL implementó la optimización para dejar de evaluar filas después de que se cumpla alguna condición (realmente espero que así sea), entonces obtendrás una mejora en el rendimiento. Desafortunadamente, no puedo probar este comportamiento porque no tengo una gran base de datos MySQL disponible. Si haces esta prueba, comparte el resultado aquí :)
Tengo que agregar otra respuesta: tengo muchas correcciones / adiciones a los comentarios y respuestas hasta ahora.
Para MyISAM, SELECT COUNT(*) sin WHERE está calculado como muerto, muy rápido. Todas las demás situaciones (incluido el InnoDB en la pregunta) deben contar a través del BTree de datos o del BTree de un índice para obtener la respuesta. Así que tenemos que ver cuánto contar.
InnoDB almacena en caché datos y bloques de índice (16KB cada uno). Pero cuando los datos de la tabla o el índice BTree son más grandes que innodb_buffer_pool_size , se garantiza que golpearán el disco. Golpear el disco es casi siempre la parte más lenta de cualquier SQL.
El caché de consultas, cuando está involucrado, generalmente resulta en tiempos de consulta de aproximadamente 1 milisegundo; Esto no parece ser un problema con ninguno de los tiempos citados. Así que no voy a detenerme en ello.
Pero ... Ejecutar la misma consulta dos veces seguidas a menudo mostrará:
- Primera ejecución: 10 segundos
- Segunda carrera: 1 segundo
Esto es sintomático de que la primera ejecución tenga que buscar la mayoría de los bloques del disco, mientras que la segunda lo encontró todo en la RAM (la buffer_pool). Sospecho que algunos de los tiempos enumerados son falsos porque no me doy cuenta de este problema de almacenamiento en caché. (Esto puede explicar 16 segundos frente a 0,6 segundos).
Voy a insistir en "hits de disco" o "bloques necesarios para ser tocados" como la métrica real de que SQL es más rápido.
COUNT(x) comprueba x para IS NOT NULL antes de realizar el recuento. Esto agrega una pequeña cantidad de procesamiento, pero no cambia la cantidad de visitas al disco.
La tabla ofrecida tiene un PK y una segunda columna. Me pregunto si esa es la mesa real ? Hace una diferencia
- Si el Optimizer decide leer los datos , es decir, escanear en orden de
PRIMARY KEY, leerá los datos de BTree, que normalmente (pero no en este cojo) es mucho más amplio que el índice secundario BTrees. - Si el Optimizador decide leer un índice secundario (pero no necesita hacer una clasificación), habrá menos bloques para tocar. Por lo tanto, más rápido.
Comentarios sobre las consultas originales:
SELECT COUNT(*) FROM table WHERE fk = 1 => 16 seconds
-- INDEX(fk) is optimal, but see below
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 => 16 seconds
-- the LIMIT does nothing, since there is only one row in the result
SELECT primary FROM table WHERE fk = 1 => 0.6 seconds
-- Again INDEX(fk), but see below
WHERE fk = 1 pide INDEX(fk, ...) , preferiblemente solo INDEX(fk) . Tenga en cuenta que en InnoDB, cada índice secundario contiene una copia del pk. Es decir, INDEX(fk) es efectivamente INDEX(fk, primary) . Por lo tanto, la tercera consulta puede usar eso como "cobertura" y no necesita tocar los datos.
Si la tabla es realmente solo las dos columnas, entonces probablemente el índice secundario BTree será más grueso que los datos BTree. Pero en tablas realistas, el índice secundario será más pequeño. Por lo tanto, un escaneo de índice será más rápido (menos bloques para tocar) que un escaneo de tabla.
La tercera consulta también está entregando un gran conjunto de resultados; esto podría provocar que la consulta tarde mucho tiempo, pero no se incluirá en el "tiempo" indicado; Es tiempo de red, no tiempo de consulta.
innodb_buffer_pool_size = 25,769,803,776 Supongo que la tabla y su índice secundario (de la FK) son cada uno de aproximadamente 3-4GB. Por lo tanto, cualquier momento puede tener que cargar muchas cosas. Entonces una segunda ejecución sería completamente almacenada en caché. (Por supuesto, no sé cuántas filas tienen fk=1 , presumiblemente menos que todas las filas?)
Pero ... En las 600M filas, la tabla y su índice se acercan cada uno al búfer de 25 GB. Entonces, llegará el día en que se convierta en E / S: esto hará que desee volver a 16 (o 25) segundos; sin embargo, no podrás. Entonces podemos hablar de alternativas para hacer el COUNT .
SELECT 1 FROM tbl WHERE fk = 1 LIMIT 5000,1 - Analicemos esto. Explorará el índice, pero se detendrá después de 5000 filas. De todo lo que necesitas es "más de 5K", esa es la mejor manera de conseguirlo. Será consistentemente rápido (solo tocará una docena de bloques), independientemente del número total de filas en la tabla. (Todavía está sujeto a las características de buffer_pool_size y caché del sistema. Pero una docena de bloques toma mucho menos de un segundo, incluso con un caché frío).
Puede que valga la pena examinar LIMIT ROWS_EXAMINED MariaDB. Sin eso, podrías hacer
SELECT COUNT(*) AS count_if_less_than_5K
FROM ( SELECT 1 FROM tbl WHERE fk = 1 LIMIT 5000 );
Puede ser más rápido que entregar las filas al cliente; Tendrá que recolectar las filas internamente en una tabla tmp, pero entregar solo el COUNT .
Una nota al margen: 640K filas insertadas por día: esto se aproxima al límite para INSERTs sola fila en MySQL con su configuración actual en un HDD (no SDD). Si necesita discutir el posible desastre, abra otra pregunta.
Línea de fondo:
- Asegúrese de evitar el caché de consulta. (usando
SQL_NO_CACHEoSQL_NO_CACHEcalidad) - Ejecuta cualquier consulta de tiempo dos veces; usar la segunda vez
- Comprender la estructura y el tamaño de los BTree (s) involucrados.
- No use
COUNT(x)menos que necesite la comprobación nula. - No use la interfaz
mysql_*PHP; cambiar amysqli_*oPDO.