Mysql optimization tool description, mysqlexplain

Mysql optimization tool description, mysqlexplain

I. Syntax

{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;

Iii. explain options

The following 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

The serial number of the Query in the execution plan selected by MySQL Query Optimizer.
Indicates the order in which the select clause or operation table is executed in the query. The higher the id value, the higher the priority. Same id, execution sequence from top to bottom

Select_type

1. SIMPLE: SIMPLE select queries, without union and subqueries
2. PRIMARY: select query on the outermost layer
3. UNION: the second or subsequent select query in UNION does not depend on the result set of the external query.
4. dependent union: the second or subsequent select query in UNION depends on the result set of the external query.
5. union result: the first select query in the SUBQUERY of the UNION query RESULT set does not depend on the RESULT set of the external query.
6. dependent subquery: The first select query in the SUBQUERY depends on the result set DERIVED of the external query for subqueries in the from clause.
MySQL recursively executes these subqueries and places the results in the temporary table.
7. uncacheable subquery: subqueries whose result set cannot be cached must be re-evaluated for each row in the outer query.
8. uncacheable union: the second or subsequent select queries in UNION are non-cacheable subqueries.

Table

1. system: The table has only one row (system table ). This is a special case of the const connection type.
2. const: const is used to compare the primary key with a common value. When the queried table has only one row, system is used.
3. eq_ref: the best possible connection type except the const type. It is used to connect all parts of an index and the index is UNIQUE or primary key,
For each index key, only one record matches the key.
4. ref: you cannot select a single row based on the keyword for the connection. Multiple qualified rows may be found. It is called ref because the index must be compared with a reference value.
This reference value is either a constant or a result value from a multi-Table query in a table.
5. ref_or_null: Like ref. However, MySQL must find the null entry in the first search result and perform a secondary search.
6. index_merge: indicates that the index merge optimization is used.
7. unique_subquery: This type is used IN some IN queries, rather than the regular ref:
Value IN (SELECT primary_key FROM single_table WHERE some_expr)
Index_subquery uses this type IN some IN queries. It is similar to unique_subquery, but the query has non-unique indexes:
Value IN (SELECT key_column FROM single_table WHERE some_expr)
8. range: searches for rows within a specified range and uses an index to select rows. The key column shows the index used.
When using the =, <>,>, >=, <, <=, is null, <=>, BETWEEN, or IN operator, you can use range to compare the keyword columns with constants.
9. index: full table scan, which is performed according to the index order rather than the row during table scan. The main advantage is that sorting is avoided, but the overhead is still very large.
10. all: Worst case, full table scan from start to end

Others

Possible_keys: indicates which indexes mysql can use in this table to facilitate query. If it is null, no index is available.
Key: The index actually used by mysql from possible_key. If it is null, no index is used.
In rare cases, mysql selects an optimized index. In this case,
You can use the use index (indexname) in the select statement to forcibly use an index.
Or use ignore index (indexname) to force mysql to ignore indexes.
Key_len: the length of the index used. The shorter the length, the better.
Ref: indicates which column of the index is used.
Rows: the number of rows that mysql deems necessary to check to return the requested data

Extra

1. Distinct: Once mysql finds the row that matches the row, it does not search any more.
2. Not exists: mysql optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.
3. Range checked for each: Record (index map: #) does not find the desired index,
Therefore, for each row combination in the preceding table, mysql checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes.
4. Using filesort: MySQL uses an external index to sort the results, instead of reading the relevant content from the table in the index order.
It may be sorted in memory or disk. Sorting operations that cannot be completed by using indexes in MySQL are called "File Sorting ".
5. Using index: the column data is returned from a table that only uses the information in the index but does not read the actual action,
This occurs when all the request columns of the table are the same index.
6. Using temporary: mysql needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.
7. Using where: The WHERE clause is used to limit which rows match the next table or are returned to the user.
If you do not want to return ALL rows in the table, and the connection type is ALL or index, this may occur, or the query is faulty.

4. Specific instances

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