1、 Basic specifications

Article 1: InnoDB storage engine must be used

Interpretation: It supports transactions, row level locks, better concurrency performance, and CPU and memory cache page optimization to improve resource utilization

Article 2: The utf8 (utf8mb4) character set must be used

Interpretation: Wanguo Code, without transcoding and risk of garbled code, saves space. utf8mb4 is a superset of utf8. Due to the increase of mobile devices in recent years, emoji expressions and some uncommon Chinese characters will appear garbled code under utf8, so it needs to be upgraded to utf8mb4

Article 3: Chinese notes must be added to data tables and data fields

Interpretation: Who will know what the a1, a2 and a3 fields are in N years

Article 4: It is forbidden to use stored procedures, views, triggers, and events

Interpretation: High concurrency big data Internet business, framework The design idea is "liberation" data base CPU, transfer the calculation to the service layer " In the case of a large amount of concurrency, these functions are likely to drag the database to death, The business logic has better scalability when placed on the service layer, and can easily achieve "performance increase when adding machines" 。 Databases are good at storage and indexing. Let's move CPU computing up

Article 5: It is forbidden to store large files or photos

Interpretation: Why should the database do what it is not good at? How good it is to store large files and photos in the file system and URI in the database

2、 Naming convention

Article 1: Only allowed Use the intranet domain name instead of IP to connect to the database

Interpretation: Although IP access is faster and domain name access requires intranet dns, domain name is better for the expansion and migration of large databases

Article 2: Online environment, development environment test The intranet domain name of the environment database follows the naming specification

Business name: xxx
Online environment: dj.xxx.db
Development environment: dj.xxx.rdb
Test environment: dj.xxx.tdb
Add the - s identifier after the name of the slave database, and add the - ss identifier after the name of the standby database
Online slave library: dj.xxx-s.db
Online standby database: dj.xxx-sss.db

Article 3: Database name, table name and field name: lower case, underline style, no more than 32 characters, no mixed use of pinyin and English

Interpretation: See the name, know the meaning, and facilitate subsequent maintenance

Article 4: Table name t_xxx, non unique index name idx_xxx, unique index name uniq_xxx

Interpretation: See the name, know the meaning, and facilitate subsequent maintenance

3、 Table and field design specifications

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

Interpretation: Foreign keys will cause coupling between tables. Both update and delete operations will involve related tables, which will greatly affect the performance of SQL , or even cause deadlock. High concurrency is easy to cause database performance, High concurrency business scenario of big data: performance first

Article 2: The field must be defined as NOT NULL and the default value must be provided

Interpretation:
a) Null columns make the index/index statistics/value comparison more complex, which is more difficult to optimize for MySQL
b) This type of MySQL requires special processing internally to increase the complexity of database processing records; Under the same conditions, when there are many empty fields in the table, the processing performance of the database will be greatly reduced
c) The null value needs more empty storage. The null column in each row of the table or index needs additional space to identify
d) When processing null, you can only use is null or is not null, not=, in,<,<>,!= Not in These operating symbols. For example, where name!=' Shenjian ', if there are records with null name, the query results will not contain records with null name

Article 3: TEXT and BLOB types are prohibited

Interpretation: More disk and memory space will be wasted, A large number of unnecessary large field queries will eliminate hot data, resulting in a sharp drop in memory hit rate and affecting database performance

Article 4: It is prohibited to use decimal to store national currency

Interpretation: Once walked through such a hole, 100 yuan was amortized in three days, and (100/3) yuan was amortized every day, resulting in three 33.33 yuan. Later, when the reconciliation system was implemented, there were always a few pennies of money that were inconsistent, and I was depressed for a long time (it was not a few pennies, but the business side's questioning eyes that made the R&D very unhappy). Finally, I found that division was the cause of the problem
Solution: Use "minute" as the unit, so the database is an integer

Article 5: Varchar (20) must be used to store mobile phone numbers

Interpretation:
a) When the area code or country code is involved,+- () may appear
b) Does the mobile phone number do math?
c) Varchar can support fuzzy queries, such as like "138%"

Article 6: The use of ENUM is prohibited, and TINYINT can be used instead

Interpretation:
a) DDL operation is required to add a new ENUM value
b) The internal actual storage of ENUM is an integer. Do you think you define a string?

Article 7: A table must have a primary key, such as auto increment primary key

Interpretation:
a) The primary key is incremented, and data row writing can improve the insertion performance, avoid page splitting, and reduce the use of table fragments to increase space and memory
b) Select a shorter data type for the primary key. Innodb engine ordinary indexes will save the value of the primary key. A shorter data type 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 in row mode will cause the standby database to be tampered with

4、 Index Design Specification

Article 1: It is recommended to limit the single table index to 5

Interpretation: A good index design can improve your efficiency dozens or even hundreds of times, but too much is counterproductive

Article 2: The number of single index fields cannot exceed 5

Interpretation: When there are more than 5 fields, they can no longer effectively filter data

Article 3: It is prohibited to create indexes on attributes that are updated frequently and are not highly differentiated

Interpretation:
a) Updating will change the B+tree. Indexing frequently updated fields will greatly reduce database performance
b) "Gender" is an attribute with little discrimination. It is meaningless to build an index. It cannot filter data effectively. Its performance is similar to that of full table scanning

Article 4: To establish a composite index, the fields with high discrimination must be placed first

Interpretation: It can filter data more effectively

5、 SQL Usage Specification

Article 1: SELECT * is prohibited, only necessary fields are obtained, and description column attributes need to be displayed

Interpretation:
a) Reading unnecessary columns will increase CPU, IO, and NET consumption
b) Overwrite index cannot be used effectively
c) Using SELECT * is easy to cause program bugs after adding or deleting fields

Article 2: It is prohibited to use INSERT INTO t_xxx VALUES (xxx), and the column attribute of the specified insert must be displayed

Interpretation: It is easy to cause program bugs after adding or deleting fields

Article 3: Implicit attribute conversion is prohibited

Interpretation: SELECT uid FROM t_user WHERE phone=13812345678 will cause a full table scan, but cannot hit the phone index. Guess why?
The priority of int data type is higher than that of archer. This query will convert phone to int, so all data in the table need to be changed to int, so it must be scanned comprehensively
The phone is a varchar type, and the SQL statement carries an integer, so it will not hit the index. Just add a quotation mark:
SELECT uid FROM t_user WHERE phone='13812345678'

Article 4: It is prohibited to use functions or expressions on attributes of WHERE conditions

Interpretation: SELECT uid FROM t_user WHERE from unixtime (day)>='2017-02-15 'will cause full table scanning
The correct writing is: SELECT uid FROM t_user WHERE day>=unix_timestamp ('2017-02-15 00:00:00 ')

Article 5: It is prohibited to use JOIN query for large tables and sub query for large tables

Interpretation: Temporary tables will be generated, consuming more memory and CPU, which will greatly affect the database performance. Large tables refer to tables with more than 10 million data

Article 6: The OR condition is prohibited and must be changed to IN query

Interpretation: The OR query of the old version of MySQL cannot hit the index. Even if it can hit the index, why should the database use more CPU to help implement query optimization?

Article 7: Negative queries and fuzzy queries starting with% are prohibited

Interpretation:
a) Negative query criteria: NOT,!=,<>,!<,!> NOT IN, NOT LIKE, etc., will result in full table scanning
b) Fuzzy queries starting with% will result in full table scanning
Generally speaking, the WHERE filter condition does not only contain such a "negative query condition", but also other filter conditions. For example, to query orders other than the completed orders of Shen Jian (it is difficult to understand):
SELECT oid FROM t_order WHERE uid=123 AND status != 1;
The order table has 5000w data, but uid=123 will quickly filter the data volume to a small level (uid has established an index). At this time, it doesn't matter if you add a negative query condition, and the number of rows scanned will be very small
However, if you want to query all orders other than completed orders:
SELECT oid FROM t_order WHERE status != 1;
The CPU will be 100% immediately. The status index will become invalid, and the negative query will lead to a full table scan

Article 8: The application must catch SQL exceptions and handle them accordingly

Interpretation: convenient maintenance, timely "leak detection and filling"

Conclusion: Internet services with large amount of data and high concurrency will not be used, which will greatly affect the database performance.