錯誤的索引被偷看

我有一個索引可以過濾 99% 的表，即ix_magic_composite（對於那個查詢參數）。當我添加另一個or過濾器時，它選擇了錯誤的索引，即fTS即使我創建了一個以該欄位開頭的索引，它仍然選擇了錯誤的索引。執行時間是 20 秒對 3 秒到更好的索引。ix_magic_compositeindex 為這兩個 SQL 返回（初始過濾器）大約 10 行中的數百萬行，同時fTS返回數百萬行。

有點不知所措。在我看來，統計數據並沒有為引擎提供所有這些列組合的正確圖片。

我簡化了表格，它有更多的列和索引。

帶有良好計劃的 SQL：

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (magic is NULL or magic = '')
and (itemId is NULL or itemId = '')
and fTS > '2020-01-01'
and fTS > '2020-01-01'
order by fTS
limit 50

+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys                                    | key                 | key_len | ref   | rows    | filtered | Extra                                              |
+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tblExample | NULL       | ref_or_null | textCol,status,textCol_4,ix_magic_composite,fTS  | ix_magic_composite  | 53      | const | 5892974 |     0.24 | Using index condition; Using where; Using filesort |
+----+-------------+------------+------------+-------------+--------------------------------------------------+---------------------+---------+-------+---------+----------+----------------------------------------------------+

帶有錯誤計劃的 SQL：

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (magic is NULL or magic = '' or magic = 'retry')
and (itemId is NULL or itemId = '' or itemId = 'retry')
and fTS > '2020-01-01'
and fTS > '2020-01-01'
order by fTS
limit 50

+----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                                   | key     | key_len | ref  | rows    | filtered | Extra                              |
+----+-------------+------------+------------+-------+-------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+
|  1 | SIMPLE      | tblExample | NULL       | range | textCol,status,textCol_4,ix_magic_composite,fTS | fTS     | 5       | NULL | 6271587 |    0.18  | Using index condition; Using where |
+----+-------------+------------+------------+-------+-----------------------------------------    ----+---------+---------+------+---------+----------+------------------------------------+

桌子：

CREATE TABLE `tblExample` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `fTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `status` varchar(50) NOT NULL DEFAULT 'new',
 `textCol` varchar(50) DEFAULT NULL,
 `insrBLN` tinyint(4) NOT NULL DEFAULT '0',
 `itemId` varchar(50) DEFAULT NULL ,
 `magic` varchar(50) DEFAULT NULL ,
 PRIMARY KEY (`id`),
 KEY `ix_magic_composite` (`itemId`,`magic`,`fTS`,`insrBLN`),
 KEY `fTS` (`fTS`)
) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1

編輯

我們重構了程式碼，所以查詢看起來像：

select *
from tblExample
where 1=1
and status = 'okay'
and textCol > ''
and insrBLN = 1
and (retry = '' or (retry='retry' and retryDT < now() - interval 1 day))
and fTS > '2020-01-01'
order by fTS
limit 50

該問題未排序（還嘗試了索引中的不同列順序）​​。看起來只有當我刪除訂單時它才會選擇正確的索引。

重構不起作用。我決定移到which 給出與orUNION ALL相同的結果。我選擇這種方法是因為如果索引被刪除或重命名，它不需要任何程式碼更改。use index``force index

添加 OR 子句使估計索引的過濾效果變得更加困難。一種解決方案是添加一個生成的 always 列，該列計算是否滿足 magic 和 itemId 的謂詞，並索引：

CREATE TABLE tblExample (
 id int(11) unsigned NOT NULL AUTO_INCREMENT,
 fTS timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 status varchar(50) NOT NULL DEFAULT 'new',
 textCol varchar(50) DEFAULT NULL,
 insrBLN tinyint(4) NOT NULL DEFAULT '0',
 itemId varchar(50) DEFAULT NULL ,
 magic varchar(50) DEFAULT NULL ,
 retry tinyint GENERATED ALWAYS AS 
     ( case when  (magic is NULL or magic = '' or magic = 'retry') 
              AND (itemId is NULL or itemId = '' or itemId = 'retry')
            then 1 
            else 0
       end
     ) STORED,  
 PRIMARY KEY (`id`),
 KEY `ix_magic_composite` (retry,`fTS`,`insrBLN`),
 KEY `fTS` (`fTS`)
) ENGINE=InnoDB AUTO_INCREMENT=14391289 DEFAULT CHARSET=latin1

然後可以將查詢更改為：

SELECT t.*
FROM tblExample t
WHERE status = 'okay'
and textCol > ''
and insrBLN = 1
and retry
and fTS > '2020-01-01'
and fTS > '2020-01-01'  -- can be removed I assume
order by fTS
limit 50; 

正確的解決方案可能是修復數據模型，但這可能是不可能的。

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