SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 22.11.2012 22:17:34

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Халивар VARCHAR против ENUM

В этой теме складываю результаты тестов...

Начало было в другой теме:

vasya написал:

О сравнении производительности между enum и varchar - http://www.mysqlperformanceblog.com/200 … is-faster/
Причем в тестовых данных замена varchar на enum серьезно сокращала длину строки, и то разница оказалась невелика.

Кроме того у enum есть проблема с добавлением/изменением определения поля. Это делается только через alter table и, не смотря на то, что реально данные не затрагиваются - создание копии исходной таблицы,... Хотя в последних версиях вроде как поправили эту фичу (или мне только так кажется).

Итак СОВЕРШЕННО условный эксперимент:
1. Берется 999 строк из случайного места  (из словаря Webster).
2. Создается две базы данных в одной сто полей ENUM, в другой сто полей VARCHAR
3. Генерируем случайные данные и заливаем в две таблицы.


feddy>  wc -l web2a
     999 web2a
####head -10 web2a 
==> web2a <==                                   
fairy prion                                     
fairy queen                                     
fairy ring                                     
fairy-ring mushroom                             
fairy-ring spot                                 
fairy rose                                     
fairy shrimp                                   
fairy smoke                                     
fairy song                                     
fairy stone                                     
....
####tail -10 web2a           
field maneuver                                           
field marigold                                           
field mark                                               
field marshal                                             
field martin                                             
field meeting                                             
field milkwort                                           
field mint                                               
field mouse                                               
field mushroom

Отредактированно федя (23.11.2012 00:10:48)

Неактивен

 

#2 22.11.2012 22:18:51

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

==== КОД ДЛЯ ГЕНЕРАЦИИ ДАННЫХ ===========
#!/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";             
}                                           

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

Неактивен

 

#3 22.11.2012 22:21:14

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

========== Makefile =====================
all: sss sql data                                                               
                                                                               
sss:                                                                           
        perl t.pl < web2a                                                       
                                                                               
sql:                                                                           
        cat monEdata.sql | mysql database                                         
        cat monSdata.sql | mysql database                                         
                                                                               
data:                                                                           
        cat monDdata.sql | mysql database                                         
        (cat monDdata.sql | sed -e 's/ xenum/ xestr/g') | mysql database           
                                                                               
        (echo "show table status like 'xenum'\G" | mysql database;echo "show table status like 'xestr'\G" | mysql database) | grep "Data_length"

Неактивен

 

#4 22.11.2012 22:32:37

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Получились файлы:
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 лишних минут время на упаковку...

Неактивен

 

#5 22.11.2012 22:42:30

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Для чистоты экспермента отображаем версию:
### mysql --version                                             
mysql  Ver 14.12 Distrib 5.0.67, for portbld-freebsd7.0 (i386) using  5.2   

И данные... Ах черт облажлался, на одну строчку меньше:

mysql> select count(*) from xestr;  
+----------+                        
| count(*) |                        
+----------+                        
|   399999 |                        
+----------+                        
1 row in set (0.00 sec)              

mysql> select count(*) from xenum;    
+----------+                          
| count(*) |                          
+----------+                          
|   399999 |                          
+----------+                          
1 row in set (0.00 sec)      

Неактивен

 

#6 22.11.2012 22:47:38

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

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

mysql> select SQL_NO_CACHE count(x2) from xenum where x1='aboard';      
+-----------+                                                          
| count(x2) |                                                          
+-----------+                                                          
|         0 |                                                          
+-----------+                                                          
1 row in set (0.11 sec)                                                

 
mysql> select SQL_NO_CACHE count(x2) from xestr where x1='aboard';    
+-----------+                                                          
| count(x2) |                                                          
+-----------+                                                          
|         0 |                                                          
+-----------+                                                          
1 row in set (1.62 sec)  
                                             

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


mysql> select SQL_NO_CACHE count(x1) from xestr where x1='fellow pupil';      
+-----------+                                                                
| count(x1) |                                                                
+-----------+                                                                
|       428 |                                                                
+-----------+                                                                
1 row in set (1.60 sec)                                                      
                                                                             
mysql> select SQL_NO_CACHE count(x1) from xenum where x1='fellow pupil';      
+-----------+                                                                
| count(x1) |                                                                
+-----------+                                                                
|       428 |                                                                
+-----------+                                                                
1 row in set (0.12 sec)  
                                                     

ВЫВОД:  ENUM в 13 РАЗ БЫСТРЕЕ чем VARCHAR

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

Неактивен

 

#7 22.11.2012 22:50:21

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

ДОБАВЛЯЕМ ИНДЕКС:

mysql> alter table xestr add index XSTR1 (x1) ;  
Query OK, 399999 rows affected (16.20 sec)      
Records: 399999  Duplicates: 0  Warnings: 0      

mysql> alter table xenum add index XENM1 (x1) ;    
Query OK, 399999 rows affected (2.56 sec)          
Records: 399999  Duplicates: 0  Warnings: 0        
 


Вывод индекс создан в 6 раз быстрее для Поля ENUM...

Неактивен

 

#8 22.11.2012 23:05:23

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

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


mysql> SET PROFILING=1;                
Query OK, 0 rows affected (0.00 sec)  

mysql> select SQL_NO_CACHE count(x1) from xenum where x1='fellow pupil';
+-----------+                                                            
| count(x1) |                                                            
+-----------+                                                            
|       428 |                                                            
+-----------+                                                            
1 row in set (0.00 sec)                                                  

mysql> show profile;                              
+--------------------------------+----------+      
| Status                         | Duration |      
+--------------------------------+----------+      
| starting                       | 0.000023 |      
| checking query cache for query | 0.000046 |      
| checking permissions           | 0.000007 |      
| Opening tables                 | 0.000009 |      
| System lock                    | 0.000007 |      
| Table lock                     | 0.000009 |      
| init                           | 0.000024 |      
| optimizing                     | 0.000012 |      
| statistics                     | 0.000178 |      
| preparing                      | 0.000013 |      
| executing                      | 0.000008 |      
| Sending data                   | 0.000364 |      
| end                            | 0.000013 |      
| end                            | 0.000007 |      
| query end                      | 0.000006 |      
| freeing items                  | 0.000008 |      
| closing tables                 | 0.000008 |      
| logging slow query             | 0.000005 |      
| cleaning up                    | 0.000007 |      
+--------------------------------+----------+      
19 rows in set (0.00 sec)                                            
                                                       
mysql> select SQL_NO_CACHE count(x1) from xestr where x1='fellow pupil';
+-----------+                                                            
| count(x1) |                                                            
+-----------+                                                            
|       428 |                                                            
+-----------+                                                            
1 row in set (0.00 sec)

mysql> show profile;                                  
+--------------------------------+----------+          
| Status                         | Duration |          
+--------------------------------+----------+          
| starting                       | 0.000024 |          
| checking query cache for query | 0.000046 |          
| checking permissions           | 0.000008 |          
| Opening tables                 | 0.000009 |          
| System lock                    | 0.000007 |          
| Table lock                     | 0.000010 |          
| init                           | 0.000023 |          
| optimizing                     | 0.000013 |          
| statistics                     | 0.000244 |          
| preparing                      | 0.000013 |          
| executing                      | 0.000008 |          
| Sending data                   | 0.000076 |          
| end                            | 0.000006 |          
| end                            | 0.000005 |          
| query end                      | 0.000006 |          
| freeing items                  | 0.000008 |          
| closing tables                 | 0.000011 |          
| logging slow query             | 0.000007 |          
| cleaning up                    | 0.000005 |          
+--------------------------------+----------+          
19 rows in set (0.00 sec)                              
 


Стало все с точностью до долей секунды....

Но мы для надежности выполним 100 раз....
Причем проверим еще одну "теорию".....

Неактивен

 

#9 22.11.2012 23:53:03

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

# ./test.pl - 100 раз запросы вида
select SQL_NO_CACHE count(*) from $baseX where $baseX = 'aaa';

SELECT INDEX TIME xenum (x1) = 0.051258   - Это с ключами
SELECT INDEX TIME xestr (x1) = 0.045486     - Это с ключами   

SELECT INDEX TIME xenum (x2) = 8.737976        - Это без ключами
SELECT INDEX TIME xestr (x2) = 164.09505         - Это без ключами

SELECT INDEX TIME xenum (x3) = 8.6889         
SELECT INDEX TIME xestr (x3) = 163.816231     

SELECT INDEX TIME xenum (x4) = 8.658204       
SELECT INDEX TIME xestr (x4) = 163.920506     

SELECT INDEX TIME xenum (x5) = 8.698678       
SELECT INDEX TIME xestr (x5) = 164.166562
.....

Дальше ждать надоело, поэтому поставил по два раза:

Проверяем зависит ли время выполнения скрипта от расположения колонки:
....
SELECT INDEX TIME xenum (x3) = 0.1993       
SELECT INDEX TIME xestr (x3) = 3.274756     
SELECT INDEX TIME xenum (x4) = 0.200065     
SELECT INDEX TIME xestr (x4) = 3.275215     
SELECT INDEX TIME xenum (x5) = 0.19901       
SELECT INDEX TIME xestr (x5) = 3.274949     
SELECT INDEX TIME xenum (x6) = 0.199076     
SELECT INDEX TIME xestr (x6) = 3.276928     
SELECT INDEX TIME xenum (x7) = 0.198502     
SELECT INDEX TIME xestr (x7) = 3.274929     
SELECT INDEX TIME xenum (x8) = 0.198885     
SELECT INDEX TIME xestr (x8) = 3.276866     
SELECT INDEX TIME xenum (x9) = 0.198914     
SELECT INDEX TIME xestr (x9) = 3.275755     
SELECT INDEX TIME xenum (x10) = 0.198844     
SELECT INDEX TIME xestr (x10) = 3.275785     
SELECT INDEX TIME xenum (x11) = 0.199154     
SELECT INDEX TIME xestr (x11) = 3.277848     
SELECT INDEX TIME xenum (x12) = 0.197974     
SELECT INDEX TIME xestr (x12) = 3.276169     
SELECT INDEX TIME xestr (x24) = 3.275773     
SELECT INDEX TIME xenum (x25) = 0.198997     
SELECT INDEX TIME xestr (x25) = 3.275909     
SELECT INDEX TIME xenum (x26) = 0.198324     
....
SELECT INDEX TIME xestr (x95) = 3.348748   
SELECT INDEX TIME xenum (x96) = 0.200526   
SELECT INDEX TIME xestr (x96) = 3.286373   
SELECT INDEX TIME xenum (x97) = 0.200572   
SELECT INDEX TIME xestr (x97) = 3.288104   
SELECT INDEX TIME xenum (x98) = 0.201017   
SELECT INDEX TIME xestr (x98) = 3.286915   
SELECT INDEX TIME xenum (x99) = 0.199746   
SELECT INDEX TIME xestr (x99) = 3.286466   
======================

Для меня очень странно что не зависит sad

Из вот этих данных:
SELECT INDEX TIME xenum (x1) = 0.051258   - Это с ключами
SELECT INDEX TIME xestr (x1) = 0.045486     - Это с ключами   
Мы можем сделать выводы, что ключ по VARCHAR быстрее или примерно равен ENUM....

Но теперь, нагрузим наш сервер 100 клиентами, которые делают те же самые выборки....

### ps | grep test3 | wc -l   
    98                                               
### ps
43804  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43806  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43808  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43810  p1  I+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43812  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43814  p1  I+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43816  p1  I+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43818  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43820  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43822  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43824  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43826  p1  I+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43828  p1  S+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
43836  p1  I+     0:00.06 /usr/bin/perl -w ./test3.pl (perl5.8.9)   
.....
И запустим наш скрипт вновь, только уже для поля X1 по которому у нас состоены ключи в двух таблицах:

SELECT INDEX TIME xenum (x1) = 0.001965
SELECT INDEX TIME xestr (x1) = 0.342526     

SELECT INDEX TIME xenum (x1) = 0.0021   
SELECT INDEX TIME xestr (x1) = 0.379128     

SELECT INDEX TIME xenum (x1) = 0.003697   
SELECT INDEX TIME xestr (x1) = 0.244425     

SELECT INDEX TIME xenum (x1) = 0.117488 
SELECT INDEX TIME xestr (x1) = 1.117447 

SELECT INDEX TIME xenum (x1) = 7.867732  - Видимо кто-то вытеснил наш индекс smile

SELECT INDEX TIME xestr (x1) = 0.086154 
SELECT INDEX TIME xenum (x1) = 0.076629 
SELECT INDEX TIME xestr (x1) = 0.763209 
SELECT INDEX TIME xenum (x1) = 0.096578 
SELECT INDEX TIME xestr (x1) = 0.651174 
SELECT INDEX TIME xenum (x1) = 0.059562 
SELECT INDEX TIME xestr (x1) = 0.623316 
SELECT INDEX TIME xenum (x1) = 0.05211   
SELECT INDEX TIME xestr (x1) = 0.488722 
SELECT INDEX TIME xenum (x1) = 0.058742 
SELECT INDEX TIME xestr (x1) = 0.34817   
SELECT INDEX TIME xenum (x1) = 0.010939 
SELECT INDEX TIME xestr (x1) = 0.848082       
SELECT INDEX TIME xenum (x1) = 0.099929       
SELECT INDEX TIME xestr (x1) = 0.830045       
SELECT INDEX TIME xenum (x1) = 0.099866       
SELECT INDEX TIME xestr (x1) = 0.744057       
SELECT INDEX TIME xenum (x1) = 0.156631       
SELECT INDEX TIME xestr (x1) = 0.73023       
SELECT INDEX TIME xenum (x1) = 0.101721       
SELECT INDEX TIME xestr (x1) = 0.589214       
SELECT INDEX TIME xenum (x1) = 0.011061       
SELECT INDEX TIME xestr (x1) = 0.015704       
SELECT INDEX TIME xenum (x1) = 0.017437       
SELECT INDEX TIME xestr (x1) = 0.721451       
SELECT INDEX TIME xenum (x1) = 0.049291       
SELECT INDEX TIME xestr (x1) = 0.175674       
SELECT INDEX TIME xenum (x1) = 0.046123       
SELECT INDEX TIME xestr (x1) = 0.705736       
SELECT INDEX TIME xenum (x1) = 0.077874       
SELECT INDEX TIME xestr (x1) = 0.783217       
SELECT INDEX TIME xenum (x1) = 0.069564       
SELECT INDEX TIME xestr (x1) = 0.722352       
SELECT INDEX TIME xenum (x1) = 0.065454         
....
О чудеса ....
На реальном продакшене все БУДЕТ ВЫГЛЯДЕТЬ совершено не так, как на домашнем компьютере smile

Отредактированно федя (23.11.2012 00:05:37)

Неактивен

 

#10 23.11.2012 00:32:55

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Халивар VARCHAR против ENUM

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

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

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

Неактивен

 

#11 23.11.2012 01:03:35

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Кстати вот размеры индексов:
3679232 xenum.MYI
1679360 xestr.MYI

Удалим их и  посмотрим, как ведет себя поиск под нагрузкой:

mysql> alter table xenum drop index XENM1;        
Query OK, 399999 rows affected (6.80 sec)          
Records: 399999  Duplicates: 0  Warnings: 0        
                                                   
mysql> alter table xestr drop index XSTR1;        
Query OK, 399999 rows affected (4 min 16.16 sec)  
Records: 399999  Duplicates: 0  Warnings: 0        
 

Заметим странную вещь, удаление VARCHAR продолжалось 4 минуты...

Повторяем поиск, но уже без индексов, при условии, что рядом у нас 100 клиентов активно читают нашу БД:
SELECT INDEX TIME xenum (x1) = 8.668247     
SELECT INDEX TIME xestr (x1) = 403.414615   - почти 7 минут на 10 запросов !

Но вернемся к вопросу ПОЧЕМУ УДАЛЕНИЕ и СОЗДАНИЕ ИНДЕКСА для VARCHAR заняло 4 и 6 минут:

mysql> alter table xestr add index X (x1) ;            
Query OK, 399999 rows affected (6 min 13.45 sec)      
Records: 399999  Duplicates: 0  Warnings: 0            

mysql> alter table xestr drop index X;              
Query OK, 399999 rows affected (4 min 6.13 sec)    
Records: 399999  Duplicates: 0  Warnings: 0        
 


Для этого посмотрим, что творится в /var/db/mysql/database/
###ls -lisq *sql*
11658299 315568 -rw-rw----  1 mysql  mysql  322961408 Nov 23 03:53 #sql-73f_a458.MYD                                                                       
11658280      2 -rw-rw----  1 mysql  mysql       1024 Nov 23 03:51 #sql-73f_a458.MYI                                                                           
11658279     36 -rw-rw----  1 mysql  mysql      36130 Nov 23 03:51 #sql-73f_a458.frm                                                                                                                                   

Оказывается выполяняется копирование таблицы !

Проверим, происходит ли копирование, при удалении/создании ENUM:

mysql> alter table xenum add index X (x1) ;  
Query OK, 399999 rows affected (22.18 sec)    
Records: 399999  Duplicates: 0  Warnings: 0
 

###ls -lisq *sql*
ПУСТО


mysql> alter table xenum drop index X;        
Query OK, 399999 rows affected (48.29 sec)    
Records: 399999  Duplicates: 0  Warnings: 0  


###ls -lisq *sql*
ПУСТО
                                               

mysql> alter table xenum drop index X;                  
Query OK, 399999 rows affected (1 min 12.57 sec)        
Records: 399999  Duplicates: 0  Warnings: 0  
           

###ls -lisq *sql*
ПУСТО

Оказывается все наоборот, VARCHAR - ведет себя странно, а не ENUM !

Неактивен

 

#12 23.11.2012 01:11:35

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Халивар VARCHAR против ENUM

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

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

Неактивен

 

#13 23.11.2012 01:14:45

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Shopen написал:

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

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

Я почему-то думаю, что 100 колонок ваще не причем...

100 колонок эту другая тема.... Я был уверен, что имеются БОЛЬШИЕ накладные расходы для таблицы DYNAMIC в случае если колока будет в конце. На данном тесте я этого не увидел, не для FIXED=ENUM, ни для VARCHAR (DYNAMIC)

Мне это не понтяно, почему данные из первого VARCHAR, извлекаются с такой-же скоростью как из сотой VARCHAR.
Что там за удивительный алгоритм, который позволяет это сделать... Как минимум могу предположить, экстремальный случай, что в начале ROW находятся 100 смещении для каждого VARCHAR (но это бред)
Буду придумывать другой эксперимент, а точнее читать исходный код...

Неактивен

 

#14 23.11.2012 01:19:18

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Халивар VARCHAR против ENUM

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

Неактивен

 

#15 23.11.2012 01:27:47

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Халивар VARCHAR против ENUM

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

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

Неактивен

 

#16 23.11.2012 01:43:56

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Shopen написал:

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

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

mysql> alter table xestr pack_keys=1;              
Query OK, 399999 rows affected (6 min 36.00 sec)    
Records: 399999  Duplicates: 0  Warnings: 0  
       

Уж сжатие то ключей можно без таблицы произвести...

Неактивен

 

#17 23.11.2012 01:45:59

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Shopen написал:

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

И varchar(4) превращается в char(6) smile

Неактивен

 

#18 23.11.2012 02:06:44

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Shopen написал:

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

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

То что касется "без индексов" не интересно, тоже не согласен... У меня на продакшенах странные вещи творятся, то 20 сек на запрос, то 5 минут... То что запросы по индексам быстро работают понятно, но мне из той же БД надо статистику по миллиону объектов сгенерить ежестуточную/ежемесячную, а это FULLSCAN на 100-500Gb.  Я enum-ом пользуюсь повсеместно, так как это входит в рекомендации лучших собаководов smile
А вот теперь вижу, что 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

Неактивен

 

#19 23.11.2012 02:14:10

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Халивар VARCHAR против ENUM

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

Shopen написал:

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

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

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

Неактивен

 

#20 24.11.2012 02:06:22

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

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)

Неактивен

 

#21 24.11.2012 02:19:33

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

Итак было предложено добавить VARCHAR в таблицу ENUM,

mysql> show table status like 'xenum'\G                            
*************************** 1. row ***************************      
           Name: xenum                                              
         Engine: MyISAM                                            
        Version: 10                                                
     Row_format: Fixed                                              
           Rows: 399999                                            
 Avg_row_length: 224                                                
    Data_length: 89599776                                          
Max_data_length: 63050394783186943                                  
   Index_length: 3679232                                            
      Data_free: 0                                                  
 Auto_increment: NULL                                              
    Create_time: 2012-11-24 03:26:18                                
    Update_time: 2012-11-24 03:26:20                                
     Check_time: 2012-11-24 03:26:21                                
      Collation: latin1_swedish_ci                                  
       Checksum: NULL                                              
 Create_options: row_format=FIXED                                  
        Comment:                                                    
1 row in set (0.00 sec)                                            
 


но для начала мы просто преобразуем таблицу XENUM в DYNAMIC:


mysql> alter table xenum row_format=dynamic;                  
Query OK, 399999 rows affected (2.78 sec)                      
Records: 399999  Duplicates: 0  Warnings: 0  
                 

Итак у нас таблица с ключом по X1 и row_format=DYNAMIC:

SELECT (100) INDEX TIME xenum (x1) = 0.319027   
SELECT (100) INDEX TIME xenum (x1) = 0.319879   
SELECT (100) INDEX TIME xenum (x1) = 0.318603   

mysql> alter table xenum row_format=fixed;      
Query OK, 399999 rows affected (3.07 sec)      
Records: 399999  Duplicates: 0  Warnings: 0    
 

row_format=FIXED + KEY

SELECT (100) INDEX TIME xenum (x1) = 0.141673
SELECT (100) INDEX TIME xenum (x1) = 0.141088
SELECT (100) INDEX TIME xenum (x1) = 0.141745
SELECT (100) INDEX TIME xenum (x1) = 0.141505


mysql> alter table xenum drop index X;                  
Query OK, 399999 rows affected (1.72 sec)              
Records: 399999  Duplicates: 0  Warnings: 0  
           

SELECT (100) INDEX TIME xenum (x1) = 9.264372   
SELECT (100) INDEX TIME xenum (x1) = 9.257882   
SELECT (100) INDEX TIME xenum (x1) = 9.284745   

mysql> alter table xenum row_format=dynamic;                
Query OK, 399999 rows affected (2.78 sec)                    
Records: 399999  Duplicates: 0  Warnings: 0  
               

SELECT (100) INDEX TIME xenum (x1) = 105.915155           
SELECT (100) INDEX TIME xenum (x1) = 106.20488                 

ВЫВОДЫ:
   - Поиск по таблице в формате FIXED без ключей в 10 раз быстрее чем DYNAMIC
   - Поиск по таблице в формате FIXED c ключом в 2 раз быстрее чем DYNAMIC

Неактивен

 

#22 24.11.2012 02:44:17

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

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

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)

Неактивен

 

#23 24.11.2012 04:50:24

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Халивар VARCHAR против ENUM

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

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

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

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

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

Вот с этим вообще никто не спорил, но конкретный способ зависит от данных и универсального нет. Если строки - коды языков (en,us) то лучше это поле сделать CHAR(2), чем ENUM. Если вы добиваетесь ROW_FIXED - это хорошо, но опять же, если колонок 5-10, то разница может оказаться несущественной

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

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

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

Неактивен

 

#24 24.11.2012 11:00:49

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

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)

Неактивен

 

#25 24.11.2012 11:12:48

федя
Завсегдатай
Зарегистрирован: 14.11.2012
Сообщений: 33

Re: Халивар VARCHAR против ENUM

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

Переносимый код это когда вы знаете способность "всех систем" и пытаетесь их учитывать при разработке.
Простейший случай это когда используются инструкции препроцесора аля
IFDEF __ENUM___;
    КОД ДЛЯ системы поддерживающей enum
ELSE
    КОД для системы не поддерживающей enum
END

IF _ORACLE_VERSION_ > 11;
    код с INLINE
ELSE
   код без INLINE
END


Причем препроцессор лучше использовать именно стандартный те CPP + Make

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson