如何通过简单的AND+OR查询使mysql使用索引

Question

这是使用MySQL 5.5。我似乎无法说服MySQL为这些查询使用索引，而且它们在一个有110万行的表上运行需要2-10秒。

表：

CREATE TABLE `notifiable_events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`key` varchar(255) NOT NULL,
`trigger_profile_id` int(10) unsigned DEFAULT NULL,
`model1` varchar(25) NOT NULL,
`model1_id` varchar(36) NOT NULL,
`model2` varchar(25) NOT NULL DEFAULT '',
`model2_id` varchar(36) NOT NULL DEFAULT '',
`event_data` text,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `key` (`key`),
KEY `notifiable_events__trigger_profile` (`trigger_profile_id`),
KEY `deleted` (`deleted`),
KEY `noti_evnts__m2` (`model2`),
KEY `noti_evnts__m1` (`model1`),
CONSTRAINT `notifiable_events__trigger_profile` FOREIGN KEY (`trigger_profile_id`) REFERENCES `profiles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1177918 DEFAULT CHARSET=utf8

查询：

  SELECT * 
    FROM notifiable_events 
    WHERE (`model1` = 'page' AND `model1_id` = '54321') 
       OR (`model2` = 'page' AND `model2_id` = '12345');

解释：

mysql>EXPLAIN EXTENDED SELECT*FROM notification_events WHERE（`model1'='page'AND `model1_id`='922645'）OR（`model2'='page'AND`model2_id`='922645'）\G***************************1。行***************************id:1select_type:SIMPLE表：可通知事件类型：ALL可能的密钥：noti_evnts_m2、noti_evnts __m1、noti_vnts__m1_m2密钥：NULLkey_len:空ref:空行数：1033088过滤：100.00附加：使用where一组中有1行，1个警告（0.00秒）mysql>EXPLAIN EXTENDED SELECT*FROM notification_events WHERE（`model1`='page'AND `model1_id`='922645'）OR（`model1'='page'AND`model1_id='922645'***************************1。行***************************id:1select_type:SIMPLE表：可通知事件类型：ref可能的密钥：noti_evnts_m1，noti_evnts __m1_m2密钥：noti_evnts_m1key_len:77ref：常量行：1过滤：100.00附加：使用where一组中有1行，1个警告（0.00秒）mysql>EXPLAIN EXTENDED SELECT*FROM notification_events WHERE（`model2`='page'AND `model2_id`='922645'）OR（`model2'='page'AND`model2_id='922645'***************************1。行***************************id:1select_type:SIMPLE表：可通知事件类型：ref可能的密钥：noti_evnts_m2密钥：noti_evnts_m2key_len:77ref：常量行数：428920过滤：100.00附加：使用where一组中有1行，1个警告（0.00秒）

你可以看到，如果我只使用model1或model2，那么它就会使用索引，然而，一旦我尝试同时使用它们，它就会完全放弃索引，并进行完整的表扫描。我已经尝试了FORCE INDEX，并尝试了多关键字索引的组合（作为示例，我为该表保留了一个）。我还尝试过重新排列查询中元素的顺序，但这似乎也没有任何效果。

更新：我忘了提到我已经尝试过ANALYZE和OPTIMIZE（每次多次，没有变化）。我已经在*_id上尝试了一个索引（基数非常糟糕，这些列大多是唯一的条目）和一个多索引，其中所有4列都在查询中使用。在这两种情况下都没有改进或使用该指数。

似乎使用索引来限制此处检查的行应该非常容易，所以我希望我只是缺少了一些东西。

Answer 1

or子句有时会混淆查询优化。

你可以试试工会之类的。它可能会重新激活索引。

SELECT * 
  FROM notifiable_events 
 WHERE  `model1` = 'page' AND `model1_id` = '54321'
 UNION
SELECT * 
  FROM notifiable_events 
 WHERE `model2` = 'page' AND `model2_id` = '12345'

编辑，回答评论中的问题

如果你试图使用这种选择方案更新记录，你可以尝试一下：

UPDATE notifiable_events
   SET what=ever,
       what=ever,
       what=else 
 WHERE id IN (
   SELECT id 
     FROM notifiable_events 
    WHERE  `model1` = 'page' AND `model1_id` = '54321'
    UNION
   SELECT id 
     FROM notifiable_events 
    WHERE `model2` = 'page' AND `model2_id` = '12345'
 )

（请注意，当您使用Stackoverflow来解释您实际要做的事情时，这很有帮助。如果可以的话，将复杂的问题简化为更简单的问题当然是可以的，但说您在实际执行UPDATE时执行SELECT是过于简单化了。）