如何提高 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_actionas 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.
clientshave an index onid?clientshave an index onrecord_typeclients_detailshave an index onclient_id?clients_industry_sectorshave 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. :)