Optimización de MySQL: índices, consultas lentas, traducción de configuración. Optimización de consultas de bases de datos MySQL
Trabajar con una base de datos suele ser el punto más débil en el rendimiento de muchas aplicaciones web. Y no son sólo los DBA los que tienen que preocuparse por esto. Los programadores deben elegir la estructura de tabla correcta, escribir consultas optimizadas y escribir buen código. Los siguientes son métodos para optimizar el trabajo con MySQL para programadores.
1. Optimice las consultas para la caché de consultas
La mayoría de los servidores MySQL tienen habilitado el almacenamiento en caché de consultas. Una de las mejores formas de mejorar el rendimiento es simplemente proporcionar almacenamiento en caché a la propia base de datos. Cuando una consulta se repite muchas veces, su resultado se extrae del caché, lo que es mucho más rápido que acceder directamente a la base de datos. El principal problema es que mucha gente simplemente utiliza consultas que no se pueden almacenar en caché:
// la solicitud no se almacenará en caché$r = consulta_mysql( "SELECCIONE el nombre de usuario DEL usuario DONDE signup_date >= CURDATE()"); // ¡y así será! $hoy = fecha("A-m-d" ); $r = consulta_mysql();"SELECCIONE el nombre de usuario DEL usuario DONDE signup_date >= "$today""
La razón es que la primera consulta utiliza la función CURDATE(). Esto se aplica a todas las funciones como NOW(), RAND() y otras cuyo resultado no es determinista. Si el resultado de una función puede cambiar, entonces MySQL no almacena en caché dicha consulta. En este ejemplo, esto se puede evitar calculando la fecha antes de ejecutar la consulta.
2. Utilice EXPLAIN para sus consultas SELECT// crear una declaración preparada si ($stmt = $mysqli ->preparar()) { "¿SELECCIONAR nombre de usuario DEL usuario DONDE estado =?"// vincular valores $stmt ->bind_param("s", $estado);// ejecutar $stmt ->ejecutar(); // vincular el resultado$stmt ->bind_result($nombre de usuario);//obtener datos
$stmt ->fetch();
printf("%s es de %s\n", $nombre de usuario, $estado);
"mysql_unbuffered_query() envía una consulta SQL a MySQL sin recuperar o almacenar automáticamente en el buffer las filas de resultados como lo hace mysql_query(). Por un lado, esto ahorra una cantidad significativa de memoria para consultas SQL que producen grandes conjuntos de resultados. Por otro lado, puede comenzar a trabajar en el conjunto de resultados de división después de que se haya recuperado la primera fila: no tiene que esperar a que se ejecute la consulta SQL completa".
Sin embargo, existen ciertas restricciones. Tendrá que leer todos los registros o llamar a mysql_free_result() antes de poder ejecutar otra consulta. Además, no puede utilizar mysql_num_rows() o mysql_data_seek() en el resultado de una función.
14. Almacenar IP en INT SIN FIRMAR
Muchos programadores almacenan direcciones IP en un campo de tipo VARCHAR(15), sin saber que se puede almacenar en forma de número entero. INT ocupa 4 bytes y tiene un tamaño de campo fijo.
Asegúrese de utilizar UNSIGNED INT porque La IP se puede escribir como un número sin signo de 32 bits.
Utilice INET_ATON() en su solicitud para convertir una dirección IP en un número e INET_NTOA() para volver a convertirla. Las mismas funciones existen en PHP: ip2long() y long2ip() (en PHP, estas funciones también pueden devolver valores negativos. Nota de The_Lion).
15. Las tablas de tamaño fijo (estáticas) son más rápidas
Si cada columna de una tabla tiene un tamaño fijo, entonces la tabla se denomina "estática" o "tamaño fijo". Ejemplo de columnas de longitud no fija: VARCHAR, TEXT, BLOB. Si incluye un campo de este tipo en una tabla, ya no será reparado y MySQL lo procesará de manera diferente.
El uso de este tipo de tablas aumentará la eficiencia, porque... MySQL puede buscar registros en ellos más rápido. Cuando necesite seleccionar la fila de la tabla deseada, MySQL puede calcular muy rápidamente su posición. Si el tamaño del registro no es fijo, se busca por índice.
Estas tablas también son más fáciles de almacenar en caché y restaurar después de una falla de la base de datos. Por ejemplo, si convierte VARCHAR(20) a CHAR(20), la entrada ocupará 20 bytes, independientemente de su contenido real.
Con el método de "división vertical", puede mover columnas con longitudes de fila variables a una tabla separada.
16. Separación vertical
La partición vertical se refiere a dividir una tabla en columnas para mejorar el rendimiento.
Ejemplo 1. Si las direcciones se almacenan en la tabla de usuarios, entonces no es un hecho que las necesitará con mucha frecuencia. Puede dividir la tabla y almacenar las direcciones en una tabla separada. Por lo tanto, la tabla de usuarios se reducirá de tamaño. La productividad aumentará.
Ejemplo 2: tiene un campo "last_login" en una tabla. Se actualiza cada vez que el usuario inicia sesión en el sitio. Pero todos los cambios en la tabla borran su caché. Al almacenar este campo en otra tabla, mantendrá al mínimo los cambios en la tabla de usuarios.
Pero si utiliza constantemente combinaciones en estas tablas, el rendimiento será deficiente.
17. Separe las consultas grandes DELETE e INSERT
Si necesita realizar una solicitud grande para eliminar o insertar datos, debe tener cuidado de no dañar la aplicación. La ejecución de una consulta grande puede bloquear la tabla y provocar un mal funcionamiento de toda la aplicación.
Apache puede ejecutar múltiples procesos paralelos al mismo tiempo. Por lo tanto, funciona de manera más eficiente si los scripts se ejecutan lo más rápido posible.
Si bloquea las tablas durante un largo período de tiempo (por ejemplo, 30 segundos o más), con un alto tráfico del sitio, puede surgir una gran cola de procesos y solicitudes, lo que puede provocar un funcionamiento lento del sitio o incluso una falla del servidor. .
Si tiene consultas como esta, use LIMIT para ejecutarlas en pequeñas ráfagas.
dormir(50000); )
18. Las columnas pequeñas son más rápidas
Para una base de datos, trabajar con el disco duro es quizás el punto más débil. Los discos pequeños y compactos suelen ser mejores en términos de rendimiento porque... reducir el trabajo del disco.
La documentación de MySQL tiene una lista de requisitos de almacenamiento de datos para todos los tipos de datos.
Si su tabla almacenará algunas filas, entonces no tiene sentido convertir la clave principal en un tipo INT; podría ser mejor convertirla en MEDIUMINT, SMALLINT o incluso TINYINT. Si no necesita almacenar la hora, use DATE en lugar de DATETIME.
Sin embargo, ten cuidado de que las cosas no salgan como Slashdot.
19. Elija el tipo de mesa correcto
20. Utilice ORM
21. Cuidado con las conexiones persistentes
mysql_pconnect() en PHP
Pero esto sólo suena bien en teoría. Desde mi experiencia personal (y la de otros), el uso de esta función no está justificado. Tendrás serios problemas con los límites de conexión, límites de memoria, etc.
Apache crea muchos hilos paralelos. Esta es la razón principal por la que las conexiones persistentes no funcionan tan bien como nos gustaría. Antes de usar mysql_pconnect(), consulte con el administrador del sistema.
El grado de carga en el servidor y, por tanto, la velocidad de carga del sitio, depende en gran medida de qué tan bien estén optimizadas las consultas a la base de datos MySQL. La optimización de las consultas MySQL ayudará a aliviar la carga del servidor y reducirá el tiempo de carga de su sitio web.
¿Por qué optimizar las consultas de bases de datos?
Los propietarios de sitios web gestionados por sistemas de administración escritos por ellos mismos simplemente deben comprender bien qué consultas a la base de datos se realizan de forma rápida y sencilla, y cuáles aumentan considerablemente la carga en el servidor y reducen significativamente la velocidad de carga del sitio.
Llegar al meollo del asunto no hará daño a aquellos webmasters que utilizan sistemas de administración conocidos y les gusta conectar todo tipo de complementos de terceros, así como personalizar temas por sí mismos, por ejemplo, en el CMS gratuito más popular: WordPress. .
Algunas acciones se pueden realizar de diferentes maneras, por ejemplo, puede contar el número de registros encontrados en una tabla usando la función mysql_num_rows (pero esto no se recomienda), o también puede usar la construcción SELECT COUNT(). Personalmente realizamos un estudio en el que creamos una enorme tabla de datos que contenía varios cientos de miles de registros y pesaba más de un gigabyte, y luego intentamos contar el número de filas utilizando los métodos especificados.
El resultado fue visible a simple vista, porque en el caso de usar mysql_num_rows, la página se congeló durante 5 segundos, después de lo cual se mostró el resultado. En el segundo caso, recibimos el resultado en forma de número de registros en la tabla casi instantáneamente. Ni siquiera tuvimos que medir el tiempo de carga del guión mediante un microtemporizador, porque el resultado era más que evidente.
Lo mismo se aplica a otros diseños. Algunas operaciones de la base de datos se pueden realizar de dos, tres, cuatro o más formas, y cada una de ellas diferirá en velocidad, mientras que el resultado en todos los casos será igualmente correcto.
Cómo optimizar las consultas de la base de datos
Para comprender exactamente cómo optimizar las consultas y qué construcciones funcionan más rápido y cuáles son más lentas, volveremos a realizar un pequeño experimento y lo haremos ahora mismo.
Tendremos que recurrir a la interfaz del popular y muy conveniente phpmyadmin en busca de ayuda. Para comenzar, debemos seleccionar una de las bases de datos disponibles y crear una tabla de prueba en ella. Su nombre en nuestro caso será bastante banal: prueba.
CREAR TABLA `prueba` (`ID` INT NOT NULL AUTO_INCREMENT, `TITLE` VARCHAR(100) CONJUNTO DE CARACTERES utf8 COLLATE utf8_unicode_ci NOT NULL, `ANUNCIO` CONJUNTO DE CARACTERES DE TEXTO utf8 COLLATE utf8_unicode_ci NOT NULL, `TEXT` SET DE TEXTO utf8 COLLATE utf8_unicode_ci NOT NULO, CLAVE PRIMARIA (`ID`)) MOTOR = MYISAM;
Ahora que ya tenemos una tabla de prueba, debemos llenarla con datos abstractos. Como puede verse en la estructura de la tabla que acabamos de crear, necesitaremos los siguientes datos para completar:
- Título
- Anuncio
- Texto completo
Para los textos resúmenes, por costumbre, recurriremos al servicio Yandex.Abstracts, creado especialmente para tales fines. Tuvimos la suerte de toparnos con el tema "El fotón de torsión en el siglo XXI", así que hagámoslo.
Indicamos un tema seleccionado al azar como título, tomamos un párrafo de texto mediocre como anuncio y como texto completo del artículo tendremos un texto de 4000 caracteres con espacios. Para contar el número de caracteres del texto utilizamos nuestro propio servicio y te recomendamos contarlo allí, porque existe la opción de tener en cuenta espacios o no.
No copiaremos la solicitud resultante aquí, porque serán más de 4000 caracteres de texto no único tomados del propio Yandex, lo cual es bastante atrevido y usted tampoco lo necesita. Es mejor escribir un bucle simple en PHP que agregue rápidamente tantos registros a la base de datos como queramos. Para empezar, serán 100.000 artículos.
Cuantas menos consultas a la base de datos, mejor
Ya en esta etapa le mostraremos un error común que ahora cometeremos nosotros mismos deliberadamente.
Por($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }
Como solicitud, pegamos el código copiado de phpmyadmin, que se mostró en la pantalla después de agregar manualmente el primer artículo una vez. Me gustaría señalar de inmediato que no debe generar consultas a la base de datos de esta manera. Hicimos esto solo porque necesitábamos llenar rápidamente la tabla con datos aleatorios, y esta consulta se escribe más rápido que la que es más óptima. En este ciclo terminamos con 99999 llamadas a bases de datos separadas (la primera la hicimos manualmente desde phpmyadmin), lo cual es de muy mala forma.
Una solución mucho más correcta sería hacer la misma operación usando una sola llamada a la base de datos, enumerando todas las líneas separadas por comas.
INSERTAR EN `prueba` (`ID`, `TÍTULO`, `ANUNCIO`, `TEXTO`) VALORES (NULL, "Título", "Anuncio", "Texto completo"), (NULL, "Título", "Anuncio" , "Texto completo"), (NULL, "Título", "Anuncio", "Texto completo"), ...
Si volvemos a nuestro primer método, se vería así:
INSERTAR EN `prueba` (`ID`, `TÍTULO`, `ANUNCIO`, `TEXTO`) VALORES (NULL, "Título", "Anuncio", "Texto completo") INSERTAR EN `prueba` (`ID`, ` TÍTULO`, `ANUNCIO`, `TEXTO`) VALORES (NULL, "Título", "Anuncio", "Texto completo") INSERTAR EN `prueba` (`ID`, `TÍTULO`, `ANUNCIO`, `TEXTO`) VALORES (NULL, "Título", "Anuncio", "Texto completo")...
¿Sientes la diferencia? La opción que utiliza un solo acceso a la base de datos es óptima. La velocidad de funcionamiento de estos dos métodos, que conducen al mismo resultado, difiere significativamente y es visible sin ninguna medición a simple vista, créanme, esto es realmente así.
Seleccione solo los campos requeridos por el script
Aquí todo es muy simple: esta o aquella función necesita ciertos datos de la tabla de destino. Muy a menudo resulta que es necesario eliminar todos los campos por completo, especialmente si la tabla es bastante grande y hay más de 10 de estos campos.
SELECCIONAR * DE `prueba`
En esta consulta, el asterisco significa que se recuperarán datos de todos los campos de la tabla de prueba. ¿Qué pasa si hay entre 20 y 30 o más de estos campos en la tabla? Lo más probable es que el script solo necesite algunos de ellos, y el resto, que no se utilizará de ninguna manera, se seleccionará en vano. Esta operación será más lenta que si especificara solo aquellos campos que realmente necesita en este momento, separados por comas.
SELECCIONE `ID`, `TÍTULO` DE `prueba`
En este ejemplo, no tocaremos en absoluto el anuncio ni el texto completo del artículo, lo que acelerará significativamente el guión. Por tanto, concluimos que optimizar las consultas a la base de datos también significa indicar específicamente los campos obligatorios en las consultas y abandonar la universalidad en forma de asterisco.
Combinando varias solicitudes en una
Ya hemos comentado que una buena optimización del trabajo con MySQL implica utilizar el mínimo número posible de consultas y dimos un ejemplo de cómo agregar datos a una tabla.
Además de sumar, esta técnica puede y debe usarse al muestrear datos. Ahora demos el ejemplo más simple. Imagine que tiene dos tablas en su base de datos: la primera tabla almacena información sobre los usuarios registrados y la segunda contiene artículos escritos por estos usuarios.
Digamos que necesita mostrar un artículo aleatorio en la pantalla y firmarlo con el nombre del autor en la parte inferior. La conexión entre las tablas en este caso es obvia y se produce por ID de usuario, es decir, la ID de usuario en la tabla de usuarios debe corresponder al campo USER_ID en la tabla de publicaciones. Esta conexión es estándar y debería ser comprensible para todos, sin excepción.
Entonces, para seleccionar un artículo aleatorio, escribe una consulta como esta:
$rs_post = mysql_query("SELECCIONE `ID`, `USER_ID`, `TÍTULO`, `TEXTO` DE `publicaciones` ORDEN por RAND() LÍMITE 1");
Se seleccionará un artículo al azar de la tabla de publicaciones. Después de lo cual nuestras acciones se verán así:
$row_post = mysql_fetch_assoc($rs_post); $ID de usuario = $row_post["USER_ID"];
Ahora la variable $userID contiene el ID del usuario que es el autor de este artículo y para obtener sus datos, por ejemplo NOMBRE (nombre) y APELLIDO (apellido), accederá a la tabla de usuarios y a la consulta. se verá así:
$rs_user = mysql_query("SELECCIONE `NOMBRE`, `APELLIDO` DE `usuarios` DONDE `ID` = "".$row_post["USER_ID"]."" LÍMITE 1");
Por cierto, no olvide incluir las variables en las solicitudes entre comillas simples, especialmente cuando los datos provienen del exterior, mediante GET o POST. Esto creará un obstáculo adicional para los atacantes y es una de las medidas destinadas a proteger contra las inyecciones de SQL. Así que volvamos a nuestro ejemplo. Una vez realizada la solicitud a la base de datos, todo es simple: obtenemos el nombre y apellido y los mostramos como firma del artículo. La tarea está completa.
Pero estas dos consultas se pueden optimizar en una sola. Para hacer esto, usaremos la construcción LEFT JOIN:
SELECCIONE `publicaciones`.`ID`, `publicaciones`.`USER_ID`, `publicaciones`.`TÍTULO`, `publicaciones`.`TEXTO`, `usuarios`.`NOMBRE`, `usuarios`.`APELLIDO` DE ` posts` LEFT JOIN `usuarios` ON `posts`.`USER_ID` = `usuarios`.`ID` ORDEN por RAND() LÍMITE 1
Como puedes ver, no hay nada complicado en el diseño anterior y todo es intuitivo. Lo único sobre lo que me gustaría llamar su atención es sobre la indicación explícita de tablas emparejadas con campos, ya que hay una selección de varias tablas a la vez. Si los nombres de algunos campos coinciden, entonces debe utilizar los llamados alias MySQL para no confundirse más adelante al mostrar el resultado.
Conclusión
Como puede ver, es posible y necesario optimizar las consultas a la base de datos. Si cree que como todo funciona tan rápido para usted, entonces no tiene sentido cambiar nada, espere hasta que la base de datos de su sitio crezca varias veces y con ella aumente el tráfico. El alto tráfico implica un acceso simultáneo más frecuente a la base de datos, cuyo tamaño también afecta la velocidad de las operaciones.
Una mala optimización de las consultas se puede descubrir un poco más tarde, cuando el sitio haya crecido lo suficiente, y con el tiempo será cada vez más difícil realizar cambios, porque el tamaño de los archivos y el número de funciones no hacen más que aumentar. Se están agregando nuevas funciones al sitio destinadas a la comodidad de los usuarios. En otras palabras, si las cosas llegan a un cierto punto de ebullición, es posible que no pueda encontrar ninguna solución y llevará varios días, o incluso semanas, optimizar todas las llamadas a la base de datos, dispersas en cientos de archivos. Por lo tanto, es mejor intentar hacerlo bien de inmediato, para no crearse problemas innecesarios en el futuro.
→ Optimización de consultas MySQL
mysql tiene una amplia gama de funciones para diversas clasificaciones ( ORDENAR POR), grupos ( Agrupar por), asociaciones ( UNIRSE A LA IZQUIERDA o ÚNETE DERECHO) etcétera. Todos ellos son ciertamente convenientes, pero en condiciones de solicitudes únicas. Por ejemplo, si personalmente necesita buscar algo en la base de datos utilizando un montón de tablas y enlaces, además de las funciones anteriores puede e incluso necesita usar operadores condicionales. SI. El principal error de los programadores novatos es el deseo de aplicar este tipo de consultas en el código de trabajo del sitio. En este caso, una consulta compleja es ciertamente hermosa, pero dañina. El caso es que ningún operador de clasificación, agrupación, unión o consulta anidada no se puede ejecutar en la RAM, sino que se utiliza el disco duro para crear tablas temporales. Y el disco duro, como sabes, es el cuello de botella del servidor.
Reglas para optimizar consultas mysql
1. Evite consultas anidadas
Este es el error más grave. El proceso principal siempre esperará a que se complete el proceso secundario y en este momento mantendrá una conexión a la base de datos, usará el disco y cargará iowait. Dos solicitudes paralelas a la base de datos y realizando el filtrado necesario en el intérprete del servidor ( perla, PHP, etc.) se ejecutará un orden de magnitud más rápido que el anidado.
Ejemplos en perl que no hacer:
Mi $algo = $dbh->prepare("SELECCIONE ID de elemento, NOMBRE de elemento, ID de grupo DESDE tbl DONDE ID de grupo EN (2,3,7)");
$algo->ejecutar();
while (my @row = $sth->fetchrow_array()) ( my $groupNAME = $dbh->selectrow_array("SELECT groupNAME FROM groups WHERE groupID = $row"); ### Digamos que necesitas recopilar los nombres de grupos ### y agréguelos al final de la matriz de datos push @row => $groupNAME ### Haz algo más... )
o en ningún caso así:
Mi $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(SELECT groupID FROM groups WHERE groupNAME = "Primero" OR groupNAME = "Segundo" OR groupNAME = "Séptimo")");
Si es necesario realizar tales acciones, en todos los casos es mejor utilizar un hash, una matriz o cualquier otra ruta de filtrado.
Un ejemplo en perl, como suelo hacer:
Mis %grupos;
my $sth = $dbh->prepare("SELECCIONE ID de grupo, NOMBRE DE grupo DE grupos DONDE ID de grupo EN (2,3,7)");
Mi $sth = $dbh->prepare("SELECCIONAR ID de elemento, NOMBRE de elemento DESDE tbl DONDE ID de grupo EN (2,3,7) ORDEN POR NOMBRE de elemento");
$algo->ejecutar();
while (mi @row = $sth->fetchrow_array()) ( print qq($row => $row); )
Un ejemplo en Perl de cómo suelo ordenar: Mi $lista = $dbh->selectall_arrayref("SELECT elementID,elementNAME FROM tbl DONDE groupID IN(2,3,7)"); foreach (ordenar ( $a-> cmp $b-> ) @$lista)( imprimir qq($_-> => $_->); )
Es mucho más rápido de esta manera. La diferencia es especialmente notable si hay muchos datos. En caso de que necesite ordenar por
perla
para varios campos, puede aplicar la clasificación de Schwartz. Si se requiere ordenación aleatoria ORDER BY RAND(): utilice la ordenación aleatoria en Perl.
3. Utilice índices
Si bien en algunos casos se puede abandonar la clasificación en la base de datos, es poco probable que WHERE sea posible. Por lo tanto, para los campos que se compararán, es necesario establecer índices. Son fáciles de hacer.
Con esta solicitud:
ALTERAR TABLA `any_db`.`any_tbl` AGREGAR ÍNDICE `text_index`(`text_fld`(255));
- Donde 255 es la longitud de la clave. Para algunos tipos de datos no es necesario. Consulte la documentación de MySQL para obtener más detalles.
En el trabajo diario, se encuentran errores bastante similares al escribir consultas.En este artículo me gustaría dar ejemplos de cómo NO escribir consultas.
- Seleccionar todos los campos
SELECCIONAR * DE la tablaAl escribir consultas, no utilice una selección de todos los campos - "*". Enumere sólo los campos que realmente necesita. Esto reducirá la cantidad de datos obtenidos y enviados. Además, no te olvides de cubrir los índices. Incluso si realmente necesita todos los campos de la tabla, es mejor enumerarlos. En primer lugar, mejora la legibilidad del código. Cuando se utiliza un asterisco, es imposible saber qué campos están 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, en un mes se pueden agregar los campos TEXTO y BLOB, lo que ralentizará la selección.
Solicitudes en un ciclo.
Debe comprender claramente que SQL es un lenguaje operativo de conjuntos. A veces, a los programadores que están acostumbrados a pensar en términos de lenguajes procedimentales les resulta difícil cambiar su pensamiento al lenguaje de conjuntos. Esto se puede hacer de manera bastante simple adoptando una regla simple: "nunca ejecute consultas en un bucle". Ejemplos de cómo se puede hacer esto:
1. MuestrasLa regla es muy simple: cuantas menos solicitudes, mejor (aunque hay excepciones, como cualquier regla). No te olvides de la construcción IN(). El código anterior se puede escribir en una consulta:
SELECCIONE título, cuerpo DE today_news INNER JOIN news USING(news_id)2. Inserciones
$registro = parse_log();
mientras($registro = siguiente($registro))
consulta("INSERT INTO logs SET value = ". $log["value"]);!}Es mucho más eficiente concatenar y ejecutar una consulta:
INSERTAR EN registros (valor) VALORES (...), (...)3. Actualizaciones
A veces es necesario actualizar varias filas en una tabla. Si el valor actualizado es el mismo, entonces todo es simple:
ACTUALIZAR noticias SET título="prueba" WHERE id IN (1, 2, 3).!}Si el valor que se cambia es diferente para cada registro, esto se puede hacer con la siguiente consulta:
ACTUALIZAR noticias CONJUNTO
título = CASO
CUANDO news_id = 1 ENTONCES "aa"
CUANDO news_id = 2 ENTONCES "bb" FINAL
DONDE news_id EN (1, 2)Nuestras pruebas muestran que una solicitud de este tipo es 2 o 3 veces más rápida que varias solicitudes separadas.
- Realizar operaciones en campos indexados
SELECCIONE user_id DE los usuarios DONDE blogs_count * 2 = $valorEsta consulta no utilizará el índice, incluso si la columna blogs_count está indexada. Para utilizar un índice, no se deben realizar transformaciones en el campo indexado de la consulta. Para tales solicitudes, mueva las funciones de conversión a otra parte:
SELECCIONE user_id DE los usuarios DONDE blogs_count = $valor / 2;Ejemplo similar:
SELECCIONE user_id DE los usuarios DONDE HASTA_DÍAS (CURRENT_DATE) - HASTA_DÍAS (registrado)<= 10;No utilizará un índice en el campo registrado, mientras que
SELECCIONE user_id DE los usuarios DONDE registrados >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
voluntad. - Obteniendo filas solo para contar su número
$resultado = mysql_query("SELECCIONAR * DE tabla", $enlace);
$num_rows = mysql_num_rows($resultado);
Si necesita seleccionar el número de filas que satisfacen una determinada condición, utilice la consulta SELECT COUNT(*) FROM table en lugar de seleccionar todas las filas solo para contar el número de filas. - Obteniendo filas adicionales
$resultado = mysql_query("SELECCIONAR * DE tabla1", $enlace);
while($fila = mysql_fetch_assoc($resultado) && $i< 20) {
…
}
Si solo necesita n recuperar filas, use LIMIT en lugar de descartar las filas adicionales en la aplicación. - Usando ORDEN POR ALEATORIO()
SELECCIONAR * DE la tabla ORDEN POR RAND() LÍMITE 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 tiene una clave primaria auto_increment y no hay espacios:
$rnd = rand(1, consulta("SELECT MAX(id) FROM tabla"));
$fila = consulta("SELECCIONAR * DE la tabla DONDE id = ".$rnd);O:
$cnt = consulta("SELECCIONAR CUENTA(*) DE la tabla");
$fila = consulta("SELECT * FROM table LIMIT ".$cnt.", 1");
lo cual, sin embargo, también puede ser lento si hay una gran cantidad de filas en la tabla. - Usar una gran cantidad de JOIN
SELECCIONAR
v.video_id
un.nombre,
g.género
DE
vídeos AS v
UNIRSE A LA IZQUIERDA
link_actors_videos AS la ON la.video_id = v.video_id
UNIRSE A LA IZQUIERDA
actores AS a ON 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_idDebe recordarse que al conectar tablas de uno a muchos, el número de filas en la selección aumentará con cada siguiente UNIÓN. Para tales casos, es más rápido dividir dicha consulta en varias simples.
- Usando LÍMITE
SELECCIONAR… DE la tabla LIMIT $inicio, $per_páginaMucha gente piensa que una consulta de este tipo devolverá $por_página de registros (normalmente entre 10 y 20) y, por tanto, funcionará rápidamente. Funcionará rápidamente durante las primeras páginas. Pero si la cantidad 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. Esto Puede que no sea nada rápido. No existen formas triviales de resolver el problema. Muchos simplemente limitan 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 (por ejemplo, Sphinx).
- No utilizar ON ACTUALIZACIÓN DE LLAVE DUPLICADA
$fila = consulta("SELECCIONAR * DE la tabla DONDE id=1");Si($fila)
consulta ("ACTUALIZAR tabla SET columna = columna + 1 DONDE id = 1")
demás
consulta("INSERT INTO table SET columna = 1, id=1");Una construcción similar se puede reemplazar con una consulta, siempre que exista una clave principal o única para el campo de identificación:
INSERTAR EN la tabla SET columna = 1, id = 1 EN ACTUALIZACIÓN DE CLAVE DUPLICADA columna = columna + 1
MySQL sigue siendo la base de datos relacional más popular del mundo, pero también la menos optimizada. Mucha gente se queda con la configuración predeterminada sin profundizar más. En este artículo veremos algunos consejos de optimización de MySQL combinados con algunas características nuevas que han aparecido hace relativamente poco tiempo.
Optimización de la configuración
Lo primero que todo usuario de MySQL debería hacer para mejorar el rendimiento es modificar la configuración. Sin embargo, la mayoría de la gente se salta este paso. En 5.7 (la versión actual), las configuraciones predeterminadas son mucho mejores que las de sus predecesores, pero aún es posible y fácil mejorarlas.
Esperamos que esté utilizando Linux o algo como Vagrant -box (como nuestro Homestead mejorado) y, en consecuencia, su archivo de configuración estará ubicado en /etc/mysql/my.cnf. Es posible que su instalación cargue un archivo de configuración adicional en este. Entonces mire, si el archivo my.cnf contiene poco, busque en /etc/mysql/mysql.conf.d/mysqld.cnf.
Sintonización manual
Las siguientes configuraciones deben realizarse desde el primer momento. De acuerdo con estos consejos, agregue al archivo de configuración en la sección:
Innodb_buffer_pool_size = 1G # (aquí cambia alrededor del 50%-70% de la RAM total) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # se puede cambiar a 2 o 0 innodb_flush_method = O_DIRECT
- innodb_buffer_pool_size. El grupo de búfer es una especie de "almacén" para almacenar en caché datos e índices en la memoria. Se utiliza para almacenar en la memoria datos a los que se accede con frecuencia. Y cuando se utiliza un servidor dedicado o virtual, donde la base de datos suele ser el cuello de botella, tiene sentido darle la mayor parte de la RAM. Por tanto, le damos entre el 50 y el 70% de la RAM total. Hay una guía para configurar este grupo en la documentación de MySQL.
- innodb_log_file_size . La configuración del tamaño del archivo de registro está bien descrita, pero en pocas palabras es la cantidad de datos almacenados en los registros antes de borrarlos. Tenga en cuenta que el registro en este caso no son registros de errores, sino una especie de instantánea delta de los cambios que aún no se han descargado al disco en los archivos principales de innodb. MySQL escribe en segundo plano, pero esto aún afecta el rendimiento al momento de escribir este artículo. Un archivo de registro más grande significa un mayor rendimiento debido a que se crean menos puntos de control nuevos y más pequeños, pero también un mayor tiempo de recuperación en caso de una falla (se deben escribir más datos en la base de datos).
- innodb_flush_log_at_trx_commit se describe y muestra lo que sucede con el archivo de registro. El valor 1 es el más seguro porque el registro se vacía en el disco después de cada transacción. Con los valores 0 y 2 se garantiza menos ACID, pero más rendimiento. La diferencia no es lo suficientemente grande como para compensar los beneficios de estabilidad en 1.
- innodb_flush_method. Para colmo, cuando se trata de vaciar datos, esta configuración debe establecerse en O_DIRECT, para evitar el doble almacenamiento en búfer. Le aconsejo que haga esto siempre mientras el sistema de E/S permanezca muy lento. Aunque en la mayoría de los sitios de hosting, como DigitalOcean, tendrás unidades SSD, por lo que el sistema de E/S será más productivo.
Existe una herramienta de Percona que nos ayudará a encontrar los problemas restantes de forma automática. Tenga en cuenta que si lo ejecutamos sin esta configuración manual, solo se definiría 1 de las 4 configuraciones, ya que las otras 3 dependen de las preferencias del usuario y del entorno de la aplicación.
Inspector de variables
Instalación del inspector de variables en Ubuntu:
Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all. deb sudo apt-get update sudo apt-get install percona-toolkit
Para otros sistemas, siga estas instrucciones.
Luego ejecute el kit de herramientas:
Pt-variable-advisor h=localhost,u=granja,p=secreto
Verás este resultado:
# WARN delay_key_write: los bloques de índice MyISAM nunca se vacían hasta que sea necesario. # NOTA max_binlog_size: max_binlog_size es más pequeño que el valor predeterminado de 1 GB. # NOTA sort_buffer_size-1: La variable sort_buffer_size generalmente debe dejarse en su valor predeterminado a menos que un experto determine que es necesario cambiarla. # NOTA innodb_data_file_path: Los archivos InnoDB que se extienden automáticamente pueden consumir una gran cantidad de espacio en disco que es muy difícil de recuperar más adelante. # WARN log_bin: el registro binario está deshabilitado, por lo que la recuperación y replicación en un momento dado no son posibles.
Nota traductor:
En mi máquina local, además de esto, también recibí la siguiente advertencia:
El hecho de que el parámetro innodb_flush_method deba establecerse en O_DIRECT y el motivo se analizó anteriormente. Y si siguió la secuencia de ajuste como en el artículo, no verá esta advertencia.
Ninguno de estos ( aprox.: indicado por el autor) las advertencias no son críticas, no es necesario corregirlas. Lo único que se puede corregir es configurar un registro binario para replicación e instantáneas.
Nota: en las nuevas versiones, el tamaño de binlog predeterminado es 1G y esta advertencia no aparecerá.
Max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "FILA"
- max_binlog_size. Determina el tamaño de los registros binarios. Registran sus transacciones y solicitudes y realizan puntos de control. Si una transacción excede el máximo, entonces el registro puede exceder su tamaño cuando se guarda en el disco; de lo contrario, MySQL lo admitirá dentro de este límite.
- log_bin. Esta opción habilita el registro binario en general. Sin él, las instantáneas o las replicaciones son imposibles. Tenga en cuenta que esto puede tener un gran impacto en su espacio en disco. server-id es una opción requerida al habilitar el registro binario, por lo que los registros "saben" de qué servidor provienen (para la replicación), y el formato binlog es simplemente la forma en que están escritos.
Como puede ver, el nuevo MySQL tiene valores predeterminados que están casi listos para producción. Por supuesto, cada aplicación es diferente y tiene trucos y ajustes adicionales que aplica.
Sintonizador MySQL
Herramientas de soporte: Percona Toolkit para identificar índices duplicados
El kit de herramientas Percona que instalamos anteriormente también tiene una herramienta para detectar índices duplicados, lo que puede ser útil cuando se utilizan CMS de terceros o simplemente para verificar si accidentalmente agregó más índices de los necesarios. Por ejemplo, la instalación predeterminada de WordPress tiene índices duplicados en la tabla wp_posts:
Pt-verificador-de-claves-duplicadas h=localhost,u=homestead,p=secret # ############################### ######################################### # homestead.wp_posts # #### ################################################## ################## # La clave type_status_date termina con un prefijo del índice agrupado # Definiciones de claves: # KEY `type_status_date` (`post_type`,`post_status`,`post_date` ,`ID`), # PRIMARY KEY (`ID`), # Tipos de columnas: # `post_type` varchar(20) intercalar utf8mb4_unicode_520_ci not null default "post" # `post_status` varchar(20) intercalar utf8mb4_unicode_520_ci not null default "publicar " " # `post_date` datetime not null default "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # Para acortar este índice agrupado duplicado, ejecute: ALTER TABLE `homestead`. `wp_posts` DROP INDEX `type_status_date`, AGREGAR ÍNDICE `type_status_date` (`post_type`,`post_status`,`post_date`);
Como puede ver en la última línea, esta herramienta también le brinda consejos sobre cómo deshacerse de índices duplicados.
Herramientas auxiliares: Percona Toolkit para índices no utilizados
Percona Toolkit también puede detectar índices no utilizados. Si está registrando consultas lentas (consulte la sección de cuellos de botella a continuación), puede ejecutar la utilidad y comprobará si esas consultas utilizan índices en las tablas y cómo.
Uso del índice pt /var/log/mysql/mysql-slow.log
Para obtener información detallada sobre el uso de esta utilidad, consulte .
Cuellos de botella
Esta sección describe cómo detectar y monitorear cuellos de botella en la base de datos.
Primero, habilitemos el registro de consultas lentas:
Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 consultas de registro que no utilizan índices = 1
Las líneas anteriores deben agregarse a la configuración de mysql. La base de datos realizará un seguimiento de las consultas que tardaron más de 1 segundo en completarse y aquellas que no utilizan índices.
Una vez que haya algunos datos en este registro, puede analizarlos para determinar el uso del índice usando la utilidad pt-index-usage anterior o usando pt-query-digest, que producirá resultados similares a estos:
Pt-query-digest /var/log/mysql/mysql-slow.log # 360 ms de tiempo de usuario, 20 ms de tiempo del sistema, 24,66 millones de rss, 92,02 millones de vsz # Fecha actual: jueves 13 de febrero 22:39:29 2014 # Nombre de host: * # Archivos: mysql-slow.log # Total: 8 en total, 6 únicos, 1,14 QPS, concurrencia 0,00x ________________ # Rango de tiempo: 2014-02-13 22:23:52 a 22:23:59 # Atributo total mínimo máximo promedio 95% de mediana de desviación estándar # ============ ======= ======= ======= ======= ===== == ======= ======= # Tiempo de ejecución 3ms 267us 406us 343us 403us 39us 348us # Tiempo de bloqueo 827us 88us 125us 103us 119us 12us 98us # Filas enviadas 36 1 15 4.50 14.52 4.18 3.89 # Fila examinar 87 4 30 10,88 28,75 7,37 7,70 # Tamaño de la consulta 2,15k 153 296 245,11 284,79 48,90 258,32 # === = === ========== === ===== ====== = ==== ================ # Perfil # Clasificación ID de consulta Tiempo de respuesta Llamadas R/Llamada V/M Artículo # ==== ==== ====== ====== ===== ====== === == =============== # 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECCIONAR artículo_blog # 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECCIONAR portafolio_artículo # 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECCIONAR portafolio_artículo # 4 0xF14E15D0F47A574 2 0,0003 12,1% 1 0,0003 0,00 SELECCIONAR categoría_portafolio # 5 0x8F848005A09C9588 0,0003 11,8% 1 0,0003 0,00 SELECCIONAR categoría_blog # 6 0x55F49 C753CA2ED64 0,0003 1 0.0003 0.00 SELECCIONAR artículo_blog # ==== ============ ====== ============= ===== ==== == ===== =============== # Consulta 1: 0 QPS, 0x concurrencia, ID 0x728E539F7617C14D en el byte 736 ______ # Puntuaciones: V/M = 0,00 # Rango de tiempo: todos los eventos ocurrieron el 13/02/2014 22:23:52 # Atributo pct total min max avg 95 % stddev mediana # ============ === ======= == ===== ======= ======= === ==== ======= ======= # Conteo 37 3 # Tiempo de ejecución 40 1ms 352us 406us 375us 403us 22us 366us # Tiempo de bloqueo 42 351us 103us 125us 117us 119us 9us 119us # Filas enviadas 25 9 1 4 3 3,89 1,37 3,89 # Filas examinadas 24 21 5 8 7 7,70 1,29 0 # Tamaño de consulta 47 1.02k 261 262 261.25 258.32 0 258.32 # Cadena: # Hosts localhost # Usuarios * # Distribución de tiempo de consulta # 1us # 10us # 100us #### ############################# ###################### ########## # 1ms # 10ms # 100ms # 1s # 10s+ # Tablas # MOSTRAR ESTADO DE LA TABLA COMO " blog_article"\G # MOSTRAR CREAR TABLA `blog_article`\G # EXPLICAR /*!50100 PARTICIONES*/ SELECCIONAR b0_.id COMO id0, b0_.slug COMO slug1, b0_.title COMO título2, b0_.excerpt COMO extracto3, b0_.external_link AS external_link4, b0_.description AS descripción5, b0_.created AS creado6, b0_.updated AS actualizado7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10
Si prefiere analizar estos registros manualmente, puede hacer lo mismo, pero primero deberá exportar el registro a un formato más analizable. Esto se puede hacer así:
Mysqldumpslow /var/log/mysql/mysql-slow.log
Con opciones avanzadas, puedes filtrar los datos para exportar solo lo que necesitas. Por ejemplo, las 10 consultas principales ordenadas por tiempo promedio de ejecución:
Mysqldumpslow -t 10 -s en /var/log/mysql/localhost-slow.log
Conclusión
En esta publicación completa sobre optimización de MySQL, hemos cubierto varios métodos y técnicas a través de los cuales podemos hacer que nuestro MySQL vuele.
Descubrimos la optimización de la configuración, actualizamos los índices y nos deshicimos de algunos cuellos de botella. Todo esto era principalmente teoría, sin embargo, todo es aplicable a aplicaciones del mundo real.
