Múltiples SELECT COUNT en una consulta MySQL. Optimización de consultas de MySQL Mysql consulta varias consultas en una

Ya he escrito sobre varios Consultas SQL, pero es hora de hablar de cosas más difíciles, por ejemplo, Consulta SQL para seleccionar registros de varias tablas.

Cuando tú y yo hicimos una selección de una mesa, todo fue muy simple:

SELECT nombres_de_campo_requeridos FROM nombre_tabla DONDE condición_seleccionada

Todo es muy simple y trivial, pero para muestreo de varias tablas a la vez se vuelve cada vez más complicado. Una de las dificultades es la coincidencia de nombres de campo. Por ejemplo, cada tabla tiene un campo identificación.

Consideremos una consulta como esta:

SELECCIONAR * FROM table_1, table_2 DONDE table_1.id> table_2.user_id

A muchos que no se han ocupado de este tipo de consultas les parecerá que todo es muy sencillo, pensando que aquí solo se han añadido los nombres de las tablas antes de los nombres de los campos. De hecho, esto evita contradicciones entre los mismos nombres campos. Sin embargo, la dificultad no radica en esto, sino en el algoritmo de funcionamiento de dicha consulta SQL.

El algoritmo de trabajo es el siguiente: el primer registro se toma de tabla 1... Tomado identificación esta entrada de tabla 1... Además, la mesa se ve completamente Tabla 2... Y todos los registros se agregan donde el valor del campo user_id menos identificación entrada seleccionada en tabla 1... Por lo tanto, después de la primera iteración, puede aparecer de 0 a infinito registros resultantes. En la siguiente iteración, se toma el siguiente registro de la tabla. tabla 1... Se vuelve a escanear toda la tabla Tabla 2 y la condición de recuperación se activa de nuevo. table_1.id> table_2.user_id... Todos los registros que cumplen esta condición se agregan al resultado. La salida puede resultar gran cantidad registros muchas veces más grandes que el tamaño total de ambas tablas.

Si comprende cómo funciona después de la primera vez, entonces es genial, y si no, lea hasta que lo comprenda completamente. Si comprende esto, será más fácil aún.

Anterior Consulta SQL como tal, rara vez se utiliza. Solo fue dado por explicaciones del algoritmo para obtener de varias tablas... Ahora veamos uno más fornido. Consulta SQL... Digamos que tenemos dos tablas: con mercancías (hay un campo owner_id responsable de identificación el propietario del producto) y con los usuarios (hay un campo identificación). Queremos uno Consulta SQL obtener todos los registros y que cada uno contenga información sobre el usuario y su único producto. La siguiente entrada contenía información sobre el mismo usuario y su próximo producto. Cuando este usuario se quede sin productos, vaya al siguiente usuario. Por lo tanto, tenemos que unir las dos tablas y obtener un resultado en el que cada registro contiene información sobre el usuario y sobre uno de sus productos.

Una consulta similar reemplazará 2 consultas SQL: para seleccionar por separado de la tabla con productos y de la tabla con usuarios. Además, dicha solicitud coincidirá inmediatamente con el usuario y su producto.

La consulta en sí es muy simple (si entendiste la anterior):

SELECCIONAR * DE usuarios, productos DONDE users.id = products.owner_id

El algoritmo aquí ya es simple: se toma el primer registro de la tabla usuarios... Entonces se toma identificación y se analizan todos los registros de la tabla productos, añadiendo al resultado aquellos para los que owner_id es igual a identificación de la mesa usuarios... Por lo tanto, en la primera iteración, todos los productos se recopilan del primer usuario. En la segunda iteración, todos los productos se recopilan del segundo usuario y así sucesivamente.

Como puedes ver Consultas SQL para seleccionar de varias tablas no las más sencillas, pero los beneficios de las mismas pueden ser enormes, por lo que es muy deseable conocer y poder utilizar este tipo de consultas.

9 de octubre de 2008 a las 11:37 p.m.

Optimización de consultas MySQL

• MySQL

En el trabajo diario, uno tiene que lidiar con el mismo tipo de errores al escribir consultas.

En este artículo me gustaría dar ejemplos de cómo NO escribir consultas.

• Obteniendo todos los campos
  SELECCIONAR * DE la tabla

  Al escribir consultas, no utilice una selección de todos los campos: "*". Enumere solo los campos que realmente necesita. Esto reducirá la cantidad de datos obtenidos y enviados. Además, no se olvide de cubrir los índices. Incluso si realmente necesita todos los campos de la tabla, es mejor enumerarlos. Primero, mejora la legibilidad del código. Cuando se usa un asterisco, es imposible averiguar qué campos hay en la tabla sin mirarlo. En segundo lugar, con el tiempo, la cantidad de columnas en su tabla puede cambiar, y si hoy hay cinco columnas INT, entonces en un mes se pueden agregar campos TEXT y BLOB, lo que ralentizará la selección.

• Consultas en bucle.
  Debe tener claro que SQL es un idioma establecido. A veces, los programadores que están acostumbrados a pensar en términos de lenguajes procedimentales encuentran difícil repensar su pensamiento en el lenguaje de conjuntos. Esto se puede hacer simplemente adoptando una regla simple: "nunca ejecute consultas en un bucle". Ejemplos de cómo se puede hacer esto:

  1. Muestras
  $ news_ids = get_list ("SELECCIONAR news_id FROM today_news");
  while ($ news_id = get_next ($ news_ids))
  $ news = get_row ("SELECCIONAR título, cuerpo DESDE noticias DONDE news_id =". $ news_id);

  La regla es muy simple: cuantas menos solicitudes, mejor (aunque hay excepciones a esto, como a cualquier regla). No se olvide de la construcción IN (). El código anterior se puede escribir en una consulta:
  SELECCIONAR título, cuerpo de today_news INNER JOIN news USING (news_id)

  2. Insertos
  $ log = parse_log ();
  while ($ registro = siguiente ($ registro))
  query ("INSERT INTO logs SET value =" (! LANG:. $ log ["value"]);!}

  Es mucho más eficiente pegar y ejecutar una consulta:
  INSERT INTO registros (valor) VALORES (...), (...)

  3. Actualizaciones
  A veces es necesario actualizar varias filas en la misma tabla. Si el valor actualizado es el mismo, entonces todo es simple:
  ACTUALIZAR noticias SET title = "(! LANG: prueba" WHERE id IN (1, 2, 3).!}

  Si el valor cambiado para cada registro es diferente, esto se puede hacer con la siguiente consulta:
  ACTUALIZAR SET de noticias
  title = CASE
  CUANDO news_id = 1 ENTONCES "aa"
  WHEN news_id = 2 THEN "bb" END
  DONDE news_id IN (1, 2)

  Nuestras pruebas muestran que tal solicitud es 2-3 veces más rápida que varias solicitudes separadas.

• Realización de operaciones en campos indexados
  SELECCIONE user_id FROM usuarios DONDE blogs_count * 2 = $ valor

  Esta consulta no utilizará el índice incluso si la columna blogs_count está indexada. No se deben realizar conversiones en el campo indexado en la consulta para que se utilice el índice. Para consultas similares, mueva las funciones de transformación a una parte diferente:
  SELECCIONE user_id FROM usuarios DONDE blogs_count = $ valor / 2;

  Un ejemplo similar:
  SELECT user_id FROM users WHERE TO_DAYS (CURRENT_DATE) - TO_DAYS (registrado)<= 10;

  No usará un índice sobre el campo registrado, mientras que
  SELECCIONE user_id FROM usuarios DONDE están registrados> = DATE_SUB (CURRENT_DATE, INTERVAL 10 DAY);
  será.

• Obteniendo filas solo para contar su número
  $ resultado = mysql_query ("SELECCIONAR * DESDE la tabla", $ enlace);
  $ num_rows = mysql_num_rows ($ resultado);
  Si necesita seleccionar el número de filas que satisfacen una condición específica, utilice la consulta de tabla SELECT COUNT (*) FROM, en lugar de seleccionar todas las filas solo para contar el número de filas.
• Obteniendo filas adicionales
  $ resultado = mysql_query ("SELECT * FROM table1", $ link);
  while ($ fila = mysql_fetch_assoc ($ resultado) && $ i< 20) {
  …
  }
  Si solo necesita n filas de muestra, use LIMIT en lugar de descartar filas adicionales en su aplicación.
• Usando ORDER BY RAND ()
  SELECT * FROM table ORDER BY RAND () LIMIT 1;

  Si la tabla tiene más de 4-5 mil filas, ORDER BY RAND () funcionará muy lentamente. Sería mucho más eficiente ejecutar dos consultas:

  Si la tabla auto_increment tiene una clave primaria "nueva" y no hay espacios:
  $ rnd = rand (1, query ("SELECT MAX (id) FROM table"));
  $ fila = consulta ("SELECCIONAR * DE la tabla DONDE id =". $ rnd);

  O:
  $ cnt = query ("SELECT COUNT (*) FROM table");
  $ fila = consulta ("SELECT * FROM table LIMIT". $ cnt. ", 1");
  que, sin embargo, también puede ser lento con una gran cantidad de filas en la tabla.

• Usando una gran cantidad de JOIN
  SELECCIONE
  v.video_id
  un nombre,
  g. género
  DESDE
  videos AS v
  UNIRSE A LA IZQUIERDA
  link_actors_videos AS la ON la.video_id = v.video_id
  UNIRSE A LA IZQUIERDA
  actores COMO EN a.actor_id = la.actor_id
  UNIRSE A LA IZQUIERDA
  link_genre_video AS lg ON lg.video_id = v.video_id
  UNIRSE A LA IZQUIERDA
  géneros AS g ON g.genre_id = lg.genre_id

  Debe recordarse que al vincular tablas de uno a varios, el número de filas de la selección aumentará con cada JOIN sucesivo. En tales casos, es más rápido dividir una consulta de este tipo en varias simples.

• Usando LIMIT
  SELECT ... FROM table LIMIT $ start, $ per_page

  Mucha gente piensa que una consulta de este tipo devolverá registros de $ per_page (generalmente 10-20) y, por lo tanto, funcionará rápidamente. Funcionará rápidamente durante las primeras páginas. Pero si el número de registros es grande y necesita ejecutar una consulta SELECT ... FROM table LIMIT 1000000, 1000020, para ejecutar dicha consulta, MySQL primero seleccionará 1000020 registros, descartará el primer millón y devolverá 20. Es posible que no ser rápido en absoluto. No hay formas triviales de resolver el problema. Mucha gente simplemente limita el número de páginas disponibles a un número razonable. También puede acelerar dichas consultas utilizando índices de cobertura o soluciones de terceros (como Sphinx).

• No se usa ON DUPLICATE KEY UPDATE
  $ fila = consulta ("SELECCIONAR * DE la tabla DONDE id = 1");

  Si ($ fila)
  consulta ("ACTUALIZAR tabla SET columna = columna + 1 DONDE id = 1")
  demás
  query ("INSERT INTO table SET column = 1, id = 1");

  Una construcción similar se puede reemplazar con una consulta, siempre que haya una clave principal o única para el campo de identificación:
  INSERT INTO table SET column = 1, id = 1 ON DUPLICATE KEY UPDATE column = column + 1

Leer

Este breve artículo se centrará en bases de datos en particular MySQL, muestreo y conteo. Al trabajar con bases de datos, a menudo se requiere contar el número de COUNT () filas con o sin una determinada condición, esto es extremadamente simple de hacer con la siguiente consulta

Ver código MYSQL

La consulta devolverá un valor con el número de filas de la tabla.

Conteo condicional

Ver código MYSQL

La consulta devolverá un valor con el número de filas en la tabla que satisfaga esta condición: var = 1

Para obtener varios valores de recuento de filas con diferentes condiciones, puede ejecutar varias consultas una por una, por ejemplo

Ver código MYSQL

Pero en algunos casos, este enfoque no es práctico ni óptimo. Por tanto, resulta relevante organizar una consulta, con varias subconsultas, para obtener varios resultados en una consulta a la vez. por ejemplo

Ver código MYSQL

Por lo tanto, habiendo ejecutado solo una consulta a la base de datos, obtenemos un resultado con un recuento del número de filas para varias condiciones, que contiene varios valores de recuento, por ejemplo

Ver código TEXT

c1 | c2 | c3 -------- 1 | 5 | 8

La desventaja de utilizar subconsultas, en comparación con varias consultas independientes, es la velocidad de ejecución y la carga en la base de datos.

El siguiente ejemplo de una consulta que contiene varios COUNT en una Consulta MySQL, está construido de manera un poco diferente, usa las construcciones IF (condición, valor1, valor2), así como la suma SUM (). Le permiten seleccionar datos de acuerdo con criterios específicos dentro de una sola consulta, luego resumirlos y mostrar varios valores como resultado.

Ver código MYSQL

Como puede ver en la solicitud, está construida de manera bastante sucinta, pero la velocidad de su ejecución tampoco fue feliz, el resultado de esta solicitud será el siguiente,

Ver código TEXT

total | c1 | c2 | c3 -------------- 14 | 1 | 5 | 8

A continuación, daré estadísticas comparativas de la velocidad de ejecución de tres variantes de consultas, para una selección de varias COUNT (). Para probar la velocidad de ejecución de las consultas, se ejecutaron 1000 consultas de cada tipo, con una tabla que contiene más de tres mil registros. En este caso, cada vez que la consulta contenía SQL_NO_CACHE para deshabilitar el almacenamiento en caché de los resultados por parte de la base de datos.

Velocidad de ejecución
Tres solicitudes separadas: 0,9 segundos
Una consulta con subconsultas: 0,95 segundos
Una consulta con construcción IF y SUM: 1,5 segundos

Conclusión. Y así, tenemos varias opciones para crear consultas en la base de datos. Datos MySQL con múltiples COUNT (), la primera opción con consultas separadas no es muy conveniente, pero tiene el mejor resultado en términos de velocidad. La segunda opción con subconsultas es algo más conveniente, pero su velocidad de ejecución es un poco más lenta. Y finalmente, la tercera versión lacónica de la consulta con las construcciones IF y SUM, que parece ser la más conveniente, tiene la velocidad de ejecución más lenta, que es casi dos veces menor que las dos primeras opciones. Por lo tanto, al optimizar el funcionamiento de la base de datos, recomiendo usar la segunda versión de la consulta que contiene subconsultas con COUNT (), en primer lugar, su velocidad de ejecución es cercana al resultado más rápido, y en segundo lugar, dicha organización dentro de una consulta es bastante conveniente .

En la última lección, nos encontramos con un inconveniente. Cuando quisimos saber quién creó el tema "bicicletas" e hizo una solicitud:

En lugar del nombre del autor, recibimos su identificador. Esto es comprensible, porque hicimos una consulta a una tabla - Temas, y los nombres de los autores de los temas se almacenan en otra tabla - Usuarios. Por lo tanto, habiendo averiguado el identificador del autor del tema, necesitamos hacer una consulta más, a la tabla Usuarios para averiguar su nombre:

SQL proporciona la capacidad de combinar dichas consultas en una convirtiendo una de ellas en una subconsulta (subconsulta). Entonces, para averiguar quién creó el tema de bicicletas, haremos la siguiente consulta:

Es decir, después de la palabra clave DONDE, escribimos otra solicitud en la condición. MySQL procesa la subconsulta primero, devuelve id_author = 2 y este valor se pasa a la cláusula DONDE solicitud externa.

Una consulta puede contener varias subconsultas, la sintaxis para dicha consulta es la siguiente: Tenga en cuenta que las subconsultas pueden seleccionar solo una columna, cuyos valores volverán a la consulta externa. Si intenta seleccionar varias columnas, se producirá un error.

Hagamos otra solicitud de consolidación, averigüe qué mensajes en el foro dejó el autor del tema "bicicletas":

Ahora compliquemos la tarea, averigüe en qué temas dejó mensajes el autor del tema "bicicletas":

Vamos a ver cómo funciona.

• MySQL ejecutará primero la consulta más profunda:


• El resultado resultante (id_author = 2) se pasará a una solicitud externa, que tomará la forma:


• El resultado resultante (id_topic: 4,1) se pasará a una solicitud externa, que tomará la forma:


• Y dará el resultado final (topic_name: sobre la pesca, sobre la pesca). Aquellos. el autor del tema "bicicletas" publicó mensajes en el hilo "Acerca de la pesca" creado por Sergey (id = 1) y en el hilo "Acerca de la pesca" creado por Sveta (id = 4).

Eso es todo lo que quería decir sobre las consultas anidadas. Aunque, hay dos puntos a los que prestar atención:

• No se recomienda crear consultas con un nivel de anidamiento de más de tres. Esto conduce a un aumento en el tiempo de ejecución y a la complejidad de la percepción del código.
• La sintaxis dada de consultas anidadas es bastante la más común, pero no la única. Por ejemplo, en lugar de solicitar
  
  escribir
  
  Aquellos. podemos usar cualquier operador usado con palabra clave DÓNDE (los estudiamos en la última lección).