导致MySQL做全表扫描的几种情况(扫描索引)

情况1：

强制类型转换的情况下，不会使用索引，会走全表扫描。

举例如下：

首先我们创建一个表

CREATETABLE`test`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`age`int(11)DEFAULTNULL,
`score`varchar(20)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
KEY`idx_score`(`score`)
)ENGINE=InnoDBAUTO_INCREMENT=12DEFAULTCHARSET=utf8

我们可以看到，这个表有三个字段，其中两个int类型，一个varchar类型。varchar类型的字段score是一个索引，而id是主键。

然后我们给这个表里面插入一些数据，插入数据之后的表如下：

mysql：yeyztest 21:43:12>>select*fromtest;
+----+------+-------+
|id|age|score|
+----+------+-------+
|1|1|5|
|2|2|10|
|5|5|25|
|8|8|40|
|9|2|45|
|10|5|50|
|11|8|55|
+----+------+-------+
7rowsinset(0.00sec)

这个时候，我们使用explain语句来查看两条sql的执行情况，分别是：

explain select * from test where score ='10';
explain select * from test where score =10;

结果如下：

mysql:yeyztest21:42:29>>explainselect*fromtestwherescore='10';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|1|SIMPLE|test|NULL|ref|idx_score|idx_score|62|const|1|100.00|NULL|
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1rowinset,1warning(0.00sec)
mysql:yeyztest21:43:06>>explainselect*fromtestwherescore=10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1|SIMPLE|test|NULL|ALL|idx_score|NULL|NULL|NULL|7|14.29|Usingwhere|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1rowinset,3warnings(0.00sec)

可以看到，如果我们使用的是varchar类型的值，那么结果中扫描的行数rows就是1，而当我们使用的是整数值10的时候，扫描行数变为了7，证明，如果出现了强制类型转换，则会导致索引失效。

情况2：

反向查询不能使用索引，会导致全表扫描。

创建一个表test1，它的主键是score，然后插入6条数据：

CREATETABLE`test1`(
`score`varchar(20) not null default '' ,
PRIMARYKEY(`score`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
mysql:yeyztest22:09:37>>select*fromtest1;
+-------+
|score|
+-------+
|111|
|222|
|333|
|444|
|555|
|666|
+-------+
6rowsinset(0.00sec)

当我们使用反向查找的时候，不会使用到索引，来看下面两条sql：

explain select * from test1 where score='111';
explain select * from test1 where score!='111';

mysql:yeyztest22:13:01>>explainselect*fromtest1wherescore='111';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|1|SIMPLE|test1|NULL|const|PRIMARY|PRIMARY|62|const|1|100.00|Usingindex|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1rowinset,1warning(0.00sec)
mysql:yeyztest22:13:08>>explainselect*fromtest1wherescore!='111';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|1|SIMPLE|test1|NULL|index|PRIMARY|PRIMARY|62|NULL|6|100.00|Usingwhere;Usingindex|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1rowinset,1warning(0.00sec)

可以看到，使用！=作为条件的时候，扫描的行数是表的总记录行数。因此如果想要使用索引，我们就不能使用反向匹配规则。

情况3：

某些or值条件可能导致全表扫描。

首先我们创建一个表，并插入几条数据：

CREATETABLE`test4`(
`id`int(11)DEFAULTNULL,
`name`varchar(20)DEFAULTNULL,
KEY`idx_id`(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)
mysql--dba_admin@127.0.0.1:yeyztest22:23:44>>select*fromtest4;
+------+------+
|id|name|
+------+------+
|1|aaa|
|2|bbb|
|3|ccc|
|4|yeyz|
|NULL|yeyz|
+------+------+
5rowsinset(0.00sec)

其中表test4包含两个字段，id字段是一个索引，而name字段是varchar类型，我们来看下面三个语句的扫描行数：

explain select * from test4 where id=1;
explain select * from test4 where id is null;
explain select * from test4 where id=1 or id is null;

mysql:yeyztest22:24:12>>explainselect*fromtest4whereidisnull;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
|1|SIMPLE|test4|NULL|ref|idx_id|idx_id|5|const|1|100.00|Usingindexcondition|
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
1rowinset,1warning(0.00sec)
mysql:yeyztest22:24:17>>explainselect*fromtest4whereid=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|1|SIMPLE|test4|NULL|ref|idx_id|idx_id|5|const|1|100.00|NULL|
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1rowinset,1warning(0.00sec)
mysql:yeyztest22:24:28>>explainselect*fromtest4whereid=1oridisnull;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1|SIMPLE|test4|NULL|ALL|idx_id|NULL|NULL|NULL|5|40.00|Usingwhere|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1rowinset,1warning(0.00sec)

可以看到单独使用id=1和id is null，都只会扫描一行记录，而使用or将二者连接起来就会导致扫描全表而不使用索引。