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;
   -- 调试过程, 插入一些数据
   WHILE i  select * from bool_no_index where id>20000 limit 1;
+-------+------------------------------+--------------+---------------------+
| id    | rand_id                      | order_status | created_at          |
+-------+------------------------------+--------------+---------------------+
| 20001 | 6b84b2dbd3e81bfc67366a640d1a |            0 | 2021-08-24 15:05:59 |
+-------+------------------------------+--------------+---------------------+
1 row in set (0.00 sec)

通过explain分析执行情况

mysql> explain select * from bool_no_index where order_status=0 and rand_id="6b84b2dbd3e81bfc67366a640d1a";
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | bool_no_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 30259 |     1.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from bool_index where order_status=0 and rand_id="6b84b2dbd3e81bfc67366a640d1a";
+----+-------------+------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | bool_index | NULL       | ref  | idx_order_status | idx_order_status | 1       | const | 6039 |    10.00 | Using where |
+----+-------------+------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                               |
+----------+------------+-----------------------------------------------------------------------------------------------------+
|       21 | 0.00019875 | explain select * from bool_no_index where order_status=0 and rand_id="6b84b2dbd3e81bfc67366a640d1a" |
|       22 | 0.00022850 | explain select * from bool_index where order_status=0 and rand_id="6b84b2dbd3e81bfc67366a640d1a"    |
+----------+------------+-----------------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

举一个非常好理解的场景（通过索引读取表中20%的数据）解释一下这个有趣的概念：

假设一张表含有10万行数据----------100000行 我们要读取其中20%(2万)行数据----20000行 表中每行数据大小80字节-------------80bytes 数据库中的数据块大小8K-------------8000bytes

所以有以下结果：

每个数据块包含100行数据---------100行 这张表一共有1000个数据块--------1000块

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

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

所以：如果按照索引读取全部的数据的20%相当于将整张表平均读取了20次！！

So，这种情况下直接读取整张表的效率会更高。）（索引还涉及多次回表查询问题）

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

查看mysql 语句执行时间

开启profile==>查询表==>显示时间

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;

mysql> show profiles;
+----------+----------+----------------------+
| Query_ID | Duration | Query                |
+----------+----------+----------------------+
|        1 | 0.277744 | select * from t_user |
+----------+----------+----------------------+
1 row in set