MySQL 可以创建复合索引(即多列索引)。一个索引最多可包含 16 列。对于某些数据类型,您可以索引列的前缀(请参阅 第 8.3.5 节,“列索引”)。
MySQL 可以将多列索引用于测试索引中所有列的查询,或仅测试第一列、前两列、前三列等的查询。如果在索引定义中以正确的顺序指定列,单个复合索引可以加速对同一个表的多种查询。
多列索引可以被认为是一个排序数组,其中的行包含通过连接索引列的值创建的值。
作为复合索引的替代方案,您可以引入一个基于其他列的信息“散列”的列。如果此列很短、相当独特且已建立索引,则它可能比许多列上的“宽”索引更快。在 MySQL 中,很容易使用这个额外的列:
SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(val1,val2))
AND col1=val1 AND col2=val2;
假设一个表具有以下规范:
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
该name
索引是对
last_name
和first_name
列的索引。该索引可用于查询中的查找,这些查询在已知范围内为
last_name
和值的组合指定first_name
值。它也可以用于只指定一个
last_name
值的查询,因为该列是索引的最左边的前缀(如本节后面所述)。因此,该name
索引用于以下查询中的查找:
SELECT * FROM test WHERE last_name='Jones';
SELECT * FROM test
WHERE last_name='Jones' AND first_name='John';
SELECT * FROM test
WHERE last_name='Jones'
AND (first_name='John' OR first_name='Jon');
SELECT * FROM test
WHERE last_name='Jones'
AND first_name >='M' AND first_name < 'N';
但是,该name
索引
不用于以下查询中的查找:
SELECT * FROM test WHERE first_name='John';
SELECT * FROM test
WHERE last_name='Jones' OR first_name='John';
假设您发出以下
SELECT
语句:
SELECT * FROM tbl_name
WHERE col1=val1 AND col2=val2;
col1
如果和
上存在多列索引col2
,则可以直接获取适当的行。col1
如果和上存在单独的单列索引
col2
,优化器会尝试使用索引合并优化(请参阅
第 8.2.1.3 节,“索引合并优化”),或尝试通过确定哪个索引排除更多行并使用来找到限制性最强的索引该索引以获取行。
如果表有一个多列索引,那么优化器可以使用索引的任何最左边的前缀来查找行。例如,如果您在 上有一个三列索引,则您在、和
(col1,
col2, col3)
上具有索引搜索功能
。
(col1)
(col1, col2)
(col1, col2, col3)
如果列不构成索引的最左前缀,则 MySQL 无法使用索引执行查找。假设您有SELECT
此处显示的语句:
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果 上存在索引(col1, col2, col3)
,则只有前两个查询使用该索引。第三个和第四个查询确实涉及索引列,但不使用索引来执行查找,因为(col2)
和
(col2, col3)
不是 的最左边前缀
(col1, col2, col3)
。