Mysql
現有查詢的執行計劃已更改 - 高 CPU 使用率
幾週前，由於 CPU 使用率飆升，我們的生產 MySQL 數據庫收到了警報。我們將該問題確定為已經存在了幾個月的查詢突然出現了異常行為。該查詢的執行計劃是：
+----+-------------+------------+------------+------+----------------------------------------------------------+----------------------------+---------+----------------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+----------------------------------------------------------+----------------------------+---------+----------------------------+------+----------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2485 | 100.00 | NULL | | 2 | DERIVED | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7420 | 10.00 | Using where; Using temporary; Using filesort | | 2 | DERIVED | app | NULL | ref | applications_status_ix,applications_ibfk_3 | applications_ibfk_3 | 4 | prod_v3.c.id | 750 | 0.41 | Using where | | 2 | DERIVED | inv | NULL | ref | app_id | app_id | 4 | ref.app.app_id | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+----------------------------------------------------------+----------------------------+---------+----------------------------+------+----------+----------------------------------------------+我們所做的是添加要使用的索引提示
applications_status_ix，然後查詢性能恢復正常，CPU 使用率下降：+----+-------------+------------+------------+--------+-------------------------------+-------------------------------+---------+----------------------------+--------+----------+---------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+-------------------------------+-------------------------------+---------+----------------------------+--------+----------+---------------------------------------------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5077 | 100.00 | NULL | | 2 | DERIVED | app | NULL | range | applications_status_ix | applications_status_ix | 1 | NULL | 464405 | 10.00 | Using index condition; Using where; Using temporary; Using filesort | | 2 | DERIVED | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | app.company_id | 1 | 10.00 | Using where | | 2 | DERIVED | inv | NULL | ref | app_id | app_id | 4 | app_id | 1 | 100.00 | NULL | +----+-------------+------------+------------+--------+-------------------------------+-------------------------------+---------+----------------------------+--------+----------+---------------------------------------------------------------------+基本上，在查詢中，我們希望某些應用程序狀態在表上具有最小的外觀。這就是為什麼即使在這種基數下也能更好地使用該索引的原因：
+---------------------+------------+---------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------------+------------+---------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | applications | 1 | applications_status_ix | 1 | status | A | 9 | NULL | NULL | | BTREE | | | | applications | 1 | applications_ibfk_3 | 1 | company_id | A | 16240 | NULL | NULL | | BTREE | | | +---------------------+------------+---------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+所以我有兩個問題：
- 為什麼查詢計劃者會突然決定對已經使用了一段時間的查詢使用不同的執行計劃？我沒有確鑿的證據，但我真的懷疑基數與我們現在所擁有的相比發生了重大變化。
- 試圖找到一個解決方案，我們發現了一個關於直方圖（在其他 DBMS 上呼叫值分佈）。但不幸的是，它們僅在 MySQL 8 上可用，我們仍在使用 5.7 版本。您是否碰巧知道我們的 MySQL 版本的任何其他解決方法？
編輯1：
所以正在執行的查詢是
SELECT sum((CASE WHEN `alias_50656806`.`status` = 'CAPTURED' THEN `alias_50656806`.`amount` ELSE `alias_50656806`.`due_by` END - `alias_50656806`.`recovered_amount`)) FROM (SELECT `app`.`app_id`, `app`.`status`, `app`.`amount`, `app`.`purchases`, `app`.`due_by`, sum(CASE WHEN (`inv`.`paid_at` IS NULL AND DATEDIFF(current_date(), `inv`.`created_at`) < 10) THEN 0 ELSE IFNULL(`inv`.`amount_due`, 0) END) AS `recovered_amount` FROM `applications` AS `app` JOIN `companies` AS `c` ON `app`.`company_id` = `c`.`id` LEFT OUTER JOIN `application_invoices` AS `inv` USING (`app_id`) WHERE (NOT (`app`.`repurchases`) AND `app`.`status` IN ('CAPTURED', 'LOCKED', 'ERROR') AND `c`.`is_test` = FALSE AND 1 = 1) GROUP BY `app`.`credit_app_id`, `app`.`status`, `app`.`credit_amount`, `app`.`cashless_repurchases`, `app`.`due_by`) AS `alias_50656806`;表格大小為：
- 應用程序有 12701431 條記錄。
- 公司有 7500 條記錄
- 狀態分佈是
STATUS COUNT (*) RECOVER_FUNDS 46400 ERROR 18792 LOCKED 3 CAPTURED 151854應用程序表 DDL 是
CREATE TABLE `applications` ( `app_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updated_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), `status` enum('PREAPPROVED','INCOMPLETE','DENIED','PENDING_DATA','PENDING_CC','FAILED_DATA','ERROR','READY','CAPTURED','EXPIRED','SETTLED','LOCKED','OPTED_OUT','RECOVER_FUNDS','EXCLUDED','ABANDONED','BAD_OPT') COLLATE utf8_bin NOT NULL, `account_code` enum('REF','NO_REF') COLLATE utf8_bin NOT NULL, `amount` int(10) unsigned NOT NULL, `customer_id` int(11) unsigned NOT NULL, `company_id` int(11) unsigned NOT NULL, `flow_id` int(11) unsigned NOT NULL, `app_ext_id` varchar(20) COLLATE utf8_bin NOT NULL, `cashless_repurchases` tinyint(1) NOT NULL DEFAULT '0', `random_no` float unsigned NOT NULL DEFAULT '0.5', `breakage_amount` int(11) DEFAULT NULL, `first_repurch` timestamp(3) NULL DEFAULT NULL, `last_repurch` timestamp(3) NULL DEFAULT NULL, `repurch_count` tinyint(3) unsigned NOT NULL DEFAULT '0', `total_repurch_amount` int(10) unsigned NOT NULL DEFAULT '0', `due_by` int(10) unsigned DEFAULT NULL, `due_by_customer` int(10) unsigned DEFAULT NULL, `last_modified_by` varchar(64) COLLATE utf8_bin DEFAULT NULL, `app_user_id` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`app_id`), UNIQUE KEY `flow_id` (`flow_id`), UNIQUE KEY `app_ext_id` (`app_ext_id`), KEY `applications_customer_ix` (`customer_id`), KEY `applications_status_ix` (`status`), KEY `applications_ibfk_3` (`company_id`), CONSTRAINT `applications_ibfk_1` FOREIGN KEY (`flow_id`) REFERENCES `flow_entries` (`id`), CONSTRAINT `applications_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `applications_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`), CONSTRAINT `applications_ibfk_4` FOREIGN KEY (`status`) REFERENCES `application_statuses` (`status`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=12709869 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
兩個執行計劃中列中的值
filtered看起來都非常偏離目標。也許condition_fanout_filter在執行這些多連接查詢的會話中禁用優化器開關會有所幫助（請參閱有關條件過濾的更多資訊）。為什麼查詢計劃者會突然決定對已經使用了一段時間的查詢使用不同的執行計劃？我沒有確鑿的證據，但我真的懷疑基數改變了……
applications即使要選擇的記錄數量大部分是恆定的，它也可能會考慮不斷增長的表大小。也許優化器切換以使其更喜歡更大的表作為前導將有助於降低其他查詢中類似失敗的可能性（有點像 Oracle 的DB_FILE_MULTIBLOCK_READ_COUNT）。但我在 MySQL 中看不到任何此類功能。您是否碰巧知道任何其他解決方法
查詢優化器以某種方式能夠估計所選行數
applications：464405。但這至少是“表格大小”部分實際數量的兩倍。也許只是ANALYZE TABLE applications有助於減少估計並在沒有提示的情況下獲得更好的執行計劃？
為什麼查詢計劃者會突然決定對已經使用了一段時間的查詢使用不同的執行計劃？
數據大小或模式的變化 - 隨著事情的發展，您可能會超過設定的限制（或兩個對象之間的基數之間的比率），這會導致計劃看起來更糟。這就是為什麼在 QA 期間甚至在開發環境中使用實際數據大小（包括幾年內的預期增長，加上一點以防萬一增長更快）進行測試非常重要的原因。
如果您查看您的強制索引計劃，它預計需要觸及近 50 萬行，而不是其他計劃的一萬行。我不確定為什麼它現在會翻轉而不是更早，也許它之前使用過一個中間計劃（如果您在問題變得明顯之前有備份，您也許可以恢復到另一個數據庫/伺服器進行檢查那）。
狀態分佈是…
這比表中的 ~12.7Mrows 少得多。您是否從該列表中過濾了其他值，或者該列中的其餘行是否為 NULL？
您是否碰巧知道我們的 MySQL 版本的任何其他解決方法？
不特定於該版本（這也可以是真正的非 mysql 數據庫，甚至）：每個對像只能使用一個索引，並且在此查詢中，您將加入
company_id並過濾status& 其他。在這種情況下，複合索引（即在 上company_id, status，可能還有其他您正在過濾以避免額外查找的索引company_id, status, repurchases, is_test）可以提供很大幫助，但由於索引較大，會犧牲一點磁碟空間和記憶體。在其他數據庫（MS SQL Server，Prosgres，…）中，您可以通過刪除您擁有的任何索引來減少空間命中，
company_id並且將使用複合索引（假設 company_id 是複合鍵中的第一列），其中將是需要的，但這不是 mysql 的選項，因為這是由 FK 的存在隱式創建的。