MySQL Forums
Forum List  »  Optimizer & Parser

why left join can not use unique index?
Posted by: h h
Date: December 07, 2015 08:30PM

i have two tables saint_customer(table A) ,db_users(table B) ,and have a unique index on each join column,but i am very confused ,why i use table A right join table B ,A's unique index can be used,but when i use table A left join B ,B's unique index can not be used?

root@localhost:qa_ucenter 10:25:59>show create table saint_customer\G
*************************** 1. row ***************************
Table: saint_customer
Create Table: CREATE TABLE `saint_customer` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键,客户ID',
`gmt_create` datetime NOT NULL COMMENT '创建时间,入库时间',
`creator` int(11) NOT NULL COMMENT '创建人ID',
`gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
`modifier` int(11) NOT NULL COMMENT '更新人ID',
`is_deleted` char(1) CHARACTER SET utf8 DEFAULT 'N' COMMENT '是否删除,Y删除,N未删除',
`contacts_id` int(11) DEFAULT NULL COMMENT '主联系人ID',
`province` int(11) DEFAULT '-100' COMMENT '省',
`city` int(11) DEFAULT '-100' COMMENT '市',
`district` int(11) DEFAULT '-100' COMMENT '区',
`street` int(11) DEFAULT '-100' COMMENT '街道',
`address` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '客户来源',
`zipcode` int(10) DEFAULT NULL COMMENT '邮编',
`industry` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '产业',
`annual_income` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '年产值',
`ownership` int(2) DEFAULT NULL COMMENT '公司性质:1合资 2国企 3民营 4私营 5个体',
`rating` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '评分,成熟度',
`origin` int(2) DEFAULT NULL COMMENT '客户来源:1直销膜拜 2客户介绍 3 主动来电',
`chain_store_num` int(11) DEFAULT NULL COMMENT '连锁门店数',
`head_count` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '员工数',
`company_name` varchar(333) CHARACTER SET utf8 DEFAULT NULL COMMENT '客户公司名',
`contacts_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '联系人姓名',
`contacts_mobile` varchar(25) COLLATE utf8_bin DEFAULT NULL,
`qq` varchar(15) CHARACTER SET utf8 DEFAULT NULL COMMENT 'qq号',
`weixin` varchar(15) CHARACTER SET utf8 DEFAULT NULL COMMENT '微信号',
`imgs` varchar(4096) COLLATE utf8_bin DEFAULT NULL COMMENT '门店图片地址,多个地址用半角逗号给开',
`comment` text COLLATE utf8_bin COMMENT '公司备注',
`introduction` varchar(10000) COLLATE utf8_bin DEFAULT NULL COMMENT '门店简介',
`technical_grade` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '技术等级:初级,中级,高级',
`business_time_begin` time DEFAULT NULL COMMENT '门店开始营业时间',
`business_time_end` time DEFAULT NULL COMMENT '门店结束营业时间',
`user_global_id` varchar(60) COLLATE utf8_bin DEFAULT NULL COMMENT '全局同步ID',
`app_reg` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '注册系统来源',
`store_area` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '门店面积',
`primary_business` varchar(4096) COLLATE utf8_bin DEFAULT NULL COMMENT '主营项目',
`parent_id` int(11) DEFAULT NULL COMMENT '主客户的id',
`store_type` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '门店类型',
`store_district` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '门店位置',
`is_clean` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '门店是否干净',
`company_formal_name` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '门店标准名称',
PRIMARY KEY (`id`),
UNIQUE KEY `IDX_CONTACTS_MOBILE` (`contacts_mobile`),
KEY `IDX_GMT_CREATE` (`gmt_create`),
KEY `IDX_DISTRICT` (`district`),
KEY `IDX_CITY` (`city`),
KEY `IDX_PROVINCE` (`province`),
KEY `IDX_COMPANY_NAME` (`company_name`(255)),
KEY `IDX_CONTACTS_NAME` (`contacts_name`)
) ENGINE=InnoDB AUTO_INCREMENT=603973 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='客户表'
1 row in set (0.00 sec)


root@localhost:qa_ucenter 10:26:11>show create table db_users\G
*************************** 1. row ***************************
Table: db_users
Create Table: CREATE TABLE `db_users` (
`user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(11) NOT NULL COMMENT '所属门店id',
`client_num` varchar(128) NOT NULL COMMENT '客户编号(来自于CRM)',
`client_name` varchar(255) NOT NULL COMMENT '客户名称(来自于CRM)',
`sale_id` int(11) NOT NULL DEFAULT '0' COMMENT '与该客户绑定的销售员',
`bind_time` int(11) NOT NULL DEFAULT '0' COMMENT '销售人员跟客户绑定时间(来自于CRM)',
`is_bind_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '绑定关系十分解除(0:未解除 1:已解除)',
`email` varchar(60) DEFAULT NULL,
`user_name` varchar(60) NOT NULL DEFAULT '',
`user_title` varchar(100) DEFAULT NULL COMMENT '名称',
`contact` varchar(20) DEFAULT NULL COMMENT '联系人',
`province` int(11) DEFAULT NULL COMMENT '省份',
`city` int(11) DEFAULT NULL COMMENT '城市',
`district` int(11) unsigned NOT NULL DEFAULT '0',
`street` int(11) NOT NULL DEFAULT '0' COMMENT '街道',
`address` varchar(200) DEFAULT NULL COMMENT '详细地址',
`password` varchar(32) NOT NULL DEFAULT '',
`question` varchar(255) DEFAULT NULL,
`answer` varchar(255) DEFAULT NULL,
`sex` tinyint(1) unsigned NOT NULL DEFAULT '0',
`birthday` date NOT NULL DEFAULT '1970-01-01',
`user_money` decimal(10,2) NOT NULL DEFAULT '0.00',
`frozen_money` decimal(10,2) NOT NULL DEFAULT '0.00',
`pay_points` int(10) unsigned NOT NULL DEFAULT '0',
`rank_points` int(10) unsigned NOT NULL DEFAULT '0',
`address_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`reg_time` int(10) unsigned NOT NULL DEFAULT '0',
`last_ip` varchar(15) DEFAULT NULL,
`last_login` int(11) unsigned NOT NULL DEFAULT '0',
`last_time` datetime NOT NULL DEFAULT '1970-01-01 12:00:00',
`visit_count` smallint(5) unsigned NOT NULL DEFAULT '0',
`user_rank` tinyint(3) unsigned NOT NULL DEFAULT '0',
`is_special` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1:代表门店主帐号0:代表副帐号',
`ec_salt` varchar(10) DEFAULT NULL,
`salt` varchar(10) NOT NULL DEFAULT '0',
`parent_id` mediumint(9) NOT NULL DEFAULT '0',
`flag` tinyint(3) unsigned NOT NULL DEFAULT '0',
`alias` varchar(60) NOT NULL DEFAULT '0',
`msn` varchar(60) NOT NULL DEFAULT '0',
`qq` varchar(20) NOT NULL DEFAULT '0',
`office_phone` varchar(20) NOT NULL DEFAULT '0',
`home_phone` varchar(20) NOT NULL DEFAULT '0',
`mobile_phone` varchar(20) NOT NULL,
`verify` char(4) NOT NULL COMMENT '手机验证码',
`is_validated` tinyint(3) unsigned NOT NULL DEFAULT '0',
`credit_line` decimal(10,2) unsigned DEFAULT NULL,
`passwd_question` varchar(50) DEFAULT NULL,
`passwd_answer` varchar(255) DEFAULT NULL,
`zipcode` varchar(11) NOT NULL DEFAULT '000000' COMMENT '邮政编码',
`device_id` varchar(128) NOT NULL COMMENT '客户端设备号',
`short_name` varchar(128) NOT NULL COMMENT '客户简称',
`province_name` varchar(64) NOT NULL DEFAULT '' COMMENT '省份名称',
`city_name` varchar(64) NOT NULL DEFAULT '' COMMENT '城市名称',
`district_name` varchar(64) NOT NULL DEFAULT '' COMMENT '地区名称',
`staff_no` varchar(64) NOT NULL DEFAULT '0' COMMENT '销售员工号',
`comment` varchar(255) DEFAULT NULL,
`user_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '用户类型:1门店、2普通用户 ',
`verify_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '认证状态(0:缺省-未认证;1:认证中;2认证成功;-1:认证失败)',
`verify_time` datetime DEFAULT NULL COMMENT '认证时间',
`gmt_modifed` datetime DEFAULT NULL COMMENT '修改时间',
`customer_id` int(11) DEFAULT '0' COMMENT 'CRM的客户ID',
`user_global_id` varchar(32) NOT NULL DEFAULT '' COMMENT 'crm同步主键',
`app_reg` varchar(32) DEFAULT NULL COMMENT '用户创建来源',
`business_licence` varchar(100) DEFAULT '' COMMENT '营业执照',
`verify_feedback` varchar(300) DEFAULT '' COMMENT '小秘书反馈内容',
PRIMARY KEY (`user_id`),
UNIQUE KEY `unique_user_global_id` (`user_global_id`),
UNIQUE KEY `idx_username` (`user_name`),
KEY `email` (`email`),
KEY `parent_id` (`parent_id`),
KEY `flag` (`flag`),
KEY `customer_id_index` (`customer_id`),
KEY `customer_id1_index` (`customer_id`) COMMENT 'dd',
KEY `idx_gmt_modifed` (`gmt_modifed`)
) ENGINE=InnoDB AUTO_INCREMENT=150482 DEFAULT CHARSET=utf8 COMMENT='客户基础信息表'


root@localhost:qa_ucenter 10:27:38>explain select a.*,b.* from saint_customer a right join db_users b on a.contacts_mobile=b.user_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 119068
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: IDX_CONTACTS_MOBILE
key: IDX_CONTACTS_MOBILE
key_len: 78
ref: qa_ucenter.b.user_name
rows: 1
Extra:
2 rows in set (0.00 sec)

root@localhost:qa_ucenter 10:28:02>explain select a.*,b.* from saint_customer a left join db_users b on a.contacts_mobile=b.user_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50561
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: idx_username
key: NULL
key_len: NULL
ref: NULL
rows: 119068
Extra:
2 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
why left join can not use unique index?
2363
h h
December 07, 2015 08:30PM
862
h h
December 11, 2015 03:20AM


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.