Introduction to explain usage of mysql optimization tool
- 2021-06-29 12:14:08
- OfStack
1. Grammar
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type] SELECT select_options
explain_type: {EXTENDED | PARTITIONS}2. Database preparation
Table 1:
DROP TABLE IF EXISTS `products`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `products` (
`products_id` int(11) unsigned NOT NULL auto_increment,
`products_type` int(11) unsigned NOT NULL default '1',
`products_quantity` float NOT NULL default '0',
`products_model` varchar(32) default NULL,
`products_upc` varchar(32) default NULL,
`products_isbn` varchar(32) default NULL,
`products_image` varchar(128) default NULL,
`products_image_thumbnail` varchar(200) NOT NULL ,
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_virtual` tinyint(1) NOT NULL default '0',
`products_date_added` datetime NOT NULL default '0001-01-01 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` float NOT NULL default '0',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_web_id` int(11) default NULL,
`products_ordered` float NOT NULL default '0',
`products_quantity_order_min` float NOT NULL default '1',
`products_quantity_order_units` float NOT NULL default '1',
`products_priced_by_attribute` tinyint(1) NOT NULL default '0',
`product_is_free` tinyint(1) NOT NULL default '0',
`product_is_call` tinyint(1) NOT NULL default '0',
`products_quantity_mixed` tinyint(1) NOT NULL default '0',
`product_is_always_free_shipping` tinyint(1) NOT NULL default '0',
`products_qty_box_status` tinyint(1) NOT NULL default '1',
`products_quantity_order_max` float NOT NULL default '0',
`products_sort_order` int(11) NOT NULL default '0',
`products_discount_type` tinyint(1) NOT NULL default '0',
`products_discount_type_from` tinyint(1) NOT NULL default '0',
`products_price_sorter` decimal(15,4) NOT NULL default '0.0000',
`master_categories_id` int(11) NOT NULL default '0',
`products_mixed_discount_quantity` tinyint(1) NOT NULL default '1',
`metatags_title_status` tinyint(1) NOT NULL default '0',
`metatags_products_name_status` tinyint(1) NOT NULL default '0',
`metatags_model_status` tinyint(1) NOT NULL default '0',
`metatags_price_status` tinyint(1) NOT NULL default '0',
`metatags_title_tagline_status` tinyint(1) NOT NULL default '0',
`itemno` varchar(32) default NULL,
`products_images_no` varchar(10) default '0',
`products_url` varchar(512) default NULL,
PRIMARY KEY (`products_id`),
UNIQUE KEY `itemno` (`itemno`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;Table 2:
DROP TABLE IF EXISTS `products_image`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `products_image` (
`id` int(10) unsigned NOT NULL auto_increment,
`products_id` int(10) unsigned NOT NULL,
`products_images_no` varchar(10) default '0' ,
`image_dir` varchar(200) default NULL,
`products_image_thumbnail` varchar(200) default NULL ,
`flag` int(2) default NULL,
`up_time` datetime default NULL,
`web_from` varchar(20) default NULL,
PRIMARY KEY (`id`),
KEY `idx_porducts_id` (`products_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;3. About explain Options
Here is an example:
mysql> explain select products_id from products limit 1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | products | index | NULL | PRIMARY | 4 | NULL | 3113 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+id
Sequence number of the query in the execution plan selected by MySQL Query Optimizer.
Represents the order in which the select clause or operation table is executed in a query. The higher the id value, the higher the priority, the more it is executed first.id is the same, executing from top to bottom
select_type
1. SIMPLE: Simple select query without using union and subqueries
2. PRIMARY: Outermost select Query
3. The second or subsequent select query in UNION:UNION does not depend on the result set of the external query
4. The second or subsequent select query in DEPENDENT UNION:UNION depends on the result set of the external query
5. The first select query in the SUBQUERY subquery of the result set of the UNION RESULT:UNION query, independent of the result set of the external query
6. DEPENDENT SUBQUERY: The first select query in a subquery depends on the result set of an external query, DERIVED, for cases where there is a subquery in an from clause.
MySQL recursively executes these subqueries, placing the results in a temporary table.
7. UNCACHEABLE SUBQUERY: Result sets cannot be cached subqueries and must be re-evaluated for each row of the outer query
8. The second or subsequent select query in UNCACHEABLE UNION:UNION is a non-cacheable subquery
table
1, system: Table has only one row (system table).This is a special case of the const connection type.
2. const:const is used to compare PRIMARY KEY with a constant value.system is used when the queried table has only one row.
3. eq_ref: Best possible connection type other than const.It is used to join all parts of an index and the index is UNIQUE or PRIMARY KEY,
For each key, only one record in the table matches it.
4. ref: Connections cannot select a single row based on a keyword, and may find multiple qualified rows.This is called ref because the index is compared to a reference value.
This reference value is either a constant or the result value from a multitable query in one table.
5. ref_or_null: Like ref, MySQL must find the null entry in the results of the first search and then do two searches.
6. index_merge: Indicates that index merge optimization is used.
7. unique_subquery: Use this type in some IN queries instead of the regular ref:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery uses this type in some IN queries, compared with unique_subquery is similar, but queries for non-1-only indexes:
value IN (SELECT key_column FROM single_table WHERE some_expr)
8. range: Retrieve only rows of a given range and use an index to select rows.The key column shows which index is used.
When using=,
<
>
,
>
,
>
=,
<
,
<
=, IS NULL,
<
=
>
The BETWEEN or IN operators can use range when comparing keyword columns with constants.
9, index: A full table scan is performed only when the table is scanned in the order of indexes, not rows.The main advantage is that sorting is avoided, but the overhead is still very large.
10, all: Worst case, full table scan from beginning to end
others
possible_keys: Indicates which indexes mysql can use in this table to help query.If empty, no index is available
key:mysql Actually from possible_key selects the index to use.If null, no index is used.
In rare cases, mysql will choose an underoptimized index.In this case,
You can use use index (indexname) in the select statement to force an index
Or use ignore index (indexname) to force mysql to ignore indexes
key_len: Length of index used.Shorter lengths are better without sacrificing accuracy
ref: Displays which column of the index is used
Number of rows that rows:mysql considers it necessary to check to return the requested data
extra
1. Distinct: 1Once mysql finds a row that matches the row union, it will no longer search.
2. Not exists: mysql optimizes LEFT JOIN, once it finds a line that matches the LEFT JOIN standard, it will no longer search.
3. The ideal index was not found by Range checked for each: Record (index map:#).
So for each row combination from the previous table, mysql checks which index to use and uses it to return rows from the table.This is the slowest connection using an index.
4. Using filesort: Indicates that MySQL will use an external index to sort the results rather than reading them from the table in index order.
Sorting may occur in memory or on disk.Sorting operations in MySQL that cannot be done using an index are called file sorting.
5. Using index: Column data is returned from a table that uses only the information in the index and does not read the actual action.
This occurs when all requested columns of a table are part of the same index.
6. Using temporary: mysql needs to create a temporary table to store the results, which usually occurs on ORDER BY for different column sets, not GROUP BY.
7. Using where: The WHERE clause is used to restrict which rows will match or be returned to the user for the following table.
This can happen if you don't want to return all the rows in the table and the join type ALL or index, or if there is a problem with the query.
4. Specific examples
1. mysql Version
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)2. sql statement analysis 1
mysql> explain select products_id from products;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | products | index | NULL | PRIMARY | 4 | NULL | 3113 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------3. sql Statement Analysis 2
mysql> explain select products_id from (select * from products limit 10) b ;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DERIVED | products | ALL | NULL | NULL | NULL | NULL | 3113 | |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+4. sql statement analysis 3
mysql> explain select products_id from products where products_id=10 union select products_id \
from products where products_id=20 ;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | products | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 2 | UNION | products | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+5. sql Statement Analysis 4
mysql> explain select * from products where products_id in ( select products_id from products where \
products_id=10 union select products_id from products where products_id=20 );
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | products | ALL | NULL | NULL | NULL | NULL | 3113 | Using where |
| 2 | DEPENDENT SUBQUERY | products | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 3 | DEPENDENT UNION | products | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+-------+---------------+---------+---------+-------+------+-------------+complete