mysql之explain(二)

一、引言

最近订阅了极客时间mysql的专栏，感觉讲的还不错，在此之前也看过一些mysql的知识，但是不太系统，故而经常性的用过即忘，趁着对这块感兴趣，赶紧把这块知识记录下来！

二、准备工作

CREATE TABLE `order_detail` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `vendor_code` varchar(30) NOT NULL DEFAULT '' COMMENT '供应商',
  `barcode` varchar(128) NOT NULL DEFAULT '' COMMENT '条码',
  `order_no` varchar(100) NOT NULL DEFAULT '' COMMENT '订单号',
  `sync_no` varchar(32) NOT NULL DEFAULT '0' COMMENT '同步号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标志',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `unique_sync_no` (`sync_no`) USING BTREE,
  KEY `idx_query_info` (`vendor_code`,`barcode`,`order_no`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单明细表';


CREATE TABLE `order_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `order_no` varchar(100) NOT NULL DEFAULT '' COMMENT '订单号',
  `order_name` varchar(100) NOT NULL DEFAULT '' COMMENT '订单名称',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标志',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单主表';


INSERT INTO `order_detail`(`id`, `vendor_code`, `barcode`, `order_no`, `sync_no`, `create_time`, `update_time`, `is_deleted`) VALUES (285, '1000', 'barcode_001', 'order_001', '285', '2018-10-11 16:46:31', '2018-12-05 16:47:12', 0);
INSERT INTO `order_detail`(`id`, `vendor_code`, `barcode`, `order_no`, `sync_no`, `create_time`, `update_time`, `is_deleted`) VALUES (286, '1000', 'barcode_002', 'order_002', '286', '2018-10-11 16:46:31', '2018-12-05 16:47:15', 0);

INSERT INTO `order_info`(`id`, `order_no`, `order_name`, `create_time`, `update_time`, `is_deleted`) VALUES (94157, 'order_001', '小明的订单', '0000-00-00 00:00:00', '2018-12-05 15:19:33', 0);
INSERT INTO `order_info`(`id`, `order_no`, `order_name`, `create_time`, `update_time`, `is_deleted`) VALUES (94158, 'order_002', '小张的订单', '0000-00-00 00:00:00', '2018-12-05 15:19:26', 0);

三、explain

工作中用来分析sql是否走索引

mysql> explain select * from order_detail where vendor_code ='1000';
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order_detail | NULL       | ref  | idx_query_info | idx_query_info | 92      | const |  156 |   100.00 | NULL  |

四、explain type类型

all最慢,逐级效率越来越高（all<index<range<ref<eq_ref<const<NULL ）

1、all 全表扫描

mysql> -- 1.all 全表扫描
mysql> explain select * from order_detail where barcode = 'barcode_001';
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order_detail | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9769 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2、index 索引全扫描

mysql> -- 2.index 索引全扫描
mysql> explain select * from order_detail order by id;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | order_detail | NULL       | index | NULL          | PRIMARY | 8       | NULL | 9769 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3、range 索引范围查询

mysql> -- 3.range 索引范围查询
mysql> explain select * from order_detail where sync_no in ('300','380') ;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_detail | NULL       | range | idx_sync_no   | idx_sync_no | 98      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from order_detail where sync_no between '300' and '380' ;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_detail | NULL       | range | idx_sync_no   | idx_sync_no | 98      | NULL |  881 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from order_detail where sync_no >= '300' and sync_no <= '380' ;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_detail | NULL       | range | idx_sync_no   | idx_sync_no | 98      | NULL |  881 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from order_detail where sync_no like '30%' ;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | order_detail | NULL       | range | idx_sync_no   | idx_sync_no | 98      | NULL |  110 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

4、ref 使用了非主键、非唯一索引的扫描

mysql> -- 4.ref 使用了非主键、非唯一索引的扫描
mysql> explain select * from order_detail where vendor_code ='1000';
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order_detail | NULL       | ref  | idx_query_info | idx_query_info | 92      | const |  156 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

5、eq_ref 关联查询使用了主键或唯一索引

mysql> -- 5.eq_ref 关联查询使用了主键或唯一索引
mysql> explain select b.order_name from order_detail a, order_info b where a.id = b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL              |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | javaxiaobang.b.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

6、const 使用主键索引或唯一索引

mysql> -- 6.const 使用主键索引或唯一索引
mysql> explain select * from order_detail where sync_no = '285';
+----+-------------+--------------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | order_detail | NULL       | const | idx_sync_no   | idx_sync_no | 98      | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

7、NULL不用访问表或者索引直接得到结果

mysql> -- 7.null 不用访问表或者索引直接得到结果
mysql> explain select 1 from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)