Почему MySQL InnoDB настолько медленнее при полных сканированиях таблицы, чем MyISAM?

Править

OP подтвердила ошибку при профилировании PostgreSQL в его ответе ниже. Я обновляю этот вопрос отразить сравнение между MyISAM & InnoDB.

Здравствуйте,

Я запустил тест против MySQL InnoDB, MyISAM и PostgreSQL, чтобы видеть, как хорошо каждый из этих механизмов работал, делая полные сканирования таблицы для понимания то, чем время отклика могло бы быть для случаев, где мы неизбежно должны позволить этому произойти.

Тесты проводились на Intel Core 2 Quad Q6600 2.4 ГГц w/4 ГБ RAM и HD на 7 200 об/мин с кэшем 16 МБ.

MySQL Version был 5.0.67-community-nt-log 32-разрядный, версия PGSQL была 8.4.

Я записал маленький сценарий для генерации 5 миллионов строк данных в таблице на 4 столбца. Это создать операторы таблицы, используемые в MySQL и PGSQL:

- InnoDB

CREATE TABLE sample_innodb (
id integer unsigned not null,
vc1 varchar(200) not null,
vc2 varchar(200) not null,
vc3 varchar(200) not null
) ENGINE=InnoDB;

- MyISAM

CREATE TABLE sample_isam (
id integer unsigned not null,
vc1 varchar(200) not null,
vc2 varchar(200) not null,
vc3 varchar(200) not null
) ENGINE=MyISAM;

- PostgreSQL

create table sample_pgsql (
id integer not null,
vc1 varchar(200) not null,
vc2 varchar(200) not null,
vc3 varchar(200) not null
);

Это - сценарий, я раньше генерировал данные для этих таблиц:

var chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz'.split('');

function randomString(length) {
 var str = '';
 for (var i = 0; i < length; i++) {
    str += chars[Math.floor(Math.random() * chars.length)];
 }

   return str;
}

function genrow(idv, vcv1, vcv2, vcv3) {
 return idv + "," + vcv1 + "," + vcv2 + "," + vcv3;
}

function gentable(numrows) {
 for (var i = 0; i < numrows; i++) {
    var row = 
        genrow(i,
               randomString(10),
               randomString(20),
               randomString(30));

    WScript.Echo(row);
  }
}

gentable(5000000);

Я запустил этот скрипт в Windows с командой:

cscript.exe /nologo test.js > data.csv

Можно загрузить эти данные в MySQL с этими командами:

LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE sample_innodb
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, vc1, vc2, vc3);

LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE sample_isam
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, vc1, vc2, vc3);

Можно загрузить данные в PGSQL с этой командой:

copy sample_pgsql (id, vc1, vc2, vc3) from 'data.csv' with delimiter ','

Я использовал этот запрос для синхронизации, чтобы попытаться вызвать худший сценарий сканирования таблицы случая:

select count(*) from [table] 
where vc1 like '%blah0%' and vc2 like '%blah1%' and vc3 like '%blah2%';

select count(*) from [table] 
where vc1 ilike '%blah0%' and vc2 ilike '%blah1%' and vc3 ilike '%blah2%';

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

Результаты были следующие:

• InnoDB - 8,56 с
• MyISAM - 1,84 с
• PGSQL - 8,4 с

Вопрос

Почему случается так, что InnoDB и MyISAM до сих пор независимо с точки зрения времени для завершения полного сканирования таблицы? Я просто пропускаю что-то очевидное в конфигурации MySQL? Я использовал MySQL в течение многих лет и не имел никаких проблем с ним, пока мои проблемы были ограничены к набору "индексов, может решить это" проблемы.

Разделение, очевидно, также решит эту проблему, но в намного более высокой стоимости.

Для ссылки вот мои файлы конфигурации для MySQL и PGSQL:

КОНФИГУРАЦИЯ MYSQL

[client]
port=3306

[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=utf8
default-storage-engine=INNODB
log="c:/logs/mysql/mysqld.log"
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=700
query_cache_size=0M
table_cache=1400
tmp_table_size=16M
thread_cache_size=34

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=200M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=208K

innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=200M
innodb_log_file_size=18M
innodb_thread_concurrency=10

КОНФИГУРАЦИЯ PGSQL

listen_addresses = '*'        # what IP address(es) to listen on;
                # comma-separated list of addresses;
                # defaults to 'localhost', '*' = all
                # (change requires restart)
port = 5432                # (change requires restart)
max_connections = 100            # (change requires restart)

shared_buffers = 32MB            # min 128kB
                # (change requires restart)
temp_buffers = 12MB            # min 800kB
maintenance_work_mem = 32MB        # min 1MB

log_destination = 'stderr'        # Valid values are combinations of
                # stderr, csvlog, syslog and eventlog,
                # depending on platform.  csvlog
                # requires logging_collector to be on.

logging_collector = on        # Enable capturing of stderr and csvlog
                # into log files. Required to be on for
                # csvlogs.
                # (change requires restart)

log_line_prefix = '%t'            # special values:
                #   %u = user name
                #   %d = database name
                #   %r = remote host and port
                #   %h = remote host
                #   %p = process ID
                #   %t = timestamp without milliseconds
                #   %m = timestamp with milliseconds
                #   %i = command tag
                #   %c = session ID
                #   %l = session line number
                #   %s = session start timestamp
                #   %v = virtual transaction ID
                #   %x = transaction ID (0 if none)
                #   %q = stop here in non-session
                #        processes
                #   %% = '%'
                # e.g. '<%u%%%d> '

datestyle = 'iso, mdy'
lc_messages = 'English_United States.1252'            # locale for system error message
                # strings
lc_monetary = 'English_United States.1252'            # locale for monetary formatting
lc_numeric = 'English_United States.1252'            # locale for number formatting
lc_time = 'English_United States.1252'                # locale for time formatting

default_text_search_config = 'pg_catalog.english'

Также, чтобы понять, насколько большой эти наборы данных на самом деле находятся в MySQL, вот выставочное состояние таблицы \G на них, в случае, если это помогает:

*************************** 1. row ***************************
           Name: sample_innodb
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5000205
 Avg_row_length: 100
    Data_length: 500154368
Max_data_length: 0
   Index_length: 149700608
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-02-02 17:27:50
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 497664 kB

*************************** 2. row ***************************
           Name: sample_isam
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 5000000
 Avg_row_length: 72
    Data_length: 360006508
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-02-02 17:27:50
    Update_time: 2010-02-02 17:37:23
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: