赵玉伟的博客

索引的使用(2)

联合索引

概念说明:

以innodb为例, 一张表的secondary索引包含多个列, 称之为联合索引。

建几个比较合适:

根据经验值, 一般情况一到三个比较合适; 四到五个有点多; 大于等于六个比较多, 要慎重。
建索引的顺序有要求, 越靠左侧的列查询的概率越高, 并且需要考虑识别度, 依次向右次之。

创建方式:

1
2
3
4
5
6
7
8
9
10
create table student(
id int(11) NOT NULL AUTO_INCREMENT,
id_card varchar(10) NOT NULL,
name varchar(10) NOT NULL,
grade varchar(10) NOT NULL,
telephone varchar(10) NOT NULL,
sex int(11) NOT NULL,
PRIMARY KEY (id),
key(id_card, telephone, grade)
);

以上语句创建了一张包含一个主键索引, 包含三个列的联合索引。

数据存储:

安装以上方式创建索引, 那么每个节点的上的值包含以上三个字段, 我们用数字模拟,假如索引的顺序为(a, b, c), 那么索引树的结构如下:

根据上图得知,索引树的排序方式按照从左到右的顺序依次排序, 这一点,决定了在使用联合索引时的方式以及诸多限制。

  • 查找方式从最左侧依次开始查找
  • 可以精确匹配 where a=’x’
  • 可以匹配最左侧的前缀, 比如: where a like ‘x%’
  • 可以按照从左到右的顺序依次匹配, 比如: where a like ‘x%’ and b=’x’ and c=’x’
  • 左侧的索引一旦失效,则后续的索引不可再用,比如:where a like ‘%x’ and b=’xx’ and c=’d’,这种写法索引会失效,导致全表扫描
  • 索引一旦跳过某列,则某列后面的索引会失效,比如: where a=’x’ and c=’xx’, c列上的索引不再有效

所以, 索引的列顺序非常重要, 一定要从左到右依次查找, 可以参考《高性能mysql》第五章。


多列索引

还有一种在多个列上创建索引的方式如下:

1
2
3
4
5
6
7
8
9
10
11
12
create table student(
id int(11) NOT NULL AUTO_INCREMENT,
id_card varchar(10) NOT NULL,
name varchar(10) NOT NULL,
grade varchar(10) NOT NULL,
telephone varchar(10) NOT NULL,
sex int(11) NOT NULL,
PRIMARY KEY (id),
key(id_card),
key(telephone),
key(grade)
);

也就是在多个列上单独创建索引,这种方式很多人不推荐, 因为在大部分情况下,并不能提高查询性能, 最好将其转化成多级索引。我的理解是这样的:
1、一个索引就是一颗树,如果按照这种方式创建三个索引, 那么就是三棵树, 导致在进行多列查询的时候,只能使用最左侧的一个索引, 其他索引会失效;
2、但是,如果查询里面主要是这种查询的话, 这种索引结构也是没问题的:

where id_card=’xxxx’
where telephone=’xxxx’
where grade=’xxxx’

3、每次插入、更新数据的时候, 会更新三棵树, 效率会依次降低
4、三棵树必然会有三棵树的叶子节点数据,数据量会增加

另外, 在where子句中查询条件的顺序会引起性能不一样吗?
比如:
A: where id_card=’xx’ and telephone=’aaa’
B: where telephone=’aaa’ and id_card=’xx’
我没做过具体实验,但是我认为mysql不会这么不智能, mysql会进行查询优化, 所以对于两者的性能我理解是没有差别的。