阅读背景:

为什么MySQL在执行此查询时不使用索引?

来源:互联网 
mysql> desc users;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| email       | varchar(128)     | NO   | UNI |         |                |
| password    | varchar(32)      | NO   |     |         |                |
| screen_name | varchar(64)      | YES  | UNI | NULL    |                |
| reputation  | int(10) unsigned | NO   |     | 0       |                |
| imtype      | varchar(1)       | YES  | MUL | 0       |                |
| last_check  | datetime         | YES  | MUL | NULL    |                |
| robotno     | int(10) unsigned | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)


mysql> create index i_users_imtype_robotno on users(imtype,robotno);
Query OK, 24 rows affected (0.25 sec)
Records: 24  Duplicates: 0  Warnings: 0
mysql> explain select * from users where imtype!='0' and robotno is null;
+----+-------------+-------+------+------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys          | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | i_users_imtype_robotno | NULL | NULL    | NULL |   24 | Using where |
+----+-------------+-------+------+------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> desc users;
+-------------+-------------



你的当前访问异常,请进行认证后继续阅读剩余内容。

分享到: