运用MOD函数优化索引：破解百万数据分批处理效能难题

利用MOD函数优化索引：攻克百万数据分批处理效能难题

问题背景

在GreatSQL中，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 2000

此场景是对业务数据进行分批处理，每次获取2000条数据，借助MOD函数分成6个通道并行处理，每批处理完成后会修改deal_status的状态，每月涉及的数据量约两百万，客户反馈该SQL执行速度逐渐变慢，后期执行时长能达到20秒。通过执行计划分析可知，SQL的主要耗时点在于依据WHERE条件从g表读取数据，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 VALUES LESS THAN (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();

查询数据

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)

业务场景验证

依据造数规则，能够看出满足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;

执行计划如下

greatsql>  EXPLAIN  ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=106.00 rows=10) (actual time=21.632..21.819 rows=10 loops=1)
    -> Filter: ((t1.bill_month = 202505) and (((t1.bill_seq % 6) + 1) = 6))  (cost=106.00 rows=850) (actual time=21.631..21.816 rows=10 loops=1)
        -> Index lookup on t1 using idx_deal_status (deal_status=1)  (cost=106.00 rows=8500) (actual time=0.027..18.503 rows=7559 loops=1)

1 row in set (0.02 sec)

greatsql>  EXPLAIN  ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=1 LIMIT 10;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=106.00 rows=10) (actual time=0.050..0.170 rows=10 loops=1)
    -> Filter: ((t1.bill_month = 202505) and (((t1.bill_seq % 6) + 1) = 1))  (cost=106.00 rows=850) (actual time=0.049..0.168 rows=10 loops=1)
        -> Index lookup on t1 using idx_deal_status (deal_status=1)  (cost=106.00 rows=8500) (actual time=0.029..0.142 rows=50 loops=1)

1 row in set (0.00 sec)

查询deal_status=1 and mod(bill_seq,6)+1=6 的SQL，需要扫描7559行数据来获取10条数据。

查询deal_status=1 and mod(bill_seq,6)+1=1 的SQL，只需扫描50行就能获取10条数据。

这表明，在真实业务案例中，分6个通道读取数据时会出现此类情况，数据变化会致使SQL执行效率不稳定。

创建函数索引

接下来创建联合函数索引，查询SQL执行计划。

ALTER TABLE t1 DROP INDEX idx_deal_status;
ALTER TABLE t1 ADD INDEX idx_dealstaus_billseq(deal_status,(MOD(bill_seq,6)+1));

greatsql>EXPLAIN SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | p1         | ref  | idx_dealstaus_billseq | idx_dealstaus_billseq | 2       | const | 5000 |    10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

greatsql>EXPLAIN  ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=71.00 rows=10) (actual time=27.500..27.530 rows=10 loops=1)
    -> Filter: ((t1.bill_month = 202505) and (((t1.bill_seq % 6) + 1) = 6))  (cost=71.00 rows=500) (actual time=27.499..27.528 rows=10 loops=1)
        -> Index lookup on t1 using idx_dealstaus_billseq (deal_status=1)  (cost=71.00 rows=5000) (actual time=0.036..23.827 rows=8344 loops=1)
1 row in set (0.02 sec)

从执行计划可以看出，仅用到了deal_status一列的索引来定位，(mod(bill_seq,6)+1) 并未起到定位作用，需要扫描8344行数据来获取10条数据。

函数索引失效了，这是为什么呢？

索引失效问题解决

为了解决问题，采用了曲线救国的办法，添加虚拟列，通过虚拟列创建联合索引。

ALTER TABLE t1 ADD c1 INT GENERATED ALWAYS AS (MOD(bill_seq,6)+1);
ALTER TABLE t1 ADD INDEX idx_deal_c1(deal_status,c1);
greatsql>  EXPLAIN SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
+----+-------------+-------+------------+------+-----------------------------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                     | key         | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------------------+-------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | p1         | ref  | idx_dealstaus_billseq,idx_deal_c1 | idx_deal_c1 | 7       | const,const |  166 |    10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------------------+-------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

greatsql> EXPLAIN  ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=22.66 rows=10) (actual time=0.098..0.171 rows=10 loops=1)
    -> Filter: (t1.bill_month = 202505)  (cost=22.66 rows=17) (actual time=0.088..0.160 rows=10 loops=1)
        -> Index lookup on t1 using idx_deal_c1 (deal_status=1, c1=6)  (cost=22.66 rows=166) (actual time=0.087..0.156 rows=10 loops=1)

1 row in set (0.00 sec)

从执行计划可以看出，用上了虚拟列的联合索引，只需扫描10行数据就能获取到10行数据，两个条件都起到了定位作用。

问题得以解决，不过回过头来思考，虚拟列和函数索引有何不同呢，理论上是相同的，却出现函数索引不行的情况。

不知细心的你是否发现一个问题，创建虚拟列时指定列类型为INT，而创建表时bill_seq列为DECIMAL类型。所以问题聚焦在MOD函数到底返回什么类型，(MOD(bill_seq,6)+1) =6 是否发生了隐式转换，导致索引无法使用。

GreatSQL对MOD函数的参数管理较为宽松，MOD(N,M)，两个参数通常是整形数值，也可以是定点和浮点类型数值，返回值可以是小数。本案例中MOD(bill_seq,6) ，bill_seq为DECIMAL(20,0)类型，返回值应与参数bill_seq类型一致。

一般情况下发生隐式转换，通常认为列类型是VARCHAR，而条件值给了数值，但MOD函数是数值类型函数，返回数值类型的数据，所以一开始没有往隐式转换方面考虑。

在DECIMAL与INT之间是否存在隐式转换呢？根据以往经验，列类型为decimal类型时，直接传值数值是没问题的，本例中deal_status 为DECIMAL(2,0)类型，能够用上索引。

抱着尝试的态度，将条件值使用CAST函数转换成DECIMAL类型。

greatsql> EXPLAIN SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=CAST(6 AS DECIMAL) LIMIT 10;
+----+-------------+-------+------------+------+-----------------------------------+-----------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                     | key                   | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------------------+-----------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | p1         | ref  | idx_dealstaus_billseq,idx_deal_c1 | idx_dealstaus_billseq | 13      | const,const |  166 |    10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------------------+-----------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

greatsql>  EXPLAIN  ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=CAST(6 AS DECIMAL) LIMIT 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=22.66 rows=10) (actual time=0.083..0.126 rows=10 loops=1)
    -> Filter: (t1.bill_month = 202505)  (cost=22.66 rows=17) (actual time=0.077..0.118 rows=10 loops=1)
        -> Index lookup on t1 using idx_dealstaus_billseq (deal_status=1, ((bill_seq % 6) + 1)=6)  (cost=22.66 rows=166) (actual time=0.075..0.115 rows=10 loops=1)

1 row in set (0.00 sec)

从执行计划可以看出，使用了之前创建的函数索引 idx_dealstaus_billseq(deal_status,(mod(bill_seq,6)+1))。MOD函数部分也起到了定位作用，是不是有意外之喜。

总结：

1.MOD函数返回值类型取决于参数值类型，当参数既有DECIMAL，又有INT时，会返回DECIMAL类型。

2.MOD函数索引使用DECIMAL类型的参数时，条件值也需使用DECIMAL类型，才能用上该函数索引定位。

3.虚拟列默认不占用存储空间，但指定列的类型会间接影响其生成表达式的类型。

Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是面向金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等核心特性，可作为MySQL或Percona Server的可选替代方案，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区
Gitee
GitHub
Bilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html