Mysql development specifications doc_mysql

Mysql development standard document bitsCN.com

Mysql development specification documentation

1. mysql database naming rules

2. table structure design and data type selection

3. development considerations

Www.bitsCN.com

1. mysql database naming rules

1.1 Naming rules:

Table/view/procedure/function/package:

Object Type abbreviation + function module abbreviation of lowercase letters + "_" + business meaning of lowercase words, such:

Primary key/foreign key/index:

Object Type abbreviation + "_" + table name abbreviation + field name

Object Type abbreviation:

Table: t

View: v

Procedure: p

Function: fn

Package: pk

Primary key: pk

Foreign key: fk

Index: idx

Www.bitsCN.com

Eg:

Trader background function: tbk_operate_log

Search function: ts_operate_log

Index command: idx_tablename_column

2. table structure design and data type selection

2.1 common data types:

Integer: tinyint, smallint, mediumint, int, bigint. Generally, int is enough.

Floating point: DECIMAL (M, D) is precise. DECIMAL is not recommended. we recommend that you multiply the DECIMAL value by a fixed number to convert it to integer storage, which saves storage space and does not bring any additional maintenance costs.

TINYINT> SMALLINT> MEDIUMINT> INT> BIGINT> DECIMAL (the storage space increases, but the performance decreases ).

Only int or bigint can be used for fields of the auto-incrementing sequence type, and the unsigned type is clearly identified. when the field exceeds 4.2 billion, the bigint type is used.

Character type: varchar, char, ENUM, SET, text

When selecting the type of character columns, do not use the TEXT data type. The lob type should be put to an end. The text type can be used only when the number of characters exceeds 20000, all fields that use the text type must be split from the original table and stored in another table separately with the original table primary key. The processing method determines that its performance is lower than that of char or varchar. For a fixed length field, we recommend that you use the CHAR type. the variable length field should use VARCHAR as much as possible, and only set the appropriate maximum length, rather than randomly giving a large maximum length limit, because of the different length ranges, MySQL also has different storage processes. For the status field, you can use char or ENUM to store it, because it can greatly reduce the storage space, and even if you need to add a new type, as long as it is added to the end, you do not need to recreate table data to modify the structure. What if it is to store pre-defined attribute data? You can try to use the SET type. even if there are multiple attributes, it can be easily used, while saving a lot of storage space.

Date and Time: commonly used TIMESTAMP, date

You can use datetime and timestamp to specify the exact time field (year, month, day, hour, minute, and second). if the time field only needs to be accurate to the day, the date type is used.

2.2 Basic table structure design:

2.2.1. The field uses not null:

If there is a difference between the MySQL NULL type and the Oracle NULL type, it will enter the index. if it is a composite index, this NULL type field will greatly affect the efficiency of the entire index. In addition, the processing of NULL in the index is special and will also occupy additional storage space.

2.2.2. proper splitting/redundancy

A. when a large field similar to TEXT or a large VARCHAR type exists in our table, this field is not required if most of us access this table, instead, we can split it into another independent table to reduce the storage space occupied by common data. One obvious advantage of this is that the number of data records that can be stored in each data block can be greatly increased, which reduces the number of physical IO operations and greatly improves the cache hit rate in the memory.

B. independent small fields that are frequently referenced and can only be obtained by joining two (or more) large tables. In this scenario, each Join operation is only used to obtain the value of a small field, A large number of Join records can cause a large number of unnecessary IO operations, which can be optimized by exchanging space for time. However, while redundancy is required, ensure that data consistency is not damaged, and ensure that redundant fields are updated at the same time.

2.2.3. control the table size

When mysql processes large tables (char tables> 1000 million rows, or int tables> million), the performance starts to decrease significantly. Therefore, it is necessary to control the capacity of a single table in different ways.

A: hierarchical data storage and historical archiving based on hot and cold data

B: Database/table/partition tables are used to control the capacity of a single table.

C: For the OLTP system, the resource consumption of a single transaction can be controlled. in case of a large transaction, the OLTP system can be disassembled. the mode is reduced to zero to avoid the impact of special cases on the public.

D: no more than 500 tables in a single database

E: the maximum number of fields in a single table is 50.

2.2.4. table definition parameters

Mysql> show create table utf8/G;

* *************************** 1. row ***************************

Table: utf8

Create Table: create table 'utf8 '(

'Id' int (11) not null AUTO_INCREMENT,

'Name' varchar (10) default null,

'Createtime' timestamp null default null,

'Strue 'tinyint (4) DEFAULT '1 ',

'A' decimal (10, 0) default null,

'B' decimal (10, 4) DEFAULT NULL,

'C' datetime default null,

'D 'date default null,

'E' enum ('A', 'B', 'C') DEFAULT NULL,

'F' set ('A', 'B', 'C') DEFAULT NULL,

Primary key ('id '),

Unique key 'name _ UNIQUE '('name '),

Unique key 'createtime _ unique' ('createtime ')

) ENGINE = InnoDB AUTO_INCREMENT = 7 default charset = utf8 ROW_FORMAT = dynamic

Engine: select an appropriate storage engine based on your business needs. select innodb for general transaction tables and myisam for read-only tables.

AUTO_INCREMENT: the initialization value of the auto-increment column.

CHARSET: define the character set of the table based on your business needs. for a multi-language environment, select utf8

ROW_FORMAT: row storage format

Note:

Mysql file storage format

Antelope

Barracuda

Mysql row storage format

Antelope: provides two row formats: compact and redundant. redundant is compatible with earlier versions. By default, the saved format is compact.

Barracuda: Innodb plugin introduces a new file format. The Barracuda file format contains two new row record formats: Dynamic and Compressed. Compressed has a good compression function for character data to improve efficiency.

2.2.5. create an appropriate index

Indexes require additional maintenance, access, and space costs. Therefore, you must be cautious when creating indexes, so that a single index can cover as many SQL statements as possible. tables with high update frequencies must control the number of indexes.

A. for a large and newer volume of data, Index maintenance costs will be very high. if there is very little demand for retrieval and there is no high requirement on retrieval efficiency, we do not recommend creating indexes, or minimize the number of indexes.

B. It is better to directly traverse faster data from a small amount of data to index retrieval, and it is not suitable for indexing.

C. try to make the search conditions as many as possible in the index, and filter all the data through the index as possible. the back table only retrieves additional data fields.

D. The order of Fields plays a vital role in the efficiency of the composite index. fields with better filtering results must be more advanced.

E. When the amount of data to be read accounts for a large proportion of the total data volume or the index filtering effect is not very good, the use of the index is not necessarily better than the full table scan.

F. in actual use, only one index can be used for one data access at a time, which must be noted during index creation, it does not mean that each condition in the Where clause in an SQL statement can be indexed.

G. do not use foreign keys in high-concurrency environments. it is too easy to generate deadlocks and should be guaranteed by the program.

H. character fields must use prefix indexes.

3. development considerations

3.1 Mathematical or function operations not performed on the index column

3.2 avoid large SQL and disassemble multiple small SQL statements

3.3 avoid using select *

3.4 replace or with in ()/union, and note that the number of in is less than 300.

3.5 avoid % prefix fuzzy prefix query

3.6 avoid subqueries

---- End ----

BitsCN.com