mysql - 对具有 3 亿行和每秒 50-100 个查询的 MySQL innoDB 表的查询随机减慢

Question

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

我无法弄清楚原因，感谢任何帮助。

服务器

英特尔(R) 至强(R) CPU E5 @ 2.40GHz | 12 个中央处理器 | 32 GB 内存

我的.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;

避免使用索引进行排序。