Share
Las operaciones de base de datos a menudo tienden a ser el principal cuello de botella para la mayoría de las aplicaciones web de hoy. No solo los administradores de bases de datos (DBA) tienen que preocuparse por estos problemas de rendimiento. Nosotros, como programadores, tenemos que hacer nuestra parte estructurando las tablas correctamente, escribiendo consultas optimizadas y mejor código. En este artículo, voy a enumerar algunas técnicas de optimización de MySQL para programadores..
Antes de comenzar, tenga en cuenta que puede encontrar un montón de scripts y utilidades de MySQL útiles en Envato Market.
MySQL scripts y utilidades en Envato Market La mayoría de los servidores MySQL tienen el caché de consulta habilitado. Es uno de los métodos más efectivos para mejorar el rendimiento, que es manejado silenciosamente por el motor de base de datos. Cuando la misma consulta se ejecuta varias veces, el resultado se obtiene del caché, que es bastante rápido..
El problema principal es que es tan fácil y oculto para el programador que la mayoría de nosotros tendemos a ignorarlo. Algunas cosas que hacemos realmente pueden evitar que el caché de consultas realice su tarea.
Accentsconagua
// el caché de consulta NO funciona $ r = mysql_query ("SELECCIONE el nombre de usuario DESDE el usuario WHERE signup_date> = CURDATE ()"); // consulta caché funciona! $ hoy = fecha ("Y-m-d"); $ r = mysql_query ("SELECCIONE un nombre de usuario DESDE el usuario WHERE signup_date> = '$ today'"); El motivo por el que la memoria caché de consultas no funciona en la primera línea es el uso de la función CURDATE (). Esto se aplica a todas las funciones no deterministas como NOW () y RAND (), etc.… Como el resultado de retorno de la función puede cambiar, MySQL decide deshabilitar el almacenamiento en caché de consultas para esa consulta. Todo lo que necesitamos hacer es agregar una línea adicional de PHP antes de la consulta para evitar que esto suceda.
El uso de la palabra clave EXPLAIN puede darle una idea de lo que está haciendo MySQL para ejecutar su consulta. Esto puede ayudarlo a detectar cuellos de botella y otros problemas con su consulta o estructuras de tablas.
Los resultados de una consulta de EXPLAIN le mostrarán qué índices se están utilizando, cómo se escanea y clasifica la tabla, etc.
Tome una consulta SELECCIONAR (preferiblemente una compleja, con uniones), y agregue la palabra clave EXPLICAR delante de ella. Puedes usar phpmyadmin para esto. Te mostrará los resultados en una buena mesa. Por ejemplo, digamos que olvidé agregar un índice a una columna, en la que realizo uniones en:
Después de agregar el índice al campo group_id:
Ahora, en lugar de escanear 7883 filas, solo escaneará 9 y 16 filas de las 2 tablas. Una buena regla general es multiplicar todos los números en la columna "filas", y el rendimiento de su consulta será algo proporcional al número resultante.
A veces, cuando consulta sus tablas, ya sabe que está buscando solo una fila. Es posible que esté obteniendo un registro único, o que simplemente esté comprobando la existencia de cualquier número de registros que satisfagan su cláusula WHERE.
En tales casos, agregar LIMIT 1 a su consulta puede aumentar el rendimiento. De esta manera, el motor de la base de datos detendrá la búsqueda de registros después de que encuentre solo 1, en lugar de recorrer toda la tabla o el índice..
// ¿Tengo algún usuario de Alabama? // lo que NO debe hacer: $ r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'"); if (mysql_num_rows ($ r)> 0) // ... // mucho mejor: $ r = mysql_query ("SELECCIONE 1 DEL usuario WHERE state = 'Alabama' LIMIT 1"); if (mysql_num_rows ($ r)> 0) //… Los índices no son solo para las claves primarias o las claves únicas. Si hay alguna columna en su tabla por la cual buscará, casi siempre debe indexarlas.
Como puede ver, esta regla también se aplica en una búsqueda de cadena parcial como "last_name LIKE 'a%'". Al buscar desde el principio de la cadena, MySQL puede utilizar el índice en esa columna.
También debe comprender qué tipos de búsquedas no pueden usar los índices regulares. Por ejemplo, al buscar una palabra (por ejemplo, "DONDE post_content LIKE '% apple%'"), no verá un beneficio de un índice normal. Será mejor que utilice la búsqueda de texto completo de MySQL o cree su propia solución de indexación..
Si su aplicación contiene muchas consultas de JOIN, debe asegurarse de que las columnas por las que se une estén indexadas en ambas tablas. Esto afecta a cómo MySQL optimiza internamente la operación de unión..
Además, las columnas que se unen, deben ser del mismo tipo. Por ejemplo, si une una columna DECIMAL a una columna INT de otra tabla, MySQL no podrá usar al menos uno de los índices. Incluso las codificaciones de caracteres deben ser del mismo tipo para las columnas de tipo cadena..
// buscando compañías en mi estado $ r = mysql_query ("SELECCIONE company_name DE los usuarios UNIRSE A LA IZQUIERDA a las compañías ON (users.state = companies.state) DONDE users.id = $ user_id"); // ambas columnas de estado deben estar indexadas // y ambas deben ser del mismo tipo y codificación de caracteres // o MySQL podría realizar exploraciones de tabla completas Este es uno de esos trucos que suenan bien al principio, y muchos programadores novatos caen en esta trampa. Es posible que no se dé cuenta del terrible cuello de botella que puede crear una vez que comience a usar esto en sus consultas..
Si realmente necesitas filas al azar de tus resultados, hay maneras mucho mejores de hacerlo. Por supuesto, se necesita código adicional, pero evitará un cuello de botella que empeore de manera exponencial a medida que sus datos crezcan. El problema es que MySQL tendrá que realizar la operación RAND () (que toma poder de procesamiento) para cada fila en la tabla antes de clasificarla y darle solo 1 fila..
// lo que NO debe hacer: $ r = mysql_query ("SELECCIONE el nombre de usuario DESDE EL ORDEN DEL ORDEN POR RAND () LÍMITE 1"); // mucho mejor: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECCIONE el nombre de usuario DESDE EL LÍMITE $ rand, 1"); Así que elige un número aleatorio menor que el número de resultados y lo utiliza como el desplazamiento en su cláusula LIMIT.
Cuantos más datos se lean de las tablas, más lenta será la consulta. Aumenta el tiempo que toma para las operaciones del disco. Además, cuando el servidor de la base de datos está separado del servidor web, tendrá demoras en la red más largas debido a que los datos deben ser transferidos entre los servidores..
Es un buen hábito especificar siempre qué columnas necesita cuando está haciendo su SELECT.
// no se prefiere $ r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Bienvenido $ d ['nombre de usuario']"; // mejor: $ r = mysql_query ("SELECCIONAR nombre de usuario DESDE el usuario WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Bienvenido $ d ['nombre de usuario']"; // las diferencias son más significativas con conjuntos de resultados más grandes En cada tabla tiene una columna de identificación que es la CLAVE PRIMARIA, AUTO_INCREMENTO y uno de los sabores de INT. También preferiblemente UNSIGNED, ya que el valor no puede ser negativo..
Incluso si tiene una tabla de usuarios que tiene un campo de nombre de usuario único, no realice esa clave primaria. Los campos VARCHAR como claves primarias son más lentos. Y tendrá una mejor estructura en su código refiriéndose a todos los usuarios con sus ID internamente.
También hay operaciones detrás de escena realizadas por el propio motor MySQL, que utiliza el campo de clave principal internamente. Cuanto más importante es la configuración de la base de datos, más complicada es. (clusters, particiones etc…).
Una posible excepción a la regla son las "tablas de asociación", que se utilizan para el tipo de asociación de muchos a muchos entre dos tablas. Por ejemplo, una tabla "posts_tags" que contiene 2 columnas: post_id, tag_id, que se usa para las relaciones entre dos tablas llamadas "post" y "etiquetas". Estas tablas pueden tener una clave PRIMARIA que contiene ambos campos de identificación.
Las columnas de tipo ENUM son muy rápidas y compactas. Internamente, se almacenan como TINYINT, aunque pueden contener y mostrar valores de cadena. Esto los hace un candidato perfecto para ciertos campos..
Si tiene un campo, que contendrá solo unos pocos tipos diferentes de valores, use ENUM en lugar de VARCHAR. Por ejemplo, podría ser una columna llamada "estado", y solo contener valores como "activo", "inactivo", "pendiente", "caducado", etc.
Incluso hay una forma de obtener una "sugerencia" de MySQL sobre cómo reestructurar su tabla. Cuando tiene un campo VARCHAR, puede sugerirle que cambie ese tipo de columna a ENUM. Esto se hace usando la llamada ANALIZAR PROCEDIMIENTO (). Lo que nos lleva a:
ANÁLISIS DE PROCEDIMIENTOS () permitirá que MySQL analice las estructuras de las columnas y los datos reales en su tabla para hacer algunas sugerencias para usted. Solo es útil si hay datos reales en sus tablas porque eso juega un papel importante en la toma de decisiones..
Por ejemplo, si creó un campo INT para su clave principal, sin embargo, no tiene demasiadas filas, puede sugerirle que use un MEDIUMINT en su lugar. O si está utilizando un campo VARCHAR, puede obtener una sugerencia para convertirlo a ENUM, si solo hay unos pocos valores únicos.
También puede ejecutar esto haciendo clic en el enlace "Proponer estructura de tabla" en phpmyadmin, en una de sus vistas de tabla.
Ten en cuenta que estas son solo sugerencias. Y si su mesa va a crecer, es posible que ni siquiera sean las sugerencias correctas a seguir. La decisión es en última instancia tuya..
A menos que tenga una razón muy específica para usar un valor NULL, siempre debe establecer sus columnas como NOT NULL.
En primer lugar, pregúntese si hay alguna diferencia entre tener un valor de cadena vacío frente a un valor NULO (para campos INT: 0 frente a NULL). Si no hay razón para tener ambos, no necesita un campo NULO. (¿Sabías que Oracle considera NULL y una cadena vacía como la misma?)
Las columnas NULL requieren espacio adicional y pueden agregar complejidad a sus declaraciones de comparación. Solo evítalos cuando puedas. Sin embargo, entiendo que algunas personas pueden tener razones muy específicas para tener valores NULL, lo que no siempre es algo malo.
De los documentos de MySQL:
"Las columnas NULL requieren espacio adicional en la fila para registrar si sus valores son NULL. Para las tablas MyISAM, cada columna NULL toma un bit extra, redondeado al byte más cercano".
El uso de declaraciones preparadas ofrece múltiples beneficios, tanto por razones de rendimiento como de seguridad..
Las declaraciones preparadas filtrarán las variables que las enlaza de forma predeterminada, lo que es excelente para proteger su aplicación contra ataques de inyección de SQL. Por supuesto, también puede filtrar sus variables manualmente, pero esos métodos son más propensos al error humano y al olvido por parte del programador. Esto es un problema menor cuando se utiliza algún tipo de marco u ORM.
Dado que nuestro enfoque está en el rendimiento, también debo mencionar los beneficios en esa área. Estos beneficios son más significativos cuando se utiliza la misma consulta varias veces en su aplicación. Puede asignar diferentes valores a la misma declaración preparada, pero MySQL solo tendrá que analizarla una vez.
También las últimas versiones de MySQL transmiten declaraciones preparadas en forma binaria nativa, que son más eficientes y también pueden ayudar a reducir los retrasos en la red..
Hubo un momento en que muchos programadores solían evitar declaraciones preparadas a propósito, por una sola razón importante. No estaban siendo cacheados por el caché de consultas de MySQL. Pero como en algún momento alrededor de la versión 5.1, el almacenamiento en caché de consultas también es compatible.
Para usar sentencias preparadas en PHP, verifique la extensión mysqli o use una capa de abstracción de base de datos como PDO.
// crear una declaración preparada si ($ stmt = $ mysqli-> prepare ("SELECCIONE el nombre de usuario DESDE el estado WHERE =?")) // parámetros de enlace $ stmt-> bind_param ("s", $ state); // ejecuta $ stmt-> execute (); // vincular variables de resultado $ stmt-> bind_result ($ nombre de usuario); // obtener valor $ stmt-> fetch (); printf ("% s es de% s \ n", $ nombre de usuario, $ estado); $ stmt-> close (); Normalmente, cuando realiza una consulta desde un script, esperará a que finalice la ejecución de esa consulta antes de que pueda continuar. Puedes cambiar eso usando consultas sin búfer.
Hay una gran explicación en la documentación de PHP para la función mysql_unbuffered_query ():
"mysql_unbuffered_query () envía la consulta de consulta SQL a MySQL sin recuperar y almacenar en búfer las filas de resultados automáticamente como mysql_query () lo hace. Esto ahorra una cantidad considerable de memoria con consultas SQL que producen grandes conjuntos de resultados, y puede comenzar a trabajar en el conjunto de resultados inmediatamente después de que se haya recuperado la primera fila, ya que no tiene que esperar hasta que se haya realizado la consulta SQL completa ".
Sin embargo, viene con ciertas limitaciones. Debe leer todas las filas o llamar a mysql_free_result () antes de poder realizar otra consulta. Además, no se le permite usar mysql_num_rows () o mysql_data_seek () en el conjunto de resultados.
Muchos programadores crearán un campo VARCHAR (15) sin darse cuenta de que realmente pueden almacenar direcciones IP como valores enteros. Con un INT, desciende a solo 4 bytes de espacio y, en cambio, tiene un campo de tamaño fijo.
Debe asegurarse de que su columna sea una INT SIN FIRMAR, ya que las direcciones IP utilizan todo el rango de un entero sin signo de 32 bits.
En sus consultas, puede usar INET_ATON () para convertir e IP a un número entero, e INET_NTOA () para viceversa. También hay funciones similares en PHP llamadas ip2long () y long2ip ().
$ r = "ACTUALIZAR usuarios SET ip = INET_ATON ('$ _ SERVER [' REMOTE_ADDR ']') WHERE user_id = $ user_id"; Cuando todas las columnas de una tabla tienen "longitud fija", la tabla también se considera "estática" o "longitud fija". Ejemplos de tipos de columnas que NO son de longitud fija son: VARCHAR, TEXT, BLOB. Si solo incluye 1 de estos tipos de columnas, la tabla deja de ser de longitud fija y debe ser manejada de manera diferente por el motor MySQL.
Las tablas de longitud fija pueden mejorar el rendimiento porque es más rápido para que el motor MySQL busque en los registros. Cuando quiere leer una fila específica en una tabla, puede calcular rápidamente la posición de la misma. Si el tamaño de la fila no es fijo, cada vez que necesite realizar una búsqueda, debe consultar el índice de clave principal.
También son más fáciles de almacenar en caché y más fáciles de reconstruir después de un bloqueo. Pero también pueden tomar más espacio. Por ejemplo, si convierte un campo VARCHAR (20) en un campo CHAR (20), siempre ocupará 20 bytes de espacio, independientemente de su ubicación.
Al utilizar las técnicas de "Partición vertical", puede separar las columnas de longitud variable en una tabla separada. Lo que nos lleva a:
La partición vertical es el acto de dividir la estructura de la tabla de manera vertical por razones de optimización.
Ejemplo 1: Es posible que tenga una tabla de usuarios que contenga direcciones personales que no se lean con frecuencia. Puede elegir dividir su tabla y almacenar la información de la dirección en una tabla separada. De esta manera, su tabla de usuarios principales se reducirá de tamaño. Como saben, las tablas más pequeñas tienen un rendimiento más rápido..
Ejemplo 2: Tienes un campo "last_login" en tu tabla. Se actualiza cada vez que un usuario inicia sesión en el sitio web. Pero cada actualización en una tabla hace que la memoria caché de consulta para esa tabla se vacíe. Puede colocar ese campo en otra tabla para mantener al mínimo las actualizaciones de su tabla de usuarios.
Pero también debe asegurarse de no tener que unirse constantemente a estas 2 tablas después de la partición o podría sufrir una disminución en el rendimiento..
Si necesita realizar una gran consulta DELETE o INSERT en un sitio web en vivo, debe tener cuidado de no perturbar el tráfico web. Cuando se realiza una consulta grande como esa, puede bloquear sus tablas y detener su aplicación web.
Apache ejecuta muchos procesos / hilos paralelos. Por lo tanto, funciona de manera más eficiente cuando los scripts terminan de ejecutarse lo antes posible, por lo que los servidores no experimentan demasiadas conexiones y procesos abiertos a la vez que consumen recursos, especialmente la memoria..
Si terminas bloqueando tus tablas por un período de tiempo prolongado (como 30 segundos o más), en un sitio web de alto tráfico, causará un proceso y una acumulación de consultas, lo que puede llevar mucho tiempo borrar o incluso bloquear tu web. servidor.
Si tiene algún tipo de secuencia de comandos de mantenimiento que necesita eliminar un gran número de filas, solo use la cláusula LIMIT para hacerlo en lotes más pequeños para evitar esta congestión.
while (1) mysql_query ("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) // done deleting break; // you can even pause a bit usleep(50000); Con los motores de base de datos, el disco es quizás el cuello de botella más importante. Mantener las cosas más pequeñas y más compactas suele ser útil en términos de rendimiento, para reducir la cantidad de transferencia de disco.
Los documentos de MySQL tienen una lista de requisitos de almacenamiento para todos los tipos de datos.
Si se espera que una tabla tenga muy pocas filas, no hay razón para hacer que la clave primaria sea una INT, en lugar de MEDIUMINT, SMALLINT o incluso, en algunos casos, TINYINT. Si no necesita el componente de tiempo, use DATE en lugar de DATETIME.
Solo asegúrate de dejar un espacio razonable para crecer o podrías terminar como Slashdot.
Los dos principales motores de almacenamiento en MySQL son MyISAM e InnoDB. Cada uno tiene sus pros y sus contras..
MyISAM es bueno para aplicaciones de lectura pesada, pero no se escala muy bien cuando hay muchas escrituras. Incluso si está actualizando un campo de una fila, toda la tabla se bloquea, y ningún otro proceso puede leer de ella hasta que la consulta haya finalizado. MyISAM es muy rápido para calcular los tipos de consultas SELECT COUNT (*).
InnoDB tiende a ser un motor de almacenamiento más complicado y puede ser más lento que MyISAM para la mayoría de las aplicaciones pequeñas. Pero es compatible con el bloqueo basado en filas, que se escala mejor. También es compatible con algunas características más avanzadas, como las transacciones.
Al utilizar un ORM (Object Relational Mapper), puede obtener ciertos beneficios de rendimiento. Todo lo que un ORM puede hacer, puede codificarse manualmente también. Pero esto puede significar demasiado trabajo extra y requiere un alto nivel de experiencia.
Los ORM son excelentes para "carga perezosa". Significa que pueden obtener valores solo cuando son necesarios. Pero debe tener cuidado con ellos o puede terminar creando muchas mini consultas que pueden reducir el rendimiento..
Los ORM también pueden agrupar sus consultas en transacciones, que funcionan mucho más rápido que el envío de consultas individuales a la base de datos.
Actualmente mi ORM favorito para PHP es Doctrine. Escribí un artículo sobre cómo instalar Doctrine con CodeIgniter.
Las conexiones persistentes están diseñadas para reducir la sobrecarga de recrear conexiones a MySQL. Cuando se crea una conexión persistente, permanecerá abierta incluso después de que el script termine de ejecutarse. Como Apache reutiliza sus procesos secundarios, la próxima vez que se ejecute un nuevo script, se reutilizará la misma conexión MySQL..
Suena genial en teoría. Pero desde mi experiencia personal (y muchas otras), estas características no valen la pena. Puede tener serios problemas con los límites de conexión, problemas de memoria, etc..
Apache se ejecuta extremadamente paralelo y crea muchos procesos secundarios. Esta es la razón principal por la que las conexiones persistentes no funcionan muy bien en este entorno. Antes de considerar el uso de la función mysql_pconnect (), consulte al administrador de su sistema.
