Considerations when designing tables for MySQL

Description: This article is a simple summary of some of the requirements of the project process, mainly for the personal reference ... First, table design

1.

When you create a table structure, the table name, field needs to be known, do not use pinyin

CREATE TABLE ' TB_ABC ' (

' id ' int (ten) unsigned not null auto_increment comment " Write your comment here ",

' Name ' varchar (a) NOT null comment ' write your comment here ',

Engine=innodb default charset=utf8mb4  comment ' write your notes here ';

Be sure to have a primary key! Be sure to have a primary key !

Plastic self-increment (typically with no business-meaning ID) (with Mycat if there is a self-increment function, you do not need to define the self-increment in the table structure).

Do not define the character set yourself in the field

Fields and tables should have comment

2.

The first is clearly defined: functions, triggers, views, foreign keys; all logical business implementations, as far as possible without using stored procedures,

A) must use the InnoDB engine, do not allow the use of MyISAM.

b) the business table must be designed with date fields, Createitme and LastUpdateTime

Update statements for each table must contain updates to UpdateTime

All fields are recommended as NOT NULL, and the time type must be NOT NULL

c) must have a primary key, the primary key as far as possible to use the self-increment field type, the recommended type is int or bigint type.

d) fields that require multiple table joins, and the data types remain absolutely consistent.

e) When the number of fields in the table is large, divide the table into two tables, one as the condition query table and one as the detail table.

f) It is recommended to use the tinyint type when the type of the field is an enumerated type or a Boolean type. (no char or varchar)

3.

Be sure to reshape the ID as the primary key (table with no primary key has a serious performance problem)

The shorter the field type, the better.

Common field types (text and blobs are not normally allowed), and if necessary, split into separate tables

Try not to use default NULL, where the field is defined as: NOT NULL default ' reasonable defaults '

4.

Why the shorter the field type is better (can be used in shaping as far as possible, such as tiny instead of char (1)), etc.

1> Less storage space

2> less disk IO

3> Less Network IO

4> less MySQL compute space

5> less app computing space

6> plastic storage and operation cost is smaller than character type

If there is a text field, it is best to divide the table. (essentially, not MySQL is not suitable for storing text, but in too many cases we expect MySQL to be more efficient in providing small data query/transaction processing)

Number of table fields to few but good

5.

Why there must be a self-augmentation shaping primary key, which generally has no business meaning;

6.

Why not use default NULL as much as possible?

The 1> index does not include a null value. Affects the statistics of the index, affecting the optimizer's judgment.

The 2> composite index is invalid for this composite index as long as one column contains a null value.

3> so we do not let the default value of the field be NULL when the database is designed.

field unification plus NOT null default ' reasonable defaults '

7.

FAQ one--int (10) and int (2) Difference

Unlike char (x) and varchar (x), x in int (x) represents the width of the integer (tinyint smallint mediumint int bigint) After the Zerofill descriptor has been added, So the Int (1) and int (ten) int (100) are no different when the Zerofill descriptor is not added.

8.

FAQ Two--How to store IP

What field type is used to store Ip,char (+), varchar (15) or int unsigned?

9.

FAQ Three--Scrambling field types

1> all strings are in varchar (255)------------> to the right length.

2> all the numbers are used bigint-------> to the appropriate type, such as tinyint, smallint, etc.

3> all numbers are used with decmal--------> Imprecise values, other floating-point types can be used, or shaped to replace

Second, the index

1.

Developers should consider what SQL is commonly used, and be sure to add index. You cannot have just one primary key for a table.

Do not modify the clustered index (primary key)

In order to maintain the b+tree will bring a lot of data movement, it is generally required to use the business is not related to the ID to do a plastic self-increment primary key

Index is not the more the better, try to merge the index

The 1> index speeds up the query, but it affects write performance.

2> the index of a table should be combined with all of the SQL that is associated with this table to be created as much as possible.

The principle of 3> composite indexes is that the better the filter is, the more forward the field.

For example, key (a) and key (A, a) exist, then key (a) can be deleted, for select ... from TB where a=123; index (A, b) can be used

Do not give low-selectivity characters Jianjian single-column index

MySQL has a requirement for index filtering, and MySQL will discard it if the filter is too low.

Do not use foreign KEY constraints

1> is particularly significant for performance losses.

2> allows the application to maintain constraints.

Character Type field use prefix index as far as possible

An index that is too long affects not only write performance, but also poor usage, so the string type field is typically built with only a prefix index

ALTER TABLE TEST_LONG_STR Add index Idx_str (str (16));

Rational use of composite indexes

Index issues for like queries

Like can only use a prefix index, so:

1>col like "abc%" can use the index

2>col like "%abc%" cannot be indexed

3>col like "%abc" cannot be indexed

2.

Third, SQL optimization or specification

Considerations when designing tables for MySQL