count(*) 一个“空表”为什么会很慢呢? 只有几十行数据为什么 select xxx from table limit 1 需要 300ms ? select min(pk) from table 也会慢? 见下例: 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 xxx 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) |
|
来自: 昵称28748685 > 《待分类》