MySQL Forums
Forum List  »  Optimizer & Parser

why the two query has a different execute plan?
Posted by: h h
Date: August 22, 2016 02:55AM

case one: ucenter.uc_shop can use index uk_userid

root@localhost:dw2 04:50:04>explain select us.id, us.user_id, dcty.id,dcty.is_deleted, dcty.tag_name from ucenter.uc_shop as us left join tqdw.dw_customer_type_yqx as dcty on us.id=dcty.customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: us
type: index
possible_keys: NULL
key: uk_userid
key_len: 5
ref: NULL
rows: 272132
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dcty
type: ref
possible_keys: customer_id
key: customer_id
key_len: 5
ref: ucenter.us.id
rows: 1
Extra:
2 rows in set (0.00 sec)

case two: ucenter.uc_shop can not use index uk_userid

root@localhost:dw2 04:49:27>explain select * from ucenter.uc_shop as us left join tqdw.dw_customer_type_yqx as dcty on us.id=dcty.customer_id\G *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: us
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 272132
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dcty
type: ref
possible_keys: customer_id
key: customer_id
key_len: 5
ref: ucenter.us.id
rows: 1
Extra:
2 rows in set (0.00 sec)

root@localhost:dw2 04:53:17>show create table ucenter.uc_shop\G
*************************** 1. row ***************************
Table: uc_shop
Create Table: CREATE TABLE `uc_shop` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '创建时间',
`creator` int(11) NOT NULL DEFAULT '0' COMMENT '创建人ID',
`gmt_modified` datetime DEFAULT '1970-01-01 12:00:00' COMMENT '更新时间',
`modifier` int(11) NOT NULL DEFAULT '0' COMMENT '更新人ID',
`is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '是否删除,Y删除,N未删除',
`company_name` varchar(300) NOT NULL DEFAULT '' COMMENT '门店名称',
`company_formal_name` varchar(300) DEFAULT '' COMMENT '门店营业执照名称',
`client_num` varchar(128) DEFAULT '' COMMENT '电商系统客户编码,主要在金蝶系统中使用',
`user_id` int(11) unsigned DEFAULT NULL COMMENT '电商系统原有ID',
`verify_status` int(11) NOT NULL DEFAULT '0' COMMENT '认证状态:-1认证失败,0未认证,1认证中,2认证通过',
`shop_type` int(10) unsigned NOT NULL DEFAULT '2' COMMENT '客户交易类型:1表示B用户,2表示C用户,3表示KA客户',
`shop_status` int(11) NOT NULL DEFAULT '0' COMMENT '门店状态:-1冻结,0正常',
`user_rank` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '会员等级',
`rank_points` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '等级积分值',
`owner_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '门店归属小淘汽的组织id,归属淘汽档口的门店组织id为0',
`app_source` varchar(32) DEFAULT '' COMMENT '系统来源',
`memo` varchar(1024) DEFAULT '' COMMENT '备注',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_userid` (`user_id`) USING BTREE COMMENT 'user_id的唯一索引',
UNIQUE KEY `uk_clientnum` (`client_num`)
) ENGINE=InnoDB AUTO_INCREMENT=321275 DEFAULT CHARSET=utf8 COMMENT='UC门店信息表'
1 row in set (0.00 sec)

root@localhost:dw2 04:55:01>show create table tqdw.dw_customer_type_yqx\G
*************************** 1. row ***************************
Table: dw_customer_type_yqx
Create Table: CREATE TABLE `dw_customer_type_yqx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`creator` varchar(255) DEFAULT '',
`modifier` varchar(255) DEFAULT '',
`gmt_create` datetime DEFAULT NULL,
`gmt_modified` datetime DEFAULT NULL,
`is_deleted` varchar(2) DEFAULT '',
`user_id` int(11) DEFAULT NULL COMMENT '电商系统原有ID',
`customer_id` int(11) DEFAULT NULL,
`company_name` varchar(333) DEFAULT NULL COMMENT '门店名称',
`contacts_name` varchar(333) DEFAULT NULL,
`company_formal_name` varchar(333) DEFAULT NULL,
`contacts_mobile` varchar(25) DEFAULT NULL,
`tag_key` varchar(100) DEFAULT NULL,
`tag_name` varchar(64) DEFAULT NULL,
`province` int(4) DEFAULT NULL,
`city` int(4) DEFAULT NULL,
`district` int(4) DEFAULT NULL,
`avg_month_amount` decimal(12,2) DEFAULT NULL COMMENT '被指定给云修顾问时间后三月平均采购额',
`dispatch_date_service` datetime DEFAULT NULL COMMENT '云修顾问客户入库时间',
`join_in_time` datetime DEFAULT NULL COMMENT '云修客户签约加盟时间',
PRIMARY KEY (`id`),
UNIQUE KEY `customer_id` (`customer_id`),
KEY `mobile_index` (`contacts_mobile`),
KEY `key_index` (`tag_key`),
KEY `user_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8882 DEFAULT CHARSET=utf8 COMMENT='云修客户明细表'
1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
why the two query has a different execute plan?
2156
h h
August 22, 2016 02:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.