无法强制mysql使用索引

Question

有关force索引有许多类似的问题，但我的问题有所不同，因为我在本地dev服务器上成功地使用了索引，但在生产服务器上不能使用。

我有一张有超过2000万张唱片的桌子，MyISAM。

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `upload_id` int(10) unsigned NOT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `upload_id` (`upload_id`)
  KEY `for_where` (`user_id`,`amount`,`upload_id`),
) ENGINE=MyISAM AUTO_INCREMENT=20547623;

我需要运行以下查询：

SELECT COUNT(DISTINCT `t`.`id`)
  FROM `cs` `t`
  LEFT OUTER JOIN `uploads` `Upload` ON (`t`.`upload_id`=`Upload`.`id`)
WHERE (((t.user_id=1) AND (upload_id IN (1,2,3)))
  AND (((Upload.term IS NOT NULL) AND (Upload.term<>"")) AND (t.amount>0)))

查询花费了超过20秒，并解释说它“使用的地方”。

+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
| id | select_type | table  | type  | possible_keys             | key       | key_len | ref                | rows   | Extra       |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
|  1 | SIMPLE      | Upload | range | PRIMARY                   | PRIMARY   | 4       | NULL               |      3 | Using where |
|  1 | SIMPLE      | t      | ref   | user_id,for_where,upload_id | upload_id | 4       | mydb.Upload.id | 381427 | Using where |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+

但是，如果我删除键upload_id或只添加IGNORE INDEX (upload_id)，它将使用“for_where”键和“使用索引条件”，查询只需1，5秒。

+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
| id | select_type | table  | type   | possible_keys   | key      | key_len | ref                  | rows   | Extra                 |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
|  1 | SIMPLE      | t      | range  | user_id,for_where | for_where | 11      | NULL                 | 693667 | Using index condition |
|  1 | SIMPLE      | Upload | eq_ref | PRIMARY         | PRIMARY  | 4       | adrevyii.t.upload_id |      1 | Using where           |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+

这对我来说是个好结果，所以我试着在生产服务器上推出这个，但是没有运气。它总是说“使用地方”，但仍然运行超过20秒。

我检查过不同之处，但找不到。本地表和生产表都使用MyISAM，类似的索引和相同的数据(只有一个例外)--生产表有200万条记录。

我的本地服务器版本: 5.6.13 Mac我的生产服务器版本: 5.1.69-0ubuntu0.11.10.1 (Ubuntu)

为什么会发生这种事？如何强制生产服务器使用索引？可能是某些服务器设置会影响它吗？是因为版本吗？能修好吗？是否有可能加快这个查询的速度？

Answer 1

您的查询可以简化为：

SELECT COUNT(DISTINCT t.id)
FROM cs AS t
  JOIN uploads AS u 
    ON t.upload_id = u.id
WHERE t.user_id = 1 
  AND t.upload_id IN (1,2,3)
  AND u.term > '' 
  AND t.amount > 0 ;

还包括：

SELECT COUNT(*)
FROM cs AS t
WHERE t.user_id = 1 
  AND t.upload_id IN (1,2,3)
  AND t.amount > 0
  AND EXISTS
      ( SELECT *
        FROM uploads AS u 
        WHERE t.upload_id = u.id
          AND u.term > '' 
      ) ;