Basic Structure and index optimization scheme of MySQL database

The following articles mainly introduce the basic structure of MySQL database, the optimization scheme of MySQL database index and the optimization of query statements. The related content is of great significance for new database beginners. The following describes the specific content of the article.

MySQL database structure

When defining field types, follow the following rules:

Minimum Field Length

Priority fixed length type

Define "not null" as much as possible"

Avoid using "ZEROFILL" in Numeric Fields"

If the data to be stored is a string with known and limited values, use enum or set first.

MySQL database index Optimization

The smaller the length of the indexed field, the higher the efficiency of the index.

The less repeated the value in the indexed field, the higher the efficiency of the index.

If the "group" clause is used in the query statement, multi-field indexes are created based on the order in which the fields appear.

If "distinct" is used in the query statement, multi-field indexes are created based on the order in which the fields appear.

When the "and" condition for multiple different fields in the same table appears in the "where" clause, multi-field indexes are created according to the order in which the fields appear.

When the "or" condition for multiple different fields in the same table appears in the "where" clause, a single field index is created for fields with the least repeated values.

Index "connection fields" When querying "internal/external connections"

It is meaningless for the "unique" index of "primary key". Do not use

Use the "not null" attribute as much as possible for indexed fields

Minimize indexes for write-intensive tables, especially "Multi-field indexes" and "unique" Indexes

Optimization of MySQL database query statements

Use "explain" to query index usage, so as to find the best query statement Writing Method and index setting solution.

Use "select *" with caution. Only required fields are selected during query.

When an index is used for query, the fewer index entries that are traversed, the smaller the index field length, the higher the query efficiency (you can use "explain" to query the index usage)

Avoid using the MySQL function to process the query results.

When using "limit", try to make the part of the "limit" result set located at the front of the result set, so that the query speed is faster and the system resource overhead is lower.

When the "and" condition of multiple fields is used in the "where" clause, the order in which each field appears must be consistent with the order in the Multi-field index.

When "like" is used in the "where" clause, the index is used only when the wildcard does not appear in the leftmost end of the condition.

In MySQL 4.1 or later versions, avoid using subqueries and try to use "internal/external connections" to implement this function.

Reduce the use of functions. If possible, use a simple expression instead.

Avoid performing "or" Conditional queries on different fields in the "where" clause, and split the query statement into multiple single fields, which is more efficient.