我有一个大约4M行的表。每天晚上,大约有15个批处理作业在数据上运行,其中有几十万个插入和更新。问题是,当我运行一个简单的计数查询,如
时select count(*) from items;
我必须等大约15分钟才能让它回来。在研究了SO之后,我发现
optimize table items;
似乎解决了这个问题,在运行它之后,上面的查询立即返回。问题是,它需要运行17个小时。有什么建议要寻找找出为什么会发生这种情况,以及如何解决它?
谢谢你的帮助,凯文
更新:下面是我优化的结果:
mysql> optimize table items;
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------+----------+----------+-------------------------------------------------------------------+
| g_production.items | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| g_production.items | optimize | status | OK |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (9 hours 20 min 48.36 sec)
而且,奇怪的是,select没有使用主索引ID:
explain select count(id) from items;
+----+-------------+-------+-------+---------------+--------------------------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------------- +---------+------+----------+-------------+
| 1 | SIMPLE | items | index | NULL | index_items_on_real_sale | 2 | NULL | 45152757 | Using index |
+----+-------------+-------+-------+---------------+--------------------------+---------+------+----------+-------------+
1 row in set (0.10 sec)
最后,这里是表上的所有索引:
+-------+------------+---------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| items | 0 | PRIMARY | 1 | id | A | 47144790 | NULL | NULL | | BTREE | | |
| items | 1 | index_items_on_affiliate_id | 1 | affiliate_id | A | 47144790 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_brand_id | 1 | brand_id | A | 1024886 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_real_sale | 1 | real_sale | A | 18 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_retailer_id_and_affiliate_id | 1 | retailer_id | A | 18 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_retailer_id_and_affiliate_id | 2 | affiliate_id | A | 47144790 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_retailer_id | 1 | retailer_id | A | 40021 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_shopzilla_id | 1 | shopzilla_id | A | 457716 | NULL | NULL | YES | BTREE | | |
| items | 1 | index_items_on_updated_at | 1 | updated_at | A | 6734970 | NULL | NULL | | BTREE | | |
注意EXPLAIN显示的索引上的基数,我有4M行,但是EXPLAIN说它使用的是index_items_on_real_sale, show indexes命令显示它的基数为18。这就是问题所在吗?
这可能是相当多的事情,但我想知道它是否被正确索引。另外,尝试使用explain运行查询,如下所示:
EXPLAIN SELECT a,b,c WHERE....
查看输出,看看它读取了多少行来处理查询,以及它们的索引类型等…
我需要更多的信息来帮助你,根据你提供的有限的信息,我只是猜测。