हालत द्वारा बड़े MySQL तालिकाओं से यादृच्छिक मूल्यों का त्वरित चयन

डेवलपर्स के लिए अक्सर तालिका से यादृच्छिक पंक्तियों को चुनने का कार्य उत्पन्न होता है।
यदि MySQL का उपयोग किया जाता है, तो यह आमतौर पर लगभग निम्नलिखित तरीके से हल किया जाता है:

SELECT *
FROM users
WHERE role_id=5
ORDER BY rand()
LIMIT 10

बड़ी तालिकाओं के लिए ऐसा कोड बेहद धीमा है।
यदि क्वेरी को WHERE का उपयोग करने की आवश्यकता नहीं है या तालिका छोटी है, तो प्रभावी समाधान हैं, उदाहरण के लिए habrahabr.ru/post/54176 या habrahabr.ru/post/55864 ।
लेकिन मुझे एक बड़ी तालिका के लिए कोई तैयार समाधान नहीं मिला और प्रत्येक अनुरोध पर नए मूल्यों को प्राप्त करते हुए, शर्त द्वारा फ़िल्टर करने की आवश्यकता थी, इसलिए मेरी विधि का वर्णन एक बिल्ली के नीचे है।


जैसा कि यह निकला, MySQL कुशलतापूर्वक ORDER BY रैंड () लिमिट एन का उपयोग करके यादृच्छिक पंक्तियों का चयन नहीं कर सकता है, जहां पंक्तियों को शर्त के अनुसार फ़िल्टर करना आवश्यक है (हालांकि एक ही MSSQL बड़ी संख्या में रिकॉर्ड के साथ तालिका से यादृच्छिक पंक्तियों का चयन करने का एक उत्कृष्ट काम करता है)।

इसलिए, समस्या का समाधान "सिर पर", अनुरोध (तालिका में 5 मिलियन रिकॉर्ड):

SELECT *
FROM users
WHERE role_id=5
ORDER BY rand()
LIMIT 10


अनुरोध में 41.3544 सेकंड लगे, जो अस्वीकार्य रूप से लंबा है। अधिकतम और न्यूनतम आईडी ढूंढना असंभव है, और फिर इस मामले में अंतराल से यादृच्छिक आईडी का चयन करें: WHERE क्लॉज के कारण, आईडी अब क्रम में और छुट्टी नहीं दी जाती है।

मेरा समाधान निम्नलिखित है: एक random_seed तालिका को जोड़ा गया है, जिसमें id और random_seed फ़ील्ड हैं, यादृच्छिक संख्याओं से भरा हुआ है, इस कॉलम में एक इंडेक्स जोड़ा गया है, और इंडेक्स उस कॉलम में जोड़ा जाता है जिसके द्वारा चयन होगा।
अब, स्थिति द्वारा यादृच्छिक तारों का चयन करने के लिए, क्वेरी को निम्नानुसार बदलना होगा (तालिका में, 5 मिलियन रिकॉर्ड):

SELECT
u1.*
FROM
users u1,
random_seed rs
WHERE
u1.role_id=5 AND u1.id=(rs.id+random_from_php)
ORDER BY
rs.random_seed
LIMIT 10

अनुरोध में 0.0460 सेकंड लगे, जो स्वीकार्य परिणाम से अधिक है। चर random_from_php उस कोड से उत्पन्न होता है जो अनुरोध को कॉल करता है, जो प्रत्येक अनुरोध के लिए मूल्यों का एक यादृच्छिक सेट प्रदान करता है, यह संख्या नए यादृच्छिक संख्याओं का चयन प्रदान करेगी। रैंडम_सेड टेबल में उतने ही मान होने चाहिए जितने टेबल से आप रैंडम रो + एन रिकॉर्ड लेना चाहते हैं, जहां एन रैंडम_फ्रेम_फपी का अधिकतम संभव मान है।

मेरे काम का एक वास्तविक उदाहरण विभिन्न संयोजनों (कुल 4,000,000 रिकॉर्ड) से सामानों की यादृच्छिक श्रेणियों का चयन है:

सामान्य: अनुरोध	"त्वरित" अनुरोध:
का चयन करें oc1। * से object_category oc1 कहाँ oc1.region_id = 6 रैंड द्वारा आदेश () सीमा १०	का चयन करें oc1। * से object_category oc1, random_seed rs कहां oc1.id = (rs.id + 564756) और oc1.region_id = 6 आदेश द्वारा rs.random_seed सीमा १०
लीड समय:
1.726s	0.007s
1.851s	0.010s
1.803s	0.006s
1.784s	0.008s

ऊपर वर्णित विधि के लाभ:
+ हालत द्वारा तालिका से यादृच्छिक पंक्तियों का चयन करने का सबसे तेज़ संभव तरीका
+ तालिका में प्रत्येक पंक्ति के लिए यादृच्छिक संख्याओं को फिर से उत्पन्न करने की आवश्यकता नहीं है
+ एक अनुरोध में सभी आवश्यक मान पुनरावृति नहीं करते हैं

विपक्ष:
- एक अतिरिक्त तालिका में प्रवेश करने की आवश्यकता
- परिचित प्रश्नों को बदलने की आवश्यकता