• The priority is to perform complex SQL calculations or service logic at the service layer rather than in SQL. Use a proper paging method to improve the efficiency of paging. Do not use skipping paging.
• Execute UPDATE statements in transactions based on primary keys or unique keys to avoid gap locks and deadlocks.
• Do not use foreign keys and cascading updates, and handle foreign key relationships at the application layer.
• Reduce IN operations. The number of collection elements should not exceed 1,000.
• For batch data operations, use batches of SQL statements as appropriate. For example, use insert into... values statement to insert data in batch. The number should be within 100.
• Do not use stored procedures, which are difficult to debug, extend, and transplant.
• Do not use triggers, event schedulers, and views to implement service logic, which must be processed at the service layer to avoid logical dependency on the database.
• Do not use implicit type conversions. You are advised to learn the type conversion rules to ensure that the data types on both sides of the comparison operator are consistent and avoid affecting query performance.
• If possible, the number of SQL statements in a transaction should be no more than five as much as possible. This can avoid locking data for a long time, memory buffer problems, and occupying too many connections.
• Use covering indexes to perform query operations and avoid back-to-table operations.