vlambda博客
学习文章列表

mysql创建的索引不生效,查询数据仍是全表扫描?

一年前,有人问我,为什么我创建了索引,然后用这个字段做查询的时候还是全表扫描?如题:

  • 建表语句
-- auto-generated definition
create table t_test
(
    id    int auto_increment
        primary key,
    name  varchar(10)
  null,
    hobby varchar(255) null
)
;

create index t_test_pk_2
    on t_test (hobby)
;
  • 测试数据
INSERT INTO `wechat_article`.`t_test`(`name`, `hobby`) VALUES ('小朱''玩游戏');
INSERT INTO `wechat_article`.`t_test`(`name`, `hobby`) VALUES ('小王''学习');
INSERT INTO `wechat_article`.`t_test`(`name`, `hobby`) VALUES ('小单''玩游戏');
INSERT INTO `wechat_article`.`t_test`(`name`, `hobby`) VALUES ('小强''看书');
INSERT INTO `wechat_article`.`t_test`(`name`, `hobby`) VALUES ('小祝''玩游戏');
id name hobby
1 小朱 玩游戏
2 小王 学习
3 小单 玩游戏
4 小强 看书
5 小祝 玩游戏
select * from t_test where hobby = '玩游戏'

这条语句走没走索引!

我觉得吧,会有 90% 的人会觉的走索引吧!

不论怎么样我们用 explain 查一下看看

explain select * from t_test where hobby = '玩游戏';

执行结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_test
ALL t_test_pk_2


5 60 Using where

为了照顾有的同学不知道上面的每列的意思,下面我就介绍下每列吧。

id

编号,没什么好讲的。

select_type

查询的类别,是简单的还是复杂的。复杂的又分为3种。

  • SIMPLE:表示简单的 select ,没有 union 和子查询
  • PRIMARY:最外面的查询 或者 主查询,在有子查询的语句中,最外面的 select 查询就是PRIMARY
  • SUBQUERY:子查询
  • UNION:UNION语句的第二个或者说是后面那一个 select
  • UNION RESULTt:UNION 之后的结果
  • DEPENDENT UNOIN:UNOIN 中的第二个或随后的 select 查询,依赖于外部查询的结果集
  • DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部 查询的结果集
  • DERIVED:衍生表

table

表的名称,或者表的别名。

partitions

使用的哪些分区(对于非分区表值为null)

type

  • const:表中最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快

  • eq_ref:唯一性索引扫描,对于每个来自于前面的表的记录,从该表中读取唯一一行

  • ref:非唯一性索引扫描,对于每个来自于前面的表的记录,所有匹配的行从这张表取出

  • ref_or_null:类似于ref,但是可以搜索包含null值的行,例如:select * from student where address='xxx' or address is null,需要在 address 建立索引。

  • index_merge:查询语句用到了一张表的多个索引时,mysql会将多个索引合并到一起

  • range:按指定范围(如in、<、>、between and等,但是前提是此字段要建立索引)来检索,很常见。如:select * from student where id < 5,id上要有索引。

  • index:全”表“扫描,但是是在索引树中扫描,通常比 ALL 快,因为索引文件通常比数据文件小,index 扫描是通过二叉树的方式扫描,而 All 是扫描物理表。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)。例如:select name from student,但name字段上需要建立索引,也就是查询的字段属于索引中的字段。

  • ALL:全表扫描,扫描完整的物理表,此时就需要优化了。

possible_keys

指出 MySQL 能在该表中可能使用的索引,显示的是索引的名称,多个索引用逗号隔开,如果没有,则为 null 。

key

MySQL决定实际用到的索引,显示的是索引的名称,多个索引用逗号隔开,如果没有,则为 null 。

key_len

key_len 只计算 where 条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到 key_len 中。

ref

显示索引的哪一列被使用了,如果可能的话,是一个常数

rows

显示mysql认为执行查询时必须要返回的行数,可结合 type 和 key 分析,没有用上索引的情况下,会全表扫描。rows 的值越小越好,说明检索的数据少

filtered

给出了一个百分比的值,这个百分比值和rows列的值一起使用,可以估计出那些将要和执行计划中的前一个表(前一个表就是指id列的值比当前表的id小的表)进行连接的行的数目。

extra

  • using where:表示查询使用了 where 语句来处理结果

  • using index:表示使用了覆盖索引。

  • using join buffer:这个值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。

  • using filesort:这是 order by 语句的结果。

  • using temporary:mysql 需要创建一张临时表来保存中间结果。

每列都介绍完了,上面用 explain 得出来的结果得知, select * from t_test where hobby = '玩游戏' 这个 sql 没走索引而是走的全表扫描。有人就会觉得很好奇,为什么索引失效了呢?

解释这个问题前,我在问个问题,select * from t_test where hobby = '看书' 这条语句是走索引,还是全表扫描呢?有人会觉得应该和上面一样吧,难道走不走索引还和数据有关?

explain select * from t_test where hobby = '学习';

执行结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_test
ref t_test_pk_2 t_test_pk_2 1023 const 1 100

从上面可以看出,这条语句走了,非唯一性索引扫描(type = ref)索引是 t_test_pk_2 。

哈哈,还真和数据有关系。我勒个擦擦擦!!!

其实,我们都知道 mysql 在查询时都有个执行计划,执行计划怎么来的呢?

看上图就知道了,有个 查询优化器 MySql 使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划的成本,并选择其中成本最小的一个。也就是说, MySql 会执行自己觉得最优的查询逻辑。如果你并不是数据库大师,那 MySql 的查询优化器就是最好的选择。那为什么数据库会选择走全表扫描呢?为什么数据库觉得走全表扫描,比走索引快呢!

说到这我们就要得聊聊索引方面的知识了,这里我们就只聊聊 聚簇索引和非聚簇索引 吧。

聚簇索引:聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。

非聚簇索引(辅助索引):在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。

所以,上面我们在 hobby 上建的索引其实就是辅助索引!也就是说我们用 hobby 索引查找需要二次查找的。也就是说当你获取的数量过大,然后再查找两次的情况下 MySql 会觉得走全表扫描效率会更高。这边在网上找的的说法是,当你查找的数据数据量占比高于 30%-40%(此值只是个模糊值) 以上就会走全表扫描。

所以 MySql 创建的索引并不是不生效,而是它觉得全表扫描都比走你创建的索引快。哈哈。

ok,结束!突然又想问个问题!

select hobby from t_test where hobby = '玩游戏';

走索引吗?


程序员小朱
分享开发、架构、娱乐等综合性知识,每天看宝典,就选它。技术提升之路上不少乏味,为您创造乐趣、提供学习路上的笔记,经验以及感悟,愿与君共同成长!
12篇原创内容
Official Account