如何提高 MySQL 查询的性能?
我有一个 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
更多
发布评论
评论(4)
请参阅: http://dev.mysql.com/doc /refman/5.0/en/order-by-optimization.html
特别是:
See: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
Especially:
您有一个
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 bydate_action
, you really need an index that hasdate_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.如果没有看到所有的表和索引,就很难判断。当询问有关加速查询的问题时,查询只是等式的一部分。
clients
在id
上有索引吗?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.
clients
have an index onid
?clients
have an index onrecord_type
clients_details
have an index onclient_id
?clients_industry_sectors
have an index onid
?These are the minimum you need for this query to have any chance of working quickly.
非常感谢您的意见和建议。最后,我决定创建一个新的数据库表,其存在的唯一目的是返回为此目的的结果,因此不需要联接,我只需在主客户端表中添加或删除记录时更新表。从数据存储的角度来看并不理想,但它解决了问题并且意味着我可以非常快地获得结果。 :)
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. :)