MySQL InnoDB 比 MyISAM 慢得多

Question

我有两张表格（只保留了重要字段）：

syskeywordobjects:
pksyskeywordobjects BIGINT
fksyskeywords BIGINT
fkcontents BIGINT

fkcontents INDEX (fkcontents)
fksyskeywords INDEX (fksyskeywords)
fkcontents_fksyskeywords INDEX (fkcontents, fksyskeywords)


syskeywords:
pksyskeywords BIGINT
keyword VARCHAR

keyword INDEX (keyword)

在这两张表上我运行了以下查询：

SELECT k.pksyskeywords, k.keyword, COUNT( k.pksyskeywords ) AS counter
FROM syskeywordobjects ko INNER JOIN syskeywords k ON ko.fksyskeywords = k.pksyskeywords
WHERE (
    k.pksyskeywords <> 1218713201167374664
    AND EXISTS (
        SELECT innerko.pksyskeywordobjects
        FROM syskeywordobjects innerko
        WHERE ko.fkcontents = innerko.fkcontents
        AND innerko.fksyskeywords = 1218713201167374664
    )
)
GROUP BY k.pksyskeywords, k.keyword
ORDER BY counter DESC 
LIMIT 20

如果使用MyISAM表，查询大约需要1-2秒，但是如果使用我必须使用的InnoDB，则需要25-30秒。为什么InnoDB要慢20倍呢？

以下是更多帮助的解释结果。

InnoDB:
id    select_type         table    type    possible_keys                                        key                        key_len   ref                   rows    Extra
1     PRIMARY             k        range   PRIMARY                                              PRIMARY                    8         NULL                  52051   Using where; Using temporary; Using filesort
1     PRIMARY             ko       ref     fksyskeywords                                        fksyskeywords              8         k.pksyskeywords       1       Using where
2     DEPENDENT SUBQUERY  innerko  ref     fkcontents,fksyskeywords,fkcontents_fksyskeywords    fkcontents_fksyskeywords   16        ko.fkcontents,const   1       Using index

MyISAM:
id    select_type           table    type    possible_keys                                       key                         key_len    ref                   rows      Extra
1     PRIMARY               ko       index   fksyskeywords                                       fkcontents_fksyskeywords    16         NULL                  277823    Using where; Using index; Using temporary; Using f...
1     PRIMARY               k        eq_ref  PRIMARY                                             PRIMARY                     8          ko.fksyskeywords      1    
2     DEPENDENT SUBQUERY    innerko  ref     fkcontents,fksyskeywords,fkcontents_fksyskeywords   fkcontents_fksyskeywords    16         ko.fkcontents,const   1    

- Werzi2001

---

如果您运行 SHOW VARIABLES LIKE '%buffer_pool%';，您会得到什么？ - N.B.

innodb_buffer_pool_size被设置为8388608。 - Werzi2001

Oracle在抽什么烟？http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-innodb-myisam-522945.pdf - duffymo

@N.B. 你是对的。我把缓冲区大小改成了512M，现在速度就像应该的一样快了。发表一个答案，我会接受它。 - Werzi2001

@Werzi2001 - 发布了，附带简短的说明。 - N.B.

Answer 1

为了让InnoDB工作更快，您应该将innodb_buffer_pool设置为一个较大的值。这允许InnoDB将工作数据集存储在内存中（表数据、索引数据结构），从而使其更快（RAM的IO > HDD的IO）。如果我没有记错，默认值为8MB，我个人将其设置为可用RAM的80%。

- N.B.