mysql - 不使用索引进行连接并且索引完全相同

Question

我不确定为什么此查询没有使用表 world_cities 上的索引。索引相同且顺序相同。数据类型具有相同的类型和相同的长度。唯一的区别是key_name。

那么为什么它不使用 key 呢？
如果我运行查询，我得到:集合中的 318824 行(2 分 51.30 秒)

这是我所做的:

explain select * from zip_codes zc
inner join world_cities wc on 
    zc.city = wc.city 
    and zc.country = wc.country 
    and zc.region = wc.region;

+----+-------------+-------+------+---------------------+---------------------+---------+---------------------------------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref                                                           | rows    | Extra       |
+----+-------------+-------+------+---------------------+---------------------+---------+---------------------------------------------------------------+---------+-------------+
|  1 | SIMPLE      | wc    | ALL  | city_country_region | NULL                | NULL    | NULL                                                          | 3173958 |             |
|  1 | SIMPLE      | zc    | ref  | country_region_city | country_region_city | 165     | largedbapi.wc.city,largedbapi.wc.country,largedbapi.wc.region |       1 | Using where |
+----+-------------+-------+------+---------------------+---------------------+---------+---------------------------------------------------------------+---------+-------------+


mysql> show indexes from world_cities where Key_name like '%city%';
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| world_cities |          1 | city_country_region |            1 | city        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| world_cities |          1 | city_country_region |            2 | country     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| world_cities |          1 | city_country_region |            3 | region      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> show indexes from zip_codes where Key_name like '%city%';
+-----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| zip_codes |          1 | country_region_city |            1 | city        | A         |      218424 |     NULL | NULL   | YES  | BTREE      |         |
| zip_codes |          1 | country_region_city |            2 | country     | A         |      218424 |     NULL | NULL   | YES  | BTREE      |         |
| zip_codes |          1 | country_region_city |            3 | region      | A         |      436849 |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 1

world_cities 上的 city_country_region 键具有 NULL 基数，而需要非 NULL 基数才能使用。对它运行分析应该可以修复它。

ANALYZE TABLE world_cities

另请查看this post有关 NULL 基数的更多信息。