如何提高 MySQL 查询的性能?

发布于 2024-09-27 19:16:03 字数 627 浏览 4 评论 0原文

我有一个 MySQL 查询:

   SELECT DISTINCT 
          c.id, 
          c.company_name, 
          cd.firstname, 
          cd.surname, 
          cis.description AS industry_sector 
     FROM (clients c) 
     JOIN clients_details cd ON c.id = cd.client_id 
LEFT JOIN clients_industry_sectors cis ON cd.industry_sector_id = cis.id 
    WHERE c.record_type='virgin'
 ORDER BY date_action, company_name asc, id desc 
    LIMIT 30

clients 表大约有 60-70k 行,并且有一个“id”、“record_type”、“date_action”和“company_name”索引 - 不幸的是,查询仍然需要 5 秒以上才能完成。删除“ORDER BY”会将其减少到大约 30 毫秒,因为不需要文件排序。有什么方法可以更改此查询以改善 5 秒以上的响应时间吗?

I have a MySQL query:

   SELECT DISTINCT 
          c.id, 
          c.company_name, 
          cd.firstname, 
          cd.surname, 
          cis.description AS industry_sector 
     FROM (clients c) 
     JOIN clients_details cd ON c.id = cd.client_id 
LEFT JOIN clients_industry_sectors cis ON cd.industry_sector_id = cis.id 
    WHERE c.record_type='virgin'
 ORDER BY date_action, company_name asc, id desc 
    LIMIT 30

The clients table has about 60-70k rows and has an index for 'id', 'record_type', 'date_action' and 'company_name' - unfortunately the query still takes 5+ secs to complete. Removing the 'ORDER BY' reduces this to about 30ms since a filesort is not required. Is there any way I can alter this query to improve upon the 5+ sec response time?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

滥情稳全场 2024-10-04 19:16:03

请参阅: http://dev.mysql.com/doc /refman/5.0/en/order-by-optimization.html

特别是:

在某些情况下,MySQL 无法使用索引来解析 ORDER BY (..)。这些案例包括以下内容:
(..)
您正在连接许多表,并且 ORDER BY 中的列并不全部来自用于检索行的第一个非常量表。 (这是 EXPLAIN 输出中第一个没有 const 连接类型的表。)

See: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Especially:

In some cases, MySQL cannot use indexes to resolve the ORDER BY (..). These cases include the following:
(..)
You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

甜嗑 2024-10-04 19:16:03

您有一个 id、record_type、date_action 索引。但如果您想按 date_action 排序,则确实需要一个将 date_action 作为索引中第一个字段的索引,最好与 order by 中的确切字段匹配。否则,查询将会很慢。

You have an index for id, record_type, date_action. But if you want to order by date_action, you really need an index that has date_action as the first field in the index, preferably matching the exact fields in the order by. Otherwise yes, it will be a slow query.

林空鹿饮溪 2024-10-04 19:16:03

如果没有看到所有的表和索引,就很难判断。当询问有关加速查询的问题时,查询只是等式的一部分。

  • clientsid 上有索引吗?
  • clients 是否在 record_type 上有索引
  • clients_details 是否在 client_id 上有索引?
  • clients_industry_sectors 是否有 id 索引?

这些是此查询快速运行所需的最低限度。

Without seeing all your tables and indexes, it's hard to tell. When asking a question about speeding up a query, the query is just part of the equation.

  • Does clients have an index on id?
  • Does clients have an index on record_type
  • Does clients_details have an index on client_id?
  • Does clients_industry_sectors have an index on id?

These are the minimum you need for this query to have any chance of working quickly.

吲‖鸣 2024-10-04 19:16:03

非常感谢您的意见和建议。最后,我决定创建一个新的数据库表,其存在的唯一目的是返回为此目的的结果,因此不需要联接,我只需在主客户端表中添加或删除记录时更新表。从数据存储的角度来看并不理想,但它解决了问题并且意味着我可以非常快地获得结果。 :)

thanks so much for the input and suggestions. In the end I've decided to create a new DB table which has the sole purpose of existing to return results for this purpose so no joins are required, I just update the table when records are added or deleted to/from the master clients table. Not ideal from a data storage point of view but it solves the problem and means I'm getting results fantastically fast. :)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文