Summary of MySQL usage specifications

1. InnoDB storage engine must be used

It has better CPU and IO performance, better backup and table locking mechanisms, and improved statistics and debugging efficiency.

In addition, as a system, InnoDB supports a variety of key features, the most important of which are transaction logging and row-level locking. The transaction log records the actual database transactions, but more importantly, data crash recovery and rollback.

InooDB-based IO can provide safer data protection and better performance. Additionally, in most cases, row-level locking can provide higher concurrency performance because users only lock the data they are writing, and reading data is never blocked.

2. Chinese comments must be added to data tables and data fields

It will be convenient for newcomers to understand and become familiar with it faster in the future; and it will be more readable. At the same time, the status field is marked with enumeration values ​​such as 0 for deletion and 1 for normal.

3. UTF8mb4 character set must be used

UTF8 is a universal character set. MB4 is an extension of UTF8, supporting new characters such as emoji.

4. Prohibit the use of stored procedures, views, triggers, events, joins, etc.

For high-concurrency big data Internet services, the architectural design idea is to "liberate the database CPU and transfer computing to the service layer." Databases are good at storage and indexing, and CPU computing is more reasonable at the business layer.

5. Do not store large files or photos

When there are many photos of people, the paging query speed slows down significantly. It used to respond within 1 second, but after adding the photo field, it takes about 4 to 5 seconds to respond.
Large files and photos are stored in the file system, and it is better to store URIs in the database

6. The table must have a primary key, such as an auto-increment primary key

a) Primary key increment and data row writing can improve insertion performance, avoid page splitting, reduce table fragmentation, and improve space and memory usage.
b) Use numeric primary keys. Shorter data types can effectively reduce the disk space of the index and improve the cache efficiency of the index.
c) Deleting a table without a primary key will cause the standby database to become blocked in a master-slave architecture in ROW mode.
d) Using business primary keys more often will make it more convenient to divide databases and tables.

7. Foreign keys are prohibited. If there are foreign key integrity constraints, application control is required.

Foreign keys will cause coupling between tables. Update and Delete operations will involve related tables, which will greatly affect SQL performance and even cause deadlock.

8. The field must be defined as NOT NULL and a default value must be provided

a) Columns with null values ​​make indexes/index statistics/value comparisons more complicated and more difficult for MySQL to optimize.
b) Null This type of data needs to be specially processed within MySQL, which increases the complexity of database record processing. Under the same conditions, when there are many empty fields in the table, the database processing performance will be greatly reduced.
c) Null values ​​require more storage space, whether it is a table or an index, each row in the null column requires additional space to identify it.
d) When dealing with null, only is null or is not null can be used, and the operation symbols such as =, in, <, <>, !=, and not in cannot be used.

For example: where name!='nx', if there are records where name is null, the query results will not include records where name is null.

9. Do not use TEXT and BLOB types

It will waste more disk and memory space. Unnecessary large-field queries will eliminate hot data, causing a sharp drop in memory hit rate and affecting database performance.

10. Do not use decimals to store currency

Use integers, as decimals can easily cause the money to not match.

11. You must use varchar(20) to store mobile phone numbers

a) When it comes to area codes or country codes, +-() may appear
b) Can mathematical operations be performed on mobile phone numbers?
c) VARCHAR can support fuzzy query, for example: like "138%"

12. ENUM is prohibited, TINYINT can be used instead

a) Adding new ENUM values ​​requires DDL operations
b) The actual internal storage of ENUM is integers. Did you think you defined a string?

13. About index design

(1) It is recommended to limit the number of indexes in a single table to less than 5.

More indexes are not always better! Indexes can improve efficiency but can also reduce efficiency.

Indexes can increase query efficiency, but they can also reduce the efficiency of insertion and update, and even query efficiency in some cases.

Because when the MySQL optimizer chooses how to optimize a query, it will evaluate each available index based on unified information to generate the best execution plan. If many indexes can be used for queries at the same time, it will increase the time for the MySQL optimizer to generate an execution plan and also reduce query performance.

(2) Do not create indexes on attributes that are updated very frequently and have low differentiation.

a) Updates will change the B+ tree, and indexing frequently updated fields will greatly reduce database performance
b) For attributes like "gender" that are not very discriminatory, it is meaningless to create an index. This cannot effectively filter data, and the performance is similar to that of a full table scan.

(3) When creating a composite index, the fields with high discrimination must be placed in the front.

Reason: Can filter data more effectively

14. About SQL usage specifications

(1) INSERT INTO t_xxx VALUES(xxx) is prohibited. The column attributes to be inserted must be specified.

Reason: It is easy to have program bugs after adding or deleting fields.

(2) It is prohibited to use functions or expressions on attributes of WHERE conditions.

Reason: SELECT uid FROM t_user WHERE from_unixtime(day)>='2019-10-09' will result in a full table scan. The correct way to write it is: SELECT uid FROM t_user WHERE day>= unix_timestamp('2019-10-09 00:00:00')

(3) Negative queries and fuzzy queries starting with % are prohibited

reason:
a) Negative query conditions: NOT, !=, <>, !<, !>, NOT IN, NOT LIKE, etc. will result in a full table scan
b) Fuzzy queries starting with % will result in a full table scan

(4) JOIN queries and subqueries are prohibited for large tables.

Reason: It will generate temporary tables, consume more memory and CPU, and greatly affect database performance

(5) OR conditions are prohibited and must be changed to IN queries

Reason: OR queries in old versions of MySQL cannot hit the index. Even if they can hit the index, why should the database consume more CPU to help implement query optimization?

(6) The application must capture SQL exceptions and handle them accordingly

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