本帖最后由 clevernby 于 2013-2-6 14:25 编辑
MySQL 5.5 Reference Manual中有这样的描述
When a long-running transaction modifies a table, queries against that table from other transactions do not make use of the covering index technique
复制代码
真的是这样的吗?我无法实验出这个效果
环境
MySQL 5.5.27 Linux x86-64
create table t_coverindex(id1 int not null,id2 int not null,name char(30)) engine=InnoDB;
alter table t_coverindex add constraint pk_coverindex primary key (id1),add index idx_coverindex (id2);
复制代码
会话1执行
update t_coverindex set id1=0,id2=0,name='ses1' where id1=1;
一直不提交
复制代码
会话2执行
mysql> explain select id1,id2 from t_coverindex where id2=100000;
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table| type | possible_keys| key
| key_len | ref | rows | Extra |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------------+
|1 | SIMPLE| t_coverindex | ref| idx_coverindex | idx_coverindex | 4 | const |1 | Using index |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
复制代码
Extra=Using index而非using where,文档中的这句话该如何理解?
|