mysql count(*)空表為何會很慢

Count(*)一個“空表”為什麼會很慢呢？只有幾十行資料為什麼select xxx from table limit 1需要300ms？select min(pk) fromtable也會慢？

見下例：

dba@localhost : test 18:14:32> show create table test_hmw\G

*************************** 1. row ***************************

Table: test_hmw

Create Table: CREATE TABLE `test_hmw` (

`mail` varchar(1024)DEFAULT NULL,

`user_id` bigint(20)DEFAULT NULL,

`id` int(10) unsigned NOTNULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3997636 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

dba@localhost : test 18:14:45> select count(*) from test_hmw;

+----------+

| count(*) |

+----------+

| 3813472 |

+----------+

1 row in set (0.69 sec)

dba@localhost : test 18:14:56> show status like '%last_query%';

+-----------------+---------------+

| Variable_name |Value |

+-----------------+---------------+

| Last_query_cost | 776677.599000 |

+-----------------+---------------+

1 row in set (0.00 sec)

dba@localhost : test 18:15:14> delete from test_hmw;

Query OK, 3813472 rows affected (15.11 sec)

dba@localhost : test 18:15:56> select count(*) fromtest_hmw;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.31 sec)

dba@localhost : test 18:15:59> show status like '%last_query%';

+-----------------+---------------+

| Variable_name |Value |

+-----------------+---------------+

| Last_query_cost | 757879.799000 |

+-----------------+---------------+

1 row in set (0.00 sec)

我們發現count一個空表的cost和刪除前是一樣的時間不一樣是因為緩衝）；其實在Oracle裡也存在這個現象，對於select xx from table where rownum<xxx或者select count(*)之類的操作是從起始的block掃描到HMW；而在MySQL裡不是HMW，MySQL的多版本是直接儲存在表裡的，因此MySQL在刪除了大量的資料，而purge thread回收前進行上面的操作就會掃描大量的“空”page，因此也就有了這種現象；

既然知道原因在MySQL可以通過重整表的方式再來看看cost:

dba@localhost : test 18:16:01> optimize table test_hmw;

2 rows in set (1.85 sec)

dba@localhost : test 18:16:14> select count(*) fromtest_hmw;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

dba@localhost : test 18:16:16> show status like '%last_query%';

+-----------------+----------+

| Variable_name |Value |

+-----------------+----------+

| Last_query_cost | 1.199000 |

+-----------------+----------+

1 row in set (0.00 sec)

本文出自 “MIKE老畢的部落格” 部落格，請務必保留此出處http://boylook.blog.51cto.com/7934327/1299866