что это и зачем нужны — Блог Lineate

ко всем статьям

< ко всем статьям

Автор: Татьяна Сергиенко, Software Engineer

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

Что такое индексы?

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

Важность индексов увеличивается по мере роста объема данных. Если у вас какая-то небольшая база данных, она может работать без индексов, но производительность ваших запросов может сильно упасть, как только она начнет расти.

Для чего используются индексы?

Индексы помогают:

  • быстро находить строки, соответствующие выражению WHERE
  • извлекать строки из других таблиц при выполнении объединений
  • находить величины MAX() или MIN() для заданного индексированного столбца
  • производить сортировку или группирование в таблице, если эти операции делаются на крайнем левом префиксе используемого ключа (например, ORDER BY key_part_1, key_part_2).

Индекс – это специальная структура данных, обычно это B-Tree дерево, которое позволяет повышать скорость извлечения данных за счет дополнительных операций записи и хранения. Здесь стоит отметить, что индексы хранятся отдельно от данных.

Схематично B-Tree можно изобразить так: дерево состоит из корня (верхняя вершинка), дальше у нас идут ветви, ветви заканчиваются листьями, на листьях находится нужная информация.

Мощность Индекса

Мощность индекса относится к уникальности значений, хранящихся в указанном столбце индекса.

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

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

Действия с индексами MySQL

Создание индекса

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

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

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

Просмотр индексов

Немаловажная возможность – посмотреть, какие индексы есть у таблицы в целом или, например, выбрать индексы с каким-то определенным параметром.

Удаление индекса

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

ДРУГИЕ СТАТЬИ

ко всем статьям

Wednesday, August 18

Индексы MySQL: что это и зачем нужны — Блог Lineate

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

Wednesday, August 18

Типы индексов MySQL — Блог Lineate

Кластеризованные — специальные индексы, Primary Key и Unique Index (Key и Index – это синонимы в данном случае). Некластеризованные, или вторичные, индексы — все остальные индексы, которые не попадают под Primary и Unique

Online Documentation for SQL Manager for MySQL

Редактор индексов

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

В поле Index name укажите имя индекса. Имя нельзя изменить только если индекс имеет статус Primary.

 

Далее, в разделе Fields for index, выберите поля таблицы, которые будут включены в индекс.

В списке Available Fields содержатся доступные поля, которые с помощью кнопок можно переместить в список полей индекса — Included Fields.

 

 

 

В разделе Index properties укажите свойства индекса.

 

Is Primary key — выберите это значение если создаете первичный ключ.

 

Index type

Из этого раскрывающегося списка можно выбрать тип индекса:

  • Fulltext — создаваемый индекс будет полнотекстовым.
  • Unique — создает уникальный индекс для таблицы или представления. Уникальным является индекс, в котором не допускается наличие двух строк с одинаковыми значениями ключа индекса.
  • Spatial — пространственный индекс для таблиц имеющих тип хранения MyISAM, InnoDB, NDB, и ARCHIVE on spatial data types.

 

Index using

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

Это может быть BTREE или HASH.

 

Size of index block (KEY_BLOCK_SIZE)

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

 

 

Fulltext parser (PARSER)

Этот параметр может быть задан только для полнотекстового индекса. Он привязывает плагин парсера к индексу если полнотекстовое индексирование и поиск требуют дополнительной обработки.

 

На вкладке Comment можно задать текстовый комментарий к индексу, содержащий до 1024 символов.

 

 

Смотрите также:

Редактор таблиц

Редактор внешних ключей

Редактор триггеров

 

Индексация

— Как увидеть индексы для базы данных или таблицы в MySQL?

Как узнать, есть ли в моей базе данных индексы?

Как насчет конкретной таблицы?

  • mysql
  • индексация
  • схема базы данных

2

Чтобы просмотреть индекс конкретной таблицы, используйте SHOW INDEX:

 SHOW INDEX FROM yourtable;

Чтобы просмотреть индексы для всех таблиц в определенной схеме, вы можете использовать таблицу STATISTICS из INFORMATION_SCHEMA:

 ВЫБЕРИТЕ ОТЛИЧНЫЙ
    ТАБЛИЦА_ИМЯ,
    INDEX_NAME
ИЗ INFORMATION_SCHEMA. СТАТИСТИКА
ГДЕ TABLE_SCHEMA = 'ваша_схема';

Удаление предложения where покажет вам все индексы во всех схемах.

3

Если вы хотите увидеть сразу все индексы во всех базах данных:

 use information_schema;
ВЫБЕРИТЕ * ИЗ статистики;

1

 ПОКАЗАТЬ ИНДЕКС ИЗ mytable ИЗ mydb;
ПОКАЗАТЬ ИНДЕКС ИЗ mydb.mytable;

См. документацию.

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

 ВЫБЕРИТЕ ИМЯ ТАБЛИЦЫ,
       COUNT(1) index_count,
       GROUP_CONCAT(DISTINCT(index_name) SEPARATOR ',\n ') индексы
ИЗ INFORMATION_SCHEMA.СТАТИСТИКА
ГДЕ TABLE_SCHEMA = 'mydb'
      AND INDEX_NAME != 'основной'
СГРУППИРОВАТЬ ПО TABLE_NAME
ЗАКАЗАТЬ ПО СЧЕТУ(1) DESC;

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

 SHOW INDEX from your_table_name;

, чтобы увидеть все индексы в таблице (созданной БД и вами)

 ПОКАЗАТЬ РАСШИРЕННЫЙ ИНДЕКС от your_table_name;

Для получения всех проиндексированных столбцов по индексу в одном столбце в порядке следования.

 ВЫБЕРИТЕ имя_таблицы КАК `Таблица`,
       index_name КАК `Индекс`,
       GROUP_CONCAT (имя_столбца ORDER BY seq_in_index) AS `Столбцы`
ИЗ information_schema.statistics
ГДЕ table_schema = 'сакила'
СГРУППИРОВАТЬ НА 1,2;

Ссылка: http://blog.9minutesnooze.com/mysql-information-schema-indexes/

Почему не показывает создание таблицы myTable ?

Кто-то сказал мне это, но я не видел здесь ни одного упоминания, что-то плохое?

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

1

Я предлагаю этот запрос:

 SELECT DISTINCT s.*
FROM INFORMATION_SCHEMA.STATISTICS s
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
       И t.TABLE_NAME = s.TABLE_NAME
       И s.INDEX_NAME = t.CONSTRAINT_NAME
ГДЕ 0 = 0
      И t.CONSTRAINT_NAME IS NULL
      И s.TABLE_SCHEMA = 'ВАШ_СХЕМА_ОБРАЗЕЦ';

Вы нашли весь индекс только индекс .

С уважением.

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

 ВЫБЕРИТЕ ИМЯ ТАБЛИЦЫ, ИМЯ_СТОЛБЦА, КОММЕНТАРИЙ
ИЗ information_schema.statistics
ГДЕ table_schema = 'database_name';

Чтобы проверить все отключенные индексы в базе данных

 SELECT INDEX_SCHEMA, COLUMN_NAME, COMMENT
ИЗ information_schema.statistics
ГДЕ table_schema = 'mydb'
И КОММЕНТАРИЙ = «отключено»

Вы можете проверить свои индексы в рабочей среде MySQL. На вкладках отчетов о производительности вы можете увидеть все используемые индексы и неиспользуемые индексы в системе. или вы можете запустить запрос.

 выберите * из sys.schema_index_statistics;

3

Чтобы запросить информацию об индексе таблицы, используйте оператор SHOW INDEXES следующим образом:

 SHOW INDEXES FROM имя_таблицы;

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

 ПОКАЗАТЬ ИНДЕКСЫ ИЗ table_name
IN имя_базы_данных;

Следующий запрос аналогичен приведенному выше:

 ПОКАЗАТЬ ИНДЕКСЫ ИЗ имя_базы_данных. имя_таблицы;

Обратите внимание, что ИНДЕКС и КЛЮЧИ являются синонимами ИНДЕКСОВ, а В — синонимом ОТ, поэтому вместо этого вы можете использовать эти синонимы в столбце ПОКАЗАТЬ ИНДЕКСЫ. Например:

 ПОКАЗАТЬ ИНДЕКС В имя_таблицы
ОТ имя_базы_данных;

или

 ПОКАЗАТЬ КЛЮЧИ ОТ имени таблицы
 IN имя базы данных;
 
 выбрать
    имя_таблицы,
    имя_индекса,
    seq_in_index,
    имя_столбца,
    не_уникальный,
    тип_индекса,
    комментарий
от
    information_schema.statistics
где 1=1
    и table_schema = 'моя_схема'
    и table_name = 'my_table'
порядок по 1,2,3,4,5,6

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

select * from all_indexes где index_name= ‘ваш индекс’

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Рекомендации по индексированию MySQL

Когда следует оптимизировать базу данных MySQL?

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

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

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

Как распознать проблемы с производительностью запросов

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

  1. Журнал медленных запросов MySQL. Включите его.
  2. список процессов. Используй это.
 показать список процессов;
показать полный список процессов;

Использование EXPLAIN для определения необходимости индекса

Теперь вы поняли, кто эти нарушители спокойствия в приложении. Но как вы узнаете, что они плохо работают только из-за отсутствия индекса? Что ж, оператор EXPLAIN в MySQL может спасти вам жизнь.

EXPLAIN работает с операторами SELECT, DELETE, REPLACE и UPDATE (начиная с версии Mysql 5.7). Как и у вас, у MySQL будут потрясающие планы для каждого запроса о том, как его выполнять, какой индекс выбрать, как соединить таблицы и какой порядок следует поддерживать для оптимальной производительности.

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

Описание таблицы

Давайте возьмем пример запроса и проанализируем, что выдает EXPLAIN.

 выбрать
  *
от
  master_users
где
  email = '[email protected]'

Объяснение приведенного выше запроса дает следующее:

До индекса

Давайте разберем результат EXPLAIN. Как вы можете видеть, MySQL прошел через 152685 строк (см. столбец: строки) таблицы, чтобы найти соответствующую строку, и это равно полному сканированию таблицы, поскольку в таблице всего 153728 строк. Результат EXPLAIN имеет два столбца с именами возможных_ключей и ключ . столбец возможных_ключей сообщает нам, какие индексы доступны в таблице или индексы, которые можно использовать для наших запросов.

Столбец key сообщает нам, какие индексы использовались MySQL для анализируемого запроса. На данный момент оба пусты. Пустой столбец key предоставляет информацию о том, что в запросе отсутствует индекс.

Давайте добавим один индекс и посмотрим, что получится:

 ИЗМЕНИТЬ ТАБЛИЦУ
  master_users
ДОБАВЛЯТЬ
  INDEX index_email(email)
После добавления index.

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

Оптимизация сложных запросов

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

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

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

Возьмем другой запрос.

 выбрать
  *
от
  master_users
где
  электронная почта = '[email protected]'
  и category_id = 3

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

col1col2

Что теперь делать? Многоколоночный (составной) индекс спешит на помощь..!

Что такое составные индексы?

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

Давайте посмотрим, как можно создать составной индекс.

 ИЗМЕНИТЬ ТАБЛИЦУ
<имя-таблицы>
ДОБАВИТЬ ИНДЕКС
index_co1_col2(column1, column2, column3,...)

Имеет ли значение порядок столбцов для составного индекса?

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

Теперь, как вы найдете мощность или количество различных значений столбца?. Вы можете просто выполнить отличительный запрос подсчета в столбце. (Рекомендуется не выполнять такие запросы, упомянутые ниже, в рабочей БД, когда она работает в пиковое время трафика.)

 select
подсчет (различный (электронная почта))
 от
 master_users 
 выбрать
количество (различные (category_id))
от
master_users
Отличительные подсчеты каждого столбца

Здесь вы можете видеть, что кардинальность столбец category_id меньше, чем столбец электронной почты, поэтому мы должны создать составной индекс следующим образом:

 ALTER TABLE
master_users
ДОБАВИТЬ ИНДЕКС
index_category_email(category_id, email)

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

Например, индекс, который мы только что создали ( category_id, электронная почта ) не подходит для запроса типа:

 select
*
от
master_users
где
email='[email protected]'

Когда составной индекс НЕ ПОЛЕЗЕН?

Что ж, на это есть причина.

Если таблица имеет индекс с несколькими столбцами, оптимизатор может использовать любой крайний левый префикс индекса для поиска строк. Например, если у вас есть индекс из трех столбцов на (столбец1, столбец2, столбец3) , у вас есть возможности индексированного поиска по номеру (столбец1) 9.0058 , (столбец1, столбец2) и (столбец1, столбец2, столбец3) .

Это означает, что оптимизатор MySQL не может использовать индекс для выполнения поиска, если запрос не содержит крайний левый столбец префикса индекса. Таким образом, крайний левый столбец в составном индексе известен как столбец поиска.

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

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

У составного индекса есть еще одно преимущество; Если индекс существует на (col1, col2, col3) , один и тот же индекс может быть эквивалентен созданию нескольких индексов, таких как индекс на col1 , индекс (col1, col2) и индекс (col1, col2, col3) обеспечил порядок индекса.

(col1, col2, col3)(col1)(col1, col2)(col1, col2, col3)

Например, возьмем индекс (status, category_id, email) .

Этот индекс будет полезен в следующих запросах:

 select
  *
от
  master_users
где
  статус = 1;
выбирать *
от
  master_users
где
  статус = 1
  и category_id = 3;
выбирать *
от
  master_users
где
  статус = 1
  и ид_категории = 3
  и электронная почта = 'akhil@gmail. com';

Но не в следующих запросах:

 выберите
  *
от
  master_users
где
  id_категории = 3;
  
выбирать *
от
  master_users
где
  id_категории = 3
  и электронная почта = '[email protected]'

Индексация для запросов с операторами ИЛИ и И 

 выберите
  положение дел,
  ид_категории
от
  master_users
где
  статус = 1
  или category_id = 3

В этом случае MySQL не сможет использовать индекс в запросах с условием ИЛИ, даже если запрос содержит столбец поиска и поддерживает порядок предложения WHERE такой же, как индекс.

Поэтому рекомендуется избегать таких условий ИЛИ и рассмотреть возможность разделения запроса на две части в сочетании с UNION DISTINCT (или, что еще лучше, UNION ALL , если вы знаете, что не будет повторяющихся результатов) :

 выбрать
  положение дел,
  ид_категории
от
  master_users
где
  статус = 1
СОЮЗ ВСЕХ
выбирать
  положение дел,
  ид_категории
от
  master_users
где
  category_id = 3

Индексация для запросов с ORDER BY и GROUP BY

возьмем тот же индекс (status, category_id, email) и запустите запрос:

 выберите
  *
от
  master_users
где
  статус = 1
СОРТИРОВАТЬ ПО
  category_id

Это будет использовать составной индекс для предложения WHERE, и если вы думаете, что это сузит записи со статусом, имеющим значение 1, к сожалению, теперь вам нужно выполнить сортировку этих результирующих записей, чтобы отсортировать их по category_id . Это потому, что индекс не сортировал результаты по category_id 9.0058 каким-либо осмысленным образом, а в предложении ORDER BY отсутствует столбец поиска.

Это известно как сортировка файлов (некоторые из вас могли заметить это в результате объяснения). Это происходит из-за того, что созданный нами индекс не соответствует предложению ORDER BY.

Сортировка файлов: Сортировка, выполняемая после запроса; он требует извлечения данных во временный буфер и их сортировки перед окончательным возвратом. В этом не было бы необходимости, если бы данные уже были отсортированы по индексу так, как вы хотели.

Это также применимо, даже если вы хотите прочитать только 10 строк:

 выберите
  *
от
  master_users
где
  статус = 1
СОРТИРОВАТЬ ПО
  ид_категории
ограничение
  5

Вы по-прежнему будете получать тысячи записей, сортировать их и только после этого возвращать первые 5, отбрасывая остальные записи, на обработку которых ушло время:

 select
  *
от
  master_users
где
  статус = 1
СОРТИРОВАТЬ ПО
  положение дел,
  category_id

Этот запрос может использовать указанный индекс, поскольку он подходит как для предложения WHERE, так и для предложения ORDER BY.

Рассмотрим другой запрос, который сортирует строки по статусу в порядке возрастания, а затем по category_id в порядке убывания:

 select
  *
от
  master_users
где
  статус = 1
СОРТИРОВАТЬ ПО
  статус АСЦ,
  category_id DESC

MySQL не может использовать индексы при сортировке со смешанным порядком (и ASC, и DESC в одном предложении ORDER BY).

Примечание. Это изменилось с выпуском функциональности обратных индексов и MySQL 8.x.

Все, что вы видели в отношении ORDER BY, также применимо к операторам GROUP BY. если вы выполните следующий запрос с составным индексом на (статус, идентификатор категории, электронная почта) :

 выбрать
  *
от
  master_users
где
  статус = 1
ГРУППА ПО
  category_id

Записи уже отсортированы по статусу , category_id и электронной почте . Это позволяет быстро отфильтровать все записи с status=1 . После того, как эти результаты возвращены, они также затем сортируются на основе category_id , поскольку индекс упорядочивает строки иначе, чем требуется в запросе.

Как насчет запросов с условиями JOIN или RANGE?

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

Запрос рассматривается как запрос диапазона, если он использует любой или сочетание следующих операторов: = , <=> , IN() , IS NULL или IS NOT NULL , > , < , >= , <= , BETWEEN , != или <> операторы, сравнения LIKE (если аргументом LIKE является постоянная строка, которая не начинается с подстановочного знака.)

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

Какие столбцы следует индексировать?

Из всего, что мы обсудили, вы должны понять, что это зависит от:

  • От каких столбцов вы собираетесь запрашивать
  • От каких соединений вы будете выполнять
  • От каких ORDER/GROUP BY и т. д.

Вы можете также обратитесь к этому руководству по проектированию и индексированию схемы MySQL.

Бонус: покрывающие индексы

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

фаза запроса выборка фаза

Если у вас есть MySQL с двигателем InnoDB, вы обычно будете использовать такие индексы, как Clustered index и вторичный индекс .

Когда вы определяете ПЕРВИЧНЫЙ КЛЮЧ в своей таблице, InnoDB использует его как кластеризованный индекс. Хранилище таблиц InnoDB организовано на основе значений столбцов первичного ключа, чтобы ускорить выполнение запросов и сортировок с использованием столбцов первичного ключа. Все индексы, кроме кластеризованного индекса, называются вторичными индексами.

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

Таким образом, MySQL должен зависеть от индекса первичного ключа для выполнения фазы выборки, даже если все совпадающие строки идентифицируются с использованием индекса (вторичный индекс):

 select
  мобильный
от
  master_users
где
  mode = 2

Рассмотрим приведенный выше запрос и предположим, что вы создали индекс для режима .

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

Что, если мы добавим такой индекс?

 ИЗМЕНИТЬ ТАБЛИЦУ
  master_users
ДОБАВЛЯТЬ
  INDEX index_covering(mode, mobile)

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

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

Рекомендации по индексированию базы данных MySQL

  • Не создавайте индексы, если вы не уверены, что они вам понадобятся.
  • Не индексировать каждый столбец в таблице отдельно.
  • Избегайте использования функций слева от оператора. Например:
 выбрать
  *
от
  master_users
где
  upper(email) = '[email protected]'

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

 выбрать
  *
от
  master_users
где
  created < now()
  • Избегайте подстановочных знаков в начале шаблона LIKE. Не надо:
 выбрать
  *
от
  master_users
где
  электронная почта LIKE '%akhil%'

Вместо этого сделайте:

 выберите
  *
от
  master_users
где
  электронная почта LIKE 'akhil%'

MySQL не будет использовать индекс для поиска по шаблону

  • Избегайте условий ИЛИ и используйте UNION в качестве альтернативы.
  • Избегайте сортировки в смешанном порядке. Не надо:
 выбрать
  *
от
  master_users
где
  статус = 1
СОРТИРОВАТЬ ПО
  статус АСЦ,
  category_id DESC

Сделать:

 выбрать
  *
от
  master_users
где
  статус = 1
СОРТИРОВАТЬ ПО
  статус АСЦ,
  category_id ASC
  • В предложении where сравните столбец со значением, которое соответствует типу столбца .

    No related posts.