Схема подтаблицы подбазы данных на основе базы данных Mysql с сотнями миллионов данных

Заявление: Воспроизведено из Хэ Гуанмин Автор: Хэ Гуанмин источник:ТЭЦ Хаос Sun.com/portal/Ariti…

предисловие

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

Пополнить

В mysql каждая база данных может создавать до 2 миллиардов таблиц, таблица позволяет определить 1024 столбца, а максимальная длина каждой строки составляет 8092 байта (исключая длину копируемого текста и типов изображений). Когда таблица определена со столбцом типа varchar, nvarchar или varbinary, если строка данных, вставленная в таблицу, превышает 8092 байта, инструкция Transact-SQL завершится ошибкой и будет создано сообщение об ошибке. SQL Server не имеет прямого ограничения на количество строк в каждой таблице, но оно ограничено объемом хранилища базы данных. Максимальное пространство для каждой базы данных составляет 1048516 ТБ, поэтому максимальное пространство, доступное для таблицы, составляет 1048516 ТБ за вычетом пространства, занимаемого системными таблицами класса базы данных и другими объектами базы данных.

проблема

Некоторые люди могут спросить, почему бы не использовать Oracle? Это правда, что многие разработчики не заботятся о SQL при написании кода.Все проблемы с производительностью передаются администратору базы данных, который отвечает за оптимизацию SQL.Однако не в каждом проекте будет администратор базы данных, и не во всех проектах будет использоваться база данных Oracle. Более того, Oracle в контексте большого объема данных очень просто решает проблемы с производительностью.

Итак, может ли MySQL поддерживать объем данных в 100 млн. Мой ответ: да, большинство решений для хранения данных, принятых большинством интернет-компаний, — это в основном MySQL, а на государственных предприятиях и в банках доминирует Oracle, и есть полные время администраторов баз данных, чтобы служить вам.

В этой статье в качестве примера будет взято реальное проектное приложение для анализа [как оптимизировать базу данных] слой за слоем. Фоном проекта является унифицированная платформа обработки сообщений на уровне предприятия. Данные о клиентах составляют более 50 миллионов, а поток сообщений 10 млн в минуту, ежедневный поток новостей около 100 млн.

Хотя MySQL может хранить 1 миллиард данных в одной таблице, производительность в настоящее время очень низкая. Поскольку одна таблица не может быть выполнена, попробуйте найти способ разместить данные в нескольких местах, чтобы решить проблему.Поэтому родилась схема подбазы данных и подтаблицы базы данных.В настоящее время существует три распространенные схемы : раздел, подбаза данных и подтаблица, NoSQL/NewSQL.

В реальных проектах для решения задач часто используется комбинация этих трех решений.В настоящее время основные данные большинства систем в основном хранятся в СУБД, дополненной хранилищем NoSQL/NewSQL.

раздел

Схема разбиения

Таблица разделов реализована несколькими связанными базовыми таблицами, и эти базовые таблицы также представлены объектами дескрипторов, поэтому мы также можем напрямую обращаться к каждому разделу.Используя тот же механизм хранения), индекс таблицы разделов просто добавляет идентичный индекс к каждой базовой таблице.С точки зрения механизма хранения базовая таблица ничем не отличается от обычной таблицы, и механизму хранения не нужно знать, что это обычная таблица, которая также является частью секционированной таблицы. Это решение тоже хорошее, оно скрывает детали шардирования от пользователей, даже если в условии запроса нет столбца шардирования, оно может работать нормально (только на этот раз производительность средняя). Однако его недостатки очевидны: многие ресурсы ограничены одной машиной, например, количество подключений, пропускная способность сети и т. д. Способ разбиения является одним из ключевых элементов в практических приложениях. В нашем проекте, взяв в качестве примера информацию о клиенте, количество данных о клиенте составляет более 50 миллионов, а фон проекта требует сохранения отношения привязки банковской карты клиента, отношения привязки сертификата клиента и бизнес-информации, привязанной к клиенту. В этом бизнес-контексте, как спроектировать базу данных? На первом этапе проекта мы создали таблицу взаимосвязей бизнес-привязки клиентов, которая содержит избыточную бизнес-информацию, связанную с каждым клиентом. Базовая структура примерно следующая:

При запросе индексируется банковская карта, индексируется бизнес-номер, индексируется номер сертификата. При увеличении спроса индекс этой таблицы достигнет более 10. Более того, когда заказчик расторгнет договор, а затем подпишет договор, в нем будут сохранены две части данных, но состояние привязки другое. Допустим, у нас 50 миллионов клиентов, 5 видов бизнеса, и у каждого клиента в среднем по 2 карты, тогда объем данных этой таблицы достигнет умопомрачительных 500 млн. На самом деле количество пользователей в нашей системе не превысило одного миллиона. , Больше нет. Данные в базе данных MySQL хранятся на диске в виде файлов и по умолчанию помещаются в каталог /mysql/data (вы можете просмотреть его через каталог данных в my.cnf).Таблица в основном соответствует трем файлам, один хранится в frm. Для структуры таблицы один — myd для хранения данных таблицы, а другой — myi для хранения индекса таблицы. Эти три файла очень большие, особенно файл .myd, который почти 5G. Первая оптимизация разделов выполняется ниже.Существует четыре метода разделения, поддерживаемые MySQL:

В нашем проекте раздел диапазона и раздел списка не используются.Если раздел диапазона или списка основан на номере привязки, то номер привязки не имеет фактического бизнес-значения и не может быть запрошен через него.Поэтому у нас остается ХЭШ раздел и KEY разделены, а раздел HASH поддерживает только разделение столбцов типа int и является одним из них. Глядя на структуру таблицы в нашей библиотеке, мы обнаружили, что ни один из столбцов не имеет тип int.Как сделать секционирование? Вы можете добавить столбец, привязать столбец времени, установить для этого столбца тип int, а затем разбить его в соответствии со временем привязки и назначить пользователей, привязанных каждый день к одной и той же области. После этой оптимизации наша вставка стала намного быстрее, но запрос по-прежнему очень медленный.Почему, потому что при выполнении запроса мы запрашиваем только на основе банковской карты или идентификационного номера, а не на основе времени, что эквивалентно каждому запросу, MySQL снова запросит все секционированные таблицы.

Затем провести оптимизацию по второй схеме.Поскольку хэш-разбиение и разбиение ключа требуют, чтобы один из столбцов был типа int, можно создать список типа int для разбиения. Анализ показал, что в строке цифр на банковской карте есть секрет. Банковские карты обычно представляют собой строки чисел от 16 до 19 цифр.Возможно ли нам взять одну из них и использовать ее в качестве раздела таблицы?Проведя анализ, мы обнаружили, что в этой строке чисел действительно есть случайная цифра от 0 до 9. Генерируется, разная длина строки карты, эта цифра разная, ни в коем случае не последняя цифра, последняя цифра вообще контрольная цифра, не случайная. Наша недавно разработанная схема выполняет КЛЮЧЕВОЕ разбиение на основе номера банковской карты + случайный бит.Каждый раз, когда выполняется запрос, случайное число бит извлекается путем вычисления, и номер карты добавляется для совместного запроса для достижения цели запроса на разделение, которое Да, после секционирования созданный индекс также должен быть столбцом секционирования, иначе MySQL все равно будет запрашивать данные во всех секционированных таблицах. То проблема запроса отношения привязки через номер банковской карты решена, то как запросить отношение привязки через номер сертификата. Как упоминалось ранее, индексация должна выполняться по ключу секции, иначе это приведет к полному сканированию таблицы.

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

Чтобы решить эту проблему, мы разделили таблицу отношений привязки номера сертификата на две части, одна из которых предназначена для сохранения номера сертификата типа ID-карты, а другая таблица используется для сохранения номера сертификата других типов сертификатов. В таблице отношений привязки сертификатов мы разделяем количество месяцев в идентификационном номере как ключи раздела и сохраняем идентификационные номера клиентов, родившихся в один и тот же месяц в той же области, которая разделена на 12 областей, и другие типы сертификатов. разделены на 12 областей., а объем данных не превышает 100 000, поэтому нет необходимости в разбиении. Таким образом, каждый раз, когда вы делаете запрос, сначала определите, к какой таблице следует обратиться, на основе типа сертификата, а затем вычислите ключ секции для запроса.

После того, как дизайн раздела сделан, когда данные 20 миллионов пользователей сохранены, файл сохранения данных таблицы банковских карт разделен на 10 небольших файлов, а файл сохранения данных таблицы сертификатов разделен на 12 небольших файлов, которые решает проблемы этих двух запросов. Остается вопрос, что делать с бизнес-номером? У клиента есть несколько контрактных бизнесов, и как их сохранить. В настоящее время схема раздела не подходит, ее нужно использовать схема раздельного стола.

Подбиблиотека и подтаблица

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

Али TDDL, DRDS и cobar,
sharding-jdbc от JD Finance;
MyCAT для межорганизационных отношений;
360 Атлас;
зебра Мейтуана;
Другие компании, такие как NetEase, 58 и JD.com, имеют промежуточное программное обеспечение собственной разработки.

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

клиентский режим

прокси-режим

Будь то режим клиента или режим прокси, несколько основных шагов одинаковы: синтаксический анализ SQL, перезапись, маршрутизация, выполнение и слияние результатов. Лично я предпочитаю использовать клиентский режим, который имеет простую архитектуру, небольшую потерю производительности и низкие затраты на эксплуатацию и обслуживание. Если вы добавляете в проект mycat или cobar, их автономный режим не может гарантировать надежность.Как только машина выходит из строя, служба становится недоступной.Вы должны внедрить HAProxy, чтобы внедрить его решение по развертыванию кластера с высокой доступностью.Чтобы решить проблему проблема с HAProxyпроблема высокой доступности иНеобходимо использовать Keepalived для достижения.

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

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

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

вертикальная библиотека

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

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

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

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

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

Настройки таблицы разделов, как правилоРазделение по столбцу индекса запроса. Например, для расходомера A запрос должен быть основан на номере мобильного телефона и номере партии, поэтому при создании раздела мы выбираем разделение по номеру мобильного телефона и номеру партии. После этой настройки , Запрос будет проходить через индекс.Каждый раз, когда вы запрашиваете MySQL, он будет рассчитываться в соответствии с условиями запроса.Данные будут попадать в этот раздел, и вы можете получить их напрямую из соответствующей таблицы разделов, избегая полного сканирования таблицы.

Для данных, которые имеют ежедневный поток более 100 миллионов, конечно, необходимо выполнить миграцию данных исторической таблицы. Заказчик требует, чтобы данные о водопроводной воде хранились в течение полугода, а некоторые ключевые данные о водопроводной воде должны храниться в течение одного года. Удалить данные невозможно, и убежать невозможно, хотя в тот момент у меня были позывы удалить данные и убежать. На самом деле невозможно удалить данные сразу. Сначала устраняется неуклюжая производительность удаления, а усечение не намного быстрее. Мы приняли более оригинальный метод. Конкретные шаги заключаются в следующем:

Создайте временную таблицу, точно такую же, как исходная таблица 1. Создайте таблицу test_a_serial_1, например test_a_serial;
Назовите исходную таблицу как временную таблицу 2 alter table test_a_serial rename test_a_serial_{date};
Измените временную таблицу 1 на исходную таблицу. сохранить данные и части вчерашнего дня Для сегодняшних данных дата во временной таблице 2 имени является датой вчерашнего дня, полученной расчетным путем, временная таблица 2 со вчерашней датой будет генерироваться каждый день, а данные в каждой таблице о 10 миллионов.
Операция переноса исторических данных из таблицы текущего дня на расходомер вчерашнего дня вся обрабатывается временными задачами.Временная задача обычно запускается после 12:00.Эта операция выполняется за несколько секунд, и их может быть несколько штук данных попадают в таблицу дня. Поэтому, наконец, нам нужно вставить исторические данные потока из таблицы текущего дня в таблицу вчерашнего дня, вставить в test_a_serial_{date}(cloumn1,cloumn2....) select(cloumn1,cloumn2....) из test_a_serial, где ЛЕВЫЙ (создать_время, 8)> CONCAT (дата); зафиксировать;

Таким образом, миграция потоковых данных завершена;

По бизнес-потребностям некоторые бизнес-данные нужно хранить полгода, а если больше полугода, то они будут удалены, при удалении можно отфильтровать поток более полугода по _{ date} в имени таблицы и удалить таблицу напрямую;

Через полгода будет более 180 таблиц для бизнес-расходомера, и в каждой таблице по 20 таблиц разделов, так как делать запросы? Поскольку у нашего проекта нет особо высоких требований к запросу конвейеров в реальном времени, когда мы делаем запрос, мы выполняем запрос маршрута в соответствии с временным интервалом запроса.

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

Выше приведена соответствующая оптимизация на уровне базы данных в случае большого объема данных.Таблица со 100 миллионами в день, после разделения данных в каждом разделе таблицы составляет около 5 миллионов. После этого дизайна мы также столкнулись с некоторыми другими проблемами, такими как статистическая проблема проточной воды.При таком большом количестве данных в проекте более 100 статистических измерений.Даже если считать 100 раз в день, это Это очень сложно. Мы приняли вычисления в реальном времени. Статистический метод решает эту проблему. Связанные технологии включают вычисления в реальном времени, очереди сообщений, промежуточное программное обеспечение кэширования и т. д. Пожалуйста, с нетерпением ждите этого!

читать далее

Подробное объяснение кроссплатформенной архитектуры оплаты WeChat

Почему Nginx настолько быстр, что вообще не может остановиться?

Как подготовиться к техническому собеседованию в апреле? Я сказал вам от интервьюера

Убить Почтальона! Плагин IDEA настолько полезен...

Вопросы интервью Meituan: подробное объяснение пула потоков Java ThreadPool