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) ) 希望能帮助到你。