背景
我们生产环境均采用单表多租户设计模式，即一个表中存有多个租户数据，在实际的业务中每个租户的数据是隔离的，即A租户不可能访问到B租户数据，假设租户ID的字段为profileid，我们每个索引都会将profileid放在最左第一列(主键除外)，如（profileid，date,status）。
昨天运营需要统计一个线上数据，会跨租户查询,wherer条件中将不能带有租户ID字段，这将导致所有索引均失效，整个SQL查询非常非常慢。
问题SQL
实际语句更复杂，我将语句简单化了，说明优化过程
研发同学写的原版SQL
explain
select profileid,billcode,billtype,billdate,billid from a1
where a1.billtype IN( 604) and a1.billdate >='2025-03-14' and a1.billdate <='2025-03-15'
复制
通过执行计划看到，将会是全表扫描，扫描300多万行
实际执行需要46327ms
性能优化
优化思路：
实际上此表有（profileid,billtype,billdate）联合索引，而billid 是主键字段，在整个语句中只有billcode不在上述联合索引之内，所以改造成先走Index skip scan，然后再自关联取billcode即可。
一个表中租户一般低于1000,所以完全符合Index skip scan的条件。我来改写，让这个语句走Index skip scan
优化的第一版SQL
explain
select
t.*,bi.billcode from
(
select profileid,billtype,billdate,billid from a1
where a1.billtype IN( 604) and a1.billdate >='2025-03-14' and a1.billdate <='2025-03-15'
) t
inner join erp_bill_index a1 as bi on t.billid = bi.billid
复制
通过执行计划发现，MySQL给我合并了子查询，虽然扫描行数未变，但走了索引覆盖扫描
虽然没有走到 index skip scan 但性能还是有较大提升 从46327ms提升到了546ms 提升84倍
优化的第二版SQL
explain
select /*+no_merge() */
t.*,bi.billcode from
(
select profileid,billtype,billdate,billid from a1
where a1.billtype IN( 604) and a1.billdate >='2025-03-14' and a1.billdate <='2025-03-15'
) t
inner join a1 bi on t.billid = bi.billid
复制强制用hint no_merge() 阻止MySQL的合并优化
在执行计划中看到扫描行数少了，也终于走到了 Index skip scan
性能继续提升 从46327ms提升到了178ms 提升260倍
Index skip scan的要求
1、索引为复合索引
2、查询为单表查询
3、查询不使用GROUP BY或DISTINCT
4、查询仅引用索引中的列
5、索引为（a,b,c）条件用到了b,c 且b、c设计不能为null
贴上官方的说明
Using this strategy decreases the number of accessed rows because MySQL skips the rows that do not qualify for each constructed range. This Skip Scan access method is applicable under the following conditions:
Table T has at least one compound index with key parts of the form ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n]). Key parts A and D may be empty, but B and C must be nonempty.
The query references only one table.
The query does not use GROUP BY or DISTINCT.
The query references only columns in the index.
The predicates on A_1, …, A_k must be equality predicates and they must be constants. This includes the IN() operator.
The query must be a conjunctive query; that is, an AND of OR conditions: (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR …) AND …
There must be a range condition on C.
Conditions on D columns are permitted. Conditions on D must be in conjunction with the range condition on C.
Use of Skip Scan is indicated in EXPLAIN output as follows:
Using index for skip scan in the Extra column indicates that the loose index Skip Scan access method is used.
If the index can be used for Skip Scan, the index should be visible in the possible_keys column.
评论
0 点赞