Организация и оптимизация больших таблиц

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

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

Итак, мой вопрос: как мне наиболее эффективно организовать эти данные?

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

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

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

Поиск ищет в таблице кодов городов определенный статус. Обновления происходят на основе идентификатора записи.

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

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

Общий размер таблиц составляет 2 миллиона строк и продолжает расти.


mysql optimization database-optimization
person TaoJoannes    schedule 25.04.2012    source источник
comment
2 миллиона строк, отредактировано для ясности   -  person TaoJoannes    schedule 27.04.2012
comment
Для 2 миллионов строк соединение будет медленным, например, при поиске по коду города, а коды городов немного по сравнению (я полагаю, вы будете фильтровать по флагу «не вызывать» и, возможно, упорядочивать что-то).   -  person Tiberiu-Ionuț Stan    schedule 02.05.2012
comment
Одна большая таблица может быть хорошей идеей для вашего конкретного сценария. В моих собственных тестах хороший сервер/рабочий стол с быстрыми твердотельными накопителями должен давать хорошие результаты.   -  person Tiberiu-Ionuț Stan    schedule 02.05.2012
comment
@Tiberiu для 2 миллионов строк соединение будет медленным - для некоторых соединений это верно, для других - нет.   -  person Matt Fenwick    schedule 02.05.2012
comment
@matt Вот почему я также добавил, например, при поиске по коду города, а коды городов мало по сравнению. Если количество элементов для кодов городов невелико, то при объединении, скорее всего, придется сравнивать сотни тысяч строк.   -  person Tiberiu-Ionuț Stan    schedule 02.05.2012

Ответы (3)


arrow_upward
2
arrow_downward

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

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

Создание отдельных таблиц на основе значения атрибута не является типичным решением и создаст много дополнительных трудностей (например, если вы организуете таблицы по почтовому индексу, как вы будете искать по территории по всем почтовым индексам?)

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

Итак, основная стратегия, которую я бы рекомендовал:

  1. создать логическую модель данных
  2. реализовать физическую модель данных
  3. analyze the performance
    • explain <query> is very handy
    • если этого недостаточно, рассмотрите возможность добавления дополнительных индексов, улучшения использования существующих индексов (почитайте о кластеризованных и покрывающих индексах) или выборочной денормализации.
    • каков баланс между выборками и вставками? Индексы могут замедлять вставку

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

person Matt Fenwick    schedule 30.04.2012
comment
Использование - это то, что заставляет меня хотеть создавать отдельные таблицы кампании. Мы используем разные исходящие номера для каждой территориальной кампании и не хотим никому звонить с одного и того же номера дважды. Так что я думаю, что лучший способ отследить это — извлечь из большого списка вычищенных номеров для построения таблиц на основе кампаний, чтобы мы могли определить, на какие номера мы звонили от имени каждой из кампаний. Тогда исходная таблица — это просто числовая ферма. Мы добавляем к нему новые числа и очищаем его от номеров DNC. Честно говоря, я не могу придумать эффективного способа отследить, по каким номерам мы кому звонили, иначе. - person TaoJoannes; 01.05.2012
comment
@TaoJoannes, что вы подразумеваете под кампанией? - person Matt Fenwick; 02.05.2012
comment
@TaoJoannes Я не думаю, что в твоей ситуации есть что-то ненормальное. Вот почему я предлагаю вам создать и реализовать свою логическую модель данных, а затем протестировать ее на производительность, а затем при необходимости оптимизировать. В противном случае, я думаю, вы можете застрять в очень трудном месте. - person Matt Fenwick; 02.05.2012
comment
кампания будет представлять собой таблицу номеров, по которым мы будем звонить от имени клиента, на основе почтовых индексов. Я думаю, что формализация LDM была бы очень хорошей идеей, так как в настоящее время это просто что-то витает в моей голове. Чтобы оптимизировать PDM, я использовал как можно меньше и как можно более простые запросы. Я считаю, что совершил ошибку, используя в основном типы данных CHAR. Я думаю, что могу безопасно преобразовать числовые данные в INT и настроить внешнюю таблицу для почтовых индексов. Я изучаю MYSQL с ноября на производственной системе, которую я создаю с нуля, поэтому случаются ошибки. - person TaoJoannes; 02.05.2012

arrow_upward
1
arrow_downward

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

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

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

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

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

person Yago Riveiro    schedule 02.05.2012

arrow_upward
0
arrow_downward

TaoNonnanes, Нет необходимости каждый раз создавать territory таблицу для area code table.

Только что сделал только одну таблицу территорий с внешним ключом area code table, просто сделай индексы для таблицы кодов территорий и областей и попробуй нормализовать всю базу данных как минимум до 3NF. Я не знаю, какова вся нормализация вашей базы данных.

person Shaikh Farooque    schedule 28.04.2012