SQL-дегі индекстер
Дерекқордағы индекстердің жұмысы, оларды баптау және қолдану туралы қысқаша ескертпе.
Кіріспе
Дерекқордағы индекстер сұраулардың өнімділігін арттыру және деректерге қол жеткізуді жеделдету үшін қолданылады. Олар сұрауларды орындауды оңтайландыруда маңызды рөл атқарады және үлкен көлемдегі деректерден ақпаратты тиімдірек алуға мүмкіндік береді.
Егер дерекқорды кітаппен салыстырсақ, индекстер — бұл мазмұн, пәндік көрсеткіш. Әрине, қажетті ақпаратты іздеу үшін бүкіл кітапты оқып шығуға болады, бірақ қажетті бетке бірден жүгіну әлдеқайда ыңғайлы.
Қолдану саласы
Индекстер, кез келген басқа құралдар сияқты, саналы түрде қолданылуы керек. Іздеу жүргізілмейтін өрістерге индекстер қоюдың мағынасы жоқ. Бірақ индекстер міндетті түрде қойылуы тиіс өрістер мен кестелер бар.
Мысалы, бізде екі кесте бар: Blog және Tag. Кестелер "көптен көпке" (ManyToMany) байланысына ие, өйткені блогтағы бір жазба әртүрлі тегтерге ие болуы мүмкін, және әрбір тег бірнеше жазбаға тиесілі болуы мүмкін. Осылайша, бізде BlogTag деп аталатын үшінші кесте пайда болады, онда тек екі баған бар: blog_id және tag_id, олар сәйкес кестелердегі жазбаларға сілтеме жасайды. Бұл байланыстырушы кестеде екі баған да индекстеліп қана қоймай, сонымен қатар сәйкес кестелердегі жазбаларға сілтеме жасайтын сыртқы кілттерге ие болуы керек.
Нақты тәжірибе көрсеткендей, тіпті шамамен 100 000 жазбасы бар шағын кестелерде бірнеше қосылу операцияларын (JOIN) қолданған кезде, деректерді таңдау жылдамдығы бірнеше есе артуы мүмкін. Мен өз тәжірибемде қажетті индекстерді қойып, сыртқы кілттерді жазу арқылы күрделі сұрауларды өңдеу жылдамдығын шамамен он есе арттыруға қол жеткіздім, ~2.7 секундтан ~0.4 секундқа дейін.
Индекстер түрлері
B-tree
Бұл, бәлкім, реттелген кілттердің теңдестірілген ағашы ретінде ұйымдастырылған индекстердің ең жиі қолданылатын түрі.
Мұндай индекс бойынша тек нақты мәндерді ғана емес, сонымен қатар салыстырмалы мәндерді (үлкен немесе кіші) іздеуге болады.
Мұндай индекс келесі типтегі сұрауларға сәйкес келеді:
WHERE age = num;
WHERE age > num;
WHERE age < num;
WHERE name LIKE 'John%';
Сонымен қатар, бұл индекс іздеу жолының басында анықталмаған мәндерді қою кезінде сұрауларға сәйкес келмейді, мысалы:
WHERE name LIKE '%Doe';
B-tree индексі ағаш бойынша айналым жасайтындықтан, бұл жағдайда бастапқы кіру нүктесі анықталмаған және индекс ескерілмейді, оның орнына дерекқор сәйкес мәндерді іздеу үшін бүкіл кестені айналып өтеді.
Бұл шектеуді reverse индексін қосу арқылы айналып өтуге болады. Бұл жағдайда сұрау басқаша көрінеді:
WHERE reverse(name) LIKE reverse('%Doe');
Reverse не үшін қолданылуы мүмкін? Мысалы, нақты серверлердің электрондық пошта мекенжайларын іздеу үшін.
WHERE reverse(email) LIKE reverse('%@gmail.com');
HASH
Бұл индекс түрі мәндердің өзін емес, олардың хэштерін сақтауды болжайды, бұл үлкен өрістерден индекстерді өңдеу өлшемі мен жылдамдығын азайтады. Осылайша, сұраулар кезінде өрістердің хэштері салыстырылады. Бұл индекстер түрін массив элементін кілт бойынша таңдаумен салыстыруға болады.
WHERE name = 'John Doe';
Хэштер салыстырылатындықтан, бұл индексті салыстырмалы мәндерді (үлкен немесе кіші) іздеу үшін пайдалану мүмкін емес. Осылайша, келесі сұрауларда индекс қолданылмайды:
WHERE name LIKE 'John%';
WHERE name LIKE '%Doe';
WHERE age > num;
WHERE age < num;
WHERE name IS NULL;
Сонымен қатар, дерекқорда бірдей мәндерді сақтау мүмкіндігіне байланысты сәйкес хэштер үшін қақтығыстарды шешу әдістері қолданылады.