问题背景
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_idWHERE g.bill_month = 202505 AND g.deal_status = 1 AND (MOD(g.bill_seq, 6) + 1) = 1LIMIT 2000
复制代码
这个场景是对业务数据分批处理，每次取 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 VALUES LESS THAN (202506));
DELIMITER //CREATE OR REPLACE PROCEDURE p1 ISBEGIN 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.虚拟列默认不占用存储空间，但可以指定列的类型，会间接影响其生成表达式的类型。
评论