MySQL ORDER BY, LIMIT और DISTINCT को कैसे ऑप्टिमाइज़ करता है

ऐसे कार्य हैं, जो संबंधपरक डीबीएमएस के ढांचे के भीतर, सार्वभौमिक समाधान नहीं हैं, और कम से कम कुछ स्वीकार्य परिणाम प्राप्त करने के लिए, आपको बैसाखी के पूरे सेट के साथ आना होगा, जिसे आप बाद में गर्व से "आर्किटेक्चर" कहते हैं। इतना समय पहले नहीं, मैं सिर्फ उससे मिला था।

मान लीजिए कि कुछ संस्थाएँ A और B हैं, जो एक-से-कई सिद्धांत के अनुसार आपस में जुड़ी हुई हैं। इन संस्थाओं के उदाहरणों की संख्या काफी बड़ी है। उपयोगकर्ता के लिए इकाइयाँ प्रदर्शित करते समय, इकाई A और निकाय B. दोनों के लिए कई स्वतंत्र मानदंड लागू करना आवश्यक है। इसके अलावा, मापदंड लागू करने का परिणाम पर्याप्त रूप से बड़ी शक्ति का एक सेट है - कई मिलियन रिकॉर्ड के क्रम का। फ़िल्टरिंग मानदंड और छँटाई सिद्धांत उपयोगकर्ता द्वारा निर्धारित किए जाते हैं। मैं कैसे (मैं यह भी पूछूंगा: उन्हें एक स्क्रीन पर लाखों रिकॉर्ड की आवश्यकता क्यों है? - लेकिन वे कहते हैं कि यह आवश्यक है) उपयोगकर्ता को 0 सेकंड के समय में यह सब दिखाने के लिए?
ऐसी समस्याओं को हल करना हमेशा दिलचस्प होता है, लेकिन उनका समाधान DBMS पर अत्यधिक निर्भर करता है जिसके तहत आपका डेटाबेस चल रहा है। यदि आप ओरेकल के रूप में अपनी आस्तीन में एक इक्का हैं, तो एक मौका है कि वह इन बैसाखियों को खुद ही प्रतिस्थापित करेगा। लेकिन पृथ्वी पर नीचे जाओ - हमारे पास केवल MySQL है, इसलिए आपको सिद्धांत पढ़ना होगा।

Sql का उपयोग करते हुए, कार्य निम्नानुसार तैयार किया जा सकता है:

drop table if exists pivot;
drop table if exists entity_details;
drop table if exists entity;

create table pivot
(
row_number int (4) unsigned auto_increment,
primary key pk_pivot (row_number)
)
engine = innodb;

insert into pivot(row_number)
select null
from information_schema.global_status g1, information_schema.global_status g2
limit 500;

create table entity(entt_id int (10) unsigned auto_increment,
order_column datetime,
high_selective_column int (10) unsigned not null ,
low_selective_column int (10) unsigned not null ,
data_column varchar (32),
constraint pk_entity primary key (entt_id)
)
engine = innodb;

create table entity_details(edet_id int (10) unsigned auto_increment,
det_high_selective_column int (10) unsigned not null ,
det_low_selective_column int (10) unsigned not null ,
det_data_column varchar (32),
entt_entt_id int (10) unsigned,
constraint pk_entity_details primary key (edet_id)
)
engine = innodb;

insert into entity(order_column,
high_selective_column,
low_selective_column,
data_column
)
select date_add(str_to_date("20000101", "%Y%m%d"),
interval (p1.row_number + (p2.row_number - 1) * 300 + (p3.row_number - 1) * 300 * 300) second
)
order_column,
round((p1.row_number + (p2.row_number - 1) * 300 + (p3.row_number - 1) * 300 * 300) / 3, 0)
high_selective_column,
(p1.row_number + (p2.row_number - 1) * 300 + (p3.row_number - 1) * 300 * 300) mod 10 low_selective_column,
p1.row_number + (p2.row_number - 1) * 300 + (p3.row_number - 1) * 300 * 300 data_column
from ( select * from pivot limit 300) p1,
( select * from pivot limit 300) p2,
( select * from pivot limit 300) p3;

insert into entity_details(det_high_selective_column,
det_low_selective_column,
det_data_column,
entt_entt_id
)
select e.high_selective_column + p.row_number det_high_selective_column,
case when e.low_selective_column = 0 then 0 else p.row_number end det_low_selective_column,
concat(e.data_column, ' det' ) det_data_column,
e.entt_id
from entity e,
( select * from pivot limit 2) p;

create index idx_entity_details_entt
on entity_details(entt_entt_id);

select *
from entity_details ed, entity e
where ed.entt_entt_id = e.entt_id
order by order_column desc
limit 0, 10;


माथे में जांच की योजना सांत्वना नहीं देती है।

आईडी	SELECT_TYPE	तालिका	टाइप	possible_keys	कुंजी	key_len	रेफरी	पंक्तियों	अतिरिक्त
1	सरल	ई	सभी	प्राथमिक				26982790	फाइलसर्ट का उपयोग करना
1	सरल	एड	रेफरी	idx_entity_details_entt	idx_entity_details_entt	5	test.e.entt_id	1	जहाँ का उपयोग करना

अनावश्यक अनुक्रमितों के एक समूह को उपद्रव करने से पहले, आइए विश्लेषण करें कि क्या 2 तालिकाओं के आधार पर ऐसी समस्या को हल करना संभव है। पहली तालिका को इकाई की विशेषताओं को संग्रहीत करने दें, और दूसरा - इसके बहुत विवरण। आमतौर पर आपको संदर्भात्मक अखंडता सुनिश्चित करने के लिए बाधाओं का उपयोग करना पड़ता है, लेकिन यदि आपके पास बड़ी मात्रा में डेटा और निरंतर DML'ing के साथ इस तरह की दो तालिकाएं हैं, तो मैं इसके लिए सिफारिश नहीं करूंगा क्योंकि BUG # 15136 नहीं है । और डेटाबेस को रीड इन मोड में स्पष्ट रूप से खतरनाक innodb_locks_unsafe_for_binlog पैरामीटर को सही तरीके से सेट करने के साथ डालने से आपको नहीं बचाया जाएगा। खैर, यह ऐसा है, वैसे, हमारी भेड़ों के पास वापस। पहले, आइए दस्तावेज़ीकरण देखें - ऑर्डर को गति देने के लिए हमारे विकल्प क्या हैं।

विकल्प 1. मान लीजिए कि हमारे पास एक इंडेक्स है, जिसमें पहले फ़ील्ड जिसमें कॉलम सूचीबद्ध हैं, और अंतिम कॉलम जिसमें सॉर्टिंग की गई है। चूंकि इंडेक्स में डेटा सॉर्ट किए गए फॉर्म में संग्रहीत है, इसलिए MySQL इसका उपयोग सॉर्टिंग को ऑप्टिमाइज़ करने के लिए कर सकता है। कार्य की एल्गोरिथ्म निम्नानुसार होगी। एक्सेस की भविष्यवाणी का उपयोग करते हुए, हम इंडेक्स के उन हिस्सों को खोजते हैं जिनकी हमें आवश्यकता है। योजना में, आप टाइप कॉलम में या तो रेंज , या रेफरी या इंडेक्स देखेंगे। डेटा के अगले बैच को प्राप्त करते हुए, रनटाइम मोड में, हम बाकी कॉलम पढ़ते हैं जिन्हें हमें जॉइन को पूरा करने की आवश्यकता होती है (क्योंकि इंडेक्स में सभी आवश्यक कॉलम नहीं हो सकते हैं, जो तार्किक लगता है) और निम्न तालिका से कनेक्शन बनाते हैं। चूंकि हम इंडेक्स से डेटा को क्रमबद्ध तरीके से पढ़ते हैं, इसलिए इस तरह के जॉइन का आउटपुट ऑर्डर किए गए फॉर्म में प्राप्त होगा। जब आपको सही मात्रा में डेटा मिलता है, तो आप इंडेक्स को पढ़ना बंद कर सकते हैं। हमारे लिए, इस दृष्टिकोण का मुख्य दोष यह होगा कि जिस स्तंभ द्वारा खोज की जाती है और जिस स्तंभ द्वारा प्रदर्शन किया जाता है वह स्तंभ अलग-अलग तालिकाओं में होते हैं। तो आपको वशीकरण करना होगा।
विकल्प 2 और 3 केवल उस स्थिति के लिए उपयुक्त हैं जब कनेक्शन के लिए डेटा वॉल्यूम छोटा है, अर्थात। वे केवल अत्यधिक चयनात्मक वक्ताओं के लिए उपयुक्त हैं। विकल्प दो में, हम परिणामों को शुरुआत में क्रमबद्ध करते हैं, और फिर जुड़ने का कार्य करते हैं, विकल्प तीन में, सम्मिलित होने के बाद छँटाई की जाती है। चूंकि MySQL द्वारा निष्पादित सॉर्टिंग केवल एक टेबल पर की जा सकती है - तीसरे मामले में, एक अस्थायी टेबल की आवश्यकता होती है। यह डरावना नहीं है, लेकिन यह बहुत कुछ खींच सकता है। हालांकि, तीसरा मामला एकमात्र संभव विकल्प है जब MySQL जुड़ने में तेजी लाने के लिए विभिन्न अनुकूलन लागू कर सकता है। पहले दो मामलों में, यह नेस्टेड लूप होगा। निष्पादन के संदर्भ में, ये प्रकार निम्नानुसार होंगे:

छाँटने के लिए एक सूचकांक का उपयोग करना	खाली है
तालिका ए के लिए फाइलशॉट का उपयोग करना	"फाइलशॉर्ट का उपयोग करना"
JOIN को सेव करने से परिणाम एक अस्थायी तालिका में आ जाता है और फाइलशॉट का उपयोग होता है	"अस्थायी का उपयोग करना; Filesort का उपयोग करना ”

दूसरे और तीसरे सॉर्टिंग तरीकों में, सीमा को केवल सॉर्टिंग प्रक्रिया के अंत में लागू किया जा सकता है, इस कारण से 2 और 3 के विकल्प सभी मामलों में उपयोग नहीं किए जा सकते हैं।
एक त्वरित फाइल मर्ज एक त्वरित सॉर्ट मर्ज से अधिक कुछ नहीं है।

छंटाई दो तरह से की जा सकती है। सबसे पहले, छंटाई शुरुआत में की जाती है, और फिर तालिका के बाकी डेटा को पढ़ा जाता है, जो क्वेरी को पूरा करने के लिए आवश्यक है। दूसरा विकल्प अनुकूलित माना जाता है, क्योंकि तालिका को एक बार पढ़ा जाता है, साथ ही सभी आवश्यक कॉलम, और फिर क्रमबद्ध। योजना के अनुसार, आप उन्हें एक दूसरे से अलग नहीं कर सकते हैं, हालांकि, एक त्वरित प्रयोग से, यह किया जा सकता है। लेकिन अनुकूलित छँटाई लागू किया जाएगा या नहीं, max_length_for_sort_data पैरामीटर जवाब । यदि टुपल्स में निहित डेटा इस पैरामीटर में निर्दिष्ट से कम है, तो छंटाई को अनुकूलित किया जाएगा।

drop procedure if exists pbenchmark_filesort;
delimiter $$
create procedure pbenchmark_filesort(i_repeat_count int (10))
main_sql:
begin
declare v_variable_value int (10);
declare v_loop_counter int (10) unsigned default 0;
declare continue handler for sqlstate '42S01' begin
end ;

create temporary table if not exists temp_sort_results(
row_number int (10) unsigned
)
engine = memory;
truncate table temp_sort_results;

select variable_value
into v_variable_value
from information_schema.session_status
where variable_name = 'INNODB_ROWS_READ' ;

begin_loop:
loop
set v_loop_counter = v_loop_counter + 1;

if v_loop_counter <= i_repeat_count then
insert into temp_sort_results(row_number)
select sql_no_cache row_number from pivot order by concat(row_number, '0' ) asc ;
truncate table temp_sort_results;
iterate begin_loop;
end if ;

leave begin_loop;
end loop begin_loop;

select variable_value - v_variable_value records_read
from information_schema.session_status
where variable_name = 'INNODB_ROWS_READ' ;
end
$$
delimiter ;

set session max_length_for_sort_data = 0;
call pbenchmark_filesort(10000);
-- records_read
-- 10 000 000
-- 0:00:12.317 Query OK

set session max_length_for_sort_data = 1024;
call pbenchmark_filesort(10000);
-- records_read
-- 5 000 000
-- 0:00:06.228 Query OK



जैसा कि आप उदाहरण से देख सकते हैं, एल्गोरिथम संख्या 2 द्वारा छंटनी वास्तव में दो बार तेजी से होती है, क्योंकि यह 2 गुना कम लाइनों को पढ़ती है। हालांकि, यह ध्यान में रखा जाना चाहिए कि अनुकूलित सॉर्टिंग के लिए महत्वपूर्ण मेमोरी लागतों की आवश्यकता होती है, और यदि आप वैरिएबल max_length_for_sort_data के मूल्य को बहुत अधिक मूल्य पर सेट करते हैं, तो आपको पूरी तरह से सरल प्रोसेसर के साथ बहुत सारे इनपुट और आउटपुट मिलेंगे।
इसलिए, हम उपलब्ध समाधानों का पूरा लाभ उठाएंगे। हम वनीकरण कर रहे हैं (आप इसका समर्थन कैसे करेंगे, यह एक और मामला है)। और हम गैर-चयनात्मक वक्ताओं के लिए समग्र सूचकांक और अत्यधिक चयनात्मक लोगों के लिए सामान्य लोगों का निर्माण करते हैं। इस प्रकार, सूचकांक द्वारा छँटाई और सीमा द्वारा सीमित करने का उपयोग उन मानदंडों के प्रश्नों के लिए किया जाएगा जिनमें केवल कम-चयनात्मक कॉलम शामिल हैं, और अत्यधिक चयनात्मक लोगों के लिए, अधिक कॉम्पैक्ट और तेज़ इंडेक्स का उपयोग किया जाएगा, जिसके बाद विलय के बिना एकल-पास फ़िसॉर्ट्स।

create table entity_and_details(entt_entt_id int (10) unsigned not null ,
edet_edet_id int (10) unsigned not null ,
order_column datetime,
high_selective_column int (10) unsigned not null ,
low_selective_column int (10) unsigned not null ,
det_high_selective_column int (10) unsigned not null ,
det_low_selective_column int (10) unsigned not null ,
constraint pk_entity_and_details primary key (entt_entt_id, edet_edet_id)
)
engine = innodb;

insert into entity_and_details(entt_entt_id,
edet_edet_id,
order_column,
high_selective_column,
low_selective_column,
det_high_selective_column,
det_low_selective_column
)
select entt_id,
edet_id,
order_column,
high_selective_column,
low_selective_column,
det_high_selective_column,
det_low_selective_column
from entity_details ed, entity e
where ed.entt_entt_id = e.entt_id;

create index idx_entity_and_details_low_date
on entity_and_details(low_selective_column, order_column);

create index idx_entity_and_details_det_low_date
on entity_and_details(det_low_selective_column, order_column);

create index idx_entity_and_details_date
on entity_and_details(order_column);

create index idx_entity_and_details_high
on entity_and_details(high_selective_column);

create index idx_entity_and_details_det_high
on entity_and_details(det_high_selective_column);


दुर्भाग्य से, विकृति के बाद, हमें एक खामी मिली। पृष्ठ लेआउट विभिन्न पृष्ठों पर एक इकाई के विवरण को बिखेर सकता है। हम इसकी अनुमति नहीं दे सकते, इसलिए मैंने चर्चों को और आगे बढ़ाने का प्रस्ताव रखा। एक बार में सभी आवश्यक डेटा का चयन करने के बजाय, हम केवल ए की पहचानकर्ता का चयन करते हैं, सभी मानदंडों की स्थिति के साथ, पेजिंग के लिए सीमा संरचना में जोड़ें, और फिर इकाई का विवरण चुनें।

select distinct entt_entt_id
from entity_and_details
where det_low_selective_column = 0
order by order_column
limit 0, 3;

select *
from entity_and_details
where entt_entt_id in (10, 20, 30);


सच कहूँ तो, मेरे दिमाग में, इस तरह के संस्करणों की तालिका पर + सीमा द्वारा भिन्न + आदेश के रूप का एक डिजाइन जल्दी से काम नहीं कर सकता है, हालांकि, MySQL डेवलपर्स अलग तरीके से सोचते हैं। इस मुद्दे पर काम करते हुए, मैं एक Not BUG # 33087 बग पर आया , जो हमेशा की तरह बग नहीं था, बल्कि एक फीचर था। उसके बाद, मैंने यह समझने का फैसला किया कि MySQL अलग कैसे अनुकूलन करता है । प्रलेखन में पहला वाक्यांश था: अलग-अलग अनुकूलन के लिए, समूह द्वारा उपयोग किए जाने वाले सभी समान अनुकूलन का उपयोग किया जा सकता है। पूरी तरह से समूहीकरण दो एल्गोरिदम द्वारा किया जा सकता है।

पहला मामला केवल तभी लागू किया जा सकता है जब कोई ऐसा सूचकांक हो जो समूहीकरण को स्वचालित रूप से परिभाषित करता हो। दूसरी योजना का उपयोग अन्य सभी मामलों में किया जाता है। इंडेक्स ग्रुपिंग को क्रमिक रूप से निष्पादित किया जाता है और इसे सीमित किया जा सकता है - और यह हमारे लिए बहुत अच्छा है, क्योंकि यह वही है जो हमें कम-चयनात्मक सूचकांकों पर परिणाम प्रदर्शित करने की आवश्यकता है। इस दृष्टिकोण के साथ अस्थायी तालिकाओं का उपयोग नहीं किया जाता है (ईमानदार होने के लिए, यह पूरी तरह से सच नहीं है, क्योंकि सामान्य तौर पर आपको आंशिक जुड़ाव के परिणामों को संग्रहीत करने की आवश्यकता होती है, साथ ही वर्तमान समूह के लिए समूह के परिणामस्वरूप मूल्यों को स्वयं प्राप्त किया जाता है, लेकिन ये त्रिगुणात्मक हैं)। दूसरी योजना बहुत धीरे-धीरे काम करती है और रैम का एक गुच्छा खाती है, क्योंकि सभी समूहीकरण परिणामों को एक अस्थायी तालिका में संग्रहीत किया जाना चाहिए, जिसमें समूह पहचानकर्ता प्राथमिक कुंजी है। बहुत बड़े मूल्यों पर, यह तालिका MyISAM में बदल जाती है और डिस्क में प्रवाहित हो जाती है। सभी नए आने वाले समूहों को पहले इस बड़ी तालिका में खोजा जाता है, और फिर इसमें मानों को बदल दें, यदि आवश्यक हो, या समूह नहीं मिलने पर एक नई पंक्ति जोड़ें। फाइनल सॉर्टिंग इस एल्गोरिथ्म के प्रदर्शन में सुधार करता है। इस कारण से, यदि आपको ऑर्डर किए गए सेट की आवश्यकता नहीं है, तो इसे अक्सर शून्य द्वारा ऑर्डर जोड़ने की सिफारिश की जाती है।
इस प्रकार, पहला दृष्टिकोण, साथ ही तथ्य यह है कि MySQL डेटा की खोज करना बंद कर देता है यदि सीमा निर्माण का उपयोग अलग-अलग किया जाता है, तो हमें समूहन परिणाम बहुत जल्दी प्राप्त करने की अनुमति देता है।

अब अप्रिय के बारे में

तो, हमें एक एल्गोरिथ्म मिला जो जल्दी से पहला रिकॉर्ड ढूंढता है, लेकिन यह एल्गोरिथ्म हमारे लिए रिकॉर्ड की कुल संख्या की गणना नहीं कर सकता है। वर्कअराउंड के रूप में, आप एक और प्रविष्टि वापस कर सकते हैं ताकि स्क्रीन पर अगला बटन दिखाई दे।
MySQL एक टेबल के लिए क्वेरी में एक से अधिक इंडेक्स का उपयोग नहीं कर सकता है। यह हमें कैसे धमकी देता है? आइए एक क्वेरी को निष्पादित करने का प्रयास करें जैसे:

select distinct entt_entt_id
from entity_and_details
where det_low_selective_column = 0 and low_selective_column = 1
order by order_column
limit 0, 3;

मेरा विश्वास करो, आप इसे खत्म करने के लिए इंतजार नहीं करेंगे, क्योंकि यदि आप डेटा को देखते हैं, तो आप एक विशेष रूप से गठित विफलता देखेंगे। मानदंड det_low_selective_column = 0 को संतुष्ट करने वाले सेट का मानदंड कम_सेप्टिव_column = 1 को संतुष्ट करने वाले सेट के साथ कोई अंतर नहीं है। हम यह जानते हैं, लेकिन दुर्भाग्य से MySQL इसके बारे में कुछ नहीं जानता है। इसलिए, वह इंडेक्स का चयन करता है, जो कि उसकी राय में स्कैनिंग के लिए सबसे उपयुक्त है और 2 इंडेक्स, चयनित और पीके का पूर्ण स्कैन करता है। यह लंबे समय से घातक है क्योंकि प्राथमिक कुंजी को स्कैन करना वास्तव में अपने क्लस्टर संगठन के कारण तालिका का पूर्ण स्कैन है। ऐसे सभी अंतरालों पर समग्र सूचकांकों के साथ कर लगाया जाना चाहिए।

और अंत में, सबसे अप्रिय क्षण। InnoDB प्रति सूचकांक केवल एक म्यूटेक्स का उपयोग करता है। इसलिए, जब आप एक ही समय में डेटा लिखने और पढ़ने वाले प्रश्नों को मिलाते हैं, तो InnoDB इंजन इंडेक्स को लॉक कर देता है, रीड ऑपरेशंस को रोक देता है, क्योंकि राइट ऑपरेशन से बी-ट्री विभाजन हो सकता है और वांछित रिकॉर्ड दूसरे पेज में दिखाई दे सकते हैं। स्मार्ट डेटाबेस पूरे पेड़ को अवरुद्ध नहीं करते हैं, लेकिन केवल इसका विभाजन भाग होता है। MySQL में, यह एल्गोरिथ्म अभी तक लागू नहीं हुआ है (हालाँकि आप इस विषय पर InnoDB प्लगइन देख सकते हैं, 5.5 में ऐसा लगता है कि यह मानक बन गया है, शायद वहां सब कुछ इतना बुरा नहीं है)। इस समस्या को हल करने के लिए, डेटा को अलग करना आवश्यक है, अर्थात विभाजन का उपयोग करें। विभाजन तालिका में सभी अनुक्रमित स्थानीय रूप से विभाजित होते हैं, वास्तव में, इस संबंध में प्रत्येक विभाजन एक अलग तालिका है, लेकिन विभाजन एक और कहानी है और मैं इस लेख में उस पर स्पर्श नहीं करूंगा।

निष्कर्ष

कई समस्याओं का समाधान, जो कठोर वास्तविकता का संकेत देती है, का वर्णन संक्षेप में नहीं किया जा सकता है। कई सेटों में बहु-मापदंड खोज की समस्या उनमें से एक है। आप स्फिंक्स का उपयोग कर सकते हैं और इस समुदाय द्वारा प्रदान किए गए कई टेम्पलेट डाउनलोड कर सकते हैं, लेकिन जल्द ही या बाद में आप अभी भी एक समस्या का सामना करेंगे जो आपके लिए कोई भी नहीं आया है। और इसलिए, फिर आपको प्रलेखन में जाना होगा, डेटा का अध्ययन करना होगा और उपयोगकर्ताओं को ऐसा करने के लिए राजी करना होगा। मुझे उम्मीद है कि यह समीक्षा लेख आपको कुछ ऐसे नुकसानों से बचने की अनुमति देता है जो इस कांटेदार पथ पर आपकी प्रतीक्षा कर रहे हैं।

ZY मुझे समूह के लिए कई अनुकूलन छोड़ना पड़ा, क्योंकि लेख बहुत बड़ा निकला। मुद्दों की पूरी समझ के लिए आप आधिकारिक दस्तावेज में उनके बारे में पढ़ सकते हैं, साथ ही कई व्यावहारिक सुझाव भी दे सकते हैं।
* सभी कोड स्रोतों को सोर्स कोड हाइलाइटर के साथ हाइलाइट किया गया था ।