White Paper: Just-in-Time Database Access Best Practices

SQL Review Guide

An online tool for DBA to generate the SQL review / DDL guideline.

Database Review Guide

Engine

  • Error

    Enforce InnoDB storage engine

    InnoDB is the default storage engine for MySQL that provides transaction support. It also provides better performance for high-concurrency and low-latency scenarios, and supports online data backup and recovery. It is the preferred choice for OLTP businesses. Suggestion error level: Error

Table

  • Error

    Enforce inclusion of primary key in a table

    Various data synchronization, comparison, and rollback tools require tables to have primary key. Suggestion error level: Error

  • Error

    Prohibit using foreign key constraints

    The advantages and disadvantages of foreign key are highly controversial. Using foreign key may significantly increase the difficulty of database changes, scalability (such as sharding), etc. And may even prevent the use of some tools. Therefore, another option is to implement foreign key constraints at the application layer. Suggestion error level: Warning

  • Error

    Restrict the naming format of tables to be deleted

    For example, by requiring the "_del" suffix, it can effectively prevent accidental deletions. Suggestion error level: Error

    Table name format (regex): _del$
  • Error

    Prohibit using partition table

    In some database engines, partitioned tables are not mature, and the use and maintenance are inconvenient. Therefore, it is more inclined to use manual data partitioning methods such as database and table sharding. Suggestion error level: Warning

  • Error

    Prevent the use of triggers on tables

    This rule restricts the usage of triggers on tables. Triggers can introduce complexity and potential performance issues to database operations. By disallowing triggers, the system can maintain a simpler and more predictable behavior. Suggestion error level: Warning

  • Error

    Table comment convention

    Configure whether the table requires comments and the maximum comment length.

    Require comment: true
    Require classification: false
    Max length: 64
  • Warning

    Disallow duplicate indexes

    This rule prohibits the creation of duplicate indexes on a table. Duplicate indexes consume extra storage space and can potentially reduce query performance. Suggestion error level: Warning

  • Warning

    Restrict the total length of text fields in a table

    This rule limits the amount of data a table can hold, preventing excessive storage usage.

    Maximum length: 1000
  • Warning

    Prohibit defining character set in table properties

    It is recommended to set the charset at the database level. Setting the charset at finer granularity can bring unnecessary complexities. Suggested error level: Error.

  • Warning

    Disallow DDL

    Configure which tables are prohibited from executing DDL. Suggestion error level: Warning

    Table names:
  • Warning

    Disallow DML

    Configure which tables are prohibited from executing DML. Suggestion error level: Warning

    Table names:
  • Warning

    Limit DDL operations on tables with a large number of rows

    Configure the maximum number of rows in tables for which DDL can be executed. Recommended error level: warning

    Max row count: 10000000
  • Warning

    Require charset

    The charset of the table must be specified. Suggestion error level: Warning

  • Warning

    Require collation

    The collation of the table must be specified. Suggestion error level: Warning

Statement

  • Error

    Prohibit using "SELECT *"

    SELECT * to fetch entire row data may cause unnecessary resource overhead and may also cause unexpected results in applications once the table adds or removes columns. Suggestion error level: Error

  • Error

    Enforce the presence of "WHERE" condition in SELECT statements

    Queries without WHERE clause may cause huge uncessary resource overhead. Suggestion error level: Error

  • Error

    Enforce the presence of "WHERE" condition in UDPATE/DELETE statements

    DMLs without WHERE clause may cause massive accidental data loss. Suggestion error level: Error

  • Error

    Prohibit using leading wildcard in filter conditions

    When using leading wildcard, such as "LIKE '%ABC'", the database optimizer cannot use fast index scan, and fallback to full table scan or full index scan, which may cause serious performance impact. Suggestion error level: Error

  • Error

    Prohibit explicit "COMMIT" statement

    In some cases, multiple statements are required to be included in a transaction committed by the system, in order to quickly rerun in case of partial failure. Therefore, explicit "COMMIT" is not allowed. Suggestion error level: Warning

  • Error

    Prohibit using CASCADE option for ON DELETE clauses

    The "CASCADE" option in 'ON DELETE' can cause a large number of dependent objects to be deleted or modified, which may cause unexpected results. Suggestion error level: Error

  • Error

    Prohibit using CASCADE when removing a table

    Using the "CASCADE" option when removing a table can cause a large number of dependent objects to be deleted or modified, which may cause unexpected results. Suggestion error level: Error

  • Warning

    Prohibit using "LIMIT" clause in DML statements

    If LIMIT is used in DML statements without an ORDER BY clause, the affected rows order are not fixed, which may cause data inconsistency between the primary and replica databases in some replication modes. Suggestion error level: Error

  • Error

    Prohibit using "ORDER BY" clause in "UPDATE" and "DELETE" statements

    Sorting operations are extremely resource-intensive, so for update and delete operations, it is recommended to use a deterministic filtering condition as much as possible instead of using ORDER BY and LIMIT. Suggestion error level: Error