Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the role of explain in Mysql

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

This article mainly introduces what kind of role explain plays in Mysql, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what kind of role explain plays in Mysql.

I. the index of MYSQL

Index: a data structure that helps Mysql get data efficiently. Used to improve the efficiency of search, can be compared to a dictionary. It can be simply understood as a sorted data structure for quick lookup.

The purpose of an index: it is easy to query and sort (so adding an index affects where statements and order by sort statements).

In addition to the data, the database maintains data structures that meet specific lookup algorithms, which refer to data in some way. In this way, advanced search algorithms can be implemented on these data structures. These data structures are indexes.

The index itself is so large that it is impossible to store it all in memory, so indexes are often stored on disk in the form of index files.

What we usually call an index, if not specifically specified, is usually a B-tree index. (clustered index, composite index, prefix index, unique index are all B+ tree indexes by default), in addition to B-tree indexes, there are also hash indexes.

Advantages:

A. improve the efficiency of data retrieval and reduce the IO cost of the database

B. sort the data through the index column, which reduces the cost of data sorting and the consumption of CPU.

Disadvantages:

A, the index is also a table, the table holds the primary key and index fields, and points to the record of the entity table, so the index also takes up space.

B. When performing INSERT, UPDATE and DELETE operations on the table, MYSQL will not only update the data, but also save the corresponding information about the index column fields added to each update of the index file.

In the actual production environment, we need to analyze step by step, optimize the establishment of the optimal index, and optimize our query conditions.

Classification of the index:

1. Single-valued index an index contains only one field, and a table can have multiple single-column indexes.

2. The value of the unique index column must be unique, but null values are allowed.

3. Composite index an index contains multiple columns

A table suggests setting up less than five indexes

Syntax:

1. CREATE [UNIQUE] INDEX indexName ON myTable (columnName (length))

2. ALTER myTable Add [UNIQUE] INDEX [indexName] ON (columnName (length))

Delete: DROP INDEX [indexName] ON myTable

View: SHOW INDEX FROM table_name\ G

II. The role of EXPLAIN

EXPLAIN: simulates how the Mysql optimizer executes SQL queries so that you know how Mysql handles your SQL statements. Analyze the performance bottlenecks of your query or table structure.

Mysql > explain select * from tb_user +-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -- + | 1 | SIMPLE | tb_user | ALL | NULL | 1 | NULL | + -+

(1) id column:

(1) the same execution order as id from top to bottom

Mysql > explain-> SELECT*FROM tb_order tb1-> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id-> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id +-+-+ | id | select_ Type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | SIMPLE | tb1 | ALL | NULL | 1 | NULL | 1 | SIMPLE | tb2 | eq_ref | PRIMARY | PRIMARY | 4 | product.tb1.tb_product_id | 1 | NULL | 1 | SIMPLE | eq_ref | PRIMARY | PRIMARY | 4 | product.tb1.tb_user_id | 1 | NULL | +-- -+

(2) if it is a subquery, the id sequence number will increase by itself, and the higher the id value, the higher the priority, and the first it will be executed.

Mysql > EXPLAIN-> select * from tb_product tb1 where tb1.id = (select tb_product_id from tb_order tb2 where id = tb2.id = 1) +-+ | id | select_type | table | type | possible_keys | key | key_ Len | ref | rows | Extra | +-- + | 1 | PRIMARY | tb1 | const | PRIMARY | | PRIMARY | 4 | const | 1 | NULL | 2 | SUBQUERY | tb2 | ALL | NULL | 1 | Using where | +-+-- | -+

(3) the same and different id exist at the same time.

Mysql > EXPLAIN-> select * from (select * from tb_order tb1 where tb1.id = 1) S1 Magazine tbaked user tb2 where s1.tb_user_id = tb2.id +-+ | id | select_type | table | type | possible_keys | key | key _ len | ref | rows | Extra | +-+ | 1 | PRIMARY | | system | NULL | | NULL | 1 | NULL | 1 | PRIMARY | tb2 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | 2 | DERIVED | tb1 | const | PRIMARY | 4 | const | 1 | NULL | + -+

Derived2: table 2 indicates that the derived table tb1 is derived from id=2

(2) select_type column: the operation type of data read operation

1. SIMPLE: simple select query. Subquery or UNION is not included in SQL.

2. PRIMARY: the query contains complex subqueries, and the outermost query is marked as PRIMARY

3. SUBQUERY: subqueries are included in the select or WHERE list

4. DERIVED: subqueries included in the FROM list are marked as DERIVED (derived tables), and MYSQL recursively executes these subqueries to put the result set into the zero-hour table.

5. UNION: if the second SELECT appears after the UNION, the marked bit UNION; if the UNION is included in the subquery of the FROM clause, the outer SELECT will be marked as DERIVED

6. UNION RESULT: the select that gets the result from the UNION table

(3) table column: which table is the row of data about?

(4) type column: access types from good to bad system > const > eq_ref > ref > range > index > ALL

1. System: the table has only one record (equal to the system table). This is a special case of const type, which usually does not appear in the business.

2. Const: the data is found at one time through the index. This type is mainly used to compare primary key or unique indexes, because only one row of data is matched, so it is very fast; if the primary key is placed after the WHERE statement, Mysql can convert the query to a constant.

3. Eq_ref: unique index scan, with only one record in the table matching for each index key. Common in primary key or unique index scans.

4. Ref: a non-unique index scan that returns matching a single worthy of all rows, which is essentially an index access, which returns all rows that match a single value, that is, it may find multiple pieces of qualified data, so it is a mixture of lookup and scanning.

5. Range: only rows in a given range are retrieved, using an index to select rows. The key column shows which index is used. Generally, between, between, and between appear in your WHERE statements.

< 、>

, in, etc., this given range scan is better than a full table scan. Because it only needs to start at one point of the index and end at another point, it doesn't have to scan all the indexes.

6. Index:FUll Index Scan scans through the index tree (scans the index of the whole table to get data from the index).

7. ALL full table scan to obtain data from disk millions of levels of data ALL type data is optimized as far as possible.

(v) possible_keys column: displays one or more indexes that may be applied to this table. If an index exists in the fields involved in the query, the index is listed, but not necessarily used by the query.

(6) keys column: the index actually used. If NULL, the index is not used. If an override index is used in the query, it appears only in the key list. Override index: the number of fields after select is the same as the number of fields we indexed.

(7) ken_ Lenn column: indicates the number of bytes used in the index, which can be used to calculate the index length used in the query. Without losing accuracy, the shorter the length, the better. The value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, the key_len is calculated based on the table definition, not retrieved within the table.

(8) ref column: shows which column of the index is used and, if possible, a constant. Which columns or constants are used to find values on indexed columns.

(IX) rows column (how many rows in each table are queried by the optimizer): based on the statistical information of the table and the selection of indexes, roughly estimate the number of rows that need to be read to find the required records.

(X) Extra column: extended attributes, but very important information.

1. Using filesort (file sorting): mysql cannot read according to the given index order in the table.

Mysql > explain select order_number from tb_order order by order_money +-+ | id | select_type | table | type | possible_keys | key | key_ Len | ref | rows | Extra | +-- + | 1 | SIMPLE | tb_order | ALL | NULL | 1 | Using filesort | +-+ 1 row in set (0.00 sec)

Note: order_number is the only index column in the table, but order by does not use this index column to sort, so mysql has to sort with another column.

2. Using temporary:Mysql uses temporary tables to store intermediate results, which are common in sorting order by and grouping query group by.

Mysql > explain select order_number from tb_order group by order_money +-+-- + | id | | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -- + | 1 | SIMPLE | tb_order | ALL | NULL | 1 | Using temporary Using filesort | +-+-- + 1 row in set (0.00 sec)

3. Using index indicates that the corresponding select operation uses an overlay index to avoid accessing the data rows of the table, which is very efficient.

If Using where appears at the same time, the index is used to perform the lookup of the index key value.

If no using where appears at the same time, it indicates that the index is used to read data rather than perform lookup actions.

Mysql > explain select order_number from tb_order group by order_number +-+ | id | | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | SIMPLE | tb_order | index | index_order_number | index_order_number | 99 | NULL | 1 | Using index | +-+ -+ 1 row in set (0.00 sec)

4. Using where search

5. Using join buffer: indicates that the connection cache is used in the current sql.

6. Impossible where: the where sentence is always false, and mysql cannot get the data row.

7 、 select tables optimized away:

8 、 distinct:

After reading the above about the role of explain in Mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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: 295

*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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report