mysql - 为什么MySQL在执行这个查询时不使用索引？

Question

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> explain select * from users where imtype in ('1','2') and robotno is null;
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys          | key                    | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | i_users_imtype_robotno | i_users_imtype_robotno | 11      | NULL |    3 | Using where |
+----+-------------+-------+-------+------------------------+------------------------+---------+------+------+-------------+
1 row in set (0.01 sec)

此外，这个也没有使用索引:

mysql> explain select id,email,imtype from users where robotno=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |   24 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Answer 1

SELECT  *
FROM   users
WHERE  imtype != '0' and robotno is null

单个连续范围 (imtype, robotno) 不满足此条件.

如果您有这样的记录:

imtype  robotno

$       NULL
$       1    
0       NULL
0       1
1       NULL
1       1
2       NULL
2       1

，按 (imtype, robotno) 排序，则记录1 , 5和7会被返回，而其他记录则不会。

您需要创建此索引来满足条件:

CREATE INDEX ix_users_ri ON users (robotno, imptype)

并稍微重写您的查询:

SELECT  *
FROM   users
WHERE  (
       robotno IS NULL
       AND imtype < '0'
       )
       OR
       (
       robotno IS NULL
       AND imtype > '0'
       )

，这将产生两个连续的 block :

robotno imtype  

--- first block start
NULL    $
--- first block end
NULL    0
--- second block start
NULL    1
NULL    2
--- second block end
1       $
1       0
1       1
1       2

该索引还将服务于该查询:

SELECT id, email, imtype
FROM   users
WHERE  robotno = 1

，由于同样的原因，现在任何索引都不再提供服务。

实际上，这个查询的索引:

SELECT  *
FROM    users
WHERE   imtype in ('1', '2')
        AND robotno is null

仅用于imtype上的粗略过滤(注意 using where 字段中的 extra)，它的范围不在 robotno 范围内。的