MySQL Database 25 specifications Interpretation (Turn)

first, the basic norms

(1) must use the UTF8 character set

Interpretation: The universal code, no transcoding, no garbled risk, space saving (due to mobile device for the best use of UTF8MB4)

(2) 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.

(3) 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

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

(5) 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

(6) Non-unique index name idx_xxx, unique index name uniq_xxx

Third, table design code

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

(8) The number of single-table columns must be less than 30

(9) 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

(10) 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

(11) 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

(12) 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

(13) Prohibit the use of fractional storage currency (can be counted as the unit, display and calculation of x100,... ）

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

(14) 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

(15) Single table index recommended control within 5

(16) 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.

(17) 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

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

(19) 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

(20) 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

(21) 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)

The int data type takes precedence over Archer, and the query converts the phone to int, so you need to change all the data in the table to int, so you must scan all

(22) 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 ')

(23) 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

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

MySQL Database 25 specifications Interpretation (Turn)