In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-03 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article will explain in detail the sample analysis of the explanation command and usage of EXPLAIN in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
1. Scenario description: my colleague taught me how to use explain in mysql, so I checked the meaning of the returned content.
2. Make a record of what is useful as follows:
1 explain shows how mysql uses indexes to process select statements and join tables. It can help select better indexes and write more optimized query statements.
To use the method, add explain before the select statement:
Explain select count (DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_date > = "2017-09-04" and uc_date= "2017-06-01" LIMIT 1
(2) the explanation of the column of Magi explain:
Table: shows which table the data in this row is about
Type: this is an important column that shows what type of connection is used. The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL
Possible_keys: displays the indexes that may be applied to this table. If empty, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain
Key: the index actually used. If NULL, the index is not used. In rare cases, MYSQL chooses indexes that are not sufficiently optimized. In this case, you can use USE INDEX (indexname) in the SELECT statement to force the use of an index or IGNORE INDEX (indexname) to force MYSQL to ignore the index
Key_len: the length of the index used. The shorter the length, the better without losing accuracy.
Ref: shows which column of the index is used and, if possible, a constant
The number of rows that rows:MYSQL believes must be checked to return the request data
Extra: additional information about how MYSQL parses queries. It will be discussed in Table 4.3.But the bad examples you can see here are Using temporary and Using filesort, meaning that MYSQL cannot use indexes at all, and the result is that retrieval will be slow
3 the meaning of the description returned by the column
Distinct: once MYSQL finds a row that matches the row union, it no longer searches
Not exists: MYSQL optimizes LEFT JOIN so that once it finds rows that match the LEFT JOIN standard, it no longer searches
Range checked for each Record (index map:#): no ideal index was found, so for each combination of rows from the previous table, MYSQL checks which index to use and uses it to return rows from the table. This is one of the slowest connections to use an index
Using filesort: when you see this, the query needs to be optimized. MYSQL needs to take additional steps to discover how to sort the returned rows. It is based on the connection type and
A row pointer that stores the sort key value and all rows of matching criteria to sort all rows
Using index: column data is returned from a table that only uses the information in the index and does not read the actual action, which occurs when all request columns on the table are part of the same index
When Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results, which usually occurs on the ORDER BY of different sets of columns, not on the GROUP BY
Where used uses the WHERE clause to restrict which rows will match the next table or be returned to the user. If you do not want to return all the rows in the table, and the connection type
ALL or index, this can happen, or the query has a problem with the interpretation of different join types (sorted in order of efficiency)
The system table has only one row: the system table. This is a special case of the const connection type
Const: the maximum value of a record in a table can match this query (the index can be a primary key or unique index). Because there is only one line, this value is actually a constant, because
MYSQL reads this value first and then treats it as a constant
Eq_ref: in a join, when MYSQL queries, the union of each record from the previous table reads a record from the table, using the index as the primary key or only
Use all of one button
Ref: this join type occurs only when the query uses keys that are not unique or primary keys or parts of these types (for example, using the leftmost prefix). For the previous table
For each row join, all records will be read out from the table. This type depends heavily on how many records are matched by the index-the fewer the better
Range: this connection type uses an index to return rows in a range, such as using > or
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
View the Exhibit and examine the structure of the CUSTOMERS table.Which two tasks would require subq
© 2024 shulou.com SLNews company. All rights reserved.