对于任何DBMS,索引都是进行优化的最主要的因素。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。
如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。例如:
假设存在组合索引it1c1c2(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值。
索引对于InnoDB非常重要,因为它可以让查询锁更少的元组。这点十分重要,因为MySQL 5.0中,InnoDB直到事务提交时才会解锁。有两个方面的原因:首先,即使InnoDB行级锁的开销非常高效,内存开销也较小,但不管怎么样,还是存在开销。其次,对不需要的元组的加锁,会增加锁的开销,降低并发性。
InnoDB仅对需要访问的元组加锁,而索引能够减少InnoDB访问的元组数。但是,只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做(即达不到过滤的目的),MySQL服务器只能对InnoDB返回的数据进行WHERE操作,此时,已经无法避免对那些元组加锁了:InnoDB已经锁住那些元组,服务器无法解锁了。
来看个例子:
create table actor( actor_id int unsigned NOT NULL AUTO_INCREMENT, name varchar(16) NOT NULL DEFAULT '', password varchar(16) NOT NULL DEFAULT '', PRIMARY KEY(actor_id), KEY (name) ) ENGINE=InnoDB insert into actor(name,password) values('cat01','1234567'); insert into actor(name,password) values('cat02','1234567'); insert into actor(name,password) values('ddddd','1234567'); insert into actor(name,password) values('aaaaa','1234567'); |
SET AUTOCOMMIT=0; BEGIN; SELECT actor_id FROM actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE; |
该查询仅仅返回2---3的数据,实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询(而没有进行过滤操作,WHERE中第二个条件已经无法使用索引了):
mysql> EXPLAIN SELECT actor_id FROM test.actor -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec)
mysql> |
表明存储引擎从索引的起始处开始,获取所有的行,直到actor_id<4为假,服务器无法告诉InnoDB去掉元组1。
为了证明row 1已经被锁住,我们另外建一个连接,执行如下操作:
SET AUTOCOMMIT=0; BEGIN; SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE; |
该查询会被挂起,直到第一个连接的事务提交释放锁时,才会执行(这种行为对于基于语句的复制(statement-based replication)是必要的)。
如上所示,当使用索引时,InnoDB会锁住它不需要的元组。更糟糕的是,如果查询不能使用索引,MySQL会进行全表扫描,并锁住每一个元组,不管是否真正需要。