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 una conexión NULA
(JOIN). La razón por la que es más eficiente es que MySQL no necesita crear una tabla temporal en la memoria para completar esta consulta lógica de dos pasos.
3. Utilice union (UNION) para reemplazar tablas temporales creadas manualmente.
MySQL admite consultas UNION a partir de la versión 4.0, que pueden fusionar dos o más consultas SELECT que requieren el uso de tablas temporales en una sola consulta. Cuando finalice la sesión de consulta del cliente, la tabla temporal se eliminará automáticamente para garantizar que la base de datos esté ordenada y eficiente. Cuando usamos UNION para crear una consulta, solo necesitamos usar UNION como palabra clave para conectar múltiples declaraciones SELECT. Cabe señalar que la cantidad de campos en todas las declaraciones SELECT debe ser la misma. El siguiente ejemplo demuestra una consulta utilizando UNION.
SELECCIONE Nombre, Teléfono DEL cliente
UNIÓN
SELECCIONE Nombre, Fecha de nacimiento DEL autor
UNIÓN
SELECCIONE Nombre, Proveedor DEL producto
4. Transacciones
Aunque podemos usar subconsultas, conexiones (JOIN) y uniones (UNION) para crear una variedad de consultas, no todas las operaciones de la base de datos se pueden realizar con una sola o se pueden completar con solo algunas declaraciones SQL. Más a menudo, se necesita una serie de declaraciones para completar un determinado tipo de trabajo. Pero en este caso, cuando una determinada declaración en este bloque de declaraciones se ejecuta incorrectamente, la operación de todo el bloque de declaraciones se volverá incierta. Imagine que desea insertar ciertos datos en dos tablas relacionadas al mismo tiempo. Esto puede suceder: después de que la primera tabla se actualiza correctamente, ocurre una situación inesperada en la base de datos que hace que la operación en la segunda tabla no se complete. De esta forma, los datos quedarán incompletos e incluso los datos de la base de datos serán destruidos. Para evitar esta situación, debe utilizar transacciones. Su función es: cada declaración en el bloque de declaración tiene éxito o falla. En otras palabras, se puede mantener la coherencia e integridad de los datos de la base de datos. Las cosas comienzan con la palabra clave BEGIN y terminan con la palabra clave COMMIT. Si una operación SQL falla durante este período, el comando ROLLBACK puede restaurar la base de datos al estado anterior a que comenzara BEGIN.
COMENZAR;
INSERTAR EN infoventas SET CustomerID=14;
ACTUALIZAR inventario SET Cantidad=11
DONDE elemento = 'libro';
COMMIT;
Otra función importante de las transacciones es que cuando varios usuarios usan la misma fuente de datos al mismo tiempo, pueden usar el método de bloquear la base de datos para proporcionar a los usuarios un método de acceso seguro, que puede garantizar que las operaciones del usuario no estén bloqueadas. por otros usuarios.
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 el rendimiento de la base de datos, especialmente 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 TABLAS
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
) TYPE = 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 declaració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 admite búsqueda e indexación de texto completo 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.
SELECCIONAR * DESDE pedido DONDE AÑO(FechaPedido)<2001;
SELECCIONAR
* DESDE pedido DONDE FechaPedido<"2001-01-01"
También ocurrirá la misma situación al calcular campos numéricos:
SELECCIONAR * DESDE inventario DONDE Importe/ 7<24;
* DESDE el inventario DONDE 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.