mysql用的索引不符合预期_MySQL索引未按预期使用

我该怎么做才能让

mysql使用预期的索引？

我有4个表,其中两个包含资源,其他包含历史变化.

一对正确使用索引,另一对没有,但两者的结构几乎相同.

我已经尝试更改主键的顺序,以及其他键的顺序,我尝试更改表结构,因此它们在两个表中使用相同的名称,并且两者都具有相同的键名,但似乎没有任何结果查询使用正确的索引.

为简洁起见,已删除不相关的列.

这两个表按预期工作.

CREATE TABLE `players` (

`player_id` varbinary(36) NOT NULL DEFAULT '',

`pop_rank_score` double NOT NULL DEFAULT '0',

PRIMARY KEY (`player_id`),

KEY `pop_rank_score` (`pop_rank_score`),

KEY `weblinc_id` (`weblinc_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `poprankhistory` (

`day_id` int(11) NOT NULL,

`player_id` varbinary(36) NOT NULL DEFAULT '',

`total` double NOT NULL DEFAULT '0',

`today` double NOT NULL DEFAULT '0',

PRIMARY KEY (`day_id`,`player_id`),

KEY `day_id` (`day_id`),

KEY `player_id` (`player_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

explain select p.`player_id`, p.pop_rank_score + 0.5 * COALESCE(h1.total,0) as pop_rank_score

from fpme_lua.`Players` p, fpme_lua.PopRankHistory h1

where ( p.`player_id` = h1.`player_id` AND h1.day_id = (SELECT Max(h2.day_id) AS day_id

FROM fpme_lua.poprankhistory h2

WHERE h2.day_id <= 15786 and h2.player_id = p.`player_id` ));

+----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+

| 1 | PRIMARY | h1 | ALL | PRIMARY,day_id,player_id | NULL | NULL | NULL | 25391 | |

| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 38 | fpme_lua.h1.player_id | 1 | Using where |

| 2 | DEPENDENT SUBQUERY | h2 | ref | PRIMARY,day_id,player_id | player_id | 38 | fpme_lua.p.player_id | 2 | Using where; Using index |

+----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+

这些表未按预期工作(必需).

CREATE TABLE `pictures` (

`id` varchar(36) NOT NULL DEFAULT '',

`pcr_score` double NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `owner_id` (`owner_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `picpcrhistory` (

`day_id` int(11) NOT NULL,

`target_id` varchar(36) NOT NULL DEFAULT '',

`total` double NOT NULL DEFAULT '0',

`today` double NOT NULL DEFAULT '0',

PRIMARY KEY (`day_id`,`target_id`),

KEY `target_id` (`target_id`),

KEY `day_id` (`day_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

explain select p.`id`, p.pcr_score + 0.5 * COALESCE(h1.total,0) as pcr_score

from fpme_lua.`Pictures` p, fpme_lua.PicPcrHistory h1

where ( p.`id` = h1.`target_id` AND h1.day_id = (SELECT Max(h2.day_id) AS day_id

FROM fpme_lua.PicPcrHistory h2

WHERE h2.day_id <= 15786 and h2.`target_id` = p.`id` ));

+----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+

| 1 | PRIMARY | h1 | ALL | PRIMARY,day_id | NULL | NULL | NULL | 65310 | |

| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 110 | func | 1 | Using where |

| 2 | DEPENDENT SUBQUERY | h2 | range | PRIMARY,day_id | day_id | 4 | NULL | 21824 | Using where; Using index |

+----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+