MySQL查询优化性能

Question

你能为我优化这个查询吗：

SELECT
case when EXISTS (
SELECT
1
FROM
usergroups item_t1
WHERE
(item_t0.p_b2bunits = item_t1.p_uid)
AND (item_t1.TypePkString = 8796095217746)
) THEN true ELSE false END as active,
item_t0.p_b2bunits as code,
item_t0.p_accountname as name1,
item_t0.p_accountnameextension as name2,
case when EXISTS (
SELECT
1
FROM
user2salreprelation item_t2
WHERE
(
item_t2.SourcePK = item_t0.PK
and item_t2.TargetPK = 8828959653892
)
AND (item_t2.TypePkString = 8796150399058)
) THEN true ELSE false END as isFav
FROM
account2salesrep item_t0
WHERE
(
item_t0.p_showondashboard = 1
AND item_t0.p_district = '4'
AND (
LOWER(item_t0.p_accountname) LIKE CONCAT('%', CONCAT('crampel alu', '%'))
OR LOWER(item_t0.p_accountnameextension) LIKE CONCAT('%', CONCAT('crampel alu', '%'))
OR LOWER(item_t0.p_b2bunits) LIKE CONCAT('%', CONCAT('crampel alu', '%'))
)
)
AND (item_t0.TypePkString = 8796149842002)
order by
item_t0.p_accountname
LIMIT
0, 11;

Answer 1

键入如果TypePkString是VARCHAR，则必须引用8796150399058，否则性能将非常糟糕。(相反的情况并不重要。(

true/false布尔表达式(如EXISTS(...)(为false返回0，为true返回1，因此此

CASE WHEN (...) THEN true ELSE false AS ...

可以简化

(...) AS ...

其中一些索引可能会有所帮助：

item_t0:  INDEX(p_showondashboard, p_district, TypePkString, p_accountname)
item_t1:  INDEX(p_uid, TypePkString)
item_t2:  INDEX(SourcePK, TargetPK, TypePkString)

不必要的LOWER假设p_accountname的排序规则以_ci结尾[请提供SHOW CREATE TABLE]，则可以删除不必要且缓慢的LOWER()。

带有前导通配符(%(的LIKE无法建立索引。您是否考虑过使用FULLTEXT(如果用户输入"单词"，而不是随机字符串(。这将涉及

FULLTEXT(p_accountname, p_accountnameextension, p_accountnameextension)
MATCH(p_accountname, p_accountnameextension, p_accountnameextension)
AGAINST('+crampel alu' IN BOOLEAN MODE)

crampel alu是列名吗？前缀？还有别的吗？