select * from ( select *, '12345678901234567890123456789012' as ComStatusInfo , '1234567890' as DtuStatus , (select User_Info from dccuser where dccuser.ID=ComUser) as ComUserInfo , (select User_Info from dccuser where dccuser.ID=ComVerifyUser)as VerifyUserInfo , ( select btimeout from terminal where comlist.terminal = terminal.id )as btimeout , ( select alias from terminal where comlist.terminal = terminal.id )as Alias , ( select owner from terminal where comlist.terminal = terminal.id )as UserGroupId from comlist ) A where find_in_set(UserGroupId,getGroupLst(1,255)) order by id desc limit 100;
explain select ...
show index from ..
以供分析。
你的语句太复杂了，要简化另外，find_in_set 也会导致更慢。
建议把 子查询，改成 left join 吧
看起来好像是find_in_set导致的,使用find_in_set的话,要3分20秒.如果是直接使用这个的话,只要0.83秒UserGroupId in (1,3,27,238,240,28,35,44,48,51,53,54,55,56,58,59,63,65,68,76,77,80,81,83,85,87,88,89,90,92,94,96,98,100,102,104,107,109,111,113,115,117,121,123,126,128,130,132,134,136,138,140,219,222,225,226,227,236,247,248,253,258,259,260,261,262,263,264,265,266,293,294,295,332,333,334,336,337,340,341,342,345,346,351,353,354,356,357,358,360,361,362,364,365,366,367,369,370,371,372,374,381,382,383,384,385,386,387,388,391,395,401,402,403,404,408,409,417,418,419,425,430,432,433,435,436,437,438,442,443,444,447,478,479,481,498,499,500,541,542,547,30,31,32,33,36,38,41,43,46,47,49,57,62,64,67,75,79,82,84,86,91,93,95,97,99,101,103,106,108,110,112,114,116,120,122,124,125,127,131,133,135,137,139,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,220,224,228,237,242,243,250,251,252,254,257,280,281,282,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,338,343,349,350,363,389,397,398,399,400,414,422,424,426,454,458,459,461,483,484,494,503,506,507,509,510,511,512,543,544,244,245,246,296,297,298,299,300,301,302,390,411,413,423,427,428,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,480,485,486,487,488,489,490,491,493,495,496,497,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,545,546)