Este artículo explora ideas para mejorar el rendimiento de la base de datos MySQL y proporciona soluciones específicas desde 8 aspectos.
1. Seleccione los atributos de campo más aplicables.
MySQL puede admitir el acceso a grandes cantidades de datos, pero en términos generales, cuanto más pequeña sea la tabla en la base de datos, más rápidas serán las consultas ejecutadas en ella. Por lo tanto, al crear una tabla, para obtener un mejor rendimiento, podemos establecer el ancho de los campos de la tabla lo más pequeño posible. Por ejemplo, al definir el campo del código postal, si lo configura en CHAR(255), obviamente agregará espacio innecesario a la base de datos. Incluso usar el tipo VARCHAR es redundante, porque CHAR(6) está bien. Asimismo, si es posible, deberíamos usar MEDIUMINT en lugar de BIGIN para definir campos de números enteros.
Otra forma de mejorar la eficiencia es establecer los campos en NOT NULL cuando sea posible, de modo que la base de datos no necesite comparar valores NULL al ejecutar consultas en el futuro.
Para algunos campos de texto, como "provincia" o "género", podemos definirlos como tipos ENUM. Porque en MySQL, el tipo ENUM se trata como datos numéricos y los datos numéricos se procesan mucho más rápido que los tipos de texto. De esta forma, podemos mejorar el rendimiento de la base de datos.
2. Utilice combinaciones (JOIN) en lugar de subconsultas (Subconsultas).
MySQL admite subconsultas SQL a partir de 4.1. Esta técnica le permite utilizar una instrucción SELECT para crear una única columna de resultados de consulta y luego utilizar este resultado como condición de filtro en otra consulta. Por ejemplo, si queremos eliminar clientes que no tienen ningún pedido en la tabla de información básica del cliente, podemos usar una subconsulta para recuperar primero los ID de todos los clientes que emitieron pedidos de la tabla de información de ventas y luego pasar los resultados a la consulta principal, como se muestra a continuación:
ELIMINAR DE información del cliente
DONDE CustomerID NO está en (SELECT CustomerID FROM salesinfo)
El uso de subconsultas puede completar muchas operaciones SQL que lógicamente requieren varios pasos para completarse a la vez. También puede evitar bloqueos de transacciones o tablas, y también es fácil de escribir. Sin embargo, en algunos casos, las subconsultas se pueden reemplazar por uniones más eficientes (JOIN). Por ejemplo, supongamos que queremos recuperar todos los usuarios que no tienen registros de pedidos, podemos usar la siguiente consulta:
SELECT * FROM customerinfo
DONDE CustomerID NO está en (SELECCIONAR CustomerID FROM salesinfo)
Si usa la conexión (UNIRSE)... para completar esta consulta, la velocidad será mucho más rápida. Especialmente si hay un índice en CustomerID en la tabla salesinfo, el rendimiento será mejor. La consulta es la siguiente:
SELECT * FROM customerinfo.
IZQUIERDA UNIRSE a salesinfoON customerinfo.CustomerID=salesinfo.
ID de cliente
DONDE salesinfo.CustomerID ES NULL
Establezca la ruta de datos y coloque los archivos de datos de la base de datos en el directorio NFS compartido (servidor NAS).
Los archivos PID e innioDB deben colocarse en el directorio local del servidor para iniciar y detener el servicio normalmente:
1125. vi /etc/ my.cnf
[mysqld]
#Dónde instalar una base de datos
datadir=/data/mysqldata
#Dónde instalar un motor innoDB
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
Relacionado con la compilación elementos que comienzan desde 222. Las dos líneas de, coloque el archivo PID en el directorio local del servidor:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid
instala la base de datos básica de MySQL:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
si es normal, puede ver que mysql se inicia normalmente
1146 /etc/rc.d/init.d/mysql.server start
1146 /etc/rc.d/init.d/mysql.server; deje de
configurar la alta disponibilidad de HA, no configure el montaje NFS y el servicio mysql se ejecute automáticamente al iniciar el servidor
5. Bloquear tablas
Aunque las transacciones son una muy buena manera de mantener la integridad de la base de datos, debido a su exclusividad, a veces afecta la integridad de la
base de datos;rendimiento de la base de datos, especialmente en períodos de tiempo muy largos en sistemas de aplicaciones grandes. Dado que la base de datos se bloqueará durante la ejecución de la transacción, otras solicitudes de usuarios solo pueden esperar hasta que finalice la transacción. Si solo unos pocos usuarios utilizan un sistema de base de datos, el impacto de las transacciones no se convertirá en un gran problema, pero si miles de usuarios acceden a un sistema de base de datos al mismo tiempo, como acceder a un sitio web de comercio electrónico, causará graves problemas; retraso en la respuesta.
De hecho, en algunos casos podemos obtener un mejor rendimiento bloqueando la tabla. El siguiente ejemplo utiliza el método de tabla de bloqueo para completar la función de transacción en el ejemplo anterior.
BLOQUEAR TABLA inventario ESCRIBIR
SELECCIONE la cantidad DEL inventario
WHEREItem='libro';
...
ACTUALIZAR inventario SET Cantidad=11
WHEREItem='libro';
DESBLOQUEAR MESAS
Aquí, usamos una declaración SELECT para recuperar los datos iniciales y, a través de algunos cálculos, usamos una declaración UPDATE para actualizar los nuevos valores en la tabla. La instrucción LOCK TABLE que contiene la palabra clave WRITE garantiza que no habrá ningún otro acceso al inventario para insertar, actualizar o eliminar antes de que se ejecute el comando UNLOCK TABLES.
6.
El método de utilizar claves externas para bloquear la tabla puede mantener la integridad de los datos, pero no puede garantizar la relevancia de los datos. En este momento podemos usar claves foráneas. Por ejemplo, una clave externa puede garantizar que cada registro de ventas apunte a un cliente existente. Aquí, la clave externa puede asignar el CustomerID en la tabla customerinfo al CustomerID en la tabla salesinfo. Cualquier registro sin un CustomerID válido no se actualizará ni se insertará en salesinfo.
CREAR TABLA información del cliente
(
ID de cliente INT NO NULO,
CLAVE PRINCIPAL (ID de cliente)
) TIPO = INNODB;
CREAR TABLA infoventas
(
SalesID INT NO NULO,
CustomerID INT NO NULO,
CLAVE PRINCIPAL (ID de cliente, ID de ventas),
CLAVE EXTRANJERA (ID de cliente) REFERENCIAS información del cliente
(ID de cliente) EN DELETECASCADE
) TIPO = INNODB;
Tenga en cuenta el parámetro "ON DELETE CASCADE" en el ejemplo. Este parámetro garantiza que cuando se elimina un registro de cliente en la tabla de información del cliente, todos los registros relacionados con el cliente en la tabla de información de ventas también se eliminarán automáticamente. Si desea utilizar claves externas en MySQL, debe recordar definir el tipo de tabla como un tipo InnoDB seguro para transacciones al crear la tabla. Este tipo no es el tipo predeterminado para las tablas MySQL. El método definido es agregar TYPE=INNODB a la instrucción CREATE TABLE. Como se muestra en el ejemplo.
7. El uso de índices
es un método común para mejorar el rendimiento de la base de datos. Permite que el servidor de la base de datos recupere filas específicas mucho más rápido que sin un índice, especialmente si la declaración de consulta contiene MAX(), MIN() y ORDERBY. la mejora del rendimiento es más obvia. Entonces, ¿qué campos deberían indexarse? En términos generales, los índices deben crearse en campos que se utilizarán para UNIRSE, juzgar DÓNDE y ordenar POR ORDENAR. Intente no indexar un campo en la base de datos que contenga una gran cantidad de valores duplicados. Para un campo de tipo ENUM, es muy posible que tenga una gran cantidad de valores duplicados, como el campo "provincia".... en la información del cliente no será útil; al contrario, es posible. Reducir el rendimiento de la base de datos. Podemos crear índices apropiados al mismo tiempo que creamos la tabla, o podemos usar ALTER TABLE o CREATE INDEX para crear índices más adelante. Además MySQL
La indexación y la búsqueda de texto completo son compatibles a partir de la versión 3.23.23. El índice de texto completo es un índice de tipo FULLTEXT en MySQL, pero solo se puede usar para tablas de tipo MyISAM. Para una base de datos grande, será muy rápido cargar los datos en una tabla sin un índice FULLTEXT y luego usar ALTER TABLE o CREATE INDEX para crear el índice. Pero si carga datos en una tabla que ya tiene un índice FULLTEXT, el proceso de ejecución será muy lento.
8. Declaraciones de consulta optimizadas
En la mayoría de los casos, el uso de índices puede mejorar la velocidad de la consulta, pero si las declaraciones SQL se usan incorrectamente, el índice no podrá desempeñar la función que le corresponde. A continuación se detallan varios aspectos a los que se debe prestar atención. Primero, es mejor realizar operaciones de comparación entre campos del mismo tipo. Antes de la versión 3.23 de MySQL, esto era incluso una condición obligatoria. Por ejemplo, un campo INT indexado no se puede comparar con un campo BIGINT pero como caso especial, cuando un campo de tipo CHAR y un campo de tipo VARCHAR tienen el mismo tamaño, se pueden comparar. En segundo lugar, trate de no utilizar funciones para operar en campos indexados.
Por ejemplo, cuando se utiliza la función YEAE() en un campo de tipo FECHA, el índice no funcionará como debería. Por lo tanto, aunque las dos consultas siguientes devuelven los mismos resultados, la última es mucho más rápida que la primera.
SELECCIONE * DEL pedido DONDE AÑO (Fecha del pedido) <2001;
SELECT * FROM pedido WHERE OrderDate<"2001-01-01";
La misma situación también ocurrirá al calcular campos numéricos:
SELECT * FROM inventario WHERE Monto/7<24;
SELECT * FROM inventario WHERE Cantidad<24*7;
Las dos consultas anteriores también devuelven los mismos resultados, pero la última consulta será mucho más rápida que la anterior. En tercer lugar, cuando buscamos campos de caracteres, a veces utilizamos palabras clave LIKE y comodines. Aunque este enfoque es simple, también perjudica el rendimiento del sistema. Por ejemplo, la siguiente consulta comparará todos los registros de la tabla.
SELECCIONAR * DE libros
DONDE nombre como "MySQL%"
Pero si usa la siguiente consulta, los resultados devueltos son los mismos, pero la velocidad es mucho más rápida: ..
SELECT * FROM books
WHERE nombre>="MySQL" y nombre<"MySQM"
Finalmente, debe tener cuidado de evitar permitir que MySQL realice la conversión automática de tipos en la consulta, porque el proceso de conversión también hará que el índice sea ineficaz.