Small ant learns MySQL performance optimization (7)--Database structure optimization--Choosing the right data type

The section on SQL and index optimization has finally finished learning, today began to enter the second level of learning, database structure optimization, the first part, select the appropriate data type.

The choice of data type, with emphasis on the appropriate two words.

1. Use the smallest data type that can save data

For example, a time type of data, you can use varchar, you can use the DateTime, you can also use int, how to choose, see which type is the smallest for us, it goes without saying, the int type is relatively the smallest data type.

2. Use a simple data type.

The int type is much simpler than the varchar type in MySQL processing, and storing the time with an int type is a good choice.

3. Use NOT NULL to define the field as much as possible.

This is because of some of the characteristics of InnoDB, for some null fields, he may need some extra fields to store, but also increase the IO and storage overhead, so in the table structure design, try to design each field as NOT null, and give a default value.

4. Use as few large types as possible, such as: text type

If it is not necessary, it is best to put these fields separately, there is an additional table, on the one hand to increase the efficiency of the main table query, on the other hand, when necessary to the additional table for this big data query.

Use the int type to store the time, using From_unixtime () and Unix_timestamp () two functions to convert.

such as From_unixtime (1443922330, '%y-%m-%d '); Get a formatted: year-month-day

Unix_timestamp (' 2015-10-4 '); Get a time stamp

Use the bigint type to store IP addresses and convert using Inet_aton (), Inet_ntoa () two functions

such as Inet_aton (' 127.0.0.1 ') returns an orthopedic

Inet_ntoa (IP) returns an IP address

Storage IP Address In addition to using varchar, you can also use bigint type storage, varchar to store the IP, it takes about 15 bytes, and bigint only need 8 bytes, 7 bytes Apart, do not underestimate the 7 bytes, the data is very large case, 7 bytes can save us a lot of storage space, and will give the IO efficiency, memory brings great savings. What is important is that not only are there savings in space, but also in the comparison of IP addresses, because the contrast of the plastic is simpler and more efficient than the varchar comparison.

Small ant learns MySQL performance optimization (7)--Database structure optimization--Choosing the right data type