当限制到达最后一条记录时，mysql查询变慢
我有一个java应用程序，我想从表中获取一些数据并显示在应用程序中。
我有数百万条记录，当我查询最后一条记录时，查询变得非常慢。需要几分钟才能得到结果。
select Id from Table1x where description like '%error%' and Id between 0 and 1329999 limit 0, 1000
上面的查询会快速返回结果。那就是首页返回速度很快。但是当我移动最后几页时，它变得很慢。
select Id from Table1x where description like '%error%' and Id between 0 and 1329999 limit 644000, 1000.
此查询速度很慢，需要 17 秒。
关于如何使其更快的任何想法？ Id是table1x的主键。
I have a java application and I would like to get some data from a table and display in the application.
I have millions of records, and the query gets really slow when I am going to the last records. it takes few good minutes to get the results.
select Id from Table1x where description like '%error%' and Id between 0 and 1329999 limit 0, 1000
The above query returns a fast result. That is first pages returns fast. But when I am moving the last pages, it becomes slow.
select Id from Table1x where description like '%error%' and Id between 0 and 1329999 limit 644000, 1000.
This query is slow and taking 17 secs.
Any ideas on how to make this faster? Id is the primary key of table1x.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题出在类似的地方。要获取前 1000 条记录，数据库只需过滤数据库，直到找到与搜索匹配的 1000 条记录。对于另一个查询，数据库需要匹配记录，直到有 645000 条记录，这使得速度慢得多。没有排序或其他过滤，因此 ID 上的索引根本没有帮助。
description上的索引会有所帮助，但如果您像现在一样使用通配符开始搜索，则不会有帮助。我看到两个解决方案。
第一个选项是在描述字段上添加全文索引。它允许使用 MATCH 而不是 LIKE 来查找单词
error。我认为它会快很多，但索引也会变得更大，而且我不确定从长远来看是否会优化。第二种解决方案：由于您显然正在寻找错误（我认为您正在日志表上构建报告？），因此您可以添加具有记录类型的列。您可以为每个记录指定一个类型（只是一个整数），该类型指示该记录是否包含错误。您将需要更新一次表，并将类型与新记录一起插入，但这将使您的查询更快。
我必须承认，第二个解决方案是基于对数据和您的目标的假设。如果我的说法有误，请提供更多信息，我可能会找到更适合您的解决方案。
The problem is in the like. To get the first 1000 records, the database only needs to filter the database until it finds 1000 records that match the search. For the other query, the database needs to match records until it has 645000 records, which makes it much slower. There is no sorting or other filtering, so the index on ID doesn't help at all.
An index on
descriptionwould help, but not if you start the search with a wildcard, like you do now.I see two solutions.
First option is to add a FULLTEXT index on the description field. It allows to to look for the word
errorusing MATCH rather than LIKE. I think it will be a lot faster, but the index will become larger too, and I'm not sure about the optimizations on the long run.Second solution: Since you're obviously looking for errors (I think you're building a report on a log table?), you may add a column with a record type. You can give each record a type (just an integer) which indicates where that record holds an error or not. You will need to update your table once, and insert the type along with new records, but it will make your query faster.
I must admit that this second solution is based on assumptions about the data and your goal. If I'm wrong about that, please provide additional information and I may find a solution that suits you better.