mysql - mysql查询间歇性地返回空结果

Question

我有以下查询，有时在主服务器上返回一个空集，但从不在只读副本上返回，并且两个数据库上都有匹配的数据。这是随机的，我想知道是否有 mysql 设置或带有查询缓存的东西。在 rds 上运行 mysql 5.6.40-log。

我试过做optimizer_switch="index_merge_intersection=off"，但没有奏效。

UPDATE optimizer_switch="index_merge_intersection=off 似乎有效，但我在进行此更改后清除了查询缓存，问题似乎已自行解决）。发生的一个非常奇怪的问题是查询通过 mysql 命令行 100%时间；但是在我清除查询缓存之前，Web 应用程序无法工作（即使它以同一用户身份连接）。

一旦我优化表phppos_items，它会修复它一点点（3 分钟），然后它又回到不稳定状态（主要是空集）。这些都是 innodb 表。

设置：

https://gist.github.com/blasto333/82b18ef979438b93e4c39624bbf489d7

似乎在一天中的繁忙时间更频繁地返回空集。服务器是 rds m4.large，有 500 个数据库，每个数据库 100 个表

询问：

SELECT SUM( phppos_sales_items.damaged_qty ) AS damaged_qty, 
        SUM( phppos_sales_items.subtotal ) AS subtotal, 
        SUM( phppos_sales_items.total ) AS total, 
        SUM( phppos_sales_items.tax ) AS tax, 
        SUM( phppos_sales_items.profit ) AS profit
FROM  `phppos_sales` 
    JOIN  `phppos_sales_items` ON  `phppos_sales_items`.`sale_id` =  `phppos_sales`.`sale_id` 
    JOIN  `phppos_items` ON  `phppos_sales_items`.`item_id` =  `phppos_items`.`item_id` 
WHERE  `phppos_sales`.`deleted` =0
AND  `sale_time` BETWEEN  '2019-01-01 00:00:00' AND  '2019-12-31 23:59:59'
AND  `phppos_sales`.`location_id` IN ( 1 ) 
AND  `phppos_sales`.`store_account_payment` =0
AND  `suspended` <2
AND  `phppos_items`.`deleted` =0
AND  `phppos_items`.`supplier_id` = '485'
GROUP BY  `phppos_sales_items`.`sale_id` 

解释：

+----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
| id | select_type | table              | type        | possible_keys                                                                                 | key                         | key_len | ref                                                   | rows | Extra                                                                                                   |
+----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | phppos_items       | index_merge | PRIMARY,phppos_items_ibfk_1,deleted,deleted_system_item                                       | phppos_items_ibfk_1,deleted | 5,4     | NULL                                                  |   44 | Using intersect(phppos_items_ibfk_1,deleted); Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | phppos_sales_items | ref         | PRIMARY,item_id,phppos_sales_items_ibfk_3,phppos_sales_items_ibfk_4,phppos_sales_items_ibfk_5 | item_id                     | 4       | phppoint_customer.phppos_items.item_id       |   16 | NULL                                                                                                    |
|  1 | SIMPLE      | phppos_sales       | eq_ref      | PRIMARY,deleted,location_id,sales_search,phppos_sales_ibfk_10                                 | PRIMARY                     | 4       | phppoint_customer.phppos_sales_items.sale_id |    1 | Using where                                                                                             |
+----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

标签： mysql