索引以在多个表中搜索

Question

问题

我的查询很慢。

我试图通过给定的搜索词"J“找到多个customer。字段im扫描分布在两个表中，customer和company_customer。

• firstname从
• lastname从
• number以(这是公司/custumer组合的特定客户号)开头。

我很难优化数据库和查询(运行mysql-8)。

我已经对10家公司和100,000名客户进行了测试。搜索花费了近半秒的--必须有更快的方法才能做到这一点。

查询

SELECT
    cc.number,
    c.firstname,
    c.lastname
FROM
    customer c
    JOIN company_customer cc
        ON cc.customer_id = c.id
WHERE
    cc.company_id = 1
    AND (
           c.lastname LIKE 'J%'
        OR c.firstname LIKE 'J%'
        OR cc.number LIKE 'J%'
    )
ORDER BY
    lastname,
    firstname
LIMIT 20;

模式(简化)

• 公司(id，.)
• 顾客(身份证.)
• company_customer (company_id，customer_id，number)

小提琴

我试过的

• 删除实际的搜索部分，以找出问题(每个like) --仍然很慢
• 通过子选择获得一个customer.id列表--甚至更慢
• 拆分搜索--但是当我搜索customer的时候，我只想搜索那些与特定的company_cusomer.company_id相关联的--所以我不能绕过联接，对吗？
• 使用EXPLAIN查找可能的索引

我所发现的

我认为问题在于lastname, firstname的索引没有被使用，因为JOIN占据了索引的使用范围，因为只能使用一个索引。

问题是

是否有办法设置索引以更快地获取数据？还是一种重写sql以获得更快数据的方法？

编辑

解释(在客户中有100万行之前)：

+----+-------------+-------+------------+--------+-------------------------+---------+---------+---------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys           | key     | key_len | ref                 | rows   | filtered | Extra                           |
+----+-------------+-------+------------+--------+-------------------------+---------+---------+---------------------+--------+----------+---------------------------------+
|  1 | SIMPLE      | cc    | NULL       | ref    | PRIMARY,IDX_co,IDX_cu   | PRIMARY | 4       | const               | 204966 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                 | PRIMARY | 4       | test.cc.customer_id |      1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+-------------------------+---------+---------+---------------------+--------+----------+---------------------------------+

解释(在1M行之前)：

+----+-------------+-------+------------+--------+-----------------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys         | key     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+-----------------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | index  | PRIMARY               | IDX_lf  | 804     | NULL            |   20 |   100.00 | Using index |
|  1 | SIMPLE      | cc    | NULL       | eq_ref | PRIMARY,IDX_co,IDX_cu | PRIMARY | 8       | const,test.c.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+-----------------------+---------+---------+-----------------+------+----------+-------------+

Answer 1

OR (特别是因为它涉及多个表)尤其难以优化。

这些可能有助于：

cc:  INDEX(company_id, number, customer_id)
c:  INDEX(lastname, firstname, id)

最好的优化(特别是对于大型表)是切换到UNION

SELECT *
    FROM (
        ( SELECT ... with one of the LIKEs ...
              ORDER BY c.lastname, c.firstname LIMIT 20 )
        UNION DISTINCT
        ( SELECT ... with another of the LIKEs ...
              ORDER BY c.lastname, c.firstname LIMIT 20 )
        UNION DISTINCT
        ( SELECT ... with the other LIKE ...
              ORDER BY c.lastname, c.firstname LIMIT 20 )
         ) AS x
    ORDER BY lastname, firstname   -- again
    LIMIT 20      -- again
    ;

和更多索引(注意对列进行重新排序：

cc:  INDEX(number, company_id, customer_id)
c:  INDEX(firstname, lastname, id)

(可能还有进一步的优化，但首先尝试一下。)