Interpretation of 30 military regulations in 58 military databases

Time:2021-12-31

The official account is reproduced.58 home Shen Jian, the original link pokes here

The following is a reprint:

Applicable scenario of military regulations: Internet business with large amount of concurrency and data

Military regulations: Introduction

Interpretation: explain the reasons. Interpretation is more important than military regulations

1、 Basic specification

(1) The InnoDB storage engine must be used
Interpretation: support transactions, row level locks, better concurrency performance, CPU and memory cache page optimization, resulting in higher resource utilization

(2) The utf8 character set must be used

Interpretation: universal code, no transcoding, no risk of garbled code, saving space

(3) Chinese notes must be added to data tables and data fields

Interpretation: who TM knows what the R1, R2 and R3 fields are after n years

(4) Prohibit the use of stored procedures, views, triggers, events

Interpretation: for the Internet business with high concurrency and big data, the architecture design idea is to "liberate the database CPU and transfer the calculation to the service layer". In the case of large concurrency, these functions are likely to drag the database to death. Putting the business logic into the service layer has better scalability and can easily realize "adding machines and adding performance". Database is good at storage and indexing. CPU calculation should be moved up

(5) Do not store large files or photos

Interpretation: why let the database do what it is not good at? Large files and photos are stored in the file system. How good it is to store URIs in the database

2、 Naming conventions

(6) Only intranet domain names are allowed to connect to the database instead of IP

(7) The intranet domain names of online environment, development environment and test environment databases follow the naming standard

Business Name: XXX

Online environment: DJ xxx. db

Development environment: DJ xxx. rdb

Test environment: DJ xxx. tdb

The slave database is marked with - s after the name, and the standby database is marked with - SS after the name

Online slave Library: DJ xxx-s.db

Online backup Library: DJ xxx-sss. db

(8) Database name, table name and field name: lowercase, underline style, no more than 32 characters. You must see the name and know the meaning. It is forbidden to mix Pinyin and English

(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 single table columns must be less than 30

(12) The table must have a primary key, such as self incrementing primary key

Interpretation:

a) Increasing the primary key and writing data rows can improve the insertion performance, avoid page splitting, reduce table fragments, and improve the use of space and memory

b) A shorter data type should be selected for the primary key. The InnoDB engine will save the value of the primary key for ordinary indexes. A shorter data type can effectively reduce the disk space of the index and improve the cache efficiency of the index

c) When a table without a primary key is deleted, the primary and secondary schema in row mode will cause the secondary database to be tampered

(13) The use of foreign keys is prohibited. If there are foreign key integrity constraints, application control is required

Interpretation: foreign keys will lead to the coupling between tables. Both update and delete operations will involve associated tables, which will greatly affect the performance of SQL and even cause deadlock. In the case of high concurrency, it is easy to cause database performance. In the business scenario of high concurrency of big data, database performance takes priority

4、 Field design specification

(14) You must define the field as not null and provide a default value

Interpretation:

a) Null columns make index / index statistics / value comparison more complex and more difficult to optimize for MySQL

b) Null requires special processing inside mysql, which increases 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) Null values need more storage space, and null columns in each row in the table or index need additional space to identify

d) When processing null, only is null or is not null can be used, but =, in, <, < >,! = Not in these operation symbols. For example: where name! = " If there are records with null name value, the query result will not contain records with null name value

(15) Text and blob types are prohibited

Interpretation: it will waste more disk and memory space. A large number of unnecessary large field queries will eliminate hot data, resulting in a sharp reduction in memory hit rate and affecting database performance

(16) Do not use decimal to store currency

Interpretation: use integers. Decimals can easily lead to money mismatch

(17) You must use varchar (20) to store the phone number

Interpretation:

a) When the area code or country code is involved, + - ()

b) Will the mobile phone number do mathematical calculations?

c) Varchar can support fuzzy queries, such as like "138%"

(18) Enum is prohibited and tinyint can be used instead

Interpretation:

a) DDL operation is required to add a new enum value

b) The actual internal storage of enum is an integer. Do you think you define a string?

5、 Index design specification

**(19) Single table indexes are recommended to be controlled within 5**

**(20) The number of single index fields cannot exceed 5**

Interpretation: when there are more than 5 fields, it can no longer effectively filter data

(21) it is forbidden to establish an index on attributes that are updated very frequently and are not highly distinguished

Interpretation:

a) Updating will change the B + tree, and indexing the frequently updated fields will greatly reduce the database performance

b) Gender, an attribute with little differentiation, is meaningless to establish an index. It can not effectively filter data. Its performance is similar to that of full table scanning

(22) when establishing a combined index, the field with high discrimination must be put in front

Interpretation: it can filter data more effectively

6、 SQL usage specification

**(23) select * is prohibited. Only the necessary fields are obtained, and the Description column attribute needs to be displayed**

Interpretation:

a) Reading unnecessary columns will increase CPU, IO and net consumption

b) Cannot effectively use overlay index

c) Using select * is prone to program bugs after adding or deleting fields

(24) the use of insert into t_xxx values (xxx) is prohibited, and the specified inserted column attribute must be displayed

Interpretation: it is easy to appear program bug after adding or deleting fields

(25) attribute implicit conversion is prohibited

Interpretation: select uid from t_ User where phone = 13812345678 will cause a full table scan, but the phone index cannot be hit. Guess why? (this online problem has occurred more than once)

(26) it is forbidden to use functions or expressions on the attributes of where conditions

Interpretation: select uid from t_ user WHERE from_ Unixtime (day) > ='2017-02-15 'will cause full table scanning

The correct wording is: select uid from t_ user WHERE day>= unix_ timestamp('2017-02-15 00:00:00')

(27) negative queries and fuzzy queries beginning with% are prohibited

Interpretation:

a) Negative query criteria: not,! =, < >,! <,! > Not in, not like, etc. will cause full table scanning

b) Fuzzy queries starting with% will cause full table scanning

(28) join query and sub query are prohibited for large tables

Interpretation: it will generate temporary tables, consume more memory and CPU, and greatly affect the database performance

(29) 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 consume more CPU to help implement query optimization?

(30) the application must catch SQL exceptions and handle them accordingly

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

Recommended Today

Redis featured Q & A

Redis data type type brief introduction characteristic scene String (string) Binary security It can contain any data, such as JPG pictures or serialized objects. One key can store up to 512M It can be used to do the simplest data. It can cache a simple string or a JSON format string. The implementation of redis […]