MySQL design table structure data type selection

Choose the right data type

When using MySQL to create a data table, you will encounter a problem, how to choose the appropriate data type for the field. For example, to create an employee information table, each field can be defined in many different types.

Int,char,float and so on.

Char and varchar

Both char and varchar are used to store data of a string type, but they are not saved and retrieved in the same way. Char is a fixed-length character type, and two varchar is a type of character that can be converted

Value	CHAR (4)	Storage requirements	varchar (4)	Storage requirements
‘‘	‘ ‘	4 bytes	‘‘	1 bytes
' AB '	' AB '	4 bytes	' AB '	3 bytes
' ABCD '	' ABCD '	4 bytes	' ABCD '	5 bytes
' ABCDEFG '	' ABCD '	4 bytes	' ABCD '	5 bytes

Since char is fixed-length, it can be processed much faster than varchar, but its disadvantage is to waste storage space, the program needs to deal with the trailing space, so for those who do not play and the query speed has high requirements of data may consider using char type to store

In MySQL, different storage engines differ in the use of char and varchar

• MyISAM Storage Engine
  • We recommend that you use a fixed-length sequence instead of a variable-length data column
• InnoDB Storage Engine
  • A varchar type is recommended, and for INNNODB data tables, the internal row storage format does not differentiate between fixed-length and variable-length, so using a char column is not necessarily better than a variable-length varchar performance
  • Since Char takes up more space on average, the amount of storage and disk I/O for the data navigation required to be processed by varchar to UI digestion is better.

Text and blob Introduction

When selecting large text, we prefer text type or blob such as article.

The main difference between text and BLOB is that blobs can be used to store binary data such as photos. and text intelligently holds string data, such as articles and diaries.

Depending on the length of the stored text and the bytes stored, we can use

Mediumtext,longtext and Mudiumblob,longblob

FAQ hole Problem

BLOBs and text are left with a large ' void ' when a large number of deletions are performed, and the subsequent entry of these ' voids ' records has an impact on the performance of the insert. To improve performance, it is recommended to defragment the class table using the Optimize table feature.

Avoid the performance problems caused by voids

Examples of voids:

CREATE TABLET (IDVARCHAR( -), ContextTEXT);INSERT  intoTVALUES(1, Repeat ('haha', -));INSERT  intoTVALUES(2,repeat ('haha', -));INSERT  intoTVALUES(3,repeat ('haha', -));Insert  intoTSelect *  fromT;Insert  intoTSelect *  fromT;Insert  intoTSelect *  fromT

At this time the file size is:

Delete the id=1 data, then delete 1/3 of the data:

MySQL>Deletefromwhere id=132768 rows Affected (0.63 sec)

Looking at the file size, we can see the file size or 96MB, which creates an empty hole.

We use optimize to optimize:

MySQL>table t;

At this point we look at the file, has become 60MB, the file is greatly reduced, indicating ' empty is retracted '

File index

Use synthetic indexes to improve query performance for large text fields:

A composite index creates a hash value based on the contents of a field in a large text, and stores the value in a separate column of data, which can then be found by retrieving the hash value.

However, it is important to note that this technique can only be used for exact matching (it is not useful for search in the range of < >=)

You can use the MD5 () function to generate a hash value.

Here's how to synthesize an index:

CREATE TABLET (IDVARCHAR( -), Context Blob,hash_valueChar( +));INSERT  intoTVALUES(1, Repeat ('Beijing',2), MD5 (context));INSERT  intoTVALUES(1, Repeat ('beijing2008',2), MD5 (context)); MySQL> Select *  fromTwhereHash_value=MD5 (Repeat ('beijing2008',2));+------+------------------------+----------------------------------+|Id|Context|Hash_value|+------+------------------------+----------------------------------+| 1    |beijing2008beijing2008|0fe88accc8741a9d1bc323bd286866bb|+------+------------------------+----------------------------------+

Since this technique can only be used for exact matching, it reduces I/O to a certain extent and improves query efficiency. If you need to make a fuzzy query on a BLOB field, MySQL provides a prefix index, which is to create an index for only the first n columns of the field

Create Index  on t (context ());

Mysql> desc Select *  fromTwhereContext like"Beijing%"\G;*************************** 1. Row***************************ID:1Select_type:simpleTable: T Type:rangepossible_keys:idx_blobKey: Idx_blob Key_len:103Ref:NULLrows:2extra:usingwhere1Rowinch Set(0.04Sec

For the first 100 characters of the context, a fuzzy query can be used to index a prefix.

Note that the% here cannot be placed in front, otherwise the index cannot be hit

Avoid using SELECT *

Do not use SELECT * to retrieve large blobs or text values

Unless you are able to determine the constraints where only the required data is found, it is likely that a large number of values will be transferred over the network for no purpose.

Users can use the search index column to determine which rows of data are needed, and then retrieve the Blob or text from the qualifying data

Sub-table

Horizontal sub-table, in some environments, if you move these large column data into the second chapter of the data, then the data column in the original data table converted to a fixed-length data row format,

Then it makes sense. This reduces fragmentation in the table and gives you a fixed-length performance advantage.

Floating point and fixed point number

Use float,double to identify floating-point numbers in MySQL. When a field is defined as a floating-point type, if the inserted data precision exceeds the actual precision defined by the column, rounding is taken to get the actual value.

Fixed-point number is different from floating-point numbers, he is stored in the form of strings, so the actual value of the inserted precision is greater than the actual definition of precision, if in the traditional mode, will be directly error, can not insert data.

CREATE TABLETest (C1float(Ten,2), C2decimal(Ten,2));INSERT  into text VALUES(131072.32,131072.32); MySQL> Select *  fromtest;+-----------+-----------+|C1|C2|+-----------+-----------+| 131072.31 | 131072.32 |+-----------+-----------+

You can see that the value of the C1 column is changed from 131072.32 to 131072.31, which is the error that occurs when you use a single-precision floating-point number.

Attention:

• The problem of floating-point number error
• For data with high precision, such as currency, it should be represented or stored by fixed-point number
• In turn, if you use floating point number, pay special attention to the error problem, try to avoid doing floating-point comparison

Selection of date types

• Depending on the actual need to select the minimum stored date type that can be met, if only the year of record is required, then a year type that is stored with one byte is satisfied. Instead of using a 4-byte date, it not only saves space, but also improves query efficiency.
• If you want to record the day of the month and seconds, and record the older, then it is best to use datetime, do not use timestamp
• If the date of the record needs to be used by a user of a different time zone, it is best to use timestamp because only he can correspond to the actual time zone in the date type

MySQL design table structure data type selection