mysql индексируют оптимизацию для таблицы с несколькими индексами, которые индексируют некоторые из тех же столбцов
У меня есть таблица, которая хранит некоторые основные данные о сессиях посетителя на сторонних веб-сайтах. Это - его структура:
id, site_id, unixtime, unixtime_last, ip_address, uid
Существует четыре индекса: id, site_id/unixtime, site_id/ip_address, и site_id/uid
Существует много различных типов способов, которыми мы запрашиваем эту таблицу, и все они характерны для site_id. Индекс с unixtime используется для отображения списка посетителей к данной дате или диапазону времени. Другие два используются для нахождения всех посещений IP-адреса или "uid" (уникальное значение cookie созданный для каждого посетителя), а также определение, если это - новый посетитель или возвращающийся посетитель.
Очевидно, хранение site_id в 3 индексах неэффективно и для скорости записи и для устройства хранения данных, но я не вижу пути вокруг этого, так как я должен смочь быстро запросить эти данные для данного определенного site_id.
Какие-либо идеи сделать это более эффективным?
Я действительно не понимаю B-деревья помимо некоторого очень простого материала, но более эффективно иметь крайний левый столбец индекса быть тем с наименьшим количеством различия - корректный? Поскольку я рассмотрел наличие site_id, являющегося вторым столбцом индекса и для ip_address и для uid, но я думаю, что это сделало бы индекс менее эффективным, так как IP и UID собираются варьироваться больше, чем идентификатор сайта будет, потому что у нас только есть приблизительно 8 000 уникальных сайтов сервер для каждой базы данных, но миллионы уникальных посетителей через все ~8000 сайтов ежедневно.
Я также рассмотрел удаление site_id от IP и индексов UID полностью, начиная с возможностей того же посетителя, идущего в несколько сайтов, которые совместно используют тот же сервер базы данных, являются довольно маленькими, но в случаях, где это действительно происходит, я боюсь, что это могло вполне не спешить определять, является ли это новым посетителем этого site_id или нет. Запрос был бы чем-то как:
select id from sessions where uid = 'value' and site_id = 123 limit 1
... таким образом, если бы этот посетитель посетил этот сайт прежде, он должен был бы только найти одну строку с этим site_id, прежде чем он остановился. Это не было бы супер быстро обязательно, но приемлемо быстро. Но скажите, что у нас есть сайт, который получает 500 000 посетителей в день, и конкретный посетитель любит этот сайт и идет туда 10 раз в день. Теперь они, оказывается, поражают другой сайт в тот же сервер базы данных впервые. Вышеупомянутый запрос мог занять довольно долгое время для поиска всего из потенциально тысячи строк для этого UID, рассеянного на всем протяжении диска, так как он не будет находить один для этого идентификатора сайта.
Любое понимание при создании этого максимально эффективного ценилось бы :)
Обновление - это - таблица MyISAM с MySQL 5.0. Мои проблемы оба с производительностью, а также пространством памяти. Эта таблица и читается и тяжелая запись. Если я должен был выбрать между производительностью и устройством хранения данных, мое самое большое беспокойство является производительностью - но оба важны.
Мы используем memcached в большой степени во всех областях нашего сервиса, но это не оправдание не заботиться о проектировании баз данных. Я хочу, чтобы база данных была максимально эффективна.
3 ответа
Прежде всего, если вы используете ip как строку, измените ее на столбец INT UNSIGNED и используйте функции INET_ATON (expr) и INET_NTOA (expr) для справиться с этим. Индексирование целочисленных значений более эффективно, чем индексирование строк переменной длины.
Хорошо индексирует торговлю памятью на производительность. Трудно, если вы хотите и того, и другого. Трудно оптимизировать это дальше, не зная все запросы, которые вы выполняете, и их количество за интервал.
То, что у вас есть, будет работать. Если вы столкнулись с узким местом, вам нужно выяснить, есть ли у него процессор, оперативная память, диск и / или сеть, и соответствующим образом отрегулировать. Трудно и неправильно оптимизировать преждевременно.
Вы, вероятно, захотите переключиться на innodb, если у вас есть какие-либо обновления, иначе myisam хорош для вставки / выбора. Кроме того, поскольку размер вашей строки невелик, вы можете заглянуть в кластер mysql (nbd). Существует также механизм архивирования, который может помочь с требованиями к хранилищу, но разбиение на разделы в 5.1, вероятно, лучше для изучения.
Изменение порядка вашего индекса не имеет никакого смысла, если эти индексы уже используются во всех ваших запросах.
но более эффективно, чтобы крайний левый столбец индекса был столбцом с наименьшей дисперсией - правильно?
не уверен, но я не слышал об этом раньше. Мне это не кажется верным для этого приложения. Порядок индекса имеет значение для сортировки, а наличие нескольких уникальных первых полей индекса позволяет большему количеству запросов использовать индекс.
Я действительно не разбираюсь в B-деревьях, кроме некоторых очень простых вещей, но более эффективно, чтобы крайний левый столбец индекса был столбцом с наименьшей дисперсией - правильно?
Есть одно важное свойство индексов B-дерева, о которых вам необходимо знать: можно (эффективно) искать произвольный префикс полного ключа, но не суффикс . Если у вас есть индекс site_ip (site_id, ip) , и вы запрашиваете , где ip = 1.2.3.4 , MySQL не будет использовать индекс site_ip. Если бы вместо этого у вас был ip_site (ip, site_id) , то MySQL мог бы использовать индекс ip_site.
Второе свойство индексов B-дерева, о котором вы также должны знать: они сортируются. Индекс b-дерева может использоваться для таких запросов, как , где site_id <40 .
Также следует помнить о важном свойстве дисковых накопителей: последовательное чтение - это дешево, а поиск - нет. Если используются какие-либо столбцы, которых нет в индексе, MySQL должен прочитать строку из данных таблицы. Это вообще поиск и медленный. Поэтому, если MySQL считает, что он прочитает даже небольшой процент такой таблицы, он вместо этого проигнорирует индекс. Одно сканирование большой таблицы (последовательное чтение) обычно выполняется быстрее, чем случайное чтение даже нескольких процентов строк в таблице.
То же самое, кстати, относится и к поиску по индексу. Поиск ключа в B-дереве на самом деле потенциально требует нескольких поисков, поэтому вы обнаружите, что WHERE site_id> 800 AND ip = '1.2.3.4 ' не может использовать индекс site_ip , потому что каждый site_id требует нескольких поисков индекса, чтобы найти начало записей 1.2.3.4 для этого сайта. Однако будет использоваться индекс ip_site .
В конечном итоге вам придется свободно использовать бенчмаркинг и EXPLAIN , чтобы определить лучшие индексы для вашей базы данных. Помните, что вы можете свободно добавлять и удалять индексы по мере необходимости. Неуникальные индексы не являются частью вашей модели данных; они просто оптимизация.
PS: Протестируйте также InnoDB, он часто имеет лучшую параллельную производительность. То же самое и с PostgreSQL.