drop table if exists test;
create table test
(
id int not null,
int_k int not null,
data1 varchar(255) not null,
data2 varchar(255) not null,
primary key (id),
key (int_k)
) ENGINE=innodb;
drop procedure if exists populate;
delimiter //
create procedure populate()
begin
declare i int; set i = 0;
start transaction;
while i < 3000000 do
insert into test (id, int_k, data1, data2)
values (i, i, repeat("-", 250), repeat("-", 250));
set i = i + 1;
if i % 1000 = 0 then
start transaction;
end if;
end while;
commit;
end;
//
delimiter ;
call populate();
drop procedure populate;
explain select count(*) from test use index (int_k);
explain select count(*) from test;
select count(*) from test use index (int_k);
select count(*) from test;测试发现主键COUNT要比走第二索引COUNT花费很多时间,优化器为什么不会直接选择走第二索引?难道是Mysql的优化器还不成熟?
+----+-------------+-------------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | test_innodb | index | NULL | int_k | 4 | NULL | 300029 | Using index |
+----+-------------+-------------+-------+---------------+-------+---------+------+--------+-------------+
1 row in set (0.00 sec)mysql> explain select count(*) from test_innodb use index (PRIMARY);
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | test_innodb | index | NULL | PRIMARY | 4 | NULL | 300029 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)mysql> explain select count(*) from test_innodb;
+----+-------------+-------------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | test_innodb | index | NULL | int_k | 4 | NULL | 300029 | Using index |
+----+-------------+-------------+-------+---------------+-------+---------+------+--------+-------------+
1 row in set (0.00 sec)mysql> select SQL_NO_CACHE count(*) from test_innodb use index (int_k);
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.16 sec)mysql> select SQL_NO_CACHE count(*) from test_innodb use index (PRIMARY);
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (6.16 sec)mysql> select SQL_NO_CACHE count(*) from test_innodb;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.30 sec)mysql>
+------------------+
| version() |
+------------------+
| 5.1.52-community |
+------------------+
1 row in set (0.03 sec)mysql>