MySQL Query statement execution process and performance optimization-basic concepts and explanations _ MySQL

MySQL Query statement execution process and performance optimization-basic concepts and explanations

BitsCN.com

MySQL Query statement execution process and performance optimization-basic concepts and explanations

The key aspect of website or service performance is the database design (assuming you have chosen the appropriate language development framework) and how to query data.

We know the MySQL Performance optimization methods, such as creating indexes, avoiding complex joint queries, setting redundant fields, creating intermediate tables, and querying caches. we also know how to use EXPLAIN to view the execution plan.

However, little is known about the execution process and internal mechanism of MySQL complex query statements, the optimization of MySQL Optimizer itself, and the impact of query statement adjustment on performance and the causes.

This article attempts to make a more in-depth discussion of some key concepts, such as the execution process and index usage,

In this way, we can avoid blindly following the trend and turning to NoSQL storage or investing money to upgrade the infrastructure when the results are achieved through simple MySQL optimization.

To do a good job, you must first sharpen the tool. here we will first introduce the MySQL Query statement performance analysis tool.

The MySQL EXPLAIN command is used to analyze the query performance. each line of EXPLAIN output corresponds to the execution plan of a table in the query statement. The Output column meanings are as follows:

In the table above, the type column is a table join type. common types are as follows (sorted by association query efficiency from high to low ):

[Plain]

Const (constant join), such as SELECT * FROM user WHERE id = 1;

Eq_ref (equivalent reference), such as SELECT * FROM user, card WHERE user. id = card. userid;

Ref (reference) for non-unique indexes, such as SELECT * FROM user, card WHERE user. last_name = 'test ';

Range, for example, SELECT * FROM tbl_name WHERE key_column> 10;

Index: reads data based on the index. if the index already contains the query data, you only need to scan the index tree. Otherwise, the full table scan is similar to All;

ALL (ALL), full table scan

The key column indicates the index and rows indicates the estimated number of rows that will be scanned,

Extra indicates additional information. There are several common types of information (sorted by query efficiency from high to low ):

[Plain]

Using index: indicates that the index is used. if the Using where clause appears at the same time, it indicates that the index is used to find and read the record. if the Using where clause is not used, the index contains the query data and no additional search is required;

Using where: indicates a condition query. if the type column is ALL or index, but this information is not displayed, you may run the wrong query: return ALL data;

Using filesort: not the meaning of "Using file indexes! Filesort is a sorting policy implemented BY MySQL. This information is usually displayed when the sort statement order by is used;

Using temporary: indicates that a temporary table is used to obtain the result. this is usually used for multi-table joint query and result sorting;

If the two following information (Using filesort, Using temporary) appears in the EXPLAIN statement, and rows is relatively large, it usually means you need to adjust the query statement or add an index, in short, we need to eliminate the two information as much as possible.

The following example shows the EXPLAIN result (nickname and gender are identified from the user Archive table and sorted by the number of users in the user table ):

[plain] mysql> explain select user.Username, user_profile.nickname, user_profile.gender, user_profile.meet_receive from user_profile join users on users.Id = user_profile.user_id where user_profile.`display` = '1' order by user_profile.fl_no limit 50;

+ ---- + ------------- + Hour + -------- + ------------- + --------- + hour + ------- + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------------------- + -------- + --------------- + --------- + hour + ------- + hour + | 1 | SIMPLE | user_profile | ALL | NULL | 14399 | Using where; using temporary; Using filesort | 1 | SIMPLE | users | eq_ref | PRIMARY | 8 | cms. user_profile.user_id | 1 | Using where | + ---- + ------------- + --------------------- + -------- + --------------- + --------- + response + ------- + response + 2 rows in set (0.00 sec)

The preceding query statements are typical cases. the specific meanings of Using filesort and Using temporary and how to optimize the preceding statements are discussed in the next article based on the query process and principles.

BitsCN.com