Ali's OceanBase has gone to heaven, but will you still use Explain to look at SQL query plans?

Posted May 25, 20208 min read

Mysql performance optimization artifact artifact explain. Be transparent

  • [Mysql performance optimization artifact artifact explain. One article transparent)(# mysql%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E7%A5%9E%E7%A5%9E%E5%99%A8explain%(E4%B8%80%E6%96%87%E9%80%9A%E9%80%8F)

Foreword

SQL statements will be written in different forms in the hands of different people, such as the SQL slow queries that are often encountered. At this time, it is often necessary to optimize for SQL.
In order to ensure that SQL statements can be run efficiently, Mysql provides an Explain command for semantic analysis of SQL statements for developers to optimize for SQL.
image.png

data preparation

In order to facilitate the execution of the entire process, first create test data.

Create a data table

Execution in SQL involves the joint execution of a single table and multiple tables. This time two tables are created to simulate this situation. More joint execution of multiple tables is the same as the execution plan of the two tables.

CREATE TABLE `users`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `name` varchar(20) NOT NULL DEFAULT '' COMMENT 'user name',
    `sex` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Gender',
    `phone` varchar(11) NOT NULL COMMENT 'Mobile number',
    `desc` varchar(200) NOT NULL DEFAULT '' COMMENT 'Introduction',
    primary key(`id`)

) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT 'User Table';

CREATE TABLE `order`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `phone` varchar(11) NOT NULL COMMENT 'Mobile number',
    `name` varchar(20) NOT NULL COMMENT 'user name',
    primary key(`id`)

) ENGINE = InnoDB default CHARSET = utf8 COMMENT 'Order';

Insert data

In order to facilitate this time, instead of using SQL statements, but using stored procedures to create data, it is simple, fast and convenient.

# Create stored procedure
create procedure insert_user_data()
begin

declare i int;
declare name varchar(20);
declare phone_num varchar(11);

set @SURNAME = 'Wang Li Zhang Liu Chen Yang Huang Zhao Wu Zhou Xu Sun Ma Zhu Hu Guo He Gao Lin Luo Zheng Liang Xie Song Tang Wei Xu Han Feng Deng Cao Peng Xiao Tian Dong Pan Yuan Yu Jiang Cai Yu Du Ye Cheng Su Wei Luding Ren Shen Yao Lu Jiang Cui Zhong Tan Lu Wang Fan Jinshi Liao Jia Xia Wei Fu Fang Bai Zou Meng Xiong Qin Qiu Jiang Yin Xue Yan Duan Lei Houlong Shi Tao Li He Gu Mao Hao Gong Shao Wan Qian Yan Qin Wu Dai Mo Kong Xiang Tang ';
set @NAME = 'Dan Juyi's music book Gan Yun is also replaced by Wei Youjun Xiu Jian'ao Yuanguang Landong Icy Ling Ningfan Kai Chuli Qin Qianhui Half Huanan Bo Youyou Tong Xiangjun Ting and Zhe Jiaguojian Cheng Xia Ye Tian Qi Ao like a wonderful son Cun Ji Gu Yu An Wan Chen Han Xun Er Yao Mountain Lan Jun Qiao Ping You Kang Jian Hongqiang Tong Yanbin Peng Xinyi Zhinian pity Mucheng Qingmin Wen Xinxuan Hao Ming Yi Xin Ying Chunyu Jin Xiaohan Sheng Jingqing Zhi Manpeng Lang Jiesong Feng Bai Rou Liu Ge Tao Meng Kai Huai Zheng Shui Pei Bo Ze Jie Yang Jipu Hao Hai Tao Run Han Yuan Pu Pu Han Ling Canyan Yan Yan Xuan Yuxi Yiyu Shanzhen Li Qiqin Yu Rui Yao Jin Pu idiot Hao Pan Zhen Rui Bi Lei Xiang Qi Bing Cheng Li Zhu Xiao Zi Shao Jing Green Qun Cui Han Zhihang Liang Fu Zhi Cang Yuan Ruo Mao Rong Lian Ling Xuan Rong Lan Rui Lei Weidie Yu Gu Hao Fu surpasses Xuan Huida and is far away drunk Jin Xinjin asked Yan Ya Yu Xuelin Frost Dew Qing Jing Jing Feng Fei Xiang Gao Gao Hong Peng He Li ';

set i = 1;

while i <100000 do
    SET phone_num = concat('1',
                    substring(cast(3 +(rand() * 10)%7 AS char(50)), 1, 1),
                    right(left(trim(cast(rand() AS char(50))), 11), 9));

    set name = concat(substr(@ surname, floor(rand() * length(@surname)/3 + 1), 1), substr(@ NAME, floor(rand() * length(@NAME)/3 + 1), 1), substr(@ NAME, floor(rand() * length(@NAME)/3 + 1), 1));
    insert into users(create_time, name, sex, phone, `desc`) values  (now(), name, rand() * 1, phone_num, 'test');
    insert into `order`(phone, name) values  (phone_num, name);

    set i = i + 1;
end while;

end

# Execute stored procedure
call insert_user_data();
# Delete stored procedure
drop procedure if exists insert_user_data;

After the stored procedure is created, you can directly delete the stored procedure if you need to modify it, and then re-create it.

explain command use

The explain command explain select * from users where id = 1 \ G; is shown as follows:

*************************** 1. row***************************
           id:1
  select_type:SIMPLE
        table:users
   partitions:NULL
         type:const
possible_keys:PRIMARY
          key:PRIMARY
      key_len:4
          ref:const
         rows:1
     filtered:100.00
        Extra:NULL
1 row in set, 1 warning(0.00 sec)

A total of 12 fields, the meaning of each field is as follows:

  • id:Each query statement will generate an identifier, the order of execution is id from largest to smallest
  • select \ _type:the type of query, which contains multiple types [Jump to select \ _type](# select_type)
  • table:Query table name, including related table information
  • partitions:matching partitions
  • type:indicates the way Mysql finds the required row in the table, here is the way to use the index. [type](# type)
  • possible \ _keys:indexes that may be used in query statements
  • key:the actual index used by the query
  • key \ _len:indicates the number of bytes used in the index, note that the maximum possible length in the index field is displayed, not the actual length used
  • ref:the connection method of the above table, which columns or constants are used to find the value on the index column
  • rows:estimate the number of rows that need to be read to find the desired record
  • extra:Other extra details included in the query in this column.

Some fields have more types, the following is a detailed explanation.

select \ _type

Used to represent each query type, commonly used types are as follows:

  • SIMPLE:The simplest query method, single-table query, does not include UNION and sub-query, such as select * from users where id = 1
  • PRIMAPY:Indicates that the secondary query is the outermost query. Displayed when there is a subquery. explain select * from users where phone =(select phone from order where id = 10);
  • UNION:indicates that the second query is the second or subsequent query method of UNION. The union keyword explain select * from users where id = 10 union select * from users where id = 20; Upload picture
  • DEPENDENT UNION:The second or later query statement in UNION depends on the query outside
  • UNION RESULT, the result of UNION. Look at the icon
  • SUBQUERY:The first SELECT.![Subquery method]in subquery( https://i0.wp.com/segmentfault.com/img/remote/1460000022742879 "subquery method")
  • DEPENDENT SUBQUERY:The first and first SELECT in the subquery, depends on the external query. When the subquery depends on the external query result, the content will be displayed explain select * from users where phone =(select phone from order where id = users.id) and id = 10; . Depend on external

The most common type here is the SIMPLE type, and the multi-table query we often use is also the SIMPLE type. For example, explain select * from users left join order o on users.phone = o.phone where users.id = 10 Multi-table query is of SIMPLE type

type

The type field helps us locate whether the query is efficient, whether it is a full table scan or an index scan.
Different types represent different performance, the order is as follows:
ALL <index <range ~ index \ _merge <ref <eq \ _ref <const <system

Common types are as follows:

  • ALL:Full table scan. When the data in the database is huge, a query still uses the full table scan. This query has a huge impact on the database, and the solution is to avoid it by adding indexes. explain select * from users;, you can see that the full table scan scanned more than 2 million rows of data. Full Table Scan
  • Index:$\ color {red} {full index scan} $, only scan all indexes without scanning data, which has reduced some data volume compared with full table scan. At the same time, in the Extra field, Index.explain select id from users; id in the query statement is the primary key index, only the index data is queried Primary key index .
  • range:based on the index for data filtering query, these identifiers that can use the index are =, <,>, <=,> =, BETWEEN, IN operator. explain select phone from users where id> 10 and id <20; Use> and <in the SQL statement to limit whether the condition is used or range, ** $\ color {red} {When the field in the statement is not an index , Then it is not the range used.
  • ref:Use the non-unique index query in the query, and show which column or constant ** is used in the ref column. Although the index is used, the value of the index column can exist multiple, such as the same phone number appears in the phone column. explain select * from order where phone = '16485461071'![ref]( https://i0.wp.com/segmentfault.com/img/remote/1460000022742886" ref ")
  • ref \ _eq:The usage is similar, but what is better than ref is that the type knows that there is only one result set. Directly know that the result set is a record index is the primary key index and the unique index, the use of this type is in the multi-table query, conditions include the primary key or unique index conditions. explain select * from users, order where users.id = order.id![ref_eq use]( https://i0.wp.com/segmentfault.com/img/remote/1460000022742891" ref_eq use ")
  • const:The primary key value is used as the conditional query of where, the Mysql optimizer will turn this query into a constant and treat it as explain select * from order where id = 10;![constant query]( https://i0.wp .com/segmentfault.com/img/remote/1460000022742890 "constant query")
  • system:A special case of const type, when there is only one row of data in the table, the system type will be used

rows

The number of rows to be scanned in the query, we use various indexes, and optimization is to reduce the number of rows scanned.

ref

Indicates that when querying, the connection matching condition of the table can be a constant or a query column explain select * from users, order where users.id = order.id;
associated column or constant represented by ref

extra

extra means more SQL query information, extra is an important supplement to query information in Mysql query plan. The types of extra are as follows:

  • Distinct:After finding the first line, no more matching to find more data, corresponding to distinct in the query to re-query.
  • Using filesort:means that MYSQL uses memory sorting or file sorting, and the sorting does not use indexes. You can use the appropriate index to modify the conditions in the order by and group by statements.
  • Using temporary:Use temporary tables to save intermediate results, commonly used in Group by and Order by statement queries. Also try to avoid using temporary tables to save intermediate results.
  • Not exists:In some LEFT JOIN connections, MYSQL uses an optimizer to optimize, change the original QUERY composition optimization part, and reduce the number of data accesses.
  • Using index:You do not need to return to the table when you query, you can get the query data directly through the index.
  • Using union:When using or to connect various index conditions, it indicates that the information indicates that the union is obtained from the processing result.
  • Using intersect:When using and to connect each index condition, it indicates that the information indicates that the union is obtained from the processing result.
  • Using sort \ _union/Using sort \ _intersection:Appears in the and/or statement, first query the primary key information, and then sort and merge the results to read the data.
  • Using where:Use the Where clause to limit the return of data. Note:Using Where means that the Mysql server returns the storage engine to the service layer before performing conditional filtering.
  • Using join buffer:There are two types of connection buffers:Block Nested Loop and Block Nested Loop, and Index Nested-Loop Join using index query.

to sum up

Understand the SQL query plan. When writing SQL, use the explain statement to see what the SQL query plan is. You can have a general understanding of the execution of SQL in your heart.

The SQL statements used in this article are uploaded to github
· END ·

Although the road is far, the line must be

This article was originally posted on the WeChat public account of the same name "Fengqi's Road to Upgrade". Reply "1024". You know, give it a thumbs up.

WeChat ID:YoungRUIQ

Public Account