mysql - 发送数据花费太长时间但索引已创建

Question

我在查询时遇到问题，该查询需要 20 秒才能返回结果:(

在表cases和cases_cstm中，我有960,000行

这是我的查询:

    SELECT  cases.id ,cases_cstm.assunto_c, cases.name , cases.case_number ,
      cases.priority  , accounts.name account_name , 
      accounts.assigned_user_id account_name_owner  , 
      'Accounts' account_name_mod, cases.account_id  , 
      LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) assigned_user_name , 
      jt1.created_by assigned_user_name_owner  , 
      'Users' assigned_user_name_mod, cases.status , cases.date_entered ,
      cases.assigned_user_id  
   FROM cases  
   LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c
   LEFT JOIN  accounts accounts ON 
      cases.account_id=accounts.id AND accounts.deleted=0   AND 
      accounts.deleted=0  
   LEFT JOIN  users jt1 ON 
      cases.assigned_user_id=jt1.id AND 
      jt1.deleted=0   AND jt1.deleted=0 
   where 
     (((LTRIM(RTRIM(CONCAT(IFNULL(accounts.name,'')))) LIKE 'rodrigo fernando%' OR 
     LTRIM(RTRIM(CONCAT(IFNULL(accounts.name,'')))) LIKE 'rodrigo fernando%'))) AND 
     cases.deleted=0 ORDER BY cases.date_entered DESC LIMIT 0,11;

这是表的索引:

+-------+------------+--------------------+--------------+------------------
+-----------+-------------+----------+--------+------+------------+--------
| Table | Non_unique | Key_name           | Seq_in_index | Column_name |Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment 
+-------+------------+--------------------+--------------+------------------
+-----------+-------------+----------+--------+------+------------+---------
| cases |          0 | PRIMARY            |            1 | id          | A         |      911472 |     NULL | NULL   |      | BTREE      |          
| cases |          0 | case_number        |            1 | case_number | A         |      911472 |     NULL | NULL   |      | BTREE      |         
|
| cases |          1 | idx_case_name      |            1 | name        | A         |      911472 |     NULL | NULL   | YES  | BTREE      |         
|
| cases |          1 | idx_account_id     |            1 | account_id  | A         |      455736 |     NULL | NULL   | YES  | BTREE      |         
|
| cases |          1 | idx_cases_stat_del |            1 | assigned_user_id| A         |         106 |     NULL | NULL   | YES  | BTREE      |         
|
| cases |          1 | idx_cases_stat_del |            2 | status      | A         |         197 |     NULL | NULL   | YES  | BTREE      |         
|
| cases |          1 | idx_cases_stat_del |            3 | deleted     | A         |         214 |     NULL | NULL   | YES  | BTREE      |         
|
| cases |          1 | idx_priority       |            1 | priority    | A         |      455736 |     NULL | NULL   | YES  | BTREE      |         
|
| cases |          1 | idx_date_entered   |            1 | date_entered| A         |      455736 |     NULL | NULL   | YES  | BTREE      |         
+-------+------------+--------------------+--------------+------------------
+-----------+-------------+----------+--------+------+------------+---------

The Explain command of query(Image!)

这是查询执行的概况:

+--------------------+-----------+
| Status             | Duration  |
+--------------------+-----------+
| starting           |  0.000122 |
| Opening tables     |  0.000180 |
| System lock        |  0.000005 |
| Table lock         |  0.000005 |
| init               |  0.000051 |
| optimizing         |  0.000017 |
| statistics         |  0.000071 |
| preparing          |  0.000021 |
| executing          |  0.000003 |
| Sorting result     |  0.000004 |
| Sending data       | 21.595455 |
| end                |  0.000012 |
| query end          |  0.000002 |
| freeing items      |  0.000419 |
| logging slow query |  0.000005 |
| logging slow query |  0.000002 |
| cleaning up        |  0.000004 |

有人可以帮助我理解为什么查询需要这么长时间才能执行吗？

谢谢!!

Answer 1

首先，将帐户的 LEFT JOIN 更改为 INNER JOIN 我不知道这是否会产生巨大的变化，但如果你明白其中的区别。

您使用LEFT JOIN所说的是“我想要所有案例，无论它们是否有关联的帐户”。 INNER JOIN 这里的意思是“给我所有案例并返回它们的所有帐户”。

您的查询的最终结果是相同的，因为您稍后会使用 WHERE 子句过滤掉内容，但我有一种感觉，这可能为什么 idx_account_id 被忽略。

第二个可能更大的问题是你的 where 子句:

 (((LTRIM(RTRIM(CONCAT(IFNULL(accounts.name,'')))) LIKE 'rodrigo fernando%' OR 
 LTRIM(RTRIM(CONCAT(IFNULL(accounts.name,'')))) LIKE 'rodrigo fernando%'))) AND

这里有大量的函数，MySQL 无法使用索引对其进行优化。将检查每条记录是否存在这种情况，并且将为每条记录调用您正在使用的所有函数。这很可能是最大的问题。

首先，这可以稍微简化一下。我认为这个 OR 语句的两边是相同的，所以让我们首先将其变成一个:

LTRIM(RTRIM(CONCAT(IFNULL(accounts.name,'')))) LIKE 'rodrigo fernando%'

既然要在 switch 语句的一侧添加通配符，为什么还要使用 RTRIM？

LTRIM(CONCAT(IFNULL(accounts.name,''))) LIKE 'rodrigo fernando%'

如果只有一件事，你不需要 CONCAT 任何东西!

LTRIM(IFNULL(accounts.name,'')) LIKE 'rodrigo fernando%'

LTRIM 在 NULL 值上工作得很好

LTRIM(accounts.name) LIKE 'rodrigo fernando%'

好吧，这为我们节省了很多功能。然而，最后一个 LTRIM 仍然是一个主要问题，因为它仍然完全阻止 mysql 使用索引。不过解决方案相当简单:

1. 更新您的帐户表一次:

   更新帐户 SET name = LTRIM(name);

2. 确保每当您插入新帐户时，都会在插入之前进行修剪。因此，您现在实际上是在 INSERT 时间而不是 SELECT 时间执行此操作。

3. 将之前的 WHERE 子句更改为:

   accounts.name LIKE 'rodrigo fernando%'

Boom，您现在可以在accounts.name 上使用索引，而且速度快极了。