主页 / dba / 问题 / 270276
Accepted
Nir
Asked: 2020-07-03 07:19:30 +0800 CST

错误的索引被偷看

  • 772

我有一个索引可以过滤 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

该问题未排序（还尝试了索引中的不同列顺序）。看起来只有当我删除订单时它才会选择正确的索引。

mysql performance

3 个回答

  1. 添加 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;

    正确的解决方案可能是修复数据模型，但这可能是不可能的。

    • 1
  2. Best Answer

    重构不起作用。我决定移到which 给出与orUNION ALL相同的结果。我选择这种方法是因为如果索引被删除或重命名，它不需要任何代码更改。use indexforce index

    • 1

  3. 影响效率的因素有很多：

    • OR. 甚至“ref_or_null”也不是最优的。首先，您可以避免同时使用''和NULL列吗？也就是说，清理数据和处理以使用或 。这样，您就不需要同时测试两者。'' NULL
    • 因为(itemId is NULL or itemId = '' or itemId = 'retry')，我建议选择NULL, not ''，以便可以使用“ref_or_null_”。
    • 正如您已经发现的那样，UNION（最好ALL）是OR. 唉，这变得一团糟OR。
    • 有多个“范围”（textcol、fTS、retryDT）。只有一个可以有效使用。而且，优化器经常无法正确选择要关注的问题。
    • 优化器更喜欢“过滤”数据（通过WHERE）而不用担心排序（ORDER BY）。但是当WHERE子句变得过于复杂时，优化器可能会放弃WHERE并简单地关注ORDER BY. 您的第二个查询就是一个例子。
    • 实际上，可以通过更改为来改进第二个INDEX(fTS)查询INDEX(status, insrBLN, fTS)。这样，它可以在排序之前进行一些过滤；然后在遍历行时完成过滤。
    • 然后，在选择''或之后NULL，该索引可以进一步更改为 INDEX(status, insrBLN, magic, itemId, fTS)。
    • 请注意，我=首先在 中进行测试INDEX，最后是“范围”和/或ORDER BY列 ( FTS)。（=列的顺序无关紧要。）
    • 第二个“范围”( textcol > '') 测试的存在阻止了使用单个索引来处理过滤和排序。（我认为没有任何解决方法。）
    • 对于您提供的所有查询，列的顺序ix_magic_composite都不是最佳的。重要的是insrBLN（用 测试=）需要在FTS（范围）之前，而不是之后。
    • 使用时，UNION您需要为.UNION
    • 1

相关问题