导致MySQL做全表扫描的几种情况

     这两天看到了两种可能会导致全表扫描的sql，这里给大家看一下，希望可以避免踩坑：

情况1：

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

举例如下：

首先我们创建一个表

?

1 2 3 4 5 6 7

create  table  `test` (    `id`  int (11)  not  null  auto_increment,    `age`  int (11)  default  null ,    `score`  varchar (20)  not  null  default  '' ,    primary  key  (`id`),    key  `idx_score` (`score`) ) engine=innodb auto_increment=12  default  charset=utf8

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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

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

?

1 2 3

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

结果如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql:yeyztest 21:42:29>>explain  select  *  from  test  where  score = '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   | + ----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row  in  set , 1 warning (0.00 sec)   mysql:yeyztest 21:43:06>>explain  select  *  from  test  where  score =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 | using  where  | + ----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row  in  set , 3 warnings (0.00 sec)

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

情况2：

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create  table  `test1` (    `score`  varchar (20) not null default '' ,    primary  key  (`score`) ) engine=innodb  default  charset=utf8   mysql:yeyztest 22:09:37>> select  *  from  test1; + -------+ | score | + -------+ | 111   | | 222   | | 333   | | 444   | | 555   | | 666   | + -------+ 6  rows  in  set  (0.00 sec)

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

?

1 2 3

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql:yeyztest 22:13:01>>explain  select  *  from  test1  where  score= '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 | using  index  | + ----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row  in  set , 1 warning (0.00 sec)   mysql:yeyztest 22:13:08>>explain  select  *  from  test1  where  score!= '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 | using  where ; using  index  | + ----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row  in  set , 1 warning (0.00 sec)

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

情况3：

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

create  table  `test4` (    `id`  int (11)  default  null ,    ` name `  varchar (20)  default  null ,    key  `idx_id` (`id`) ) engine=innodb  default  charset=utf8 1 row  in  set  (0.00 sec)   mysql --dba_admin@127.0.0.1:yeyztest 22:23:44>>select * from test4; + ------+------+ | id   |  name  | + ------+------+ |    1 | aaa  | |    2 | bbb  | |    3 | ccc  | |    4 | yeyz | |  null  | yeyz | + ------+------+ 5  rows  in  set  (0.00 sec)

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

?

1 2 3 4 5

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 ;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql:yeyztest 22:24:12>>explain  select  *  from  test4  where  id  is  null ; + ----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ | 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 | using  index  condition | + ----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ 1 row  in  set , 1 warning (0.00 sec)   mysql:yeyztest 22:24:17>>explain  select  *  from  test4  where  id=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   | + ----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 1 row  in  set , 1 warning (0.00 sec)   mysql:yeyztest 22:24:28>>explain  select  *  from  test4  where  id=1  or  id  is  null ; + ----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | using  where  | + ----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row  in  set , 1 warning (0.00 sec)

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

简单总结一下：

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

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

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

以上就是导致mysql做全表扫描的几种情况的详细内容，更多关于mysql 全表扫描的资料请关注服务器之家其它相关文章！

原文链接：https://mp.weixin.qq.com/s/5G1xGrxb6ii_gpcWE1hC6A