|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Есть таблица на 30 млн. записей:
CREATE TABLE stat (
polltime int unsigned not null default 0,
device int unsigned not null default 0,
value1 float(12,2),
value2 float(12,2),
...
value9 float(12,2),
error varchar(255),
PRIMARY KEY(device,polltime),
KEY(polltime)
) ENGINE=MyISAM DEFAULT;
Примерно 50% записей в таблице содержат в поле error => описание ошибки
'ERROR: XXXXXXXXXXXXXX XXXX', а поля value1 .... value9 равны NULL.
Вторая половина наоборот содержит поля value1.... value9 и поле error
равное NULL.
Прикладная задача:
Формирование XML по следующему запросу device = X and polltime > Y.
<dev name={device} time={polltime} value1= ..... value9= ... error={error}/>
Имеет ли смысл ли разделить таблицу STAT на две таблицы DATA и ERROR следующего вида:
CREATE TABLE statdata (
polltime int unsigned not null default 0,
device int unsigned not null default 0,
value1 float(12,2),
value2 float(12,2),
...
value9 float(12,2),
PRIMARY KEY(device,polltime),
KEY(polltime)
) ENGINE=MyISAM DEFAULT /* ТУТ ДОБАВИТЬ ROW_FORMAT=FIXED */;
CREATE TABLE staterror (
polltime int unsigned not null default 0,
device int unsigned not null default 0,
error varchar(255),
PRIMARY KEY(device,polltime),
KEY(polltime)
) ENGINE=MyISAM DEFAULT;
Те поля переменной длины выносятся в отдельную таблицу, поля постоянной длины во вторую...
Имеет ли какой-то прикладной смысл разделить таблицу на две части ?
Сократиться ли общии размер БД ?
Будут ли два SELECT-запроса выполнятся медленее (быстрее) на двух таблицах ?
Будут ли два INSERT-запроса выполнятся медленее (быстрее) на двух таблицах ?
Имеет ли смысл использовать ROW_FORMAT= FIXED для таблицы DATA (ведь размер каждой записи имеет фиксированную длину) ? Явлется ли это подсказкой для MYSQL, что выделение буфферов, вычисление размера записи , .... производить не требуется ?
Неактивен
федя написал:
Сократиться ли общии размер БД ?
Да. Мегов на триста.
федя написал:
Будут ли два SELECT-запроса выполнятся медленее (быстрее) на двух таблицах ?
Имхо, не принципиально.
федя написал:
Будут ли два INSERT-запроса выполнятся медленее (быстрее) на двух таблицах ?
Учитывая характер ваших данных (добавляются или value, или error), против одного insert в старую таблицу, будет один в одну из новых (которая в два раза меньше исходной). Т.е. сам insert быстрее, но расходы на определение в какую таблицу делать вставку.
федя написал:
Имеет ли смысл использовать ROW_FORMAT= FIXED для таблицы DATA (ведь размер каждой записи имеет фиксированную длину) ? Явлется ли это подсказкой для MYSQL, что выделение буфферов, вычисление размера записи , .... производить не требуется ?
Не понял о каких буферах идет речь. Выигрыш ROW_FORMAT= FIXED будет в том, что сервер изначально знает с какого места в файле идет нужная строка, т.е. быстрее доступ к данным.
федя написал:
Имеет ли какой-то прикладной смысл разделить таблицу на две части ?
Имхо, экономия на спичках, но возможно именно она вам и нужна.
Неактивен
В общем реальный расклад такой:
vasya написал:
Да. Мегов на триста.
Каждые ВОСЕМЬ значение NULL занимаю 1 Байт.
На моем примере value 10 штук, те 2 байта на запись...
Сокращение 15 млн записей * 2 байт = 30 Метров.
Сокращение SELECT и INSERT было ~0.8 сек в большую таблицу, стало ~0.4 + 0.2 сек в две которые ровно в два раза меньше первой.
vasya написал:
федя написал:
Имеет ли смысл использовать ROW_FORMAT= FIXED для таблицы DATA (ведь размер каждой записи имеет фиксированную длину) ? Явлется ли это подсказкой для MYSQL, что выделение буфферов, вычисление размера записи , .... производить не требуется ?
Не понял о каких буферах идет речь. Выигрыш ROW_FORMAT= FIXED будет в том, что сервер изначально знает с какого места в файле идет нужная строка, т.е. быстрее доступ к данным.
Совершено верно, в случае с FIXED
a) Если правильно разработать формат таблицы, то доступ к данным можно организовать по НОМЕРУ ЗАПИСИ, минуя использования индексов.
К примеру, если я точно знаю, что имеется ровно 1000 записей на каждый из 30000 объектов, то можно заранее создать таблицу на 30 млн записей. А затем выполнять UPDATE значении необходимой записи. При этом перестройки индекса вообще не должно происходить, так как изменяются поля которые не участвуют в Индексах.
б) Мне кажется, что извлечение полей из записей СУБД созданных в формате DYNAMIC имеет "вычислительную сложность" на порядок большую чем при использовании формата FIXED.
К примеру, у нас имеется таблица в которой всего три поля
К - ключ, A - VARCHAR(10), B - VARCHAR(10)
Мы ее записываем двумя способами FIXED и DYNAMIC.
В первом случае запись будет выглядеть примерно вот так:
ККККAAAAAAAAAABBBBBBBBBB
А во втором:
КККК[ДАННЫЕ A и B]
Для извлечения поля B из таблицы FIXED достаточно тупо извлекать в поле длинной 10 байт значения с 15-того по 25 байт. Типа
buffer = READBUFFER();
for (i = 1; i < limit; i++) {
strcpy (ROWDATA[i], buffer + (i-1) * длина_записи; 10);
}
Для извлечения поля B в случае формата DYNAMIC, необходимо как минимум найти конец строки A, длину строки B
Предположим, что СУБД хранит строки методом завершающего '\0', тогда мы вынуждены провести от 1 до 15 сравнении для нахождения каждого конца строки A, затем 15 сравнении для нахождения конца строки B.
Предположим, что хранение строки производится следующим образом:
<длина строки>+<строка>.
В этом случае алгоритм по "сложности сравним" с FIXED.
Но
1. требуется дополнительный БАЙт для хранения длинны строки.
2. Если у нас в записи будет 25 полей VARCHAR, то необходимо 25 раз узнать длину строки, а уже затем спозиционироваться в поле B
В случае записи в такую таблицу все еще сложнее.
Представим себе, что поле A было записано '111', а в поле B записано 'YYYYYYYYYYYYY1'.
ТРИ ЗАПИСИ:
111xxxYYYYYYYYYYYYY1xx
222xxxYYYYYYY2xx
333xxxYYYYYYYY3xx
ВОТ так они примерно в выгялядят в файле БД:
111xxxYYYYYYYYYYYYY1xx|222xxxYYYYYYY2xx|333xxxYYYYYYYY3xx
xxx - это заполнитель (СУБД резвирует его) именно для случая UPDATE "строки из трех" на строку длиной до 6 байтов.
Теперь нам необходимо в поле А записать XXXXXXXXXXXXXXXXXX. Для этого нам необходимо "сдвинуть поле B", так как зарезервированно места (xxx + xx - заполнитель) нам не хватает для записи такой длинной строки.
111xxxYYYYYYYYYYYYY1xx|222xxxYYYYYYY2xx|333xxxYYYYYYYY3xx
XXXXXXXXXXXXXXXXXXYYYYYYYYYYYYY1|222xxxYYYYYYY2xx|333xxxYYYYYYYY3xx
И мы вынуждены "двигать" СЛЕДУЮЩУЮ ЗАПИСЬ, а потом еще и СЛЕДУЮЩУЮ.... Или переместить это поле
в другое место,
xxxxxxxxxxxxxxxxxxxxxxxxxx - Освободили
222xxxYYYYYYY2xx
333xxxYYYYYYYY3xx
111xxxYYYYYYYYYYYYY1xx - переместили
при этом ПЕРЕПИСАТЬ Index, так как ссылка на начало записи изменена, хотя сам индекс и не был задет операцией UPDATE.
ps.
В общем хотелось бы узнать... Проводил ли кто-нибудь эксперименты (сравнения) FIXED и DYNAMIC ?
Теоретические расскладки я привел 
Отредактированно федя (18.11.2012 11:20:05)
Неактивен
федя написал:
vasya написал:
Да. Мегов на триста.
Каждые ВОСЕМЬ значение NULL занимаю 1 Байт.
На моем примере value 10 штук, те 2 байта на запись...
Сокращение 15 млн записей * 2 байт = 30 Метров.
Согласен, был не прав. Каждый столбец, допускающий NULL, требует 1 дополнительный бит. Величина округляется до байта в большую сторону. Т.е. как вы и написали 2 байта на строку, но на 30 млн строк.
федя написал:
Мне кажется, что извлечение полей из записей СУБД созданных в формате DYNAMIC имеет "вычислительную сложность" на порядок большую чем при использовании формата FIXED.
Нет, это вы очень печальную картину нарисовали.
DYNAMIC имеет более сложный заголовок строки, в котором в числе прочего хранится и актуальная длина данных для каждого столбца.
Посмотрите
http://dev.mysql.com/doc/internals/en/m … cture.html
федя написал:
В общем хотелось бы узнать... Проводил ли кто-нибудь эксперименты (сравнения) FIXED и DYNAMIC ?
Наверняка, но мне не попадались, что косвенно указывает на незначительную разницу
Неактивен
федя написал:
Совершено верно, в случае с FIXED
a) Если правильно разработать формат таблицы, то доступ к данным можно организовать по НОМЕРУ ЗАПИСИ, минуя использования индексов.
К примеру, если я точно знаю, что имеется ровно 1000 записей на каждый из 30000 объектов, то можно заранее создать таблицу на 30 млн записей. А затем выполнять UPDATE значении необходимой записи. При этом перестройки индекса вообще не должно происходить, так как изменяются поля которые не участвуют в Индексах.
Тут у вас противоречие. Для того, чтобы узнать номер записи по условию (device = X and polltime > Y) вам как раз и потребуется индекс.
федя написал:
при этом ПЕРЕПИСАТЬ Index, так как ссылка на начало записи изменена, хотя сам индекс и не был задет операцией UPDATE.
Даже если и потребуется переписать указатель (DYNAMIC позволяет хранить запись в нескольких частях), то это операция быстрая. Это ведь не перестройка индекса, которая происходит для обеспечения балансировки дерева.
В "Материалы и ссылки по MySQL" есть ссылка на демонстраицю работы B-Tree
Неактивен
Имхо, если единственный сценарий использования - дай мне протокол по девайсу [в определенном промежутке], то разбивать не стоит, если же нужны будут сценарии "дай мне протокол ошибок [в определенном промежутке]" то я бы разбил на две таблицы, потому что так и логичнее и быстрее. Конечное решение лежит в соотношении частоты использования этих двух типов
Отредактированно Shopen (19.11.2012 23:33:55)
Неактивен
Страниц: 1
