Detailed explanation of MySQL 30 military rules

1. Basic Specifications

(1) InnoDB storage engine must be used Interpretation: Supports transactions, row-level locks, better concurrency performance, CPU and memory cache page optimization to achieve higher resource utilization

(2) UTF8 character set must be used for interpretation: Unicode, no need for transcoding, no risk of garbled characters, and space saving

(3) Data tables and data fields must be annotated in Chinese: N years later, who knows what the r1, r2, and r3 fields are for?

(4) Prohibit the use of stored procedures, views, triggers, and events
Interpretation: For high-concurrency big data Internet businesses, the architectural design idea is to "liberate the database CPU and transfer computing to the service layer." When the concurrency is large, these functions are likely to slow down the database. Putting business logic in the service layer has better scalability and can easily achieve "increasing performance by adding machines." Databases are good at storage and indexing, so CPU computing should be moved up

(5) Do not store large files or large photos Interpretation: Why let the database do something it is not good at? Large files and photos are stored in the file system, it is better to store URIs in the database

2. Naming Standards

(6) Only the intranet domain name is allowed to connect to the database, not the IP address.

(7) The domain name of the online environment, development environment, and test environment database intranet follows the naming convention Business name: xxx
Online environment: dj.xxx.db
Development environment: dj.xxx.rdb
Test environment: dj.xxx.tdb
The slave database has -s added after its name, and the standby database has -ss added after its name. Online slave database: dj.xxx-s.db
Online backup database: dj.xxx-sss.db

(8) Database name, table name, field name: lowercase, underline style, no more than 32 characters, must be self-explanatory, and the combination of pinyin and English is prohibited

(9) Table name t_xxx, non-unique index name idx_xxx, unique index name uniq_xxx

3. Table design specifications

(10) The number of single instance tables must be less than 500

(11) The number of columns in a single table must be less than 30

(12) The table must have a primary key, for example, an auto-increment primary key interpretation:
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) The primary key should have a shorter data type. The Innodb engine's normal index will store the primary key value. A shorter data type can effectively reduce the disk space of the index and improve the index's cache efficiency.
c) Deleting a table without a primary key will cause the standby database to become blocked in a master-slave architecture in row mode.

(13) 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 may even cause deadlock. High concurrency can easily cause database performance problems. In big data and high concurrency business scenarios, database performance is the top priority.

4. Field design specifications

(14) The field must be defined as NOT NULL and a default value must be provided:
a) Null columns 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 in 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. The null column in each row in the table or index requires additional space to identify
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!='shenjian', if there is a record with a null value for name, the query result will not include the record with a null value for name

(15) Prohibit the use of TEXT and BLOB types. Interpretation: This 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.

(16) Do not use decimals to store currency. Interpretation: Use integers. Decimals can easily cause money to not match.

(17) You must use varchar(20) to store mobile phone numbers. Interpretation:
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%"

(18) 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?

5. Index design specifications

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

(20) The number of fields in a single index is not allowed to exceed 5. Interpretation: When there are more than 5 fields, it can no longer effectively filter data.

(21) It is prohibited to create index interpretations 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.

(22) When creating a combined index, the fields with high discrimination must be placed in the front. Interpretation: This can filter data more effectively.

6. SQL usage specifications

(23) Do not use SELECT *. Only necessary fields are obtained. The column attributes need to be displayed:
a) Reading unnecessary columns will increase CPU, IO, and NET consumption
b) Covering indexes cannot be used effectively
c) Using SELECT * is prone to program bugs after adding or deleting fields

(24) It is forbidden to use INSERT INTO t_xxx VALUES(xxx). The attributes of the specified inserted columns must be displayed. Interpretation: It is easy to cause program bugs after adding or deleting fields.

(25) Implicit attribute conversion is prohibited. Interpretation: SELECT uid FROM t_user WHERE phone=13812345678 will result in a full table scan and will not hit the phone index. Guess why? (This online problem has occurred more than once)

(26) It is forbidden to use functions or expressions on the attributes of the WHERE condition. Interpretation: SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' will result in a full table scan. The correct way to write it is: SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

(27) Negative queries and fuzzy queries starting with % are prohibited:
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

(28) JOIN queries and subquery interpretation for large tables are prohibited: temporary tables will be generated, consuming more memory and CPU, greatly affecting database performance

(29) OR conditions are prohibited and must be changed to IN queries. Interpretation: 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?

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

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.