mysql 字段值分布很少的字段要不要加索引 | 您所在的位置:网站首页 › mysql连表查询索引有用吗 › mysql 字段值分布很少的字段要不要加索引 |
在我还是个菜鸟程序员的时候,看到有的同事给字段值分布很少的字段也加索引,这违背了我看过的大部分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数据总量1W0.00s0.00s约15968W0.02s0.02s约1309232W0.08s0.09s约5271750W0.16s0.13s约8.25W100W6.62s0.45s约16.6W200W通过比较,在数据量小于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_NumMYSQL中,一行数据大约一行数据的数据大小为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博客 所学知识有限,所有储备都是为了让自己去理解这些查询过程的原因,数值的统计也是只辅助理解,并不一定准确。如有错误,欢迎指出。 |
CopyRight 2018-2019 实验室设备网 版权所有 |