Кодовете на префикс и индекса на селективност
Понякога искате да се индексират много дълго характер колони, поради това, индексите са големи и бавни. Една стратегия е емулация на индекс на хашиш, както показахме по-горе. Но понякога това не е достатъчно. Какво друго можеш да направиш?
Често можете да спестите място и да получите добри резултати, които са индексирани от първите няколко символа, но не и цялата разлика. Тогава индексът ще заемат по-малко пространство, но ще стане по-малко избирателно. Селективност индекс - отношението на броя на различните стойности са индексирани (кардиналност) към общия брой на редове в таблица (#t). Гамата от възможни стойности на селективността на 1 / # T до 1. Index с висока селективност е добро, защото позволява на MySQL да търсят съответните филтърни повече редове. Уникална индекс има селективност равен на единица.
Представката на колоната често е много селективен, за да се гарантира добро представяне. Ако индексиране петно колони или на текст или много дълги колони, като например VARCHAR, тогава задължението за определяне на кодовете за префикси, защото MySQL не позволява такива колонки индексирани от пълната си дължина.
Трудността се състои в избора на дължината на префикс, който трябва да бъде достатъчно голям, за да се осигури добра селективност, но не прекалено голяма, за да се пести място. Представката е избран за толкова дълго време, че да се възползват от използването му е почти същото като използването на индекса на пълна колона. С други думи, префикс кардиналност трябва да бъде почти същата като на цялата колона кардиналността.
За да се определи подходяща дължина на префикса, намиране на общи ценности и сравни техния списък със списъка на най-често използваните представки. Базата данни на тест Sakila 1 няма подходящ пример за демонстриране на данните, така че ние ще създадем една маса въз основа на града на масата, така че имахме достатъчно информация:
Създаване на таблица sakila.city_demo (град VARCHAR (50) NOT NULL);
INSERT INTO sakila.city_demo (град) SELECT града от sakila.city;
-- Повторете следната команда пет пъти:
INSERT INTO sakila.city_demo (град) SELECT града от sakila.city_demo;
-- Рандомизирането разпределението на (неефективни, но удобно):
SET град = (SELECT град от sakila.city РЕД ОТ RAND () ГРАНИЦА 1);
Сега имаме тестов набор от ценности. Разпределението на резултатите е далеч от реалистични, така че ние използвахме (функцията RAND). Поради това, вие ще наблюдавате различни резултати от нашите, но и за този пример, че не е от съществено значение. На първо място, ние откриваме най-често на града:
MySQL> SELECT COUNT (*) AS CNT, град
-> ОТ sakila.city_demo GROUP BY град ORDER BY CNT DESC LIMIT 10;
Имайте предвид, че всяка стойност се случва между 45 и 65 пъти. Сега ние намерите най-често срещаните префиксите на имената на градове, започвайки с трибуквени:
MySQL> SELECT COUNT (*) AS CNT, НАЛЯВО (град, 3) като Pref
-> ОТ sakila.city_demo ГРУПА ОТ Pref РЕД ОТ CNT Низходящо ГРАНИЦА 10;
Броят на повторения на всеки префикс е много по-голям, така че уникалните представките е много по-малки, отколкото общите уникални имена на градове. Идеята е да се увеличи дължината на префикс толкова дълго, колкото той няма да бъде толкова селективно като по цялата дължина на колоната. Няколко експерименти позволиха да разберете, че седем знака са достатъчни:
MySQL> SELECT COUNT (*) AS CNT, НАЛЯВО (град, 7) като Pref
-> ОТ sakila.city_demo ГРУПА ОТ Pref РЕД ОТ CNT Низходящо ГРАНИЦА 10;
Друг начин за определяне на подходящата дължина префикс е да се изчисли селективност пълна колона и се опитайте да вземете дължината на префикс, който осигурява близо селективност. Ето как можете да намерите на избирателността на пълен колоната:
MySQL> SELECT COUNT (DISTINCT град) / COUNT (*) FROM sakila.city_demo;
Като цяло, префиксът ще бъде толкова добър, ако неговата селективност е приблизително 0,031. Можете да оцените няколко различни дължини на представките в една заявка, която е полезна за много големи таблици. Ето как можете да намерите на селективността за различни дължини префикс в една и съща заявка:
MySQL> SELECT COUNT (DISTINCT НАЛЯВО (град, 3)) / COUNT (*) AS sel3,
-> COUNT (DISTINCT НАЛЯВО (град, 4)) / COUNT (*) AS sel4,
-> COUNT (DISTINCT НАЛЯВО (град, 5)) / COUNT (*) AS sel5,
-> COUNT (DISTINCT НАЛЯВО (град, 6)) / COUNT (*) AS sel6,
-> COUNT (DISTINCT НАЛЯВО (град, 7)) / COUNT (*) AS sel7
-> ОТ sakila.city_demo;
| sel3 | sel4 | sel5 | sel6 | sel7
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310
Искането показва, че последователна дължина увеличение префикс дава леко подобрение в селективността на до седем знака.
Това не е достатъчно, за да се обърне внимание само на високата селективност. Вие също трябва да се мисли за селективността в най-лошия случай. Въз основа на средна селективност може да дойде до заключението, че представката в четири или пет символа достатъчно дълго, но ако данните се разпределя много неравномерно, тя може да ви доведе в капан. С поглед към броя на повторения на най-често срещаните четири писмо префиксите на имена на градове, ще видите ясно неравномерно:
MySQL> SELECT COUNT (*) AS CNT, НАЛЯВО (град, 4) като Pref -> ОТ sakila.city_demo ГРУПА ОТ Pref РЕД ОТ CNT Низходящо ГРАНИЦА 5;
| 205 | сан | 200 | Sant | 135 | Sout | 104 | чан | 91 | Toul + ----- + ------ +
С дължината си от четири знака, най-често срещаните префиксите са много по-чести, отколкото най-честата пълноценно. Това означава, че селективността на тези стойности са по-ниски от средната селективност. Ако имате по-реалистичен набор от данни, с изключение на тази, генерирана случайна извадка, е вероятно, че този ефект може да бъде много по-силно изразени. Така например, изграждането на четирицифрен префикс на индекса на истинските имена на градове в света ще даде много ниска селективност за градовете, започващи с «Сан» и «Нова», което е много.
Сега, намирането на подходяща дължина префикс за нашия тестови данни, създаване на индекс на префикс на колоната:
MySQL> ALTER TABLE sakila.city_demo Добавяне на ключ (град (7));
Префикс индекси може да бъде добър начин за намаляване на размера и подобряване на ефективността на индекса, но те също имат недостатъци
ки: MySQL не може да използва кодовете префикс за всички въпроси с ORDER BY клауза и GROUP BY, или като индекс на покритие.