mysql optimization (1) table optimization and column type selection

Optimization of table:

1: Separation of fixed length and variable length

For example, id int occupies 4 bytes, char(4) occupies 4 characters, and is also fixed length ,
time means that the bytes occupied by each unit value are fixed.

Core and commonly used fields should be built to a fixed length and placed in a table. And varchar,
text,blob, This kind of variable-length field is suitable for placing in a single table and using the primary key to associate it with the core table.

SQL will skip 100,000 pieces of data very quickly because all of them are constant

2: Commonly used fields and uncommon fields should be separated.

It is necessary to analyze the specific business of the website, analyze the query scenarios of the fields, and separate the fields with low query frequency.

3: Add redundant fields to 1-to-many fields that require related statistics.
Reduce related queries

See the following BBS effect statistics. Do not count the number of posts but add them under the column. Redundant fields, update the number of articles by +1 each time an article is published, which will reduce the query intensity

Column selection principle:

1: Field type priority integer> date, time > enum,char>varchar > blob,text

Characteristic analysis of columns: integer type: fixed length, no country/region distinction, no character set difference

For example, tinyint 1,2 ,3,4,5 71fb34173e4ee87dab1f85dc1c283a44 char(1) a,b,c,d,e, in terms of space, they all occupy 1 byte, but order
by sorting, the former is faster

Reason: The latter needs to consider the character set and collation set (that is, the sorting rules)

time is fixed length, fast to operate, and saves space. Considering the time zone, it is inconvenient to write SQL where
> '2005-10-12'; The time is stored in int type;
enum: It can be used to constrain the value. It is stored internally with integer type, but when combined with char, the internal string and value conversion is required.
Char is of fixed length, considering the character set and (sorting) collation set
varchar, and variable length must consider the conversion of the character set and the collation set during sorting, which is slow.
text/Blob cannot use memory temporary tables (sorting, etc.) The operation can only be performed on the disk)

Gender: Take utf8 as an example

char(1), 3 bytes long

enum('Male',' Female'); // Internally converted into numbers for storage, there is an additional conversion process

tinyint(), // 0 1 2 // Fixed length of 1 byte.

sql optimization Book "MYSQL High Performance Optimization"

Regarding the selection of date/time, the master's clear opinion is to directly choose int unsgined not null to store the timestamp http://www.xaprb.com/blog/2014/01 /30/timestamps-in-mysql/

Time--->Save as integer

2: Just enough, don’t be generous (such as smallint, varchar(N))

Reason: Large fields waste memory and affect speed.

Take age as an example. tinyint unsigned not null can store 255 years old, which is enough. Using int wastes 3 bytes

The contents stored in varchar(10) and varchar(300) are the same, but when performing table join queries, varchar(300) takes more memory

3: Try to avoid using NULL()

Reason: NULL is not conducive to indexing and needs to be marked with special bytes.

actually takes up more space on the disk. (mysql5.7 has improved null, but the query is still Inconvenience)

Experiment:

You can create two tables with the same fields, one is allowed to be null, and the other is not allowed to be Null, add 10,000 entries to each, and check the size of the index file. You can find , the index for null is larger. (In mysql5.5, optimization has been done for null, and the size difference is no longer obvious)

In addition: null is not convenient for querying,

where column Name = null;

Where column name!=null; no value can be found,

where column name is null, or is not null can be queried.

create table dictnn (
id int,
word varchar(14) not null default '',
key(word)
)engine myisam charset utf8;

create table dictyn (
id int,
word varchar(14),
key(word)
)engine myisam charset utf8;

alter table dictnn disable keys;
alter table dictyn disable keys;

insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
insert into dictyn select id,if(id%2,word,null) from dict limit 10000;

alert table dictnn enable keys;
alter table dictyn enable keys;

Description of Enum columns

1: Enum columns are stored internally using integers

2: Enum columns are associated with enum columns The fastest

3: The disadvantage of enum column compared to (var) char---when encountering the association with char, it needs to be converted. It takes time.

4: The advantage is that when When char is very long, enum is still an integer fixed length.

When the amount of queried data is larger, the advantage of enum becomes more obvious.

5: enum is related to char/varchar, because To convert, the speed is slower than enum->enum, char->char,

But sometimes it is used this way-----when the amount of data is particularly large, it can save IO.

Test:

##

create table t2 (
id int,
gender enum('man','woman'),
key(gender)
)engine myisam charset utf8;

create table t3 (
id int,
gender char(5) not null default '',
key(gender)
)engine myisam charset utf8;

alter table t2 disable keys;
alter table t3 disable keys;

insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;

alter table t2 enable keys;
alter table t3 enable keys;
mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender

Column4bc407409327eef13c66a538ac7449a4Column

Time

Enumf96cd2e1052de3d07a602458d541daf9enum

10.53

Char4bc407409327eef13c66a538ac7449a4char

24.65

Enum4bc407409327eef13c66a538ac7449a4char

18.22

If the advantages of the t2 table are not obvious, increase the gender column of t3, char(15) ,

char(20)...

As the t3 gender column becomes larger, the advantages of the t2 table gradually become apparent.



The reason----no matter The length of the characters enumerated by enum('manmaman','womanwomanwoman') is expressed internally by integers. The size of the data generated in the memory remains unchanged.

The char type , but more and more data are generated in the memory.



Summary: enum and enum types are associated faster

Enum type saves IO

The above is the content of mysql optimization (1) table optimization and column type selection. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!