Shulou(Shulou.com)06/01 Report--

I. basic norms

The table storage engine must use InnoDB

The table character set uses utf8 by default and utf8mb4 if necessary

Interpretation:

(1) Universal, no risk of garbled code, 3 bytes of Chinese characters and 1 byte of English

(2) utf8mb4 is a superset of utf8. When storing 4 bytes, such as emoticons, use it.

Prohibit the use of stored procedures, views, triggers, Event

Interpretation:

(1) it has a great impact on the performance of the database. For Internet business, what can be done by the site layer and the service layer should not be handed over to the database layer.

(2) debugging, debugging and migration are difficult, and the expansibility is poor.

It is forbidden to store large files, such as photos, in the database. Large files can be stored in the object storage system and the path in the database.

Prohibit database stress testing in online environment

Testing, development, and online database environments must be isolated

II. Naming rules

Library names, table names, and column names must be lowercase and separated by underscores

Interpretation: abc,Abc,ABC is to bury a hole for itself.

The library name, table name and column name must see the meaning of the name, and the length must not exceed 32 characters.

Interpretation: tmp,wushan who TM knows what these libraries are for

Library backups must be prefixed with bak and suffixed with date

Slave library must be suffixed with-s

The standby library must be suffixed with-ss

III. Specification for watch design

The number of tables in a single instance must be controlled within 2000.

The number of sub-meters in a single table must be controlled within 1024.

The table must have a primary key. It is recommended to use UNSIGNED integers as the primary key

Note: whether or not to use unsigned can refer to this question: https://www.cnblogs.com/blankqdb/archive/2012/11/03/blank_qdb.html

Potential pit: delete a table without a primary key. If it is a master-slave schema in row mode, the slave library will hang up.

The use of foreign keys is prohibited and should be implemented by the application if integrity is to be guaranteed

Interpretation: foreign keys couple tables and affect SQL performance such as update/delete, which may cause deadlock and easily become a database bottleneck in the case of high concurrency.

It is recommended that the fields with large fields and low access frequency be split into separate tables for storage and separate hot and cold data.

IV. Code for column design

Using tinyint/int/bigint according to business differentiation will consume 1-4-8 bytes respectively.

Use char/varchar according to business differentiation

Interpretation:

(1) Business scenarios with fixed or similar field length are suitable for using char, which can reduce fragmentation and achieve high query performance.

(2) for business scenarios with large differences in field length or less updates, it is suitable to use varchar and can reduce space.

Use datetime/timestamp according to business differentiation

Note: you can refer to this article: https://blog.51cto.com/hanchaohan/1751447

Interpretation: the former occupies 5 bytes, the latter 4 bytes, storage year uses YEAR, storage date uses DATE, storage time uses datetime

You must define the field as NOT NULL and set the default value. You can refer to this article:

Https://blog.csdn.net/u010737354/article/details/53081830

Https://www.cnblogs.com/balfish/p/7905100.html

Interpretation:

(1) the use of indexes, index statistics and values of NULL columns are more complex, and MySQL is more difficult to optimize.

(2) NULL needs more storage space.

(3) NULL can only use IS NULL or IS NOT NULL, and there is a big pit when = /! = / in/not in

Use INT UNSIGNED to store IPv4, not char (15)

Interpretation:

(1) if you use CHAR (15) to store it, you need 15 bytes (16 bytes for VARCHAR). It takes only 4 bytes to store IP in unsigned integers, which can save space.

(2) it is more efficient for range search.

SELECT * FROM test_han WHERE ip BETWEEN INET_ATON ('192.168.0.0') AND INET_ATON (' 192.168.0.255')

(3) SELECT * FROM test_han WHERE ip = INET_ATON ('192.168.0.0')

SELECT INET_ATON ('192.168.0.0')

SELECT INET_NTOA (3232235520)

You can refer to this article: http://www.voidcn.com/article/p-dnqfznsx-mc.html

Use varchar (20) to store mobile phone numbers instead of integers

Interpretation:

(1) when country codes are involved, characters such as + /-/ () may appear, such as + 86.

(2) the mobile phone number will not be used for mathematical operations.

(3) varchar can be queried vaguely, such as like '138%'.

Use TINYINT instead of ENUM

Interpretation: ENUM adds a new value to DDL operation

V. Index specification

Unique indexes are named using uniq_ [field name]

Non-unique indexes are named using idx_ [field name]

It is recommended that the number of indexes in a single table should be limited to 5.

Index reference: https://blog.51cto.com/hanchaohan/926580

Interpretation:

(1) with high concurrency on the Internet, too many indexes will affect write performance.

(2) when generating an execution plan, if there are too many indexes, it will degrade performance and may cause MySQL to choose less than the optimal index.

(3) for extremely complex query requirements, you can choose more suitable storage methods such as ES.

The number of combined index fields is not recommended to exceed 5

Interpretation: if the five fields can not greatly reduce the scope of row, there is probably a design problem.

It is not recommended to index fields that are updated frequently

Do not make a JOIN query unless necessary. If you want to do a JOIN query, the fields to be JOIN must be of the same type and indexed.

Interpretation: have you stepped on a pit that leads to a full table scan due to inconsistent JOIN field types?

Understand the leftmost prefix principle of the combined index and avoid repeated construction of the index. if you build (a), (b), ()

VI. SQL specification

Prohibit the use of select * and get only the necessary fields

Interpretation:

(1) select * increases the memory / bandwidth consumption of cpu/io/

(2) the specified field can be covered by the index effectively.

(3) the specified field query can guarantee no impact on the application when the table structure is changed.

Insert must specify a field to prohibit the use of insert into T values ()

Interpretation: specify field insertion to ensure that there is no impact on the application when the table structure changes

Implicit type conversion invalidates the index, resulting in a full table scan

Prohibit the use of functions or expressions in where conditional columns

Interpretation: resulting in missed index, full table scan

Prohibit negative queries and fuzzy queries starting with%

Interpretation: resulting in missed index, full table scan

Disable large table JOIN and subquery

OR on the same field must be rewritten. The value of IN,IN must be less than 50.

The application must catch SQL exceptions

Interpretation: convenient positioning of online problems

Description: the above recommendation is used for typical Internet services with large concurrency and large amount of data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.