mysql - 如何在 MYSQL 中优化此查询？

Question

mysql> explain select a.id,a.title from users c
    ->                                  straight_join iask a on c.id=a.uid
    ->                                         straight_join ianswer b on a.id=b.iaskid
    ->
    ->                                  where (c.last_check is null or b.created>c.last_check) and c.id in (1,2)
    ->                                  group by a.id;
+----+-------------+-------+-------+---------------------------+------------+---------+----------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys             | key        | key_len | ref      | rows | Extra                                        |
+----+-------------+-------+-------+---------------------------+------------+---------+----------+------+----------------------------------------------+
|  1 | SIMPLE      | c     | range | PRIMARY,i_users_lastcheck | PRIMARY    | 4       | NULL     |    2 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref   | PRIMARY,i_iask_uid        | i_iask_uid | 4       | bbs.c.id |    2 |                                              |
|  1 | SIMPLE      | b     | ALL   | i_ianswer_iaskid          | NULL       | NULL    | NULL     |   17 | Using where; Using join buffer               |
+----+-------------+-------+-------+---------------------------+------------+---------+----------+------+----------------------------------------------+
3 rows in set (0.00 sec)

把上面的"in (1,2)"改成"=1"就可以全部使用索引了:

mysql> explain select a.id,a.title from iask a
    ->                                          join ianswer b on a.id=b.iaskid
    ->                                          join users c on c.id=a.uid
    ->                                  where (c.last_check is null or b.created>c.last_check) and c.id=1
    ->                                  group by a.id;
+----+-------------+-------+-------+---------------------------+------------------+---------+----------+------+---------------------------------+
| id | select_type | table | type  | possible_keys             | key              | key_len | ref      | rows | Extra                           |
+----+-------------+-------+-------+---------------------------+------------------+---------+----------+------+---------------------------------+
|  1 | SIMPLE      | c     | const | PRIMARY,i_users_lastcheck | PRIMARY          | 4       | const    |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref   | PRIMARY,i_iask_uid        | i_iask_uid       | 4       | const    |    1 | Using where                     |
|  1 | SIMPLE      | b     | ref   | i_ianswer_iaskid          | i_ianswer_iaskid | 4       | bbs.a.id |    8 | Using where                     |
+----+-------------+-------+-------+---------------------------+------------------+---------+----------+------+---------------------------------+
3 rows in set (0.00 sec)

表结构如下:

mysql> show create table users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| users | CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) NOT NULL,
  `password` varchar(32) NOT NULL,
  `screen_name` varchar(64) DEFAULT NULL,
  `reputation` int(10) unsigned NOT NULL DEFAULT '0',
  `imtype` varchar(1) DEFAULT '0' COMMENT '0--email,1--gtalk,2--msn',
  `last_check` datetime DEFAULT NULL,
  `robotno` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_users_email` (`email`),
  UNIQUE KEY `u_users_screen_name` (`screen_name`),
  KEY `i_users_lastcheck` (`last_check`),
  KEY `i_users_imtype_robotno` (`imtype`,`robotno`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)

mysql> show create table iask;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| iask  | CREATE TABLE `iask` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `title` varchar(250) NOT NULL,
  `body` text,
  `tags` varchar(100) NOT NULL,
  `views` int(10) unsigned DEFAULT '0',
  `votes` int(10) unsigned DEFAULT '0',
  `answer_id` int(10) unsigned DEFAULT NULL,
  `created` datetime NOT NULL,
  `keywords` text,
  PRIMARY KEY (`id`),
  KEY `i_iask_uid` (`uid`),
  FULLTEXT KEY `keywords` (`keywords`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> show create table ianswer;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ianswer | CREATE TABLE `ianswer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `iaskid` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned DEFAULT NULL,
  `body` text,
  `votes` int(10) unsigned DEFAULT '0',
  `created` datetime NOT NULL,
  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `i_ianswer_iaskid` (`iaskid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Answer 1

如果您询问如何使第一个查询像第二个查询那样使用索引，我怀疑这是因为您的表中没有足够的行来证明两次索引查找是合理的。全表扫描的行数为 17 行，因此它认为仅在行间旋转以查找两个 ID 会更快。如果您担心，您可以尝试填充表以查看它是否仍然选择全表扫描。