MySQL: низкие столбцы кардинальности/селективности =, как индексировать?
Я должен добавить индексы к своей таблице (столбцы) и споткнулся через это сообщение:
То, сколько индексирует база данных, является слишком многими?
Кавычка: “Однако можно ясно добавить, что много бессмысленных индексов к таблице так не пойдет что-либо. Добавление индексов B-дерева к столбцу с 2 отличными значениями будет бессмысленно, так как оно ничего не добавляет с точки зрения поиска данных. Чем более уникальный значения в столбце, тем больше он извлечет выгоду из индекса”.
Индекс действительно бессмыслен, если существует только два отличных значения? Учитывая таблицу следующим образом (MySQL Database, InnoDB)
Id (BIGINT)
fullname (VARCHAR)
address (VARCHAR)
status (VARCHAR)
Дальнейшие условия:
- База данных содержит 300 миллионов записей
- Состояние может только быть “включено” и “отключено”
- 150 миллионов записей имеют состояние =, включил, и 150 миллионов записей имеют крепкие портеры = отключенный
Мое понимание, не имея индекса на состоянии, выборе с помощью where status=’enabled’ привел бы к полному сканированию таблицы с 300 миллионами Записей для обработки?
Насколько эффективный поиск, когда я использую индекс B-ДЕРЕВА на состоянии?
Я должен индексировать этот столбец или нет?
Какие альтернативы (возможно, какие-либо другие индексы) MySQL InnoDB обеспечивает для эффективного поиска записей, "где состояние = "включило" пункт в данном примере с очень низкой кардинальностью/селективностью значений?
4 ответа
Индекс, который вы описываете, практически бесполезен. Индекс лучше всего использовать, когда вам нужно выбрать небольшое количество строк по сравнению с общим количеством строк.
Причина этого связана с тем, как база данных обращается к таблице. Таблицы можно оценить либо путем полного сканирования таблицы, где каждый блок читается и обрабатывается по очереди. Или с помощью поиска по идентификатору строки или ключа, когда база данных имеет ключ / идентификатор строки и считывает именно ту строку, которая ей требуется.
В случае использования предложения where на основе первичного ключа или другого уникального индекса, например. где id = 1 , база данных может использовать индекс, чтобы получить точную ссылку на то, где хранятся данные строки. Это явно более эффективно, чем полное сканирование таблицы и обработка каждого блока.
Теперь вернемся к вашему примеру, у вас есть предложение where , где status = 'enabled' , индекс вернет 150 миллионов строк, и базе данных придется читать каждую строку по очереди, используя отдельные небольшие чтения. В то время как доступ к таблице с полным сканированием таблицы позволяет базе данных использовать более эффективные операции чтения больших объемов.
Есть момент, когда лучше просто выполнить полное сканирование таблицы, чем использовать индекс. С mysql вы можете использовать FORCE INDEX (idx_name) как часть вашего запроса, чтобы позволить сравнения между каждым методом доступа к таблице.
Ссылка: http: //dev.mysql.com / doc / refman / 5.5 / en / how-to-избежать-table-scan.html
Мне жаль, но я не согласен с Майком. Добавление индекса предназначено для ограничения количества полных поисков записей для MySQL, тем самым ограничивая ввод-вывод, который обычно является узким местом.
Это индексирование платное; вы платите за вставки / обновления, когда индекс должен быть обновлен, и за сам поиск, поскольку теперь ему нужно загрузить файл индекса (полнотекстовый индекс для 300M записей, вероятно, не находится в памяти). Так что вполне возможно, что вы получите дополнительный ввод-вывод вместо его ограничения.
Я согласен с утверждением, что двоичную переменную лучше всего хранить как единицу, bool или tinyint, так как это уменьшает длину строки и тем самым может ограничить дисковый ввод-вывод, а также быстрее сравнивать числа.
Если вам нужна скорость и вы редко используете отключенные записи, вы можете иметь 2 таблицы, одну для включенных и одну для отключенных записей, и перемещать записи при изменении статуса. Поскольку это увеличивает сложность и риск, это, конечно, будет моим самым последним выбором. Обязательно сделайте ход за 1 транзакцию, если вам посчастливилось пойти на это.
Мне просто пришло в голову, что вы можете проверить, действительно ли используется индекс , используя оператор объяснение . Это должно показать вам, как MySQL оптимизирует запрос.Я действительно не знаю, что MySQL оптимизирует запросы, но из postgresql я знаю, что вы должны объяснять запрос к базе данных, примерно такой же (по размеру и данным), что и реальная база данных. Поэтому, если у вас есть копия в базе данных, создайте индекс для таблицы и посмотрите, действительно ли она используется. Как я уже сказал, я сомневаюсь в этом, но я определенно не знаю всего :)
Ян, тебе обязательно нужно проиндексировать этот столбец. Я не уверен в контексте цитаты, но все, что вы сказали выше, правильно. Без индекса в этом столбце вы наверняка сканируете таблицу по 300 миллионам строк, а это худшее, что вы можете сделать для этих данных.
Ян, как и просили, когда ваш запрос включает просто «where status = enabled» без какого-либо другого ограничивающего фактора, индекс в этом столбце явно не поможет (рад, что сообщество SO показало мне, что происходит). Однако, если есть ограничивающий фактор, такой как «предел 10», индекс может помочь. Также помните, что индексы также используются для группировки и упорядочения по оптимизации. Если вы выполняете «выберите счетчик (*), статус из группы таблиц по статусу», вам будет полезен индекс.
Вам также следует подумать о преобразовании статуса в tinyint, где 0 означает отключено, а 1 - включено. Вы тратите кучу места на хранение этой строки по сравнению с крошечным интервалом, для которого требуется только 1 байт на строку!
Вряд ли вам понадобятся все 150 млн. записей сразу, поэтому я полагаю, что "статус" всегда будет использоваться в сочетании с другими колонками. Возможно, логичнее использовать составной индекс типа (status, fullname)