mysql in 集合查询优化问题。

Question

问题描述 1.mysql in 集合查询 效率太低，时间过长。 问题出现的环境背景及自己尝试过哪些方法 user_id 和 physicion 是此表关联的用户字段。因为要求上级可看到下级， (99, 166, 96)这个集合是下级用户Id集合。in 查询集合效率太低，不知道有和解决方法，集合是从另外一个数据库查的所以无法关联查询。 相关代码 // 请把代码文本粘贴到下方（请勿用图片代替代码）SELECT a.id, a.case_id AS caseId, a.create_time AS createTime, a.case_state AS caseState, a.ealigner_treatment_plan AS ealignerTreatmentPlan, a.patient_name AS patientName, a.remarks, a.is_valid AS isValid, b.fileUrl FROM cm_caseinfo AS a LEFT JOIN cm_filesinfo AS b ON b.caseInfo_id = a.case_id AND b.fileTypeKey_id = 9 AND b.`status` = 1 WHERE ( a.user_id OR a.physicion IN (99, 166, 96) ) AND a.is_valid = 1 ORDER BY a.create_time DESC LIMIT 10 CREATE TABLE cm_caseinfo (id varchar(50) NOT NULL COMMENT '主键Id',address varchar(255) DEFAULT NULL COMMENT '地址',age int(11) DEFAULT NULL COMMENT '年龄（后台计算）',birthday varchar(255) DEFAULT NULL COMMENT '生日必填',case_id varchar(255) NOT NULL COMMENT '病例号（时间+序列号）',case_state int(11) unsigned NOT NULL DEFAULT '8' COMMENT '病例状态：0未完成，1正常，2删除',create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',ealigner_treatment_plan int(11) DEFAULT '0' COMMENT '治疗计划（1，舌侧，2透明，3唇侧）',gender int(11) DEFAULT NULL COMMENT '性别（0，男，1，女）',patient_name varchar(255) DEFAULT NULL COMMENT '患者姓名',patient_name_pingyin varchar(255) DEFAULT NULL COMMENT '患者姓名的拼音',phone varchar(255) DEFAULT NULL COMMENT '患者电话（兼容之前数据库）',physicion int(10) DEFAULT NULL COMMENT '主治医生ID',profession varchar(255) DEFAULT NULL COMMENT '医生的单位',treament_date datetime DEFAULT NULL COMMENT '治疗日期',update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',user_id int(11) DEFAULT NULL COMMENT '操作人Id',remarks varchar(255) DEFAULT NULL COMMENT '备注',operator_ip varchar(255) DEFAULT NULL COMMENT '操作人IP',operator_id varchar(255) DEFAULT NULL COMMENT '操作人姓名',is_valid tinyint(11) DEFAULT '1',org_id varchar(20) DEFAULT NULL COMMENT '组织ID', PRIMARY KEY (id) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; 你期待的结果是什么？实际看到的错误信息又是什么？

Answer 1

首先啊，从你的建表语句中没看到为user_id和physicion字段建立索引，你应该：

alter table cm_caseinfo add index `ix_user_id` on (`user_id`);
alter table cm_caseinfo add index `ix_physicion` on (`physicion`);

其次，你的select语句当中，通过『b.caseInfo_id = a.case_id 』来关联了两个表，这里也最好建立索引：

alter table cm_caseinfo add index `ix_case_id` on (`case_id`);
alter table cm_filesinfo add index `ix_caseInfo_id` on (`caseInfo_id`);

索引一旦建立，查询起来会很快，并且在join当中如果能用啥建立过索引的关联字段也会很快。

然后where语句当中的『( a.user_id OR a.physicion IN (99, 166, 96) ) 』这种写法很糟糕，只能利用一个索引，应该写成：

(a.user_id IN (99, 166, 96) OR a.physicion IN (99, 166, 96) ) 

希望能帮助到你。