MySQL optimization strategy

First, the establishment of the principle of the table:

1. Table optimization and type selection

(1) Separation between fixed length and variable appearance.

(2) According to the frequency of use to establish the main table and sub-table (the infrequently used fields into the sub-table: such as user table, the user's home address and other details into the schedule, when the need to query the details, and then click on the query).

(3) Under the premise of satisfying the "three paradigms" of the database, we adopt "anti-three paradigm" and add redundant fields reasonably. The idea is to ensure the system performance with space-changing time, such as decreasing frequent summation and frequent cascade.

2. Column selection (data type selection)

(1) field type selection: Integral type >date,time>enum, Char>varchar>blob, text.

(2) for integer and char types, preference is given to integer types, such as when using integer or character type for gender, preference is given to the tinyint type, because choosing char (1) may take into account the character set and the proofing set.

(3) Enum: The internal use of integral type storage, play a role in the constraints.

(4) Char and varchar:char fixed length, varchar indefinite length, although varchar will save space than Char, but because the changes will be based on the length of the "row migration" phenomenon, which caused the redundant I/O, is the database design and tuning to avoid, so for frequently modified data as far as possible use char instead of varchar, to ensure performance.

Second, index optimization (CORE):

Indexes can improve query, sorting, and grouping speed.

1, Index classification: Betree index, hash index.

(1) Betree index: A tree structure index, the page node records information pointing to the data, quickly locate the data.

(2) Hash index: Run in memory and can only be used in the storage table. In the disk to give a space, through the complex operation to obtain the address of the disk, when the data to take advantage of the function, directly get the address to get the data, directly hit the query faster.

Hash Index Disadvantage Disadvantages:

1) There may be address conflicts: using the zipper algorithm to solve.

2) random address, disk hole.

3) Range query cannot be optimized. Because the address is random, it is not possible to optimize the range.

4) The prefix index cannot be applied (using a portion of the indexed field), and sorting cannot be optimized. Because you need to fetch the data back.

2. Index type: Stand-alone index, federated Index

(1) A federated index is a combination of multiple columns together with the same index, the column order is differentiated, that is, to meet the "left prefix" requirements, from left to right in order to meet, if there is a break in the middle, then can not be used behind.

3. Index creation:

(1) General index

This is the most basic index and it has no limitations.

To create an index:

CREATE INDEX indexname on mytable (username (length)); If it is a char varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.

To delete an index:

DROP INDEX [IndexName] on mytable;

(2) Unique index

It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique.

To create an index:

CREATE UNIQUE INDEX indexname on mytable (username (length));

(3) Primary key index

It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id));

3, Betree Index classification: According to the engine is divided into clustered index and non-clustered index

(1) Nonclustered index (MyISAM engine): The index tree and the data are separated, and only the leaf nodes are relegated to the index value and the reference to the row data.

(2) clustered index (InnoDB engine): A clustered index is an algorithm that re-organizes actual data on disk to sort by the values of one or more columns that you specify. The characteristic is that the order of storing data is consistent with the index order. Store both the index value and the row data (the data is placed under the primary key index, if no primary key index is placed under the non-null index, if nothing but empty index directly inside create a row to store row data), do not have to return rows to obtain data. The index secondary index points to a reference to the primary key index.

Clustered index Benefits: No need to go back to the line, fast access to data, disadvantage: If the data is irregular, it will cause the page splitting (large data, primary key index irregular).

4, Index Overwrite: If the index already contains the queried field, then do not return the row, directly from the index value to get the data, called the index overlay. If the field in the index does not fully contain the field being queried, the row will be returned.

5, Indexing principle: Frequent hits, high sensitivity, small length, as far as possible to cover the common query fields.

Generally in the actual work, can be based on research, and running logs, statistical data, the establishment of the index.

7. For fields where the left prefix is not highly differentiated (for example, address https://www.cnblogs.com, in: "Https://www.") ): Using reverse, pseudo-hash method.

Pseudo-Hash: Add a field, using the CRC32 algorithm (hash algorithm), the field that needs to be indexed into an integer, and then index the integer, and then query the time to query the data needs to use CRC32 calculation, and then query CRC32 field data.

8. Index and Sort: the fields that need to be sorted are as consistent as the index, the index is ordered, and the sorting speed on the index is faster.

9. Duplicate indexes and redundant indexes:

(1) Repeat index: Two indexes are identical: meaningless.

(2) Redundant index: Two indexes have crossed places.

Third, SQL statement optimization

1, SQL statement takes time: Wait time and execution time (find, remove, transfer less).

(1) According to data split into multiple queries, such as paged query.

(2) by index Search, sorting, grouping, the data is best to go index coverage.

(3) Do not require absolute accuracy of the data, not accurate query,

(4) Transfer: Avoid using SELECT * queries in data queries, which fields are required, check those fields.

2, SQL statement Performance discrimination: Using explain to interpret SQL statements:

Id:

Select_type:

Table:

Type:all, index, range, ref (reference), const (constant), System, NULL

Ref (Reference):

Extra:

four, limit optimization;

1. Business Solution: Page Turn page limit

2, Technical solution: First along the index, to obtain the qualified ID, and then based on the ID query.

MySQL optimization strategy