mysql创建的单列索引不生效，查询数据仍是全表扫描？｜8月更文挑战

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

如题：

建表语句


-- 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 = '玩游戏';

走索引吗？

一枚猫奴程序员，喜欢的请关注公众号《程序员小朱》