mysql - 为什么 MySQL InnoDB 在全表扫描时比 MyISAM 慢那么多？

Question

编辑

OP has acknowledged a mistake when profiling PostgreSQL in his answer below. I am updating this question to reflect the comparison between MyISAM & InnoDB.

你好，

我对 MySQL InnoDB、MyISAM 和 PostgreSQL 进行了测试，看看效果如何这些引擎中的每一个都执行全表扫描以了解什么响应时间可能适用于我们不可避免的情况需要让这一切发生。

测试是在 Intel Core 2 Quad Q6600 @ 2.4Ghz w/4GB RAM 和 7200 RPM HD 以及 16MB 缓存。

MySQL 版本为 5.0.67-community-nt-log 32 位，PGSQL 版本为 8.4。

我写了一个小脚本来在一个包含 4 列的表中生成 500 万行数据。这些是在 MySQL 和 PGSQL 中使用的创建表语句:

-- 数据库

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 ','

我将此查询用于计时以尝试强制执行最坏情况的表扫描场景:

MySQL

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.56s
• MyISAM - 1.84s
• 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:

Answer 1

在使用您的配置的我的服务器上，我的基本性能如下:

• InnoDB:5.71 秒
• MyISAM:2.50 秒

在我的书中，这还不错，但可以进行一些调整。

您的 InnoDB 性能可以在几个方面得到改进:

增加innodb_buffer_pool_size

• 这是最重要的 InnoDB 配置变量。理想情况下，它应该是专用于 MySQL 和 InnoDB 的服务器上可用 RAM 的 70-80%。
• 将我服务器上的 innodb_buffer_pool_size 增加到 2G(对于这个测试来说绰绰有余)将 InnoDB 时间减少到4.60s

将id设为主键

• InnoDB 基于 PRIMARY KEY 对其数据进行集群。当你不声明一个时，InnoDB 会隐式地生成一个随机的。拥有顺序主键 (id) 比随机主键更快。
• 将 id 作为我服务器上的主键将 InnoDB 时间减少到3.80s

升级你的 MySQL/InnoDB

MySQL 5.1+ MySQL 支持可插拔存储引擎。特别是新的InnoDB Plugin .

新的 InnoDB 引擎提供了许多性能增强，可能对这种特定类型的查询有重大影响。

注意事项:

• 从 MySQL 5.1.38 开始，InnoDB 插件包含在 MySQL 中
• 从 MySQL 5.1.43 开始，InnoDB 插件不仅包括在内，而且是 MySQL 的默认引擎