MySQL: 联合索引在order by中的使用

对于联合索引，要注意索引字段在order by中出现的方式，否则可能会导致索引失效。下面举例说明。

下面这张表有个联合索引: <a1,a2,a3>，并且表里有100万行数据

create table a123(
        id bigint unsigned auto_increment not null,
        a1 varchar(50) not null,
        a2 varchar(50) not null,
        a3 varchar(50) not null,
        b1 varchar(50) not null,
        b2 varchar(50) not null,
        b3 varchar(50) not null,
        primary key(id),
        index idx_a_123(a1, a2, a3) 
)

explain select * from a123 order by a1 asc  limit 100;
explain select * from a123 order by a1 asc, a2 asc  limit 100; 
explain select * from a123 order by a1 asc, a2 asc, a3 asc limit 100
explain select * from a123 order by a1 desc, a2 desc, a3 desc  limit 100

都会用到索引idx_a_123, explain plain中显示rows=100

explain select * from a123 order by a1 asc, a2 asc, a3 desc limit 100;
explain select * from a123 order by a1 asc, a2 desc, a3 asc  limit 100;

索引失效, 数据库走filesort。rows为百万级。
Order By中三个字段的排序方向必须相同，否则会导致filesort. 联想一下B-树的结构，这个也不难理解：如果方向相同，MySQL只需顺着最底层索引树叶的横向链表捞一次就可以；如果方向不同还要用索引的话，MySQL就需要对底层子树各取一次，然后再拼装起来，MySQL觉得与其这样还不如不用索引。

explain select * from a123 order by a2 asc, a3 asc  limit 100;

索引失效, 数据库走filesort。rows为百万级。
Order By必须出现索引中的第1个字段. 这跟index-based lookup中的leftmost机制一样，不难理解。

explain select * from a123 order by a3 asc, a2 asc, a1 asc  limit 100;
explain select * from a123 order by a2 asc, a3 asc, a1 asc  limit 100;

索引失效, 数据库走filesort。rows为百万级。
Order By中三个字段的出现顺序必须与它们在索引中的顺序一致。. 这跟index-based lookup中的leftmost机制一样，不难理解。

explain select * from a123 order by a1 asc, a3 asc  limit 100

索引失效, 数据库走filesort。rows为百万级。
Order By中字段不可中断出现. 这跟index-based lookup中的不可中断机制一样(本质是也是leftmost原则)，但是情况更严重，因为lookup时还能用上a1字段上的索引，而排序时索引全部失效。

explain  select * from a123 where a1 = 'iUBHqrgGiv' order by a2 asc
explain  select * from a123 where a1 = 'iUBHqrgGiv' order by a2 asc, a3 asc

索引没有失效，数据库也没有走filesort. 因为
lookup中的where a1 = xxx 和order by中的order by a2, a3 恰好联成了一个leftmost。

explain  select * from a123 where a1 = 'iUBHqrgGiv' order by a3 asc

使用了索引做lookup, 但没有用索引排序，排序用的还是filesort. 因为
lookup中的where a1 = xxx 和order by中的order by a3 没有联成一个leftmost。

附：插入一百万条数据的代码

package my.tools.mysql.csvdata.t;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.RandomStringUtils;
import org.apache.commons.lang.StringUtils;

//生成数据文件
public class GenA123 {

	public static void main(String[] args) throws IOException {

		File dir = new File(System.getProperty("user.home") + "/kentbench");
		File file = new File(dir, "a123.txt");

		int numOfRows = 1000000;

		List<String> lines = new ArrayList<String>();

		for (int i = 1; i <= numOfRows; i++) {
			List<String> columns = new ArrayList<String>();
			for (int j = 0; j < 6; j++) {
				columns.add(RandomStringUtils.randomAlphanumeric(10));
			}
			lines.add(StringUtils.join(columns, ","));

		}

		FileUtils.writeLines(file, lines);

		System.out.println("Done. please check " + dir);

	}

}

load data local infile '/Users/kent/kentbench/a123.txt' into table a123 fields terminated by ',' lines terminated by '\n' (a1,a2,a3,b1,b2,b3);

Leave a Comment Cancel Reply