MySQL innoDB表上每秒有300万行和50-100个查询的随机查询速度减慢

Question

我有一个MySQL数据库，其中有一个约3亿行的InnoDB表。最多有10个连接的客户端每秒发送50-60个读取查询。几个月来，一切都很顺利，直到最近，MySQL在使用大量CPU的情况下开始停滞(100%+.uptime命令显示的值为15、12、15)。原本需要500ms的查询现在需要几秒钟，从几十秒到几百秒。执行SHOW PROCESSLIST显示挂在Sending data状态的查询。

我不知道为什么，我们非常感谢您的帮助。

服务器

Intel(R)Xeon(R)CPU E5@2.40GHz | 12 CPU | 32 GB RAM

my.cnf

innodb_file_per_table = 1
tmp-table-size                 = 32M
max-heap-table-size            = 32M
innodb-log-files-in-group      = 2
innodb-flush-method            = O_DIRECT
innodb-log-file-size           = 512M
innodb-buffer-pool-size        = 26G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb_file_format = barracuda    

表(名称：记录)

+----------------+------------+------+-----+---------+----------------+
| Field          | Type       | Null | Key | Default | Extra          |
+----------------+------------+------+-----+---------+----------------+
| id             | bigint(20) | NO   | PRI | NULL    | auto_increment |
| identifier     | int(11)    | YES  | MUL | 0       |                |
| timestamp      | int(11)    | YES  | MUL | NULL    |                |
| rtype          | int(5)     | YES  | MUL | NULL    |                |
| x1             | int(11)    | YES  |     | NULL    |                |
| x2             | int(11)    | YES  |     | NULL    |                |
| net            | bigint(20) | YES  |     | NULL    |                |
| created_at     | datetime   | NO   |     | NULL    |                |
+----------------+------------+------+-----+---------+----------------+

索引并在WHERE查询中使用：

• 时间戳(UNIX时间戳为INT)
• 标识符
• rtype(五种可能值，1-5)

数据大小

Data_length  = ~18 GB
Index_length = ~16 GB

查询

SELECT identifier, timestamp, x1 AS a, x2 AS b, net
FROM records
WHERE
identifier=1010
AND timestamp >=1463111100
AND timestamp <= 1463738400
AND rtype=5
ORDER BY timestamp;

(返回大约900行。有时在不到一秒钟内完成，有时在10-100秒内完成)

查询分析

select_type   = SIMPLE
type          = index_merge
possible_keys = indeXidentifier, indeXtimestamp, indeXrtype
key           = indeXidentifier, indeXrtype
key_len       = 4,5
rows          = 10641
Extra         = Using intersect(indeXidentifier,indeXrtype); Using where

Answer 1

我有两个建议：

1。更改多列索引中的列顺序。推荐顺序为：标识符、rtype、时间戳。

索引唯一扫描比索引范围扫描快，因此最好先出现。

2。更改您的查询如下：

select * from(
SELECT identifier, timestamp, x1 AS a, x2 AS b, net
FROM records
WHERE
identifier=1010
AND timestamp >=1463111100
AND timestamp <= 1463738400
AND rtype=5
) t1 ORDER BY timestamp;

避免使用索引进行排序。

Answer 2

对于问题：中的SELECT，这两种方法中的任何一种都是最优的

INDEX(rtype, identifier, timestamp)
INDEX(identifier, rtype, timestamp)

原理是先把WHERE的所有"=常数"部分放在上面，然后再加一个(timestamp上的"范围")更多烹饪书技巧。

没有必要把它放在子查询中——这只会通过构建不必要的tmp表来减慢速度。

为什么它突然变慢了？可能的原因是"缓存"。添加新索引后，RAM中缓存的内容就减少了，任何SELECTs都会大量访问磁盘。

让我们仔细检查一下查询计划。请提供EXPLAIN SELECT ...。它应该是一行长，表示它使用的是3列索引，而不是说"intersect"、"temporary"或"filesort"。

如果还有什么问题，请提供解释，加上SHOW CREATE TABLE(它比DESCRIBE更具描述性。)

另一件需要确保的事情是：关闭查询缓存。在my.cnf中添加/更改这些设置并重新启动服务器：

query_cache_type = OFF
query_cache_size = 0

INSERTs是如何发生的？一排一排？如果它们可以"分批"，甚至一次几十个，那将有很大帮助。

由于您在评论CPU，听起来您有一些查询是CPU绑定的，而不是I/O绑定的。做SHOW FULL PROCESSLIST;——你看到一些带有大"时间"的查询了吗？这是你还没有提到的事情吗？

请运行

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

(这些价值观可能会引发一场关于"雷鸣般的牛群"的讨论。)