Како да користите индекси во MySQL

Авторот ја избра програмата Пишувај за донации.

Вовед

Релационите бази на податоци може да се користат за работа со податоци од која било големина, вклучувајќи големи бази на податоци кои содржат милиони редови. Структурниот јазик за пребарување (SQL) дава концизен и јасен начин да се најдат одредени редови во табелите на базата на податоци врз основа на специфични критериуми. Како што базите на податоци стануваат се поголеми, лоцирањето на одредени редови во нив станува сè потешко, слично на пребарувањето за игла во стог сено.

Способноста за базите на податоци да прифатат широк опсег на услови за пребарување го прави предизвик за моторот на базата на податоци да предвиди кои прашања ќе бидат најчести. Моторот мора да биде подготвен за ефикасно лоцирање на редови во табелите со бази на податоци, без оглед на нивната големина. Меѓутоа, како што податоците се зголемуваат, перформансите на пребарувањето може да страдаат. Колку е поголема базата на податоци, толку е потешко за моторот на базата на податоци брзо да ги пронајде документите што одговараат на барањето.

Администраторите на базите на податоци можат да користат индекси за да му помогнат на моторот на базата на податоци и да ги подобрат неговите перформанси.

Во ова упатство, ќе научите што се индекси, како да ги креирате и дали се користат за пребарување на базата на податоци.

Предуслови

За да го следите ова упатство, ќе ви треба компјутер со систем за управување со релациона база на податоци (RDBMS) базиран на SQL. Инструкциите и примерите во ова упатство беа потврдени со користење на следната средина:

• Сервер кој работи на Ubuntu 20.04, со корисник кој не е root со административни привилегии и заштитен ѕид конфигуриран со UFW, како што е опишано во нашиот првичен водич за поставување сервер за Ubuntu 20.04.
• MySQL е инсталиран и заштитен на серверот, како што е наведено во чекор 3.
• Основно познавање со извршувањето на барањата SELECT за да се преземат податоци од базата на податоци, како што е опишано во нашиот Како да се одберат редови од табели во SQL водичот.

Забелешка: Ве молиме имајте предвид дека многу RDBMS користат свои уникатни имплементации на SQL. Иако командите наведени во ова упатство ќе работат на повеќето RDBMS, индексите не се дел од стандардната SQL синтакса, и затоа точната синтакса или излезот може да се разликуваат ако ги тестирате на систем различен од MySQL.

Ќе ви треба и база на податоци со некои табели натоварени со примерок на податоци за да можете да вежбате со користење на индекси. Ве охрабруваме да го поминете следниов дел Поврзување со MySQL и поставување на база на податоци за примерок за детали за поврзување со MySQL сервер и креирање на базата за тестирање што се користи во примерите низ овој водич.

Поврзување со MySQL и поставување на база на податоци за примерок

Во овој дел, ќе се поврзете со MySQL сервер и ќе креирате примерок од базата на податоци за да можете да ги следите примерите во ова упатство.

Ако вашиот систем за база на податоци SQL работи на оддалечен сервер, SSH во вашиот сервер од вашата локална машина:

ssh sammy@your_server_ip

Потоа отворете го потсетникот за серверот MySQL, заменувајќи го sammy со името на вашата корисничка сметка на MySQL:

mysql -u sammy -p

Создадете база на податоци со име индекси:

CREATE DATABASE indexes;

Ако базата на податоци е успешно креирана, ќе добиете излез вака:

Output
Query OK, 1 row affected (0.01 sec)

За да ја изберете базата на податоци indexes, извршете ја следнава изјава USE:

USE indexes;

Ќе го добиете следниот излез:

Output
Database changed

Откако ќе ја изберете базата на податоци, можете да креирате примерок табела во неа. За овој водич, ќе користите имагинарна база на податоци за вработените за да складирате детали за тековните вработени и нивните работни уреди.

Табелата вработени ќе содржи поедноставени податоци за вработените во базата на податоци. Ќе ги содржи следните колони:

• employee_id: оваа колона го содржи идентификаторот на вработениот, претставен со типот на податоци int. Оваа колона ќе стане примарен клуч на табелата, при што секоја вредност ќе стане единствен идентификатор за соодветниот ред.
• first_name: оваа колона го содржи името на секој вработен, изразено со користење на типот на податоци varchar со максимум 50 знаци.< /li>
• last_name: оваа колона го содржи презимето на секој вработен, изразено со користење на типот на податоци varchar со максимум 50 знаци.< /li>
• device_serial: оваа колона го содржи серискиот број на компјутерот доделен на вработениот, изразен со користење на типот на податоци varchar со максимум 15 знаци.
• плата: оваа колона ја содржи платата на секој вработен, изразена со користење на типот на податоци int за складирање на нумерички податоци.

Направете примерок од табелата со следнава команда:

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

Ако се отпечати следниот излез, табелата е креирана:

Output
Query OK, 0 rows affected (0.00 sec)

После тоа, вчитајте ја табелата вработени со некои примероци на податоци со извршување на следнава операција INSERT INTO:

INSERT INTO employees VALUES
    (1, 'John', 'Smith', 'ABC123', 60000),
    (2, 'Jane', 'Doe', 'DEF456', 65000),
    (3, 'Bob', 'Johnson', 'GHI789', 70000),
    (4, 'Sally', 'Fields', 'JKL012', 75000),
    (5, 'Michael', 'Smith', 'MNO345', 80000),
    (6, 'Emily', 'Jones', 'PQR678', 85000),
    (7, 'David', 'Williams', 'STU901', 90000),
    (8, 'Sarah', 'Johnson', 'VWX234', 95000),
    (9, 'James', 'Brown', 'YZA567', 100000),
    (10, 'Emma', 'Miller', 'BCD890', 105000),
    (11, 'William', 'Davis', 'EFG123', 110000),
    (12, 'Olivia', 'Garcia', 'HIJ456', 115000),
    (13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
    (14, 'Isabella', 'Wilson', 'NOP012', 125000),
    (15, 'Matthew', 'Martinez', 'QRS345', 130000),
    (16, 'Sophia', 'Anderson', 'TUV678', 135000),
    (17, 'Daniel', 'Smith', 'WXY901', 140000),
    (18, 'Mia', 'Thomas', 'ZAB234', 145000),
    (19, 'Joseph', 'Hernandez', 'CDE567', 150000),
    (20, 'Abigail', 'Smith', 'FGH890', 155000);

Базата на податоци ќе одговори со порака за успех:

Output
Query OK, 20 rows affected (0.010 sec)
Records: 20  Duplicates: 0  Warnings: 0

Забелешка: Збирот на податоци не е доволно голем за директно да го илустрира влијанието на индексите врз перформансите. Сепак, оваа база на податоци ќе покаже како MySQL ги користи индексите за да го ограничи количеството на поминати редови за да изврши прашања и да добие резултати.

Со тоа, подготвени сте да го следите остатокот од водичот и да започнете со користење на индекси во MySQL.

Вовед во индекси

Вообичаено, кога извршувате барање против базата на податоци MySQL, базата мора да ги помине сите редови во табелата еден по еден. На пример, можеби ќе сакате да пребарувате презимиња на вработени кои одговараат на Smith или на сите вработени со плата повисока од 100000 $. Секој ред во табелата ќе се испитува еден по еден за да се потврди дали одговара на состојбата. Ако го направи тоа, ќе се додаде на листата на вратени редови. Ако не, MySQL ќе ги скенира следните редови додека не ја прелистува целата табела.

Иако овој метод за наоѓање соодветни редови е ефикасен, тој може да стане бавен и да бара ресурси како што се зголемува големината на табелата. Како резултат на тоа, овој пристап можеби не е соодветен за големи табели или прашања кои бараат чест или брз пристап до податоците.

За да ги решите проблемите со перформансите со големи табели и прашања, можете да користите индекси. Индексите се уникатни структури на податоци кои складираат само подредено подмножество на податоци одделно од редовите на табелата. Тие му овозможуваат на моторот на базата на податоци да работи побрзо и поефикасно кога барате вредности или нарачувате според одредено поле или збир на полиња.

Користејќи ја табелата вработени како пример, едно од типичните прашања што можете да ги извршите е да ги пронајдете вработените по нивното презиме. Без никакви индекси, MySQL ќе го извади секој вработен од табелата и ќе потврди дали презимето се совпаѓа со барањето. Но, кога се користи индекс, MySQL ќе има посебна листа со презимиња, која содржи само покажувачи до редови за дадените вработени во главната табела. Потоа ќе го користи тој индекс за да ги врати резултатите без да ја скенира целата табела.

Можете да ги замислите индексите како аналогија на телефонскиот именик. За да лоцирате личност по име John Smith во книгата, прво превртете на десната страница каде што се наведени луѓето со имиња што почнуваат со S, а потоа погледнете низ страниците за луѓе. со имиња кои почнуваат со Sm. Следејќи ја таа логика, можете брзо да елиминирате многу записи, знаејќи дека тие не се совпаѓаат со личноста што ја барате. Процесот функционира само затоа што податоците во телефонскиот именик се подредени по азбучен ред, што ретко е случај со податоците складирани директно во базата на податоци. Индексот во моторот на базата на податоци служи за слична цел како и телефонскиот именик, чувајќи ги азбучно подредените референци на податоците и на тој начин и помага на базата на податоци брзо да ги пронајде потребните редови.

Користењето индекси во MySQL има повеќекратни придобивки. Најчести се забрзувањето на клаузулите ORDER BY побрзо и спроведувањето на уникатноста на вредноста.

Сепак, користењето на индекси може да го намали максимумот на перформансите на базата на податоци во некои околности. Индексите се дизајнирани да го забрзаат пребарувањето на податоците и се имплементираат со користење на дополнителни структури на податоци кои се складираат заедно со податоците од табелата. Тие структури мора да се ажурираат со секоја промена во базата на податоци, што може да ја забави изведбата на барањата SELECT понекогаш може да се надминат со значително побавната изведба на барањата што запишуваат податоци во базата на податоци.

Се препорачува да се креираат индекси само кога има јасна потреба за нив, како на пример кога перформансите на апликацијата почнуваат да опаѓаат. Кога избирате кои индекси да се креираат, земете ги предвид прашањата што се извршуваат најчесто и одземаат најдолго време, и градете индекси врз основа на условите за барање што ќе имаат најголема корист од нив.

Забелешка: Овој водич има за цел да ја воведе темата за индекси на бази на податоци во MySQL, илустрирајќи ги вообичаените апликации и типови на индекси. Моторот на базата на податоци поддржува многу посложени сценарија за користење на индекси за зголемување на перформансите на базата на податоци, што е надвор од опсегот на овој водич. Ве охрабруваме да се консултирате со официјалната MySQL документација за индекси за поцелосен опис на карактеристиките на базата на податоци.

Во следните чекори, ќе креирате индекси од различни типови за низа сценарија. Ќе научите како да потврдите дали индексите се користат во барањето. Конечно, ќе научите да отстранувате индекси доколку е потребно.

Користење на индекси со една колона

Индексот со една колона е најчестиот и наједноставен тип на индекс што можете да го користите за да ја оптимизирате изведбата на барањето. Овој тип на индекс и помага на базата на податоци да ги забрза барањата што ја филтрираат базата на податоци врз основа на вредности од една колона. Индексите создадени на една колона може да забрзаат многу условни барања, вклучувајќи точни совпаѓања со користење на операторот = и споредби со операторите > или <.

Во примерот на базата на податоци што ја креиравте во претходниот чекор, нема индекси. Пред да креирате индекс, прво ќе тестирате како базата на податоци се справува со прашањата SELECT на табелата вработени кога клаузулата WHERE се користи само за барање подмножество податоци од табелата.

Претпоставете дека сакате да најдете вработени со плата од точно 100000 $. Извршете го следното барање:

SELECT * FROM employees WHERE salary = 100000;

Клаузулата WHERE бара точно совпаѓање на вработените со платата што одговара на бараната вредност. Во овој пример, базата на податоци ќе одговори на следниов начин:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Забелешка: Како што е прикажано на излезот погоре, базата на податоци одговори речиси моментално на издаденото барање. Со само неколку примероци од редови во базата на податоци, користењето индекси нема видливо да влијае на перформансите на барањето. Меѓутоа, со големи збирки на податоци, ќе забележите значителни промени во времето на извршување на барањето пријавено од базата на податоци по извршувањето на барањето.

Судејќи според излезот на барањето, не можете да знаете како моторот на базата на податоци му пристапил на проблемот со наоѓање на соодветните редови во табелата. Сепак, MySQL обезбедува начин да се добие увид во планот за барање, на кој начин моторот го извршува барањето: EXPLAIN изјави.

За да пристапите до планот за пребарување за барањето SELECT, извршете го следново:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

Командата EXPLAIN му кажува на MySQL да го изврши барањето SELECT, но наместо да ги враќа резултатите, ќе покаже информации за тоа како моторот на базата на податоци го извршил барањето внатрешно.

Планот за извршување ќе биде сличен на следниот (вашата табела може малку да се разликува):

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Во оваа табела, колоните опишуваат многу аспекти на извршувањето на барањето. Во зависност од вашата верзија на MySQL, вашиот излез може да содржи дополнителни колони, но за ова упатство, еве ги најважните информации:

• possible_keys ги наведува индексите што MySQL ги сметал за употреба. Во овој случај, нема (NULL).
• клучот го опишува индексот што MySQL одлучи да го користи при извршувањето на барањето. Во овој случај, не се користеше индекс (NULL).
• редовите го покажува бројот на редови што MySQL мораше да ги анализира поединечно пред да ги врати резултатите. Овде, тоа е 20, што одговара на бројот на сите можни редови во табелата. Ова значи дека MySQL мораше да го скенира секој ред во табелата вработени за да го најде вратениот единствен.
• Extra прикажува дополнителни, описни информации за планот за пребарување. Во овој пример, прибелешката Using where значи дека базата на податоци филтрирала резултати директно од табелата користејќи ја изјавата WHERE.

Без индекси, базата на податоци мораше да скенира 20 редови за да добие еден единствен. Ако табелата содржеше милиони редови, MySQL ќе мораше да помине низ нив еден по еден, што резултираше со слаби перформанси на барањето.

Забелешка: поновите верзии на MySQL, кога се користи EXPLAIN, се прикажува 1 ред во комплет, 1 предупредување на излезот, додека постарите верзии на MySQL и базите на податоци компатибилни со MySQL честопати едноставно ќе прикажуваат 1 ред во множеството. Предупредувањето не е знак за проблем. MySQL го користи својот механизам за предупредување за да обезбеди дополнителни проширени информации за планот за пребарување. Оваа употреба на овие дополнителни информации е надвор од опсегот на ова упатство. Можете да дознаете повеќе за тоа однесување на страницата Extended EXPLAIN Output Format во документацијата MySQL.

Барањето SELECT што штотуку го извршивте го користеше точниот услов за барање, WHERE плата=100000. Следно, да провериме дали базата на податоци ќе се однесува слично со услов за споредба. Обидете се да ги вратите вработените со плата помала од 70000:

SELECT * FROM employees WHERE salary < 70000;

Овој пат, базата на податоци врати два реда за John Smith и Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

Меѓутоа, кога користите EXPLAIN за да го разберете извршувањето на барањето на следниов начин:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

Ќе забележите дека табелата е речиси идентична со претходното барање:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Како и со претходното барање, MySQL ги скенира сите редови 20 во табелата за да ги најде оние што сте ги побарале преку клаузулата WHERE на барањето. Иако бројот на вратени редови е мал во споредба со бројот на сите редови во табелата, моторот на базата на податоци треба да изврши многу работа за да ги пронајде.

За да го поправите тоа, можете да креирате индекс за колоната плата, која ќе му каже на MySQL да одржува дополнителна, високо оптимизирана структура на податоци, особено за податоците плата од <вработени табела. За да го направите тоа, извршете го следново барање:

CREATE INDEX salary ON employees(salary);

Синтаксата на изјавата CREATE INDEX бара:

• Името на индексот, кое во овој случај е плата. Името мора да биде единствено во една табела, но може да се повторува низ различни табели во истата база на податоци.
• Името на табелата за кое е креиран индексот. Во овој случај, се работи за вработени.
• Списокот на колони за кои е креиран индексот. Овде, користите една колона наречена плата за да го изградите индексот.

Забелешка: во зависност од дозволите на корисникот MySQL, може да добиете грешка при извршувањето на командата CREATE INDEX: ERROR 1142 (42000): командата INDEX е одбиена на корисникот @host за вработени во маса. За да му дадете дозволи на INDEX на вашиот корисник, најавете се на MySQL како root и извршете ги следните команди, заменувајќи ги корисничкото име и домаќинот MySQL по потреба:

GRANT INDEX on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

По ажурирањето на корисничките дозволи, одјавете се како root и повторно најавете се како корисник и повторно извршете ја изјавата CREATE INDEX.

Базата на податоци ќе потврди дека индексот е успешно креиран:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Со поставениот индекс, обидете се да ги повторите претходните прашања за да проверите дали нешто се променило. Започнете со враќање на самохраниот вработен со точно 100000 плата:

SELECT * FROM employees WHERE salary = 100000;

Резултатот ќе биде ист како и претходно, со вратен само Џејмс Браун:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Меѓутоа, барањето MySQL да објасни како му пристапи на барањето ќе покаже некои разлики од претходно. Извршете го барањето EXPLAIN на следниов начин:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

Овој пат, излезот ќе се отпечати вака:

Output
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | salary        | salary | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL изјавува дека од еден можен клуч прикажан во possible_keys, одлучи да го користи клучот со име плата, што е индексот што го создадовте. Колоната редови сега покажува 1 наместо 20. Бидејќи го користеше индексот, базата на податоци избегна да ги скенира сите редови во базата и можеше веднаш да го врати единствениот баран ред. Во колоната Extra сега не се споменува Користење WHERE, бидејќи повторувањето низ главната табела и проверката на секој ред во однос на условот за барање не беше неопходно за да се изврши барањето.

Со мала база на податоци, влијанието од користењето на индексот не е многу забележливо. Но, на базата на податоци и требаше многу помалку работа за да се поврати резултатот и ефектот од оваа промена ќе биде значаен за поголема база на податоци.

Обидете се да го повторите второто барање, да ги вратите вработените со плата помала од 70000, за да проверите дали индексот ќе се користи и таму.

Извршете го следното барање:

SELECT * FROM employees WHERE salary < 70000;

Ќе се вратат истите два реда за John Smith и Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

Меѓутоа, кога користите EXPLAIN на следниов начин:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

Табелата ќе се разликува од претходното извршување на истото барање:

Output
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | salary        | salary | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Колоната key ви кажува дека MySQL го користел индексот за да го изврши барањето. Во редови, беа анализирани само два реда за да се врати резултатот. Овој пат, колоната Extra вели Using index condition, што значи дека во конкретниот случај, MySQL се филтрирал со користење на индексот и потоа ја користел главната табела само за да ги врати веќе усогласени редови.

Забелешка: Понекогаш, иако индексот е присутен и може да се користи, MySQL ќе одлучи против него. На пример, ако извршите:

EXPLAIN SELECT * FROM employees WHERE salary < 140000;

Планот за извршување ќе гласи вака:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | salary        | NULL | NULL    | NULL |   20 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Иако плата е наведена во possible_keys, празната колона key (што чита NULL) значи дека MySQL одлучи да не користете го индексот, што може да се потврди со скенираните редови 20. Планерот за прашања на базата на податоци го анализира секое барање според можните индекси за да ја одреди најбрзата патека на извршување. Ако трошоците за пристап до индексот ги надминуваат придобивките од неговото користење (на пример, ако барањето врати значителен дел од оригиналните податоци од табелата), базата на податоци може да одлучи дека е всушност побрзо да се направи целосна скенирање на табелата наместо тоа.

Слично на тоа, прибелешките во колоната Extra, како што се Користење на состојбата на индексот или Користење каде, опишуваат како моторот на базата на податоци го извршил барањето подетално . Во зависност од контекстот, базата на податоци може да избере друг начин за извршување на барањето и може да имате излези со прибелешката Користење на состојбата на индексот што недостасува или присутна е друга прибелешка. Ова не значи дека индексот не се користи правилно, туку само дека базата на податоци одлучила дека различен начин на пристап до редови ќе биде попрофесионален.

Во овој дел, креиравте и користевте индекси со една колона за да ги подобрите перформансите на барањата SELECT кои се потпираат на филтрирање во однос на една колона. Во следниот дел, ќе истражите како може да се користат индексите за да се гарантира единственоста на вредностите во дадена колона.

Користење на единствени индекси за да се спречи дуплирање на податоците

Како што истражувавте во последниот дел, една вообичаена употреба на индексите е поефикасно враќање на податоците со помагање на моторот на базата на податоци да направи помалку работа за да го постигне истиот резултат. Друга цел е да се осигура дека податоците во делот од табелата на кој е дефиниран индексот нема да се повторуваат. Ова е она што го прави единствениот индекс.

Избегнувањето дупликат вредности често е неопходно за да се гарантира интегритетот на податоците, било од логичка или од техничка гледна точка. На пример, не треба да има две различни луѓе кои користат ист број за социјално осигурување, или онлајн систем не треба да дозволува повеќе корисници со исто корисничко име или адреса на е-пошта да се регистрираат.

Во случајот со примерот на табелата вработени во ова упатство, серискиот број на доделениот уред е поле што не треба да содржи дупликати. Ако се случи, тоа би значело дека на двајца вработени им бил даден ист компјутер. Во овој момент, сепак, можете лесно да вметнете нови вработени со повторени сериски броеви.

Обидете се да вметнете друг вработен со сериски број на уред кој веќе се користи:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

Базата на податоци ќе го обврзе и вметне редот, известувајќи ве за успехот:

Output
Query OK, 1 row affected (0.009 sec)

Меѓутоа, ако сега ја побарате базата на податоци за вработени кои користат компјутер ABCD123 вака:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Како резултат ќе добиете две различни луѓе:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|          21 | Sammy      | Smith     | ABC123        |  65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)

Ова не е очекувано однесување за да се задржи валидна базата на податоци вработени. Ајде да ја вратиме оваа промена со бришење на новосоздадениот ред:

DELETE FROM employees WHERE employee_id = 21;

Тоа може да го потврдите со повторно извршување на претходното барање SELECT:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Уште еднаш, само Џон Смит го користи уредот со сериски број ABC123:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

За да ја заштитите базата на податоци од такви грешки, можете да креирате единствен индекс на колоната device_serial.

За да го направите тоа, извршете:

CREATE UNIQUE INDEX device_serial ON employees(device_serial);

Додавањето на клучниот збор UNIQUE при креирањето на индексот ја наложува базата на податоци да се осигура дека вредностите во колоната device_serial не може да се повторуваат. Со единствени индекси, сите нови редови додадени на табелата ќе се проверат во однос на индексот за да се утврди дали вредноста на колоната го задоволува ограничувањето.

Базата на податоци ќе го потврди создавањето на индексот:

Output
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

Сега, проверете дали додавањето дупликат запис во табелата сè уште е можно. Обидете се повторно да го извршите претходното успешно барање INSERT:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

Овој пат ќе се прикаже пораката за грешка:

Output
ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'

Можете да потврдите дека новиот ред не е додаден на табелата со повторно користење на барањето SELECT:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Сега е вратен еден ред:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Уникатните индекси, покрај заштитата од дупликат записи, се и целосно функционални индекси за забрзување на барањата. Моторот на базата на податоци ќе користи уникатни индекси на ист начин како и во претходниот чекор. Можете да го потврдите ова со извршување на:

EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';

Планот за извршување ќе биде сличен на следниот (вашата табела може малку да се разликува):

Output
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Индексот device_serial се прикажува и во possible_keys и во колоната клуч, со што се потврдува дека индексот бил користен при извршувањето на барањето.

Сте користеле уникатни индекси за да се заштитите од дупликат податоци во базата на податоци. Во следниот дел, ќе користите индекси кои опфаќаат повеќе од една колона.

Користење на индекси на повеќе колони

Досега, сите индекси што ги креиравте во претходните делови беа дефинирани користејќи име на една колона, што се однесуваше на вредностите од избраната колона. Повеќето системи за бази на податоци поддржуваат индекси кои опфаќаат повеќе од една колона. Таквите индекси, наречени индекси со повеќе колони, обезбедуваат начин за складирање на вредности за повеќе колони во еден индекс, овозможувајќи му на моторот на базата на податоци побрзо и поефикасно да ги извршува барањата користејќи го збирот на колони заедно.

Често користените прашања кои треба да се оптимизираат за перформанси често користат повеќе услови во клаузулата за филтрирање WHERE. Пример за вакво барање би било да се побара од базата на податоци да најде лице и по име и по презиме:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

Првата мисла да се оптимизира ова барање со индекси може да биде да се создадат два поединечни индекси, еден на колоната last_name и друг на колоната first_name. Сепак, ова не е најдобриот избор за оваа ситуација.

Ако креирате два одделни индекси на овој начин, MySQL ќе знае како да ги пронајде сите вработени со име Smith. Исто така, ќе знае како да ги најде сите вработени по име John. Сепак, не би знаел како да најде луѓе по име Џон Смит.

За да го илустрирате проблемот со тоа што имате два поединечни индекси, замислете да имате два посебни именици, еден подреден по презимиња, а друг по имиња. И двата именици наликуваат на индекси создадени на колоните last_name и first_name соодветно. Како корисник на телефонски именик, можете да му пристапите на проблемот со наоѓање Џон Смит на три можни начини:

• Користете го телефонскиот именик подреден по презимиња за да ги најдете сите луѓе по име Smith, игнорирајте го вториот телефонски именик и рачно преминете ги сите луѓе Smith еден по еден додека не ги најдете Џон Смит.
• Направете го спротивното: користете го телефонскиот именик подреден по имиња за да ги најдете сите луѓе по име John, игнорирајте го вториот телефонски именик и рачно поминете ги сите луѓе John еден по еден додека не го најдете Џон Смит.
• Обидете се да ги користите двата именици заедно: пронајдете ги сите луѓе по име John и одделно сите луѓе по име Smith, запишете ги привремените резултати и обидете се рачно да ги пресечете двете подмножества на податоци кои бараат луѓе кои се на двете поединечни списоци.

Ниту еден од овие пристапи не е идеален, а MySQL има слични избори на располагање кога се работи со повеќе разделени индекси и барање кое бара повеќе од една состојба за филтрирање.

Друг пристап би бил да се користат индекси кои земаат предвид не една колона, туку многу колони. Можете да го замислите ова како телефонски именик сместен во друг именик: прво го барате презимето Smith и ве води до вториот каталог за сите луѓе по име Smith организирани по азбучен ред по имиња, кои можете да ги користите за брзо наоѓање John.

Забелешка: Често се вели дека MySQL може да користи само еден индекс за секоја табела што се користи во барањето. Тоа не е секогаш точно, бидејќи MySQL поддржува оптимизации за спојување на индекси за заеднички да користи повеќе индекси при извршување на барањето. Сепак, ова ограничување е добро правило при градење на индекси. MySQL може да одлучи да не користи повеќе индекси; дури и ако тоа е така, во многу сценарија, тие нема да служат за целта како и посветен индекс.

Во MySQL, за да креирате индекс со повеќе колони за презимиња и имиња во табелата вработени, извршете:

CREATE INDEX names ON employees(last_name, first_name);

Во овој случај, изјавата CREATE INDEX малку се разликува. Сега во заградите по името на табелата (вработени), се наведени две колони: last_name и потоа first_name. Ова создава индекс со повеќе колони на двете колони. Редоследот по кој се наведени колоните во дефиницијата на индексот е важен, како што ќе откриете во еден момент.

Базата на податоци ќе ја прикаже следнава порака што потврдува дека го создала индексот успешно:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Сега, обидете се да го поставите барањето SELECT за да најдете редови со името што се совпаѓа со John и презимето што одговара на Smith:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

Резултатот е еден ред со вработен по име Џон Смит:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Сега користете го барањето EXPLAIN за да проверите дали индексот бил користен:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

Планот за извршување ќе биде сличен на следниот (вашата табела може малку да се разликува):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Базата на податоци го користеше индексот names. Беше скениран еден ред, така што табелата не беше помината повеќе од потребното. Колоната Extra вели Користење на состојбата на индексот, што значи дека MySQL може да го заврши филтрирањето исклучиво користејќи го индексот.

Филтрирањето според имињата и презимињата со користење на индексот со повеќе колони што ги опфаќа тие две колони и обезбедува на базата на податоци директен, брз начин за наоѓање на саканите резултати.

Со индексот дефиниран на двете колони, што ќе се случи ако се обидете да ги најдете сите вработени со име Smith, но не филтрирате според името? Извршете го изменетото барање:

SELECT * FROM employees WHERE last_name = 'Smith';

Излезот ќе го врати следново:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|          20 | Abigail    | Smith     | FGH890        | 155000 |
|          17 | Daniel     | Smith     | WXY901        | 140000 |
|           1 | John       | Smith     | ABC123        |  60000 |
|           5 | Michael    | Smith     | MNO345        |  80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)

Четворица вработени се презиме Smith.

Уште еднаш, пристапете до планот за извршување на барањето:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

Планот за извршување ќе биде сличен на следниот (вашата табела може малку да се разликува):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 203     | const |    4 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Овој пат се вратени четири реда, бидејќи има повеќе од еден вработен со тоа презиме. Сепак, табелата со план за извршување покажува дека базата на податоци го користела индексот на повеќе колони име за да го изврши ова барање, скенирање само 4 редови - вратениот точен број.

Во претходните прашања, колоната што се користеше за филтрирање на резултатите (last_name) беше пренесена прва во изјавата CREATE INDEX. Сега ќе ја филтрирате табелата вработени според first_name, која беше втората колона во списокот со колони за овој индекс со повеќе колони. Извршете го следното барање:

SELECT * FROM employees WHERE first_name = 'John';

Излезот ќе се врати на следниов начин:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Пристапете до планот за извршување на барањето:

EXPLAIN SELECT * FROM employees WHERE first_name = 'John';

Излезот ќе се врати на следниов начин:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Повторно, вратените резултати содржат еден вработен, но овојпат не е користен индекс. Базата на податоци ја скенира целата табела, илустрирана со прибелешката Using where во колоната Extra, како и скенираните редови 20.

Во овој случај, базата на податоци не го користела индексот поради редоследот на колоните предадени на изјавата CREATE INDEX кога индексот првпат бил креиран: last_name, first_name. Базата на податоци може да го користи индексот само ако барањето ја користи или првата колона или и првата и втората колона; не може да поддржува прашања во однос на индексот каде што не се користи првата колона од дефиницијата на индексот.

Со индекс создаден на повеќе колони, базата на податоци може да го користи индексот за да ги забрза барањата што ги вклучуваат сите индексирани колони или растечкиот префикс од левата страна на сите индексирани колони. На пример, индексот со повеќе колони што вклучува колони (a, b, c) може да се користи за да се забрзаат барањата што ги вклучуваат сите три колони и прашањата што ги вклучуваат само првите две колони, па дури и прашања кои ја вклучуваат само првата колона. Од друга страна, индексот нема да помогне со прашања кои ја вклучуваат само последната колона, c или последните две колони, b и c.

Со внимателно избирање на колоните вклучени во индексот и нивниот редослед, може да се користи еден индекс со повеќе колони за да се забрзаат различни прашања на истата табела. Во овој пример, ако претпоставиме дека барањето на вработените се случува и по име и презиме или само по презиме, дадениот редослед на колони во индексот names гарантира дека индексот ќе ги забрза сите релевантни прашања.

Во овој дел, користевте индекси со повеќе колони и научивте за редоследот на колоните кога одредувате таков индекс. Во следниот дел, ќе научите како да управувате со постоечките индекси.

Наведување и отстранување на постојните индекси

Во претходните делови, креиравте нови индекси. Бидејќи индексите имаат имиња и се дефинирани на одредени табели, можете исто така да ги наведете и да манипулирате со нив кога е потребно.

За да ги наведете сите индекси што сте ги создале во ова упатство за табелата вработени, извршете ја следнава изјава:

SHOW INDEXES FROM employees;

Излезот ќе биде сличен на следново:

Output
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | device_serial |            1 | device_serial | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | salary        |            1 | salary        | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            1 | last_name     | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            2 | first_name    | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

Во зависност од вашата верзија на MySQL, вашиот излез може малку да се разликува, но ќе ги вклучи сите индекси, вклучувајќи ги нивните имиња, колони што се користат за дефинирање на индексот, информации за неговата единственост и други опширни детали за дефиницијата на индексот.

За да ги избришете постоечките индекси, можете да ја користите изјавата DROP INDEX SQL. Замислете дека повеќе не сакате да наметнувате уникатност на колоната device_serial. Така, индексот device_serial повеќе нема да биде потребен. Извршете ја следнава команда:

DROP INDEX device_serial ON employees;

device_serial е името на индексот, а вработените е табелата на која е дефиниран индексот. Базата на податоци ќе го потврди бришењето на индексот:

Output
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

Понекогаш шемите на типични барања ќе се променат со текот на времето или ќе станат истакнати нови типови на прашања. Потоа, можеби ќе треба повторно да ги процените користените индекси, да креирате нови или да ги избришете неискористените за да избегнете понижување на перформансите на базата на податоци со тоа што ќе ги ажурирате.

Користејќи ги командите DROP INDEX, можете да управувате со индекси на постоечка база на податоци, следејќи ги најдобрите практики за создавање индекси кога ќе станат потребни и корисни.

Заклучок

Следејќи го ова упатство, научивте што се индекси и како да ги користите најчестите типови во MySQL за да го забрзате преземањето податоци преку условни барања SELECT. Користевте индекси за да ја одржите уникатноста на податоците на колоните и научивте како индексите можат да влијаат на прашањата каде што се користат повеќе од една колона во условите за филтрирање.

Можете да користите индекси за да ги обликувате перформансите на базата според типот на прашања кои најчесто се извршуваат, постигнувајќи ја вистинската рамнотежа помеѓу перформансите за читање и пишување за случаите за вообичаена употреба. Овој туторијал ги опфати само основите за користење на индекси за таа цел. Можете да поддржувате посложени барања преку индекси со разбирање како MySQL избира кои индекси да ги користи и кога да ги користи. За да дознаете повеќе за тоа, погледнете ја MySQL документацијата за индекси.

Ако сакате да дознаете повеќе за различните концепти околу јазикот SQL и работата со него, ве охрабруваме да ги погледнете другите водичи од серијата Како да се користи SQL.