Database Review Guide
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
Various data synchronization, comparison, and rollback tools require tables to have primary key. Suggestion error level: Error
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
For example, by requiring the "_del" suffix, it can effectively prevent accidental deletions. Suggestion error level: Error
Table name format (regex): _del$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
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
Configure whether the table requires comments and the maximum comment length.
Require comment: trueRequire classification: falseMax length: 64This 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
This rule limits the amount of data a table can hold, preventing excessive storage usage.
Maximum length: 1000It is recommended to set the charset at the database level. Setting the charset at finer granularity can bring unnecessary complexities. Suggested error level: Error.
Configure which tables are prohibited from executing DDL. Suggestion error level: Warning
Table names:Configure which tables are prohibited from executing DML. Suggestion error level: Warning
Table names:Configure the maximum number of rows in tables for which DDL can be executed. Recommended error level: warning
Max row count: 10000000The charset of the table must be specified. Suggestion error level: Warning
The collation of the table must be specified. Suggestion error level: Warning
Statement
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
Queries without WHERE clause may cause huge uncessary resource overhead. Suggestion error level: Error
DMLs without WHERE clause may cause massive accidental data loss. Suggestion error level: Error
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
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
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
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
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
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