summarize
Use explain to output detailed information about the execution of the SELECT statement, including the following information:
- Table loading order
- Types of queries in sql
- What indexes might be used and what indexes are actually used
- Number of lines read
The Explain execution plan contains the following field information: id, select_type, table, parts, type, possible_keys, key, key_len, ref, rows, filtered, Extra 12 fields.
With explain extended + show warnings, you can provide some additional query optimization information on top of the original explain, and get the possible query statements after optimization (not necessarily the final optimized result).
Test environment:
CREATE TABLE `blog` (
`blog_id` int NOT NULL AUTO_INCREMENT COMMENT 'Unique blog post id - Primary Key',
`blog_title` varchar(255) NOT NULL COMMENT 'Title of the blog post', `blog_body` text
`blog_body` text NOT NULL COMMENT 'The content of the blog post', `blog_time` datetime
`blog_time` datetime NOT NULL COMMENT 'Blog posting time', `update_time` datetime NOT NULL COMMENT
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`blog_state` int NOT NULL COMMENT 'blog_state - 0 deleted 1 normal', `user_id` int NOT NULL COMMENT
`user_id` int NOT NULL COMMENT 'User id',
PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
CREATE TABLE `user` (
`user_id` int NOT NULL AUTO_INCREMENT COMMENT 'Unique id of the user - primary key',
`user_name` varchar(30) NOT NULL COMMENT 'User name - cannot be repeated', `user_password` varchar(30) NOT NULL AUTO_INCREMENT COMMENT
`user_password` varchar(255) NOT NULL COMMENT 'user_password',
PRIMARY KEY (`user_id`),
KEY `name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
CREATE TABLE `discuss` (
`discuss_id` int NOT NULL AUTO_INCREMENT COMMENT 'Comment unique id', `discuss_body` int AUTO_INCREMENT COMMENT
`discuss_body` varchar(255) NOT NULL COMMENT 'Comment content', `discuss_time` int AUTO_INCREMENT COMMENT
`discuss_time` datetime NOT NULL COMMENT 'Comment time', `user_id` int NOT NULL COMMENT 'comment_time', `user_id` int NOT NULL COMMENT
`user_id` int NOT NULL COMMENT 'user id', `blog_id` int NOT NULL COMMENT 'comment_time', `blog_id` int NOT NULL COMMENT
`blog_id` int NOT NULL COMMENT 'blog post id',
PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8
id
Indicates the order in which select clauses or table operations are performed in a query, the larger the value of id, the higher the priority, the first to be performed.
explain select discuss_body
from discuss
where blog_id = (
select blog_id from blog where user_id = (
select user_id from user where user_name = 'admin'));
The three tables are nested sequentially and it is found that the innermost subquery with the largest id is executed first.
select_type
Indicates the type of select query, which is mainly used to distinguish various complex queries, such as: common query, union query, subquery, etc.
- SIMPLE: The simplest select query statement, which does not include subqueries or intersection and difference operations in the query.
- PRIMARY: The outermost SELECT in the query (the table operation at the outer level of the existent subquery is PRIMARY).
- SUBQUERY: The first SELECT in the subquery.
- DERIVED: the driven SELECT subquery (the subquery is in the FROM clause).
- UNION: UNION is used after SELECT
table
The name of the table for the query, not necessarily the real existing table, has an alias to show the alias, and may also be a temporary table. When there is a subquery in the from clause, the table column is in the format <derivenN>, indicating that the current query relies on the query with id N. The query with id N will be executed first.
partitions
The partition information matched in the query, the value is NULL for non-partitioned tables. when the query is for a partitioned table, parts displays the partitioning of the partitioned table hit.
type
What type the query uses, which is a very important metric in SQL optimization
Access efficiency: const > eq_ref > ref > range > index > ALL
system
When a table has only one row of records (system table), the amount of data is very small, and disk IO is often not required and is very fast. For example, the Mysql system table proxies_priv is loaded in memory when the Mysql service starts, and no disk IO is required to query this table.
const
The query uses a primary key or unique index when operating on a single table.
eq_ref
Primary keys and unique indexes are used as join conditions in multi-table join queries. In the following sql, for each row of the user table (outer loop), there is only one row of the user_role table (inner loop) that satisfies the join condition, and as soon as this row is found, it will jump out of the inner loop and continue to the next round of query in the outer loop.
ref
The lookup condition column uses an index and is not a primary and unique index. Although the index is used, but the value of the indexed column is not unique, so even if the index is used to find the first piece of data, still can not stop, to do a small scan in the vicinity of the target value. But the advantage is that it does not need to sweep the entire table, because the index is ordered, even if there are duplicate values, but also in a very small range to do the scan.
ref_or_null
Similar to ref, it additionally searches for rows containing NULL values
index_merge
The index merge optimization method is used and the query uses more than two indexes. Create a new comment table with id as the primary key and value_id as a non-unique index, execute explain select content from comment where value_id = 1181000 and id > 1000;, the execution result shows that the query uses both id and value_id indexes. The value of the type column is index_merge.
range
Index scans with ranges are preferred over full index scans with indices, which have range restrictions. things like between, and, >, <, in, and or are all range index scans.
index
index includes both select index columns and order by primary key.
order by primary key. In this case, the whole table is scanned for data in the order of the index, and the data you get is sorted by the primary key, so you don't need to do any additional sorting.
Select indexed columns. type is index, and the extra field is using index, also known as the case of index coverage. The data you need to fetch are in the indexed columns, so you don't need to go back to the table to query.
all
Full table scans, where the query does not use indexes, have the worst performance.
possible_keys
The index that may be used in this query. However, this index may not be the index that will be used in the final query.
key
The exact indexes used in this query
ref
The ref column shows which column or constant is used with key to select data rows from the table. Common values are const, func, NULL, and specific field names. When the key column is NULL, that is, when no index is used. If the value is func, the value used is the result of some function.
The execution plan ref for the following SQL is const because a combined index (user_id, blog_id) is used, where user_id = 13 where 13 is constant
mysql> explain select blog_id from user_like where user_id = 13;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user_like | NULL | ref | ul1,ul2 | ul1 | 4 | const | 2 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
And the following SQL has an execution plan ref value of NULL because the key is NULL and the query does not use an index.
mysql> explain select user_id from user_like where status = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user_like | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
rows
Estimating the number of rows that need to be read to find the desired record. Evaluation of SQL performance of a more important data, mysql need to scan the number of rows, very intuitive to show the performance of SQL good or bad, in general, the smaller the value of rows the better!
filtered
The percentage of data returned by the storage engine that, after filtering, leaves the number of records that satisfy the condition
extra
Indicates additional informational notes. For testing purposes, here are two new tables.
CREATE TABLE `t_order` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`order_id` int DEFAULT NULL,
`order_status` tinyint DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8
CREATE TABLE `t_orderdetail` (
`id` int NOT NULL AUTO_INCREMENT,
`order_id` int DEFAULT NULL,
`product_name` varchar(100) DEFAULT NULL,
`cnt` int DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8
using where
Indicates that a WHERE condition was used to filter data during the query. When a query contains a WHERE condition, MySQL filters out the rows that satisfy the condition before proceeding. This process is called "Using Where".
Indicates that the query column is not covered by an index, and the where filter condition is a range of the indexed column's leading columns, or a non-leading column of the indexed column, or a non-indexed column. Filtering the results returned by the storage engine (Post-filter) generally occurs at the MySQL server, not at the storage engine level, so you need to go back to the table to query the data.
using index
The columns of the query are covered by the index, and the where filter condition conforms to the principle of the leftmost prefix, and the index lookup can directly find the data that meets the condition.No need to go back to the tableQuery Data.
Using where&Using index
The queried columns are covered by the index, but the eligible data cannot be found by index lookup, although the eligible data can be found by index scanning, and there is no need to go back to the table to query the data.
Two cases are included (the combined index is (user_id, orde)):
where filter conditions do not match the leftmost prefix principle
The where filter condition is a range of the index column's leading columns
null
The query column is not covered by the index, and the where filter condition is the leading column of the index, that is, the index is used, but some fields are not covered by the index, you must go back to the table to query these fields, NULL in Extra.
using index condition
Index condition pushdown (ICP), first use the where condition to filter the index, after filtering the index to find all the data rows that match the index condition, and then use other conditions in the WHERE clause to filter these data rows.
For the joint index (a, b), in the execution of select * from table where a > 1 and b = 2 statement, only the a field can be used in the index, then in the joint index of the B+Tree to find the first meet the conditions of the primary key value (ID 2), but also need to determine whether the other conditions are met (to see whether b is equal to 2), it is in the joint index to In that case, should we judge it in the union index? Or go back to the primary key index to judge?
MySQL 5.6 introduces the index condition pushdown optimization, which can be used to filter out the records that do not meet the conditions and reduce the number of times you have to return to the table during the traversal process of a union index by judging the fields contained in the union index first.
The case of not using ICP (set optimizer_switch='index_condition_pushdown=off'), as shown below, does not use the where condition to filter the index in step 4:
Use the ICP case (set optimizer_switch='index_condition_pushdown=on'):
The following example uses ICP:
explain select user_id, order_id, order_status from t_order where user_id > 1 and user_id < 5\G;
After turning off ICP (set optimizer_switch='index_condition_pushdown=off'), you can see that extra is listed as using where, and will not use index pushdown.
using temporary
Temporary tables are used to hold intermediate results, commonly in order by and group by. Typically, when group by and order by co-exist and act on different fields, temporary tables are created in order to compute the final result set
filesort
Filesort. Indicates that the index could not be utilized to complete the sort operation, and the following conditions result in filesort:
- The fields of order by are not indexed fields
- select Query fields are not all indexed fields
- The select query fields are all index fields, but the order by fields are not in the same order as the index fields.
using join buffer
Block Nested Loop, which requires a nested loop calculation. This happens when two related tables join and none of the related fields are indexed. For example, both inner and outer types are ALL, and rows are 4. A loop is required for the4*4calculations. A common optimization is to add an index to the associated field to avoid each nested loop calculation.
Interview questions column
Java interview questions columnIt's online, so feel free to visit.
- If you don't know how to write a resume, resume projects don't know how to package them;
- If there's something on your resume that you're not sure if you should put on it or not;
- If there are some comprehensive questions you don't know how to answer;
Then feel free to private message me and I will help you in any way I can.