如何在MySQL查询中处理NULL以提高性能？

Question

我有一个包含数百万条记录的表：

CREATE TABLE `customers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `store_id` int(10) unsigned DEFAULT NULL,
  `first_name` varchar(64) DEFAULT NULL,
  `middle_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_store_email` (`store_id`,`email`),
  KEY `index_store_phone` (`store_id`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查询1大约需要800毫秒:
SELECT COUNT(*) FROM `customers` WHERE `store_id` = 1;

查询2大约需要1.5毫秒:
SELECT COUNT(*) FROM `customers` WHERE `store_id` = 1 AND `email` IS NULL;

查询3竟然需要5秒钟:
SELECT COUNT(*) FROM `customers` WHERE `store_id` = 1 AND `email` IS NOT NULL;

注:

• 我简化了表格以提出问题，但查询是相同的。
• 是的，我的表已经优化过了。
• 是的，这两个字段都有索引，请参考上面的创建语法。
• 只有几个store_ids，但每个记录都有一个。
• 有很少客户的email为null。

我发现这里有几件奇怪的事情：

1. 查询1是最简单的！只有几个可能的INT值。难道它不应该是最快的吗？
2. 为什么查询3这么慢？通过执行其他两个查询并将#1从#2中减去，我可以将时间减半，但我不应该这样做。

对于这个看似基本的问题，你有什么想法？我感觉自己错过了一些简单的东西。我在数据库学校里睡着了吗？

- designosis

---

你试过查看EXPLAIN吗？我猜测由于表中的数据而变得更慢了。如果只有几行具有email = NULL，那么它可以轻松使用索引并返回相当快的结果。第一个查询较慢，因为必须遍历许多更多的行，如果只有少量不同的store_id结果，MySQL可能会决定遍历整个表比遍历索引更快。这可能是错误的选择。 - Wolph

@neokio：看一下rows。它应该告诉你有多少行被检查以返回结果。 - Wolph

此外，possible_keys 显示出当前查询可能使用的索引，而 key 则表示实际使用了哪个索引（如果有的话）。 - Wolph

在某些情况下，MySQL 可能会猜错...您可以强制 MySQL 使用索引：http://dev.mysql.com/doc/refman/5.7/en/index-hints.html - Wolph

摆脱“store_id”索引。 - Strawberry

Answer 1

只需删除仅包含(store_id)的索引；这个索引与其他两个索引是冗余的。

这可能还可以避免使用FORCE INDEX等操作。

INDEX(store_id, email)对于所有三个查询都有效。

- Rick James

Answer 2

有时候MySQL查询解析器在决定使用哪个索引时会猜错。对于这种情况，索引提示可能会有用（http://dev.mysql.com/doc/refman/5.7/en/index-hints.html）。

要强制使用一个索引：

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

强制使用索引，包括替换表扫描：

SELECT * FROM table1 FORCE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

忽略特定索引：

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

为了调试使用哪个索引，可以使用EXPLAIN语句: (https://dev.mysql.com/doc/refman/5.7/en/explain-output.html)

EXPLAIN SELECT * FROM table1
  WHERE col1=1 AND col2=2 AND col3=3;

- Wolph

---

感谢@Wolph。还值得一提的是，使用EXPLAIN可以揭示查询解析器中的问题。 - designosis