mysql - 如何提高带有 NULL 的 MySQL 查询的性能？

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_id，但每条记录都有一个。
• email 设置为 null 的客户很少。

我在这里发现了一些奇怪的事情:

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

对这个看似基本的问题有什么想法吗？感觉就像我错过了一些简单的东西。我在 db 学校上了一节课吗？

Answer 1

有时 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;