[Hỏi] Truy vấn SQL nhanh với random records

Chào ae,

Các bác ở đây có thể nhiều lần lấy ngẫu nhiên records từ DB. Ae đang dùng cách nào để truy vấn dữ liệu hơn 300k records mà nhanh vậy?

Mình thấy trên mạng có mẫu thế này:

Code:

SELECT r1.name FROM random AS r1
   JOIN (SELECT (RAND() *
                (SELECT MAX(id) FROM random)) AS id
        ) AS r2
  WHERE r1.id >= r2.id
  ORDER BY r1.id ASC
  LIMIT 10

Source: http://jan.kneschke.de/projects/mysql/order-by-rand/

Mẫu này truy vấn chắc chắn nhanh hơn SELECT r1.name FROM random ORDER BY RAND() rồi. Tuy nhiên, kết quả truy vấn nó gần nhau quá, và nó ko dùng đối số LIMIT được.

Xin cám ơn ae,

 

http://cafemmo.club/threads/lay-nhanh-random-rows-trong-mysql-table.415/

 

Code:

SELECT * FROM
(SELECT ID FROM PRODUCTS ORDER BY RAND() LIMIT 10) A
INNER JOIN PRODUCTS B on B.ID = A.ID;

Thanks anh Andy, cách này nhanh hơn trung bình 7 lần so với cách dùng thuần túy, tuy nhiên hiệu năng so với mẫu trong bài viết trên thì còn thấp hơn. Không biết còn phương pháp nào hiệu quả hơn chăng?

 

Cách trên dùng ok với db nhỏ dưới 2M rows.

Còn nếu muốn nhanh nhất và cho mọi kích cỡ DB thì dùng cách của anh có nói trong bài đó.

 

Mình cũng vưừa research cái giống bác command đó
Tuy nhiên mình k dùng query rand trong mysql nữa, vì kiểu gì nó cũng chậm vãi r a
nên mình quyết định rand = php rồi insert vào db, rand theo điều kiện thì mình set rand(start,end)
start = xxx
end = yyy
x và y thì mình get min, max theo điều kiện cần rand từ database ra, làm vậy vừa nhanh vừa nhẹ =.="
lâu lâu vào get min max lại để đổi
cách cùi nhưng hiệu quả
hehe

 

Có phải anh Andy đề cập là dùng cách sau?

 

@command làm vậy chính xác anh. Between id rồi limit lại nhanh hơn

 

@command uh, cách đó em. Trí Mén có confirm là nhanh đó.

 

Hỏi thêm ae nào dùng cú pháp RANDOM dạng này mà có nhiều trường WHERE và thêm ORDER BY để nhanh nhất vậy với tầm 50 M rows?

Mình dùng cú pháp:

Thì kết quả trả về random với mỗi lần chạy SQL này, với tốc độ khoảng 0.00xx seconds

Tuy nhiên, khi mình thêm ORDER BY như:

Thì lúc này kết quả giống nhau giữa các lần chạy SQL 2 này, với tốc độ khoảng 0.2xx seconds. Mặc dù priority có tầm M rows có cùng priority cao nhất và status=1. Dĩ nhiên tất các các fields với WHERE và ORDER BY thì mình đều đánh index.

Nếu tách SQL 2 ra để thử cú pháp SQL:

Thì kết quả random được ID, với tốc độ khoảng 0.000xx seconds. Tức là lúc này vẫn cho ra ID ngẫu nhiên, nhưng khi kết hợp trong tổng thể của SQL 2 thì kết quả giống nhau mỗi lần thực hiện SQL. Tại sao lại như thế tơ?

Ae có thử random rows với WHERE và ORDER BY tư vấn mình với!

 

Câu lệnh:

có Order By chẳng có ý nghĩa gì trong ngữ cảnh này vì em chỉ lấy max id tức là chỉ có 1 kết quả trả về.

Câu SQL 1 nhanh hơn vì nó không sắp xếp tập kết quả, chỉ lấy 10 cái đầu tiên. Nếu em thay bằng limit 100000, 10 chắc chắn chậm hơn do nó phải move đến kết quả thứ 100000 rồi lấy ra 10 cái (đương nhiên là tập kết quả của em phải lớn hơn 100000 rows mới thấy nó chậm chứ chỉ có vài K rows ko thấy đâu)

Câu SQL 2 chậm hơn SQL 1 do nó phải Order By tập kết quả rồi mới lấy 10 rows đầu tiên.

Và anh tin rằng tập kết quả của em là khá nhỏ, tầm vài chục K rows là cùng, chứ nếu tập kết quả mà cỡ mấy trăm K trở lên dùng order by chắc phải tính bằng giây.

 

Câu lệnh sau vẫn order by bình thường và hiệu lực anh Andy ơi, vì nhiều khi ID được thêm mới sau cùng có priority thấp hơn ID các records trước

Minh hoạ cụ thể qua các hình sau:



Tất cả các hình trên đều truy vấn cùng 1 table. Tuy nhiên câu lệnh SQL 2 như sau thì trả về tập kết quả như sau cho mỗi lần chạy (đã nêu nguyên nhân khó hiểu ở post trước).

 

1. Database này em đang insert vào lien tục hay sao? Vì anh thấy 2 câu lệnh sau chẳng có gì khác nhau, không hiểu sao lại cho kết quả khác nhau?

và

2. Câu lệnh cuối:

trả về hơn 50M kết quả (có paging) mà thời gian chỉ có như vậy là quá nhanh. Em đang dùng MySQL hay cái gì? Cấu hình server ra sao?

3.

Khó hiểu là chậm hơn hay sao? Nếu chậm hơn thì anh đã nói là do nó phài Order By còn SQL1 không Order By.

 

Đúng như anh nói về việc cập nhật dữ liệu mới liên tục từ spiders nên hình thứ 2 dữ liệu đã được tăng so với hình 1. Tuy nhiên, priority của record mới đa số thấp hơn records cũ (do spiders quyết định).

1. Tức câu lệnh SQL

và

thì số lượng records chắc sẽ thay đổi, tuy nhiên vì khi thêm mới 1 record thì priority có thể thấp hơn các records củ, nên MAX(id) của cú pháp 1 và 2 chắc chắn sẽ khác nhau vì ORDER BY priority DESC.

2. Em dùng MySQL và tăng cấu hình chạy MySQL so với mặc định (tăng cache/buffer cho InnoDB vì table em dùng InnoDB engine), CPUs 2 cores. Có vẽ nhanh vì VPS này chỉ có em truy cập (READ) và các spiders thêm mới dữ liệu (WRITE), nên số lượng online không phải quá nhiều.

3. Ý em nói câu lệnh SQL 2 là câu lệnh:

Khi em thử SQL

SQL 3 sẽ cho ra kết quả ngẫu nhiên (1 số ngẫu nhiên bé hơn MAX(id) có priority cao nhất) khi thực thi câu lệnh này. SQL 3 là 1 phần nhỏ trong SQL 2. SQL 3 đã ra số ngẫu nhiên do có hàm RAND(). Tuy thế, khi kết hợp trong tổng thể của SQL 2 (thực thi SQL 2 này) thì kết quả giống nhau mỗi lần thực hiện SQL, hay nói cách khác là không cho ra kết quả ngẫu nhiên mỗi lần thực thi câu lệnh. Tại sao lại như thế thì em khó hiểu? Em ko hiểu em sai chổ nào mà lại như thế?

 

@command
1. em test trên 1 db động thì bó tay rồi. Đúng ra 2 câu query đó chỉ ra 1 kết quả thôi, nên việc order by là vô nghĩa

3. Giống nhau là do em order by.
Em lấy ngẫu nhiên 1 số rồi lấy các rows có id >= số đó và status = 1 thì sẽ cho tập kết quả khác nhau.
Nhưng khi em order 2 tập đó và lấy 10 rows đầu thì sẽ giống nhau thôi. Vì 2 lí do:
- em chưa có priority nào lớn hơn max priority cũ
- mysql show các rows theo thứ tự insert vào db (nói chung là viết ra hơi khó hiểu, có máy làm demo thì nhanh hơn)
Anh nghĩ là em nên thêm ORDER BY priority DESC id DESC LIMIT 0, 10 để lấy ra các rows có id mới nhất sẽ thấy khác nhau giữa các lần query (nhưng chỉ với db em insert liên tục thôi chứ db ngừng insert thì khả năng các lần query sẽ cho kết quả như nhau).
Chốt lại là nếu em muốn query random rows theo câu lệnh em viết thì anh nghĩ là không đúng logic

Và nếu em đang test code thì nên dùng 1 db độc lập, tự insert hoặc update bằng tay vài rows để đảm bảo nó tạo ra trường hợp mới cho mỗi lần query. Chứ em đang insert thêm vào db bằng tool, em không chắc chắn data mới insert có làm khác tập kết quả hay không thì em chạy test làm sao chính xác được.

Ví dụ em order by priority mà max(priority) lần query đầu là 100 thì em phải insert row mới (hoặc update 1 row nào đó) có priority = 101 để xem cái 101 nó có show ra không, nếu có thì đúng còn không show là code sai.

Hỏi thêm: em cho anh chi tiết cấu hình server em đang xài được không? RAM? SSD hay HDD? CPU speed? Provider? Vì anh đang dùng Server Core i7 6700 Quad-Core Skylake, RAM 64Gb, 2 x 4TB HDD 7200rpm mà tốc độ query anh thấy khá chậm (như trong hình, anh query trên table news có hơn 13M rows, field haveimg có index)

 

Thanks anh, để khi rãnh ngâm lại vụ random vì lúc triển khai thực tế mới cần, bây giờ lo việc crawlers hơn ^_^

Em nghĩ tốc độ truy vấn MySQL của anh Andy chậm do dùng HDD 7200rpm vì RAM và CPUs mà anh đang dùng còn tốt chán, em dùng local SSD cho OS (gói VPS Cloud 3 https://www.ovh.com/asia/vps/vps-cloud.xml) và mua thêm disk (https[:]//www_ovh_com/asia/vps/storage.xml)

 

Bạn chuyển thử limit 0,10 vào trong SQL 3 khả năng là random được,
SQL 3: SELECT ROUND(RAND() * (SELECT MAX(id) FROM `abc` WHERE status = 1 ORDER BY priority DESC) LIMIT 0,10

 

Cao siêu quá, hẻm dám coi luôn. Thật hàn lâm.

 

Chưa thử cách bạn, nhưng LIMIT ko có tác dụng rồi vì chỉ có MAX(id), 1 kết quả trả về.

Mình lựa chọn cuối cùng SQL cho random với 50 M rows, mình thấy làm theo cách sau sẽ ổn, tốc độ trong khoảng 0,1s-0,25s:

Ghi chú:
- Thêm cột random cho table: chứa số random (mình dùng kiểu unsigned tinyint, như vậy random sẽ từ 0-255), khi đó trong truy vấn mình sẽ sinh ra số 25 và 50 (như minh hoạ ngẫu nhiên). Tuỳ tập rows mà lựa chọn between hợp lý ^_^
- limit 0, 25 và limit 0, 10: tuỳ bạn có áp dụng hay không, vì đôi khi sẽ trùng tập random ở trên, nên mình muốn thêm LIMIT này để cho tập ngẫu nhiên hơn. Tốc độ truy vấn khi có thêm LIMIT này ảnh hưởng ko đáng kể nên mình thêm vào.

 

Hôm nọ tranh luận vụ này và hôm nay gặp phải cái này thật nên tìm ra 1 giải pháp tương đối ổn.
Chạy câu lệnh nay nó sẽ ra 1 loạt thông tin và trong đó có column Rows

Code:

SHOW TABLE STATUS FROM `table_name` LIKE `column_name`

Câu lệnh trên mình thực hiện với 1 table 4.000.000 row thì ra kết quả [Empty set (0.09 sec)]
Khi có được cái Rows thì chỉ việc rand 1 số nào đó trong khoảng từ limit đến Rows - limit rồi lấy sao cho id > rand là được
Goodluck