Что такое индекс mysql и как их использовать

Можете привести понятный пример индекса в таблицах? В документациях столько всего написано, это вводит в заблуждение. Индекс – это и есть первичный или внешний ключ?

705 2 2 золотых знака 9 9 серебряных знаков 20 20 бронзовых знаков

индекс это структура данных, обеспечивающая быстрый поиск. ключи реализуются на основе индексов, т.к. для проверки данных по правилам накладываемым ключем как раз нужен поиск

И вы не пробовали в гугле забить ваш вопрос. первая же ссылка на википедию ru.wikipedia.org/wiki/…

3 ответа 3

Если в кратце, то индекс, это поле по которому оптимизирован(ускорен) поиск.

Поскольку индекс занимает место, то индексировать нужно только те поля, по которым происходит выборка.

Допустим есть таблица.

CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP ) 

Допустим вам нужен поиск по имени (firstname).

SELECT * FROM MyGuests WHERE firstname = "Вася" 

тогда есть смысл добавить индекс по данному полю.

CREATE INDEX firstname_index ON MyGuests (firstname) USING BTREE; 

Будет созданна “карта” которая позволет легко находить записи в оригинальном списке.

3,151 1 1 золотой знак 13 13 серебряных знаков 19 19 бронзовых знаков
как добавляется и куда этот индекс, и как с помощью него сделать поиск этот по полю

а что такое BTREE ? и что далее происходит после этого CREATE INDEX firstname_index ON MyGuests (firstname) USING BTREE; ?

Вкратце, индексы создаются для повышения производительности поиска данных. Таблицы могут иметь огромное количество строк, которые хранятся в произвольном порядке. Без индекса поиск нужных строк идёт по порядку (последовательно), что на больших объемах данных отнимает много времени.

Индекс – обычно один или несколько столбцов таблицы и указателей на соответствующие строки таблицы, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск — например, в MySQL b-дерева. Индекс лучше использовать на тех столбцах таблицы, на которые вы чаще всего будете накладывать условия через where column_name = .

Индекс создаётся по правилу:

create index название_индекса on название_таблицы (название_столбца) 

Например, у вас таблица называется test , где хранятся данные по городам России с улицами вида Город, Улица, Дом. Понятно, что строк в таблице при таком раскладе будет много. Если вы часто делаете выборку по определенному городу, например:

select * from test where city = 'Омск' 

то, чтобы этот запрос отработал быстрее обычного, следует добавить индекс по вышеуказанному правилу:

create index city_index on test (city) 

Тогда тот же самый запрос

select * from test where city = 'Омск' 

отработает гораздо быстрее, если столбец city будет проиндексирован.

8,900 11 11 золотых знаков 31 31 серебряный знак 55 55 бронзовых знаков

@Sven нет, внешний ключ – это столбец для соединения с другими таблицами. Индекс – столбец, по которому можно задать условие и тогда запрос отработает гораздо быстрее.

ага, т. е это комманда create index city_index on test (city) просто чтобы упростить компу сделать поиск этот select * from test where city = ‘Омск’ и всё собственно?

Это create index city_index on test (city) можно(нужно) писать, если много в базе значений, а если до 500 например, то никто этот индекс и не создаёт таким путём create index city_index.

На пальцах можно объяснить так:

Когда Вы создаёте таблицу, добавляете в неё данные, то таблица разрастается и она выглядит как просто последовательный список, упорядоченный по тому как в неё данные добавлялись.

Когда данных мало, список маленький и все запросы к ней выполняются, почти, незаметно. Но когда количество записей в таблице начинает переваливать за миллион (в разных случаях по разном, но как пример миллион), то у Вас поиск уже идёт не так быстро и с добавлением всё новых и новых записей – ещё медленнее.

Это связано с тем, что когда Вы ищите какую-то запись, то просматриваются все записи, пока не дойдут до нужной.

Когда Вам это окончательно надоедает и Вы хотите что-нибудь сделать, то к Вам на помощь приходят индексы.

Индекс создаётся по какому-то определённому полю (можно по нескольким) по которому, обычно, выполняется поиск. Когда Вы создаёте индекс, то MySql (и любая другая БД) обходит все записи в таблице и строит дерево (скорее всего B-дерево или разновидность), в котором ключами выступает выбранное поле, а содержимым ссылки на записи в таблице.

И когда Вы делаете очередной свой select запрос по таблице, по полю для которого создали индекс MySql (и любая другая БД) знает что у неё есть индекс, по которому пройтись будет быстрее, нежели перебирать все записи и Ваш запрос будет направлен этому индексу и записи, удовлетворяющие условию, будут найдены гораздо быстрее, так как поиск по построенному дереву будет гораздо быстрее, нежели простой перебор всех записей.

Просто про складне: індекси в MySQL

Потрібно швидко знайти певну інформацію у базі даних? З’явилися проблеми з продуктивністю, база почала гальмувати? Знайомтеся з індексами в MySQL.

Чому MySQL?

MySQL — це система управління базами даних (СУБД), в тому числі реляційними. Вона має відкритий вихідний код та підтримується Oracle, тож ви можете використовувати MySQL безплатно. Щоб отримувати послуги підтримки преміумкласу, ви можете купити комерційну ліцензію в Oracle. Непогано, як для СУБД. Що ще?

MySQL досить легко освоїти в порівнянні з іншими системами, типу Oracle Database або Microsoft SQL Server. Також, MySQL може працювати на різних платформах: UNIX, Linux, Windows тощо. Ви можете встановити цю СУБД на сервері або навіть на десктопі.

MySQL — надійне і швидке ПЗ. Якщо ви розробляєте вебсайти або вебдодатки, MySQL буде оптимальним вибором. Ще ця СУБД — важливий компонент стека LAMP/LEMP, який включає Linux, Apache (Nginx), MySQL та PHP.

Індекси в MySQL

Знати про індекси необхідно і системному адміністратору, і DevOps-інженеру, щоб швидко знаходити дані в базі.

* Індекси — це специфічні об’єкти бази даних, що дозволяють значно підвищити швидкість пошуку значень з таблиць БД.

* MySQL index — структура, в якій зберігаються значення одного або кількох стовпчиків таблиці та посилання на рядки, де ці значення розташовані.

Приклад. У вас є таблиця, де 10 тисяч рядків і колонка з датами. Вам потрібно швидко знайти якусь дату. Для цього доведеться кожну колонку порівнювати з тою, що ви шукаєте, щоб знайти збіги. Але це занадто довго!

Щоб зберегти час, створіть індекс для цієї колонки, який розташує дати послідовно. У такому випадку за допомогою пошуку ви відразу ж знайдете всі значення, що збігаються. Оскільки вони відсортовані, не потрібно буде перечитувати всю таблицю.

Індекси можуть бути різні, під різні типи даних. Також вони можуть бути складеними з декількох колонок. Загальний сенс їх існування один — впорядкувати дані для швидкого пошуку, сортування, порівняння тощо.

Може здатися, що зробити індекси на всі колонки, щоб база працювала швидше — гарна ідея. Але це не так. Перебудова індексів після зміни даних — ресурсомістка операція. Важливо використовувати індекси лише за необхідністю. Вони ще і місце в базі займають.

Навіщо це потрібно знати сисадміну?

Інколи індекси допомагають розв’язувати проблеми продуктивності. Наприклад, якщо база гальмує. Включайте slow-log і дивіться всі запити, які виконуються довше секунди. Якщо бачите, що конкретний запит постійно гальмує — прискорте його за допомогою відповідного індексу.

Звісно, вам не завжди вдасться знайти необхідне рішення за допомогою індексів. Але їх розуміння буде перевагою для тих, хто працює з базами даних.

А чим ви користуєтеся: Oracle Database, Microsoft SQL Server чи MySQL? Пишіть у коментарях.

click fraud protection

Індекс, інакше відомий як індекси, відноситься до структури даних, яка підвищує швидкість отримання даних у таблиці MySQL. Якщо таблиця не має індексу, виконання запиту пропонує MySQL відсканувати всі рядки, поки не знайде запитувану інформацію. Якщо таблиця містить багато записів, цей процес займе багато часу, щоб завершити, що суттєво вплине на продуктивність бази даних.

Цей підручник покаже вам, як створити індекс у таблиці MySQL як для існуючих, так і для нових таблиць.

Перш ніж ми зможемо виконувати запити та створювати індекси, давайте розглянемо кілька концепцій індексу MySQL.

Типи індексів MySQL

MySQL підтримує два типи індексів:

1. Первинний або кластерний індекс
2. Вторинний індекс

MySQL автоматично створює індекс, який називається PRIMARY, щоразу, коли ми створюємо таблицю з PRIMARY KEY. Залежно від механізму баз даних, якщо первинний ключ або унікальний ключ недоступні в таблиці, MySQL може створити прихований ключ у стовпці зі значеннями ідентифікатора.

Основний індекс, створений MySQL, зберігається разом з даними в тій же таблиці. Інші індекси, які існують у таблиці, окрім ПРОМИРНОГО індексу, відомі як вторинні індекси.

Як додати індекс до наявної таблиці

Хоча MySQL рекомендує додавати індекс під час створення таблиці, є деякі випадки, коли вам може знадобитися додати індекс до наявної таблиці, наприклад, у стовпці з регулярним доступом.

Щоб додати індекс до існуючої таблиці, ми можемо використовувати запит ALTER.

Давайте візьмемо зразок бази даних з таблицями, як показано в наведеному нижче запиті:

СТВОРИТИ БАЗА ДАННИХ ІФ НІ ІСНУЄ mysql_indices ;
ВИКОРИСТОВУВАТИ mysql_indices ;
СТВОРИТИ ТАБЛИЦЯ походи (
id INT AUTO_INCREMENT ОСНОВНИЙ КЛЮЧ ,
cpt_name
ВАРЧАР ( 255 ) НІ НУЛЬ ,
корабель
ВАРЧАР ( 255 ) НІ НУЛЬ
) ;
ВСТАВИТИ INTO походи ( cpt_name , корабель )
ЦІННОСТІ ( “Керол Фрімен” , “USS Cerritos” ) ,
( “Крістофер Пайк” , “USS Discovery” ) ,
( “Жан-Люк Пікар” , “USS Enterprise” ) ,
( ‘Джеймс Т. Кірк , “USS Enterprise” ) ,
( “Джонатан Арчер” , “USS Enterprise” ) ;

У наведеному вище прикладі MySQL створить ключ індексу, використовуючи стовпець id, оскільки він вказаний як ПЕРВИННИЙ КЛЮЧ.

Перевірити це можна за допомогою запиту:

Щоб створити власний індекс, використовуйте запит ALTER як:

У наведеному вище прикладі ми використовуємо ім’я cpt_name як другий первинний ключ. Щоб показати індекси, скористайтеся запитом:

Якщо це не вказано, MySQL буде використовувати будь-який індекс за замовчуванням як B-TREE. Інші підтримувані типи індексів включають HASH та FULLTEXT.

Тип індексу буде залежати від механізму зберігання для вказаної таблиці.

Щоб створити індекс для стовпця або списку стовпців, ми використовуємо запит CREATE INDEX. Наприклад, щоб створити індекс для стовпця “корабель:”, ми можемо зробити:

Якщо ми виконаємо запит SHOW INDEX у таблиці, ми побачимо індекс під назвою “ship_index” у стовпці “корабель”.

Як додати індекс до нової таблиці

Рекомендований спосіб – створити індекс або індекси під час створення таблиці. Для цього в запиті INDEX () вкажіть стовпці для використання як індекси.

Почнемо, опустивши таблицю походів у попередньому прикладі:

Далі відтворимо таблицю та вкажемо ІНДЕКС під час створення. Приклад запиту:

СТВОРИТИ ТАБЛИЦЯ походи (
id INT AUTO_INCREMENT ОСНОВНИЙ КЛЮЧ ,
cpt_name
ВАРЧАР ( 255 ) НІ НУЛЬ ,
корабель
ВАРЧАР ( 255 ) НІ НУЛЬ ,
ІНДЕКС ( корабель )
) ;
ВСТАВИТИ INTO походи ( cpt_name , корабель )
ЦІННОСТІ ( “Керол Фрімен” , “USS Cerritos” ) ,
( “Крістофер Пайк” , “USS Discovery” ) ,
( “Жан-Люк Пікар” , “USS Enterprise” ) ,
( ‘Джеймс Т. Кірк , “USS Enterprise” ) ,
( “Джонатан Арчер” , “USS Enterprise” ) ;

У наведеному вище прикладі запиту ми додаємо стовпець корабля як індекс під час створення таблиці. Оскільки ідентифікатор є ОСНОВНИМ КЛЮЧОМ, MySQL автоматично додає його як індекс.

Висновок

У цьому посібнику ви дізналися, як додати індекс до наявної таблиці, додати індекс для стовпця та створити індекс під час створення таблиці.