使用Django從資料庫中隨機取N條記錄的不同方法及其效能實測

熊鐸發表於2013-12-06

【宣告】：本文中的實驗僅限於特定資料庫和特定框架。不同資料庫，資料庫伺服器的效能，甚至同一個資料庫的不同配置都會影響到同一段程式碼的效能。具體情況請在自己的生產環境進行測試。

這裡（stackoverflow）有一篇關於使用Django隨機獲取記錄的討論。主要意思是說

Record.objects.order_by('?')[:2]

1	Record.objects.order_by('?')[:2]

這樣獲取2個記錄會導致效能問題，原因如下：

“ 對於有著相當多數量記錄的表來說，這種方法異常糟糕。這會導致一個 ORDER BY RAND() 的SQL查詢。舉個栗子，這裡是MYSQL是如何處理這個查詢的（其他資料庫的情況也差不多），想象一下當一個表有十億行的時候會怎樣：

為了完成ORDER BY RAND() ，需要一個RAND()列來排序
為了有RAND()列，需要一個新表，因為現有的表沒有這個列。
為了這個新表，mysql建立了一個帶有新列的，新的臨時表，並且將已有的一百萬行資料複製進去。
當其新建完了，他如你所要求的，為每一行執行RAND()函式來填上這個值。是的，你派mysql建立一百萬個隨機數，這要點時間：）
幾個小時或幾天後，當他幹完這活，他要排序。是的，你排mysql去排序一個一百萬行的，最糟糕的表（說他最糟糕是因為排序的鍵是隨機的）。
幾天或者幾星期後，當排序完了，他忠誠地將你實際需要的可憐的兩行抓出來返回給你。做的好。；）

注意：只是稍微說一句，得注意到mysql一開始會試著在記憶體中建立臨時表。當記憶體不夠了，他將會把所有東西放在硬碟上，所以你會因為近乎於整個過程中的I/O瓶頸而雪上加霜。

懷疑者可以去看看python程式碼引起的查詢語句，確認是ORDER BY RAND()，然後去Google下”order by rand()”（帶上引號）。

一個更好的方式是將這個耗費嚴重的查詢換成3個耗費更輕的：

last = MyModel.objects.count() - 1  
# 這是一個獲取兩個不重複隨機數的簡單方法  
index1 = randint(0, last)  
index2 = randint(0, last - 1)  
if index2 == index1:   
    index2 = last  
MyObj1 = MyModel.objects.all()[index1]  
MyObj2 = MyModel.objects.all()[index2]

last = MyModel.objects.count() - 1

# 這是一個獲取兩個不重複隨機數的簡單方法

index1 = randint(0, last)

index2 = randint(0, last - 1)

if index2 == index1:

index2 = last

MyObj1 = MyModel.objects.all()[index1]

MyObj2 = MyModel.objects.all()[index2]

”

如上Manganeez所說的方法，相應的獲取n條記錄的程式碼應該如下：

sample = random.sample(xrange(Record.objects.count()),n)  
result = [Record.objects.all()[i] for i in sample]

1 2	sample = random.sample(xrange(Record.objects.count()),n) result = [Record.objects.all()[i] for i in sample]

基於Python程式碼應該簡潔優雅的想法，如上的程式碼似乎可以寫成：

result = random.sample(Record.objects.all(),n)

1	result = random.sample(Record.objects.all(),n)

就效能問題，請教了stackoverflow上的大神（雖然被踩和被教育了=。=）

“Record.objects.count() 將被轉換成一個相當輕量級的SQL請求：

SELECT COUNT(*) FROM TABLE

1	SELECT COUNT(*) FROM TABLE

Record.objects.all()[0]也會被轉換成一個十分輕量級的SQL請求

SELECT * FROM TABLE LIMIT 1

1	SELECT * FROM TABLE LIMIT 1

Querying all 是一個耗費十分嚴重的請求

SELECT * FROM TABLE

1	SELECT * FROM TABLE

通常情況下Django會不顯示其他的結果，這樣你不會真正的獲取到所有的記錄。

SELECT * FROM table LIMIT 20;  // or something similar

1	SELECT * FROM table LIMIT 20; // or something similar

任何時候你將一個Queryset轉換成list的時候，將是資源消耗嚴重的時候。

如果我沒錯的話，在這個例子裡，sample方法將把Queryset轉換成list。

這樣如果你result = random.sample(Record.objects.all(),n) 這樣做的話，全部的Queryset將會轉換成list，然後從中隨機選擇。

想象一下如果你有十億行的資料。你是打算把它儲存在一個有百萬元素的list中，還是願意一個一個的query？

”

在上邊Yeo的回答中，freakish回覆道：“.count的效能是基於資料庫的。而Postgres的.count為人所熟知的相當之慢。”

某人說過，要知道梨子的滋味，就得變革梨子，親口嘗一嘗。

測試環境：

Win8.1 pro x64
Wampserver2.4-x64 (apache2.4.4 mysql5.6.12 php5.4.12)
Python2.7.5
Django1.4.6

在一個已有的測試project中新建一個app，資料庫是MYSQL：

D:\PyWorkspace\DjangoTest>python manage.py startapp randomrecords

1	D:\PyWorkspace\DjangoTest>python manage.py startapp randomrecords

在models.py中新增模型：

class Record(models.Model):  
    """docstring for Record"""  

    id = models.AutoField(primary_key = True)  
    content = models.CharField(max_length = 16)  

    def __str__(self):  
        return "id:%s content:%s" % (self.id, self.content)  
    def __unicode__(self):  
        return u"id:%s content:%s" % (self.id, self.content)

class Record(models.Model):

"""docstring for Record"""

id = models.AutoField(primary_key = True)

content = models.CharField(max_length = 16)

def __str__(self):

return "id:%s content:%s" % (self.id, self.content)

def __unicode__(self):

return u"id:%s content:%s" % (self.id, self.content)

新增一萬行資料：

D:\PyWorkspace\DjangoTest>python manage.py syncdb  
Creating tables ...  
Creating table randomrecords_record  
Installing custom SQL ...  
Installing indexes ...  
Installed 0 object(s) from 0 fixture(s)  

D:\PyWorkspace\DjangoTest>python manage.py shell  
Python 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)] on win  
32  
Type "help", "copyright", "credits" or "license" for more information.  
(InteractiveConsole)  
>>> from randomrecords.models import Record  
>>> for i in xrange(10000):  
...   Record.objects.create(content = 'c of %s' % i).save()

D:\PyWorkspace\DjangoTest>python manage.py syncdb

Creating tables ...

Creating table randomrecords_record

Installing custom SQL ...

Installing indexes ...

Installed 0 object(s) from 0 fixture(s)

D:\PyWorkspace\DjangoTest>python manage.py shell

Python 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)] on win

Type "help", "copyright", "credits" or "license" for more information.

(InteractiveConsole)

>>> from randomrecords.models import Record

>>> for i in xrange(10000):

... Record.objects.create(content = 'c of %s' % i).save()

15分鐘以後我得到了這個MYSQL表。真的，不騙你，真的是15分鐘。看了記錄才知道每次save都要呼叫一次insert和一次update。。。。下次一定用SQL語句初始化。。。。

先寫了個指令碼在manage.py shell中呼叫了下結果讓我震驚了。我表示不敢相信又寫了view 並在settings.py中新增了顯示SQL Query語句的log

這裡是寫的view：

def test1(request):  
    start = datetime.datetime.now()  
    result = Record.objects.order_by('?')[:20]  
    l = list(result) # Queryset是惰性的，強制將Queryset轉為list  
    end = datetime.datetime.now()  
    return HttpResponse("time: <br/> %s" % (end-start).microseconds/1000))  

def test2(request):  
    start = datetime.datetime.now()  
    sample = random.sample(xrange(Record.objects.count()),20)  
    result = [Record.objects.all()[i] for i in sample]  
    l = list(result)  
    end = datetime.datetime.now()  
    return HttpResponse("time: <br/> %s" % (end-start)  

def test3(request):  
    start = datetime.datetime.now()  
    result = random.sample(Record.objects.all(),20)  
    l = list(result)  
    end = datetime.datetime.now()  
    return HttpResponse("time: <br/> %s" % (end-start)

def test1(request):

start = datetime.datetime.now()

result = Record.objects.order_by('?')[:20]

l = list(result) # Queryset是惰性的，強制將Queryset轉為list

end = datetime.datetime.now()

return HttpResponse("time: <br/> %s" % (end-start).microseconds/1000))

def test2(request):

start = datetime.datetime.now()

sample = random.sample(xrange(Record.objects.count()),20)

result = [Record.objects.all()[i] for i in sample]

l = list(result)

end = datetime.datetime.now()

return HttpResponse("time: <br/> %s" % (end-start)

def test3(request):

start = datetime.datetime.now()

result = random.sample(Record.objects.all(),20)

l = list(result)

end = datetime.datetime.now()

return HttpResponse("time: <br/> %s" % (end-start)

執行結果如下，第一行是頁面顯示的時間，後邊是Queryset實際呼叫的SQL語句

test1：  

time: 0:00:00.012000  

(0.009) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()  
[05/Dec/2013 17:48:19] "GET /dbtest/test1 HTTP/1.1" 200 775  

test2:  

time: 0:00:00.055000  

(0.002) SELECT COUNT(*) FROM `randomrecords_record`; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 6593; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 2570; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 620; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 5814; args=()  
(0.003) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 6510; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3536; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3362; args=()  
(0.003) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 8948; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 7723; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 2374; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 8269; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 4370; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 6953; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1441; args=()  
(0.000) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 772; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 4323; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 8139; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 7441; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1306; args=()  
(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 5462; args=()  
[05/Dec/2013 17:50:34] "GET /dbtest/test2 HTTP/1.1" 200 777  

test3:  

time: 0:00:00.156000  

(0.032) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record`; args=()  
[05/Dec/2013 17:51:29] "GET /dbtest/test3 HTTP/1.1" 200 774

test1：

time: 0:00:00.012000

(0.009) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()

[05/Dec/2013 17:48:19] "GET /dbtest/test1 HTTP/1.1" 200 775

test2:

time: 0:00:00.055000

(0.002) SELECT COUNT(*) FROM `randomrecords_record`; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 6593; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 2570; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 620; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 5814; args=()

(0.003) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 6510; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3536; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3362; args=()

(0.003) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 8948; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 7723; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 2374; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 8269; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 4370; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 6953; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1441; args=()

(0.000) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 772; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 4323; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 8139; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 7441; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1306; args=()

(0.001) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 5462; args=()

[05/Dec/2013 17:50:34] "GET /dbtest/test2 HTTP/1.1" 200 777

test3:

time: 0:00:00.156000

(0.032) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record`; args=()

[05/Dec/2013 17:51:29] "GET /dbtest/test3 HTTP/1.1" 200 774

令人難以置信的，在10000行的MYSQL表中方法1的效率是最高的。無論是結果上看（12ms）還是SQL語句的執行時間上看（9ms）方法1甩了其他方法一大截

即便資料量增加到21萬，方法1也會比其他兩種方法快：

time: 98  

(0.094) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()  
[05/Dec/2013 19:18:59] "GET /dbtest/test1 HTTP/1.1" 200 14  

time: 0:00:00.668000  
//這裡沒有注意到 掉了一行count語句  
(0.045) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 176449; args=()  
(0.016) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 68082; args=()  
(0.036) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 145571; args=()  
(0.033) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 111029; args=()  
(0.043) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 169675; args=()  
(0.046) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 186234; args=()  
(0.043) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 167233; args=()  
(0.015) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 54404; args=()  
(0.036) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 140395; args=()  
(0.004) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 13128; args=()  
(0.039) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 153695; args=()  
(0.034) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 131863; args=()  
(0.021) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 82785; args=()  
(0.015) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 57253; args=()  
(0.021) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 77836; args=()  
(0.049) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 199567; args=()  
(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3867; args=()  
(0.027) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 104470; args=()  
(0.026) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 107058; args=()  
(0.043) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 150979; args=()  
[05/Dec/2013 19:21:33] "GET /dbtest/test2 HTTP/1.1" 200 15  

time 0:00:00.781000  

[05/Dec/2013 19:23:01] "GET /dbtest/test3 HTTP/1.1" 200 15  
(0.703) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record`; args=()  
[05/Dec/2013 19:23:06] "GET /dbtest/test3 HTTP/1.1" 200 15

time: 98

(0.094) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()

[05/Dec/2013 19:18:59] "GET /dbtest/test1 HTTP/1.1" 200 14

time: 0:00:00.668000

//這裡沒有注意到掉了一行count語句

(0.045) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 176449; args=()

(0.016) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 68082; args=()

(0.036) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 145571; args=()

(0.033) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 111029; args=()

(0.043) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 169675; args=()

(0.046) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 186234; args=()

(0.043) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 167233; args=()

(0.015) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 54404; args=()

(0.036) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 140395; args=()

(0.004) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 13128; args=()

(0.039) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 153695; args=()

(0.034) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 131863; args=()

(0.021) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 82785; args=()

(0.015) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 57253; args=()

(0.021) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 77836; args=()

(0.049) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 199567; args=()

(0.002) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3867; args=()

(0.027) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 104470; args=()

(0.026) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 107058; args=()

(0.043) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 150979; args=()

[05/Dec/2013 19:21:33] "GET /dbtest/test2 HTTP/1.1" 200 15

time 0:00:00.781000

[05/Dec/2013 19:23:01] "GET /dbtest/test3 HTTP/1.1" 200 15

(0.703) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record`; args=()

[05/Dec/2013 19:23:06] "GET /dbtest/test3 HTTP/1.1" 200 15

資料量再次提升至百萬級別 1066768條資料

time:   
0:00:02.197000  

(2.193) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()  
[05/Dec/2013 20:00:55] "GET /dbtest/test1 HTTP/1.1" 200 26  

time:   
0:00:02.659000  

(0.204) SELECT COUNT(*) FROM `randomrecords_record`; args=()  
(0.180) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 703891; args=()  
(0.038) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 156668; args=()  
(0.013) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 50742; args=()  
(0.031) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 121107; args=()  
(0.033) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 130565; args=()  
(0.017) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 66225; args=()  
(0.234) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 922479; args=()  
(0.267) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1027166; args=()  
(0.189) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 765499; args=()  
(0.009) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 31569; args=()  
(0.233) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 934055; args=()  
(0.264) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1052741; args=()  
(0.155) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 621692; args=()  
(0.014) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 52388; args=()  
(0.199) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 759669; args=()  
(0.170) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 655598; args=()  
(0.035) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 139709; args=()  
(0.228) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 919480; args=()  
(0.104) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 422051; args=()  
(0.017) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 67549; args=()  
[05/Dec/2013 20:00:45] "GET /dbtest/test2 HTTP/1.1" 200 26  

time:   
0:00:19.651000  

(3.645) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record`; args=()  
[05/Dec/2013 20:02:50] "GET /dbtest/test3 HTTP/1.1" 200 26

time:

0:00:02.197000

(2.193) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()

[05/Dec/2013 20:00:55] "GET /dbtest/test1 HTTP/1.1" 200 26

time:

0:00:02.659000

(0.204) SELECT COUNT(*) FROM `randomrecords_record`; args=()

(0.180) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 703891; args=()

(0.038) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 156668; args=()

(0.013) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 50742; args=()

(0.031) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 121107; args=()

(0.033) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 130565; args=()

(0.017) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 66225; args=()

(0.234) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 922479; args=()

(0.267) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1027166; args=()

(0.189) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 765499; args=()

(0.009) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 31569; args=()

(0.233) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 934055; args=()

(0.264) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1052741; args=()

(0.155) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 621692; args=()

(0.014) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 52388; args=()

(0.199) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 759669; args=()

(0.170) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 655598; args=()

(0.035) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 139709; args=()

(0.228) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 919480; args=()

(0.104) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 422051; args=()

(0.017) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 67549; args=()

[05/Dec/2013 20:00:45] "GET /dbtest/test2 HTTP/1.1" 200 26

time:

0:00:19.651000

(3.645) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record`; args=()

[05/Dec/2013 20:02:50] "GET /dbtest/test3 HTTP/1.1" 200 26

第三種方法所用時間長到令人無法接受（19.65秒）。但有意思的是 SQL語句所花費的時間“只有”3.6秒。而大部分的時間都用在python上了。

既然第二種方法和第三種方法都需要random.sample 一個百萬個資料的list，那就是說，有大量的時間花費在將SELECT到的結果轉化為django物件的過程中了。

此後將不再測試第三種方法

最後，資料量增加到5,195,536個

隨著表中資料行數的增加，兩個方法的所用的時間都到了一個完全不能接受的程度。兩種方法所用的時間也幾乎相同。

time:   
0:00:22.278000  

(22.275) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FR  
OM `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()  
[05/Dec/2013 21:46:33] "GET /dbtest/test1 HTTP/1.1" 200 26  

time:   
0:00:33.319000  

(1.393) SELECT COUNT(*) FROM `randomrecords_record`; args=()  
(3.201) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 4997880; args=()  
(1.229) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 2169311; args=()  
(0.445) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1745307; args=()  
(1.306) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3233861; args=()  
(1.881) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3946647; args=()  
(1.624) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3534377; args=()  
(1.068) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1684337; args=()  
(0.902) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 2607361; args=()  
(2.938) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 4872494; args=()  
(0.493) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 851494; args=()  
(3.275) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 5182414; args=()  
(0.946) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1684670; args=()  
(0.701) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1819730; args=()  
(0.915) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1626221; args=()  
(1.809) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 3638682; args=()  
(3.237) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 4801027; args=()  
(1.187) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1955843; args=()  
(2.736) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 4835733; args=()  
(1.705) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 2756641; args=()  
(0.286) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO  
M `randomrecords_record` LIMIT 1 OFFSET 1117426; args=()

time:

0:00:22.278000

(22.275) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FR

OM `randomrecords_record` ORDER BY RAND() LIMIT 20; args=()

[05/Dec/2013 21:46:33] "GET /dbtest/test1 HTTP/1.1" 200 26

time:

0:00:33.319000

(1.393) SELECT COUNT(*) FROM `randomrecords_record`; args=()

(3.201) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 4997880; args=()

(1.229) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 2169311; args=()

(0.445) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1745307; args=()

(1.306) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3233861; args=()

(1.881) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3946647; args=()

(1.624) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3534377; args=()

(1.068) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1684337; args=()

(0.902) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 2607361; args=()

(2.938) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 4872494; args=()

(0.493) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 851494; args=()

(3.275) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 5182414; args=()

(0.946) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1684670; args=()

(0.701) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1819730; args=()

(0.915) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1626221; args=()

(1.809) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 3638682; args=()

(3.237) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 4801027; args=()

(1.187) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1955843; args=()

(2.736) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 4835733; args=()

(1.705) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 2756641; args=()

(0.286) SELECT `randomrecords_record`.`id`, `randomrecords_record`.`content` FRO

M `randomrecords_record` LIMIT 1 OFFSET 1117426; args=()

值得注意的是，Mysql資料庫有一個特點是，對於一個大表，OFFSET越大，查詢時間越長。或許有其他方法可以在offset較大的時候加快select的速度，然而django明顯沒有做到。如果能夠減少這種消耗，方法2明顯會優於方法1。

附上三種方法資料量和SQL時間/總時間的資料圖表：

table

20131205220333

總時間

最後總結，Django下，使用mysql資料庫，資料量在百萬級以下時，使用

Record.objects.order_by('?')[:2]

1	Record.objects.order_by('?')[:2]

來獲取隨機記錄序列，效能不會比

sample = random.sample(xrange(Record.objects.count()),n)  
result = [Record.objects.all()[i]) for i in sample]

1 2	sample = random.sample(xrange(Record.objects.count()),n) result = [Record.objects.all()[i]) for i in sample]

差。

不同資料庫限制取前N條記錄
2018-07-26
資料庫
各個資料庫的取一表前N條記錄不同SQL寫法
2013-11-06
資料庫SQL
MongoDB隨機查詢返回一條或N條記錄的方法
2019-07-03
MongoDB隨機
關於資料庫取表的前N條記錄 (轉)
2007-08-15
資料庫
使用Oracle分析函式隨機抽取N條記錄
2014-08-26
Oracle函式隨機
演算法，PHP取資料庫中百萬條資料中隨機20條記錄
2013-09-16
演算法PHP資料庫隨機
[Q]隨機抽取前N條記錄的問題zt
2005-03-15
隨機
從oracle表中隨機取記錄，產生隨機數和隨機字串
2009-03-26
Oracle隨機字串
對查詢資料庫中第M到N條記錄的思考
2008-09-25
資料庫
使用SQL語句從資料庫一個表中隨機獲取資料
2012-02-12
SQL資料庫隨機
Mysql取隨機資料效率測試（400W條中讀取100條）
2017-05-17
MySql隨機
Django Models隨機獲取指定數量資料方法
2021-09-09
Django隨機
js隨機從陣列中取資料
2017-04-12
JS隨機陣列
【新炬網路名師大講堂】不同資料庫取前幾條記錄
2014-12-15
資料庫
PHP查詢資料庫中滿足條件的記錄條數(二種實現方法)
2015-08-26
PHP資料庫
獲取不同資料庫新增記錄主鍵值
2017-11-13
資料庫
隨機取表記錄
2008-01-18
隨機
表中隨機取資料
2007-04-17
隨機
Django ORM 引發的資料庫 N+1 效能問題
2020-10-11
DjangoORM資料庫
如何在不同的資料平臺取前百分之N的記錄？
2022-06-27
Oracle隨機抽取記錄的方法
2010-09-07
Oracle隨機
【面經】面試官：如何以最高的效率從MySQL中隨機查詢一條記錄？
2020-11-04
面試MySql隨機
asp中利用陣列實現資料庫記錄的批次錄入方法 (轉)
2007-12-12
陣列資料庫
mysql 批次更新與批次更新多條記錄的不同值實現方法
2021-09-09
MySql
postgresql 多條記錄合併一條，或取最新的一條資料
2024-04-26
SQL
資料庫-隨記
2024-04-03
資料庫
隨機獲取oracle資料庫中的任意一行資料(rownum)示例介紹
2017-12-12
隨機Oracle資料庫
Java從List中獲取隨機元素
2018-09-17
Java隨機
django使用多個資料庫實現
2023-04-21
Django資料庫
使用RMAN恢復一個資料庫到另一個目錄結構不同的資料庫中
2012-07-02
資料庫
Spring Boot應用中如何動態指定資料庫，實現不同使用者不同資料庫的場景
2024-04-26
Spring Boot資料庫
條件隨機場CRF(一)從隨機場到線性鏈條件隨機場
2017-06-19
條件隨機場CRF
NHibernate使用ICriteria分頁並返回資料庫記錄總條數
2014-01-18
資料庫
Django資料庫效能優化之 - 使用Python集合操作
2022-07-05
Django資料庫優化Python
記錄DEV gridview獲取行列資料方法
2017-08-09
devView
介面測試-使用mock生產隨機資料
2020-12-30
Mock隨機
介面測試-使用 mock 生產隨機資料
2020-12-30
Mock隨機
Oracle中取固定記錄數的方法薦
2007-06-06
Oracle

使用Django從資料庫中隨機取N條記錄的不同方法及其效能實測

相關文章