MYSQL 可以创建 联合索引(即有多个列的索引)。一个索引最多可以包含 16 列。
当你创建了一个联合索引,该索引的任何最左前缀都可以用于查询。比如当你有一个联合索引 (col1, col2, col3)
,该索引的所有前缀为 (col1)
、(col1, col2)
、(col1, col2, col3)
,包含这些列的所有查询都会使用该索引进行查询。
例子
首先我们有如下一个表:
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';
如果表拥有一个联合索引,任何一个索引的最左前缀都会被优化器用于查找列。比如,如果你创建了一个包含三列的联合索引 (col1, col2, col3)
,你的索引生效于 (col1), (col1, col2), (col1, col2, col3)
。
为什么要使用联合索引?
-
减少开销。建一个联合索引
(col1,col2,col3)
,实际相当于建了(col1)
,(col1,col2)
,(col1,col2,col3)
三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销! -
覆盖索引。对联合索引
(col1,col2,col3)
,如果有如下的 SQL:select col1,col2,col3 from test where col1=1 and col2=2;
。那么 MySQL 可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机 IO 操作。减少 IO 操作,特别的随机 IO 其实是 DBA 主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。 -
效率高。索引列越多,通过索引筛选出的数据越少。有 1000W 条数据的表,有如下 SQL:
select from table where col1=1 and col2=2 and col3=3
,假设假设每个条件可以筛选出 10% 的数据,如果只有单值索引,那么通过该索引能筛选出 1000W*10%=100w 条数据,然后再回表从 100w 条数据中找到符合 col2=2 and col3=3 的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出 1000w * 10% * 10% * 10% =1w,效率提升可想而知!