mysql - 选择速度很慢但 key 唯一，有什么办法改进吗？

Question

根据我的授权，我通过用户的社交 ID 查找用户:

select * from users where yandex_id = 65250508;

结果非常糟糕:一组中有 1 行(11.25 秒)

该表的计数:

select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   1852446 |
+-----------+

还有我的查询的解释:

explain select * from users where yandex_id = 65250508;
+------+-------------+-------+------+---------------------------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys                   | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | users | ALL  | UNIQ_1483A5E988FDD79D,yandex_id | NULL | NULL    | NULL | 1820017 | Using where |
+------+-------------+-------+------+---------------------------------+------+---------+------+---------+-------------+

表的描述:

describe users;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| ts          | int(10) unsigned    | NO   | MUL | NULL    |                |
| last_ts     | int(10) unsigned    | NO   | MUL | NULL    |                |
| last_mail   | int(10) unsigned    | NO   |     | NULL    |                |
| photo       | varchar(32)         | YES  |     | NULL    |                |
| name        | varchar(48)         | YES  |     | NULL    |                |
| email       | varchar(48)         | YES  | UNI | NULL    |                |
| state       | smallint(6)         | NO   | MUL | NULL    |                |
| ip          | bigint(20) unsigned | NO   |     | NULL    |                |
| gender      | varchar(1)          | NO   |     | NULL    |                |
| facebook_id | varchar(64)         | YES  | UNI | NULL    |                |
| mailru_id   | varchar(64)         | YES  | UNI | NULL    |                |
| vk_id       | varchar(64)         | YES  | UNI | NULL    |                |
| yandex_id   | varchar(64)         | YES  | UNI | NULL    |                |
| google_id   | varchar(64)         | YES  | UNI | NULL    |                |
| roles       | longtext            | YES  |     | NULL    |                |
| is_active   | tinyint(1)          | NO   | MUL | 1       |                |
+-------------+---------------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)

Answer 1

尝试在查询中显式使用 USE INDEX(update_index)。

有时优化器在选择索引时会做出错误的选择，从而导致查询变慢。