58 Home Database 30 rule

Military Application Scenario : Internet business with large amount of concurrency and large data volume

Military: Introduction Content

Interpretation : Explain the reason, interpretation is more important than military

First, the basic norms

(1) InnoDB storage engine must be used

Interpretation: Support transaction, row-level lock, concurrency performance better, CPU and Memory cache page optimization make resource utilization higher

(2) must use the UTF8 character set

Interpretation: Universal code, no transcoding, no garbled risk, space-saving

And the DBA leader confirms that the utf8mb4 character Set is used by default for the new library .

This is thanks to the reminder, UTF8MB4 is UTF8 super set, emoji expression and some of the uncommon characters in UTF8 will be garbled, it needs to upgrade to UTF8MB4.

The default reason for using this character set is: "Standard, Universal code, no transcoding, no garbled risk", does not "save space."

(3) Data table, data fields must be added in Chinese comments

Interpretation: N years later who TM knows what this R1,R2,R3 field is for?

There should be no doubt about this.

However, some friends suggest that adding comments will make it easier for hackers to suggest that "comments are written in documents, documents and databases are updated synchronously". This suggestion is not very reliable in terms of experience:

(1) Can not be afraid of bugs do not write code, afraid of hackers do not write comments, right?

(2) Document synchronization Update is not too realistic, or to write the comments, code readability is more feasible, the Internet company's document management? The students who have stayed in the Internet company are expected to know.

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

Interpretation: High concurrency Big Data internet business, the architecture design idea is "to liberate the database CPU, transfer the computation to the service layer", the concurrency is large, these functions are likely to drag the database, the business logic to the service layer with better scalability, can easily achieve "add machine on the performance". The database is good at storing and indexing, CPU computing or moving up.

(5) Prohibit storing large files or large photos

Explanation: Why should we let the database do something that it is not good at? Large files and photos stored in the file system, the database to save the URI how good

Second, naming norms

(6) Only intranet domain name is allowed, not IP connection database

(7) Online environment, development environment, test environment database intranet domain name follow the naming convention

Business Name: XXX

Online Environment: Dj.xxx.db

Development environment: DJ.XXX.RDB

Test environment: DJ.XXX.TDB

from the library after the name with the-s flag, the repository after the name plus-SS identity

Online from library: dj.xxx-s.db

Online Library: dj.xxx-sss.db

(8) Library name, table name, field name: lowercase, underline style, no more than 32 characters, must see the name of the meaning, prohibit pinyin mixed in English

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

Third, table design code

(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 a self-increment primary key

Interpretation:

A) primary key increment, data row write can improve insert performance, can avoid page splitting, reduce table fragmentation to improve space and memory usage

b) Primary key to select a shorter data type, the InnoDB engine Normal index will hold the value of the primary key, the shorter data type can effectively reduce the index disk space, improve the efficiency of the index cache

c) Table deletion without a primary key, the master-slave architecture in row mode causes the repository to be compacted

(13) Prohibit the use of foreign keys, if there are foreign key integrity constraints, need to apply program control

Interpretation: A foreign key causes the table to be coupled to the table, and the update and delete operations involve the associated tables, which can affect the performance of SQL and even cause deadlocks. High concurrency results in database performance, high-data concurrency Scenario database usage takes precedence over performance

Four, field design specifications

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

Interpretation:

A) NULL columns make index/index statistics/value comparisons more complex and more difficult to optimize for MySQL

b) Null this type of MySQL internally requires special processing to increase the complexity of database processing records; Under the same conditions, when there are more empty fields in the table, the processing performance of the database will be reduced a lot

c) Null values require more storage space, and columns that are null in each row of the table and in the index require additional room to identify

d) When processing null, only is NULL or is not NULL, and cannot be used in the operations symbols =, IN, <, <>,! =, not. For example: where name!= ' Shenjian ', if there is a record with a null value for name, the query result will not contain a record with the name null value

(15) Prohibit the use of text, BLOB type

Interpretation: Will waste more disk and memory space, non-essential large size query will eliminate the hot data, resulting in a sharp reduction in memory hit rate, affecting database performance

(16) Prohibit the use of fractional storage currency

Interpretation: Using integers, decimals easily lead to money not on

(17) must use varchar (20) to store mobile phone number

Interpretation:

A) refers to the area code or country code, may appear + + ()

b) Does the cell phone number do mathematical calculations?

c) varchar can support fuzzy queries, for example: like "138%"

(18) prohibit the use of enum, can use tinyint instead

Interpretation:

A) Add a new enum value to do the DDL operation

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

V. Index DESIGN SPECIFICATIONS

(19) Single table index recommended control within 5

(20) The number of single indexed fields does not allow more than 5

Interpretation: When there are more than 5 fields, the actual effect of filtering data is not effective.

(21) Prohibit indexing on highly updated, low-sensitivity attributes

Interpretation:

A) The update changes the B + tree, and the updated field indexing can significantly reduce database performance

b) "Gender" is such a small-scale attribute, indexing is meaningless, not effective filtering data, performance and full-table scan similar

(22) To set up a composite index, you must put the high-sensitivity field in front

Interpretation: The ability to filter data more effectively

Vi.. SQL Usage Specification

(23) Prohibit the use of select *, only get the necessary fields, need to display the Description column properties

Interpretation:

A) reading unwanted columns increases CPU, IO, net consumption

b) cannot effectively use the overlay index

c) Use SELECT * to easily add or remove fields after a program bug occurs

(24) Disable INSERT INTO t_xxx VALUES (XXX), you must display the specified column properties

READ: Easy to add or delete fields after the program bug

(25) Disable the use of attribute implicit conversions

Interpretation: The SELECT uid from T_user WHERE phone=13812345678 causes a full table scan and cannot hit the phone index, guess why? (This line problem has not only occurred once)

(26) Prohibit the use of functions or expressions on the properties of a Where condition

Interpretation: SELECT uid from T_user WHERE from_unixtime (day) >= ' 2017-02-15 ' will cause full table scan

The correct notation is: SELECT uid from T_user WHERE day>= unix_timestamp (' 2017-02-15 00:00:00 ')

(27) Non-negative queries, and a fuzzy query starting with%

Interpretation:

A) Negative query conditions: not,! =, <>,!<,!>, not in, no like, etc., resulting in a full table scan

b) The fuzzy query at the beginning of the% will result in a full table scan

This military dispute is relatively large, some users feedback does not do a lot of business implementation, a little explanation:

In general, where filter conditions do not take such a "negative query condition", there will be other filtering conditions, for example: query Shenjian completed orders outside the order (a good mouthful):

SELECT oid from T_order WHERE uid=123 and status! = 1;

Order table 5000w data, but Uid=123 will quickly filter the amount of data to a very small number of levels (UID indexed), then the next negative query condition does not matter, scan of the line itself is very small.

However, if you want to query all orders other than the completed order:

SELECT oid from T_order WHERE status! = 1;

That's it, right now. Cpu100%,status indexes are invalidated and negative queries cause full table scans.

(28) prohibit large tables using a join query, prohibit large tables using subqueries

Interpretation: can generate temporary tables, consume more memory and CPU, greatly affect database performance

(29) Prohibit the use or condition, must change in query

Interpretation: The old version of MySQL or query is not hit index, even if you can hit the index, why should the database consume more CPU to help implement query optimization?

(30) The application must capture the SQL exception and have the appropriate processing

Above the military, although applied to the Internet industry, but there are many rules, or universal!

Turn from: 58 Shen Jian Architect's Road

58 Home Database 30 rule