Uso de SQL

Consulta de 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 como máximo 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.

  Por 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 tienen el mismo valor, solo se usa b en el índice (b,c) de t1.

  Si cambia t2.c=4 en la condición WHERE a t1.c=4, puede utilizar el índice completo. Esto puede ocurrir durante el diseño de redundancia de campo (desnormalización).

• Si no se requiere deduplicación, utilice UNION ALL en lugar de UNION.

  Como UNION ALL no deduplica ni ordena los datos, se ejecuta más rápido que UNION. Si no se requiere deduplicación, utilice UNION ALL preferentemente.

• Para implementar la consulta de paginación en el código, especifique que si count se establece en 0 no se ejecuten las siguientes sentencias de paginación.
• 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.

• Evite utilizar JOIN para unir más de tres tablas. Los tipos de datos de los campos que se van a unir deben ser los mismos.
• Durante la consulta de combinación de varias tablas, asegúrese de que los campos asociados tienen í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.
• Para consultar tablas ultragrandes, también debe cumplir con las siguientes reglas:
  • Para localizar sentencias de SQL lentas, habilite registros de consultas lentas.
  • No realice operaciones de columna, por ejemplo, SELECT id WHERE age+1=10. Cualquier operación en una columna, incluidas las funciones de tutorial de base de datos y las expresiones de cálculo, causará escaneos de tablas. Mueva las operaciones a la derecha del signo igual (=) durante la consulta.
  • Divida las sentencias más grandes en sentencias más pequeñas y más simples para reducir el tiempo de bloqueo y evitar el bloqueo de toda la base de datos.
  • No utilice SELECT*.
  • Cambie OR a IN. La eficiencia de OR está en el nivel n, mientras que la eficiencia de IN está en el nivel log(n). Intente mantener la cantidad de IN por debajo de 200.
  • Evite el uso de procedimientos almacenados y activadores en las aplicaciones.
  • Evite usar consultas en formato %xxx.
  • Evite utilizar JOIN e intente consultar una sola tabla siempre que sea posible.
  • Utilice el mismo tipo para la comparación, por ejemplo, de '123' a '123' o de 123 a 123.
  • Evite el uso de los operadores != o <>en la cláusula WHERE. De lo contrario, el motor no utilizará índices y en su lugar escaneará la tabla completa.
  • Para valores consecutivos, utilice BETWEEN en lugar de IN: SELECT id FROM t WHERE num BETWEEN1AND5.

Desarrollo de sentencias SQL

• Dividir sentencias SQL simples.

  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 la paginación. No se recomienda saltar la paginación para páginas grandes.

  • Ejemplo negativo: SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;

    Provoca un gran número de operaciones de E/S porque MySQL usa la política de lectura anticipada.

  • Ejemplo positivo: SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10;

    Método de paginación recomendado: Transfiera el valor de umbral a la última paginación.

• 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.

  Por 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 las interacciones con la base de datos, utilice lotes de sentencias SQL, por ejemplo, INSERT INTO … VALUES (*),(*),(*)....(*);. Intente mantener la cantidad de elementos de * por debajo de 100.
• No utilice procedimientos almacenados, que son difíciles de depurar, extender y trasplantar.
• No utilice activadores, planificadores de eventos ni vistas para la lógica del 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 el 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.

• Para tablas con decenas de millones o cientos de millones de registros de datos, se recomienda utilizar los siguientes métodos para mejorar la eficiencia de escritura de datos:
  1. Eliminar índices innecesarios.

     Cuando se actualizan los datos, también se actualizan los datos de índice. Para tablas con grandes cantidades de datos, evite crear demasiados índices, ya que esto puede ralentizar el proceso de actualización. Eliminar índices innecesarios.

  2. Insertar varios registros de datos por lotes.

     Esto se debe a que la inserción por lotes solo requiere una única solicitud remota a la base de datos.

     Por ejemplo:

     insert into tb1 values(1,'value1');
     insert into tb2 values(2,'value2');
     insert into tb3 values(3,'value3');

     Después de la optimización:

     insert into tb values(1,'value1'),(2,'value2'),(3,'value3');

  3. Al insertar varios registros de datos, controle manualmente las transacciones.

     Al controlar manualmente la transacción, se pueden fusionar múltiples unidades de ejecución en una sola transacción, lo que evita la sobrecarga de múltiples transacciones y garantiza la integridad y la consistencia de los datos.

     Por ejemplo:

     insert into table1 values(1,'value1'),(2,'value2'),(3,'value3');
     insert into table2 values(4,'value1'),(5,'value2'),(6,'value3');
     insert into table3 values(7,'value1'),(8,'value2'),(9,'value3');

     Después de la optimización:

     start transaction;
     insert into table1 values(1,'value1'),(2,'value2'),(3,'value3');
     insert into table2 values(4,'value1'),(5,'value2'),(6,'value3');
     insert into table3 values(7,'value1'),(8,'value2'),(9,'value3');
     commit;

     

     Tener demasiadas sentencias combinadas puede dar lugar a transacciones grandes, lo que bloqueará la tabla durante mucho tiempo. Evaluar las necesidades de servicio y controlar el número de estados en una transacción en consecuencia.

  4. Al insertar datos con claves principales, intente insertarlos en un orden secuencial de las claves principales. Puede utilizar AUTO_INCREMENT.

     La inserción de datos en un orden aleatorio de las claves principales puede provocar la separación de páginas, lo que puede afectar negativamente al rendimiento.

     Por ejemplo:

     Insertar datos en un orden aleatorio de claves principales: 6 2 9 7 2

     Insertar datos en orden secuencial de claves principales: 1 2 4 6 8

  5. Evite usar UUID u otras claves naturales, como números de tarjetas de identificación, como claves principales.

     Los UUID generados cada vez se desordenan, e insertarlos como claves primarias puede provocar la separación de páginas, lo que puede afectar negativamente al rendimiento.

  6. Evite modificar las claves primarias durante las operaciones de servicio.

     La modificación de las claves primarias requiere modificar la estructura del índice, lo que puede ser costoso.

  7. Reduzca la longitud de las claves principales tanto como sea posible.
  8. No utilice claves externas para mantener relaciones de claves externas. Use programas en su lugar.
  9. Operaciones de lectura y escritura separadas. Solicitudes de lectura directa para leer réplicas para evitar la inserción lenta causada por las E/S.