多列索引和SQL查询的顺序

Question

关于MySQL中多列索引的顺序，我发现了两个相反的说法。 这篇文章这里有在注释中，(a, b) 索引也可用于 (b = value1 AND a = value2) 的查询。此常见问题解答条目 这里说（在底部）完全相反（不会使用索引）。什么是正确的？ PostgreSQL 怎么样？它的行为方式相同吗？

Answer 1

首先我要说的是，没有灵丹妙药的答案。

(a,b) 复合索引上的索引将用于满足查询（b=value1 和 a=value2）。请注意，WHERE 子句中“b”出现在“a”之前并不重要，因为查询引擎知道您正在处理 a 和 b。如果选择性不够高，它仍然可能无法使用。

话虽如此，

2. [SELECT * FROM buyers WHERE last_name=? AND first_name=? AND zip=?]
can't use the index.

这必须是我今天令人难以置信的常见问题解答条目中发现的最重要的。它部分正确，只是因为 SELECT * 需要查找回表，因此复合索引的任何好处都会减半（或进一步最小化）。比较此代码末尾的两个查询，而不是

CREATE TABLE buyers(
 buyer_id INT NOT NULL AUTO_INCREMENT,
 first_name CHAR(19) NOT NULL,
 last_name CHAR(19) NOT NULL,
 zip CHAR(5) NOT NULL,
 state_code CHAR(2) NOT NULL,
 PRIMARY KEY (buyer_id)
 );

insert buyers values
(991,'zeshan ','Nadeem ',92082,'CA'),
(992,'Ken ','Marcus ',92082,'CA'),
(993,'Tariq ','Iqbal ',92082,'CA'),
(994,'Tariq ','Iqbal ',92082,'CA'),
(995,'Hasnat ','Ahmad ',92083,'NY'),
(996,'Tariq ','Iqbal ',92082,'DC'),
(997,'Keith ','Worlf ',93083,'NG'),
(998,'Ashley ','Lewis ',92088,'NJ'),
(999,'Tariq ','Mehmood ',99088,'TX');

ALTER TABLE buyers ADD INDEX idx_firstname (first_name);
ALTER TABLE buyers ADD INDEX idx_last_name (last_name);
ALTER TABLE buyers ADD INDEX idx_zip (zip);
ALTER TABLE buyers ADD INDEX idx_flname_zip(first_name,last_name,zip);

在单独的查询中运行此查询

explain
SELECT first_name,last_name,zip FROM buyers WHERE first_name='Tariq' AND last_name='Iqbal' AND zip=92082;

，然后

explain
SELECT last_name,first_name,zip FROM buyers WHERE last_name='Iqbal' AND first_name='Tariq' AND zip=92082;

它们将显示相同的计划