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

例如：订单状态字段只有6个值： 1 待付款，2 待发货，3.待收货，4.已完成 5.已取消 6.退款售后

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

• 建立相关表数据

建立带索引的表

CREATE TABLE `orders_1` (
	`id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_no` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '订单号',
	`goods_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '商品名称',
	`goods_num` INT ( 4 ) NOT NULL DEFAULT '1' COMMENT '商品数量',
	`pay_status` TINYINT ( 1 ) NOT NULL DEFAULT '0' COMMENT '0 未支付 1.已支付 2.已退款',
	`order_status` TINYINT ( 1 ) NOT NULL DEFAULT '1' COMMENT '1 待付款，2 待发货，3.待收货，4.已完成 5.已取消 6.退款售后',
	`created_date` date NOT NULL COMMENT '下单日期',
	`created_at` datetime NOT NULL COMMENT '下单日期',
	PRIMARY KEY ( `id` ),
	KEY `idx_order_status` ( `order_status` ),
	KEY `idx_order_no` ( `order_no` ),
KEY `idx_created_date` ( `created_date` ) 
) ENGINE = INNODB AUTO_INCREMENT = 12209 DEFAULT CHARSET = utf8;

建立不带索引的表

CREATE TABLE `orders_2` (
	`id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_no` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '订单号',
	`goods_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '商品名称',
	`goods_num` INT ( 4 ) NOT NULL DEFAULT '1' COMMENT '商品数量',
	`pay_status` TINYINT ( 1 ) NOT NULL DEFAULT '0' COMMENT '0 未支付 1.已支付 2.已退款',
	`order_status` TINYINT ( 1 ) NOT NULL DEFAULT '1' COMMENT '1 待付款，2 待发货，3.待收货，4.已完成 5.已取消 6.退款售后',
	`created_date` date NOT NULL COMMENT '下单日期',
	`created_at` datetime NOT NULL COMMENT '下单日期',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 12209 DEFAULT CHARSET = utf8;

往两张表中填入数据，除了一张表带索引，一张表没索引，数据完全一致。

其中created_at字段，每秒的时间点，只会有一条数据，方便查询数据的唯一性处理

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

select * from orders_1 where order_status=1 and created_at='2020-01-01 00:37:32';

表数据量/耗时	索引表(orders_1)	非索引表（orders_2）	order_status=1数据总量
1W	0.00s	0.00s	约1596
8W	0.02s	0.02s	约13092
32W	0.08s	0.09s	约52717
50W	0.16s	0.13s	约8.25W
100W	6.62s	0.45s	约16.6W
200W

通过比较，在数据量小于32W时，加索引和不加索引查询速度差别不大，数据大于50W后，随着数据量的增大，加索引的查询速度相对会越来越慢。100W级别数据后，加索引表的查询速度急速下降。

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

以数据量为100W时分析：

select * from orders_1 where order_status=1 and created_at='2020-01-01 00:37:32';

查询结果所在的数据所在位置为：2252；

通过explain分析执行情况

索引表：

 非索引表

加索引扫描的数据rows=342505 (实际数据行数：166119)，不加索引rows=996109 (全表扫描)，明明加索引的扫描条目更少，为何反而变慢了呢？

rows的值为一个估算值：

Rows = ((Records_PLeft + Records_PRight)/2)*Page_Num

MYSQL中，一行数据大约一行数据的数据大小为1k，InnoDB页的大小默认是16k。即一页可以存16行数据。

一页的大小：16384  一个存储索引数据的指针14字节，即一页能存放：16384/14=1170 个索引的指针数据。

100W数据，大约Page_num = 1000000/1170=855个页

每页的数据量实际并没有存放满数据：目前大约存放了400个，

rows 估算值：(（400+400）/2)*855 = 342000 （计算并不准确，只是一个推理过程）

回到慢的原因：

• 有索引的查询过程：

第一步：根据索引树 过滤符合order_status条件数据，获得数据的id

第二步：根据数据的id，回表查询到数据，判断created_at是否符合条件，找到符合条件的数据id.

第三部：根据数据的id，回表查询 完整的记录返回。

单条数据的查询过程，存在2次回表查询。166119 +  次 (实际查询过程，也存在多条数据在同一页，次数统计并不准确)

• 无索引的查询过程：

理想状态下，1页有16条数据， 遍历表需要io : 1000000/16= 62500 次；

结论：虽然上述有索引的查询过程，IO次数只是一些估算值，但远远大于无索引的表遍历查询方式。所以查询过程会变慢。

注：

查询数据库时，不论读一行，还是读多行，都是将这些行所在的整页数据加载，然后在内存中匹配过滤出最终结果。即一次页加载就是一次IO。查询慢的主要原因就是IO的次数+带宽传输速度。

总结：禁止在更新十分频繁、区分度不高的属性上建立索引
 

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

深入理解 mysql 索引 原理_华尔街之猫的博客-CSDN博客

InnoDB一棵B+树可以存放多少行数据？_华尔街之猫的博客-CSDN博客

所学知识有限，所有储备都是为了让自己去理解这些查询过程的原因，数值的统计也是只辅助理解，并不一定准确。如有错误，欢迎指出。