Форум пользователей MySQL

==== КОД ДЛЯ ГЕНЕРАЦИИ ДАННЫХ ===========
#!/usr/bin/perl                           
                                         
use POSIX qw/floor ceil/;                 
my $MAXCOL=100;                           
my $MAXROW=400000;                           
                                         
                                         
open(FHE, ">", "monEdata.sql");           
open(FHS, ">", "monSdata.sql");           
open(FHD, ">", "monDdata.sql");           
                                         
my $count = 1;                           
my $AX = {};                             
my $enuma = "`y` enum(\n";               
my $enuma = "enum (\n";                   
               
while (<>) {             
    chomp();             
    s/\;//g;             
    $AX[$count] = $_;   
    $enuma .= "'$_',\n";
    $count++;           
}                       
$enuma .= "'')";         
my $l = floor(rand($count-1));             
                                           
print FHE "DROP TABLE IF EXISTS xenum;\n"; 
print FHE "CREATE TABLE xenum (\n";         
print FHS "DROP TABLE IF EXISTS xestr;\n"; 
print FHS "CREATE TABLE xestr (\n";         
                                           
for ($i = 1; $i < $MAXCOL; $i++) {   
    print FHE "x$i $enuma,\n";       
    print FHS "x$i varchar(256),\n";
}                                   

print FHE "x$i $enuma\n";                                 
print FHE ") ENGINE=MyISAM DEFAULT CHARSET=latin1;\n";     
                                                           
print FHS "x$i varchar(256)\n";                           
print FHS ") ENGINE=MyISAM DEFAULT CHARSET=latin1;\n";     
                                                           
for ($j = 1; $j < $MAXROW; $j++)           
{                                           
    my $l = 1;                             
    print FHD "insert into xenum values (";
    for ($i = 1; $i < $MAXCOL; $i++) {     
        $l = floor(rand($count-1));         
        print FHD "'$AX[$l]',";             
    }                                       
    $l = floor(rand($count-1));             
    print FHD "'$AX[$l]');\n";             
}                                           

================================

Получились файлы:
DROP TABLE IF EXISTS xestr;   
CREATE TABLE xestr (           
x1 varchar(256),               
x2 varchar(256),               
x3 varchar(256),               
...
x99 varchar(256),                           
x100 varchar(256)                           
) ENGINE=MyISAM DEFAULT CHARSET=latin1;     

==============
DROP TABLE IF EXISTS xenum;       
CREATE TABLE xenum (               
x1 enum (                         
'fairy prion',                     
'fairy queen',                     
'...
x2 enum (                         
....
'field mouse',                             
'field mushroom',                         
'')                                       
) ENGINE=MyISAM DEFAULT CHARSET=latin1;   

Данные:
insert into xenum values ('fellow pupil','fashion book','fever therapy','far-back','false-dealing','fairy-ring mushroom','farm level','far-back','fast-darkening','far-flashing','fennel giant','field madder','few-celled','fellow mortal','field duck','feather trimmer','faro bank','fan pump','field control','fellow delegate','fen orchis','fat-reducing','faith-keeping','fault terrace','fatal-looking',
.......
И такой же второй:
insert into xestr values ('fellow pupil','fashion book','fever therapy','far-back','false-dealing','fairy-ring mushroom','farm level','far-back','fast-darkening','far-flashing','fennel giant','field madder','few-celled','fellow mortal','field duck','feather trimmer','faro bank','fan pump','field control','fellow delegate','fen orchis','fat-reducing','faith-keeping','fault terrace','fatal-looking',
.......

Запускаем Makefile:                                     
date                                                                       
Fri Nov 23 01:10:11 NOVT 2012                                                   
cat monDdata.sql | mysql feddy                                                 
date                                                                           
Fri Nov 23 01:17:09 NOVT 2012                                                   
(cat monDdata.sql | sed -e 's/ xenum/ xestr/g') | mysql feddy                   
date                                                                           
Fri Nov 23 01:18:27 NOVT 2012                                                   
(echo "show table status like 'xenum'\G" | mysql feddy;echo "show table status l
ike 'xestr'\G" | mysql feddy) | grep "Data_length"                             
    Data_length: 85199787                                                       
    Data_length: 529932556                                                     
date                                                                           
Fri Nov 23 01:18:27 NOVT 2012                                                   


ВЫВОДЫ:
Как и ожидалось:
1. Даные ENUM занимают значительно меньше места 85Mb вместо 530Mb
2. Тупая заливка построчно с STDOUT
       в XESTR ~ 1 минута
       в XENUM ~ 7 Минут
Тут тоже все ясно SQL должен найти занчение в HASH, а потом записать 2 байта
Т.е. 6 лишних минут время на упаковку...

БЕЗ ИНДЕКСОВ НЕ СУЩЕСТВУЮЩИЕ ДАННЫЕ:

Отредактированно федя (22.11.2012 23:57:11)

С индексами все стало намного лучше, но время не видать:

А почему не 1000 колонок, почему не миллион? По сути вы сделали не тест ENUM vs VARCHAR, а ROW FIXED vs DINAMIC. Все скорости достигнуты за счет того, что много полей int конечно работают быстрее чем много полей varchar. Сильно меньше данных (почти в 10 раз, поэтому сильная деградация без индексов), немного меньше индекс (поэтому не так велика разница). Причем чем больше будет полей тем быстрее (но не чем больше данных)

За 100 колонок в одной таблице стоит задуматься о смене архитектуры. А сравнение надо проводить на больших объемах данных, а не на большом количестве колонок, всё таки первая ситуация типовая, а вторая слишком синтетическая, и ее появление на продакшене маловероятно.

Отредактированно Shopen (23.11.2012 00:51:17)

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

Отредактированно Shopen (23.11.2012 01:12:08)

Я думаю что для каждой колонки хранится два числа - смещение и длина. Соответственно найдя нужную строку остается прыгнуть на заданное количество байт и прочитать поле нужной длины. Где оно находится при этом - несущественно. По крайней мере для свежей таблицы, не дефрагментированной множеством удалений/вставок

Shopen написал:

Кстати копирование таблицы при удалении/добавлении индекса, имхо, тоже происходит из за динамического формата

Вот зачем копировать таблицу мне не понятно. Тем более при такой операции:

Shopen написал:

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

Зачем же VARCHAR, мы же в другой теме пытались холивар устроить по поводу DYNAMIC/FIXED.
Другое дело если char(1000) приписать, чтобы таблицы обе по 500 метров стали весить.

То что касется "без индексов" не интересно, тоже не согласен... У меня на продакшенах странные вещи творятся, то 20 сек на запрос, то 5 минут... То что запросы по индексам быстро работают понятно, но мне из той же БД надо статистику по миллиону объектов сгенерить ежестуточную/ежемесячную, а это FULLSCAN на 100-500Gb.  Я enum-ом пользуюсь повсеместно, так как это входит в рекомендации лучших собаководов
А вот теперь вижу, что c ENUM какие-то глюки творятся (ПРИЧЕМ ТОЛЬКО ПОД НАГРУЗКОЙ), то он долю секунды отвечает, а иногда и 20 сек, и это на пол-ляме записей...  А что на 100 лямах происходит вообще не понтяно....

SELECT INDEX TIME xenum (x1) = 4.464065                     
SELECT INDEX TIME xestr (x1) = 0.884817                     
SELECT INDEX TIME xenum (x1) = 0.077248                     
SELECT INDEX TIME xestr (x1) = 1.112519                     
SELECT INDEX TIME xenum (x1) = 0.116779                     
SELECT INDEX TIME xestr (x1) = 0.435789                     
SELECT INDEX TIME xenum (x1) = 9.075406                     
SELECT INDEX TIME xestr (x1) = 0.729085                     
SELECT INDEX TIME xenum (x1) = 6.607169                     
...........
SELECT INDEX TIME xenum (x1) = 0.011834   
SELECT INDEX TIME xestr (x1) = 0.013776   
SELECT INDEX TIME xenum (x1) = 0.530361   
SELECT INDEX TIME xestr (x1) = 0.688365   
SELECT INDEX TIME xenum (x1) = 0.067067   
SELECT INDEX TIME xestr (x1) = 0.558347   
SELECT INDEX TIME xenum (x1) = 0.01304   
SELECT INDEX TIME xestr (x1) = 0.016717   
SELECT INDEX TIME xenum (x1) = 0.008244   
SELECT INDEX TIME xestr (x1) = 0.010692   
SELECT INDEX TIME xenum (x1) = 0.450902   
SELECT INDEX TIME xestr (x1) = 0.51849   
SELECT INDEX TIME xenum (x1) = 5.566052   
SELECT INDEX TIME xestr (x1) = 0.722315   
SELECT INDEX TIME xenum (x1) = 0.063313   
SELECT INDEX TIME xestr (x1) = 0.620084   
SELECT INDEX TIME xenum (x1) = 7.304188   
SELECT INDEX TIME xestr (x1) = 0.975454   
SELECT INDEX TIME xenum (x1) = 0.097783   
SELECT INDEX TIME xestr (x1) = 0.840194   
SELECT INDEX TIME xestr (x1) = 0.882955         
SELECT INDEX TIME xenum (x1) = 0.069888         
SELECT INDEX TIME xestr (x1) = 0.815592         
SELECT INDEX TIME xenum (x1) = 5.096668         
SELECT INDEX TIME xestr (x1) = 4.687986         
SELECT INDEX TIME xenum (x1) = 0.068007         
SELECT INDEX TIME xestr (x1) = 0.612578         
SELECT INDEX TIME xenum (x1) = 21.137908       
....
SELECT INDEX TIME xestr (x1) = 1.035605         
SELECT INDEX TIME xenum (x1) = 0.093493         
SELECT INDEX TIME xenum (x1) = 20.818862   
..........
SELECT INDEX TIME xenum (x1) = 5.250252     
SELECT INDEX TIME xestr (x1) = 1.196446     
SELECT INDEX TIME xenum (x1) = 0.116201

Shopen написал:

федя написал:

Shopen написал:

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

Зачем же VARCHAR, мы же в другой теме пытались холивар устроить по поводу DYNAMIC/FIXED.
Другое дело если char(1000) приписать, чтобы таблицы обе по 500 метров стали весить.

Вот как раз затем, чтобы в холиваре enum vs varchar исключить влияние форматов таблиц, т.к. добавление одного поля varchar изменит Row format на dinamic. Пусть они обе будут динамические, тогда сравнение enum и varchar станет сильно ближе к реальным задачам (но все равно далеко). Либо тестируйте на одной-двух колонках, но с большим объемом данных. Только тогда это будет enum vs varchar.

OK.
Признаю свою ошибку...
Для меня сравнение ENUM/VARCHAR лежит в контексте вот этой задачи
FIXED vs DYNAMIC
ENUM в ней играет ключевую роль для возможности удаления столбцов VARCHAR.
Понятное дело, что VARCHAR можно "удалить", создав таблицу вида
   ID = VARCHAR VALUE.
Но при заранее известном наборе VARCHAR VALUE, вполне возможно использовать ENUM.

Т.е. я утверждаю, что
- если из таблицы тем или иным способом убрать все столбцы
VARCHAR, то "таблица будет работать быстрее".

И второе:
- если в таблице два вида записей:
     1. дата+данные
     2. дата+ошибка,
то лучше создать две указанных таблицы, вместо одной:
    дата + данные + ошибка

Ну да ладно....
Теперь проведем сравнение DYNAMIC/FIXED

Отредактированно федя (24.11.2012 02:07:16)

Добавление  записей в конец этой же таблицы:

Create_options: row_format=FIXED+KEY 

WRITE (100) INDEX TIME xenum (x1) = 0.106309   
WRITE (100) INDEX TIME xenum (x1) = 0.106504   
WRITE (100) INDEX TIME xenum (x1) = 0.106584   

Create_options: row_format=DYNAMIC+KEY 

WRITE (100) INDEX TIME xenum (x1) = 0.10688   
WRITE (100) INDEX TIME xenum (x1) = 0.106611   
WRITE (100) INDEX TIME xenum (x1) = 0.107017   

Create_options: row_format=DYNAMIC  + NO KEY

WRITE (100) INDEX TIME xenum (x1) = 0.103374       
WRITE (100) INDEX TIME xenum (x1) = 0.103614       
WRITE (100) INDEX TIME xenum (x1) = 0.103622       

Create_options: row_format=FIXED  + NO KEY

WRITE (100) INDEX TIME xenum (x1) = 0.10273   
WRITE (100) INDEX TIME xenum (x1) = 0.102757 
WRITE (100) INDEX TIME xenum (x1) = 0.10269   

Выводы:
   Добавление записей в конец таблицы происходит с одинаковой скоростью... Накладные расходы на DYNAMIC менее 1%. Ключ  добавил еще 5% накладных  расходов.

А теперь попробуем операцию записи в середину таких таблиц (UPDATE/REPLACE)

Отредактированно федя (24.11.2012 02:47:12)

Shopen написал:

федя написал:

ENUM в ней играет ключевую роль для возможности удаления столбцов VARCHAR.
Понятное дело, что VARCHAR можно "удалить", создав таблицу вида
   ID = VARCHAR VALUE.
Но при заранее известном наборе VARCHAR VALUE, вполне возможно использовать ENUM.

А можно не ENUM а справочник, при большинстве видов запросов будет работать также как и ENUM. Использовать ENUM  можно, но как выше уже говорилось - нецелесообразно при реальных задачах (т.е. без горизонтального масштабирования на большое число колонок как у вас). В реальных задачах ENUM тем ценнее, чем длиннее строки. С другой стороны он не переносим на другую СУБД, и хорошо если набор строк навсегда предопределен, но если надо добавить то, чего нет - то это ALTER TABLE, что плохо.

Конечно можно справочник...

Вот у меня в каждой инсталляции по 100-1000 тыс объектов по которым я сохраняю данные раз в час. Время хранения 1 год.
24*365 ~ 10 000 записей на объект. Итого 10 млрд записей.
Каждый "лишний байт" при хранении это 10 Gb. 

Теперь у меня есть поле в котором сохраняется состояние объекта включен/выключен.

Какие у меня есть варианты:
1. enum (up/down) - 1 байт
2. char(4) - 4 байта (5)
3. varchar(4) - 4 байта (5)
4. tinyint - 1 байт (теоретически BOOL/BIT которые тоже видимо занимают 1 байт так как в большинстве СУБД это  синонимы для tinyint(1))

Способы 1-3 в select мне сразу возвращается up/down и мне нет необходимости объяснять "внешней системе", что 1 - это up, а 2 это down.

В случае 4 у меня есть два варианта:
   a) join c таблицей из двух строк.
   б) обработка на прикладном уровне (если "полеX" равно 1, то "DOWN", если "полеX" равно 2, то "UP")
   в) обработка в запросе (CASE/IF)
В случае записи значения мне также всегда приходится писать DOWN=1, UP=2.

Я считаю, что в порядки приоритета необходимо выбирать:
   
   1. Так как самый маленький объем + нет геморроя с отслеживанием up/down на прикладном уровне
   
  4б) Прикладная задача имеет возможность сравнивать поле==1 (1-5 инструкции на ассемблере), вместо strcmp(a,"down") - (тут будет инструкции 20-30 минимум в случае использования комплилятора С++ к примеру) 
   
   4в) Тут как раз и будет 20-30 инструкции на ассемблере. реально будет все 100-200 (так как интерпретатор)

    4a) JOIN - поверим разработчикам СУБД, что они умеют быстро искать значение в HASH-е из двух значении.
   
    2) 30 лишних GB   

   3) Это однозначно в конце ???? 10-40 Gb лишних. Переносимо. Нет проблем с проверками IF/CASE. Но Таблица автоматически становится DYNAMIC !

ps.
Про переносимость вообще отдельная тема

Отредактированно федя (24.11.2012 11:26:24)