MySQL: столбцы с низкой мощностью/избирательностью = как индексировать?

Мне нужно добавить индексы к моей таблице (столбцам) и наткнуться на это сообщение:

Сколько индексов базы данных слишком много?

Цитата: "Сказав это, вы можете четко добавить множество бессмысленных индексов в таблицу, которые ничего не сделают. Добавление индексов B-Tree в столбец с двумя разными значениями будет бессмысленным, поскольку оно ничего не добавляет с точки зрения поиска данные вверх. Чем уникальнее значения в столбце, тем больше он будет выигрывать от индекса."

Является ли индекс действительно бессмысленным, если есть только два разных значения? Учитывая таблицу следующим образом (база данных MySQL, InnoDB)

Id (BIGINT)
fullname (VARCHAR)
address (VARCHAR)
status (VARCHAR)

Дополнительные условия:

База данных содержит 300 миллионов записей
Статус может быть "включен" и "отключен"
150 миллионов записей имеют статус = включено и 150 миллионов записей stauts = disabled

Насколько я понимаю, без индекса статуса, выбор с помощью where status=’enabled’ приведет к полному использованию таблиц с 300 миллионами записей для обработки?

Насколько эффективен поиск, когда я использую индекс BTREE для статуса?

Нужно ли индексировать этот столбец или нет?

Какие альтернативы (возможно, любые другие индексы) MySQL InnoDB обеспечивают эффективный просмотр записей с помощью "where status =" enabled "в данном примере с очень низкой мощностью/селективностью значений?

Ответ 1

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

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

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

Теперь вернемся к вашему примеру, у вас есть предложение where where status = 'enabled', индекс вернет 150 м строк, и базе данных придется читать каждую строку поочередно с помощью отдельных небольших чтений. Если доступ к таблице с полным сканированием таблицы позволяет базе данных использовать более эффективные более крупные чтения.

Существует точка, в которой лучше просто выполнить полное сканирование таблицы, а не использовать индекс. С помощью mysql вы можете использовать FORCE INDEX (idx_name) как часть вашего запроса, чтобы разрешить сравнение между каждым методом доступа к таблице.

Ссылка: http://dev.mysql.com/doc/refman/5.5/en/how-to-avoid-table-scan.html

Ответ 2

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

Это индексирование не является бесплатным; вы платите за него при вставках/обновлениях, когда индекс должен быть обновлен и в самом поиске, так как теперь ему нужно загрузить индексный файл (полный текстовый индекс для записей 300M, вероятно, не находится в памяти). Поэтому вполне возможно, что вы получите дополнительный IO вместо того, чтобы его лимитировать.

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

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

Это просто появилось в моей голове, что вы можете проверить, действительно ли индекс используется с помощью инструкции explain. Это должно показать вам, как MySQL оптимизирует запрос. Я действительно не знаю, что мотыга MySQL оптимизирует запросы, но из postgresql я знаю, что вы должны объяснить запрос в базе данных примерно одинаковым (по размеру и данным) в качестве реальной базы данных. Поэтому, если у вас есть копия в базе данных, создайте индекс в таблице и посмотрите, как оно действительно используется. Как я уже сказал, я сомневаюсь в этом, но я определенно не знаю всего:)

Ответ 3

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

Ответ 4

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

Ян, как и было задано, где ваш запрос включает просто "где status = enabled" без какого-либо другого ограничивающего фактора, индекс в этом столбце, по-видимому, не поможет (рад, что сообщество SO показало мне, что случилось). Если, однако, существует ограничивающий фактор, такой как "предел 10", индекс может помочь. Кроме того, помните, что индексы также используются в группах по порядку и по порядку путем оптимизации. Если вы делаете "select count (*), статус из группы по статусу", индекс был бы полезен.

Вы также должны рассмотреть возможность преобразования состояния в tinyint, где 0 будет отображаться как отключенный, а 1 - включен. Вы тратите тонны пространства, хранящего эту строку против tinyint, которая требует только 1 байт за строку!

Ответ 5

Если данные распределены как 50:50, тогда запрос типа where status="enabled" будет избегать половинного сканирования таблицы.

Наличие индекса для таких таблиц полностью зависит от распределения данных, i, e: если записи с включенным статусом составляют 90%, а другие - 10%. и для запроса, где status="disabled" он сканирует только 10% таблицы.

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