mysql数据库多表连接查询问题

码拜

11年 ago

数据库 mysql select 我有5个表 chanet_cmiims_person_info表 chanet_cmiism_person_commitstate表 chanet_cmiims_commitstate_type表 chanet_cmiims_person_state表 chanet_cmiims_person_state_type表这是我现在的查询语句，但是效率太低了，2600多条数据都要查询4秒多。请教如何提高此语句的查询效率 SELECT info.* from ( SELECT sinfo.personName as relName,state.id AS personStateId,stateName,cmit.id AS personCommitStateId,commitStateName,info.* FROM chanet_cmiims_person_info AS info INNER JOIN chanet_cmiism_person_commitstate AS cmit ON cmit.personInfoId=info.id INNER JOIN chanet_cmiims_commitstate_type AS committype ON committype.id=cmit.commitStateId INNER JOIN chanet_cmiims_person_state AS state ON info.id=state.personInfoId INNER JOIN chanet_cmiims_person_state_type AS statetype ON statetype.id =state.stateTypeId LEFT JOIN chanet_cmiims_person_info AS sinfo ON sinfo.id=info.relevancePerson) AS info
	在查询字段上建立索引试试
	贴出 explain select … show index from .. 以供分析。
	引用 2 楼 ACMAIN_CHM 的回复: 贴出 explain select … show index from .. 以供分析。
60分	请直接贴出 explain SELECT sinfo.personName as relName,state.id AS personStateId,stateName,cmit.id AS personCommitStateId,commitStateName,info.* FROM chanet_cmiims_person_info AS info INNER JOIN chanet_cmiism_person_commitstate AS cmit ON cmit.personInfoId=info.id INNER JOIN chanet_cmiims_commitstate_type AS committype ON committype.id=cmit.commitStateId INNER JOIN chanet_cmiims_person_state AS state ON info.id=state.personInfoId INNER JOIN chanet_cmiims_person_state_type AS statetype ON statetype.id =state.stateTypeId LEFT JOIN chanet_cmiims_person_info AS sinfo ON sinfo.id=info.relevancePerson 可能cmit state 表没有走索引。另外建议不要贴图，直接贴文本更方便别人分析。
	引用 4 楼 ACMAIN_CHM 的回复: 请直接贴出 explain SELECT sinfo.personName as relName,state.id AS personStateId,stateName,cmit.id AS personCommitStateId,commitStateName,info.* FROM chanet_cmiims_person_info AS info INNER JOIN chanet_cmiism_person_commitstate AS cmit ON cmit.personInfoId=info.id INNER JOIN chanet_cmiims_commitstate_type AS committype ON committype.id=cmit.commitStateId INNER JOIN chanet_cmiims_person_state AS state ON info.id=state.personInfoId INNER JOIN chanet_cmiims_person_state_type AS statetype ON statetype.id =state.stateTypeId LEFT JOIN chanet_cmiims_person_info AS sinfo ON sinfo.id=info.relevancePerson 可能cmit state 表没有走索引。另外建议不要贴图，直接贴文本更方便别人分析。谢谢版主。果然是这两个表索引的问题。给他加上索引就好了。谢谢！