vlambda博客
学习文章列表

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

• 建表语句

-- 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 ('小祝', '玩游戏');

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

这条语句走没走索引！

我觉得吧，会有 90% 的人会觉的走索引吧！

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

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

执行结果

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

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 = '学习';

执行结果

从上面可以看出，这条语句走了，非唯一性索引扫描（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 = '玩游戏';

走索引吗？