我有一个 MySQL 5.7.30 作为 Magento 2 商店的存储。在 magento 重新索引过程（将数据转换为各种表以便更快地检索的 PHP 过程）期间，会创建并填充一些临时表。这些查询应该非常快，但是，有些查询会无限期地运行。这会导致其他查询挂起，因为它们正在等待插入正在读取的表的表锁。这会导致整个网站瘫痪，因为没有查询会完成，从而导致 Web 前端出现超时错误。
即使终止了重新索引进程，情况没有改变。插入查询仍在运行，其他查询正在等待表锁 - 直到我在 MySQL 中终止该查询。
以下是此类死锁情况（运行重新索引进程时发生）期间 MySQL 中的进程列表：
mysql> select id, db,command, state, left(info,100) from information_schema.processlist;
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
| id | db | command | state | left(info,100) |
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 8165312 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_bb745c5e7f2610352c060b36709e54032aaf8c15` SELECT `e`.`entity_id` FROM `cata |
| 8165311 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_f91821f67870f011d3f8a38e9908c3235747584b` SELECT `e`.`entity_id` FROM `cata |
| 8165289 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_2d834f92e9b71a91a5c2520192539cd38560d22c` SELECT `e`.`entity_id` FROM `cata |
| 8165288 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_abca7e583ce39ed454be82b78bdc29b389a4ee74` SELECT `e`.`entity_id` FROM `cata |
| 8165260 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_18b43873915bfb0015033be4ee261cae3e897dee` SELECT `e`.`entity_id` FROM `cata |
| 8165244 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_d661c3a49566eb6a35f5043718ae99047b176662` SELECT `e`.`entity_id` FROM `cata |
| 8165217 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_da8bbd709684db07102be86caeae8712790bb37e` SELECT `e`.`entity_id` FROM `cata |
| 8165261 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_dd977f7939670fc3eb19c6b5c89be77b8bba52b1` SELECT `e`.`entity_id` FROM `cata |
| 8165259 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_28d513e50adac3dd498f9d4087f648cf73ad8e9d` SELECT `e`.`entity_id` FROM `cata |
| 8165199 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_438c79baa1470f9f4e435b50046e5f65c06c58dc` SELECT `e`.`entity_id` FROM `cata |
| 8165153 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_a12ea76415ddb9a0179ed2dbdf48b88b9b3132e6` SELECT `e`.`entity_id` FROM `cata |
| 8165020 | magento2 | Query | Waiting for table metadata lock | RENAME TABLE catalog_product_index_price TO catalog_product_index_price_outdated,catalog_product_ind |
| 8165251 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_32a2d91876b5b4fa38eb6f3773074ad9d4854390` SELECT `e`.`entity_id` FROM `cata |
| 8165247 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_be2127ec0bdd7a56beb8e59fdc5a5a1164ef3bbb` SELECT `e`.`entity_id` FROM `cata |
| 8165017 | magento2 | Sleep | | NULL |
| 8164957 | magento2 | Query | Filling schema table | select id, db,command, state, left(info,100) from information_schema.processlist |
| 8165240 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_1ca9188823f3af39434548117fde49aacd9cf7c1` SELECT `e`.`entity_id` FROM `cata |
| 8165226 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_94f703629874d7a4b20c58a4d863266d1f7f6ba4` SELECT `e`.`entity_id` FROM `cata |
| 8165219 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_0e88fc675d33b150e9cc7718cb70820ca88bc80c` SELECT `e`.`entity_id` FROM `cata |
| 8165210 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_b6f322baae91918f8b91001156a149072e904f41` SELECT `e`.`entity_id` FROM `cata |
| 8165206 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_e62016ea2ea8631932f1dd5643c93d81922c4949` SELECT `e`.`entity_id` FROM `cata |
| 8165194 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_e69208892fe0386e6baff43b2ff244d49fa6eb4c` SELECT `e`.`entity_id` FROM `cata |
| 8165202 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_3a805f9691560f0b892b4da18c0041277ce1b99e` SELECT `e`.`entity_id` FROM `cata |
| 8165188 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_c97916867bdb1e1427abeac76cee57a164038c50` SELECT `e`.`entity_id` FROM `cata |
| 8165179 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_2b4ee30f3f92d09e3843e9b6a65f31751d180770` SELECT `e`.`entity_id` FROM `cata |
| 8165173 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_860d16fe274ea842d23c9bd73c74fef4abf5ebc4` SELECT `e`.`entity_id` FROM `cata |
| 8165167 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_a963adc29cdca3c797b041e777010031d3eb5fc6` SELECT `e`.`entity_id` FROM `cata |
| 8165155 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_895b1ef14a24275b53c171d0d2bb5cc94c1ae4b1` SELECT `e`.`entity_id` FROM `cata |
| 8165147 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `cata |
| 8165142 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_dee736e9ad4f2d069f5750cd5411f566775e42cb` SELECT `e`.`entity_id` FROM `cata |
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
如您所见，目前有 2 个查询正在将数据插入到 temp_XXX 表中。其他查询正在等待锁定。在这种情况下，只有索引器在运行，因为网站处于维护状态。在网站可访问的情况下，还会有其他查询（例如选择产品）等待表锁定。
终止重新索引进程后（通过Ctrl+C在运行它的终端上），情况如下：
mysql> select id, db,command, state, left(info,100) from information_schema.processlist;
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
| id | db | command | state | left(info,100) |
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
| 8165147 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `cata |
| 8165142 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_dee736e9ad4f2d069f5750cd5411f566775e42cb` SELECT `e`.`entity_id` FROM `cata |
| 8164957 | magento2 | Query | Filling schema table | select id, db,command, state, left(info,100) from information_schema.processlist |
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
可以看到，之前的两条语句仍然在运行。
具体表述如下：
INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store3` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=17036
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_typ_1` ON `layered_nav_f_filter_typ_1`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_typ_1`.`attribute_id` = '18249' AND `layered_nav_f_filter_typ_1`.`store_id` = '3' AND `layered_nav_f_filter_typ_1`.`value` IN (2894)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_typ_1__stock` ON `layered_nav_f_filter_typ_1`.`source_id` = `layered_nav_f_filter_typ_1__stock`.`product_id` AND `layered_nav_f_filter_typ_1__stock`.`website_id` = 0 AND `layered_nav_f_filter_typ_1__stock`.`stock_id` = 1 AND `layered_nav_f_filter_typ_1__stock`.`stock_status` = 1
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_kabelmantel` ON `layered_nav_f_filter_kabelmantel`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_kabelmantel`.`attribute_id` = '18253' AND `layered_nav_f_filter_kabelmantel`.`store_id` = '3' AND `layered_nav_f_filter_kabelmantel`.`value` IN (3044)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_kabelmantel__stock` ON `layered_nav_f_filter_kabelmantel`.`source_id` = `layered_nav_f_filter_kabelmantel__stock`.`product_id` AND `layered_nav_f_filter_kabelmantel__stock`.`website_id` = 0 AND `layered_nav_f_filter_kabelmantel__stock`.`stock_id` = 1 AND `layered_nav_f_filter_kabelmantel__stock`.`stock_status` = 1
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_paarzahl_1` ON `layered_nav_f_filter_paarzahl_1`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_paarzahl_1`.`attribute_id` = '18251' AND `layered_nav_f_filter_paarzahl_1`.`store_id` = '3' AND `layered_nav_f_filter_paarzahl_1`.`value` IN (3009,3004)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_paarzahl_1__stock` ON `layered_nav_f_filter_paarzahl_1`.`source_id` = `layered_nav_f_filter_paarzahl_1__stock`.`product_id` AND `layered_nav_f_filter_paarzahl_1__stock`.`website_id` = 0 AND `layered_nav_f_filter_paarzahl_1__stock`.`stock_id` = 1 AND `layered_nav_f_filter_paarzahl_1__stock`.`stock_status` = 1 WHERE (stock_status_index.stock_status = 1)
此插入的基本选择花费 0.0 毫秒并返回 ~1000 条记录。
当这些查询仍在运行时，其他查询继续挂起，等待表锁。只有在我KILL为这些插入发出语句后，网站才恢复正常运行。
还请注意，这种情况并非每次都会发生。有时（少数尝试），语句可以顺利完成。
注意：这似乎只发生在股票和价格表的重新索引中。所有表的表引擎都是 InnoDB。
价格表结构：
CREATE TABLE `catalog_product_index_price` (
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
`customer_group_id` int(10) unsigned NOT NULL COMMENT 'Customer Group ID',
`website_id` smallint(5) unsigned NOT NULL COMMENT 'Website ID',
`tax_class_id` smallint(5) unsigned DEFAULT 0 COMMENT 'Tax Class ID',
`price` decimal(20,6) DEFAULT NULL COMMENT 'Price',
`final_price` decimal(20,6) DEFAULT NULL COMMENT 'Final Price',
`min_price` decimal(20,6) DEFAULT NULL COMMENT 'Min Price',
`max_price` decimal(20,6) DEFAULT NULL COMMENT 'Max Price',
`tier_price` decimal(20,6) DEFAULT NULL COMMENT 'Tier Price',
PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`),
KEY `CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID` (`customer_group_id`),
KEY `CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE` (`min_price`),
KEY `CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE` (`website_id`,`customer_group_id`,`min_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Price Index Table'
表变量包括：
mysql> show variables like '%table%';
+----------------------------------------+----------------------+
| Variable_name | Value |
+----------------------------------------+----------------------+
| aria_encrypt_tables | OFF |
| aria_used_for_temp_tables | ON |
| big_tables | OFF |
| encrypt_tmp_disk_tables | OFF |
| innodb_encrypt_tables | OFF |
| innodb_encrypt_temporary_tables | OFF |
| innodb_file_per_table | ON |
| innodb_ft_aux_table | |
| innodb_ft_server_stopword_table | |
| innodb_ft_user_stopword_table | |
| innodb_table_locks | ON |
| innodb_undo_tablespaces | 0 |
| lower_case_table_names | 0 |
| max_heap_table_size | 268435456 |
| max_tmp_tables | 32 |
| old_alter_table | DEFAULT |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| replicate_do_table | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
| table_definition_cache | 400 |
| table_open_cache | 1024 |
| table_open_cache_instances | 7 |
| tmp_disk_table_size | 18446744073709551615 |
| tmp_memory_table_size | 268435456 |
| tmp_table_size | 268435456 |
| updatable_views_with_limit | YES |
| use_stat_tables | NEVER |
+----------------------------------------+----------------------+
30 rows in set (0.00 sec)
问题：查询从表中读取数据并插入（专门为此进程创建的临时表）如何导致其他读取无限期挂起？启动查询的进程被终止后，此查询如何继续运行？
最重要的是：我该如何解决这种情况？