newbb_post并将其更改为InnoDB。这些表当前每个包含newbb_innopost个条目。在新启动的数据库上运行这些选择（因此此时不涉及缓存！），该数据库会产生以下结果（忽略完整的输出，请注意我什至都不要求数据库对结果进行排序）：
SELECT post.postid, post.attach FROM newbb_post AS post WHERE post.threadid = 51506; . . | 5401593 | 0 | | 5401634 | 0 | +---------+--------+ 62510 rows in set (0.13 sec)
SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; . . | 5397410 | 0 | | 5397883 | 0 | +---------+--------+ 62510 rows in set (1 min 22.19 sec)
0.13秒到86.19秒（！）
我想知道为什么会这样。我确实在此处阅读了有关InnoDB的Stackexchange上的一些答案，并且有人建议将
5,390,146的大小增加到已安装RAM的80％。这不会解决问题，对特定ID的初始查询将至少花费50倍以上的时间，并使整个Web服务器停滞不前，从而对数据库进行连接和查询。之后可能会启动缓存/缓冲区，但是此数据库中有超过100.000线程，因此很有可能缓存将永远无法容纳要服务的所有相关查询。 上面的查询很简单（没有连接），并且使用了所有键：
EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; +------+-------------+-------+------+-----------------------------------------------+----------+---------+-------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+-----------------------------------------------+----------+---------+-------+--------+-------+ | 1 | SIMPLE | post | ref | threadid,threadid_2,threadid_visible_dateline | threadid | 4 | const | 120144 | | +------+-------------+-------+------+-----------------------------------------------+----------+---------+-------+--------+-------+
这是MyISAM表：
CREATE TABLE `newbb_post` (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`threadid` int(10) unsigned NOT NULL DEFAULT '0',
`parentid` int(10) unsigned NOT NULL DEFAULT '0',
`username` varchar(100) NOT NULL DEFAULT '',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`title` varchar(250) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`pagetext` mediumtext,
`allowsmilie` smallint(6) NOT NULL DEFAULT '0',
`showsignature` smallint(6) NOT NULL DEFAULT '0',
`ipaddress` varchar(15) NOT NULL DEFAULT '',
`iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
`visible` smallint(6) NOT NULL DEFAULT '0',
`attach` smallint(5) unsigned NOT NULL DEFAULT '0',
`infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
`reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
`importthreadid` bigint(20) NOT NULL DEFAULT '0',
`importpostid` bigint(20) NOT NULL DEFAULT '0',
`converted_2_utf8` int(11) NOT NULL,
`htmlstate` enum('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
PRIMARY KEY (`postid`),
KEY `threadid` (`threadid`,`userid`),
KEY `importpost_index` (`importpostid`),
KEY `dateline` (`dateline`),
KEY `threadid_2` (`threadid`,`visible`,`dateline`),
KEY `converted_2_utf8` (`converted_2_utf8`),
KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
KEY `ipaddress` (`ipaddress`),
KEY `userid` (`userid`,`parentid`),
KEY `user_date` (`userid`,`dateline`)
) ENGINE=MyISAM AUTO_INCREMENT=5402802 DEFAULT CHARSET=latin1
，这是InnoDB表（完全相同）： ：
CREATE TABLE `newbb_innopost` (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`threadid` int(10) unsigned NOT NULL DEFAULT '0',
`parentid` int(10) unsigned NOT NULL DEFAULT '0',
`username` varchar(100) NOT NULL DEFAULT '',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`title` varchar(250) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`pagetext` mediumtext,
`allowsmilie` smallint(6) NOT NULL DEFAULT '0',
`showsignature` smallint(6) NOT NULL DEFAULT '0',
`ipaddress` varchar(15) NOT NULL DEFAULT '',
`iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
`visible` smallint(6) NOT NULL DEFAULT '0',
`attach` smallint(5) unsigned NOT NULL DEFAULT '0',
`infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
`reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
`importthreadid` bigint(20) NOT NULL DEFAULT '0',
`importpostid` bigint(20) NOT NULL DEFAULT '0',
`converted_2_utf8` int(11) NOT NULL,
`htmlstate` enum('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
PRIMARY KEY (`postid`),
KEY `threadid` (`threadid`,`userid`),
KEY `importpost_index` (`importpostid`),
KEY `dateline` (`dateline`),
KEY `threadid_2` (`threadid`,`visible`,`dateline`),
KEY `converted_2_utf8` (`converted_2_utf8`),
KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
KEY `ipaddress` (`ipaddress`),
KEY `userid` (`userid`,`parentid`),
KEY `user_date` (`userid`,`dateline`)
) ENGINE=InnoDB AUTO_INCREMENT=5402802 DEFAULT CHARSET=latin1
如果需要所有innodb_变量设置，我可以将其附加到这篇文章。
更新：
我将所有索引与主索引分开，然后结果如下所示：
Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002
. . | 5402697 | 0 | | 5402759 | 0 | +---------+--------+ 62510 rows in set (29.74 sec)
之后刚刚将一个索引添加回混合，threadid，结果如下：
奇怪的是，没有任何相关索引，与使用索引（！）的88秒相比，完整扫描仅花费了29秒。完整-对于任何现实世界的使用来说仍然太慢了。具有完全相同的硬件配置和完全相同的数据库/表的服务器。
结果几乎相同，首先是MyISAM表：
EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; +------+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | post | ALL | NULL | NULL | NULL | NULL | 5909836 | Using where | +------+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
这是InnoDB表的结果
. . | 5402697 | 0 | | 5402759 | 0 | +---------+--------+ 62510 rows in set (11.58 sec)
更新3：
my.cnf的内容
EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; +------+-------------+-------+------+---------------+----------+---------+-------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+----------+---------+-------+--------+-------+ | 1 | SIMPLE | post | ref | threadid | threadid | 4 | const | 124622 | | +------+-------------+-------+------+---------------+----------+---------+-------+--------+-------+ 1 row in set (0.00 sec)
和inno变量的内容：
. . | 5401593 | 0 | | 5401634 | 0 | +---------+--------+ 62510 rows in set (0.14 sec)
机器的内核数是8，它是
innodb_buffer_pool自Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz起最后一点：用RolandoMYSQLDBA建议的索引运行查询，每个查询大约花费11-20s。
我确实要指出，对我来说至关重要（这是公告板的主表），因为关于线程ID的第一个查询返回的时间少于一秒钟，因为该线程和Google超过60.000 -bot会不断抓取这些线程。
#1 楼
您的查询SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
乍一看，该查询应该只触及表的1.1597％（5390146中的62510）。给定线程ID 51506的密钥分发，应该很快。
REALITY CHECK
无论使用哪个版本的MySQL（Oracle，Percona，MariaDB），它们都不能与所有人都有一个共同点：InnoDB体系结构。
聚集索引
请记住，每个threadid条目有一个主键。这意味着，当您从索引中读取时，它必须在ClusteredIndex（内部命名为gen_clust_index）内进行主键查找。在ClusteredIndex中，每个InnoDB页面都包含数据和PRIMARY KEY索引信息。有关更多信息，请参见我的MyISAM和InnoDB最佳文章。
冗余索引
由于表中的某些索引具有相同的前导列，因此表中的内容非常混乱。 MySQL和InnoDB必须在索引混乱中导航才能到达所需的BTREE节点。您应该通过运行以下命令来减少混乱：
ALTER TABLE newbb_innopost
DROP INDEX threadid,
DROP INDEX threadid_2,
DROP INDEX threadid_visible_dateline,
ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;
为什么要删除这些索引？
前三个索引开始带有threadid
threadid_2和threadid_visible_dateline以相同的三列开头InnoDB缓冲池缓存数据和索引页。 MyISAM仅缓存索引页。
仅在此区域，MyISAM不会浪费时间来缓存数据。那是因为它不是设计用来缓存数据的。 InnoDB缓存它接触的每个数据页和索引页（及其祖母）。如果您的InnoDB缓冲池太小，则可能会在一个查询中缓存页面，使页面无效以及删除所有页面。
表布局
您可以考虑
threadid_visible_dateline和importthreadid来从行中节省一些空间。您将它们作为BIGINT。它们在每行的ClusteredIndex中占用16个字节。 应运行此命令
SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();
这将建议给定数据集这些列应为哪种数据类型。
/>结论
与InnoDB相比，MyISAM的竞争要少得多，尤其是在缓存方面。
虽然您揭示了RAM的数量（
importpostid）和MySQL的版本（32GB），但此难题中还有其他一些您尚未揭示的问题InnoDB设置
内核数
Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002中的其他设置如果您可以将这些内容添加到问题中，我可以进一步阐述。 >
UPDATE 2014-08-28 11:27 EDT
您应该增加线程处理
innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M
我会考虑禁用查询缓存（请参阅我的最新文章，为什么默认情况下从MySQL 5.6开始禁用query_cache_type？）
增加清除线程（如果在多个表上执行DML）
query_cache_size = 0
尝试一下！
评论
我知道InnoDB在纯速度测试中本应较慢，但是达到这个程度呢？我读过MySQL团队一直在努力缩小这一差距。我们仍在处理〜100倍的增长！问题-您是说使用“直接”非聚集B树索引（即不包含PK数据）更好地解决这种性质的查询吗？如果是这样，为什么/尚未执行？ OP所需的功能绝对不是边际用例。
–Vérace
2014年8月27日在22:22
您可以添加指向该图片的完整尺寸版本的链接吗？有些部分很难阅读:-)
–水汪汪
2014年8月28日在6:34
@RolandMySQLDBA感谢您提供信息-我希望您不建议对InnoDB来说100倍的减速是“正常” ...我可以忍受2倍或3倍，但100倍实在太多了。根据要求，我已将缺少的信息添加到我的问题中：）感谢您到目前为止的解释！机器的核心数为8。
– jollyroger
2014年8月28日在6:39
@watery这是完整尺寸的图片：scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing
– RolandoMySQLDBA
2014年8月28日14:53
非常感谢@RolandoMySQLDBA的帮助，不幸的是，即使最后的调整也无济于事，InnoDB大约需要11到20秒来完成。我根据您的答案尝试了一些方法-删除所有索引，并创建覆盖索引。这很有帮助。没有您对索引的解释，我将找不到此解决方案。要检查您的答案并自己写一个答案，解释我的所作所为:)
– jollyroger
2014年8月28日在17:34
#2 楼
@RolandMySQLDBA提供了正确的提示来回答这个问题。问题似乎出在查询中，并且要返回结果，必须读取每个字段（以某种方式从数据库中读取）。我删除了除
PRIMARY KEY之外的所有索引，并插入了这个新索引：现在可以从键本身中提取查询的查询字段ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index (threadid,visible,dateline,userid,attach,ipaddress);。这样就省去了检查真实数据并使用I / O到硬盘的麻烦。帮助。编辑：实际的根本问题并没有解决，我只是用这种技术来解决。 InnoDB在这方面需要认真解决。
评论
我面临着同样的问题。 myisma查询需要0.01秒，而innodb需要60秒，请尝试您的建议。
–user16108
15年7月30日在4:01
@AMB-0.01s闻起来像查询缓存；使用SQL_NO_CACHE再次计时。
–里克·詹姆斯（Rick James）
17年12月3日在1:44
#3 楼
尽管不适用于@jollyroger，因为他已经有了正确的设置，但是我将innodb_buffer_pool_size更改为RAM的70％时获得了重大改进，如为什么myisam慢于Innodb所述。慢，但是好。然后MyISAM使情况变糟，类似于此问题中的慢100倍，并且更改了设置之后，InnoDB的速度比InnoDB快了10倍。#4 楼
根据查询和表，似乎您是从时序表中选择数据。这样，可能是因为同时插入查询时间很慢？如果这两件事是对的，那么我是否可以建议使用ScaleDB作为替代方案？您仍然会使用MariaDB，只是（也许）一个更合适的引擎。
http://www.scaledb.com-主页
http://www.scaledb.com/download-form.php-我们的产品
评论
您应该添加的主要版本不是免费的。
–超立方体ᵀᴹ
15年12月14日在6:42
#5 楼
两种引擎都可以使用INDEX(threadid, attach, postid)
更快地运行查询，因为这将是一个“覆盖”索引，并且实际上将以相同的方式运行（使用索引BTree ）。
此外，我将说这对于“冷”服务器上的任何一个引擎都无法实现：
62510 rows in set (0.13 sec)
请使用每当运行计时时-我们不希望查询缓存污染结论。
另一种快速方法（不考虑I / O缓存）：从
SQL_NO_CACHE更改为PRIMARY KEY(threadid, postid),
INDEX(postid)
原因是这将导致所有相关行相邻，从而需要更少的I / O等。
PRIMARY KEY (postid)用于保留INDEX(postid)很高兴。警告：这会弄乱所有辅助键-有些会更快，有些会更慢。
评论
评论不作进一步讨论；此对话已移至聊天。