时间:2021-05-23
这两天看到了两种可能会导致全表扫描的sql,这里给大家看一下,希望可以避免踩坑:
强制类型转换的情况下,不会使用索引,会走全表扫描。
举例如下:
首先我们创建一个表
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,证明,如果出现了强制类型转换,则会导致索引失效。
反向查询不能使用索引,会导致全表扫描。
创建一个表test1,它的主键是score,然后插入6条数据:
CREATETABLE`test1`(`score`varchar(20) not null default '' ,PRIMARYKEY(`score`))ENGINE=InnoDBDEFAULTCHARSET=utf8mysql: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)可以看到,使用!=作为条件的时候,扫描的行数是表的总记录行数。因此如果想要使用索引,我们就不能使用反向匹配规则。
某些or值条件可能导致全表扫描。
首先我们创建一个表,并插入几条数据:
CREATETABLE`test4`(`id`int(11)DEFAULTNULL,`name`varchar(20)DEFAULTNULL,KEY`idx_id`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(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将二者连接起来就会导致扫描全表而不使用索引。
1.强制类型转换的情况下,不会使用索引,会走全表扫描
2.反向查询不能使用索引,会导致全表扫描。
3.某些or值条件可能导致全表扫描。
以上就是导致MySQL做全表扫描的几种情况的详细内容,更多关于MySQL 全表扫描的资料请关注其它相关文章!
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
在以下几种条件下,MySQL就会做全表扫描:1>数据表是在太小了,做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。
在MySQL数据库操作中,我们在做一些查询的时候总希望能避免数据库引擎做全表扫描,因为全表扫描时间长,而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使
表复制mysql拷贝表操作我们会常常用到,下面就为您详细介绍几种mysql拷贝表的方式,希望对您学习mysql拷贝表方面能够有所帮助。假如我们有以下这样一个表:
前言要想分析MySQL查询语句中的相关信息,如是全表查询还是部分查询,就要用到explain.索引的优点大大减少了服务器需要扫描的数据量可以帮助服务器避免排序或
选择扫描分辨率时所要考虑的因素只是你需要多大的图片,下面分为几种情况做详解: 1、如果是作为图像素材扫描,最终要打印输出,那么最好使用扫描仪的最高光学分辨率,