複雜的關鍵性能問題（MySQL、InnoDB）

在生產環境和（幸運的是可重現）我的本地開發盒上，查詢都非常緩慢。生產環境是 Linux，我的開發人員目前是帶有 MySQL 5.7.11 的 Windows 10：

SHOW VARIABLES LIKE "%version%";
'innodb_version', '5.7.11'
'protocol_version', '10'
'slave_type_conversions', ''
'tls_version', 'TLSv1,TLSv1.1'
'version', '5.7.11-log'
'version_comment', 'MySQL Community Server (GPL)'
'version_compile_machine', 'x86_64'
'version_compile_os', 'Win64'

這些表是 InnoDB。查詢只涉及一張表，由DjangoORM 生成。這是表格：

select  column_name, column_type, is_nullable
   from  information_schema.columns
   where  table_name='main_contactinfo'
     and  table_schema='live040616'

id  int(11) NO
first_name  varchar(100)    YES
last_name   varchar(100)    YES
club_name   varchar(100)    YES
email   varchar(254)    YES
team_name_id    int(11) YES
address varchar(100)    YES
city    varchar(100)    YES
state   varchar(100)    YES
zip varchar(20) YES
contact_type    varchar(100)    YES
player_id   int(11) YES
last_modified   datetime    NO
object_id   varchar(300)    YES
sync_inactive   tinyint(1)  NO
customer_account_id int(11) YES
is_asgr tinyint(1)  NO
relation    varchar(150)    YES
note    longtext    YES
twitter varchar(100)    YES
facebook    varchar(100)    YES
other   longtext    YES
aau longtext    YES
aau_coachnum    varchar(300)    YES
hs_coachnum varchar(300)    YES
parent_status   varchar(300)    YES
instagram   varchar(100)    YES

該表中有大約 21702503 條記錄。查詢：

SELECT  main_contactinfo.id, main_contactinfo.first_name, main_contactinfo.last_name,
       main_contactinfo.club_name, main_contactinfo.email, main_contactinfo.team_name_id,
       main_contactinfo.address, main_contactinfo.city, main_contactinfo.state,
       main_contactinfo.zip, main_contactinfo.contact_type, main_contactinfo.relation,
       main_contactinfo.player_id, main_contactinfo.last_modified,
       main_contactinfo.object_id, main_contactinfo.sync_inactive,
       main_contactinfo.customer_account_id, main_contactinfo.is_asgr,
       main_contactinfo.note, main_contactinfo.twitter, main_contactinfo.facebook,
       main_contactinfo.instagram, main_contactinfo.other, main_contactinfo.aau,
       main_contactinfo.aau_coachnum, main_contactinfo.hs_coachnum,
       main_contactinfo.parent_status
FROM main_contactinfo 
WHERE ( main_contactinfo.customer_account_id = 12345 
   AND ( (main_contactinfo.sync_inactive = 1 
          AND main_contactinfo.last_modified >= '2016-04-17 00:16:55') 
      OR (main_contactinfo.last_modified > '2015-09-28 17:40:21' 
          AND main_contactinfo.sync_inactive = 0) 
      OR (main_contactinfo.sync_inactive = 0
          AND main_contactinfo.last_modified = '2015-09-28 17:40:21'
          AND main_contactinfo.id > 19968645)
       )
     ) 
ORDER BY main_contactinfo.last_modified ASC, 
        main_contactinfo.id ASC 
LIMIT 83;

WHERE 子句在 上執行last_modified，因為它是系統的一部分，自某個時間戳（日期時間）以來，只應返回更改。我機器上的查詢大約需要 190 秒，返回 80 條記錄。我看到在那段時間 HDD I/O 燈亮了，我在 RAID0 中執行了兩個 Crucial M550。這與今天的 nVME SSD 相去甚遠，但它仍然是 ~1GBps 吞吐量。

1. 我懷疑sync_inactive（這是 ORM 另一端的布爾欄位），它沒有被索引覆蓋，創建索引sync_inactive沒有幫助。
2. 我逐漸創建了覆蓋越來越多列的複合索引，但即使 aCREATE UNIQUE INDEX composite_index ON main_contactinfo(id, customer_account_id, last_modified, sync_inactive);也無濟於事。
3. 我使用日期時間範圍會是一個問題嗎？
4. 另請注意，我longtext在查詢中涉及 3 列（我不知道為什麼ORM包含這些，我希望它會做一些延遲載入。但我從查詢中刪除了這 3 個欄位，這些也沒有幫助
5. 我想知道是否會有一些不需要的轉換字元集或排序規則轉換？我看到的是列的字元集是latin1，排序規則是latin1_swwedish_ci，而ORM端強制執行的排序規則和字元集是utf8。

然而，主要問題似乎OPTIMIZER_TRACE在於，即使有一個覆蓋很多列的巨大索引，引擎也會選擇另一個索引，它只覆蓋customer_account_id. 我的覆蓋綜合指數沒有被選中的原因是“未知”。如何改進該查詢？

             "analyzing_range_alternatives": {
               "range_scan_alternatives": [
                 {
                   "index": "composite_index",
                   "chosen": false,
                   "cause": "unknown"
                 },
                 {
                   "index": "main_contactinfo_f4b91458",
                   "ranges": [
                     "12345 <= customer_account_id <= 12345"
                   ],
                   "index_dives_for_eq_ranges": true,
                   "rowid_ordered": true,
                   "using_mrr": false,
                   "index_only": false,
                   "rows": 42294,
                   "cost": 50754,
                   "chosen": true
                 },
                 {
                   "index": "main_contactinfo_last_modified_4769d8a97da686d4_uniq",
                   "ranges": [
                     "0x9997391a15 <= last_modified <= 0x9997391a15 AND 19968645 < id",
                     "0x9997391a15 < last_modified"
                   ],
                   "index_dives_for_eq_ranges": true,
                   "rowid_ordered": false,
                   "using_mrr": false,
                   "index_only": false,
                   "rows": 10272032,
                   "cost": 1.23e7,
                   "chosen": false,
                   "cause": "cost"
                 }
               ],
               "analyzing_roworder_intersect": {
                 "usable": false,
                 "cause": "too_few_roworder_scans"
               }
             },

更完整的優化跟踪（不幸的是被截斷了）： https ://gist.github.com/MrCsabaToth/62ed290b6c8ddd4f40a172a87a8c0f84

對於這個WHERE子句，我會嘗試一個索引(customer_account_id, sync_inactive, last_modified, id)：

ALTER TABLE main_contactinfo
 ADD INDEX customer_active_last_modified
 ( customer_account_id, 
   sync_inactive, 
   last_modified, 
   id
 ) ;

雖然ORDER BYwith e smallLIMIT使事情複雜化，所以索引中列的不同順序可能會更好：(customer_account_id, last_modified, id, sync_inactive).

引用自：https://dba.stackexchange.com/questions/135698