一个简单的左外链接。查询很慢。麻烦看一下为啥加上索引不对
表结构如下
CREATE TABLE `t_event_share_read` (
`id` varchar(36) NOT NULL COMMENT ,
`eventId` varchar(36) DEFAULT NULL COMMENT ,
`agentId` varchar(36) DEFAULT NULL COMMENT ,
`fDate` datetime DEFAULT NULL COMMENT ,
`ftype` varchar(36) DEFAULT NULL COMMENT ,
`source` varchar(36) DEFAULT NULL COMMENT ,
`orgCode` varchar(255) DEFAULT NULL COMMENT ,
PRIMARY KEY (`id`),
KEY `agentId` (`agentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `t_agent` (
`userid` varchar(64) NOT NULL COMMENT ,
`mobileid` varchar(32) NOT NULL COMMENT ,
`state` int(4) NOT NULL DEFAULT '1' COMMENT ,
`level` varchar(32) DEFAULT NULL COMMENT ,
`validcommission` decimal(32,2) DEFAULT '0.00' COMMENT ,
`pickUpCommission` decimal(11,2) DEFAULT '0.00' COMMENT ,
`applyCommission` decimal(11,2) DEFAULT '0.00' COMMENT ,
`settleCommission` decimal(11,2) DEFAULT '0.00' COMMENT ,
`noSettleCommission` decimal(11,2) DEFAULT '0.00' COMMENT ,
`checkCode` varchar(200) DEFAULT NULL COMMENT ,
`create_date` datetime DEFAULT NULL COMMENT ,
`update_date` datetime DEFAULT NULL COMMENT ,
`create_by` varchar(50) DEFAULT NULL COMMENT ,
`create_name` varchar(50) DEFAULT NULL COMMENT ,
`sys_company_code` varchar(50) DEFAULT NULL COMMENT ,
`update_by` varchar(50) DEFAULT NULL COMMENT ,
`sys_org_code` varchar(50) DEFAULT NULL COMMENT ,
`bpm_status` varchar(32) DEFAULT '1' COMMENT ,
`update_name` varchar(50) DEFAULT NULL COMMENT ,
`payType` varchar(36) DEFAULT NULL COMMENT ,
`isManual` varchar(36) DEFAULT '1' COMMENT ,
`orgCode` varchar(36) DEFAULT NULL COMMENT ,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain SELECT * FROM t_event_share_read AS A left JOIN t_agent AS B ON A.agentId=B.userid
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A ALL NULL NULL NULL NULL 5280 NULL
1 SIMPLE B ALL NULL NULL NULL NULL 18968 Using where; Using join buffer (Block Nested Loop)
show index from t_event_share_read
t_event_share_read 0 PRIMARY 1 id A 5280 NULL NULL BTREE
t_event_share_read 1 agentId 1 agentId A 5280 NULL NULL YES BTREE
show index from t_agent
t_agent 0 PRIMARY 1 userid A 18968 NULL NULL BTREE