时间戳

首页 > 解决方案 > MySQL 5.7 简单计数查询有时快有时很慢

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

问题描述

【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】但在slave服务器上运行良好且速度非常快，几乎都是2+秒。

请帮忙，如何调整性能？

标签： mysqlperformancecount

解决方案

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

更多详细信息：

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

治愈：

  • 不要运行“大”查询。计算一百万行可能需要 100MB 的缓存。我不能说第 3 项的大查询是什么。
  • 更好的索引。对于那个查询，INDEX(flag, bmonth)是最优的并且需要更少的数据，从而加快了非缓存 (35s) 版本的速度。此外，由于更小，它的数据不太可能被撞出缓存。
  • 避免交换。5GB RAM 中的 4GB buffer_pool 可能会导致交换。这对于 MySQL 的性能来说是很糟糕的。我强烈建议您将 buffer_pool_size 降低到 3G。
  • 更多 RAM（和更大的 buffer_pool）。
  • 找到其他“大”查询并努力减少使用数据。
  • 质疑需要一百万左右的精确计数。这涉及 UI 更改以及可能“估计”计数的方法。
  • 汇总表——每天晚上统计前一天发生了多少；将其保存在汇总表中，并针对它运行更快的查询。（我可以进一步讨论这个问题。）

缓冲区和页面使用指标是“计数器”，需要除以Uptime“每秒”。进一步分析：http: //mysql.rjweb.org/doc.php/mysql_analysis

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

推荐阅读