Что в итоге вызовет меньше нагрузки? внешние ключи vs объём?

@BeginnerCoderCS · Регистрация: 10.01.2020

Студворк — интернет-сервис помощи студентам

Привет товарищи!

Сбор метрики в БД MySQL.

Представим что есть метрика которая состоит из OS и Browser.

1. Можно сделать обычный табличный способ

SQL
1
2
3
4
5
6
CREATE TABLE `Metrics` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `OS` VARCHAR(20) NOT NULL,
  `Browser` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`Id`)
);

2. А можно сделать как положено со связями

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE `Metrics` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `OSId` INT(11) NOT NULL,
  `BrowserId` INT(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `FK_Metrics_OSId` (`OSId`),
  KEY `FK_Metrics_BrowserId` (`BrowserId`),
  CONSTRAINT `FK_Metrics_OSId` FOREIGN KEY (`OSId`) REFERENCES `OS` (`Id`),
  CONSTRAINT `FK_Metrics_BrowserId` FOREIGN KEY (`BrowserId`) REFERENCES `Browser` (`Id`)
);
 
CREATE TABLE `OS` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_OS_Name` (`Name`)
);
 
CREATE TABLE `Browser` (
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Browser_Name` (`Name`)
);

2 вариант считается правильным.

Но вот у меня вопрос, есть ли смысл этих связей, если количество OS и Browser ограничено?
Ну то есть в таблицах OS и Browser будет штук по 10 записей. Нет у нас миллионов операционных систем и браузеров.

Выборки по OS или Browser не будет.

Что будет дороже внешние ключи или объём данных без внешних ключей?

@Usaga · 18.01.2023, 04:39

Сообщение от BeginnerCoderCS

Что будет дороже внешние ключи или объём данных без внешних ключей?

Смотря в каком контексте вопрос. И да, "дороже" и "дорого" разные вещи. В любой непонятной ситуации нужно делать правильно (№2), а уже если будет обоснованно, то прибегать к оптимизациям (№1).

Вообще, если использовать тупой, наивный подход, то вариант №1 будет затратнее, так как вам надо сначала найти записи в подчинённых таблицах (браузер и ОС), а потом внести запись в основную с ключами нужными. Если в подчинённых ничего нет, то придётся сначала в них вносить записи.

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

В общем, старайтесь придерживать нормализации. Да и на будущее задел будет. Если понадобится выборка по браузерам и ОС, то это будет эффективнее, нежели денармализованный вариант.

@kotelok · 18.01.2023, 08:40

Для одного сервиса сейчас использую второй вариант без внешних ключей. Изначально вообще была только таблица 'Metrics' с ID. Потом уже, чисто для удобства (для отчётов), появились таблицы с текстовыми описаниями этих ID. Если даже для какого-то ID по какой-то причине не обнаруживается описания, на функционале приложения это никак не сказывается (а описание можно добавить в любой момент).

@BeginnerCoderCS · 18.01.2023, 10:29 **[ТС]**

Сообщение от Usaga

И да, "дороже" и "дорого" разные вещи.

Имел в виду что в итоге будет создавать больше нагрузку на СУБД, но разницу и сейчас не вижу )

Сообщение от Usaga

В любой непонятной ситуации нужно делать правильно

Хороший совет (не сарказм).

Сообщение от Usaga

В любой непонятной ситуации нужно делать правильно (№2), а уже если будет обоснованно, то прибегать к оптимизациям (№1).

В этом и сложность обосновать переход к денормализации.

@Andrey-MSK · 18.01.2023, 10:34

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

Сообщение от BeginnerCoderCS

В этом и сложность обосновать переход к денормализации.

Скорость работы запроса - быстрее выбрать из одной таблицы, чем из 10-ти... Но всегда есть НО

@Usaga · 18.01.2023, 10:54

Сообщение от BeginnerCoderCS

В этом и сложность обосновать переход к денормализации.

Обоснование одно - производительность. В вашем случае, разницы в производительности между подходами вы не заметите. Поэтому лучше выбрать №2.

@BeginnerCoderCS · 18.01.2023, 14:14 **[ТС]**

Подтвердите или опровергните выводы мои.

Допустим мне нужно хранить сокращение языков (en|ru|ua|kz).

1.1. В этом случае лучше в таблицу `Metrics` добавить непосредственно запись `lang varchar(2)` который будет занимать 3 байта, чем делать отдельную таблицу с внешним ключом, который будет занимать 4 байта.

1.2. Но если нужна выборка по языку, то всё-таки лучше использовать внешний ключ.

Но если нужно хранить страну в `Metrics` (приблизительно 251 штука)

2.1. Лучше использовать внешний ключ, так как длина страны может быть большой (Кита́йская Наро́дная Респу́блика).

2.2. Но если выборка не нужна, есть ли плюсы хранения в самой `Metrics`

@nedel · 18.01.2023, 15:45

Сообщение от BeginnerCoderCS

Но если нужна выборка по языку, то всё-таки лучше использовать внешний ключ.

еще существует такая штука, как индекс

@Usaga · 18.01.2023, 16:28

BeginnerCoderCS, сокращение языка и название страны - справочники. Справочникам свойственно расширять набор атрибутов и корректировать значения. Поэтому внешний ключ (читай: ссылка) на таблицу справочника гибче, чем само значение в исходной таблице. Сегодня вам надо просто сокращения языков, а завтра к ним добавить ещё какие-нибудь особенности. К примеру полное название и формат даты. Что вы будете делать со своими VARCHAR(2)?

По поводу 3 байта vs 4 байта. Записи на диске один чёрт хранятся в виде страниц, часто фиксированного размера. Поля выравниваются по краю 4 байт, для оптимизации доступа в ОЗУ (куда страница попадает с диска). Вот эти вот экономии в единицах байт больше вредят, чем пользы приносят.

Про выборку я вообще не понял. Если вам нужна будет выборка по языку RU, то сканировать всю таблицу и сравнивать каждое поле LANG со строкой "RU" не будет сильно быстрее однократного поиска в таблице-справочнике, с последующим поиском int'а в исходной таблице. Да ещё и индексы никто не отменял...

@BeginnerCoderCS · 18.01.2023, 16:45 **[ТС]**

Сообщение от nedel

еще существует такая штука, как индекс

Сообщение от Usaga

Да ещё и индексы никто не отменял...

Но ведь индекс поля VARCHAR будет медленнее работать чем ForeignKey INT?

@Andrey-MSK · 18.01.2023, 16:49

Сообщение от BeginnerCoderCS

Но ведь индекс поля VARCHAR будет медленнее работать чем ForeignKey INT?

Имеется ввиду индекс по внешнему ключу.

@Usaga · 18.01.2023, 17:02

Сообщение от BeginnerCoderCS

Но ведь индекс поля VARCHAR будет медленнее работать чем ForeignKey INT?

Эм. С одной стороны - нет, не медленнее. С другой, где идёт речь о неких сокращениях стран, индекс по VARCHAR вообще может не использоваться, ибо селективность у него никакущая.

@Andrey-MSK · 18.01.2023, 17:15

BeginnerCoderCS, Не знаю как в MySQL, но в SSMS для MS SQL Server есть анализатор запросов, который показывает рекомендуемые отсутствующие индексы для запроса. Оченно удобная штука, если нужно создать необходимый индекс о котором даже не подозреваешь.

Новые блоги и статьи Все статьи Все блоги /
Уведомление о неверно выбранном значении справочника Maks 06.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "НарядПутевка", разработанного в конфигурации КА2. Задача: уведомлять пользователя, если в документе выбран неверный склад. . .	Установка Qt Creator для C и C++: ставим среду, CMake и MinGW без фреймворка Qt 8Observer8 05.04.2026 Среду разработки Qt Creator можно установить без фреймворка Qt. Есть отдельный репозиторий для этой среды: https:/ / github. com/ qt-creator/ qt-creator, где можно скачать установщик, на вкладке Releases:. . .	AkelPad-скрипты, структуры, и немного лирики.. testuser2 05.04.2026 Такая программа, как AkelPad существует уже давно, и также давно существуют скрипты под нее. Тем не менее, прога живет, периодически что-то не спеша дополняется, улучшается. Что меня в первую очередь. . .	Отображение реквизитов в документе по условию и контроль их заполнения Maks 04.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "ПланированиеСпецтехники", разработанного в конфигурации КА2. Данный документ берёт данные из другого нетипового документа. . .
Фото всей Земли с борта корабля Orion миссии Artemis II kumehtar 04.04.2026 Это первое подобное фото сделанное человеком за 50 лет. Снимок называют новым вариантом легендарной фотографии «The Blue Marble» 1972 года, сделанной с борта корабля «Аполлон-17». Новое фото. . .	Вывод диалогового окна перед закрытием, если документ не проведён Maks 04.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: реализовать программный контроль на предмет проведения документа. . .	Программный контроль заполнения реквизитов табличной части документа Maks 02.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: 1. Реализовать контроль заполнения реквизита. . .	wmic не является внутренней или внешней командой Maks 02.04.2026 Решение: DISM / Online / Add-Capability / CapabilityName:WMIC~~~~ Отсюда: https:/ / winitpro. ru/ index. php/ 2025/ 02/ 14/ komanda-wmic-ne-naydena/

@kotelok 1341 / 920 / 265 Регистрация: 08.08.2014 Сообщений: 2,768
	18.01.2023, 08:40
	Для одного сервиса сейчас использую второй вариант без внешних ключей. Изначально вообще была только таблица 'Metrics' с ID. Потом уже, чисто для удобства (для отчётов), появились таблицы с текстовыми описаниями этих ID. Если даже для какого-то ID по какой-то причине не обнаруживается описания, на функционале приложения это никак не сказывается (а описание можно добавить в любой момент). 1

@BeginnerCoderCS 9 / 6 / 3 Регистрация: 10.01.2020 Сообщений: 330
	18.01.2023, 14:14 [ТС]
	Подтвердите или опровергните выводы мои. Допустим мне нужно хранить сокращение языков (en\|ru\|ua\|kz). 1.1. В этом случае лучше в таблицу `Metrics` добавить непосредственно запись `lang varchar(2)` который будет занимать 3 байта, чем делать отдельную таблицу с внешним ключом, который будет занимать 4 байта. 1.2. Но если нужна выборка по языку, то всё-таки лучше использовать внешний ключ. Но если нужно хранить страну в `Metrics` (приблизительно 251 штука) 2.1. Лучше использовать внешний ключ, так как длина страны может быть большой (Кита́йская Наро́дная Респу́блика). 2.2. Но если выборка не нужна, есть ли плюсы хранения в самой `Metrics` 0

@Usaga 14314 / 9399 / 1355 Регистрация: 21.01.2016 Сообщений: 35,439
	18.01.2023, 16:28
	BeginnerCoderCS, сокращение языка и название страны - справочники. Справочникам свойственно расширять набор атрибутов и корректировать значения. Поэтому внешний ключ (читай: ссылка) на таблицу справочника гибче, чем само значение в исходной таблице. Сегодня вам надо просто сокращения языков, а завтра к ним добавить ещё какие-нибудь особенности. К примеру полное название и формат даты. Что вы будете делать со своими VARCHAR(2)? По поводу 3 байта vs 4 байта. Записи на диске один чёрт хранятся в виде страниц, часто фиксированного размера. Поля выравниваются по краю 4 байт, для оптимизации доступа в ОЗУ (куда страница попадает с диска). Вот эти вот экономии в единицах байт больше вредят, чем пользы приносят. Про выборку я вообще не понял. Если вам нужна будет выборка по языку RU, то сканировать всю таблицу и сравнивать каждое поле LANG со строкой "RU" не будет сильно быстрее однократного поиска в таблице-справочнике, с последующим поиском int'а в исходной таблице. Да ещё и индексы никто не отменял... 0

@Andrey-MSK 3360 / 2246 / 388 Регистрация: 14.08.2018 Сообщений: 7,588 Записей в блоге: 4
	18.01.2023, 17:15
	BeginnerCoderCS, Не знаю как в MySQL, но в SSMS для MS SQL Server есть анализатор запросов, который показывает рекомендуемые отсутствующие индексы для запроса. Оченно удобная штука, если нужно создать необходимый индекс о котором даже не подозреваешь. 0