Summary of MySQL database usage specifications

Introduction:

Regarding MySQL database specifications, I believe everyone has read some documents. This article provides a detailed summary of database-related specifications, starting with library and table naming design specifications, and then index design specifications, and then provides suggestions on SQL writing. I believe these specifications are applicable to most companies, and I hope everyone can use our database in accordance with the specifications so that our database can achieve higher performance.

About the library:

1. [Mandatory] The library name must be within 32 characters, and all English characters must be lowercase.

2. [Mandatory] The name format of the library is: business system name_subsystem name.

3. [Mandatory] Library names can only use English letters, numbers, and underscores, and must start with an English letter.

4. [Mandatory] The character set must be explicitly specified when creating a database, and the character set can only be utf8 or utf8mb4. Example of creating a database SQL: Create database db1 default character set utf8;

5. [Suggestion] The temporary database and table names should be prefixed with tmp_ and suffixed with the date. The backup database and table names should be prefixed with bak_ and suffixed with the date.

About Table

1. [Mandatory] The names of tables and columns must be within 32 characters. Table names can only use letters, numbers, and underscores, all in lowercase.

2. [Mandatory] The table name must be strongly related to the module name. Table names used in the same module should use a unified prefix as much as possible.

3. [Mandatory] When creating a table, you must explicitly specify the character set as utf8 or utf8mb4.

4. [Mandatory] Avoid using keywords (such as type, order, etc.) in column names.

5. [Mandatory] When creating a table, you must explicitly specify the table storage engine type. If there is no special requirement, InnoDB is always used.

6. [Mandatory] A comment must be included when creating a table.

7. [Mandatory] For large tables with more than 1 million rows, alter table operations must be reviewed by the DBA and executed during off-peak hours. Multiple alter operations must be integrated together.

Because alter table will generate a table lock, blocking all writes to the table during the period, which may have a significant impact on the business.

8. [Suggestion] Regarding the primary key when creating a table: the table must have a primary key

(1) The primary key is required to be id, of type int or bigint, and auto_increment. It is recommended to use unsigned type.

(2) Do not set the field that identifies the subject of each row in the table as the primary key. It is recommended to set it to other fields such as user_id, order_id, etc., and create a unique key index.

Because if it is set as the primary key and the primary key value is inserted randomly, it will cause internal page splitting and a large amount of random I/O in InnoDB, resulting in performance degradation.

9. [Suggestion] Core tables (such as user tables) must have a row data creation time field create_time and a last update time field update_time to facilitate problem checking.

10. [Suggestion] All fields in the table should have NOT NULL attributes as much as possible, and the business can define DEFAULT values ​​as needed.

This is because using NULL values ​​will cause each row to occupy additional storage space, data migration is prone to errors, and aggregate function calculation results will deviate.

11. [Suggestion] The intermediate table is used to store the intermediate result set, and its name must start with tmp_. The backup table is used to back up or capture a snapshot of the source table. The name must start with bak_. The intermediate tables and backup tables are cleaned up regularly.

12. [Demonstration] A more standardized table creation statement:

: : : : : : : : : : : : : : :

About Index

1. [Mandatory] The primary key of the InnoDB table must be id int/bigint auto_increment, and the primary key value cannot be updated.

2. [Mandatory] For InnoDB and MyISAM storage engine tables, the index type must be BTREE.

3. [Recommendation] The name of the primary key should start with pk_, the unique key should start with uniq_ or uk_, and the common index should start with idx_. All names should be in lowercase format, with the name or abbreviation of the field as the suffix.

4. [Suggestion] The number of indexes on a single table cannot exceed 8.

5. [Suggestion] When creating an index, consider creating a joint index and put the most discriminative field at the front. For example, the distinctiveness of the userid column can be calculated by select count(distinct userid).

6. [Suggestion] In the SQL for multi-table joins, ensure that the join columns of the driven table have indexes to maximize the join execution efficiency.

7. [Suggestion] When creating a table or adding an index, ensure that there are no redundant indexes in the table.
For MySQL, if key(a,b) already exists in the table, key(a) is a redundant index and needs to be deleted.

SQL Writing

1. [Mandatory] The SELECT statement on the program side must specify the specific field name. It is forbidden to write *.

2. [Mandatory] Specify the specific field name in the program-side insert statement. Do not write it as insert into t1 values(…).

3. [Mandatory] Except for static tables or small tables (with less than 100 rows), DML statements must have a where condition and use index search.

4. [Mandatory] The field types on both sides of the equal sign in the where condition must be consistent, otherwise the index cannot be used.

5. [Mandatory] In the WHERE clause, you are not allowed to use only the fully fuzzy LIKE condition for search. You must have other equality or range query conditions. Otherwise, the index cannot be used.

6. [Mandatory] Do not use functions or expressions in index columns; otherwise, the index cannot be utilized. For example, where length(name)='Admin' or where user_id+2=10023.

7. [Suggestion] insert into…values(XX),(XX),(XX)… The value of XX here should not exceed 5000.
Although too many values ​​can quickly go online, it will cause master-slave synchronization delays.

8. [Suggestion] Do not use UNION in the SELECT statement. It is recommended to use UNION ALL, and the number of UNION clauses is limited to 5.
Because union all does not require deduplication, it saves database resources and improves performance.

9. [Mandatory] Cross-DB join statements are prohibited.

10. [Suggestion] It is not recommended to use subqueries. It is recommended to split the subquery SQL into multiple queries combined with the program, or use join instead of subqueries.

11. [Suggestion] In an online environment, do not join more than 5 tables.

12. [Suggestion] In multi-table joins, try to select a table with a smaller result set as the driving table to join other tables.

13. [Suggestion] When operating data in batches, it is necessary to control the transaction processing interval and perform necessary sleep.

14. [Suggestion] The transaction should contain no more than 5 SQL statements because a transaction that is too long will cause data to be locked for a long time, and cause problems such as excessive consumption of MySQL internal cache and connections.

15. [Suggestion] Update statements in transactions should be based on primary keys or unique keys as much as possible, such as update … where id=XX;
Otherwise, a gap lock will be generated, which will expand the locking range internally, causing system performance to degrade and deadlock to occur.

16. [Suggestion] Reduce the use of order by, communicate with the business to avoid sorting if possible, or move the sorting to the program end. Statements such as order by, group by, and distinct consume a lot of CPU, and the CPU resources of the database are extremely precious.

17. [Suggestion] For SQL statements such as order by, group by, and distinct, try to use indexes to directly retrieve sorted data. For example, where a=1 order by b can use key(a,b).

18. [Suggestion] For statements that include order by, group by, and distinct, please keep the result set filtered by the where condition within 1,000 rows, otherwise the SQL will be very slow.

The above is the detailed content of MySQL database usage specifications. For more information about MySQL usage specifications, please pay attention to other related articles on 123WORDPRESS.COM!