mysql - 优化我表上的索引

Question

我有一张 table friend :

CREATE TABLE IF NOT EXISTS `friends` (
    `fr_id` int(11) NOT NULL AUTO_INCREMENT,
    `fr_sender` int(11) NOT NULL,
    `fr_receiver` int(11) NOT NULL,
    `fr_validate` enum('0','1','2') NOT NULL DEFAULT '0',
    PRIMARY KEY (`fr_id`),
    KEY `fr_sender` (`fr_sender`),
    KEY `fr_receiver` (`fr_receiver`),
    KEY `fr_validate` (`fr_validate`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2397953 ;

fr_id       => index
fr_sender   => sender of the friend request
fr_receiver => the receiver
fr_falidate => 0 = no reply, 1 = request accepted, 2 = request refused.

我的 mysql-slow.log 对此查询有很多行:

SELECT fr_id FROM friends WHERE (fr_sender = '113405' OR fr_receiver = '113405') && fr_validate = "1";
# Query_time: 5.607869  Lock_time: 0.000052 Rows_sent: 106  Rows_examined: 833517

我如何为这个查询优化我的索引？

谢谢。

Answer 1

当您在两个不同 列的条件之间使用OR 时，很难进行优化。它通常会导致代价高昂的表扫描。

解决方法如下:

ALTER TABLE friends 
 ADD INDEX (fr_validate, fr_sender),
 ADD INDEX (fr_validate, fr_receiver);

SELECT fr_id FROM friends WHERE fr_validate = '1' AND fr_sender = '113405'
UNION 
SELECT fr_id FROM friends WHERE fr_validate = '1' AND fr_receiver = '113405'

创建两个索引的原因是为了让每个子查询都有一个各自的索引，以尽可能减少检查的行。然后将每个子查询的结果组合起来，得到与原始查询等效的结果集。

PS:请对字符串文字和日期文字使用单引号。 MySQL 默认允许双引号起同样的作用，但是如果你使用其他 RDBMS 品牌，或者如果你在 MySQL 中 SET SQL_MODE=ANSI_QUOTES，你会发现双引号的标准含义是用于分隔表名和列名，而不是字符串。