Mysql
MySQL查詢優化不使用索引
我的 MySQL 數據庫遇到了問題，我想知道你們中的任何人是否可以提供一些啟發。
背景：
我目前使用 InnoDB 配置了整個架構。該數據庫目前正被一個實時應用程序使用，該應用程序每天在其中一個表（即嘗試）中生成數千（接近一百萬）條記錄。我們整天都在大量查詢該表以解析數據並以 JSON 格式儲存在單獨的表中（使用 5.7 版引入的 JSON 類型）。
問題：
由於某種原因，MySQL 優化器似乎沒有使用正確的索引（在這種情況下，最重要的索引存在於“created_at”上）。請記住，我們使用這個完全相同的查詢將近 2 個月，但突然之間它開始表現得很糟糕。您可以在下面找到詳細資訊：
查詢：
SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att JOIN application app ON (app.id = att.application_id) JOIN loan_application type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr .id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' UNION SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att JOIN application app ON (app.id = att.application_id) JOIN loan_application_external_lookup type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr.id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' ORDER BY approved=1 DESC, created_at ASC索引：
MySQL [db]> show index from attempt; +---------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | attempt | 0 | PRIMARY | 1 | id | A | 12528955 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC02663E030ACD | 1 | application_id | A | 1426571 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC02669E82DDEA | 1 | integration_id | A | 8495 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC0266FA64550D | 1 | campaign_payout_rule_id | A | 10319 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC0266E4640ADF | 1 | campaign_credential_id | A | 11885 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC0266A354F9DC | 1 | tier_id | A | 14 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC02661500F99B | 1 | request_method | A | 1 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC02667C57D81D | 1 | approved | A | 1 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC02668B8E8428 | 1 | created_at | A | 3030286 | NULL | NULL | | BTREE | | | | attempt | 1 | IDX_18EC02665552D714 | 1 | parsed_at | A | 1284185 | NULL | NULL | YES | BTREE | | | | attempt | 1 | IDX_18EC0266720357E8 | 1 | parse_status | A | 1 | NULL | NULL | | BTREE | | | +---------+------------+----------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+解釋：
MySQL [db]> explain extended SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att JOIN application app ON (app.id = att.application_id) JOIN loan_application type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr .id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' UNION SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att JOIN application app ON (app.id = att.application_id) JOIN loan_application_external_lookup type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr.id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' ORDER BY approved=1 DESC, created_at ASC; +----+--------------+------------+------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------+---------------------------------+ | 1 | PRIMARY | ca | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 1 | PRIMARY | cpr | NULL | ref | PRIMARY,IDX_D5BDECA3F639F774 | IDX_D5BDECA3F639F774 | 4 | const | 1 | 100.00 | Using where; Using index | | 1 | PRIMARY | att | NULL | ref | IDX_18EC02663E030ACD,IDX_18EC0266FA64550D,IDX_18EC02668B8E8428,IDX_18EC02665552D714,IDX_18EC0266720357E8 | IDX_18EC0266FA64550D | 4 | dois.cpr.id | 1226 | 0.00 | Using where | | 1 | PRIMARY | type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.att.application_id | 1 | 100.00 | NULL | | 1 | PRIMARY | app | NULL | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C | PRIMARY | 4 | dois.att.application_id | 1 | 50.00 | Using where | | 1 | PRIMARY | aff | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.app.affiliate_id | 1 | 100.00 | NULL | | 2 | UNION | ca | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 2 | UNION | cpr | NULL | ref | PRIMARY,IDX_D5BDECA3F639F774 | IDX_D5BDECA3F639F774 | 4 | const | 1 | 100.00 | Using where; Using index | | 2 | UNION | att | NULL | ref | IDX_18EC02663E030ACD,IDX_18EC0266FA64550D,IDX_18EC02668B8E8428,IDX_18EC02665552D714,IDX_18EC0266720357E8 | IDX_18EC0266FA64550D | 4 | dois.cpr.id | 1226 | 0.00 | Using where | | 2 | UNION | type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.att.application_id | 1 | 100.00 | NULL | | 2 | UNION | app | NULL | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C | PRIMARY | 4 | dois.att.application_id | 1 | 50.00 | Using where | | 2 | UNION | aff | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.app.affiliate_id | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort | +----+--------------+------------+------------+--------+----------------------------------------------------------------------------------------------------------+----------------------+---------+-------------------------+------+----------+---------------------------------+一旦我使用索引提示稍微更改了查詢，您可以看到添加了“範圍”類型並且查詢按預期工作：
MySQL [db]> explain extended SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att USE INDEX (IDX_18EC02668B8E8428) JOIN application app ON (app.id = att.application_id) JOIN loan_application type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr .id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' UNION SELECT att.id AS attempt_id, app.id AS application_id, ca.id AS campaign_id, IFNULL(aff.private, 0) AS affiliate_id, app.ping_tree_id AS ping_tree_id, att.request_timeout AS request_timeout, att.response_time AS response_time, type.state AS state, att.approved AS approved, att.price AS price, att.created_at AS created_at FROM attempt att USE INDEX (IDX_18EC02668B8E8428) JOIN application app ON (app.id = att.application_id) JOIN loan_application_external_lookup type ON (type.id = app.id) JOIN campaign_payout_rule cpr ON (cpr.id = att.campaign_payout_rule_id) JOIN campaign ca ON (ca.id = cpr.campaign_id) LEFT JOIN affiliate aff ON (aff.id = app.affiliate_id) WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31' ORDER BY approved=1 DESC, created_at ASC; +----+--------------+------------+------------+--------+------------------------------+----------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+--------+------------------------------+----------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------+ | 1 | PRIMARY | ca | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 1 | PRIMARY | cpr | NULL | ref | PRIMARY,IDX_D5BDECA3F639F774 | IDX_D5BDECA3F639F774 | 4 | const | 1 | 100.00 | Using where; Using index | | 1 | PRIMARY | att | NULL | range | IDX_18EC02668B8E8428 | IDX_18EC02668B8E8428 | 4 | NULL | 78308 | 0.00 | Using index condition; Using where; Using join buffer (Block Nested Loop) | | 1 | PRIMARY | type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.att.application_id | 1 | 100.00 | NULL | | 1 | PRIMARY | app | NULL | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C | PRIMARY | 4 | dois.att.application_id | 1 | 50.00 | Using where | | 1 | PRIMARY | aff | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.app.affiliate_id | 1 | 100.00 | NULL | | 2 | UNION | ca | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 2 | UNION | cpr | NULL | ref | PRIMARY,IDX_D5BDECA3F639F774 | IDX_D5BDECA3F639F774 | 4 | const | 1 | 100.00 | Using where; Using index | | 2 | UNION | att | NULL | range | IDX_18EC02668B8E8428 | IDX_18EC02668B8E8428 | 4 | NULL | 78308 | 0.00 | Using index condition; Using where; Using join buffer (Block Nested Loop) | | 2 | UNION | type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.att.application_id | 1 | 100.00 | NULL | | 2 | UNION | app | NULL | eq_ref | PRIMARY,IDX_A45BDDC1D87F7E0C | PRIMARY | 4 | dois.att.application_id | 1 | 50.00 | Using where | | 2 | UNION | aff | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dois.app.affiliate_id | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort | +----+--------------+------------+------------+--------+------------------------------+----------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------+幾點注意事項：
- 我們目前正在使用 AWS RDS
- 截至目前，嘗試表有 15097025 條記錄
- 截至目前，嘗試表正在使用 50.8GB 的儲存空間
- 伺服器目前有 550GB 的可用儲存空間。
- 我昨晚根據這篇文章對所有表進行了碎片整理：https ://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html （我認為與 OPTIMIZE 是一樣的嗎？）
問題：
- 使用索引提示是一種不好的做法嗎？我們目前正在使用 ORM 來建構其中一些查詢，如果您決定使用他們的查詢建構器，那麼添加它會變得更加困難。儘管我們可以將查詢擴展或更改為原始格式。
- 你會建議我以任何其他方式進行此查詢嗎？您認為連接數會影響性能嗎？
有什麼想法嗎？先感謝您。
雷納托。
如果 MySQL 選擇了錯誤的索引，則使用索引提示是正確的（並且通常是唯一的）解決方案。雖然這可能由於過時的統計數據或過小的採樣率而發生，但當相等性可用時，MySQL 很難找到合適的範圍索引。
您可以嘗試增加innodb_stats_persistent_sample_pages（例如 200），然後執行
analyze table attempt並檢查它是否改進了執行計劃。它使用表的較大部分來創建統計資訊，因此對於較大的表，估計值可能會更精確。這可以解釋為什麼這種影響最近才發生，儘管它可能不會改變計劃。由於計算統計數據需要更多時間，如果它不起作用，您應該將其重置為原始值。根據
null您的列的常見程度parsed_at，我會推薦複合索引attempt(parsed_at, created_at)。它可能比您目前的單列索引執行得更好，並且即使不強制它也可能會被使用。因此，這也將間接解決您的問題，因此即使它實際上並沒有加快您的查詢速度，它也很有用 - 如果（幾乎）created_at範圍內的每一行也都有parsed_at = null.另一個優化：
看起來您使用的是
union唯一的，因為有join2 個不同的loan_application_*-tables。如果不這樣做，您最多可以節省 50% 的執行時間。更好的方法是規範化該表，例如將兩者的內容放在一個表中，也許id, subtype, other columns，並subtype=1為來自的行和來自loan_application的subtype=2行設置loan_application_external_lookup。或者保留兩個表，但另外有一個父表id，subtype並且這兩個表的所有列都有共同點。另外兩個表將僅包含id特定於子類型的 和 列。這將使join“一般”loan_application實體更加可靠和容易。如果您需要特定於子類型的列，您可以join使用它。這被稱為is-a關係（“貸款申請”是子類型 1 或 2）。它可能需要對您的 orm/data 模型進行一些返工，因此可以為此嘗試一個視圖（或將行插入臨時表中），並
join以此查看它可能產生的效果。（這是根據您在查詢中使用這些表的方式及其名稱得出的結論。如果它們實際上描述了完全不同且沒有共同點的事物，那當然不是一個好主意，儘管它們的名稱會非常誤導。）
根據您的數據的具體情況（例如，如果一切都是 1:1），可能還有其他一些優化可能，但最大的問題似乎是
attempt-table，無論如何您只提供了詳細資訊。
**
WHERE att.created_at BETWEEN '2017-08-24 07:31:31' AND '2017-08-24 11:31:31' AND att.parsed_at IS NULL AND ( att.parse_status = '1' OR att.parse_status = '11' ) AND app.test = 0 AND ca.id = '31'改寫為
WHERE att.created_at >= '2017-08-24 07:31:31' AND att.created_at < '2017-08-24 07:31:31' INTERVAL + 4 HOUR AND att.parsed_at IS NULL AND att.parse_status IN (1, 11) AND app.test = 0 AND ca.id = '31'（這簡化了
OR, 並避免在間隔中增加額外的秒數。）** 並添加
INDEX(parse_at, parse_status, created_at)這與3 個單列索引不同。
** 添加括號：
( SELECT ... ) UNION ( SELECT ... ) ORDER BY ...（這會將 the 與 2nd 聯繫
ORDER BY起來UNION——SELECT我假設你想要那個？？）** 如果有效，請使用
UNION ALL代替UNION DISTINCT. （這將避免重複數據傳遞。）**
OPTIMIZE TABLE幾乎總是浪費時間。** 讓我們看看
SHOW CREATE TABLE；我們也許可以稍微削減 50GB。這會有所幫助。** 索引提示是最後的手段。今天有幫助的東西明天可能會受到傷害（在數據分佈發生變化之後）。
** 除了表格之外，
SELECTs其他有什麼區別嗎？loan_application*** 你可能可以擺脫一個
JOIN——即 tocampaign ca。所有對 的引用ca.id都可以替換為cpr.campaign_id。