Как проиндексировать таблицу mysql по полю

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

А между тем, одна только индексация таблиц порой поднимает производительность сайта в несколько раз.

Что же такое — индексация? Попробую объяснить на примере.

Вспомните любую публичную библиотеку. Пусть детскую или даже школьную. Помните зал со стеллажами книг? И даже если вы были очень давно в библиотеке, вы прекрасно знаете, что все книги в этом уважаемом заведении расставлены не абы как, не в порядке их поступления в библиотеку (как поступают данные в базу), а по каким-то правилам. Обычно, книги разносят по темам, авторам и по алфавиту.

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

Предположим, вы в библиотеке ищите какую-то книгу.

Если вы начнете тупо перебирать все книги в библиотеке, то у вас на это уйдет не один день или даже не один месяц, если это крупная библиотека.

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

Так какого же хрена, прошу прощения за резкость, мы заставляем наши компьютеры искать необходимые нам данные методом тупого перебора? Только потому, что они это делают быстро?

Да, быстро. Но это если поиск надо произвести в тысячах записей. А если речь идет о миллионах? Или вы думаете, что ваша база данных не так велика, чтобы заниматься ее оптимизацией? Ошибаетесь, дорогие мои. Как только к вашей базе обратятся сотни человек, так ваши тысячи записей тут же превратятся для движка баз данных в миллионы! И ваш провайдер совершенно резонно сделает вам замечание.

Итак. В том, что базы данных надо индексировать — не сомневается ни один здравомыслящий программист. Правильно построенные индексы позволяют находить нужную информацию «в одно касание».

Как это происходит технически — нам знать не обязательно. Достаточно иметь в виду, что MySQL, как и любой другой движок баз данных, использует дополнительное место на диске для хранения индексных файлов. Это надо знать, только для того, чтобы не плодить ненужных индексов. Во всем надо знать меру. Даже в таком полезном деле, как индексация таблиц.

Так давайте сразу и определимся с тем, что нам надо индексировать.

А индексировать надо те поля таблицы, по которым происходит поиск или сортировка данных.

Например, у нас есть таблица books и таблица authors. В первой мы храним информацию о книгах, а во второй — информацию об их авторах.

Разумеется, самое логичное для подобной базы — искать в ней книги по названию и по автору.

Не имея индексации такой базы, примитивный поиск по первым буквам произведения вынудит компьютер просмотреть все записи в таблице, чтобы выдать полный результат. Если база большая, на это уйдет время.

Если же мы добавим в таблицу books индекс по полю book_name (название книги), то MySQL создаст индекс этой таблицы. То есть, отсортирует таблицу по указанному полю и расставит метки и ссылки на ячейки в реальной таблице.

Другими словами, индекс — это копия таблицы без данных, но отсортированная по определенным правилам, где каждая ячейка — есть ссылка на родительскую ячейку в основной таблице. Надеюсь, что выразился достаточно ясно.

Построив индекс по полю book_name, наш компьютер найдет по первым буквам нужные нам произведения практически мгновенно, ведь ему теперь не надо перебирать всю таблицу, а достаточно посмотреть на ту ее часть, где хранятся записи на нужную букву.

Еще проще говоря, если мы ищем книги на букву «М», то компьютер не станет перебирать записи, начинающиеся на другие буквы, прекрасно зная, что там нет записей, удовлетворяющих запросу.

И если в нашей книжной базе находится 100.000 книг, среди которых только 30 на букву Ж, то по запросу «найти все книги на букву Ж», компьютер переберет только 30 записей при наличии индекса, или переберет 100.000 записей при его отсутствии.

По-моему, польза очевидна.

Кстати, индексация текстовых полей — занятие чуть более сложное, чем индексация полей другого типа. Поясню. Цифровые, буквенные, булевы, поля дат, времени и другие — индексируются, как правило, без каких либо дополнительных размышлений.

Скажем, если в таблице книг есть поле book_date, хранящее дату публикации книги, то добавление индекса к такому полю будет выглядеть примерно так:

alter table tbl_books add index i_date (book_date);

Эта директива указывает MySQL создать индекс по полю book_date.

Теперь компьютеру не составит труда найти все книги 1993-го года или выстроить все найденные книги в порядке даты их публикации.

Точно так же можно создать индексы для других полей, по которым нам интересно производить поиск или сортировку.

Создание же индекса по текстовому полю осложняется только тем, что надо указать количество символов от начала записи, по которым надо построить индекс.

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

Например, если мы строим индекс по названиям улиц, то нам не надо индексировать их по всей длине, а можно предположить, что подавляющее большинство записей начнут различаться уже где-то на пятой букве.

А если мы индексируем коды шариковых подшипников, то индексировать лучше как можно больше символов, ибо большое количество изделий могут нести коды, отличающиеся только последними буквами или цифрами.

Иногда нам не надо специально строить индексы. Достаточно того, что MySQL сам построит индексы по полю, если при объявлении структуры таблицы мы зададим полю уникальность UNIQUE, говорящую о том, что данное поле не может хранить два одинаковых значения.

Другое ключевой слово, создающее индексы — KEY. Помните объявление primary key, которое я обязательно использую в каждой таблице для id-поля.

Если вы хотите увидеть, какие поля в таблице проиндексированы, дайте команду MySQL:

desc tbl_name;

и MySQL выведет всю информацию о таблице tbl_name, включая отметки об индексации полей.

Или более подробно, только об индексах:

show index from tbl_name;

Остается еще отметить, что в индексе может участвовать множество полей. Не обязательно одно.

Если в базе накладных чаще всего производится поиск по сумме с учетом диапазона дат, то логично создать индекс по этим двум полям: сумма и дата. Именно, создать один индекс по двум полям, а не два индекса по каждому полю!

alter table tbl_name add index i_name (field1, field2);

Поиск по уникальным индексам производится чуть быстрее, поэтому, если значение какого-то поля должно быть обязательно уникальным — не поленитесь отметить это в конструкции таблицы. MySQL отблагодарит вас скоростью своих ответов на ваши вопросы.

О синтаксисе создания индексов лучше всего написано тут: http://www.mysql.com/doc/A/L/ALTER_TABLE.html.

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

Если вы поставите это слово перед любым запросом к базе данных, MySQL не станет выводить вам результат запроса, а покажет подробную информацию о том, какими средствами пришлось воспользоваться и сколько операций пришлось произвести, чтобы получить ответ на ваш запрос.

Это волшебное слово explain позволит оценить эффективность любого запроса и отрегулировать все индексы вашей базы.

Удачной индексации, и да прибудет с вами порядок!

Индексирование JSON в MySQL

В середине 2015 года, в MySQL 5.7.8 появился тип данных JSON. С тех пор он применяется, чтобы избегать жёстких определений столбцов и сохранять документы JSON всех форм и размеров: логи аудита, параметры конфигурации, сторонние полезные нагрузки, пользовательские поля и др. Подробности — к старту нашего курса по анализу данных.

Хотя в MySQL есть функции чтения и записи данных JSON, вы быстро обнаружите, что явно не хватает прямого индексирования столбцов с JSON.

В других базах данных в качестве способа прямого индексирования столбца JSON обычно применяется обобщённый обратный индекс, или GIN-индекс (Generalized INverted index). В MySQL GIN-индексов нет, поэтому индексировать весь сохранённый документ JSON напрямую нельзя, зато возможно косвенно индексировать части JSON.

В зависимости от версии MySQL есть два варианта. В MySQL 5.7 нужно создавать промежуточный сгенерированный столбец, но с версии MySQL 8.0.13 можно создавать функциональный индекс напрямую.

Начнём с таблицы логирования действий в приложении:

CREATE TABLE `activity_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `properties` json NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) )

В эту таблицу вставим такой JSON:

В примере ключ электронной почты будем индексировать внутри объекта запроса, чтобы пользователи быстро находили формы, отправленные определёнными людьми.

Первый способ индексирования — сгенерированные столбцы.

Индексирование JSON с помощью сгенерированного столбца

Сгенерированным столбцом называют столбец, значения в котором — это результат вычислений, а не данные как есть. В выражении могут содержаться литеральные значения, встроенные функции или ссылки на другие столбцы. Его результат должен быть скалярным и детерминированным. Индексируя поле request.email в столбце свойств, извлекать значения в сгенерированном столбце будем оператором извлечения JSON без кавычек.

Чтобы проверить правильность сформированного выражения, сначала запустим оператор SELECT и посмотрим результаты:

mysql> SELECT properties->>"$.request.email" FROM activity_log; +--------------------------------+ | properties->>"$.request.email" | +--------------------------------+ | little.bobby@tables.com | +--------------------------------+

->> — это сокращённый оператор извлечения значений без кавычек, его эквивалент — JSON_UNQUOTE(JSON_EXTRACT(column, path)) . Если написать этот оператор SELECT в полной версии, результат будет тот же:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email")) -> FROM activity_log; +-----------------------------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email")) | +-----------------------------------------------------------+ | little.bobby@tables.com | +-----------------------------------------------------------+

После подтверждения корректности и точности выражения сгенерируем столбец с его помощью:

ALTER TABLE activity_log ADD COLUMN email VARCHAR(255) GENERATED ALWAYS as (properties->>"$.request.email");

Первая часть инструкции ALTER должна быть вам знакома, мы добавляем столбец email и определяем его как VARCHAR(255) . Во второй её половине объявляем, что столбец сгенерирован и всегда должен соответствовать результату выражения properties->>»$.request.email» .

Убедимся, что столбец добавлен, то есть выберем его:

mysql> SELECT id, email FROM activity_log; +----+-------------------------+ | id | email | +----+-------------------------+ | 1 | little.bobby@tables.com | +----+-------------------------+

Видно, что теперь этот столбец поддерживается MySQL. Если обновить значение JSON, обновится и значение сгенерированного столбца.

Теперь добавим сгенерированному столбцу индекс, как у любого другого столбца:

ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;

Вот и всё. Ключ request.email в столбце свойств JSON индексирован. Теперь проверим, что MySQL использует этот индекс для ускорения запросов фильтрации по электронной почте:

mysql> EXPLAIN SELECT * FROM activity_log -> WHERE properties->>"$.request.email" = 'little.bobby@tables.com'; *************************** 1. row *************************** id: 1 possible_keys: email key: email key_len: 768 [. ]: [. ]

MySQL сообщает, что для выполнения этого запроса планирует использовать индекс электронной почты.

Индексы сгенерированных столбцов и оптимизатор

Оптимизатор MySQL — мощная и загадочная сущность. Оператор MySQL сообщает, что нам нужно, но не как это получить. Часто MySQL слегка переписывает запрос, и это хорошо! На то, чтобы сделать эффективный оптимизатор, ушли тысячи часов и десятки лет.

С индексами в сгенерированных столбцах оптимизатор может «видеть» через различные шаблоны доступа, обеспечивая применение основного индекса.

Мы определили индекс email . Это сгенерированный столбец на основе выражения properties->>»$.request.email». И уже доказали, что индекс используется при выполнении запроса в столбце почты. Но вот что ещё интереснее: оптимизатор достаточно умён и выручает нас, когда мы забываем запросить столбец email !

Вместо того чтобы запрашивать сгенерированный столбец по имени, мы используем сокращённый оператор извлечения значений JSON. Ниже некоторые строки в EXPLAIN опущены для краткости:

mysql> EXPLAIN SELECT * FROM activity_log -> WHERE properties->>"$.request.email" = 'little.bobby@tables.com'; *************************** 1. row *************************** id: 1 possible_keys: email key: email key_len: 768 [. ]: [. ]

Хотя явного обращения к столбцу по имени не было, оптимизатор понимает, что в сгенерированном на основе того выражения столбце есть индекс, и предпочитает использовать его. Спасибо оптимизатору! Убедиться в этом можно и в полной версии:

mysql> EXPLAIN SELECT * from activity_log WHERE -> JSON_UNQUOTE( -> JSON_EXTRACT(properties, "$.request.email") -> ) = 'little.bobby@tables.com'; *************************** 1. row *************************** id: 1 possible_keys: email key: email key_len: 768 [. ]: [. ]

Опять же оптимизатор считывает выражение и использует индекс почты.

Хотите убедиться ещё раз? Взгляните на то, что происходит в оптимизаторе при запуске SHOW WARNINGS после прошлого оператора EXPLAIN , и на переписанный запрос:

mysql> SHOW WARNINGS; *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `activity_log`.`id` AS `id`,`activity_log`.`properties` AS `properties`,`activity_log`.`created_at` AS `created_at`,`activity_log`.`email` AS `email` from `activity_log` where (`activity_log`.`email` = 'little.bobby@tables.com')

Посмотрите внимательно: оптимизатор переписал запрос и изменил сравнение на равенство для ссылки на индексированный столбец. Это особенно полезно, если нет возможности контролировать шаблон доступа, потому что запрос выдаётся из стороннего пакета кодовой базы или вы не можете изменить эту часть кода по какой-то другой причине.

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

Функциональные индексы

С версии MySQL 8.0.13 промежуточный этап сгенерированного столбца можно пропускать и создавать «функциональные индексы». В документации MySQL они называются функциональными ключевыми частями.

Функциональный индекс — это индекс по выражению, а не столбцу. Он очень похож на сгенерированный столбец, так как реализован с помощью скрытого сгенерированного столбца! Генерируемый столбец создавать больше не нужно, но он создаётся.

В функциональных индексах есть ряд нюансов, особенно при их использовании для JSON.

Неплохо было бы создать индекс JSON вот так:

ALTER TABLE activity_log ADD INDEX email ((properties->>"$.request.email")) USING BTREE;

Но здесь вы получите неприятную ошибку:

Query 1 ERROR: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

Что же здесь происходит? В примерах выше мы сами создавали генерируемый столбец и объявляли его как VARCHAR(255) , легко индексируемый в MySQL.

А при использовании функционального индекса этот столбец создаётся на основе типа данных, выводимых в MySQL. В JSON_UNQUOTE возвращается неиндексируемое значение LONGTEXT .

К счастью, в сообщении об ошибке указывается правильное направление: нужно привести значение к типу, отличному от LONGTEXT . При приведении к типу с помощью функции CHAR в MySQL даётся указание вывести тип данных VARCHAR :

ALTER TABLE activity_log ADD INDEX email ((CAST(properties->>"$.request.email" as CHAR(255)))) USING BTREE;

Добавив индекс, запустим теперь EXPLAIN :

mysql> EXPLAIN SELECT * FROM activity_log -> WHERE properties->>"$.request.email" = 'little.bobby@tables.com'; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: activity_log partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where

К сожалению, индекс вообще не учитывается, так что проблемы ещё не ушли.

Если не указано иное, приведение значения к строке устанавливает сопоставление на utf8mb4_0900_ai_ci . С другой стороны, функции извлечения JSON возвращают строку с параметром сортировки utf8mb4_bin . В этом и проблема. Параметры сортировки между выражением запроса и сохранённым индексом не совпадают, поэтому новый функциональный индекс не используется.

Финальный этап состоит в том, чтобы явно задать сопоставление приведения к utf8mb4_bin :

ALTER TABLE activity_log ADD INDEX email (( CAST(properties->>"$.request.email" as CHAR(255)) COLLATE utf8mb4_bin )) USING BTREE;

Снова запускаем предыдущий EXPLAIN и видим, что наконец можно использовать функциональный индекс:

mysql> EXPLAIN SELECT * FROM activity_log -> WHERE properties->>"$.request.email" = 'little.bobby@tables.com'; *************************** 1. row *************************** id: 1 possible_keys: email key: email key_len: 1023 [. ]: [. ]

Очевидно, применение функциональных индексов сопряжено с рядом проблем, часть из которых явные и простые в отладке, а для решения других нужно чуть покопаться в документации.

И помните: под капотом у функциональных индексов — скрытые генерируемые столбцы. Хотите управлять генерируемыми столбцами сами (даже в версии MySQL 8.0.13 и новее)? Это вполне разумно.

Хотя прямое индексирование JSON может быть недоступно в MySQL, косвенным индексированием определённых ключей можно охватить большинство случаев использования.

Но не останавливайтесь только на JSON! Использовать генерируемые столбцы и функциональные индексы можно во всех типах распространённых, трудно индексируемых паттернах.

Поможем разобраться с SQL, чтобы вы прокачали карьеру или стали востребованным IT-специалистом:

Профессия Data Analyst (12 месяцев)
Профессия Data Scientist (24 месяца)

Краткий каталог курсов

Профессия Data Scientist
Профессия Data Analyst
Курс «Математика для Data Science»
Курс «Математика и Machine Learning для Data Science»
Курс по Data Engineering
Курс «Machine Learning и Deep Learning»
Курс по Machine Learning

Профессия на Python
Курс «Python для »
Профессия
Профессия

Профессия
Профессия

Профессия
Профессия на JAVA
Профессия C#-разработчик
Профессия Разработчик игр на Unity

Курс «Алгоритмы и структуры данных»
Профессия разработчик
Профессия «Белый хакер»

Курс по DevOps
Все курсы

Индексы в MySQL.

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

Часто программисты, проектируя базу данных, не задумываются об индексах MySQL, однако, последствия этого начинают ощущаться позднее, когда проект становится популярным и посещаемым. Сайт начинает притормаживать, а его владельцы полагают, что всему виной плохой хостинг или слабый сервер. В итоге затраты на «железо» растут, хотя все, что необходимо сделать – это провести оптимизацию базы данных.

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

Таким образом, индексы в MySQL – это определенная сортировка данных в таблице для ускорения поиска данных. Сортировка происходит на «низком» (машинном) уровне, и нам нет необходимости вникать в ее процессы. Все, что нам требуется – это указать MySQL, какие поля нужно индексировать.

Как определить, для каких полей нужно создавать индексы?

Индексы, прежде всего, нужно создавать по тем полям, которые часто попадают в условие «where» ваших sql-запросов.

Например, допустим, ваша таблица с товарами имеет следующую структуру:

Для формирования страниц, к примеру, вам часто приходится делать запросы для поиска товаров одной категории:

SELECT id, product_name FROM products WHERE cat_id = '5'

В этом случае для оптимизации запросов целесообразно создать индекс для поля cat_id. Первое поле – id всегда имеет уникальное значение и для него целесообразно создать «первичный ключ» (Primary Key).

Как создать индексы в базе данных MySQL.

Для создания индекса в MySQL можно воспользоваться интерфейсом phpMyAdmin. Напротив нужного поля в режиме редактирования структуры вы можете увидеть различные опции по добавлению индексов для поля.

Как создать индексы в базе данных MySQL

Выберите, к примеру, «добавить индекс» и он будет добавлен к выбранному полю.

Второй способ – это создать SQL-запрос по типу:

ALTER TABLE table_name ADD INDEX [index_name] (index_col_name. )

ALTER TABLE users ADD INDEX i_name (username);

Виды (типы) индексов в MySQL

Первичный ключ (PRIMARY KEY) – это основной ключ, который в таблице может быть только один. Он позволяет идентифицировать уникальные записи в таблице. Значения, которые находятся в столбце, где поля имеют PRIMARY KEY, не могут повторяться. Нередко первичный ключ назначают для полей с идентификатором id.

Уникальный ключ (UNIQUE) – по сути, это альтернатива первичному ключу: значения, которые содержатся в таких полях также не могут повторяться и иметь значение NULL.

Составной индекс – позволяет включать в индекс несколько полей, по которым часто происходит выборка. Например, если в условиях часто фигурирует два параметра:

SELECT username FROM users WHERE city = '5' AND age > '18'

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

Недостатки использования индексов:

1. За все приходится платить, не исключение и индексы в MySQL. В процессе индексирования MySQL создает дополнительные таблицы, в которых хранятся отсортированные данные. Однако, в большинстве случаев выгоды от быстродействия более ощутимы, чем издержки физической памяти.

2. Запросы на вставку данных в таблицу выполняются чуть дольше, посколько MySQL требуется создать новые индексы и реорганизовать данные в предназначеных для этого таблицах. Но поскольку запросы на вставку выполняются реже, чем на извлечение данных, то серьезных потерь это обычно не приносит.

В целом, как вы можете видеть, достоинства от использования индексов значительно перевешивают небольшое количество недостатков. На больших и посещаемых проектах без использования индексов не обойтись.

Другие посты

Как обновить значения поля с типом ENUM в таблице базы данных (MySQL)
Получение случайных записей из базы данных.
MySQL LIKE.
DISTINCT MySQL.
MySQL TIMESTAMP.

Комментарии (2)

Здравствуйте. Объясните, почему бы не сделать один unique key, который бы люди вешали туда, где им нужна уникальность записей? Зачем было создавать ещё primary key? В чём их особенные отличия-то? Заранее спасибо.

unique key- игнорирует значения равные NULL? Т.е. пустоту. А Primary Key не может быть пустотой и используется для уникальных значений допустим идентификаторов в таблицах это поле ID всегда уникально и всего чемуто равно

Как лучше проиндексировать таблицу MySQL?

Вопрос: Правильно будет проиндексировать столбцы ip и updated_at вместе или только столбец ip ? Индексы какого типа необходимо использовать? В таблице > 6 миллионов строк, не затормозит ли работу индексация, если я прямо сейчас сделаю запрос на создание индекса?

Отслеживать
Владимир Соловьев
задан 30 янв 2018 в 9:43
Владимир Соловьев Владимир Соловьев
63 6 6 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Индексация наоборот улучшает оптимизацию. В данном случае Вам нужно сделать составной индекс ip и updated_at CREATE INDEX ip_updated ON users(ip, updated_at);

Ваш же случай описан здесь

Сортировка

Составные индексы также можно использовать, если выполняется сортировка:

SELECT * FROM users WHERE gender = ‘male’ ORDER BY age В этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):

CREATE INDEX gender_age ON users(gender, age); Такой порядок колонок в индексе позволит выполнить фильтрацию по первой части индекса, а затем отсортировать результат по второй.

Как проиндексировать таблицу mysql по полю