Estos contenidos se han traducido de forma automática para su comodidad, pero Huawei Cloud no garantiza la exactitud de estos. Para consultar los contenidos originales, acceda a la versión en inglés.

Centro de ayuda> GaussDB(for MySQL)> Guía del usuario> Especificaciones y sugerencias de uso> Reglas de desarrollo> Uso de SQL

Actualización más reciente 2023-12-14 GMT+08:00

Ver PDF

Uso de SQL

Diseño de bases de datos

Asegúrese de que todos los caracteres estén almacenados y representados en formato utf-8 o utf8mb4. Los comentarios deben ser proporcionados para tablas y campos.
Evite el uso de grandes transacciones.

Por ejemplo, si se ejecutan varias sentencias SELECT y UPDATE en una transacción de alta frecuencia, la capacidad de concurrencia de la base de datos se ve gravemente afectada porque recursos tales como bloqueos mantenidos por la transacción solo pueden liberarse cuando la transacción se revierte o se confirma. En este caso, también se debe considerar la coherencia de escritura de datos.

Diseño de índice

Reduzca el uso de ORDER BY que no se puede usar con índices según los requisitos de servicio reales. Las sentencias como ORDER BY, GROUP BY y DISTINCT consumen muchos recursos de CPU.
Si se trata de una sentencia SQL compleja, utilice el diseño de índice existente y agregue EXPLAIN antes de la sentencia SQL. EXPLAIN puede ayudarle a optimizar el índice mediante la adición de algunas restricciones de consulta.
Ejecute las nuevas sentencias SELECT, UPDATE o DELETE con EXPLAIN para comprobar el uso del índice y asegurarse de que no se muestren Using filesort y Using temporary en la columna Extra. Si el número de filas analizadas es superior a 1,000, tenga cuidado al ejecutar estas sentencias. Analice los registros de consultas lentas y elimine las sentencias de consultas lentas no utilizadas todos los días.
EXPLAIN:
- type: ALL, index, range, ref, eq_ref, const, system, NULL (El rendimiento se clasifica de pobre a bueno de izquierda a derecha.)
- possible_keys: indica los índices desde los que MySQL puede elegir encontrar las filas de esta tabla. Si hay un índice en un campo, el índice aparece en la lista, pero no puede ser utilizado por la consulta.
- key: indica la clave (índice) que MySQL realmente decidió usar. Si clave es NULL, MySQL no encontró ningún índice que usar para ejecutar la consulta de manera más eficiente. Para forzar a MySQL a usar o ignorar un índice que aparece en la columna possible_keys, use FORCE INDEX, USE INDEX o IGNORE INDEX en la consulta.
- ref: muestra qué columnas o constantes se comparan con el índice nombrado en la columna clave para seleccionar filas de la tabla.
- rows indica el número estimado de filas que se van a leer para los registros requeridos en función de las estadísticas de la tabla y la selección del índice.
- Extra:
  
  Using temporary: Para resolver la consulta, MySQL necesita crear una tabla temporal para contener el resultado. Esto suele ocurrir si la consulta contiene cláusulas GROUP BY y ORDER BY que muestran columnas de manera diferente.
  
  Using filesort: MySQL debe hacer una pasada extra para averiguar cómo recuperar las filas en orden ordenado.
  
  Using index: La información de la columna se recupera de la tabla usando solo información en el árbol de índices sin tener que hacer una búsqueda adicional para leer la fila real. Si se muestra Using where al mismo tiempo, indica que la información deseada necesita obtenerse usando el árbol de índices y leyendo filas de la tabla.
  
  Using where: En la cláusula WHERE, se muestra Using where cuando los datos de deseo se obtienen sin leer todos los datos de la tabla o los datos de deseo no se pueden obtener solo usando índices. A menos que específicamente tenga la intención de buscar o examinar todas las filas de la tabla, puede que tenga algo mal en su consulta si el valor Extra no es Using where y el tipo de combinación de tabla es ALL o index.
Si se utiliza una función en una sentencia WHERE, el índice no es válido.

Por ejemplo, en WHERE left(name, 5) = 'zhang', la función left invalida el índice de name. Puede modificar la condición en el lado del servicio y eliminar la función. Cuando el conjunto de resultados devuelto es pequeño, el lado del servicio filtra las filas que cumplen la condición.

Consulta SQL de base de datos

Optimice las sentencias ORDER BY... LIMIT por índices para mejorar la eficiencia de ejecución.
Si las sentencias contienen ORDER BY, GROUP BY o DISTINCT, asegúrese de que el conjunto de resultados filtrado por la condición WHERE contenga hasta 1000 líneas. De lo contrario, las sentencias SQL se ejecutan lentamente.
Para las sentencias ORDER BY, GROUP BY y DISTINCT, utilice índices para recuperar directamente los datos ordenados. Por ejemplo, utilice key(a,b) en where a=1 order by b.
Cuando utilice JOIN, utilice índices en la misma tabla en la condición WHERE.

Ejemplo:

select t1.a, t2.b from t1,t2 where t1.a=t2.a and t1.b=123 and t2.c= 4

Si los campos t1.c y t2.c son los mismos, solo se utiliza b en el t1. En este caso, si t2.c=4 en la condición WHERE se cambia a t1.c=4,(b,c) se puede utilizar. Esto puede ocurrir durante el diseño de redundancia de campo (forma antinormal).
Si no se requiere deduplicación, utilice UNION ALL, que no realiza operaciones de clasificación y es más rápido que UNION.
Para implementar la consulta de paginación en código, especifique que si count se establece en 0, no se ejecutan las sentencias de paginación posteriores.
No ejecute con frecuencia COUNT en una tabla. Lleva mucho tiempo realizar COUNT en una tabla con una gran cantidad de datos. Generalmente, la velocidad de respuesta es en segundos. Si necesita realizar con frecuencia la operación COUNT en una tabla, introduzca una tabla de conteo especial.
Si solo se devuelve un registro, utilice LIMIT 1. Si los datos son correctos y se puede determinar el número de registros devueltos en el conjunto de resultados, utilice LIMIT tan pronto como sea posible.
Al evaluar la eficiencia de las sentencias DELETE y UPDATE, cambie las sentencias a SELECT y ejecute EXPLAIN. Un gran número de sentencias SELECT ralentizarán la base de datos, y las operaciones de escritura bloquearán las tablas.
TRUNCATE TABLE es más rápido y utiliza menos recursos de sistema y registro que DELETE. Si la tabla que se va a eliminar no tiene un disparador y se necesita eliminar toda la tabla, se recomienda TRUNCATE TABLE.
- TRUNCATE TABLE no escribe los datos eliminados en los archivos de registro.
- Una instrucción TRUNCATE TABLE tiene la misma función que una sentencia DELETE sin una cláusula WHERE.
- Las sentencias TRUNCATE TABLE no se pueden escribir con otras sentencias DML en la misma transacción.
No utilice consultas negativas para evitar el análisis completo de la tabla.

Las consultas negativas indican que se utilizan los siguientes operadores negativos: NOT, !=, <>, NOT EXISTS, NOT IN, y NOT LIKE. Si se utiliza una consulta negativa, la estructura de índice no se puede utilizar para la búsqueda binaria. En su lugar, es necesario escanear toda la tabla.
No realice JOIN en más de tres tablas. Los tipos de datos de los campos que se van a unir deben ser los mismos.
Durante la consulta asociada a varias tablas, asegúrese de que los campos asociados tengan índices. Al unir varias tablas, seleccione la tabla con un conjunto de resultados más pequeño como la tabla de control para unir otras tablas. Preste atención a los índices de tablas y al rendimiento de SQL incluso si se unen dos tablas.

Desarrollo de sentencias SQL

División de sentencias SQL individuales.

Por ejemplo, en la condición OR f_phone='10000' or f_mobile='10000', los dos campos tienen sus propios índices, pero solo se puede usar uno de ellos. Puede dividir la sentencia en dos sentencias SQL o usar UNION ALL.
Si es posible, realice el cálculo SQL complejo o la lógica de servicio en la capa de servicio.
Utilice un método de paginación adecuado para mejorar la eficiencia de paginación. No se recomienda saltar la paginación para páginas grandes.
- Supongamos que se utiliza una sentencia de paginación similar a la siguiente:
```
SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
```
  Esto provoca un gran número de operaciones de E/S porque MySQL utiliza la política de lectura anticipada.
- Modo de paginación recomendado: Transfiere el valor umbral de la última página.
```
SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10;
```

Ejecute sentencias UPDATE en transacciones basadas en claves primarias o claves únicas. De lo contrario, se genera un bloqueo de hueco y se expande el intervalo de datos bloqueados. Como resultado, el rendimiento del sistema se deteriora y se produce un interbloqueo.
No utilice claves externas ni operaciones en cascada. Los problemas de las claves foráneas se pueden resolver en la capa de aplicación.

Ejemplo:

Si student_id es una clave principal en la tabla de estudiantes, student_id es una clave externa en la tabla de puntuación. Si se actualiza student_id en la tabla de estudiantes, también se actualiza student_id en la tabla de puntuaciones. Esta es una actualización en cascada.

Las claves externas y las actualizaciones en cascada son adecuadas para clústeres de nodo único con baja simultaneidad y no son adecuadas para clústeres distribuidos con alta simultaneidad.

Las actualizaciones en cascada pueden hacer que los bloques fuertes y las claves externas afecten a las operaciones INSERT.
Si es posible, no utilice IN. Si es necesario, asegúrese de que el número de elementos establecidos después de IN debería ser como máximo 500.
Para reducir el número de interacciones con la base de datos, utilice lotes de sentencias SQL. Por ejemplo, INSERT INTO... VALUES (XX),(XX),(XX)....(XX), el número de XX debe estar dentro de 100.
No utilice procedimientos almacenados, que son difíciles de depurar, extender y trasplantar.
No se recomienda utilizar disparadores, programadores de eventos y vistas para la lógica de servicio. La lógica de servicio debe procesarse en la capa de servicio para evitar la dependencia lógica de la base de datos.
No utilice la conversión de tipo implícito.
Las reglas de conversión son las siguientes:
1. Si al menos uno de los dos parámetros es NULL, el resultado de la comparación también es NULL. Sin embargo, cuando se utiliza <=> para comparar dos valores NULL, se devuelve 1.
2. Si ambos parámetros son cadenas de caracteres, se comparan como cadenas de caracteres.
3. Si ambos parámetros son enteros, se comparan como enteros.
4. Cuando un parámetro es un valor hexadecimal y el otro parámetro es un valor no numérico, se comparan como cadenas binarias.
5. Si un parámetro es un valor TIMESTAMP o DATETIME y el otro parámetro es un valor CONSTANT, se comparan como valores TIMESTAMP.
6. Si un parámetro es un valor DECIMAL y otro parámetro es un valor DECIMAL o INTEGER, se comparan como valores DECIMAL. Si el otro argumento es un valor de FLOATING POINT, se comparan como valores de FLOATING POINT.
7. En otros casos, ambos parámetros se comparan como valores de FLOATING POINT.
8. Si un parámetro es una cadena y el otro parámetro es un valor INT, se comparan como valores FLOATING POINT (haciendo referencia al elemento 7)
  Por ejemplo, el tipo de f_phone es varchar. Si se utiliza f_phone in (098890) en la condición WHERE, se comparan dos parámetros como valores de FLOATING POINT. En este caso, no se puede utilizar el índice, lo que afecta al rendimiento de la base de datos.
  
  Si es f_user_id = '1234567', el número se compara directamente como una cadena de caracteres. Para más detalles, véase el punto 2.
Si es posible, asegúrese de que el número de sentencias SQL en una transacción debe ser lo más pequeño posible, no más de 5. Las transacciones largas bloquearán los datos durante mucho tiempo, generarán muchas cachés en MySQL y ocuparán muchas conexiones.
No utilice NATURAL JOIN.

NATURAL JOIN se utiliza para unir una columna implícitamente, lo que es difícil de entender y puede causar problemas. La sentencia NATURAL JOIN no se puede trasplantar.