编辑
OP已经在下面的回答中承认了在分析PostgreSQL时犯了一个错误。我更新了这个问题，以反映MyISAM和InnoDB之间的比较。
你好，
我对MySQL InnoDB、MyISAM和PostgreSQL进行了测试，以了解每个引擎在执行全表扫描时的表现如何，以便了解在我们不可避免地需要让它发生的情况下，响应时间可能会是什么样子。
测试是在一台Intel Core 2 Quad Q6600 @ 2.4Ghz w/ 4GB RAM和一个带有16MB缓存的7200 RPM硬盘上进行的。
MySQL版本是5.0.67-community-nt-log 32位，PGSQL版本是8.4。
我编写了一个小脚本，在一个4列表中生成了500万行数据。以下是在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中这些数据集的实际大小，以下是它们的show table status \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: