Архитектура MySQL таблицы: строки или числа

Джентльмены, у меня дилемма.

Реорганизую таблицу в БД, в этой таблице есть столбец (далее services) который используется для хранения статуса объекта в каких-то категориях. Статус это цифра от 0 до 3. Количество категорий может меняться (сейчас их 11).

То есть, для примера:
Объект 1:
Категория 1 - Статус 3
Категория 2 - Статус 1
Категория 3 - Статус 2
...

Объект 2:
Категория 1 - Статус 1
Категория 2 - Статус 3
Категория 3 - Статус 2
...

На данный момент для хранения статусов используется VARCHAR. То есть, просто 11 символов, каждый из которых представляет статус категории с номером равным позиции этого символа.
...
(1, '31200000000'),
(2, '13200000000')
...

Очень часто приходится проверять статус категории. Для поиска используется LIKE.

Есть мысль хранить эти статусы в BIGINT используя четырехричную систему счисления. Иными словами, каждый разряд четырехричного числа будет хранить статус категории.
...
(1, 39), #3910 = 2134
(2, 45) #4510 = 2314
...

Для определения статуса будет использоваться формула:
services mod 4service + 1 div 4service

Где service это индекс категории, отчет с нуля.

Я провел небольшой тест, на идентичных таблицах из 13 тысяч записей. Используя следующие запросы:
/* Запрос 1 */ SELECT * FROM `test_b` WHERE `services` LIKE '___2%';
/* Запрос 2 */ SELECT * FROM `test_a` WHERE (`services` MOD 256) DIV 64 = 2;

Вот результаты для 10 000 выборок:
#Запрос 1Запрос 2
0128.21с27.31с
0227.18с27.26с
0328.31с27.56с
0429.25с27.14с
0529.18с27.47с
0627.60c27.47c
0727.74c26.79c
0827.43c26.95c
0927.99c26.52c
1028.80c28.06c

Судя по этим тестам вариант с BIGINT выигрывает 3.2% в скорости. Могу предположить, что при увеличении объемов таблицы, прирост будет только увеличиваться.

Технология тестов
Замерялось все вот этим php кодом:
$time_taken = microtime(true);	
for($i = 0; $i < 10000; $i++)
{
	mysql_query("Запрос");
}
$time_taken = microtime(true) - $time_taken;


Так же, для удобства, была написана хранимая функция:
DROP FUNCTION IF EXISTS `GET_SERVICE_STATE`;
DELIMITER //
CREATE FUNCTION `GET_SERVICE_STATE`(`services` BIGINT, `service` BIGINT)
RETURNS TINYINT
BEGIN
	RETURN (`services` MOD (`service` * 4)) DIV `service`;
END //
DELIMITER ;

Но результаты ее использования оказалось плачевны.
SELECT * FROM `test_a` WHERE GET_SERVICE_STATE(`services`, 64) = 2;
Работает в ~20 раз медленнее исходного запроса. Не могу понять, это из за дополнительного умножения или из за самого вызова функции?

Для себя я выявил следующие плюсы и минусы способа с BIGINT над способом с VARCHAR:
+ Экономия 3 байт на каждую запись в таблице
+ Прирост скорости, хоть и не значительный
+ Нет необходимости изменять структуру таблицы при увеличении количества категорий

- Ограничение на количество категорий - 31 штука
- В самой БД визуально не видно состояния категорий

Пока что, я склоняюсь в сторону способа с BIGINT. Пожалуйста, помогите сделать правильный выбор. Или подскажите способ более эффективный.
  • Вопрос задан
  • 3380 просмотров
Пригласить эксперта
Ответы на вопрос 5
NorthDakota
@NorthDakota
PHP програмист
Мне кажется у вас неправильно организована структура данных.
Могу предложить хранить статусы в отдельной табличке (cat_statuses).
+--------------------------------------+
|   id   |    cat_id    |    status    | 
+--------------------------------------+


Где status - TINYINT
cat_id - INT

Далее просто использовать RIGHT JOIN:

SELECT * FROM `cat_statuses` as cs 
RIGHT JOIN `test_b` as tb ON (as.cat_id = tb.services)
WHERE cs.cat_id=4;
Ответ написан
conf
@conf
Ruby developer
Насколько я понял у вас есть объекты, у которых много категорий, у которых много статусов.
Я бы разделил это на 4 таблицы: объекты, категории, статусы и таблица связей (object_id, category_id, status_id) - все 3 поля кстати можно объединить в составной первичный ключ.
Т.к. статусов мало, можно взять TINYINT в качестве первичного ключа.
4ричную систему делать не надо - пожалейте мозг того, кто это будет дальше поддерживать.
Таким образом сможете легко сделать запрос с джоинами по нужным таблицам и все будет работать очень быстро.
Да, и проводить тесты на скорость на основании 13000 рядов - это несерьезно. Отключите кэш запросов mysql (или просто SELECT SQL_NOCACHE <тут ваш запрос> ) и сделайте хотя бы миллион рядов (чтобы таблица в память не влезала), тогда будут результаты поинтереснее. Хранимые процедуры кстати отрабатывают намного медленнее чем прямые запросы.
Почитайте книгу High Performance Mysql - очень прочищает мозги.
Ответ написан
Rsa97
@Rsa97
Для правильного вопроса надо знать половину ответа
Пусть в вашей таблице объектов есть поле objectId - идентификатор объекта. Создадим дополнительную таблицу, связанную по полю objectId с таблицей объектов. Таблица содержит поля номера сервиса serviceNum и его статуса serviceStatus.
CREATE TABLE `service_states` 
  `objectId` BIGINT NOT NULL DEFAULT '0',
  `serviceNum` INT NOT NULL DEFAULT '0',
  `serviceState` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`objectId`,`serviceNum`),
  KEY `byServiceState` (`serviceState`),
  KEY `byServiceNum` (`serviceNum`))
ENGINE=InnoDB DEFAULT CHARSET=utf8';

Запрос всех объектов, аналогичный вашему, будет выглядеть как
SELECT * FROM `test_b` as tb
RIGHT JOIN `service_states` as ss USING(objectId)
WHERE ss.serviceNum = 3 AND ss.serviceState = 2;
Ответ написан
Если Вам так не нравится вариант с JOIN-ами, то попробуйте использовать 10 систему счисления (пусть немного избыточно, зато будет запас по статусам). Проверка сведется к вычислениям критериев на стороне PHP:
$service = 3; // от 1 до 11
$status = 2; // от 1 до 4
$min = $status * pow(10, $service);
$max = ($status+1) * pow(10, $service);

И последующим запросом к MySQL:
SELECT * FROM `test` WHERE `service` >= $min AND `service` < $max

При этом для производительности надо бы добавить индекс по колонке service:
ALTER TABLE `test` ADD INDEX `test` (`service`)
P.S. возможно что-то не так с формулой $min и max, но сама идея - использовать >, < (или BETWEEN) для проверки нужного разряда.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы