数据库排序的时间复杂度 [英] Time Complexity of Sorting a database

问题描述

我目前正在开发移动应用程序，并使用Codeigniter MySQL.我现在面临的情况是我有一本书的桌子(此桌子将是100k +记录).在此表中，我有一个称为NotSelling的列. db的示例:

I'm currently developing a mobile app and using Codeigniter MySQL. I'm now faced with a situation where I have a table of books (this table will be 100k+ with records). With in this table I have a column called NotSelling. Example of db:

Book A 45
Book B 0
Book C 159
Book D 78
.
.
.
Book Z 450

上面的数字是在数据库的不销售"列中显示的数字.我需要从这张大桌子上提取前20本书籍.现在，我的解决方案是对表进行排序，然后使用TOP提取前20条记录.

Where above the numbers are what appears in the NotSelling column in the db. I need to extract the top 20 books from this large table. Now my solution to doing this is to sort the table and then just use TOP to extract the top 20 records.

我想知道的是关于表排序的性能.因为我敢肯定，不断地对表格进行排序以仅获得前20个结果将花费很长的时间.已经为我提供了解决问题的方法:

What I would like to know is about the performance of sorting of the table. As I'm sure constantly sorting the table to simply get the top 20 results would take a hideously long time. I have been given solutions to the problem:

• 索引NotSelling问题.
• 缓存查询(但是我已经读过有关无效的粗略知识，因为我的案例中无效频率很高，因此可能会引起问题)
• 对表进行排序以获取前20条记录，将它们放置在另一个表中，然后定期更新表，例如每小时进行一次更新.

但是，所有这些都没有人知道有一个更好的解决方案，或者有一种方法/方法可以优化我要执行的功能的性能吗?请注意，我是一个新手，因此任何人都应该能够向我指出正确的方向，使我可以阅读有关数据库性能的信息.

But all this being said does anyone know of a better solution to this problem or have a way/method of optimizing the performance of the functionality I'm looking to do? Note I am a newbie so should anyone be able to point me in the right direction where I can read up about database performance I would really appreciate it.

推荐答案

我认为您在这里想的太多了.肯定是过早优化的情况.虽然上述所有解决方案都是完全有效的.您应该知道，超过100K条记录对Mysql来说是杂烩.我们过去经常在具有3000万行以上记录的表上order，具有出色的性能.

I think you are thinking too much here. Definitely a case of premature optimization. While all the above mentioned solutions are perfectly valid. You should know that 100K+ records is chowder to Mysql. We used to routinely order on tables with 30 million+ rows, with excellent perf.

但是您必须在要排序的列上具有索引，并仔细检查您的表架构. Reg.缓存也不必担心，当表未更改时，mysql会为您执行重复查询.但是列索引是必须的，主要的也是最重要的要求.

But You MUST have index on the column being sorted on and double check your table schema. Reg. Caching too don't worry, mysql does that for you for repetitive queries when the table has not changed. But index on column is a must, primary and most important requirement.

这篇关于数据库排序的时间复杂度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！