MOD函数索引实战：解决百万级数据分批处理性能瓶颈-极客码头

问题背景

GreatSQL 的数索数据 MOD 函数，大家应该都不陌生，引实使用MOD函数创建函数索引，战解是决百不是很少有人这么用呀，下面听我讲讲使用MOD函数创建函数索引的分批故事吧。

故事的处理引子呢，是瓶颈有这么一个使用场景，为了忽略客户真实的数索数据业务，对涉及的引实表只保留了别名。

MOD函数索引实战：解决百万级数据分批处理性能瓶颈

复制SELECT g.* FROM g JOIN a ON g.customer_id = a.customer_id JOIN d ON a.customer_code = d.customer_code JOIN f ON g.subs_id = f.subs_id JOIN c ON a.company_id = c.province_id JOIN e ON d.city = e.city_id WHERE g.bill_month = 202505 AND g.deal_status = 1 AND (MOD(g.bill_seq,战解 6) + 1) = 1 LIMIT 20001.2.3.4.5.6.7.8.9.10.11.

这个场景是对业务数据分批处理，每次取2000条数据，决百使用MOD函数分6个通道并行处理，分批每批处理完后会修改deal_status的处理状态，每月涉及的瓶颈数据量约两百万，亿华云计算客户描述这个SQL越执行越慢，数索数据后面执行会达到20s。

通过执行计划分析获知，SQL主要慢在了对g表按照 WHERE 条件读取数据上，这个g表在 deal_status上有单列索引。由于业务处理的复杂性，并不总是6个通道齐头并进的处理的，如果符合deal_status的条件多，但是满足MOD函数条件的数据少，g表读取满足条件的2000条数据就会耗时久。考虑到这种情况，决定在deal_status,mod(g.bill_seq, 6) + 1 上建联合函数索引。函数索引生效后，SQL执行效率显著提升，性能稳定在毫秒级别，不因数据变化而变化。服务器托管

为什么此处建联合函数索引能提升效率呢？

MOD(g.bill_seq, 6)的取值只有6个值，函数索引的选择性并不好，之所以建联合函数索引能提升效率，是因为数据处理过程中数据会变得不均匀，而该业务SQL有LIMIT 关键字限制，只提取前2000行数据，故通过联合函数索引精确定位后，可快速取得数据。

下面介绍一下MOD函数索引创建过程中遇到的插曲故事。

实验验证

准备工作

建表t1，通过存储过程p1填充了10000行数据。

复制CREATE TABLE t1( bill_seq DECIMAL(20,0), bill_month INT, deal_status DECIMAL(2,0) ) PARTITION BY RANGE (bill_month) (PARTITION p1 VALUESLESSTHAN (202506)); DELIMITER // CREATE OR REPLACE PROCEDURE p1 IS BEGIN FOR i IN 1..10000 LOOP INSERT INTO t1(bill_seq,bill_month,deal_status) VALUES(i,202505,1); END LOOP; UPDATE t1 SET deal_status=2 WHERE bill_seq<9000 AND MOD(bill_seq,6)+1=6; END; // DELIMITER ; CALL p1();1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19. 查询数据复制greatsql> SELECT COUNT(*) FROM t1 WHERE deal_status=1; +----------+ | count(*) | +----------+ | 8500 | +----------+ 1 row in set (0.00 sec) greatsql> SELECT COUNT(*) FROM t1 WHERE deal_status=1 AND MOD(bill_seq,6)+1=6; +----------+ | count(*) | +----------+ | 166 | +----------+ 1 row in set (0.02 sec) greatsql> SELECT COUNT(*) FROM t1 WHERE deal_status=1 AND MOD(bill_seq,6)+1=1; +----------+ | count(*) | +----------+ | 1666 | +----------+ 1 row in set (0.03 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23. 业务场景验证

根据造数规则，可以看出满足deal_status=1的数据量多，deal_status=1 and mod(bill_seq,6)+1=6 的数据量不多。

表t1 上增加deal_status的单列索引,查询两个语句的执行计划。

复制ALTER TABLE t1 ADD INDEX idx_deal_status(deal_status); SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10; SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=1 LIMIT 10;1.2.3.

执行计划如下：

复制greatsql>EXPLAIN ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD (bill_seq,6)+1=6 LIMIT 10;

WordPress模板