Star /fork: github.com/Wasabi1234/…
Explain or desc to get information about how MySQL executes select statements.
The result contains many columns
1 Field description of each column
1.1 id
SELECT identifier. This is the serial number of the SELECT in the query and indicates the order in which the SELECT clause or operation table is executed in the query. This value can be NULL if this row references the union result of other rows.
Id numbers fall into three categories:
- If the id is the same, the order of execution is from top to bottom
explain se1ect * from emp e join dept d on e.deptno = d.deptno
join salgrade sg on e.sa1 between sg1.osal and sg.hisal;
Copy the code- The ID is different. For subqueries, the ID sequence increases. A larger ID has a higher priority and is executed earlier
explain select * from emp e where e.deptno in
(select d.deptno from dept d where d.dname = 'SALES');
Copy the code- Those with the same id and those with different ids exist at the same time: Those with the same ID can be considered as a group and are executed from top to bottom. In all groups, the greater the ID value, the earlier the group is executed
exp1ain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sa1
between sg1.osal and sg.hisal where e. deptno in (select d.deptno from dept d where
d.dname = 'SALES');
select_ type
Copy the code1.2 select_type
SELECT * from (SELECT * from (SELECT * from))
- Simple (simple table, i.e. no table join or subquery)
- Primary (external query)
- Union (the second or subsequent query in union)
- Subquery (the first select in the subquery)
Table 1.3
Output the result set. Which table is being accessed by the row, the table name or alias, possibly a temporary table or union merge result set.
- If it is a concrete table name, it indicates that the data is obtained from the actual physical table, and of course it is the alias of the table
- The table name is the form derivedN and represents a derived table that uses a query with ID N
- When there is a union result, the table name is in the form of union N1,n2, etc. N1 and n2 represent the IDS participating in the union
1.4 type
The type column describes how tables are joined.
Represents the way MySQL finds the desired rows in the table, or the access type. Common types: all,index,range,ref,eq_ref,const,system,null, performance from bad to good.
Generally, you need to ensure that the query reaches at least range level, and preferably ref level.
1.4.1 ALL
MySQL is the most efficient and violent full table scan, MySQL traverses the full table to find the matching row. A full table scan is performed for each combination of rows from the previous table. If the table is the first one not marked as const, it is usually bad, and in all other cases it usually performs very poorly. In general, you can avoid ALL by adding indexes that allow you to retrieve rows from a table based on constant or column values from earlier tables.
explain select * from film where rating > 9;
Copy the code1.4.2 index
The connection type is the same as ALL, except that the index tree is scanned. This happens in two ways:
- Only the index tree is scanned if the index is the overwrite index of the query and can be used to satisfy all the data required in the table.
In this case, the Extra column shows Using Index. Index-only scans are usually faster than ALL because indexes are usually smaller than table data. 2. Perform a full table scan using reads of indexes to find rows in index order. The Extra column does not show Using index.
MySQL can use this join type when queries use only columns that are part of a single index.
explain select title from film;
Copy the code1.4.3 range
Query rows using indexes to retrieve only rows in a given range. The key column in the output row indicates which index to use. Key_len contains the longest key part used. A REF of this type is listed as NULL.
When using the =, <>, >,>=, <,<=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators to compare key columns with constants, you can use range: index range scanning, common <,<=,>,>=, BETWEEN
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10.20.30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10.20.30);
Copy the code1.4.4 index_subquery
This connection type is similar to unique_subquery. It replaces the IN subquery, but applies to non-unique indexes IN subqueries of the following form:
value IN (SELECT key_column FROM single_table WHERE some_expr)
Copy the code1.4.5 unique_subquery
This type replaces eq_ref with some IN subqueries of the following form:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
Copy the codeUnique_subquery is just an index lookup function that can completely replace subqueries for efficiency.
1.4.6 index_merge
This join type indicates the use of index merge optimization. In this case, the key column in the output row contains the list of indexes used, and key_len contains the list of the longest key part of the index used.
1.4.7 ref_or_null
This connection type is similar to ref, but MySQL will additionally search for rows that contain NULL values. This join type optimization is most often used to parse subqueries. In the following example, MySQL can use the ref_OR_NULL connection to handle ref_table:
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
Copy the code1.4.8 fulltext
Use the FULLTEXT index to perform the join.
1.4.9 ref
For each combination of rows in the previous table, all rows with matching index values are read from the table. Ref is used if the join uses only the leftmost prefix of the KEY, or if the KEY is not a PRIMARY KEY or UNIQUE index (that is, if the join cannot select a single row based on the KEY value). This is a good type of join if you use keys that match only a few rows.
Ref can be used for indexed columns that are compared using the = or <=> operator. In the following example, MySQL can use a ref join to handle ref_table:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
Copy the code1.4.10 eq_ref
For each row combination in the previous table, read a row from this table. Besides the System and const types, this is the best connection type. This is used when the join uses all parts of the index and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
Similar to ref, the difference is that the index used is a unique index. For each index key value, one record in the table matches. Select a primary key or unique index as the association condition.
Eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses a column in a table that was read before this table. In the following example, MySQL can use the eq_ref connection to handle ref_table:
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
Copy the code1.4.11 const
The table has at most one matching row, which is read at the start of the query. Because there is only one row, the rest of the optimizer can treat the values of the columns in that row as constants. Const tables are fast because they are read only once.
Const is used when all parts of the PRIMARY KEY or UNIQUE index are compared to constant values. Tbl_name can be used as a const table in the following queries:
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
Copy the code1.4.12 system
The table has only one row (system table). This is a special case of the const connection type.
Type null, MySQL does not need to access the database directly to get the result.
1.5 possible_keys
Possible indexes for this query
1.6 the key
The exact index used in this query
1.7 ref
Which field or constant is used with the key
1.8 rows
How many rows are scanned by this query? This is an estimate and not accurate.
1.9 filtered
The percentage of data filtered by this query condition
1.10 extra
Additional information.
using filesort
Use EXPLAIN to check whether MySQL can parse the ORDER BY clause using indexes:
- If the Extra column of the EXPLAIN output does not contain Using Filesort, the index is used and file sorting is not performed
- If the Extra column of the EXPLAIN output contains a file sort in use, instead of using the index, a full-file sort is performed
EXPLAIN cannot distinguish whether the optimizer performs file sorting in memory. You can see the use of memory file sorting in the optimizer trace output. Find filesort_priority_queue_optimization.
using temporary
Create a temporary table to save the intermediate results, after the query is completed, the temporary table is sharply divided
using index
Indicates that the current query is an overwrite index, reading data directly from the summation without accessing the data table. Using WHERE indicates that the index is used to perform a lookup of the index key. If not, the index is being used to read the data, not actually look it up
using where
Use WHERE for conditional filtering
using join buffer
Using connection caching
impossible where
The result of the WHERE statement is always false
no matching row in const table
For a query with a join, there is an empty table or table with no rows that meet the unique index condition.
There are many more. I won’t describe them too much.
explain extended
MySQL 4.1 introduced the Explain extended command. With explain Extended plus show Warnings, you can see what the optimizer does before MySQL is actually executed
explain select * from users;
show warnings;
Copy the codeAs you can see from the warning field, some of the constant conditions are removed, and you can use the explain extended results to quickly get a clearer and more readable SQL statement.
2 show profile
The SHOW PROFILE and SHOW PROFILES statements SHOW the PROFILE information, which indicates the resource usage of the statements executed during the current session.
The SHOW PROFILE and SHOW PROFILES statements have been deprecated and will be removed in future MySQL releases in favor of performance mode. So we’re just going to give you a brief introduction, but you know that it’s there.
- Check whether profile is enabled
As you can see, profiling is off by default.
Profiling can be enabled at the session level with the SET statement:
set profiling=1;
Copy the codeYou can view the status and time of each thread during execution. The sendingData state indicates that the MySQL thread accesses the data row and returns the result to the client, not only to the client, because in sending Data state, the MySQL thread usually needs to do a lot of disk reads. So it’s often the most time-consuming state in the entire query.
You can select all, CPU,block IO,context,switch, and Page faults to check which resources the MySQL database uses
show profile cpu for query 6;
Copy the codeInnodb is Sending data. MyISAM does not need to send data at all
If you are interested in the MySQL source code, you can run show profile source for Query to see the source files for each step of the SQL parsing process
show profile source for query 6
Copy the code3 Trace analysis optimizer
MySQL 5.6 provides. The trace file provides a better understanding of optimizer selection and optimizer behavior.
use
Enable trace, set the format to JSON, and set the maximum memory that trace can use to avoid incomplete display due to the default small memory during parsing
set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
Copy the codeNext, execute the SQL statement for trace
select * from. where....Copy the codeFinally, check information_schema.optimizer_trace to see how Mysql executes SQL
select * from information_schema.optimizer_trace
Copy the codereference
- Dev.mysql.com/doc/refman/…