MySQL ☞ explain usage

Explain for SQL pre-analysis, can help us better analyze the use of index is reasonable, whether there is room for optimization.

If you have the following table, trade pre-fill table 500W+ data (you can also use MySQL functions to write random numbers, but it is slower)

CREATE TABLE `trade` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increment primary key',
  `trade_id` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Order Number',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'user ID',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'commodity ID',
  `num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Purchase Quantity',
  `price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'price',
  `total_price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'total',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'state',
  `create_time` int(10) NOT NULL DEFAULT '0' COMMENT 'Creation time',
  `update_time` int(10) NOT NULL DEFAULT '0' COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  UNIQUE key idx_trade_id (`trade_id`),
  key product_id (`product_id`),  key uid_ctime (`user_id`, `create_time`),
  key ctime_pid (`create_time`, `product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order sheet';

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increment primary key',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'user ID',
  `user_name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'name',
  `phone` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Mobile phone Number',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'state',
  `create_time` int(10) NOT NULL DEFAULT '0' COMMENT 'Creation time',
  `update_time` int(10) NOT NULL DEFAULT '0' COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  key user_id (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Information Table';

insert into user (user_id, user_name, phone, create_time) 
values (1001, 'Joe'.'15600001234', 1557900059);
Copy the code

      
$conn = new mysqli('127.0.0.1'.'root'.' '.'work');
if(! $conn -> connect_errno)exit(a); $i =1000;
while ($i) {
    $i--;
    $sql = "insert into trade (trade_id, user_id, product_id, num, price, total_price, status, create_time) values ";
    $j = 5000;
    $tradeId = 10000;
    while($j){
        $j--;
        $tradeId++;
        $row = ['"'."BJ".time()."_".$tradeId.'"', mt_rand(1000001.9999999), mt_rand(10001.99999), mt_rand(1.20), mt_rand(100.99999), 0, mt_rand(0.3), mt_rand(1507894221.1587894221)];
        $row[5] = $row[3] * $row[4];
        $sql .= "(".implode(', ', $row)."),";
    }
    $sql = trim($sql, ', ').";";
    $query = $conn->query($sql);
    echo $query;echo "\n";
    sleep(1);
}Copy the code

1, select_type

1.1. Query types and examples

Query usage type, there are roughly the following situations, attached sample screenshots for better understanding:

SIMPLE: SIMPLE SELECT(do not use UNION or subquery, etc.) PRIMARY: simply understand the outer query UNION: the second or subsequent SELECT statement in the UNION: SUBQUERY: the first SELECT DEPENDENT SUBQUERY in a SUBQUERY: The first SELECT in the SUBQUERY depends on the external query. DERIVED table SELECT(FROM the clause) UNCACHEABLE SUBQUERY: a SUBQUERY whose result set cannot be cachedCopy the code

Select * from uid_ctime where product= ‘product’; select * from product= ‘product’; Let’s talk about MySQL subqueries

MySQL > alter table subquery

MySQL subquery implementation is very poor, many times it is not executed according to our expectation, take the above case as an example, it is normal to execute the internal subquery first, and then do in operation, but the actual result is like this:

SQL > alter table subquery; alter table subquery; alter table subquery;

select * from user 
where 
EXISTS(select * from trade where product_id = 19066 and trade.user_id = user.user_id);Copy the code

If the outer layer is a large table, the performance of the query will be poor. Of course, we can also make some optimizations, using GROUP_CONCAT to construct a comma-concatenated string, or using inner join to join.

1.3. Derived tables

A derived table is a virtual table returned by SELECT. A derived table is similar to but simpler than a temporary table because no steps are required to create a temporary table. For example:

select * from (select * from trade where id < 1000000) as t;Copy the code

Unlike subqueries, derived tables must have aliases and an error will be reported if they do not.

Note: Do not use derived tables with large amounts of data.

1.4 temporary table, internal temporary table, external temporary table

When you work on larger tables, you may need to run many queries to get a final result set, and in the process, many temporary tables may be created.

Temporary tables exist mainly during the connection to MySQL. When the connection is disconnected, temporary tables are automatically deleted to release space. You can also manually delete it.

For temporary tables, performance is optimal if they exist in memory. MySQL also preempts in-memory temporary tables. When a certain threshold is reached, internal temporary tables are converted to external temporary tables. The threshold size is determined by the smaller values of the system variables max_HEAP_TABLE_size and tmp_table_size.

2, the type

This column is important because it reflects the quality of the statement. The resulting values, from good to bad, are:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

In general, you need to ensure that the query is at least range, preferably ref, or performance issues may occur.

3, possible_keys

4, the key

The actual key used by MySQL is NULL if no index is used

5, key_len

Display the length of the index used by MySQL. If the key is NULL, the length is NULL. As short as possible without loss of accuracy, see the example above:

For single-column indexes with columns of type int, key_len = 4

For a federated index, one field is int, one field is bigINT, key_len = 12

Smallint: 2 MIDDLEINT: 3 MIDDLEINT: 4 BIGINT: 8 VARCHR (10) Variable length fields that allow NULL 10 * (character)set: UTf8 =3, GBK =2,latin1=1)+1(NULL)+2(variable length field) VARCHR (10) Variable length field and NULL 10 *(character) is not allowedset: UTf8 =3, GBK =2,latin1=1)+2(variable length fields) char(10) Fixed field and allowed NULL 10 * (character)set: UTf8 =3, GBK =2,latin1=1)+1(NULL) CHAR (10) Fixed field and not allowed NULL 10 * (character)set: utf8 = 3, GBK = 2, latin1 = 1)Copy the code

6, ref

7, rows

8 filtered.

Indicates the percentage of the number of records that satisfy the query after the data returned by the storage engine is filtered at the Server layer.

A lot of people ask, is a bigger value better, or a smaller value?

Personally, it depends on the situation:

If all of your WHERE conditions hit the index, this value is 100%. If there are no partial hits, the rest of the data is filtered at the server layer, but MySQL takes an extra step and returns the same result set size to the client.

However, in the case of a large amount of data, it is recommended that all index matches provide better performance.

9, extra

Most of the scenarios are briefly introduced, some of the results are not listed, and specific examples are given at the end of the article

9.1, NULL

This means that an index is used, but no overwrite index is used, and the WHERE condition is the leading column of the index

9.2, Using the where

Leading columns that mean where conditions are not indexed, most of the time, do not hit the index

9.3, Using the index

Indicates that all processing can be done at the index level. If an overridden index is hit, no need to return to the table.

9.4, Using filesort

Obviously, sorting can’t be done at the index level, so file sorting is required. The larger the result set of order BY, the worse the performance and the slower the SQL.

Using index condition

The columns in the query are not all in the index, but the where condition is a filter of leading columns or can hit the index at all.

9.6, Using the MRR

5.6 What are the new features?

select * from trade whereuser_id = 3606493; Before 5.6: first query primary key set in the federated index of user_id, and then traverse the set to obtain the data in the corresponding clustered index; After 5.6: Get the primary key set in the combined index of user_id, sort it in buffer, and then query the data in the corresponding clustered index; What's the difference? When retrieving the clustered index, one is random IO and the other is sequential IO.Copy the code

9.7, Using the where; Using index

The lookup uses the index, but the data needed is found in the index column, so there is no need to go back to the table to query the data

9.8, Using sort_union (…).

When the query result of AND and or is large, the primary key is queried first, and then the sorting merge is performed. Records are read and returned.