券码表(msp_voucher)使用shardingsphere分成了32张表,分表字段为卡券号(coupon_no),主要是对查询券码表的优化。
考虑到联查效率问题,接口使用代码逻辑查询。
券码表共有1111033条数据
{
"pageNum":100000,
"pageSize":10
}
查询券码表sql如下,分页使用PageHelper,用时:
| 1,10 | 100,10 | 10000,10 | 100000,10 |
|---|---|---|---|
| 659 ms | 896 ms | 29.94 s | 2 m 2.76 s |
selectfrom msp_voucher order by create_time desc voucher_no = #{voucherNo} and voucher_end_time between #{dateRangeBegin} and date_add(#{dateRangeEnd},interval 1 day) and status = #{status} and coupon_no in #{couponNo}
覆盖索引:覆盖索引是指一个索引包含或者覆盖了所有需要查询的字段的值,不需要根据索引回表查询数据
覆盖索引必须要存储索引列的值,因此MySQL只能用BTree索引做覆盖索引。
- 索引条目通常远小于数据行大小,极大减少数据访问量。
- 因为索引按照顺序存储,所以io密集型查询会比随机从磁盘读取每一行数据的io少的多。
- innoDB使用聚簇索引,innoDB的二级索引在叶子节点保存了主键,如果二级主键能覆盖查询就可以避免对主键索引的二次查询。
优化一:使用覆盖索引,先查出具体十条id,再id in ()去查。由于sharding-jdbc不支持在子查询中出现同样的表,无法自关联查询,会报错:Must have one sharding with subquery。因此分成两个sql查询。
| 1,10 | 100,10 | 10000,10 | 100000,10 |
|---|---|---|---|
| 918 ms | 1066 ms | 3 s | 25s |
select id from msp_voucherorder by create_time desc limit #{offset}, #{size} voucher_no = #{voucherNo} and voucher_end_time between #{dateRangeBegin} and date_add(#{dateRangeEnd},interval 1 day) and status = #{status} and coupon_no in #{couponNo}
selectfrom msp_voucher where id in #{id} and coupon_no in#{couponNo}
优化二:依然是覆盖索引,先查出符合条件的第一个id,再使用id作为条件过滤,id为雪花算法生成,为趋势递增。
| 1,10 | 100,10 | 10000,10 | 100000,10 |
|---|---|---|---|
| 764ms | 913ms | 2s | 17s |
select id from msp_voucherorder by id desc limit #{offset}, 1 voucher_no = #{voucherNo} and voucher_end_time between #{dateRangeBegin} and date_add(#{dateRangeEnd},interval 1 day) and status = #{status} and coupon_no in#{couponNo}
selectfrom msp_voucher where id #{id} and voucher_no = #{voucherNo} and voucher_end_time between #{dateRangeBegin} and date_add(#{dateRangeEnd},interval 1 day) and status = #{status} and coupon_no in#{couponNo} order by id desc limit #{size}



