即使使用索引,MySQL语句也非常慢

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了即使使用索引,MySQL语句也非常慢，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4775字，纯文字阅读大概需要7分钟。

内容图文

以下查询大约需要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    |                |
+-----------------+---------------+------+-----+---------+----------------+

订单表(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    |                |
+-----------------+---------------+------+-----+---------+----------------+

解决方法:

您不需要对订单行进行COUNT操作,您需要检索没有订单的用户,这不是真正相同的事情.

而不是计数,过滤没有订单的用户：

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(*)
==================== 07001
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.

内容总结

以上是互联网集市为您收集整理的即使使用索引,MySQL语句也非常慢全部内容，希望文章能够帮你解决即使使用索引,MySQL语句也非常慢所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端