mysql 字段值分布很少的字段要不要加索引

最新推荐文章于 2024-05-04 13:51:18 发布

C18298182575

最新推荐文章于 2024-05-04 13:51:18 发布

阅读量828

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

原文链接：https://blog.csdn.net/qq_24935119/article/details/108601180

版权

mysql 专栏收录该内容

53 篇文章 1 订阅

订阅专栏

在我还是个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 < 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='bd0bcd23960dbe0140bea563e7bd';

select * from bool_no_index

where order_status=3 and rand_id='bd0bcd23960dbe0140bea563e7bd';

order_status=3数据总量
1W   0.002s   0.002s   约2000
4W   0.011s   0.009s   约8000
8W   0.021s   0.021s   约1.6W
16W   0.059s   0.040s   约3.2W
32W   0.142s   0.110s   约6.3W
64W   1.194s   0.383s   约12W
100W   2.761s   0.563s   约20W
200W   7.025s   1.158s   约40W
通过比较，在数据量小于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
————————————————
版权声明：本文为CSDN博主「华尔街之猫」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_24935119/article/details/108601180