测试mysql的查询速度很慢_mysql查询运行速度很慢。请帮我索引

我试图运行一个查询，需要5秒钟才能执行100000行。查询如下。我尝试了所有可能的索引。请告诉我我错过了什么。

select distinct db_books.bookid as id

, request_type.name as book_type

, request_type.id as book_type_id

, db_books.subject as subject

, sender_user.uid as sender_user_id

, sender_user.username as sender_user

, sender_company.companyid as sender_company_id

, sender_company.companyname as sender_company

, sender_team_id.teamid as sender_team_id

, sender_team_id.name as sender_team

, GROUP_CONCAT(distinct receiver_user_details.uid separator '|') as receiver_user_id

, GROUP_CONCAT(distinct receiver_user_details.username separator '|') as receiver_user

, GROUP_CONCAT(distinct receiver_company.companyid separator '|') as receiver_company_id

, GROUP_CONCAT(distinct receiver_company.companyname separator '|') as receiver_company

, GROUP_CONCAT(distinct receiver_team_details.teamid separator '|') as receiver_team_id

, GROUP_CONCAT(distinct receiver_team_details.name separator '|') as receiver_team

, status.id as statusid

, status.name as status

, db_books.modifydate as modified_date

, db_books.createddate as creation_date

, state.id as stateid

, state.name as state

, assignee.uid as assignee_user_id

, assignee.username as assignee_user

, purpose.name as purpose

, purpose.id as purposeid

, g.name as entityname

, g.entityid as entityid

from db_books db_books

inner join db_users sender_user on (sender_user.deleted=0 and sender_user.uid=db_books.sndrUserid)

inner join db_companies sender_company on (sender_company.deleted=0 and sender_company.companyid=db_books.sndrCompanyid)

inner join db_companies receiver_company on (receiver_company.deleted=0 and receiver_company.companyid=db_books.target_company_id)

inner join db_request_types request_type on (request_type.id=db_books.book_type_id)

left outer join db_teams sender_team_id on (sender_team_id.deleted=0 and sender_team_id.teamid=db_books.sender_team_id)

left outer join db_books_to_users receiver_user on (receiver_user.bookid=db_books.bookid)

left outer join db_users receiver_user_details on (receiver_user_details.uid=receiver_user.userid)

left outer join db_books_to_teams receiver_teams on (receiver_teams.bookid=db_books.bookid)

left outer join db_teams receiver_team_details on (receiver_team_details.teamid=receiver_teams.teamid)

left outer join db_request_status status on (status.id=db_books.statusid)

left outer join db_request_state_types state on (state.id=db_books.request_state_id)

left outer join db_request_purpose purpose on (purpose.id=db_books.request_purpose_id)

left outer join db_users assignee on (assignee.uid=db_books.assignee)

left outer join db_books_details mdtl on (mdtl.deleted=0 and mdtl.bookid=db_books.bookid)

left outer join db_entities g on (g.deleted=0 and g.entityid=mdtl.entityid)

where 1=1

and

(db_books.sndrUserid=25000000003265

or db_books.sender_team_id in (

select a.teamid from db_team_users a

inner join db_teams b on (b.teamid=a.teamid and b.deleted=0)

where a.userid=25000000003265

)

or db_books.bookid in (

select distinct bookid from db_books_to_users where userid=25000000003265

union

select distinct bookid from db_books_to_teams where teamid in

(

select a.teamid from db_team_users a

inner join db_teams b on (b.teamid=a.teamid and b.deleted=0)

where a.deleted=0 AND a.userid=25000000003265

)

)

)

group by db_books.bookid

limit 20解释计划如下。

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY sender_user ALL PRIMARY,u2 14573 Using where; Using temporary; Using filesort

1 PRIMARY db_books ref i_db_books_target_company_id,i_db_books_sndrUserid,i_db_books_sndrCompanyid,i_sndrUserid_sender_team_idbookid i_db_books_sndrUserid 7 mde_staging.sender_user.uid 41 Using where

1 PRIMARY sender_company eq_ref PRIMARY,db_companies_icd PRIMARY 7 mde_staging.db_books.sndrCompanyid 1 Using where

1 PRIMARY receiver_company eq_ref PRIMARY,db_companies_icd PRIMARY 7 mde_staging.db_books.target_company_id 1 Using where

1 PRIMARY sender_team_id eq_ref PRIMARY,db_teams_i PRIMARY 7 mde_staging.db_books.sender_team_id 1

1 PRIMARY receiver_user ref i_db_books_to_users_bookid i_db_books_to_users_bookid 7 mde_staging.db_books.bookid 1

1 PRIMARY receiver_user_details eq_ref PRIMARY,u2 PRIMARY 7 mde_staging.receiver_user.userid 1

1 PRIMARY receiver_teams ref i_db_books_to_teams_bookid i_db_books_to_teams_bookid 7 mde_staging.db_books.bookid 1

1 PRIMARY receiver_team_details eq_ref PRIMARY,db_teams_i PRIMARY 7 mde_staging.receiver_teams.teamid 1

1 PRIMARY status eq_ref PRIMARY PRIMARY 4 mde_staging.db_books.statusid 1

1 PRIMARY state eq_ref PRIMARY PRIMARY 4 mde_staging.db_books.request_state_id 1

1 PRIMARY purpose eq_ref PRIMARY PRIMARY 4 mde_staging.db_books.request_purpose_id 1

1 PRIMARY assignee eq_ref PRIMARY,u2 PRIMARY 7 mde_staging.db_books.assignee 1

1 PRIMARY mdtl ref db_books_details_bookid db_books_details_bookid 7 mde_staging.db_books.bookid 1

1 PRIMARY request_type ALL PRIMARY 4 Using where; Using join buffer

1 PRIMARY g eq_ref PRIMARY,db_entities7 PRIMARY 7 mde_staging.mdtl.entityid 1

3 DEPENDENT SUBQUERY db_books_to_users ref i_db_books_to_users_bookid i_db_books_to_users_bookid 7 func 1 Using where; Using temporary

4 DEPENDENT UNION db_books_to_teams ref i_db_books_to_teams_bookid i_db_books_to_teams_bookid 7 func 1 Using where; Using temporary

5 DEPENDENT SUBQUERY b eq_ref PRIMARY,db_teams_i PRIMARY 7 func 1 Using where

5 DEPENDENT SUBQUERY a ref db_team_users_i db_team_users_i 11 func,const 1 Using where

UNION RESULT ALL

2 DEPENDENT SUBQUERY b eq_ref PRIMARY,db_teams_i PRIMARY 7 func 1 Using where

2 DEPENDENT SUBQUERY a ref db_team_users_i db_team_users_i 7 func 1 Using where如果你看到解释计划的第一行，它没有使用可能的索引，然后使用文件排序等。不知道这是否是问题。请建议我如何解决这个或我使用什么索引？