多个和单个索引 [英] Multiple and single indexes
问题描述
自从我使用MySQL多年以来，我有点羞于问这个问题，但是很好。
I'm kinda ashamed of asking this since I've been working with MySQL for years, but oh well.
我有一个包含两个字段的表， a 和 b 。我将对其运行以下查询：
I have a table with two fields, a and b. I will be running the following queries on it:
-
SELECT * FROM ... WHERE A = 1; -
SELECT * FROM ... WHERE B = 1; -
SELECT * FROM ... WHERE A = 1 AND B = 1;
SELECT * FROM ... WHERE A = 1;SELECT * FROM ... WHERE B = 1;SELECT * FROM ... WHERE A = 1 AND B = 1;
从性能的角度来看，对于至少一个查询，至少是以下一种索引配置？如果是，请详细说明。
From the performance point of view, is at least one of the following configurations of indexes slower for at least one query? If yes, please elaborate.
-
ALTER TABLE ... ADD INDEX（a）; ALTER TABLE ... ADD INDEX（b）; -
ALTER TABLE ... ADD INDEX（a，b）; -
ALTER TABLE ... ADD INDEX（a）; ALTER TABLE ... ADD INDEX（b）; ALTER TABLE ... ADD INDEX（a，b）;
ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);ALTER TABLE ... ADD INDEX (a, b);ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);
谢谢（注意我们正在谈论关于非唯一索引）
Thanks (note that we are talking about non unique indexes)
推荐答案
是的，至少有一个案例相当慢。如果您只定义以下索引：
Yes, at least one case is considerably slower. If you only define the following index:
ALTER TABLE ... ADD INDEX (a, b);
...然后查询 SELECT * FROM ... WHERE B = 1; 不会使用该索引。
... then the query SELECT * FROM ... WHERE B = 1; will not use that index.
使用复合键创建索引时，键的列顺序很重要。建议尝试对密钥中的列进行排序以增强选择性，最具选择性的列位于密钥的最左侧。如果不这样做，并将非选择性列作为密钥的第一部分，则可能根本不使用索引。 （来源：优化SQL Server综合索引的提示）
When you create an index with a composite key, the order of the columns of the key is important. It is recommended to try to order the columns in the key to enhance selectivity, with the most selective columns to the left-most of the key. If you don't do this, and put a non-selective column as the first part of the key, you risk not using the index at all. (Source: Tips on Optimizing SQL Server Composite Index)
这篇关于多个和单个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
