MySQL optimization and indexing

• Table optimization

1. Fixed length and variable length separation

such as Int,char (4), time core and characters commonly used paragraph, built fixed length, placed in a table;

Varchar,text,blob This variable-length field is suitable for a single table, with a primary key associated with the core table.

2. Characters commonly used segment and non-characters commonly used segment to be separated

3. On the 1-to-many field that requires correlation statistics, add a redundant field.

• Column type selection principle

1. field type precedence integral type > Date,time > Enum,char > VarChar > Blob,text

2. Enough on the line, do not be generous, large fields waste memory, affect the speed.

3. Try to avoid the use of Null,null unfavorable index, need special byte to label.

• Description of the enum column

The 1.enum column is internally stored using an integer type.

2.enum columns and enum columns want to correlate fastest

3.enum columns than (VAR) char is weak--it takes time to convert when it encounters a char association

4. Advantage---When char is very long, the enum remains an integer fixed length, when the amount of data queried is larger. The more obvious the advantages of enum.

Indexes increase the speed of queries, increase the speed of sorting, and increase the speed of grouping

• Clustered and non-clustered indexes

Clustered index: Btree

InnoDB engine, index and data are in one piece,

InnoDB stores row data directly in the tree of the primary key index (both the primary key value and the row data are stored).

The InnoDB secondary index points to a reference to the primary key.

Pros: There is less time to query entries based on the primary key, without having to return rows (the data is under the primary key node).

Disadvantage: If you encounter irregular data insertions, it causes frequent page splits.

Non-clustered index: btree

MyISAM engine, index and data are separate,

MyISAM index points to the location of the row on disk

In MyISAM, both the primary and secondary indexes point to physical rows (disk locations).

MySQL optimization and indexing