MySQL es un sistema gratuito de gestión de bases de datos ampliamente utilizado en PHP. Al crear tablas en MySQL, hay muchos tipos de motor de almacenamiento para elegir. MyIsam, Innodb y Memory son tres tipos populares.
Elegir una herramienta de tienda es extremadamente importante antes de diseñar la base de datos, ya que ayuda a mejorar el rendimiento de las consultas. Si el motor de la tienda cambia más tarde, llevará mucho tiempo si los datos de la tabla son grandes. Hablemos de eso.
I. Motores de almacenamiento MySQL
- 1. MiISAM
Este motor de almacenamiento permite índices de búsqueda de texto completo. Por lo tanto, este motor de almacenamiento brinda velocidad de lectura y búsqueda rápida.
La desventaja de MyISAM es que utiliza un mecanismo de bloqueo a nivel de tabla, por lo que al agregar, editar o eliminar un determinado registro en la misma tabla, esa tabla se bloqueará y no se permitirá hasta que se realice la operación anterior.
1.2. InnoDB
Actualmente, InnoDB está configurado de forma predeterminada al crear una base de datos, admite clave externa y, desde la versión 5.6, InnoDB también admite índices de búsqueda de texto completo.
La mejor velocidad de inserción/actualización/eliminación porque funciona de acuerdo con el mecanismo de bloqueo de nivel de fila, por lo que al agregar, editar o eliminar una tabla, solo se manipulan los registros y otras actividades en esta tabla no se ven afectadas.
1.3. Memoria
El motor de memoria es el motor más adecuado para un acceso rápido a los datos, ya que todo se almacena en la RAM. Y así, al reiniciar MySQL o el servidor, se eliminarán todos los datos.
Entonces debemos elegir el motor de tienda que se adapte al propósito de nuestro proyecto:
– MyISAM: Para una aplicación con alta frecuencia de lectura, como sitios de noticias, blogs, debes usar MyISAM.
– InnoDB: Para aplicaciones con alta frecuencia de inserción y actualización como: Foros, redes sociales… debes usar InnoDB.
– Memoria: para tablas que contienen datos que no se han almacenado durante demasiado tiempo y para sesiones de usuario, debe usar Memoria
II. Optimizar consultas a la base de datos MySQL
2.1. MySQL inserta varias filas
No puede ejecutar la instrucción INSERT en un bucle, lo que en realidad afecta la velocidad de la conexión a la base de datos en caso de que su bucle tenga muchos valores.
Usemos una declaración INSERT en este caso, por ejemplo:
INSERT INTO USERS (full_name, age, gender) VALUES (‘John’, 20, 1), (‘Sara’, 30, 0), (‘Mary’, 25, 0);
2.2. Actualización múltiple de MySQL en una sola declaración
De manera similar a la declaración INSERT múltiple, la ACTUALIZACIÓN debe optimizarse cuando desee realizar la misma tarea.
Utilice ON DUPLICATE KEY UPDATE para aplicar, por ejemplo:
INSERT INTO USERS (id, full_name, age, gender) VALUES (1, ‘John’, 21, 0), (2, ‘Sara’, 31, 0), (3, ‘Mary’, 26, 1) ON DUPLICATE KEY UPDATE full_name=values(full_name), age=values(age), gender=values(gender);
2.3. No utilice LIMIT OFFSET con tablas con datos grandes
Por ejemplo:
SELECT * FROM TABLE LIMIT 10 OFFSET 80000 (starting at 80001)
OR
SELECT * FROM TABLE LIMIT 80001, 10
Por favor reemplácelo con:
SELECT * FROM TABLE WHERE id > 80000 LIMIT 10
2.4. Indexación en MySQL
– Cómo escribir índice compuesto vs índice:
- Si consulta con frecuencia 2 campos, utilice el índice compuesto (a, b) en lugar del índice (a), índice (b). El índice (a, b) es más rápido.
- Si la consulta tiene solo 1 campo, simplemente indexe a sin índice b
– Cada campo individual no necesita ÚNICO, pero cuando se combina como la dirección de casa, puede escribir ÍNDICE de la siguiente manera:
CREATE UNIQUE INDEX index_street_number_city ON addresses (street, house_number, city);
Nota: Solo debe indexar para admitir las consultas leídas con frecuencia y los campos utilizados con frecuencia. Cuantos más índices, más afecta la inserción, eliminación y actualización.
2.5. Utilice el tipo de campo correcto para los datos.
Hay muchos tipos diferentes de campos disponibles a través de MySQL, pero es mejor utilizar aquellos que estén familiarizados con cadenas y números.
Por ejemplo, si solo almacena los números 1, 2 y 3 en un campo, es mejor usar TINYINT que INTERGER. El espacio de almacenamiento en el servidor se ahorrará más.
III. Algunos comandos de consulta MySQL útiles
- Lo más básico en una consulta SELECT
La consulta SELECT no debe utilizar SELECT * si no es absolutamente necesario.
- Función CONCAT de SQL:
La función CONCAT se utiliza para concatenar dos o más cadenas en una sola cadena.
SELECT CustomerID, CONCAT(FirstName, LastName) AS FullName, CONCAT(PostalCode, Town, District, City) AS Address FROM Customers;
Resultado:
3. MySQL JOIN Seleccione el último registro:
SELECT a.id, a.user_id, a.user_name
FROM Customers as a
LEFT JOIN ViewHistories as b ON b.user_id = a.user_id
WHERE b.id = (SELECT MAX(id) FROM ViewHistories WHERE user_id = a.user_id)
ORDER BY a.name
Si el nombre de usuario incluye japonés , es posible ORDENAR POR condición: ORDER BY nombre de usuario intercalar utf8_unicode_ci
- Insertar registro si no existe en la tabla [duplicado]
$db = DB::getInstance();
$user_id = 1001;
$name = ‘John’;
$age = ’26’;
$birthday = ‘1999-01-01’;
$sql = “INSERT INTO users (user_id, name, age, birthday)
SELECT * FROM (SELECT ?, ?, ?, ?) AS tmp
WHERE NOT EXISTS (
SELECT user_id FROM users WHERE user_id = ?
)”;
$run = $db->prepare($sql);
if ($run->execute([$user_id, $name, $age, $birthday, $user_id])) {
// …
// ex: $lastInsertId = $db->lastInsertId();
}
if (!empty($lastInsertId)) {
return ‘This data already exists !’;
}
- ¿Cómo eliminar de varias tablas con un comando MySQL?
Utilice JOIN en la declaración DELETE.
Ejemplo: eliminar la tabla del registro dos (registros, log_detail) con $id = 1 en la tabla de registros
$sql = “DELETE a, b
FROM logs as a
JOIN log_details as b ON b.log_id = a.id
WHERE a.id = $id “;
Gracias por leer el artículo.