使用 SELECT ALL 进行二进制较低时不使用 MySQL 索引

Question

我有一个像这样的关键字表:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | YES  | MUL | NULL    |                |
| country | varchar(2)   | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

我在[名称，国家/地区]上有复合索引:

+----------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table    | Non_unique | Key_name                           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+----------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| keywords |          0 | PRIMARY                            |            1 | id          | A         |      377729 |     NULL | NULL   |      | BTREE      |
| keywords |          1 | index_keywords_on_name_and_country |            1 | name        | A         |      377729 |     NULL | NULL   | YES  | BTREE      |
| keywords |          1 | index_keywords_on_name_and_country |            2 | country     | A         |      377729 |     NULL | NULL   | YES  | BTREE      |
+----------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

我需要使用BINARY LOWER来比较name字段，所以我的查询将如下所示:

SELECT keywords.* FROM `keywords` WHERE (BINARY LOWER(`name`) = BINARY LOWER('Apple') AND `country` = 'US');

但问题是:它没有使用索引。使用我的解释:

+------+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | keywords | ALL  | NULL          | NULL | NULL    | NULL | 366519 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+--------+-------------+

但是，如果我选择某些字段，它将使用索引:

，而不是 select *

Explain SELECT keywords.id, keywords.name FROM `keywords` WHERE (BINARY LOWER(`name`) = BINARY LOWER('Apple') AND `country` = 'US');

+------+-------------+----------+-------+---------------+------------------------------------+---------+------+--------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key                                | key_len | ref  | rows   | Extra                    |
+------+-------------+----------+-------+---------------+------------------------------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | keywords | index | NULL          | index_keywords_on_name_and_country | 777     | NULL | 366519 | Using where; Using index |
+------+-------------+----------+-------+---------------+------------------------------------+---------+------+--------+--------------------------+

我使用的是 MySQL 5.5。

发生这种情况有什么原因吗？

有没有办法可以在查询中使用索引？或者如何更改我的查询和表以便使用索引来加速查询。

谢谢

Answer 1

为什么需要与binary lower()进行比较？这对于关键字来说似乎是一个非常奇怪的要求。

无论如何，您都可以使用子查询来做到这一点:

SELECT k.*
FROM (SELECT k.*
      FROM `keywords` k
      WHERE name = 'Apple' and country = 'US'
     ) k
WHERE (BINARY LOWER(`name`) = BINARY LOWER('Apple') AND `country` = 'US');

内部子查询应该使用索引。生成的扫描应该是在一个小的子集上，所以它应该很快。