关于 mysql 字段值分布很少的字段要不要加索引的问题

在我还是个mysql新手的时候，看到有的同事给字段值分布很少的字段也加索引，这违背了我看过的大部分mysql索引优化的文章内容，甚是疑惑。

例如：订单状态字段只有6个值： 0 待确认，1 已确认，2 已收货，3 已取消，4 已完成，5 已作废

在我理解mysql B+tree的原理后，很有必要去实战这种情况到底有没有必要加索引。

• 建立相关表数据

建立带索引的表

DROP TABLE if EXISTS `bool_index`;
CREATE TABLE `bool_index` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`rand_id` VARCHAR (200) COMMENT '随机数',
	`order_status` TINYINT (1) NOT NULL DEFAULT '0' COMMENT '订单状态.0待确认，1已确认，2已收货，3已取消，4已完成，5已作废',
	`created_at` datetime NOT NULL,
	PRIMARY KEY (`id`),
	KEY `idx_order_status` (`order_status`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

建立不带索引的表

DROP TABLE if EXISTS `bool_no_index`;
CREATE TABLE `bool_no_index` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`rand_id` VARCHAR (200) COMMENT '随机数',
	`order_status` TINYINT (1) NOT NULL DEFAULT '0' COMMENT '订单状态.0待确认，1已确认，2已收货，3已取消，4已完成，5已作废',
	`created_at` datetime NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

通过存储过程造一些测试数据

DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_index`$$
CREATE PROCEDURE proc_index()
BEGIN
   DECLARE rand_id VARCHAR(120);
   DECLARE order_status INT(1);
   DECLARE i INT DEFAULT 0;
   DECLARE createtime DATETIME;
   -- 调试过程, 先插入5条
   WHILE i < 10000 DO
     SET rand_id= SUBSTRING(MD5(RAND()),1,28);
			-- 生成 订单状态值.0待确认，1已确认，2已收货，3已取消，4已完成，5已作废
		 SET order_status = FLOOR(RAND()*10)%6;
     SET createtime = NOW();
     INSERT INTO  `bool_index`(`rand_id`,`order_status`,`created_at`) VALUES(rand_id,order_status,createtime);
     INSERT INTO  `bool_no_index`(`rand_id`,`order_status`,`created_at`) VALUES(rand_id,order_status,createtime);
     SET i=i+1;
     END WHILE;
END$$
call proc_index();

• 在表数据量不同的情况下测试结果：

表数据量/耗时	select * from bool_index where order_status=3 and rand_id='7d257470725dae2c78db97263deb';	select * from bool_no_index where order_status=3 and rand_id='7d257470725dae2c78db97263deb';
1W	0.002s	0.004s
2W	0.007s	0.008s
8W	0.023s	0.027s
16W	0.049s	0.053s
32W	0.091s	0.085s
64W	2.909s	0.600s
100W	3.345s	0.762s

通过比较，在数据量小于16W时，加索引查询速度略微比不加索引快，数据大于16W时，随着数据量的增大，加索引的查询速度相对会越来越慢。

• 为什么随着数据量的增加，反而加索引的查询比没加索引的更慢呢？

如：第20001万条记录rand_id='56079ad22da839c1a00bd812a191'  order_status=3

通过explain分析执行情况

 

加索引扫描的数据rows=366798，不加索引rows=997976 (全表扫描)，明明加索引的扫描条目更少，为何反而变慢了呢？

 

举一个非常好理解的场景（通过索引读取表中20%的数据）解释一下这个有趣的概念：（例子来源 http://blog.itpub.net/519536/viewspace-612715/）

假设一张表含有10万行数据--------100000行
我们要读取其中20%(2万)行数据----20000行
表中每行数据大小80字节----------80bytes
数据库中的数据块大小8K----------8000bytes
所以有以下结果：
每个数据块包含100行数据---------100行
这张表一共有1000个数据块--------1000块

上面列出了一系列浅显易懂的数据，我们挖掘一下这些数据后面的故事：

通过索引读取20000行数据 = 约20000个table access by rowid = 需要处理20000个块来执行这个查询
但是，请大家注意：整个表只有1000个块！

所以：如果按照索引读取全部的数据的20%相当于将整张表平均读取了20次！！So，这种情况下直接读取整张表的效率会更高。）

 

具体深层次的原因请了解先B+tree的底层原理

https://blog.csdn.net/qq_24935119/article/details/108185311

-