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

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

转载作者：qq735679552 更新时间：2022-09-27 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章导致MySQL做全表扫描的几种情况由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

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

情况1:

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

举例如下:

首先我们创建一个表。

 
    ? 
   
         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是主键.

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

 
    ? 
   
         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的执行情况，分别是:

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

结果如下:

 
    ? 
   
 
     
       
       
         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条数据:

 
    ? 
   
         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:

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

 
    ? 
   
 
     
       
       
         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值条件可能导致全表扫描.

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

 
    ? 
   
         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类型，我们来看下面三个语句的扫描行数:

 
    ? 
   
         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: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 。

最后此篇关于导致MySQL做全表扫描的几种情况的文章就讲到这里了,如果你想了解更多关于导致MySQL做全表扫描的几种情况的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： MySQL 截取字符串函数的sql语句

文章推荐： mysql主从复制读写分离的配置方法详解

文章推荐： MySQL多实例配置方案

文章推荐： MySQL中关于null值的一个小问题

做 Passport nodejs时的javascript语法
我有一个关于 JavaScript 语法的问题。实际上，我在自学 MEAN 堆栈教程时想出了编码(https://thinkster.io/mean-stack-tutorial#adding-aut
Perl && 做 { 最后; };
在我的书中它使用了这样的东西: for($ARGV[0]) { Expression && do { print "..."; last; }; ... } for 循环不完整吗？另外，do 的意义何
c - 做 while 循环过早退出
我已经编写了读取开关状态的代码，如果按 3 次 # 则退出。 void allkeypadTest(void) { static uint8_t modeKeyCount=0; do
Java 做 while 猜谜游戏
因此，对于上周我必须做的作业，我必须使用 4 个 do-while 循环和 if 语句在 Java 中制作一个猜谜游戏。我无法成功完成它，类(class)已经继续，没有为我提供任何帮助。如果有人可以查
c - 做 while 和右移没有效果
int i=1,j=0,n=10,k; do{ j+=i; i<<1; printf("%d\n",i); // printf("%d\n",12<<1); }while
java - 做 while 循环问题
此代码用于基本杂货计算器的按钮。当我按下按钮时，一个输入对话框会显示您输入商品价格的位置。我遇到的问题是我无法弄清楚如何获得 do ... while 循环以使输入对话框在输入后弹出。我希望它始终恢
c++ - 做 while 循环和其他
当我在循环中修改字符串或另一个变量时，它的条件是否每次都重新计算？或者在循环开始前一次 std::string a("aa"); do { a = "aaaa"; } while(a.size<10)
C 编程做 while
我刚刚写了这个，但我找不到问题。我使用代码块并编写了这个问题 error: expected 'while' before '{' token === Build finished: 1 errors
c 做 while 循环不起作用？
do { printf("Enter number (0-6): ", ""); scanf("%d", &Num); }while(Num >= 0 && Num 表示“超过”，<表
C++ 做 while 循环
我有一个包含 10 个项目的 vector (为简单起见，所有项目都属于同一类，称其为“a”)。我想要做的是检查“A”不是 a) 隐藏墙壁或 b) 隐藏另一个“A”。我有一个碰撞函数可以做到这一点。
Android 做 while 循环
嗨，这是我的第二个问题。我有下表 |-----|-------|------|------| |._id.|..INFO.|.DONE.|.LAST.| |..1..|...A...|...N..|.
C:做 {...} while(0)？
这个问题在这里已经有了答案: 关闭 12 年前。 Possible Duplicates: Why are there sometimes meaningless do/while and if/e
f# - 让!/做!总是在新线程中运行异步对象？
来自 wikibook在 F# 上有一小部分它说: What does let! do?# let! runs an async object on its own thread, then it i
haskell - (某事-> 做)的意思
我在 Real World Haskell 书中遇到了以下函数: namesMatching pat | not (isPattern pat) = do exists do
r - 做 arrangeGrob 时是否可以裁剪图？
我有一个类似于下面的用例，我创建了多个图并使用 gridExtra 将它们排列到一些页面布局中，最后使用 ggsave 将其保存为 PDF : p1 % mutate(label2
clojure - 打嗝代码没有响应没有(做(每个级别的html5
当我使用具有 for 循环的嵌套 let 语句时，如果没有 (do (html5 ..))，我将无法运行内部 [:tr]。 (defpartial column-settings-layout [&
virtualbox - 做 vagrant up 时出错
执行 vagrant up 时出现此错误: anr@anr-Lenovo-G505s ~ $ vagrant up Bringing machine 'default' up with 'virtua
perl - 错误消息:无法对未定义的值调用方法“做”
# ################################################# # Subroutine to add data to the table Blas
powershell - 做…直到-使用ValidPattern读取主机
我想创建一个检查特定日期格式的读取主机。此外，目标是检查用户输入是否正确，如果不正确，则提示应再次弹出。当我刚接触编程时，发现了这段代码，这似乎很合适。我仍然在努力“直到” do {
tensorflow - 做 Tensorflow 教程时出错
我关注这个tutorial在谷歌云机器学习引擎上进行培训。我一步一步地跟着它，但是在将 ml 作业提交到云时我遇到了错误。我运行了这个命令。 sam@sam-VirtualBox:~/models/r

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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