mysql 按非常大的数字对行进行排序。加载时间
我面临着两难的境地。我通过两个字段从表中提取数据：
**field 1**
1979385197241
1235467
95823352353
9089892189735
versus
**field 2**
6
2
7
2
1
假设该表有大约 2000 行和 40 个字段。如果我按一个或另一个字段排序，加载时间是否会有很大差异？
我这么问是因为较大的数字是实时的，而较小的数字是由较大的数字通过 cronjob 更新的。如果我按字段 2 对行进行排序，我就会得到实时结果。不需要 cronjob。
I'm facing a dilemma. I pull data from my table by two fields:
**field 1**
1979385197241
1235467
95823352353
9089892189735
versus
**field 2**
6
2
7
2
1
Lets say the table has about 2000 rows and 40 fields. Would the loading time vary significantly should I order it by one or the other field?
I'm asking because the bigger numbers are live, and the smaller ones are updated by a cronjob by the bigger numbers. If I were to order the rows by field 2, I'd have live results. No need for a cronjob.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您关心排序的性能，那么最重要的是正确使用索引。您通常应该在您想要的列上创建索引在
ORDER BY子句中使用。然而，由于表中只有 2000 行，我想即使没有索引，速度也会相当快。
If you are concerned about the performance of the sorting then the most important thing is to use indexes correctly. You should usually create an index on the column you wish to use in an
ORDER BYclause.However with just 2000 rows in your table I guess it will be quite fast even without the index.
按数字排序是一种二进制比较操作，对于处理器整数大小（通常为 32 位）以内的任何内容都会在相同的时间内发生。在任何一种情况下，两个数字都可能在单个处理器指令中进行比较（英特尔汇编：cmp。加载寄存器和检查值的时间尽管如此......）。仅在具有 64 位数字且两个数字的前 32 位相同的 32 位系统上，比较较大数字和较小数字会花费更多时间。所有排序操作都是通过一次比较两个数字来完成的。
实际上，不必担心要排序的数字的大小。 9999999999999999999999999999999999999999999999999999999999999999999999 cmp 1 -- 与 2 cmp 1 相同的时间 -- 我们立即知道我们不必检查第一个 9 串的完整长度。
Ordering by numbers is a binary comparison operation and will happen in equal time for anything up to the processor's integer size (usually 32 bit). In either case, both numbers are probably compared in a single processor instruction (Intel assembly: cmp. Time to load register and check value not withstanding...). Only on a 32 bit system with a 64 bit number AND with the first 32 bits of both numbers being the same would it take more time to compare the larger number to the smaller one. All sorting operations are done by comparing two numbers at a time.
In practical terms, never worry about the size of a number that you're sorting. 9999999999999999999999999999999999999999999999999999999999999999999999 cmp 1 -- same amount of time as 2 cmp 1 -- we know right away that we don't have to check the full length of the first string of 9s.
最好对这两种方式都进行测试，但我认为按字段 2 排序不会造成任何性能损失。
It's best to test both ways, but I don't think that you'll suffer any performance loss at all ordering by field 2.