Mysql按非常慢的、非驱动的表字段排序(或跨表查询)，而不使用索引

Question

I。问题的现象和背景

遇到的问题：

• Mysql按非常慢的非驱动表字段排序，不使用索引

• a.CreatedAt查询非常快(119ms-Get(；f.类型查询非常慢(12.194s Get(

现象：在MySQL中，对多个表执行左联接(视图(查询，并使用Order By FIELD((函数执行指定的排序。发现没有使用索引，导致查询速度慢

需求背景：根据创建时间按反序或正序排列，默认将审批请求放在底部

-- Query view: vi_orderinfo
EXPLAIN
SELECT
*
FROM
vi_orderinfo b
WHERE
1= 1 and b.Status!= 5
ORDER BY
FIELD(b.Type,0,4,1,2,3),
b.CreatedAt DESC;

II。相关定义：

f表类型字段被编入索引：NON类型

f表与a表(驱动程序表(相关：a JOIN f ON f.OrderId=a Id

我需要指定排序，类型=1,2,3查询结果集应该显示在的末尾

MySQL数据库的当前版本

SELECT VERSION() vi_orderinfo 
-- Output：
vi_worderinfo|  
-------------+  
8.0.18       | 

跨表查询

当您跨表查询时，索引将消失

-- Query the relationship between table a and table f in view: vi_orderinfo
EXPLAIN   
SELECT
*
FROM
(
SELECT
`a`.*,
`f`.`Type` AS `Type`
FROM
`tb_orderinfo` `a`
left JOIN 
`tb_status` `f` ON  `f`.`OrderId` = `a`.`Id`   AND `f`.`Deleted` = 0
WHERE
`a`.`Deleted` = 0  AND `a`.`ServiceProviderCode` IS NULL 
)vw
WHERE
1 = 1  AND vw.WorkStatus != 10
ORDER BY 
FIELD(vw.TypeStatus, 0, 4, 1, 2, 3) ,
vw.InstallTime DESC;

--  Output
id|select_type|table|partitions|type|possible_keys  |key            |key_len|ref                    |rows |filtered|Extra      |
--+-----------+-----+----------+----+---------------+---------------+-------+-----------------------+-----+--------+-----------+
1|SIMPLE     |a    |          |ALL |               |               |       |                       |53129|   100.0|           |
1|SIMPLE     |f    |          |ref |NON-OrderId|NON-OrderId|37     |serviceordercenter.a.Id|    1|   100.0|Using where|

III。我的解决方案和尝试过的方法

CASE WHEN：查询也很慢，并且没有使用索引

-- Query view: vi_orderinfo
SELECT *,
case when b.Type in (1,2,3) then 1 else 0 END AS RequestReviewType
FROM vi_orderinfo b
WHERE 1=1
and b.Status != five
ORDER BY
RequestReviewType ASC，CreatedAt desc

FIELD((：第二个改为上述方面的Demo，函数FIELD(

剑：刚开始的时候是左击。后来，它被改为jion。结果集的大小是个问题。和jion一样，内心的jion写了jion

STRIGHT_JOIN:此视图用于多个接口，驱动程序表a在其他地方大量使用，不会影响其他模块的排序

冗余字段、临时表和虚拟列似乎不起作用

4、 我想要实现的目标

如何解决查询时间慢的问题？我使用分页查询请求超过10秒，太长了！

我的大脑停止了，请帮我TVT

Answer 1

这可能有助于：

SELECT  a2.*
FROM  (
SELECT  a1.*
FROM  `tb_orderinfo` `a1`
WHERE  a1.Status!= 5
AND  `a1`.`Deleted` = 0
AND  `a1`.`ProviderCode` IS NULL 
) AS a2
LEFT JOIN  `tb_order` `b`  ON `b`.`OrderId` = `a2`.`Id` AND  `b`.`Deleted` = 0
LEFT JOIN  `tb_app` `c`    ON `c`.`OrderId` = `a2`.`Id` AND  `c`.`Deleted` = 0
LEFT JOIN  `tb_cust` `d`   ON `d`.`OrderId` = `a2`.`Id` AND  `d`.`Deleted` = 0
LEFT JOIN  `tb_remarksinfo` `e`
ON `e`.`OrderId` = `a2`.`Id` AND  `e`.`Deleted` = 0
JOIN  `tb_status` `f` 
ON `f`.`OrderId` = `a2`.`Id` AND  `f`.`Deleted` = 0
ORDER BY  FIELD(a2.`Type`,0,4,1,2,3) , a2.CreatedAt DESC

带索引

a1:  INDEX(Deleted, ProviderCode, Status)
b:  INDEX(OrderId,  Deleted)
c:  INDEX(OrderId,  Deleted)
d:  INDEX(OrderId,  Deleted)
e:  INDEX(OrderId,  Deleted)
f:  INDEX(OrderId,  Deleted)

但真正加快速度的是去掉LEFT JOINs，因为您没有将这些表用于任何事情！优化器可能太笨了，无法为您抛出它们。

注意：如果还有一个LIMIT，我们需要在派生表中复制ORDER BY，并只将LIMIT放在那里。

至于FIELD()不使用索引——没有函数允许使用索引。参见：可扫描