“This is the 18th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

Analyze EXPLAIN results

Now let’s analyze the results of EXPLAIN.

explain select * from salaries where from_date ='1996-12-02';
Copy the code

Select_type is simple, indicating that this is a simple query; The query table is salaries; If type is ALL, a full table scan occurs, and the performance of a full second scan is the worst. Possible_keys, key, and key_len are all empty, indicating that no index is used. Rows execute this SQL statement by scanning more than two million rows for filtered data to return. The final value of extra is Using WHERE to indicate that the WHERE condition is used.

By executing the SQL statement, you can find that it takes 600 milliseconds to execute the SQL statement, indicating that the performance of the SQL statement is poor.

Let’s examine another SQL statement.

The id field is useful when there are more than one row of results. It describes the execution of the SQL statement.

If the explain result contains multiple ID values, such as id value 1 and ID value 2, then the higher the number, the higher the execution, that is, id value 2 is executed first and id value 1 is executed later. Rows with the same ID are executed from top to bottom, as we discussed earlier.

One of them operates on the employees table and the other operates on the salaries table, and since we have aliased it, the table column shows the alias.

From type, the employees table is const, which is a nice level, uses a primary key index, probably uses a component, actually uses a component, and only scans one piece of data because we specified the condition to query. Rows x filtered is 1, which means MySQL expects a row from the employees table to be associated with the salaries table.

Similarly, when operating on a table, the value of type is ref, and ref also performs well. The primary key index was also used, and it was estimated that 17 rows of data needed to be scanned.

Visual tools analyze SQL

Here we introduce two visualization tools, one is IntelliJ IDEA, as long as this tool how to use, I believe many friends know, the other is the official website of MySQL provides a MySQL Workbench. Let’s take a quick look at both tools.

Analyze SQL using IntelliJ IDEA. Select the SQL statement and right-click Explain Plan.

The following figure shows analyzing SQL statements using IntelliJ IDEA.

At this point, you can click Show Visualization to Show a tree-like result.

Use the MySQL Workbench visualization provided by MySQL. MySQL Workbench provides visual Sql development, database modeling, and database management capabilities for database administrators, program developers, and system planners.

Extension of the EXPLAIN

EXPLAIN generates additional information, which can help us learn more about the SQL by viewing the extended information following the EXPLAIN statement with a SHOW WARNING statement.

The extended EXPLAIN has a lot to do with the MySQL version:

In MySQL 8.0.12 and later, extended information can be used in SELECT, DELETE, INSERT, REPLACE, UPDATE statements; Prior to MySQL 8.0.12, extended information only applied to SELECT statements.

In MySQL 5.6 and later, use the EXPLAIN EXTENDED XXX statement. Starting with MySQL5.7, you don’t need to add EXTENDED keywords.

Example:

explain select * from employees e left join salaries s on e.emp_no = s.emp_no where e.emp_no=10001\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: e partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s partitions: NULL type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 17 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 SEC) mysql> show warnings\g +-------+------+-------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select '10001' AS `emp_no`,'1953-09-02' AS `birth_date`,'Georgi' AS `first_name`,'Facello' AS `last_name`,'M' AS `gender`,'1986-06-26' AS `hire_date`,`employees`.`s`.`emp_no` AS `emp_no`,`employees`.`s`.`salary` AS  `salary`,`employees`.`s`.`from_date` AS `from_date`,`employees`.`s`.`to_date` AS `to_date` from `employees`.`employees`  `e` left join `employees`.`salaries` `s` on((`employees`.`s`.`emp_no` = 10001)) where 1 | +-------+------+-------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Note: Extended EXPLAIN cannot be implemented using IntelliJ IDEA and must be implemented in MySQL terminal.

The above results show that the MySQL optimized SQL statement and the original SQL statement is quite different.

Because the result of SHOW WARNING is not necessarily a valid SQL statement, it may not be executable.

No special tags appear in the above example. In normal projects, extended EXPLAIN might have the following special tags in its output:


  • : indicates the temporary table key that is automatically generated.
  • (EXPR) : An expression (such as a scalar quantum query) is executed once and the resulting value is stored in memory for later use. For results consisting of multiple values, you can create a temporary table that will see the information.
  • Query Fragment: Converts a subquery predicate to an EXISTS predicate, and then converts a subquery to be used with an EXISTS predicate.

  • (Query fragment) : This is an internal optimizer object that has no meaning to the user.
  • < index_Lookup >(Query Fragment) : Use index lookup to process query fragments to find qualified rows.
  • (condition, expr1, expr2) : Expr1 if the condition is true, expr2 otherwise.
  • < is_NOT_NULl_test >(expR) : tests that verify expressions are not NULL.
  • Query Fragment: Implemented using subqueries.
  • Materialized-subquery. col_name: Reference to col_name in an internal materialized temporary table to hold the results of an evaluation subquery.
  • < primary_index_Lookup >(Query Fragment) : Use primary key lookup to process query fragments to find eligible rows.
  • < ref_NULl_helper >(expR) : This is an internal optimizer object and has no meaning to the user.
  • /* SELECT #N */ select_STmt: associate SELECT with a value N with an output ID in a non-extended EXPLAIN line.
  • Outer_tables semi JOIN (inner_tables) : a semi-join operation. Inner_tables shows the table that is not pulled.
  • : represents an internal temporary table created to cache intermediate results.

When some tables are of type const or System, the expressions involved in the columns of those tables are covered by the pre-evaluation and are not part of the displayed statement. However, when FORMAT=JSON is used, some const table accesses are shown as ref accesses using constant values.

Estimate query performance

In most cases, you can estimate query performance by counting the number of disk searches. For smaller tables, rows can usually be found in a disk search (because the index may already be cached), while for larger tables, you can use the B-tree index to estimate: How many lookups do you need to do to find rows: Log (row_count)/log(index_block_length / 3 * 2 / (index_length + data_POinter_length)) + 1.

In MysQL, index_block_length is typically 1024 bytes, and data Pointers are typically 4 bytes. For example, if you have a table with a value of 500,000 and a key of 3 bytes, use the formula: log(500,000) /1g (1024/3*2/(3+4)) + 1 = 4 searches.

The index will require 500,000 x 7 x 3/2=5.2MB of storage (assuming a typical index cache fill rate of 2/3), so you can store more indexes in memory and probably find the desired row with just one or two calls. However, for write operations, you need four search requests to find where to put the new index value, and then usually two searches to update the index and write the row.

The previous discussion does not mean that your application performance will slow down because of logN. As long as the content is cached by the OS or MySQL server, it will only slow down slightly as the table grows larger. After the amount of data becomes too large to cache, it will slow down a lot, until your application is constrained by disk search (logN growth). To avoid this, increase key values as the data grows. For MyISAM tables, the cache size for key is defined by the name key_buffer_size