当前位置:主页 > 站长学院 > 数据库教程 >

关于导致MySQL做全表扫描的三种情况的原因分析

YouYou 发布于 2021-03-25

关于导致MySQL做全表扫描的三种情况的原因分析,这两天看到了两种可能会导致全表扫描的sql,这里给大家看一下,希望可以避免踩坑:

原因一:强制类型转换的情况下,不会使用索引,会走全表扫描。

举例如下:

首先我们创建一个表

  1.  CREATE TABLE `test` ( 
  2.  
  3.   `id` int(11) NOT NULL AUTO_INCREMENT, 
  4.  
  5.   `age` int(11) DEFAULT NULL
  6.  
  7.   `score` varchar(20) NOT NULL DEFAULT ''
  8.  
  9.   PRIMARY KEY (`id`), 
  10.  
  11.   KEY `idx_score` (`score`) 
  12.  
  13. ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 

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

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

  1. mysql:yeyztest 21:43:12>>select * from test; 
  2.  
  3. +----+------+-------+ 
  4.  
  5. | id | age  | score | 
  6.  
  7. +----+------+-------+ 
  8.  
  9. |  1 |    1 | 5     | 
  10.  
  11. |  2 |    2 | 10    | 
  12.  
  13. |  5 |    5 | 25    | 
  14.  
  15. |  8 |    8 | 40    | 
  16.  
  17. |  9 |    2 | 45    | 
  18.  
  19. | 10 |    5 | 50    | 
  20.  
  21. | 11 |    8 | 55    | 
  22.  
  23. +----+------+-------+ 
  24.  
  25. rows in set (0.00 sec) 

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

  1. explain select * from test where score ='10'
  2.  
  3. explain select * from test where score =10; 

结果如下:

  1. mysql:yeyztest 21:42:29>>explain select * from test where score ='10'
  2.  
  3. +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 
  4.  
  5. | id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra | 
  6.  
  7. +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 
  8.  
  9. |  1 | SIMPLE      | test  | NULL       | ref  | idx_score     | idx_score | 62      | const |    1 |   100.00 | NULL  | 
  10.  
  11. +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 
  12.  
  13. 1 row in set, 1 warning (0.00 sec) 
  14.  
  15.  
  16.  
  17. mysql:yeyztest 21:43:06>>explain select * from test where score =10;   
  18.  
  19. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 
  20.  
  21. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | 
  22.  
  23. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 
  24.  
  25. |  1 | SIMPLE      | test  | NULL       | ALL  | idx_score     | NULL | NULL    | NULL |    7 |    14.29 | Using where | 
  26.  
  27. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 
  28.  
  29. 1 row in set, 3 warnings (0.00 sec) 

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

原因二:反向查询不能使用索引,会导致全表扫描。

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

  1. CREATE TABLE `test1` ( 
  2.  
  3.   `score` varchar(20) not null default '' , 
  4.  
  5.   PRIMARY KEY (`score`) 
  6.  
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  8.  
  9.  
  10.  
  11. mysql:yeyztest 22:09:37>>select * from test1; 
  12.  
  13. +-------+ 
  14.  
  15. | score | 
  16.  
  17. +-------+ 
  18.  
  19. | 111   | 
  20.  
  21. | 222   | 
  22.  
  23. | 333   | 
  24.  
  25. | 444   | 
  26.  
  27. | 555   | 
  28.  
  29. | 666   | 
  30.  
  31. +-------+ 
  32.  
  33. rows in set (0.00 sec) 

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

  1. explain select * from test1 where score='111'
  2.  
  3.  
  4.  
  5. explain select * from test1 where score!='111'
  6.  
  7. mysql:yeyztest 22:13:01>>explain select * from test1 where score='111'
  8.  
  9. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 
  10.  
  11. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       | 
  12.  
  13. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 
  14.  
  15. |  1 | SIMPLE      | test1 | NULL       | const | PRIMARY       | PRIMARY | 62      | const |    1 |   100.00 | Using index | 
  16.  
  17. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 
  18.  
  19. 1 row in set, 1 warning (0.00 sec) 
  20.  
  21.  
  22.  
  23. mysql:yeyztest 22:13:08>>explain select * from test1 where score!='111'
  24.  
  25. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 
  26.  
  27. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    | 
  28.  
  29. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 
  30.  
  31. |  1 | SIMPLE      | test1 | NULL       | index | PRIMARY       | PRIMARY | 62      | NULL |    6 |   100.00 | Using where; Using index | 
  32.  
  33. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 
  34.  
  35. 1 row in set, 1 warning (0.00 sec) 

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

原因三、某些or值条件可能导致全表扫描。

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

  1. CREATE TABLE `test4` ( 
  2.  
  3.   `id` int(11) DEFAULT NULL
  4.  
  5.   `namevarchar(20) DEFAULT NULL
  6.  
  7.   KEY `idx_id` (`id`) 
  8.  
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10.  
  11. 1 row in set (0.00 sec) 
  12.  
  13. mysql[email protected]:yeyztest 22:23:44>>select * from test4; 
  14.  
  15. +------+------+ 
  16.  
  17. | id   | name | 
  18.  
  19. +------+------+ 
  20.  
  21. |    1 | aaa  | 
  22.  
  23. |    2 | bbb  | 
  24.  
  25. |    3 | ccc  | 
  26.  
  27. |    4 | yeyz | 
  28.  
  29. NULL | yeyz | 
  30.  
  31. +------+------+ 
  32.  
  33. rows in set (0.00 sec) 

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

  1. mysql:yeyztest 22:24:12>>explain select * from test4 where id is null
  2.  
  3. +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ 
  4.  
  5. | id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                 | 
  6.  
  7. +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ 
  8.  
  9. |  1 | SIMPLE      | test4 | NULL       | ref  | idx_id        | idx_id | 5       | const |    1 |   100.00 | Using index condition | 
  10.  
  11. +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ 
  12.  
  13. 1 row in set, 1 warning (0.00 sec) 
  14.  
  15.  
  16.  
  17. mysql:yeyztest 22:24:17>>explain select * from test4 where id=1;                       
  18.  
  19. +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 
  20.  
  21. | id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra | 
  22.  
  23. +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 
  24.  
  25. |  1 | SIMPLE      | test4 | NULL       | ref  | idx_id        | idx_id | 5       | const |    1 |   100.00 | NULL  | 
  26.  
  27. +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 
  28.  
  29. 1 row in set, 1 warning (0.00 sec) 
  30.  
  31.  
  32.  
  33. mysql:yeyztest 22:24:28>>explain select * from test4 where id=1 or id is null
  34.  
  35. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 
  36.  
  37. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | 
  38.  
  39. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 
  40.  
  41. |  1 | SIMPLE      | test4 | NULL       | ALL  | idx_id        | NULL | NULL    | NULL |    5 |    40.00 | Using where | 
  42.  
  43. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 
  44.  
  45. 1 row in set, 1 warning (0.00 sec) 

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

简单总结一下:

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

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

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

    打赏是您对我们最大的鼓励!

    扫一扫打赏
    相关评论

    评论列表(条)

      ×
      QQ在线客服

      服务中心

      zfzf