MySQL 5.7 简单计数查询有时快有时很慢

Question

【1】这是一个简单的计数查询:

SELECT SQL_NO_CACHE count(*) FROM fees WHERE 1 = 1 AND flag = 0 AND bmonth = '201906';

【2】查询结果:

+----------+
| count(*) |
+----------+
| 1021863 |
+----------+
1 row in set, 1 warning (34.77 sec)

【3】有时是(大多数时候):

+----------+
| count(*) |
+----------+
| 1021863 |
+----------+
1 row in set, 1 warning (1.89 sec)

【4】几分钟后，它变得很慢，就像第一个结果显示一样。

【5】查询将使用以下索引，并且 uk_customer 包含 bmonth 列:

+----+-------------+----------------------+------------+------+--------------------------------------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------------+------------+------+--------------------------------------------+----------------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | fees | NULL | ref | uk_warehouse,uk,uk_calcu | uk_customer | 4 | const | 2028586 | 10.00 | Using where |
+----+-------------+----------------------+------------+------+--------------------------------------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

================================================== =======================

【6】服务器内存为5G，缓冲池大小为4G。和索引大小:

+--------------------------------------------------+---------------------------+------------+--------------+
| Table_Name | Index_Name | Page_Count | Size_in_MB |
+--------------------------------------------------+---------------------------+------------+--------------+
| `test`.`fees` | PRIMARY | 46503 | 567.04021835 |
| `test`.`fees` | uk_customer | 2521 | 34.86496449 |
| `test`.`fees` | uk_calcu | 3178 | 30.72235107 |

【7】innodb设置如下:

innodb_page_size = 16384
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2048
innodb_lock_wait_timeout = 5
lock_wait_timeout=5
innodb_io_capacity = 1000
innodb_io_capacity_max = 6000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
#innodb_undo_logs = 128
innodb_undo_tablespaces = 2
innodb_flush_neighbors = 0
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16777216
#innodb_max_undo_log_size = 2147483648
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 16
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 256k
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_file_per_table = 1

当前缓冲区使用情况:

+---------------------------------------+---------------+
| Variable_name | Value |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 753136482 |
| Innodb_buffer_pool_read_ahead_evicted | 185375 |
| Innodb_buffer_pool_read_requests | 1355375133341 |
| Innodb_buffer_pool_reads | 2418853408 |
+---------------------------------------+---------------+
5 rows in set (0.00 sec)

页面使用:

+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_data | 246447 |
| Innodb_buffer_pool_pages_dirty | 2738 |
| Innodb_buffer_pool_pages_flushed | 84827986 |
| Innodb_buffer_pool_pages_free | 3982 |
| Innodb_buffer_pool_pages_misc | 11699 |
| Innodb_buffer_pool_pages_total | 262128 |
+----------------------------------+----------+
6 rows in set (0.00 sec)

================================================== =======================【8】但是在从服务器上运行得很好，速度也很快，都差不多2秒多了。

请帮忙，如何调整性能？

Answer 1

这就是“缓存”的效果。 InnoDB 本质上有一个主缓存，即“buffer_pool”，其大小由 innodb_buffer_pool_size 控制。总数据集有多大？

更多详细信息:

1. 由于 buffer_pool 中没有缓存任何内容，因此必须从磁盘读取内容，因此需要花费 35 秒的时间。
2. 如果您很快再次运行查询，则不需要 I/O，因为内容仍在缓存中。因此是 2 秒。
3. 如果在再次运行之前有一段时间间隔，其他数据就会进入 buffer_pool，从而淘汰查询的 block (可能是所有 block )。不是又回到了 35 秒。

治疗:

• 不要运行“大”查询。计算一百万行可能需要 100MB 的缓存。我无法说出第 3 项的大查询是什么。
• 更好的索引。对于该查询，INDEX(flag, bmonth) 是最佳选择，需要较少的数据，从而加快非缓存(35 秒)版本的速度。此外，由于较小，其数据不太可能从缓存中删除。
• 避免交换。 5GB RAM 中的 4GB buffer_pool 可能会导致交换。对于 MySQL 的性能来说，这很糟糕。我强烈建议您将 buffer_pool_size 降至 3G。
• 更多 RAM(以及更大的 buffer_pool)。
• 找到其他“大”查询并努力减少使用的数据。
• 质疑是否需要百万级的精确计数。这涉及用户界面的更改以及“估计”计数的方式。
• 汇总表——每天晚上都会统计前一天发生的情况；将其保存在汇总表中，并对其运行更快的查询。 (我可以进一步讨论这个问题。)

缓冲区和页面使用指标是“计数器”，需要除以正常运行时间才能得到“每秒”。进一步分析:http://mysql.rjweb.org/doc.php/mysql_analysis

汇总表:http://mysql.rjweb.org/doc.php/summarytables