优化大型MySQL选择WHERE IN子句

Question

如何选择以下情况下的优化位置？

我有一个有超过1亿行的表，只有3列。主键(col1)是一个127 varchar。我正在执行一个选择col1，其中col1 IN (.)其中IN子句是5,000个字符串。我只是想看看数据库中的5,000个字符串中哪一个是主键。

使用专用服务器和InnoDB表，查询的时间从3到10秒不等，这是不可接受的。我不认为即使在选择5k行时，100行million+行对MySQL来说也太难进行选择，但是我可能错了？

怎样才能对此进行优化呢？我读过一些关于全文键的文章--因为键是127 varchar，这些键会更好吗？或者某种类型的JOIN或UNION会加速对大型IN子句的查询吗？

任何帮助都将不胜感激！谢谢!

--编辑--

SHOW ENGINE INNODB STATUS;

| InnoDB |      | 
=====================================
2014-07-14 10:59:19 2bf5cf25700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6664 srv_active, 0 srv_shutdown, 142740 srv_idle
srv_master_thread log flush and writes: 149372
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 417120
OS WAIT ARRAY INFO: signal count 449454
Mutex spin waits 323558, rounds 2089912, OS waits 48403
RW-shared spins 49101, rounds 462555, OS waits 12976
RW-excl spins 406820, rounds 11261153, OS waits 350839
Spin rounds per wait: 6.46 mutex, 9.42 RW-shared, 27.68 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 21503
Purge done for trx's n:o < 21472 undo n:o < 0 state: running but idle
History list length 641
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 15895, OS thread handle 0x2bf5cf25700, query id 399305 localhost root init
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1203330 OS file reads, 2141172 OS file writes, 78570 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 52 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 117811837521
Log flushed up to   117811837521
Pages flushed up to 117811837521
Last checkpoint at  117811837521
0 pending log writes, 0 pending chkp writes
21279 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 720503
Buffer pool size   8191
Free buffers       1024
Database pages     7115
Old database pages 2606
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4741, not young 1045985243
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1202370, created 1138775, written 2068616
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7115, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 23063, id 3020409116416, state: sleeping
Number of rows inserted 113995729, updated 144489445, deleted 0, read 171054938
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

Answer 1

总的来说，我成功地将冗长的in操作替换为与临时表的连接。这是有意义的，因为RDBMS被优化以尽可能高效地执行联接，并且处理冗长的IN列表很可能是通过对in列表中的每个值重复查询来完成的。您看到的性能问题可以很容易地通过查询运行5,000次这一简单事实来解释。

Answer 2

下面是一个很长的时间，因为我们对您的硬件、InnoDB配置和查询细节一无所知，但我敢打赌您使用了错误的作业工具(InnoDB引擎)。

您想要实现的是创建一个非常重的索引(最多127个字符，这可能会占用-this的范围--每个条目127 *3个字节)，它是使用InnoDB可用的唯一方法( B+Tree )创建的。此外，在主键周围聚集行时，整行实际上都在索引上，而访问主键意味着使用整个行内容访问页面。

简而言之，您有一个唯一的索引，它包含整个表，并且应该或多或少地适合内存(不一定全部，但在这种情况下，您的工作集似乎是您的大部分行)。您的InnoDB缓冲池有多大？您的缓冲池命中率如何？您可以使用SHOW ENGINE INNODB STATUS检查这两个参数。我敢打赌，你的缓冲池太小，甚至你的物理内存不足以容纳你的工作装置。在这两种情况下，这都可能迫使InnoDB对每个查询执行IOPS。您可能会认为，您不需要拥有所有的缓存才能使一切正常工作，而且您应该是正确的。但是对于特定的工作负载(大型PKs)，InnoDB并不是最好的引擎。在其他关系数据库管理系统和MySQL引擎中可用的散列索引可能更小、更快，但InnoDB不支持它。此外，具有大量行的IN + list of values可能不是最理想的查询方式(在MySQL级别)，但它肯定比单独执行查询更快。

• 确保查询计划器(您可以使用EXPLAIN检查它)使用range连接类型，而不是执行完整的表扫描。
• 在此之后，我首先建议您调优InnoDB缓冲区，以减少InnoDB缓存丢失。
• 接下来要尝试的是通过创建一个小的二级索引来模拟哈希索引。这种方法在“高性能MySQL”一书中作了解释.。这样，只有小的次要索引将被缓存在内存中，并且它可能更适合您的物理内存。
• 如果这些方法不起作用，在更改技术之前，我建议您尝试使用不同的引擎来处理键值数据集。也许TokuDB能更好地处理这件事？另外，集成到MySQL/InnoDB5.6中的memcached接口可能是另一种解决方案？多重获取似乎很适合你的解决方案。
• 最后，如果你的负载主要是阅读，你可以尝试外部技术，比如你提到的全文搜索引擎-as，但是要小心，因为这些软件往往依赖于模糊搜索，而且可能忽略一些结果，而且它们往往不完全兼容(这些都是为了换取查询速度而不得不牺牲的东西)。