即使有索引，MySQL 语句也非常慢

Question

以下查询大约需要 200 秒才能完成。我想要实现的目标是吸引已支付 6 次或更多次但尚未下任何订单的用户(不同市场有 2 个订单表)。

u.id, ju.id 都是主键

我已将 user_id 和 order_status 合并为两个订单表上的一个索引。如果我删除 mp_orders 表上的连接和 COUNT()，查询需要 8 秒才能完成，但有了它，它会花费太长时间。我想我已经为所有我可以拥有的东西编制了索引，但我不明白为什么要花这么长时间才能完成。有什么想法吗？

SELECT 
    u.id, 
    ju.name,
    COUNT(p.id) as payment_count, 
    COUNT(o.id) as order_count,
    COUNT(mi.id) as marketplace_order_count
FROM users as u
    INNER JOIN users2 as ju
        ON u.id = ju.id
    INNER JOIN payments as p
        ON u.id = p.user_id
    LEFT OUTER JOIN orders as o
        ON u.id = o.user_id
            AND o.order_status = 1
    LEFT OUTER JOIN mp_orders as mi
        ON u.id = mi.producer
            AND mi.order_status = 1
WHERE u.package != 1
AND u.enabled = 1
AND u.chart_ban = 0
GROUP BY u.id
HAVING COUNT(p.id) >= 6
    AND COUNT(o.id) = 0
    AND COUNT(mi.id) = 0
LIMIT 10

付款表

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | bigint(255)   | NO   | PRI | NULL    | auto_increment |
| user_id         | bigint(255)   | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

orders 表(mp_orders 表几乎相同)

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(255)      | NO   | PRI | NULL    | auto_increment |
| order_number    | varchar(1024) | NO   | MUL | NULL    |                |
| user_id         | int(255)      | NO   | MUL | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

Answer 1

您不需要计算订单的行数，您需要检索没有订单的用户，这实际上不是一回事。

不统计，过滤没有订单的用户:

SELECT 
    u.id, 
    ju.name,
    COUNT(p.id) as payment_count
FROM users as u
    INNER JOIN users2 as ju
        ON u.id = ju.id
    INNER JOIN payments as p
        ON u.id = p.user_id
    LEFT OUTER JOIN orders as o
        ON u.id = o.user_id
            AND o.order_status = 1
    LEFT OUTER JOIN mp_orders as mi
        ON u.id = mi.producer
            AND mi.order_status = 1
WHERE u.package != 1
AND u.enabled = 1
AND u.chart_ban = 0
AND o.id IS NULL    -- filter happens here
AND mi.id IS NULL   -- and here
GROUP BY u.id
HAVING COUNT(p.id) >= 6
LIMIT 10

这将防止引擎为您的每个用户计算每个订单，您将获得大量时间。

可以认为引擎应该使用索引来进行计数，因此计数必须足够快。
I will quote from a different site: InnoDB COUNT(id) - Why so slow?

It may be to do with the buffering, InnoDb does not cache the index it caches into memory the actual data rows, because of this for what seems to be a simple scan it is not loading the primary key index but all the data into RAM and then running your query on it. This may take some time to work - hopefully if you were running queries after this on the same table then they would run much faster.

MyIsam loads the indexes into RAM and then runs its calculations over this space and then returns a result, as an index is generally much much smaller than all the data in the table you should see an immediate difference there.

Another option may be the way that innodb stores the data on the disk - the innodb files are a virtual tablespace and as such are not necessarily ordered by the data in your table, if you have a fragmented data file then this could be creating problems for your disk IO and as a result running slower. MyIsam generally are sequential files, and as such if you are using an index to access data the system knows exactly in what location on disk the row is located - you do not have this luxury with innodb, but I do not think this particular issue comes into play with just a simple count(*) ==================== http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html explains this:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 14.2.11, “InnoDB Performance Tuning Tips”. =================== todd_farmer:It actually does explain the difference - MyISAM understands that COUNT(ID) where ID is a PK column is the same as COUNT(*), which MyISAM keeps precalculated while InnoDB does not.